SpringBoot定时任务实现Oracle和mysql数据同步

package com.qwh.sjtb.config; import org.springframework.scheduling.annotation.Scheduled; import org.springframework.stereotype.Component; import java.sql.*; import java.text.SimpleDateFormat; import java.util.*; import java.util.Date; @Component public class Scheduler { private static final SimpleDateFormat dateFormat = new SimpleDateFormat("HH:mm:ss"); //每隔2秒执行一次 @Scheduled(fixedRate = 2000) public void testTasks() { System.out.println("定时任务执行时间：" + dateFormat.format(new Date())); } //每天23点30分执行 0 30 00,23 ? * * @Scheduled(cron = "0 30 23 ? * *") public void testTasks1() { exceDatas(); } //每天中午12点执行 @Scheduled(cron = "0 00 12 ? * *") public void testTasks2() { exceDatas(); } public void exceDatas(){ //ZCGL List<Map<String,Object>> zcgl_rs=query(USER_QUERY,DBDRIVER_ORACLE,DBURL_ORACLE,DBUSER_ORACLE,DBPASS_ORACLE,2); del(USER_DELETE,DBDRIVER_MYSQL,DBURL_MYSQL,DBUSER_MYSQL,DBPASS_MYSQL); add(USER_ADD,DBDRIVER_MYSQL,DBURL_MYSQL,DBUSER_MYSQL,DBPASS_MYSQL,2,zcgl_rs); } //定义MySQL的数据库驱动程序 public static final String DBDRIVER_MYSQL = "com.mysql.jdbc.Driver"; //定义MySQL数据库的连接地址 public static final String DBURL_MYSQL = "jdbc:mysql://127.0.0.1:3306/gljcsgldb"; //MySQL数据库的连接用户名 public static final String DBUSER_MYSQL = "mysql"; //MySQL数据库的连接密码 public static final String DBPASS_MYSQL = "mysql"; //定义Oracle的数据库驱动程序 public static final String DBDRIVER_ORACLE = "oracle.jdbc.driver.OracleDriver"; //定义Oracle数据库的连接地址 public static final String DBURL_ORACLE = "jdbc:oracle:thin:@127.0.0.1:1521:orcl"; //Oracle数据库的连接用户名 public static final String DBUSER_ORACLE = "oracle"; //Oracle数据库的连接密码 public static final String DBPASS_ORACLE = "oracle"; //要执行的sql 同步数据即先删除、再查询、再添加 public static final String USER_QUERY = "SELECT ID, NAME FROM USERS"; public static final String USER_ADD = "insert into USERS (id, name)" + " values (?,?)"; public static final String USER_DELETE = "DELETE FROM USERS"; //删除 /** * * @param sql 要执行的sql语句 * @param driver //驱动 * @param url //数据库url地址 * @param username //连接数据库用户名 * @param password //连接数据库密码 */ public static void del(String sql, String driver, String url, String username, String password) { Connection con = null; Statement stmt = null; try { //加载驱动程序 Class.forName(driver); } catch (ClassNotFoundException e) { e.printStackTrace(); } try { //连接MySQL数据库时，要写上连接的用户名和密码 con = DriverManager.getConnection(url, username, password); //实例化Statement对象 stmt = con.createStatement(); //执行数据库更新操作 stmt.executeUpdate(sql); } catch (SQLException e) { e.printStackTrace(); } System.out.println(con); try { //关闭操作 stmt.close(); //关闭数据库 con.close(); } catch (SQLException e) { e.printStackTrace(); } } /** * * @param sql 要执行的sql语句 * @param driver //驱动 * @param url //数据库url地址 * @param username //连接数据库用户名 * @param password //连接数据库密码 * @param cols //共查询字段数 * @return //返回查询到的结果 */ public static List<Map<String,Object>> query(String sql, String driver, String url, String username, String password,int cols) { Connection con = null; Statement stmt = null; ResultSet rs = null; //数据库查询语句 List<Map<String,Object>> l=new ArrayList<>(); try { //加载驱动程序 Class.forName(driver); } catch (ClassNotFoundException e) { e.printStackTrace(); } try { //连接MySQL数据库时，要写上连接的用户名和密码 con = DriverManager.getConnection(url, username, password); //实例化Statement对象 stmt = con.createStatement(); //执行数据库查询操作 rs = stmt.executeQuery(sql); while (rs.next()) { Map<String,Object> m=new HashMap<>(); for(int i=1;i<=cols;i++){ m.put("r"+i,rs.getString(i)); } l.add(m); /*String id = rs.getString("id"); String name = rs.getString(2); String age = rs.getString("age"); System.out.print("id:" + id + " "); System.out.print("name:" + name + " "); System.out.println("age:" + age);*/ } } catch (SQLException e) { e.printStackTrace(); } System.out.println(con); try { //关闭结果集 rs.close(); //关闭操作 stmt.close(); //关闭数据库 con.close(); } catch (SQLException e) { e.printStackTrace(); } return l; } /** * * @param sql 要执行的sql语句 * @param driver //驱动 * @param url //数据库url地址 * @param username //连接数据库用户名 * @param password //连接数据库密码 * @param cols //要添加的字段数 * @param list //要添加的list */ public static void add(String sql, String driver, String url, String username, String password,int cols,List<Map<String,Object>> list) { Connection conn = null; PreparedStatement pstm =null; /*//数据库插入语句 String insertSQL = "insert into user (id, name, age) values (3, 'key', 23)"; //数据库修改语句 String alterSQL = "update user SET name='jon' where id=8"; //数据库删除语句 String deleteSQL = "delete from user where id=5";*/ try { //加载驱动程序 Class.forName(driver); } catch (ClassNotFoundException e) { e.printStackTrace(); } try { //连接MySQL数据库时，要写上连接的用户名和密码 conn = DriverManager.getConnection(url, username, password); //实例化Statement对象 pstm = conn.prepareStatement(sql); Long startTime = System.currentTimeMillis(); conn.setAutoCommit(false); for (int i = 0; i < list.size(); i++) { for(int j=1;j<=cols;j++){ pstm.setObject(j, list.get(i).get("r"+j)); } pstm.addBatch(); } pstm.executeBatch(); conn.commit(); Long endTime = System.currentTimeMillis(); System.out.println("用时：" + (endTime - startTime)); } catch (SQLException e) { e.printStackTrace(); } System.out.println(conn); try { //关闭操作 pstm.close(); //关闭数据库 conn.close(); } catch (SQLException e) { e.printStackTrace