批量操作与数据同步-优快云博客

本文介绍了一种使用批处理方式将日志数据插入Oracle数据库的方法，并实现数据同步时的作业号标记功能。通过批处理提高数据插入效率，利用作业号进行数据同步状态追踪。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

绑定变量+ 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() {
				@Override
				public 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]);
				}
				@Override
				public int getBatchSize() {
					return logList.size();
				}
			});
			
			return updateCounts.length;
		} catch (DataAccessException e) {
			log.error(e.getMessage());
		}
		return 0;
	}