近期公司做個報表系統，為了報表系統中復雜的查詢條件，不影響線上業務系統的使用，研究了一下MySQL數據庫同步，下面用Java代碼實現MySQL數據庫同步，以便自己查閱！

?

數據庫同步實現功能點：

1.支持跨服務器跨庫的多線程同步

2.每張表的同步有日志記錄

3.每次同步記錄數可配置

源碼和具體的使用細則，可以到下載源碼及使用說明請添加鏈接描述?。

?

一、數據同步核心代碼

package com.zrscsoft.synchtool.db;import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.HashMap; import java.util.List;import cn.com.dd.connector.db.ConnectionPoolManager;import com.synch.connector.DataBaseUtil; import com.synch.connector.DataSourceConnectDBVO; import com.synch.connector.TableFieldVO; import com.zrscsoft.synchtool.util.PropertyUtil; import com.zrscsoft.synchtool.util.StringUtil;public class SynchMain {public void addSynchLog(Connection bakconn, String synchtable,String synchsql, String syncherr, String synchtime) {PreparedStatement bakps = null;try {String bakInsertSQL = "insert into sys_synchlog (synchtable,synchsql,syncherr,synchtime,createtime) values ('"+ synchtable+ "','"+ synchsql+ "',?,'"+ synchtime + "',now())";bakps = bakconn.prepareStatement(bakInsertSQL);bakps.setString(1, syncherr);bakps.execute();bakconn.commit();} catch (Exception e) {e.printStackTrace();} finally {// 備庫連接釋放if (bakps != null) {try {bakps.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}}}public void synch(String synchtablenames) throws Exception {HashMap<String, String> synProperty = PropertyUtil.getAllMessage();// 數據庫連接參數DataSourceConnectDBVO maindb = new DataSourceConnectDBVO();maindb.setDataSourceType(0);maindb.setDataSourceName("maindb"+synchtablenames);maindb.setAccessMethod(0);maindb.setDataSourceAddress(synProperty.get("maindb.address"));maindb.setDataSourcePort(Integer.parseInt(synProperty.get("maindb.port")));maindb.setDatabaseName(synProperty.get("maindb.databasename"));maindb.setAccountName(synProperty.get("maindb.accountname"));maindb.setAccountPwd(synProperty.get("maindb.accountpwd"));DataSourceConnectDBVO bakdb = new DataSourceConnectDBVO();bakdb.setDataSourceType(0);bakdb.setDataSourceName("bakdb"+synchtablenames);bakdb.setAccessMethod(0);bakdb.setDataSourceAddress(synProperty.get("bakdb.address"));bakdb.setDataSourcePort(Integer.parseInt(synProperty.get("bakdb.port")));bakdb.setDatabaseName(synProperty.get("bakdb.databasename"));bakdb.setAccountName(synProperty.get("bakdb.accountname"));bakdb.setAccountPwd(synProperty.get("bakdb.accountpwd"));int currows = Integer.parseInt(synProperty.get("synchrows"));// 每次同步的記錄數// 同步表名.String synchtablenamestmp=synProperty.get(synchtablenames);if(synchtablenamestmp==null||"".equals(synchtablenamestmp)){return;}String[] tableNames = synchtablenamestmp.split(",");for (int t = 0; t < tableNames.length; t++) {String tableName = tableNames[t];// 獲取同步字段名List<TableFieldVO> list = DataBaseUtil.getFieldsForTable(tableName,maindb);String fields = "`"+list.get(0).getColumnName()+"`";String values = "?";String idfield="";int paraCount = list.size();for (int i = 1; i < paraCount; i++) {String columnName=list.get(i).getColumnName();fields = fields + ",`" + columnName+"`";values = values + ",?";if("id".equals(columnName.toLowerCase())){idfield=columnName;}}Connection mainconn = null;PreparedStatement mainps = null;ResultSet mainrs = null;Connection bakconn = null;PreparedStatement bakps = null;int curpage = 1;// 第幾頁,默認顯示第1頁long startTime = System.currentTimeMillis();String mainQuerySQL = "";String bakInsertSQL ="";while (true) {try {String orderby="".equals(idfield)?"":" order by "+idfield+" ";String limit = orderby+" limit " + (curpage - 1) * currows + ","+ currows;mainQuerySQL = "select * from " + tableName + limit;bakInsertSQL = "insert ignore into " + tableName + " ("+ fields + ") values (" + values + ")";// 主庫mainconn = DataBaseUtil.getMySQLConnect(maindb);mainps = mainconn.prepareStatement(mainQuerySQL);mainrs = mainps.executeQuery();// 被庫bakconn = DataBaseUtil.getMySQLConnect(bakdb);bakps = bakconn.prepareStatement(bakInsertSQL);int querySize = 0;while (mainrs.next()) {querySize++;for (int i = 0; i < paraCount; i++) {bakps.setString(i + 1, mainrs.getString(list.get(i).getColumnName()));}bakps.addBatch();}if (querySize == 0) {break;}curpage++;bakps.executeBatch();bakconn.commit();this.addSynchLog(bakconn, tableName, mainQuerySQL, "",(System.currentTimeMillis() - startTime) + "");} catch (Exception e) {e.printStackTrace();this.addSynchLog(bakconn, tableName, mainQuerySQL,StringUtil.getExceptionAllinformation(e),(System.currentTimeMillis() - startTime) + "");break;} finally {// 主庫連接釋放if (mainrs != null) {try {mainrs.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}if (mainps != null) {try {mainps.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}if (mainconn != null) {ConnectionPoolManager.getInstance().release(mainconn);}// 備庫連接釋放if (bakps != null) {try {bakps.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}if (bakconn != null) {ConnectionPoolManager.getInstance().release(bakconn);}}}System.out.println(tableNames.length + "/" + t + "表" + tableName+ "耗時：" + (System.currentTimeMillis() - startTime) + "毫秒");}}public static void main(String[] args) throws Exception {new SynchThread("synchtablenames1").start();new SynchThread("synchtablenames2").start();new SynchThread("synchtablenames3").start();}}

二、數據庫同步多線程實現

package com.zrscsoft.synchtool.db;public class SynchThread extends Thread {private String synchtablenames;public SynchThread(String synchtablenames) {this.synchtablenames = synchtablenames;}public void run() {if(synchtablenames==null||"".equals(synchtablenames)){return;}SynchMain main = new SynchMain();try {main.synch(synchtablenames);} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();}}}

三、配置文件及讀取配置文件代碼

配置文件內容為：

#主庫 maindb.address=localhost maindb.port=3306 maindb.databasename=mysqlmain maindb.accountname=root maindb.accountpwd=root #備庫 bakdb.address=localhost bakdb.port=3306 bakdb.databasename=mysqlbak bakdb.accountname=root bakdb.accountpwd=root #同步表名，多張表以,分開 synchtablenames1=sys_user1 synchtablenames2=sys_user2 synchtablenames3=sys_user3 #每次同步記錄數 synchrows=10000

讀取配置文件的Java類為：

package com.zrscsoft.synchtool.util;import java.util.Enumeration; import java.util.HashMap; import java.util.ResourceBundle;public class PropertyUtil {public static HashMap<String, String> getAllMessage() {String propertyName = "synch";// 獲得資源包ResourceBundle rb = ResourceBundle.getBundle(propertyName.trim());// 通過資源包拿到所有的keyEnumeration<String> allKey = rb.getKeys();// 遍歷key 得到 valueHashMap<String, String> valList = new HashMap<String, String>();while (allKey.hasMoreElements()) {String key = allKey.nextElement();String value = (String) rb.getString(key);valList.put(key, value);}return valList;} }

運行方式為：執行SynchMain.java類中的main()方法。

源碼和具體的使用細則，可以到下載源碼及使用說明請添加鏈接描述?。

轉載于:https://blog.51cto.com/1681229/2370801

總結

以上是生活随笔為你收集整理的MySQL数据库同步小工具（Java实现）的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。