/**
 * JDBC example
 * @author Rasmus Pagh 
 * Just for testing things - no exception handling
**/

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

public class JDBCexample
{

    public static void main (String[] args) throws Exception {

        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/imdb","root","");
	    conn.setAutoCommit(false);
		Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY);

		switch (Integer.parseInt(args[0])) {
			
	    /* Movies by year, imperative way */
		case 1:
		long starttime = System.currentTimeMillis();
		for (int year=1900; year<2012; year++) {
			String q2 = "SELECT COUNT(*) FROM movie WHERE year="+year;
			ResultSet rs2 = stmt.executeQuery(q2);
			rs2.next(); // Move to first (and only) tuple
			int count = rs2.getInt(1);
			if (count>0) System.out.print(year+","+count+";");
		}
		System.out.println((System.currentTimeMillis()-starttime)/1000.0+" seconds");
		break;

	    /* Movies by year, SQL centric way */
		case 2:
		starttime = System.currentTimeMillis();
		String q3 = "SELECT year,COUNT(*) FROM movie GROUP BY year";
		ResultSet rs3 = stmt.executeQuery(q3);
		while (rs3.next()) {
			int year = rs3.getInt(1);
			int count = rs3.getInt(2);
			System.out.print(year+","+count+";");
		}
		System.out.println((System.currentTimeMillis()-starttime)/1000.0+" seconds");
		break;

	    /* Iterating through a large result set */
		case 3:
		starttime = System.currentTimeMillis();
		stmt.setFetchSize(Integer.MIN_VALUE);// Generate result incrementally, save space
		String q1 = "SELECT * FROM involved";
		ResultSet rs1 = stmt.executeQuery(q1);
		int c=0;
		while (rs1.next()) {
			String role = rs1.getString(3);
			if (role.equals("director")) c++;
		}
   		System.out.println(c+" directors in involved");
		System.out.println((System.currentTimeMillis()-starttime)/1000.0+" seconds");
		break;

	    /* Iterating through a smaller result set */
		case 4:
		starttime = System.currentTimeMillis();
		//stmt.setFetchSize(Integer.MIN_VALUE);// Generate result incrementally, save space
		String q4 = "SELECT * FROM involved WHERE role=\"director\"";
		ResultSet rs4 = stmt.executeQuery(q4);
		c=0;
		while (rs4.next()) {
			c++;
		}
   		System.out.println(c+" tuples in involved with role=\"director\"");
		System.out.println((System.currentTimeMillis()-starttime)/1000.0+" seconds");
		}
        conn.close ();

    }
}