2023年6月26日发(作者：)

java实现同步sqlserver中的表数据到mysql的⽅案场景项⽬框架情况： SpringMVC + MybatisPlus + 需求： 从sqlserver数据库中同步产品和产品类别两个表的数据。

思路1、利⽤ java⾃带的tion 与sqlserver数据库建⽴连接。2、通过Apache的dbutils⼯具类QueryRunner（ unner）来获取sqlserver中的对应数据。3、分析转化上述数据⾄我们需要的数据。4、通过本地项⽬中的mysql数据源（或者步骤1中同样的⽅式），与msyql建⽴连接。5、批量插⼊、更新mysql中的数据。

代码1、数据源连接⼯具类DbConnUtilpackage ;import ceUtil;import tion;import Manager;public class DbConnUtil { private static String sqlserverDriverClassName = figByName("verDriverClassName"); private static String sqlserverDbUrl = figByName("verDbUrl"); private static String sqlserverDbUser = figByName("verDbUser"); private static String sqlserverDbPwd = figByName("verDbPwd"); /** * 从这⾥取数据 * * @return */ public static Connection createConnSrc() { Connection conn = null; try { e(sqlserverDriverClassName).newInstance(); conn = nection(sqlserverDbUrl, sqlserverDbUser, sqlserverDbPwd); } catch (Exception e) { tackTrace(); } return conn; } /** * 存⼊的⽬标数据库 * * @return */ public static Connection createConnDist() { Connection conn = null; try { String driverClassName = ""; String dbUrl = "jdbc:mysql://192.168.1.110:3306/haoyuan?useUnicode=true&characterEncoding=UTF-8"; String dbUser = "root"; String dbPwd = "1234567890"; e(driverClassName).newInstance(); conn = nection(dbUrl, dbUser, dbPwd); } catch (Exception e) { tackTrace(); } return conn; } public static void close(Connection conn) { try { (); } catch (Exception e) { tackTrace(); } }}2、Apache dbutils QueryRunner数据操作⼯具类的封装类DbBuilder

package tabase;import Util;import unner;import ndler;import stHandler;import dler;import tHandler;import tion;import ;import ;public class DbBuilder { Connection connection = null; public DbBuilder(Connection conn) { tion = conn; } public QueryRunner getQueryRunner() { public QueryRunner getQueryRunner() { return new QueryRunner(); } /** * 根据传⼊的sql，查询记录，以Map形式返回第⼀⾏记录 注意：如果有多⾏记录，只会返回第⼀⾏，所以使⽤场景需要注意，可以使⽤根据主键来查询的场景 * * @param sql * @param params * @return */ public Map getFirstRowMap(String sql, params) { try { QueryRunner runner = new QueryRunner(); return (connection, sql, new MapHandler(), params); } catch (Exception ex) { tackTrace(); } return null; } /** * 根据传⼊的sql查询数据，以List Map形式返回 * * @param sql * @param params * @return */ public List> getListMap(String sql, params) { try { QueryRunner runner = new QueryRunner(); return (connection, sql, new MapListHandler(), params); } catch (Exception ex) { tackTrace(); } return null; } /** * 根据sql和对象，查询结果并以对象形式返回 * * @param sql * @param type * @return */ public T getBean(String sql, Class type) { try { QueryRunner runner = getQueryRunner(); return (sql, new BeanHandler(type)); } catch (Exception ex) { tackTrace(); } return null; } /** * 根据sql和对象，查询结果并以对象形式返回 * * @param sql * @param type * @return */ public T getBean(Connection connection, String sql, Class type) { try { QueryRunner runner = new QueryRunner(); QueryRunner runner = new QueryRunner(); return (connection, sql, new BeanHandler(type)); } catch (Exception ex) { tackTrace(); } return null; } /** * 根据sql和对象，查询结果并以对象形式返回 * * @param sql * @param type * @param params * @return */ public T getBean(Connection connection, String sql, Class type, params) { try { QueryRunner runner = new QueryRunner(); return (connection, sql, new BeanHandler(type), params); } catch (Exception ex) { tackTrace(); } return null; } /** * 根据sql查询list对象 * * @param sql * @param type * @return */ public List getListBean(Connection connection, String sql, Class type) { try { QueryRunner runner = new QueryRunner(); return (connection, sql, new BeanListHandler(type)); } catch (Exception ex) { tackTrace(); } return null; } /** * 根据sql查询list对象 * * @param sql * @param type * @param params * @return */ public List getListBean(String sql, Class type, params) { try { QueryRunner runner = getQueryRunner(); return (sql, new BeanListHandler(type), params); } catch (Exception ex) { tackTrace(); } return null; } /** * 根据sql查询list对象 * * * @param sql * @param type * @param params * @return */ public List getListBean(Connection connection, String sql, Class type, params) { try { QueryRunner runner = new QueryRunner(); return (connection, sql, new BeanListHandler(type), params); } catch (Exception ex) { tackTrace(); } return null; } /** * 保存操作 * * @param sql * @param params * @return */ public int save(String sql, params) { try { QueryRunner runner = getQueryRunner(); return (connection, sql, params); } catch (Exception ex) { tackTrace(); } return 0; } /** * 保存操作 * * @param sql * @param params * @return */ public int save(Connection connection, String sql, params) { try { QueryRunner runner = new QueryRunner(); return (connection, sql, params); } catch (Exception ex) { tackTrace(); } return 0; } /** * 更新操作 * * @param sql * @param params * @return */ public int update(String sql, params) { try { QueryRunner runner = getQueryRunner(); return (sql, params); } catch (Exception ex) { tackTrace(); } return 0; } } /** * 更新操作 * * @param sql * @param params * @return */ public int update(Connection connection, String sql, params) { try { QueryRunner runner = new QueryRunner(); return (connection, sql, params); } catch (Exception ex) { tackTrace(); } return 0; } /** * 删除操作 * * @param sql * @param params * @return */ public int delete(String sql, params) { try { QueryRunner runner = getQueryRunner(); return (sql, params); } catch (Exception ex) { tackTrace(); } return 0; } /** * 删除操作 * * @param sql * @param params * @return */ public int delete(Connection connection, String sql, params) { try { QueryRunner runner = new QueryRunner(); return (connection, sql, params); } catch (Exception ex) { tackTrace(); } return 0; } /** * 批量操作，包括批量保存、修改、删、 * * * @param sql * @param params * @return */ public int[] batch(String sql, Object[][] params) { try { QueryRunner runner = getQueryRunner(); return (sql, params); } catch (Exception ex) { tackTrace(); tackTrace(); } return null; } /** * 批量操作，包括批量保存、修改、删处 * * * @param sql * @param params * @return */ public int[] batch(Connection connection, String sql, Object[][] params) { try { QueryRunner runner = new QueryRunner(); return (connection, sql, params); } catch (Exception ex) { tackTrace(); } return null; } /** * �?��事务 */ public void beginTransaction(Connection conn) { try { oCommit(false); } catch (Exception e) { throw new RuntimeException(e); } } /** * 回滚事务 */ public void rollback(Connection conn) { try { ck(); } catch (Exception e) { throw new RuntimeException(e); } } /** * 提交事务 */ public void commit(Connection conn) { try { (); } catch (Exception e) { throw new RuntimeException(e); } } public static void main(String[] args) { try { Connection conn = ConnDist(); DbBuilder b = new DbBuilder(conn); n(("insert into demo_test(id,kitchen) values(?,?)", "11", "11")); (conn); } catch (Exception e) { tackTrace(); } } }}3、数据同步类DoDataSycn，以下同步中对mysql中已存在的数据进⾏更新时可以先与sqlserver中的数据对⽐版本变化，只更新有变化的数据即可。此处由于sqlserver数据库的数据版本信息不可⽤所以取消了对⽐，⽽是全量更新。package tabase;import ryEntity;import ntity;import ryService;import ervice;import Util;import 4j;import Utils;import red;import ent;import imal;import tion;import .*;@Slf4jpublic class DoDataSycn { @Autowired CategoryService categoryService; @Autowired GoodsService goodsService; /** * 更新品类 */ public void updateCategory() { //打开链接 Connection connSqlserver = ConnSrc(); DbBuilder sqlserver = new DbBuilder(connSqlserver); try { //DO //DO 1、遍历mysql数据。添加map id为key //DO 2、遍历sqlserver数据获取新增数据添加插⼊map，获取修改过的数据添加update map //DO 3、实现批量更新功能 String sql = "Select * from t_Item WHERE FItemClassID = 4"; List> remoteList = tMap(sql, null);//获取远程⾦蝶数据 if (remoteList != null && !y()) { List insertList = new ArrayList<>(); List updateList = new ArrayList<>(); //查询mysql现有品类 List mysqlCategoryList = ist(new HashMap<>()); if (mysqlCategoryList != null && !y()) {//已存在数据时，对⽐更新 //1、现有数据分析整理 Map mysqlCategoryMap = new HashMap<>(); for (CategoryEntity category : mysqlCategoryList) { (() + "", category); } //获取现有id Set ids = (); ("() " + ()); //2、开始对⽐数据 //遍历⾦蝶数据 for (Map item : remoteList) { try { String id = ("FItemID") + ""; if (ns(id)) {//已存在的品类 更新 (copyCategoryInfo(item, (id))); } else {//新增的品类 (copyCategoryInfo(item, new CategoryEntity())); } } catch (Exception e) { tackTrace(); } } } else {//初始化全量插⼊ for (Map item : remoteList) { try { (copyCategoryInfo(item, new CategoryEntity())); } catch (Exception e) { tackTrace(); } } } try { if (!y()) { (("saveBatch category " + ())); tch(insertList); } } catch (Exception e) { tackTrace(); } try { if (!y()) { (("batchUpdate category " + ())); pdate(updateList); } } catch (Exception e) { tackTrace(); } } } catch (Exception e) { tackTrace(); } finally { //关闭 (connSqlserver); } } /** * 更新品类信息 * * @param item * @param categoryEntity * @return */ private CategoryEntity copyCategoryInfo(Map item, CategoryEntity categoryEntity) { (nt(("FItemID") + "")); e(("FName") + ""); entId(nt(("FParentID") + "")); tOrder(nt(("FShortNumber") + "")); String fDeleted = ("FDeleted") + ""; if (lank(fDeleted) && "1".equals(fDeleted)) { how(0); } else { how(1); } el(("FLevel") + ""); vel(nt(("FLevel") + "")); lnumber(("FFullNumber") + ""); lname(("FFullName") + ""); d(("UUID") + ""); //拼接ModifyTime byte[] fModifyTimes = (byte[]) ("FModifyTime"); StringBuilder modifyTime = new StringBuilder(); for (byte b : fModifyTimes) { (b); } ifyTime(ng());//添加更新版本信息 ood((boolean) ("FDetail") ? "1" : "0"); return categoryEntity; } /** * 更新产品 */ public void updateGoods() { //打开链接 Connection connSqlserver = ConnSrc(); DbBuilder sqlserver = new DbBuilder(connSqlserver); try { //DO //DO 1、遍历mysql数据。添加map id为key //DO 2、遍历sqlserver数据获取新增数据添加插⼊map，获取修改过的数据添加update map //DO 3、实现批量更新功能 String sql = "SELECT" + " D," + " ," + " tID," + " Number," + " ed," + " ," + " umber," + " ame," + " ," + " yTime," + " Price," + " Price," + " Price," + " " + "FROM " + " t_item i" + " LEFT JOIN t_icitemcore c ON D = D " + "WHERE " + " lassID = 4 " + " AND l = 1 ;";// " AND l = 1 "+// " AND D = 291;"; List> remoteList = tMap(sql, null);//获取远程⾦蝶数据 if (remoteList != null && !y()) { List insertList = new ArrayList<>(); List updateList = new ArrayList<>(); //查询mysql现有产品 List mysqlGoodsList = llGoods(); if (mysqlGoodsList != null && !y()) {//已存在数据时，对⽐更新 //1、现有数据分析整理 Map mysqlGoodsMap = new HashMap<>(); for (GoodsEntity goods : mysqlGoodsList) { (() + "", goods); } //获取现有id //获取现有id Set ids = (); //2、开始对⽐数据 //遍历⾦蝶数据 for (Map item : remoteList) { try { String id = ("FItemID") + ""; if (ns(id)) {//已存在的品类 //更新 (copyGoodsInfo(item, (id))); } else {//新增的品类 (copyGoodsInfo(item, new GoodsEntity())); } } catch (Exception e) { tackTrace(); } } } else {//初始化全量插⼊ for (Map item : remoteList) { try { (copyGoodsInfo(item, new GoodsEntity())); } catch (Exception e) { tackTrace(); } } } try { if (!y()) { (("saveBatch goods " + ())); tch(insertList); } } catch (Exception e) { tackTrace(); } try { if (!y()) { (("batchUpdate goods " + ())); pdate(updateList); } } catch (Exception e) { tackTrace(); } } } catch (Exception e) { tackTrace(); } finally { //关闭 (connSqlserver); } } /** * 更新产品信息 * * @param item * @param goodsEntity * @return */ private GoodsEntity copyGoodsInfo(Map item, GoodsEntity goodsEntity) { (nt(("FItemID") + "")); (nt(("FItemID") + "")); e(("FName") + ""); egoryId(nt(("FParentID") + "")); tOrder(nt(("FShortNumber") + "")); String fDeleted = ("FDeleted") + ""; if (lank(fDeleted) && "1".equals(fDeleted)) { elete(0); } else { elete(1); } vel(nt(("FLevel") + "")); lnumber(("FFullNumber") + ""); lname(("FFullName") + ""); d(("UUID") + ""); //拼接ModifyTime byte[] fModifyTimes = (byte[]) ("FModifyTime"); StringBuilder modifyTime = new StringBuilder(); for (byte b : fModifyTimes) { (b); } ifyTime(ng());//添加更新版本信息 nterPrice(new BigDecimal(("FOrderPrice") + "")); ailPrice(new BigDecimal(("FOrderPrice") + "")); tPrice(new BigDecimal(("FOrderPrice") + "")); el(("FModel") + ""); return goodsEntity; }}4、项⽬properties配置⽂件中的sqlserver连接信⼼配置#sqlserver连接信息verDriverClassName = verDbUrl = jdbc:jtds:sqlserver://192.168.1.111:1433;DatabaseName=verDbUser = verDbPwd = 3