如题
我现在有两个服务器 A和B 我现在要把A服务器上的某一个库里面的所有的表及结构及数据 导入另外一个服务器上,实现原理利用原始JDBC 完成
代码类:
回家再撸 目前在公司 。。 下班 哈哈 晚上吃了点饭 就回来晚了 继续写
首先JDBC连接类
package com.gpdata.ic.usermanagement.admin.datasource.insert; import java.sql.*; /** * Created by qws on 2017/5/26/026. */ public class ConnectionDateBases { /** * 资源服务器连接 **/ private static String targetUrl = UtilConfig.targetUrl; /** * 本地服务器连接 **/ private static String nativeUrl = UtilConfig.nativeUrl; /** * 用户名 */ private static String userName = "root"; /** * 密码 */ private static String password = "root"; /** * 连接 */ private static String driver = "com.mysql.jdbc.Driver"; public Connection getNativeConnection() { Connection conn = null; try { Class.forName(driver); conn = DriverManager.getConnection(nativeUrl, userName, password); } catch (Exception e) { e.printStackTrace(); } return conn; } public Connection getSourceConnection() { Connection conn = null; try { Class.forName(driver); conn = DriverManager.getConnection(targetUrl, userName, password); } catch (Exception e) { e.printStackTrace(); } return conn; } /** * 释放连接 * * @param conn */ private static void freeConnection(Connection conn) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } /** * 释放statement * * @param statement */ private static void freeStatement(Statement statement) { try { statement.close(); } catch (SQLException e) { e.printStackTrace(); } } /** * 释放statement * * @param statement */ private static void freePreStatement(PreparedStatement statement) { try { statement.close(); } catch (SQLException e) { e.printStackTrace(); } } /** * 释放resultset * * @param rs */ private static void freeResultSet(ResultSet rs) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } /** * 释放资源 * * @param conn * @param statement * @param rs */ public static void free(Connection conn, PreparedStatement preparedStatement, Statement statement, ResultSet rs) { if (rs != null) { freeResultSet(rs); } if (preparedStatement != null) { freePreStatement(preparedStatement); } if (statement != null) { freeStatement(statement); } if (conn != null) { freeConnection(conn); } } }连接config
package com.gpdata.ic.usermanagement.admin.datasource.insert;
/**
* Created by qws on 2017/5/27/027.
*/
public class UtilConfig {
/**
* 定义数据库前缀
**/
final static String pre_dataBase = "gp_clone";
/**
* 定义数据表前缀
**/
final static String pre_tableName = "gp_";
/**
* 定义来源数据库链接
**/
final static String targetUrl = "jdbc:mysql://127.0.0.1:3306/uuuu";
/**
* 定义本地数据库链接
**/
final static String nativeUrl = "jdbc:mysql://127.0.0.1:3306/uuuu";
}
JDBC里面的配置 是从这里拿的
Dao 层
数据库的一些操作 里面有详细描述
package com.gpdata.ic.usermanagement.admin.datasource.insert; import com.gpdata.ic.usermanagement.admin.entity.Tablestructure; import java.sql.*; import java.util.ArrayList; import java.util.List; /** * Created by qws on 2017/5/26/026. */ public class SyDateDao { ConnectionDateBases dataBase = new ConnectionDateBases(); /** * 获得数据库名->以后用到 **/ public List getDateBaseDao(Connection con) { PreparedStatement ptst = null; ResultSet rs = null; List<String> datename = new ArrayList(); // 数据库名 String sql = "show databases"; try { ptst = con.prepareStatement(sql); rs = ptst.executeQuery(); while (rs.next()) { datename.add(rs.getString("Database")); } dataBase.free(con, ptst, null, rs); } catch (SQLException e) { e.printStackTrace(); } return datename; } /** * 生成数据库名字 **/ public void createDataBase(Connection con, String sourcedataname) { PreparedStatement ptst = null; String sql = new SqlUtil().createDataBase(sourcedataname); try { ptst = con.prepareStatement(sql); ptst.execute(); dataBase.free(null, ptst, null, null); } catch (SQLException e) { e.printStackTrace(); } } /** * 获得连接资源的某表 **/ public List getTableDao(Connection con, String dataname) { PreparedStatement ptst = null; ResultSet rs = null; List<String> Table = new ArrayList(); String sql = "SELECT TABLE_NAME FROM information_schema.tables t WHERE t.table_schema = '" + dataname + "'"; try { ptst = con.prepareStatement(sql); rs = ptst.executeQuery(); while (rs.next()) { Table.add(rs.getString("TABLE_NAME")); } dataBase.free(null, ptst, null, rs); } catch (SQLException e) { e.printStackTrace(); } return Table; } /** * 获得资源库某表的条数 **/ public List getTableCount(Connection con, String table, String fromDate, String toDate) { PreparedStatement ptst = null; ResultSet rs; List count = new ArrayList(); //数量 try { String sql = "select count(1) from " + table; if (fromDate != null && toDate != null) { sql = "select count(1) from " + table + " where createtime > ' " + fromDate + " ' and createtime < ' " + toDate + " ' "; } ptst = con.prepareStatement(sql); rs = ptst.executeQuery(); while (rs.next()) { count.add(rs.getObject("count(1)")); } dataBase.free(null, ptst, null, rs); } catch (SQLException e) { return null; } return count; } /** * 查询某表所有字段 **/ public List Find_table_field(Connection con, String table) { PreparedStatement ptst = null; ResultSet rs; List field = new ArrayList(); //字段 String sql = "desc " + table; try { ptst = con.prepareStatement(sql); rs = ptst.executeQuery(); while (rs.next()) { field.add(rs.getObject("field")); } dataBase.free(null, ptst, null, rs); } catch (SQLException e) { e.printStackTrace(); } return field; } /** * 增加接入时间的字段 **/ public void alertTime(Connection con, String tableName) { PreparedStatement ptst = null; String sql = new SqlUtil().alertSql(tableName); try { ptst = con.prepareStatement(sql); ptst.executeUpdate(); dataBase.free(null, ptst, null, null); } catch (SQLException e) { e.printStackTrace(); } } /** * 全量清表 **/ public void trunTable(Connection con, String tableName) { PreparedStatement ptst = null; String sql = "TRUNCATE " + tableName; try { ptst = con.prepareStatement(sql); ptst.execute(); dataBase.free(null, ptst, null, null); } catch (SQLException e) { e.printStackTrace(); } } /** * 同步表数据 **/ public void launchSyncData(Connection coreConnection, Connection targetConn, String tableName, String fromdate, String todate, String nativeSource) { if (fromdate == null || todate == null) { this.trunTable(targetConn, UtilConfig.pre_tableName + tableName); } try { Statement coreStmt = coreConnection.createStatement(); //本地 List<String> field = new SyDateDao().Find_table_field(targetConn, nativeSource + "." + UtilConfig.pre_tableName + tableName); //有时间限制 List list = new SyDateDao().getTableCount(coreConnection, tableName, fromdate, todate); int size = Integer.parseInt(list.get(0).toString()); int a = field.size(); //19 int b = a; int c = 1; //默认全量pre语句 String preSql = new SqlUtil().preSql(tableName); if (fromdate != null && todate != null) { //增量 preSql = new SqlUtil().addPreSql(tableName); } PreparedStatement targetPstmt = targetConn.prepareStatement(preSql); //批处理 10 条处理 int page = size / 1000; for (int i = 0; i < page + 1; i++) { int size2 = i * 1000; String seleSql = "select * from " + tableName + " limit " + size2 + ",1000 "; if (fromdate != null && todate != null) { seleSql = "select * from " + tableName + " where createtime > ' " + fromdate + " ' and createtime < ' " + todate + " ' limit " + size2 + ",1000"; } ResultSet coreRs = coreStmt.executeQuery(seleSql); while (coreRs.next()) { targetPstmt.setObject(1, null); while (++c < b) { targetPstmt.setObject(c, coreRs.getObject(c)); } c = 1; targetPstmt.execute(); } coreRs.close(); } coreStmt.close(); targetPstmt.close(); } catch (SQLException e) { e.printStackTrace(); } } /** * by ysh 查询某库下的表所有字段 **/ public List<Tablestructure> listsql(Connection con, String databasename, String tablename) { PreparedStatement ptst = null; ResultSet rs; List<Tablestructure> field = new ArrayList(); //字段 final String sql = "describe " + databasename + "." + tablename + ""; try { ptst = con.prepareStatement(sql); rs = ptst.executeQuery(); while (rs.next()) { Tablestructure tablestructure = new Tablestructure(); tablestructure.setField(rs.getString("Field")); tablestructure.setType(rs.getString("type")); tablestructure.setNull(rs.getString("Null")); tablestructure.setKey(rs.getString("key")); tablestructure.setDefault(rs.getString("Default")); tablestructure.setExtra(rs.getString("Extra")); field.add(tablestructure); } dataBase.free(null, ptst, null, rs); } catch (SQLException e) { e.printStackTrace(); } return field; } /** * 切换用户进行 创建表结构 * **/ public void moveUse(Connection source,Connection con, String sourcebaseName,String tableName) { PreparedStatement move_ptst = null; PreparedStatement drop_ptst = null; PreparedStatement table_ptst = null; String move_sql = new SqlUtil().useSQL( UtilConfig.pre_dataBase+"_"+sourcebaseName); String drop_sql=new SqlUtil().Tableexist(UtilConfig.pre_tableName+tableName); String create_sql=new SqlUtil().allSql(source,sourcebaseName,tableName); try { move_ptst = con.prepareStatement(move_sql); move_ptst.execute(); drop_ptst = con.prepareStatement(drop_sql); drop_ptst.execute(); table_ptst = con.prepareStatement(create_sql); table_ptst.execute(); dataBase.free(null, table_ptst, null, null); drop_ptst.close(); move_ptst.close(); } catch (SQLException e) { e.printStackTrace(); } } }
数据用的批处理 1000一处理,, 毕竟不会线程 。。。 尴尬
拼接SQL的类
package com.gpdata.ic.usermanagement.admin.datasource.insert; import com.gpdata.ic.usermanagement.admin.entity.Tablestructure; import java.sql.Connection; import java.util.List; /** * Created by qws on 2017/5/26/026. */ public class SqlUtil { //本地资源库 Connection natiCon = new ConnectionDateBases().getNativeConnection(); //对方资源库 Connection sourceCon = new ConnectionDateBases().getSourceConnection(); /** * 生成增加接入时间的列名 **/ public String alertSql(String tableName) { StringBuffer sb = new StringBuffer(); sb.append("ALTER table " + tableName + " add gp_updatetime timestamp null"); return sb.toString(); } /** * 全量同步 * 本地结构跟源结构一致,查询本地结构加字段 * 生成预处理数据库插入语句 **/ public String preSql(String tableName) { String natiTable = UtilConfig.pre_tableName + tableName; List list = new SyDateDao().Find_table_field(sourceCon, tableName); StringBuffer sb = new StringBuffer(); sb.append(" REPLACE INTO " + natiTable + "("); for (int i = 0; i < list.size(); i++) { sb.append(list.get(i) + ","); } sb.append("gp_updatetime )values("); for (int i = 0; i < list.size(); i++) { sb.append("?,"); } sb.append("CURRENT_TIMESTAMP )"); return sb.toString(); } /** * 增量同步 * 本地结构跟源结构一致,查询本地结构加字段 * 生成预处理数据库插入语句 **/ public String addPreSql(String tableName) { String natiTable = UtilConfig.pre_tableName + tableName; List list = new SyDateDao().Find_table_field(sourceCon, tableName); StringBuffer sb = new StringBuffer(); sb.append("INSERT INTO " + natiTable + "("); for (int i = 0; i < list.size(); i++) { sb.append(list.get(i) + ","); } sb.append("gp_updatetime )values("); for (int i = 0; i < list.size(); i++) { sb.append("?,"); } sb.append("CURRENT_TIMESTAMP )"); return sb.toString(); } /** * 拿到另一个库中额中的表字段 by ysh **/ public String allSql(Connection con, String databasename, String tablename) { List<Tablestructure> list = new SyDateDao().listsql(con, databasename, tablename); String alllsitsql = ""; for (Tablestructure tablestructure : list) { alllsitsql += "`" + tablestructure.getField() + "`" + " " + tablestructure.getType() + " "; if (tablestructure.getNull().equals("NO")) { alllsitsql += "NOT NULL" + " "; } if (tablestructure.getExtra().equals("auto_increment") && tablestructure.getKey().equals("PRI")) { alllsitsql += "AUTO_INCREMENT" + ","; } if (tablestructure.getDefault() == null && tablestructure.getNull().trim().equals("YES")) { alllsitsql += "DEFAULT NULL " + " "; } if (tablestructure.getKey().trim().contains("PRI")) { alllsitsql += " PRIMARY KEY (`" + tablestructure.getField() + "`)"; } alllsitsql += ","; } String allString = alllsitsql.substring(0, alllsitsql.length() - 1); String sql2 = "CREATE TABLE "+ UtilConfig.pre_tableName+tablename + " (" + allString + " ) DEFAULT CHARSET=utf8"; return sql2; } /** * 创建数据库 前缀一gp_clone命名 **/ public String createDataBase(String dataBaseName) { return "create database " + UtilConfig.pre_dataBase + "_" + dataBaseName; } /** * 验证表是否存在 by ysh **/ public String Tableexist( String tablename) { String sql1 = " DROP TABLE IF EXISTS " + tablename + ";"; return sql1; } public String useSQL(String nativeName) { return "use " + nativeName + " ; "; } }
这个 拼接表结构的实体类。。。。 写的不好 哈哈 不是我写的 (*^__^*) 嘻嘻……
package com.gpdata.ic.usermanagement.admin.entity; /** * Created by yushuanghong on 2017/5/27. */ public class Tablestructure { private String Field; private String type; private String Null; private String key; private String Default; private String Extra; public String getField() { return Field; } public void setField(String field) { Field = field; } public String getType() { return type; } public void setType(String type) { this.type = type; } public String getNull() { return Null; } public void setNull(String aNull) { Null = aNull; } public String getKey() { return key; } public void setKey(String key) { this.key = key; } public String getDefault() { return Default; } public void setDefault(String aDefault) { Default = aDefault; } public String getExtra() { return Extra; } public void setExtra(String extra) { Extra = extra; } }测试类、、
package com.gpdata.ic.usermanagement.admin.datasource.insert; import java.sql.Connection; import java.sql.SQLException; import java.util.List; /** * Created by qws on 2017/5/26/026. */ public class ExecuteSql { /** * 同步全部数据 * * @param fromdate :大于这个时间 * @param todate :小于这个时间 * @param sourcedataName :资源数据库名称 * **/ public int executeSql(String fromdate, String todate, String sourcedataName) { //本地数据库名字 String NativeDataBase= UtilConfig.pre_dataBase+"_"+sourcedataName; Connection natiCon = new ConnectionDateBases().getNativeConnection(); Connection sourceCon = new ConnectionDateBases().getSourceConnection(); SyDateDao dao = new SyDateDao(); List nameList = dao.getTableDao(sourceCon, sourcedataName); //创建数据库 new SyDateDao().createDataBase(natiCon,sourcedataName); for (int i = 0; i < nameList.size(); i++) { String tableName = nameList.get(i).toString(); //创建表结构 new SyDateDao().moveUse(sourceCon,natiCon,sourcedataName,tableName); //多加一个时间字段 dao.alertTime(natiCon, UtilConfig.pre_tableName+tableName); //同步数据 dao.launchSyncData(sourceCon, natiCon, tableName, fromdate, todate,NativeDataBase); } if (sourceCon != null) { try { sourceCon.close(); } catch (SQLException e) { e.printStackTrace(); } } if (natiCon != null) { try { natiCon.close(); } catch (SQLException e) { e.printStackTrace(); } } return 0; } public static void main(String[] args) { String fromdate = "2017-05-04 14:34:18"; String todate = "2017-05-27 10:16:44"; new ExecuteSql().executeSql(fromdate, todate, "uuuu"); } }噢哦 对了还有表结构 我这个表结构 以及同步数据 是根据特定的表结构写的。。。 什么主键只有ID 没有主外键关联啊。。。 还有主键自增啊 数据库结构比较简单些的 具体复杂的话 可以再改里面同步数据的方法。。。 都是java基础知识写的。。
表结构
/*
Navicat MySQL Data Transfer
Source Server : localhost_3306
Source Server Version : 50022
Source Host : 127.0.0.1:3306
Source Database : uuuu
Target Server Type : MYSQL
Target Server Version : 50022
File Encoding : 65001
Date: 2017-05-27 21:50:30
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for data_connect
-- ----------------------------
DROP TABLE IF EXISTS `data_connect`;
CREATE TABLE `data_connect` (
`id` bigint(50) NOT NULL auto_increment COMMENT '接入列表id',
`conDBName` varchar(500) default NULL COMMENT '接入数据库',
`conTabName` varchar(500) default NULL COMMENT '表名',
`numIncre` bigint(255) default NULL COMMENT '新增数据量',
`numAll` bigint(255) default NULL COMMENT '总数据量',
`conType` varchar(500) default NULL COMMENT '类别',
`conStatus` bigint(50) default NULL COMMENT '接入状态0:暂停 1:正常接入',
`remark` longtext COMMENT '备注',
`userID` varchar(500) default NULL COMMENT '操作人ID',
`userName` varchar(255) default NULL COMMENT '操作人名字',
`taskStart` datetime default NULL COMMENT '任务起始周期',
`taskEnd` datetime default NULL COMMENT '任务结束周期',
`taskCycle` varchar(50) default NULL COMMENT '执行周期',
`taskTime` datetime default NULL COMMENT '执行时间',
`taskLocal` varchar(500) default NULL COMMENT '任务位置',
`taskId` bigint(50) default NULL COMMENT '对应的任务ID',
`createTime` datetime default NULL COMMENT '创建时间',
`updateTime` datetime default NULL COMMENT '修改时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Table structure for data_connect_copy
-- ----------------------------
DROP TABLE IF EXISTS `data_connect_copy`;
CREATE TABLE `data_connect_copy` (
`id` bigint(50) NOT NULL auto_increment COMMENT '接入列表id',
`conDBName` varchar(500) default NULL COMMENT '接入数据库',
`conTabName` varchar(500) default NULL COMMENT '表名',
`numIncre` bigint(255) default NULL COMMENT '新增数据量',
`numAll` bigint(255) default NULL COMMENT '总数据量',
`conType` varchar(500) default NULL COMMENT '类别',
`conStatus` bigint(50) default NULL COMMENT '接入状态0:暂停 1:正常接入',
`remark` longtext COMMENT '备注',
`userID` varchar(500) default NULL COMMENT '操作人ID',
`userName` varchar(255) default NULL COMMENT '操作人名字',
`taskStart` datetime default NULL COMMENT '任务起始周期',
`taskEnd` datetime default NULL COMMENT '任务结束周期',
`taskCycle` varchar(50) default NULL COMMENT '执行周期',
`taskTime` datetime default NULL COMMENT '执行时间',
`taskLocal` varchar(500) default NULL COMMENT '任务位置',
`taskId` bigint(50) default NULL COMMENT '对应的任务ID',
`createTime` datetime default NULL COMMENT '创建时间',
`updateTime` datetime default NULL COMMENT '修改时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
具体源码就这样了。