java/DataHelper/src/datahelper/DataHelper.java

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package datahelper;

import com.microsoft.sqlserver.jdbc.SQLServerBulkCopy;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Types;

/**
 *
 * @author Lisewski
 */
public class DataHelper {

    static Method[] colMethods;

    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) throws Exception {
    }

    public static int CopyData(Connection sourceCon, Connection targetCon, String sourceSql, String targetTable, boolean dropTargetTable) throws Exception {
        var iRow = 0;
        var iBuffer = 50000;
        var sourceCommand = sourceCon.createStatement();
        var dataReader = sourceCommand.executeQuery(sourceSql);
        var schemaTable = dataReader.getMetaData();
        targetCon.setAutoCommit(false);
        // create the insert statement and create table if it doesn't exist
        CreateTable(targetCon, schemaTable, targetTable, dropTargetTable);

        // if target is sqlserver do a bulkcopy
        if (targetCon.getMetaData().getDriverName().contains("SQL Server")) {
            SQLServerBulkCopy bulkCopy = new SQLServerBulkCopy(targetCon);
            bulkCopy.setDestinationTableName(targetTable);
            bulkCopy.writeToServer(dataReader);
            targetCon.commit();
            var cmd = targetCon.createStatement();
            var rs = cmd.executeQuery(String.format("SELECT COUNT(1) FROM %s", targetTable));
            rs.next();
            iRow = rs.getInt(1);
        } else {
            var targetCommand = PrepareCommand(targetCon, schemaTable, targetTable);
            while (dataReader.next()) {
                iRow++;
                SetParams(dataReader, schemaTable, targetCommand);
                targetCommand.addBatch();
                if (iRow % iBuffer == 0) {
                    targetCommand.executeBatch();
                    targetCon.commit();
                }
            }
            if (iRow % iBuffer != 0) {
                targetCommand.executeBatch();
                targetCon.commit();
            }
        }

        return iRow;
    }

    private static void CreateTable(Connection dbConnection, ResultSetMetaData schemaTable, String tableName, boolean withDrop) throws Exception {
        var dbTypeName = dbConnection.getMetaData().getDriverName();
        System.out.println(dbTypeName);
        if (withDrop) {
            try {
                dbConnection.createStatement().executeUpdate(String.format("DROP TABLE IF EXISTS %s", tableName));
                dbConnection.commit();
            } catch (Exception ex) {
                dbConnection.createStatement().executeUpdate(String.format("DROP TABLE %s", tableName));
                dbConnection.commit();
            }
        }

        var createNotExists = (dbTypeName.contains("SQL Server"))
                ? String.format("IF OBJECT_ID('%s') IS NULL CREATE TABLE", tableName)
                : "CREATE TABLE IF NOT EXISTS";
        if (dbTypeName.contains("Derby")) {
            createNotExists = "CREATE TABLE";
        }
        var Sql = new StringBuilder(String.format("%s %s (", createNotExists, tableName));
        var iColumns = schemaTable.getColumnCount();
        colMethods = new Method[iColumns];
        for (var i = 1; i < iColumns + 1; i++) {
            if (i > 1) {
                Sql.append(", ");
            }

            var name = schemaTable.getColumnName(i);
            var dt = schemaTable.getColumnTypeName(i);
            var type = schemaTable.getColumnType(i);
            var size = schemaTable.getPrecision(i);
            var nullable = schemaTable.isNullable(i) == 1;
            
            colMethods[i-1] = getAccessMethod(type);
            Sql.append(String.format("%s %s", '"' + name + '"', dt));
            if (dt.toLowerCase().endsWith("char")) {
                Sql.append(String.format("(%d)", size));
            }
            if (!nullable) {
                Sql.append(" NOT NULL");
            }
        }
        Sql.append(")");
        var sqlString = Sql.toString();
        if (dbTypeName.contains("PostgreSQL") || dbTypeName.contains("Derby")) {
            sqlString = sqlString.replaceAll("datetime", "timestamp");
            sqlString = sqlString.replaceAll("tinyint", "int");
            sqlString = sqlString.replaceAll("bit", "boolean");
        }
        var createCommand = dbConnection.createStatement();
        System.out.println(sqlString);
        // Assume the table already exists and ignore the create
        try {
            createCommand.executeUpdate(sqlString);
            dbConnection.commit();
        } catch (Exception ex) {
        }

    }

    private static PreparedStatement PrepareCommand(Connection dbConnection, ResultSetMetaData schemaTable, String tableName) throws Exception {
        var sbFields = new StringBuilder();
        var sbFmtValues = new StringBuilder();
        var iColumns = schemaTable.getColumnCount();
        for (var i = 1; i < iColumns + 1; i++) {
            var columnName = schemaTable.getColumnName(i);
            sbFields.append(i > 1 ? ", " : "");
            sbFields.append('"' + columnName + '"');
            sbFmtValues.append(i > 1 ? ", " : "");
            sbFmtValues.append("?");
        }
        var insertSql = String.format("INSERT INTO %s  (%s) VALUES (%s)", tableName, sbFields.toString(), sbFmtValues.toString());
        System.out.println(insertSql);
        var cmd = dbConnection.prepareStatement(insertSql);
        return cmd;
    }

    private static void SetParams(ResultSet dr, ResultSetMetaData schemaTable, PreparedStatement cmd) throws Exception {
        var iColumns = schemaTable.getColumnCount();
        for (var i = 1; i < iColumns + 1; i++) {
//            cmd.setObject(i, dr.getObject(i), dr.getType());
            cmd.setObject(i, colMethods[i-1].invoke(dr, i));
        }
    }

    private static Method getAccessMethod(int type) throws Exception {
        switch (type) {
            case Types.BIT:
            case Types.TINYINT:
            case Types.SMALLINT:
            case Types.INTEGER:
                return ResultSet.class.getMethod("getInt", int.class);
            case Types.BIGINT:
                return ResultSet.class.getMethod("getLong", int.class);
            case Types.FLOAT:
                return ResultSet.class.getMethod("getFloat", int.class);
            case Types.REAL:
            case Types.DOUBLE:
            case Types.NUMERIC:
            case Types.DECIMAL:
                return ResultSet.class.getMethod("getLong", int.class);
            case Types.CHAR:
            case Types.VARCHAR:
            case Types.LONGVARCHAR:
                return ResultSet.class.getMethod("getString", int.class);
            case Types.DATE:
                return ResultSet.class.getMethod("getDate", int.class);
            case Types.TIME:
                return ResultSet.class.getMethod("getTime", int.class);
            case Types.TIMESTAMP:
                return ResultSet.class.getMethod("getTimestamp", int.class);
            case Types.BINARY:
            case Types.VARBINARY:
            case Types.LONGVARBINARY:
            case Types.NULL:
            case Types.OTHER:
            case Types.JAVA_OBJECT:
            case Types.DISTINCT:
            case Types.STRUCT:
            case Types.ARRAY:
            case Types.BLOB:
            case Types.CLOB:
            case Types.NCLOB:
            case Types.REF:
            case Types.DATALINK:
                return ResultSet.class.getMethod("getBlob", int.class);
            case Types.BOOLEAN:
                return ResultSet.class.getMethod("getInt", int.class);
            case Types.ROWID:
            case Types.NCHAR:
            case Types.NVARCHAR:
            case Types.LONGNVARCHAR:
            case Types.SQLXML:
                return ResultSet.class.getMethod("getString", int.class);
        }
        return ResultSet.class.getMethod("getObject", int.class);

    }
}