记用JDBC合并数据库表数据工具问题解决

实现过程(要合并的两个表结构要一致)

首先获取两个数据库连接

/**
     * 获取数据库链接
     */
    private String getSqlServiceConnIn(LhdbCfgConn lhdbCfgConn) {
        String connDriverIn = "oracle.jdbc.driver.OracleDriver";
        String connInUser = "user1";
        String connInPw ="12345678";
        String url ="jdbc:oracle:thin:@localhost:1521:orcl" ;
        try {
            Class.forName(connDriverIn).newInstance();
            connectionIn = DriverManager.getConnection(url, connInUser,
                    connInPw);// 打开conn
            System.out.println(connectionIn + "-----------sqlServiceConn=" + url);
            return "ok";
        } catch (Exception e) {

            connectionIn = null;
            e.printStackTrace();
            System.out.println("获取in数据库链接异常");
            return "获取被同步的数据库链接异常";
        }
    }
/**
     * 获取数据库链接
     */
    private String getSqlServiceConnOut(LhdbCfgConn lhdbCfgConn) {
        String connDriverOut = "oracle.jdbc.driver.OracleDriver";
        String connOutUser = "user2";
        String connOutPw = "12345678";
        String url = lhdbCfgConn.getConnOutLink();
        try {
            Class.forName(connDriverOut).newInstance();
            connectionOut = DriverManager.getConnection(url, connOutUser,
                    connOutPw);// 打开conn
            System.out.println(connectionOut + "-----------sqlServiceConn=" + url);
            // 设置自动提交为false
            connectionOut.setAutoCommit(false);
            return "ok";
        } catch (Exception e) {

            connectionOut = null;
            e.printStackTrace();
            System.out.println("获取out数据库链接异常");
            return "获取被同步的数据库链接异常";
        }
    }

注意:为了后边利用事务和批量提交,要把要写入的数据库连接的自动提交设置为false,connectionOut.setAutoCommit(false);

查询表数据

private ResultSet getResultSet() throws SQLException {
        if(connectionIn==null){
           //这里要获取连接
        }
        String tableName = "表名";
        Timestamp timestamp = 最后更新时间;
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append("select * from ").append(tableName);
        if (timestamp != null) {
            stringBuilder.append(" where update > ? and update<= ?");
        }
        String qureSql = stringBuilder.toString();
        logger.error("quersql==" + qureSql);
        logTxt("quersql==" + qureSql);
        statementIn = connectionIn.prepareStatement(qureSql, ResultSet.TYPE_FORWARD_ONLY,
                ResultSet.CONCUR_READ_ONLY);
        Timestamp timestamp1 = UtilDate.currentTimestamp();
        if (timestamp != null) {
            statementIn.setTimestamp(1,timestamp);
            statementIn.setTimestamp(2,timestamp1);
        }
        statementIn.setFetchSize(10000);//设置每次读取的数量
        ResultSet rsIn = statementIn.executeQuery();
        return rsIn;
    }

注意:在测试过程种遇到一个170w条数据的表,每到40w左右的时候就报内存溢出,开始总是以为内存设置不够大,设置为4G,6G,8G改善情况不大,通过日志看出,是读取的时候报错,猜测是因为读取数据太多导致内存溢出,一顿度娘,在api中有设置每次返回行数限制(statementIn.setFetchSize(10000)),每次返回固定行数,减少内存消耗.

获取表中含有哪些字段

/**
     * 获取字段
     *
     * @param rs
     * @throws SQLException
     */
    private List<String> getColumns(ResultSet rs) throws SQLException {
        ResultSetMetaData data = rs.getMetaData();
        List<String> list = new ArrayList<>();
        for (int i = 1; i <= data.getColumnCount(); i++) {
            // 获得所有列的数目及实际列数
            //int columnCount = data.getColumnCount();
            // 获得指定列的列名
            String columnName = data.getColumnName(i);
            // 获得指定列的列值
            //int columnType = data.getColumnType(i);
            // 获得指定列的数据类型名
            //String columnTypeName = data.getColumnTypeName(i);
            // 所在的Catalog名字
            //String catalogName = data.getCatalogName(i);
            // 对应数据类型的类
            //String columnClassName = data.getColumnClassName(i);
            // 在数据库中类型的最大字符个数
            //int columnDisplaySize = data.getColumnDisplaySize(i);
            // 默认的列的标题
            //String columnLabel = data.getColumnLabel(i);
            // 获得列的模式
            //String schemaName = data.getSchemaName(i);
            // 某列类型的精确度(类型的长度)
            //int precision = data.getPrecision(i);
            // 小数点后的位数
            //int scale = data.getScale(i);
            // 获取某列对应的表名
            //String tableName = data.getTableName(i);
            // 是否自动递增
            //boolean isAutoInctement = data.isAutoIncrement(i);
            // 在数据库中是否为货币型
            //boolean isCurrency = data.isCurrency(i);
            // 是否为空
            //int isNullable = data.isNullable(i);
            // 是否为只读
            //boolean isReadOnly = data.isReadOnly(i);
            // 能否出现在where中
            //boolean isSearchable = data.isSearchable(i);
            /*System.out.println("获得列" + i + "的类型,返回SqlType中的编号:" + columnType);
            System.out.println("获得列" + i + "的数据类型名:" + columnTypeName);
            System.out.println("获得列" + i + "所在的Catalog名字:" + catalogName);
            System.out.println("获得列" + i + "对应数据类型的类:" + columnClassName);
            System.out.println("获得列" + i + "在数据库中类型的最大字符个数:" + columnDisplaySize);
            System.out.println("获得列" + i + "的默认的列的标题:" + columnLabel);
            System.out.println("获得列" + i + "的模式:" + schemaName);
            System.out.println("获得列" + i + "类型的精确度(类型的长度):" + precision);
            System.out.println("获得列" + i + "小数点后的位数:" + scale);
            System.out.println("获得列" + i + "对应的表名:" + tableName);
            System.out.println("获得列" + i + "是否自动递增:" + isAutoInctement);
            System.out.println("获得列" + i + "在数据库中是否为货币型:" + isCurrency);
            System.out.println("获得列" + i + "是否为空:" + isNullable);
            System.out.println("获得列" + i + "是否为只读:" + isReadOnly);
            System.out.println("获得列" + i + "能否出现在where中:" + isSearchable);*/

            //list.add(rs.getObject(i));
            //System.out.println("获得列" + i + "的字段名称:" + columnName);

            list.add(columnName);
        }

        return list;
    }

根据获取的字段合成insert语句

/**
     * @param list
     * @param tableName
     * @return
     */
    private String getsql(List<String> list, String tableName) {
        int size = list == null ? 0 : list.size();
        StringBuffer sbSql = new StringBuffer();
        StringBuffer sbColumns = new StringBuffer();
        StringBuffer sbValue = new StringBuffer();
        sbSql.append("INSERT INTO ").append(tableName).append(" ( ");
        for (int i = 0; i < size; i++) {
            String columnName = list.get(i);
            if (i == size - 1) {
                sbColumns.append(columnName);
                sbValue.append("?");
            } else {
                sbColumns.append(columnName).append(",");
                sbValue.append("?").append(",");
            }
        }
        sbSql.append(sbColumns).append(" ) VALUES ( ").append(sbValue).append(")");
        return sbSql.toString();
    }

写入数据

private void saveData(ResultSet rsIn, String outTableName) throws SQLException {
        List<String> columns = getColumns(rsIn);
        String sql = getsql(columns, outTableName);
        int size = columns.size() + 1;

        int ttt = 0;
        logger.error("插入sql==" + sql);
        logTxt("插入sql==" + sql);
        statementOut = connectionOut.prepareStatement(sql);
        while (rsIn.next()) {
            ttt++;
            for (int i = 1; i < size; i++) {
                // 设置占位符的值
                statementOut.setObject(i, rsIn.getObject(i));
            }
            // 添加到批处理
            statementOut.addBatch();
            // 达到批量保存大小时，执行批处理
            if (ttt % 10000 == 0) {
                statementOut.executeBatch();
                statementOut.clearBatch();
                // 提交事务
                connectionOut.commit();
            }
        }
        // 执行剩余的批处理
        statementOut.executeBatch();
        // 提交事务
        connectionOut.commit();

    }

关闭连接

private void closeStatement() {
        // 关闭连接
        if (statementOut != null) {
            try {

                statementOut.clearBatch();
                statementOut.close();
                statementOut = null;
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        // 关闭连接
        if (statementIn != null) {
            try {
                statementIn.clearBatch();
                statementIn.close();
                statementIn = null;
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

日常记录
互相交流
欢迎指教
各显神通