springboot双数据库定时同步

package com.bwlib.doorserver.service.impl; import com.bwlib.doorserver.data.DoorDataTO; import com.bwlib.doorserver.util.JDBCUtils; import lombok.extern.slf4j.Slf4j; import org.springframework.beans.factory.annotation.Value; import org.springframework.stereotype.Service; import org.springframework.transaction.annotation.Transactional; import org.springframework.util.StringUtils; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.Collections; import java.util.List; /** * @author Mr.Liu * @version 1.0 * @description: 两个SqlServer定时同步 * @date 2022/4/8 17:03 */ @Service @Slf4j public class SyncDataServiceImpl{ @Value("${mj0.jdbc.url}") private String url0; @Value("${mj0.jdbc.username}") private String username0; @Value("${mj0.jdbc.password}") private String password0; @Value("${mj0.jdbc.tablename}") private String tablename0; @Value("${mj.jdbc.url}") private String url; @Value("${mj.jdbc.username}") private String username; @Value("${mj.jdbc.password}") private String password; @Value("${mj.jdbc.tablename}") private String tablename; @Transactional(rollbackFor = Exception.class) public void syncData() { //源数据库 Connection connection0 = null; //目标数据库 Connection connection = null; try { //从源数据库获取数据 connection0 = JDBCUtils.getConnection(url0, username0, password0); //目标数据库 connection = JDBCUtils.getConnection(url, username, password); if (connection0 == null || connection == null) { log.info("【同步数据，获取数据库连接失败】"); } log.info("【同步数据，获取数据库连接成功】"); } catch (Exception e) { e.printStackTrace(); log.info("【同步数据，获取数据库连接出现异常：】"+e.getMessage()); } String startDate = getSyncDate(connection0); if(StringUtils.hasText(startDate)){ int queryCount = queryCount(connection0, startDate); if (queryCount > 0) { syncData(connection0,connection,startDate); } } } /** * @description: 获取上次同步时间 * @author Mr.Liud * @date 2022/6/17 15:35 * @version 1.0 */ private String getSyncDate(Connection connection) { PreparedStatement statement = null; ResultSet resultSet = null; try { String sql = "select top 1 create_date from sync_date order by create_date desc"; statement = connection.prepareStatement(sql); resultSet = statement.executeQuery(); String syncDate = ""; while (resultSet.next()) { syncDate = resultSet.getString("create_date"); } log.info("【获取到上次同步时间：{}】", syncDate); return syncDate; } catch (SQLException e) { log.info("【获取到上次同步时间出现异常】", e); } return null; } /** * @description: 获取待同步数据数量 * @author Mr.Liud * @date 2022/6/17 15:41 * @version 1.0 */ private int queryCount(Connection connection, String startDate) { PreparedStatement statement = null; ResultSet resultSet = null; int queryCount = 0; String countSql = "SELECT COUNT(user_id) AS number FROM visit_data_log WHERE visit_date_time > ? "; try { statement = connection.prepareStatement(countSql); statement.setString(1, startDate); System.out.println(countSql); resultSet = statement.executeQuery(); if (resultSet.next()) { queryCount = resultSet.getInt(1); } } catch (Exception e) { e.printStackTrace(); log.error("获取待同步数据数量出现异常：{}",e.getMessage()); } return queryCount; } /** * @description: 查询结果转do * @author Mr.Liud * @date 2022/6/17 16:06 * @version 1.0 */ private DoorDataTO getDoorDataTOByResultSet(ResultSet resultSet)throws SQLException{ DoorDataTO doorDataTO = new DoorDataTO(); doorDataTO.setUserId(resultSet.getString("user_id")); doorDataTO.setVisitDateTime(resultSet.getString("visit_date_time")); doorDataTO.setVisitDate(resultSet.getString("visit_date")); doorDataTO.setVisitTime(resultSet.getString("visit_time")); doorDataTO.setDirection(resultSet.getInt("direction")); doorDataTO.setSnName(resultSet.getString("sn_name")); doorDataTO.setSnCode(resultSet.getString("sn_code")); doorDataTO.setUserName(resultSet.getString("user_name")); doorDataTO.setCardId(resultSet.getString("card_id")); return doorDataTO; } /** * @description: 同步数据 * @author Mr.Liud * @date 2022/4/9 17:33 * @version 1.0 */ private void syncData(Connection connection0,Connection connection, String startDate) { log.info("数据同步任务开始"); PreparedStatement statement0 = null; PreparedStatement statement = null; try { //读取 String sql0 = "select * from " + tablename0 + " where visit_date_time > ?"; String max = null; List<String> dataList = new ArrayList<>(); List<DoorDataTO> list = new ArrayList<>(); try { statement0 = connection0.prepareStatement(sql0); statement0.setString(1, startDate); ResultSet resultSet = statement0.executeQuery(); while (resultSet.next()){ DoorDataTO doorDataTO = getDoorDataTOByResultSet(resultSet); list.add(doorDataTO); dataList.add(resultSet.getString("visit_date_time")); } }catch (Exception e){ e.getMessage(); log.error("读取数据发生异常：{}",e.getMessage()); } if(list != null){ int row = 0; for (DoorDataTO doorDataTO : list) { //同步写入 String sql = "Insert into " + tablename + " values(?,?,?,?,?,?,?,?,?)"; try { statement = connection.prepareStatement(sql); statement.setString(1, doorDataTO.getUserId()); statement.setString(2, doorDataTO.getVisitDateTime()); statement.setString(3, doorDataTO.getVisitDate()); statement.setString(4, doorDataTO.getVisitTime()); statement.setString(5, String.valueOf(doorDataTO.getDirection())); statement.setString(6, doorDataTO.getSnName()); statement.setString(7, doorDataTO.getSnCode()); statement.setString(8, doorDataTO.getUserName()); statement.setString(9, doorDataTO.getCardId()); int update = statement.executeUpdate(); if(update != 0){ row ++; } }catch (Exception e){ log.info("【批量同步数据出现异常：】"+e); } } log.info("批量同步数据成功，共写入："+row+"条！"); max = Collections.max(dataList);