基于JDBC同步Oracle到MySQL

package com.database.utils; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.sql.Statement; import java.sql.Timestamp; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.List; import org.apache.commons.lang3.StringUtils; import com.alibaba.fastjson.JSONArray; import com.alibaba.fastjson.JSONObject; /** * jdbc连接数据库封装工具 * * @auther zhuteng * @time 2019年7月28日 */ public class JdbcDataBaseUtils { private Connection connection = null;// 连接对象 private Statement pst = null;// 事务对象 private String drivername = null;// 驱动类型 private String url = null;// 请求地址 private String username = null;// 用户名 private String password = null;// 密码 private JSONObject commentMap = null;// 字段名注释 private JSONArray columnsInfosArray = null;// 字段名信息 private JSONObject tableCommentMap = null;// 表名注释 public JdbcDataBaseUtils() { super(); } /** * 连接初始化 * * @throws Exception * * @auther zhuteng * @time 2019年7月30日 */ public JdbcDataBaseUtils(String drivername, String url, String username, String password) throws Exception { this.drivername = drivername; this.url = url; this.username = username; this.password = password; } public JSONArray getColumnsInfosArray() throws Exception { if (this.columnsInfosArray == null) throw new Exception("数据为空，请确认是否已执行查询数据函数"); return columnsInfosArray; } public JSONObject getTableCommentMap() throws Exception { if (this.tableCommentMap == null) throw new Exception("数据为空，请确认是否已执行查询数据函数"); return tableCommentMap; } /** * 获取连接 * * @throws Exception * * @auther zhuteng * @time 2019年7月24日 */ private void connection() throws Exception { try { Class.forName(this.drivername);// 加载驱动 if (this.username == null) this.connection = DriverManager.getConnection(this.url); else this.connection = DriverManager.getConnection(this.url, this.username, this.password); } catch (Exception e) { throw e; } } /** * 加载数据库字段注释 * * @auther zhuteng * @time 2019年7月26日 */ public void getColumnCommentInfo(boolean isQueryAll) throws Exception { JSONObject jsonObject = new JSONObject();// 保存至JSON对象中 JSONArray columnsJsonArray = new JSONArray();// 保存至JSON对象中 try { StringBuffer sql = new StringBuffer();// sql sql.append("select a.table_name,a.column_name,b.comments,a.data_type,a.data_length,"); sql.append("a.nullable,a.data_default,a.low_value,a.high_value "); sql.append("from cols a "); sql.append("left join user_col_comments b "); sql.append("on (a.TABLE_NAME = b.table_name and a.COLUMN_NAME=b.column_name) "); sql.append("left join user_tab_comments c "); sql.append("on (a.TABLE_NAME=c.table_name) "); sql.append(!isQueryAll ? "where b.comments is not null " : ""); sql.append("order by a.TABLE_NAME,a.COLUMN_ID"); ResultSet result = getResultSet(sql.toString());// 查询结果 //遍历取数据 while (result.next()) { //字段需要临时取出放入数据栈中，数据取值只能按顺序依次取出，且只能取一次，否则将会抛出【java.sql.SQLException: 流已被关闭】 String table_name = result.getString(1);//表格名称 String column_name = result.getString(2);//列名 String comments = result.getString(3);//列注释 String data_type = result.getString(4);//数据类型 String data_length = result.getString(5);//字段长度 String nullable = result.getString(6);//是否为空 String data_default = result.getString(7);//默认值 String low_value = result.getString(8);//最低值 String high_value = result.getString(9);//最高值 jsonObject.put(column_name, comments);//字段注释Map JSONObject columnInfoObject = new JSONObject();//保存字段信息 columnInfoObject.put("table_name", table_name); columnInfoObject.put("column_name", column_name); columnInfoObject.put("comments", comments == null ? "" : comments); columnInfoObject.put("data_type", data_type == null ? "" : data_type); columnInfoObject.put("data_length", data_length == null ? "" : data_length); columnInfoObject.put("nullable", nullable == null ? "" : nullable); columnInfoObject.put("data_default", data_default == null ? "" : data_default); columnInfoObject.put("low_value", low_value == null ? "" : low_value); columnInfoObject.put("high_value", high_value == null ? "" : high_value); columnsJsonArray.add(columnInfoObject);//保存 } } catch (Exception e) { throw e; } finally { //关闭数据源 close(); } this.commentMap = jsonObject; this.columnsInfosArray = columnsJsonArray; } /** * 加载数据表名注释 * @author ZhuTeng * @Time 2019年11月7日 */ public void getTableCommentInfo() throws Exception { JSONObject jsonObject = new JSONObject();// 保存至JSON对象中 try { StringBuffer sql = new StringBuffer();// sql sql.append("SELECT A.TABLE_NAME, B.COMMENTS "); sql.append("FROM USER_TABLES A, USER_TAB_COMMENTS B "); sql.append("WHERE A.TABLE_NAME = B.TABLE_NAME "); sql.append("ORDER BY TABLE_NAME "); ResultSet result = getResultSet(sql.toString());// 查询结果 while (result.next()) { jsonObject.put(result.getString(1), result.getString(2) == null ? "" : result.getString(2)); } } catch (Exception e) { throw e; } finally { //关闭数据源 close(); } this.tableCommentMap = jsonObject; } /** * 根据对象信息获取DDL信息 * @author ZhuTeng * @Time 2019年12月8日 */ public String executeQueryTableDDLSqlForOracle(String object, String tablename) throws Exception { String ddlsql = ""; try { if (StringUtils.isEmpty(tablename)) { throw new Exception("数据表名为空！"); } StringBuffer sql = new StringBuffer(); sql.append("SELECT DBMS_METADATA.GET_DDL('").append(object).append("',"); sql.append("'").append(tablename.toUpperCase()).append("') AS ddlsql "); sql.append(" FROM DUAL "); // 执行查询 ResultSet result = getResultSet(sql.toString());// 查询结果;