import java.sql.*;
import java.util.ArrayList;

/**
 * Created by IntelliJ IDEA.
 * User: Philippe Legrain
 * Date: Sep 20, 2006
 * Time: 11:04:00 AM
 * To change this template use File | Settings | File Templates.
 */
public class Main {

    public static final String mysqlUrl = "jdbc:mysql://mysqlhost/xwiki";
    public static final String db2Url = "jdbc:db2://db2host:50540/PORTAL:currentSchema=XWIKI;";


    public Connection connect(String url, String login, String passwd) throws SQLException {
        return DriverManager.getConnection(url,login, passwd);
    }

    void loadDriver() throws ClassNotFoundException {
        Class.forName("com.mysql.jdbc.Driver");
        Class.forName("com.ibm.db2.jcc.DB2Driver");
    }

    public static void main(String[] args) {
        Main main = new Main();

        try {
            main.loadDriver();
            Connection mysqlConn = main.connect(mysqlUrl, "mysqluser", "mysqlpassword");
            Connection db2conn = main.connect(db2Url, "db2user", "db2password");

            ResultSet tables = main.fetchOriginalData(mysqlConn, "show tables");
            String tableName = null;
            while (tables.next()) {
                tableName = tables.getString(1);
                main.transferTable(tableName,mysqlConn,db2conn);
            }

        } catch (Exception e) {
            e.printStackTrace();
            System.err.println(e.getMessage());
        }
    }

    ResultSet fetchOriginalData(Connection mysqlConn, String query) throws SQLException {
        Statement statement = mysqlConn.createStatement();
        String contentFetchQuery = query;
        return statement.executeQuery(contentFetchQuery);
    }

    public void transferTable(String tableName, Connection source, Connection target)  {
        System.out.print("Transfering table " + tableName);
        int count = 0;
        try {
            Statement stm = source.createStatement();
            ResultSet resultSet = stm.executeQuery("SELECT * from " + tableName);
            ResultSetMetaData metaData = resultSet.getMetaData();
            int colCount = metaData.getColumnCount();
            String insertQuery = createCompleteInsert(tableName, colCount);
            String deleteQuery = "DELETE from " + tableName;
            target.createStatement().executeUpdate(deleteQuery);

            count = 0;

            while (resultSet.next()) {
                PreparedStatement insertStm = target.prepareStatement(insertQuery);
                for (int i = 1; i <= colCount; i++) {
                    Object object = resultSet.getObject(i);
                    if (object == null) {
                        insertStm.setNull(i,metaData.getColumnType(i));
                    } else {
                        insertStm.setObject(i,object);
                    }

                }
                insertStm.executeUpdate();
                System.out.print(".");
                count++;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        System.out.println();
        System.out.println(count + " rows.");
    }

    private String createCompleteInsert(String tableName, int colCount) {
        StringBuffer insertStatement = new StringBuffer("INSERT into " + tableName + " VALUES (?");
        for (int i = 2; i <= colCount; i++) {
            insertStatement.append(",?");
        }
        insertStatement.append(")");
        return insertStatement.toString();
    }

}