以下是部分核心代码,仅供参考,具体代码可以点击链接下载
1.csdn下载链接: link
pom
<dependency>
<groupId>com.microsoft.sqlserver</groupId>
<artifactId>mssql-jdbc</artifactId>
<version>6.4.0.jre8</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
工具类
数据库连接工具
/**
* 获取 Connetion
*/
public static Connection getConnection(String url, String username, String password) throws SQLException {
return DriverManager.getConnection(url, username, password);
}
properties配置
#数据库同步开关
scheduled.enable=true
#本机SqlServer-源数据库
mj0.jdbc.url=jdbc:jtds:sqlserver://127.0.0.1;DatabaseName=mj
mj0.jdbc.username=sa
mj0.jdbc.password=root
mj0.jdbc.tablename=visit_data_log
#目标SqlServer
mj.jdbc.url=jdbc:jtds:sqlserver://192.168.1.6;DatabaseName=mj;socketTimeout=60000
mj.jdbc.username=sa
mj.jdbc.password=root
mj.jdbc.tablename=visit_data_log
同步业务
log.info("数据同步任务开始");
PreparedStatement statement0 = null;
PreparedStatement statement = null;
try {
//读取
String sql0 = "select * from ..........";
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 .........";
try {
statement = connection.prepareStatement(sql);
statement.setString(1, "");
int update = statement.executeUpdate();
if(update != 0){
row ++;
}
}catch (Exception e){
log.info("【批量同步数据出现异常:】"+e);
}
}
log.info("批量同步数据成功,共写入:"+row+"条!");
max = Collections.max(dataList);
//更新时间
syncDate(connection0,max);
}
}catch (Exception e){
e.printStackTrace();
log.error("【数据库同步出现异常】", e.getMessage());
}finally {
JDBCUtils.closeResource2(connection0, statement0);
JDBCUtils.closeResource2(connection, statement);
}
定时器
@Scheduled(cron = "0 0/5 * * * ?")
public void timerForSyncArchives() {
log.info("定时同步数据的定时任务开始");
if (enable) {
//业务代码
}
log.info("定时同步数据的定时任务结束");
}