java多个数据库实现数据同步

package com.dhe.business; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import com.dhe.sourceDB.DatabaseSourceParamFactory; import com.dhe.sourceDB.GetSourceDB; import com.dhe.targetDB.GetTargetDB; public class Business extends javax.swing.JFrame implements Runnable { /** * */ private static final long serialVersionUID = -2814878419565328292L; private long inter; private Thread th; public Thread getTh() { return th; } public void setTh(Thread th) { this.th = th; } public long getInter() { return inter; } public void setInter(long inter) { this.inter = inter; } public Business(Thread th) { this.setInter(DatabaseSourceParamFactory.getInterval()); this.setTh(th); } public void run() { // 开始同步数据 operateSynchro(); } @SuppressWarnings("static-access") public void operateSynchro() { while (true) { // 调用同步用户的方法 startSynchroUser(); // 调用同步车辆的方法 startSynchroCar(); try { th.sleep(inter); } catch (InterruptedException e) { } operateSynchro(); } } // 同步用户的方法 同步用户的时候要判断该用户所在组中是否有车辆 如果有车辆 就同步 没有车辆就不同步 public void startSynchroUser() { Connection sourceConn = GetSourceDB.getConnection(); PreparedStatement sourcePs = null; ResultSet sourceRs = null; ResultSet tempSourceRs = null; ResultSet tempSourceCompanyRs = null; Connection targetConn = GetTargetDB.getConnection(); PreparedStatement targetPs = null; ResultSet targetRs = null; ResultSet targetCompanyRs = null; ResultSet targetCompanyKeyRs = null; ResultSet targetUserKeyRs = null; // 查找所有用户 String sourceSql = "select * from userInfo"; try { sourcePs = sourceConn.prepareStatement(sourceSql); sourceRs = sourcePs.executeQuery(); while (sourceRs.next()) { String tempLoginName = sourceRs.getString("UserName"); String tempPassword = sourceRs.getString("userPassword"); int tempLoginCount = 0; int tempLoginUserId = sourceRs.getInt("userID"); String targetSql = "select count(*) from dhe_user where nickname='" + tempLoginName + "' "; targetPs = targetConn.prepareStatement(targetSql); targetRs = targetPs.executeQuery(); int count = 0; if (targetRs.next()) { count = targetRs.getInt(1); } // 目标表中不存在该用户的时候 if (count <= 0) { sourceSql = "select count(*) from vehicleinfo v,( select d.deviceId from userInfo u,deviceGroupUnderUserGroup d where u.groupId = d.groupId and u.userId=" + tempLoginUserId + " ) k where v.deviceId = k.deviceId"; sourcePs = sourceConn.prepareStatement(sourceSql); tempSourceRs = sourcePs.executeQuery(); if (tempSourceRs.next()) { tempLoginCount = tempSourceRs.getInt(1); } // 该用户下有车辆则进行同步该用户 if (tempLoginCount > 0) { String tempCompanyName = ""; sourceSql = " select distinct(v.company) from vehicleinfo v,(select d.deviceId from userInfo u,deviceGroupUnderUserGroup d where u.groupId = d.groupId and u.userId=" + tempLoginUserId + ") k where v.deviceId = k.deviceId"; sourcePs = sourceConn.prepareStatement(sourceSql); tempSourceCompanyRs = sourcePs.executeQuery(); // 查询一个和该用户对应的单位名称 if (tempSourceCompanyRs.next()) { tempCompanyName = tempSourceCompanyRs .getString("company"); } // 如果查到单位名称 if (tempCompanyName != null && !"".equals(tempCompanyName)) { int tempCompanyId = -1; targetPs = targetConn .prepareStatement("select id from dhe_enterprise where name='" + tempCompanyName + "'"); targetCompanyRs = targetPs.executeQuery(); // 根据单位名称找到所对应的单位ID if (targetCompanyRs.next()) { tempCompanyId = targetCompanyRs.getInt("id"); } // 如果没查到所对应的单位ID 则需要插入一个单位并返回一个ID if (tempCompanyId == -1) { targetPs = targetConn .prepareStatement("insert into dhe_enterprise(name) values('" + tempCompanyName + "')"); targetPs.executeUpdate(); targetCompanyKeyRs = targetPs .getGeneratedKeys(); if (targetCompanyKeyRs.next()) { tempCompanyId = targetCompanyKeyRs .getInt(1); } } // 开始把用户插入到目标表中 targetSql = "insert into dhe_user(ischk,nickname,passwd,userType,enterpriseID,isValid) values(1,'" + tempLoginName + "','" + tempPassword + "',103," + tempCompanyId + ",1)"; targetPs = targetConn.prepareStatement(targetSql); targetPs.executeUpdate(); int tempUserId = -1; targetUserKeyRs = targetPs.getGeneratedKeys(); if (targetUserKeyRs.next()) { tempUserId = targetUserKeyRs.getInt(1); } // 插入默认的权限 for (int i = 0; i < 3; i++) { // 车辆状态实时监控 if (i == 0) { targetSql = "insert into dhe_user_module_relation(userId,moduleId,isVaild) values(" + tempUserId + ",20,0) "; } // 车辆状态历史轨迹 if (i == 1) { targetSql = "insert into dhe_user_module_relation(userId,moduleId,isVaild) values(" + tempUserId + ",51,0) "; } // 车辆一览 if (i == 2) { targetSql = "insert into dhe_user_module_relation(userId,moduleId,isVaild) values(" + tempUserId + ",41,0) "; } // 车辆轨迹回放 if (i == 2) { targetSql = "insert into dhe_user_module_relation(userId,moduleId,isVaild) values(" + tempUserId + ",70,0) "; } targetPs = targetConn .prepareStatement(targetSql); targetPs.executeUpdate(); } } } } } } catch (Exception e) { e.printStackTrace(); } finally { GetSourceDB.closeObject(sourceConn, sourcePs, sourceRs); GetTargetDB.closeObject(targetConn, targetPs, targetRs); try { if (tempSourceRs != null) { tempSourceRs.close(); } } catch (SQLException e) { e.printStackTrace(); } try { if (tempSourceCompanyRs != null) { tempSourceCompanyRs.close(); } } catch (SQLException e) { e.printStackTrace(); } try { if (targetCompanyRs != null) { targetCompanyRs.close(); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } try { if (targetUserKeyRs != null) { targetUserKeyRs.close(); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } try { if (targetCompanyKeyRs != null) { targetCompanyKeyRs.close(); } } catch (SQLException e) { e.printStackTrace(); } } } // 同步车辆的方法 public void startSynchroCar() { Connection sourceConn = GetSourceDB.getConnection(); PreparedStatement sourcePs = null; ResultSet sourceRs = null; Connection targetConn = GetTargetDB.getConnection(); PreparedStatement targetPs = null; ResultSet targetRs = null; ResultSet targetCompanyRs = null; ResultSet targetCompanyKeyRs = null; String sourceSql = ""; String targetSql = ""; try { sourceSql = "select plateNum ,company ,model from vehicleinfo"; sourcePs = sourceConn.prepareStatement(sourceSql); sourceRs = sourcePs.executeQuery(); while (sourceRs.next()) { String tempCarNo = sourceRs.getString("plateNum"); String tempCompanyName = sourceRs.getString("company"); String tempModel = sourceRs.getString("model"); // 判断目标数据库中有没该车辆 targetSql = "select count(*) from dhe_ca