TableCopierTest.java

package cyrille.sql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

import junit.framework.TestCase;

public class TableCopierTest extends TestCase {

    public void copy(Connection sourceCnn, Connection targetCnn) throws Exception {
        String[] tableNames = new String[] {};
        for (int i = 0; i < tableNames.length; i++) {
            String tableName = tableNames[i];
            copy(tableName, sourceCnn, targetCnn);
        }
    }

    public void copy(String table, Connection sourceCnn, Connection targetCnn) throws Exception {
        ResultSet rst = sourceCnn.createStatement().executeQuery("select * from " + table);

        ResultSetMetaData metaData = rst.getMetaData();

        String insertSql = buildInsertPrepareStatement(table, metaData);

        PreparedStatement insertStmt = targetCnn.prepareStatement(insertSql);

        while (rst.next()) {
            for (int i = 1; i <= metaData.getColumnCount(); i++) {
                Object value = rst.getObject(i);
                insertStmt.setObject(i, value);
            }
            insertStmt.addBatch();
        }

        insertStmt.executeBatch();
    }

    private String buildInsertPrepareStatement(String table, ResultSetMetaData metaData) throws SQLException {
        String insertSql = "insert into " + "(";
        String values = " values (";
        for (int i = 1; i <= metaData.getColumnCount(); i++) {
            String columnName = metaData.getColumnName(i);
            if (i > 1) {
                insertSql += ", ";
                values += ", ";
            }
            insertSql += columnName;
            values += "?";
        }
        insertSql += ")";
        values += ")";

        insertSql += values;
        return insertSql;
    }
}