import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;

public class Main
{
    private static final long BATCH_SIZE  = 10000; 
    private static final long NUM_BATCHES = 100; 
    private static final long NUM_ROWS    = NUM_BATCHES * BATCH_SIZE; 
    private static final int  FETCH_SIZE  = 1000; 

    private static void setupTest(Connection connection) throws SQLException
    {
        Statement stmt = connection.createStatement();

        System.out.println("Creating the table");

        stmt.executeUpdate("CREATE TABLE test ( id INT4 NOT NULL, creationtime TIMESTAMP NOT NULL, PRIMARY KEY (id) )");
        stmt.close();

        System.out.println("Inserting rows into the table");

        connection.setAutoCommit(false);
        
        PreparedStatement prepStmt    = connection.prepareStatement("INSERT INTO test (id, creationtime) VALUEs (?, ?)");
        long              numInserted = 0;

        for (long batchNum = 0; batchNum < NUM_BATCHES; batchNum++)
        {
            for (long idx = 0; idx < BATCH_SIZE; idx++, numInserted++)
            {
                prepStmt.setLong(1, numInserted + 1);
                prepStmt.setTimestamp(2, new Timestamp(System.currentTimeMillis()));
                prepStmt.addBatch();
            }
            prepStmt.executeBatch();
            connection.commit();
        }

        prepStmt.close();
    }

    private static void performTest(Connection connection) throws SQLException
    {
        System.out.println("Reading " + NUM_ROWS + " integers from the table");

        Statement stmt = connection.createStatement();

        // First the ids
        long      startTime = System.currentTimeMillis();
        ResultSet rs        = stmt.executeQuery("SELECT id FROM test");
        long      numRead   = 0;
        
        rs.setFetchSize(FETCH_SIZE);
        while (rs.next())
        {
            numRead++;
            rs.getLong(1);
        }
        stmt.close();

        long resultTime = System.currentTimeMillis() - startTime;
        
        System.out.println("Fetching " + numRead + " integers from the db took " + resultTime + "ms");
        System.out.println("Reading " + NUM_ROWS + " timestamps from the table");

        // First the timestamps
        stmt = connection.createStatement();
        
        startTime = System.currentTimeMillis();
        rs        = stmt.executeQuery("SELECT creationtime FROM test");
        numRead   = 0;

        rs.setFetchSize(FETCH_SIZE);
        while (rs.next())
        {
            numRead++;
            rs.getTimestamp(1);
        }
        stmt.close();

        resultTime = System.currentTimeMillis() - startTime;
        
        System.out.println("Fetching " + numRead + " timestamps from the db took " + resultTime + "ms");
    }
    
    public static void main(String[] args) throws Exception
    {
        Class.forName("org.postgresql.Driver");

        Connection connection = DriverManager.getConnection("jdbc:postgresql://localhost/timestamptest", "postgres", "root123");

        setupTest(connection);
        performTest(connection);

        connection.close();
    }
}

