Java Copying tables to another schema

Question

I have a place where I store tables. In this place I have a table. Lets' name it Table A. All columns in table A columns are varchars. I want to create a process that will go through every table and create a new table in a different place in the database. However this table has to have the right datatypes instead of varchars. So if my column in table A is a column with the values 1234, I want to create a field that will be an INT an not a varchar.

I don't have any idea how to do it in java. Could somebody point me in the right direction on what things I would have to learn and if it is too difficult

Thanks

Answer 1

As I see it, your problem has a few parts:

1. EASY - Getting the existing table information. You'll have to get the DatabaseMetaData from your jdbc connection. Documentation on that class is here, and Google is your friend as to how to get that Object in the first place. (Hint: this question should get you most of the way )

2. MEDIUM - Figuring out what data type your data really is. If you try to narrow it down to just a few simple types like Integer, String, and dates, this might not actually be that hard. You'll just need to select a few rows for each column (where that column isn't null), and then try and parse it into different types. If it succeeds, it probably is that type. If it throws an Exception, then it's not. Integer.parseInt(), LocalDateTimeFormatter, etc. are the things you'll be using.

If you want to get table meta data, then just do a "SELECT * FROM < TABLE >" and the ResultSetMetaData will have all the information you'll need to keep going.

3. VERY HARD - Creating tables using the new, appropriate data types. This is hard not because of the stuff that's been mentioned so far, but because we haven't yet talked about foreign keys, indexes, and all that other good stuff. Mapping all those things programmatically will be a very hard problem. Therefore, I recommend NOT trying to program this part. Instead, have your script output the mapping of old datatypes to new datatypes for each table. Then you can go into your SQL mananger and get the SQL script to generate that table. Almost every SQL database management tool can generate the creation script for a table. Edit that script with your new datatypes, give it a new name, and run it.

Answer 2

Here's what worked for me in the end - simple routine to copy multiple table from the T0 schema to the T1, T2 and T3 schemas

public class CopyFromT0toT123 {

public static void main(String[] args) throws Exception {
    CopyFromT0toT123 copy = new CopyFromT0toT123();
    copy.copyTable("table name 1", Schema.XXXX_T0, Schema.XXXX_T1, Schema.XXXX_T2, Schema.XXXX_T3);
    copy.copyTable("table name 2", Schema.XXXX_T0, Schema.XXXX_T1, Schema.XXXX_T2, Schema.XXXX_T3);
    copy.copyTable("table name 3", Schema.XXXX_T0, Schema.XXXX_T1, Schema.XXXX_T2, Schema.XXXX_T3);
}

private void copyTable(String tableName, Schema schemaSource, Schema... schemaDestinations) throws Exception {
    // Clear out table
    clearOutDestinationTable(tableName, schemaDestinations);

    // Start copying
    String sqlSelect = "SELECT * FROM " + tableName;
    Schema schema = Schema.XXXX_T0;
    Connection conn = DriverManager.getConnection(schema.getConnectionString(), schema.getUserId(), schema.getPassword());
    PreparedStatement prepStmnt = conn.prepareStatement(sqlSelect);
    ResultSet resSet = prepStmnt.executeQuery();

    copyToDestinationTable(tableName, resSet, schemaSource, schemaDestinations);

    resSet.close();
    prepStmnt.close();
    conn.close();
}

private void copyToDestinationTable(String tableName, ResultSet resSet, Schema schemaSource, Schema... schemaDestinations) throws Exception {
    for (Schema schemaDestination : schemaDestinations) {
        System.out.println("Copying " + tableName + " to " + schemaDestination.toString());
        String sqlInsert = buildInsert(tableName);
        Connection connDestination = DriverManager.getConnection(schemaDestination.getConnectionString(), schemaDestination.getUserId(), schemaDestination.getPassword());
        PreparedStatement prepStmntDestination = connDestination.prepareStatement(sqlInsert);

        while (resSet.next()) {
            for (int i = 0; i < resSet.getMetaData().getColumnCount(); i++) {
                prepStmntDestination.setObject(i + 1, resSet.getObject(i + 1));
            }
            prepStmntDestination.executeUpdate();
        }

        prepStmntDestination.close();
        connDestination.close();
    }
}

private void clearOutDestinationTable(String tableName, Schema... schemaDestinations) throws SQLException {
    for (Schema schemaDestination : schemaDestinations) {
        String sqlDelete = "DELETE FROM " + tableName;
        Connection connDestination = DriverManager.getConnection(schemaDestination.getConnectionString(), schemaDestination.getUserId(), schemaDestination.getPassword());
        PreparedStatement prepStmntDestination = connDestination.prepareStatement(sqlDelete);
        prepStmntDestination.executeUpdate();
        prepStmntDestination.close();
        connDestination.close();
    }
}

private String buildInsert(String tableName) throws SQLException {
    String sqlSelect = "SELECT * FROM " + tableName;
    String result = "";
    Schema schema = Schema.XXXX_T0;
    Connection conn = DriverManager.getConnection(schema.getConnectionString(), schema.getUserId(), schema.getPassword());
    PreparedStatement prepStmnt = conn.prepareStatement(sqlSelect);
    ResultSet resSet = prepStmnt.executeQuery();
    ResultSetMetaData metaData = resSet.getMetaData();
    String[] columnNames = new String[metaData.getColumnCount()];
    result = "INSERT INTO " + tableName + "(";
    for (int i = 0; i < columnNames.length; i++) {
        result = result + metaData.getColumnName(i + 1) + ",";
    }
    result = result.substring(0, result.length() - 1) + ") VALUES ( ";
    for (int i = 0; i < columnNames.length - 1; i++) {
        result = result + " ? ,";
    }
    result = result + " ? )";
    resSet.close();
    prepStmnt.close();
    conn.close();

    return result;
}

}

public enum Schema {

XXXX_T0("XXXX_T0", "password here", "T0", "20", true,
        "jdbc:oracle:thin:@(DESCRIPTION..."),

XXXX_T1("XXXX_T1", "password here", "T1", "30", true,
        "jdbc:oracle:thin:@(DESCRIPTION....."),

XXXX_T2("XXXX_T2", "password here", "T2", "40", true,
        "jdbc:oracle:thin:@(DESCRIPTION....."),

XXXX_T3("XXXX_T3", "password here", "T2", "50", true,
        "jdbc:oracle:thin:@(DESCRIPTION....."),


private final String connectionString;
private final String userId;
private final String password;
private final String displayId;
private final String order;
private final boolean interAct;

private Schema(String userId, String password, String databaseId, String order, boolean interAct, String connectionString) {
    this.connectionString = connectionString;
    this.userId = userId;
    this.password = password;
    this.displayId = databaseId;
    this.order = order;
    this.interAct = interAct;
}


/**
 * Return a specific Schema for a give Schema database id
 */
public static final Schema getSchema(String displayId) {
    for (Schema schema : values()) {
        if (displayId.equals(schema.displayId)) {
            return schema;
        }
    }
    return null;
}

public String getConnectionString() {
    return connectionString;
}

public String getUserId() {
    return userId;
}

}