利用JDBC连接实现跨服务器跨数据库跨表数据传输

  如题

 我现在有两个服务器 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;

具体源码就这样了。