import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Savepoint;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.Statement;

public class JDBCTransact2 {
	private static void printContent(Statement stmt) throws SQLException {
		ResultSet rs =
			stmt.executeQuery("SELECT FNAME,MINIT,LNAME FROM EMPLOYEE;");
		while (!rs.isLast()) {
			rs.next();
			System.out.println(
				rs.getString("LNAME")
					+ "\t"
					+ rs.getString("MINIT")
					+ "\t"
					+ rs.getString("LNAME"));
		}
	}
	public static void main(String[] args) {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException cnfe) {
			System.err.println("Driver class not found");
			cnfe.printStackTrace();
		}
		Connection con = null;

		try {
			con =
				(Connection) DriverManager.getConnection(
					"jdbc:mysql://localhost/jdbctest/",
					"mario",
					"thePassword");
		} catch (SQLException sqle) {
			System.err.println("Error establishing database connection");
			Throwable t = sqle;
			while (t != null) {
   			System.err.println("Type: " + t.getClass().getName());
      		System.err.println("Message: " + t.getMessage());
      		System.err.println("-----");
      		t = t.getCause();
			}
		}

		Statement stmt = null;
		try {
			stmt = (Statement) con.createStatement();
		} catch (SQLException sqle) {
			System.err.println("Error creating SQL-Statement");
			Throwable t = sqle;
			while (t != null) {
   			System.err.println("Type: " + t.getClass().getName());
      		System.err.println("Message: " + t.getMessage());
      		System.err.println("-----");
      		t = t.getCause();
			}
		}

		try {
			int transactionIsolation = con.getTransactionIsolation();
			switch (transactionIsolation) {
				case Connection.TRANSACTION_NONE :
					System.out.println("Transactions are not supported");
					break;
				case Connection.TRANSACTION_READ_UNCOMMITTED :
					System.out.println(
						"Dirty reads, non-repeatable reads and phantom reads can occur");
					break;
				case Connection.TRANSACTION_READ_COMMITTED :
					System.out.println(
						"Dirty reads are prevented; non-repeatable reads and phantom reads can occur");
					break;
				case Connection.TRANSACTION_REPEATABLE_READ :
					System.out.println(
						"Dirty reads and non-repeatable reads are prevented; phantom reads can occur");
					break;
				case Connection.TRANSACTION_SERIALIZABLE :
					System.out.println(
						"Dirty reads, non-repeatable reads and phantom reads are prevented");
					break;
			}
			if (transactionIsolation < Connection.TRANSACTION_SERIALIZABLE) {
				con.setTransactionIsolation(
					Connection.TRANSACTION_SERIALIZABLE);
				if (con.getTransactionIsolation()
					!= Connection.TRANSACTION_SERIALIZABLE) {
					System.out.println(
						"cannot set Connection.TRANSACTION_SERIALIZABLE");
				} else {
					System.out.println(
						"reached highest possible isolation level");
				}
			}

			con.setAutoCommit(false);
			stmt.executeUpdate(
				"INSERT INTO EMPLOYEE VALUES('Hans','X','Hinterhuber','111111111',NULL,NULL,NULL,NULL,NULL,NULL);");
			Savepoint sp = con.setSavepoint();
			stmt.executeUpdate(
				"INSERT INTO EMPLOYEE VALUES('Franz','X','Obermüller','222222222',NULL,NULL,NULL,NULL,NULL,NULL);");
			printContent(stmt);
			//suppose error happens here
			Thread.sleep(5000);
			boolean error = true;
			if (error) {
				con.rollback(sp);
			}
			stmt.executeUpdate(
				"INSERT INTO EMPLOYEE VALUES('Fritz','X','Meier','333333333',NULL,NULL,NULL,NULL,NULL,NULL);");
			printContent(stmt);
			con.commit();
		} catch (SQLException sqle) {
			Throwable t = sqle;
			while (t != null) {
   			System.err.println("Type: " + t.getClass().getName());
      		System.err.println("Message: " + t.getMessage());
      		System.err.println("-----");
      		t = t.getCause();
			}
		} catch (InterruptedException ie) {
			ie.printStackTrace();
		}
	}
}