import java.util.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

/**
 * Setup
 *
 * This java code uses a database called "isolationlevels" on iprojsrv
 * To connect to it: use the login "guest" and password "transactions"
 * To run the code:
 * javac isolation.java
 * java isolation [user] [passwrod] [transaction-number]
 *
 * to see the effect of isolation levels:
 * run transactions 1 and 2 in two windows, both with READ_COMMITTED
 *
 * run both transactions again, now with REAPEATABLE_READ (you need to recompile)
 * 
 * observe the difference !
 *
 * repeat the same with transactions 3 and 4
 *
 * To try it on a different database system:
 * create database isolationlevels
 * use isolationlevels
 * create table Account(id integer, value integer)
 * insert into Account values(0,100)
 * insert into Account values(1,100)
 * Then run the code as above
 */
public class isolation {

    private static final String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
    //    private static final String url = "jdbc:sqlserver://iisqlsrv.cs.washington.edu;database=lecture13";
    private static final String url = "jdbc:sqlserver://iprojsrv.cs.washington.edu;database=isolationlevels";

    //private static final String driver = "org.postgresql.Driver";
    //private static final String url = "jdbc:postgresql:lecture13";

    private static Connection db;

    private static final String read_sql = "select value from Account where id = 0";
    private static PreparedStatement read_statement;

    private static final String write_sql = "update Account set value = value + 100 where id = 0";
    private static PreparedStatement write_statement;

    private static final String count_sql = "select count(*) from Account";
    private static PreparedStatement count_statement;

    private static final String insert_sql = "insert into Account values(2,0)";
    private static PreparedStatement insert_statement;
   

    public static void main (String args[]) throws Exception {
 
        if (args.length < 2) {
            System.err.println ("Usage: java isolation username password transactionNb");
            System.exit(1);
        } 

        String user = args[0];
        String passwd = args[1];
        int transactionNb = Integer.parseInt(args[2]);

        try {

            openConnection(user,passwd);
            prepareStatements();

            switch (transactionNb) {
            case 1: 
                runTransaction1();
                break;
            case 2:
                runTransaction2();
                break;
            case 3:
                runTransaction3();
                break;
            default:
                runTransaction4();
                break;
            }
        
            closeConnection();

        } catch (Exception e) {
            System.out.println("Error: " + e.getMessage());
        }
    }


    public static void openConnection(String user, String password) throws Exception {
        Class.forName(driver).newInstance();
        db = DriverManager.getConnection(url, // connection
                                                 user, // user
                                                 password); // password
    }

    public static void closeConnection() throws Exception {
        db.close();
    }

    public static void prepareStatements() throws Exception {
        read_statement = db.prepareStatement(read_sql);
        write_statement = db.prepareStatement(write_sql);
        count_statement = db.prepareStatement(count_sql);
        insert_statement = db.prepareStatement(insert_sql);
    }


    public static void readAccount() throws Exception {
        ResultSet value_set = read_statement.executeQuery();
        if (value_set.next()) {
            System.out.println("Value is " + value_set.getInt(1));
        }
    }

    public static void writeAccount() throws Exception {
        write_statement.executeUpdate();
    }

    public static void countAccounts() throws Exception {
        ResultSet value_set = count_statement.executeQuery();
        if (value_set.next()) {
            System.out.println("Value is " + value_set.getInt(1));
        }
    }

    public static void insertAccount() throws Exception {
        insert_statement.executeUpdate();
    }

    public static void runTransaction1() throws Exception {

        db.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
        //db.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
  
        db.setAutoCommit(false);

        readAccount();
       
        Thread.sleep(5000);

        readAccount();
        
        db.commit();
    }

    public static void runTransaction2() throws Exception {

        db.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
        //db.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);  

        db.setAutoCommit(false);

        writeAccount();
        
        db.commit();

    }

    public static void runTransaction3() throws Exception {

        // Note: In PostgreSQL READ UNCOMMITTED is treated as READ COMMITTED, while REPEATABLE READ is treated as SERIALIZABLE. 
         db.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
        //db.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
  
        db.setAutoCommit(false);

        countAccounts();

        Thread.sleep(5000);

        countAccounts();
        
        db.commit();
    }

    public static void runTransaction4() throws Exception {

        // Note: In PostgreSQL READ UNCOMMITTED is treated as READ COMMITTED, while REPEATABLE READ is treated as SERIALIZABLE. 
        db.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
        //db.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);  

        db.setAutoCommit(false);

        insertAccount();

        db.commit();

    }

}