SpringJDBC

来源：互联网发布：记忆曲线软件编辑：程序博客网时间：2024/05/20 04:51

绑定变量+ Where In

/** * 根据要同步数据ID的集合 向省Portal表查询记录，得到的数据放到synMap (key:表名T_PROVINCE_PORTALS  value:表的数据provinceMap) * @param idsSet */private void provincePortals(Set<String> idsSet) {NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);List<Map<String, Object>> list = namedParameterJdbcTemplate.queryForList("SELECT CODE , ENDPOINT FROM T_PROVINCE_PORTALS WHERE CODE IN (:ids) ",new MapSqlParameterSource().addValue("ids", idsSet));Map<String, Object> provinceMap = new HashMap<String, Object>();for (Map<String, Object> map : list)provinceMap.put(map.get("CODE").toString(), String.valueOf(map.get("ENDPOINT")));synMap.put("T_PROVINCE_PORTALS".toLowerCase(), provinceMap);}

/** * 给需要同步的数据 打上一个作业号，表示这些数据属于这一次同步。 * @param syncType (locale:同步到本地应用的application)  (remote:同步到远程oracle) * @param workId 作业号 */private void markWorkID(String syncType, String workId) {String sql="";if("locale".equals(syncType)){sql = "UPDATE T_DATA_SYNC SET LOCALE_SYNC=2 ,LOCALE_WORK_ID=? WHERE ID IN("+ "SELECT ID FROM T_DATA_SYNC WHERE LOCALE_SYNC=1) AND LOCALE_WORK_ID IS NULL";}else if("remote".equals(syncType)){sql = "UPDATE T_DATA_SYNC SET REMOTE_SYNC=2 ,REMOTE_WORK_ID=? WHERE ID IN("+ "SELECT ID FROM T_DATA_SYNC WHERE REMOTE_SYNC=1) AND REMOTE_WORK_ID IS NULL";}int updateCount = jdbcTemplate.update(sql, workId);log.info("作业号："+workId+" 有多少条记录要被同步:" + updateCount);}

批处理

/** * 下线日志插入oracle数据库 * @param pwdLogList */public long addLogoutLogIntoOracle(List<String> logutLogList){log.info("addLogoutLogIntoOracle log size:"+logutLogList.size());//14个字段String sql="INSERT INTO T_LOGOUT_RECORD(SERIAL,USERIP,SERVERNAME,ACCOUNT,CLIENTTYPE," +"ACCOUNTTYPE,BELONEPRO,ACCESSPRO,RESULTCODE,DESCRIPTION," +"EXCEPTION,STARTTIME,ENDTIME,REMARK) " +"VALUES (?,?,?,?,?," +"?,?,?,?,?," +"?,?,?,?)";//把log 字符串 装换成 log 属性数组final List<Object[]> logList=changeLogStringToBean(logutLogList);try {int[] updateCounts = jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {@Overridepublic void setValues(PreparedStatement ps, int i) throws SQLException {ps.setString(1, (String) logList.get(i)[0]);ps.setString(2, (String) logList.get(i)[1]);ps.setString(3, (String) logList.get(i)[2]);ps.setString(4, (String) logList.get(i)[3]);ps.setInt(5, Integer.valueOf(String.valueOf(logList.get(i)[4])));ps.setInt(6, Integer.valueOf(String.valueOf(logList.get(i)[5])));ps.setString(7, (String) logList.get(i)[6]);ps.setString(8, (String) logList.get(i)[7]);ps.setString(9, (String) logList.get(i)[8]);ps.setString(10, (String) logList.get(i)[9]);ps.setString(11, (String) logList.get(i)[10]);ps.setTimestamp(12, new Timestamp(Long.valueOf((String) logList.get(i)[11])));ps.setTimestamp(13, new Timestamp(Long.valueOf((String) logList.get(i)[12])));ps.setString(14, (String) logList.get(i)[13]);}@Overridepublic int getBatchSize() {return logList.size();}});return updateCounts.length;} catch (DataAccessException e) {log.error(e.getMessage());}return 0;}

0 0