import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;

import org.teiid.jdbc.TeiidDataSource;

public class TestMain {
	private static final String host = "vdb.example.com";
	private static final String port = "31000";
	private static final String vdb = "EngVDBR";
	private static final String user = "user";
	private static final String password = "user";

	private static final int batchSize = 50000;

	public static void main(String[] args) throws Exception {
		new TestMain();
	}
	
	public TestMain() {
		try {
			System.out.println("Executing using the TeiidDriver");
			Connection connSrc = getSrcConnection();
			Connection connTar = getTarConnection();
			ArrayList<String> batches = getBatches(getCount(connSrc, "Bugzilla.bugs", "1 = 1"));
			System.out.println(batches.size());
			Iterator<String> it = batches.iterator();
			while (it.hasNext()) {
				String str = "select keywords from Bugzilla.bugs order by bug_id "
						+ it.next();
				System.out.println(str);
				execute(connSrc, str, connTar);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	static Connection getSrcConnection() throws Exception {
		String url = "jdbc:teiid:" + vdb + "@mms://" + host + ":" + port
				+ ";showplan=on";
		Class.forName("org.teiid.jdbc.TeiidDriver");
		return DriverManager.getConnection(url, user, password);
	}

	static Connection getTarConnection() throws Exception {
		String url = "jdbc:mysql://localhost:3306/test";
		Class.forName("com.mysql.jdbc.Driver");
		return DriverManager.getConnection(url, "test", "test");
	}

	static Connection getDataSourceConnection() throws Exception {
		TeiidDataSource ds = new TeiidDataSource();
		ds.setDatabaseName(vdb);
		ds.setUser(user);
		ds.setPassword(password);
		ds.setServerName(host);
		ds.setPortNumber(Integer.valueOf(port));

		ds.setShowPlan("on");

		return ds.getConnection();
	}
	
	public static void execute(Connection connSrc, String sql,
			Connection connTar) throws Exception {
		try {
			Statement statement = connSrc.createStatement(
					ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
			// statement.setFetchSize(5000);

			ResultSet results = statement.executeQuery(sql);

			ResultSetMetaData metadata = results.getMetaData();
			int columns = metadata.getColumnCount();
			connTar.setAutoCommit(false);
			PreparedStatement ps = connTar
					.prepareStatement("insert into bugs values("
							+ getColStr(columns) + ")");
			System.out.println("insert into bugs values(" + getColStr(columns)
					+ ")");
			for (int row = 1; results.next(); row++) {
				for (int i = 1; i <= columns; i++) {
                                        // System.out.println(results.getObject(i));
					ps.setObject(i, results.getObject(i));

				}
				ps.executeUpdate();
			}
			connTar.commit();
			results.close();
			statement.close();
		} catch (SQLException e) {
			e.printStackTrace();
			System.exit(1);
		} finally {
			
		}
	}

	public ArrayList<String> getBatches(int tabCnt) {
		ArrayList<String> batches = new ArrayList<String>();
		int batchLen = 0;
		int init = 0;
		int endNum = 0;
		if (tabCnt % batchSize == 0) {
			batchLen = tabCnt / batchSize;
		} else {
			batchLen = tabCnt / batchSize + 1;
		}
		for (int i = 0; i < batchLen; i++) {
			batches.add(" limit " + init + ", " + batchSize);
			endNum = init + batchSize;
			init = endNum;
		}
		return batches;
	}

	public int getCount(Connection conn, String table, String condition) {
		try {
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("select count(1) from "
					+ table + " where " + condition);
			if (rs.next()) {
				return rs.getInt(1);
			}
		} catch (Exception e) {

		}
		return 0;
	}

	public static String getColStr(int columnCount) {
		StringBuffer sb = new StringBuffer();
		for (int i = 0; i < columnCount; i++) {
			sb.append("?,");
		}
		String str = sb.toString();
		return str.substring(0, str.length() - 1);
	}
}