import java.sql.*;
import java.io.IOException;
import javax.swing.JOptionPane;

class Problem {

    public static void main(String [] arguments) {
	Connection conn = null;
	try {
	    conn = getConn();
	    
	    Statement stat = conn.createStatement();
	    
	    String sequenceTest = "CREATE SEQUENCE sequence_test";
	    String table1 = "CREATE TABLE test (id INTEGER DEFAULT NEXTVAL('sequence_test') NOT NULL, value VARCHAR(100))";
	    
	    //create sequence and table for testing purposes
	    stat.executeUpdate(sequenceTest);
	    stat.executeUpdate(table1);

	    //let's go with the problem
	    
	    boolean autoCommit = conn.getAutoCommit();
	    conn.setAutoCommit(false);
	    
	    String insert1 = "INSERT INTO test(value) VALUES('this will have id1')";
	    String insert2 = "INSERT INTO test(value) VALUES('this will have id2')";
	    String insert3 = "INSERT INTO test(value, other) VALUES('this will have id3', 'this will cause an error')"; //<= error

	    stat.executeUpdate(insert1);
	    stat.executeUpdate(insert2);
	    stat.executeUpdate(insert3);
	    
	    conn.setAutoCommit(autoCommit);
	    
	} catch(SQLException e) {
	    try {
		conn.rollback();		
		conn.close();
		System.out.println("rollback done");
	    } catch(SQLException SQLe) {
		e.printStackTrace();
		SQLe.printStackTrace();
		System.out.println("rollback isn't done :'(");
	    }
	    System.out.println("Error code: " + e.getErrorCode());
	    System.out.println("Error messague: " + e.getMessage());
	    System.out.println("Localized messague: " + e.getLocalizedMessage());
	    System.out.println("Description: " + e);
	    System.out.println();
	    System.out.println("<-  ->");
	    System.out.println();	 
	    e.printStackTrace();
	    System.out.println();
	    System.out.println("<-  ->");
	    System.out.println();
	} catch(IOException ioe) {
	    ioe.printStackTrace();
	    System.out.println("Imput/output error");
	}


	try {
	    conn = getConn();
	    Statement stat = conn.createStatement();
	    String insert1 = "INSERT INTO test(value) VALUES('this will have id1')";
	    stat.executeUpdate(insert1);
	    stat.close();
	    
	    PreparedStatement pstmt = conn.prepareStatement("SELECT CURRVAL('sequence_test') AS CURRVAL");
	    ResultSet rs = pstmt.executeQuery();
	    if (rs.first()) {
		System.out.println("Current sequence value is: " + rs.getInt("CURRVAL"));
		System.out.println("According with the code, rollback was called, so this new row should have 1");
	    } else {
		System.out.println("FATAL");
		System.out.println("FATAL");
		System.out.println("FATAL");
		System.out.println("cannot reach the sequence");
		return;
	    }
	    rs.close();
	    pstmt.close();
	    conn.close();
	} catch(SQLException e) {
	    e.printStackTrace();
	} catch(IOException ioe) {
	    ioe.printStackTrace();
	}
    }

    public static Connection getConn() throws SQLException, IOException {	   	 
	
	try {
	    
	    Class.forName("org.postgresql.Driver").newInstance();
            
	    //return DriverManager.getConnection("jdbc:postgresql://yourIp:yourPort/yourDataBaseName?charSet=yourCharSet", "yourUser", "yourPassword");
	    
	} catch(ClassNotFoundException e) {
	    e.printStackTrace();    	 
	} catch(IllegalAccessException ee) {
	    ee.printStackTrace();
	} catch(InstantiationException eee) {
	    eee.printStackTrace();
	}    	 
	
	catch(SQLException e) {
	    JOptionPane.showMessageDialog(null,
					  "can't connect with database");
	    
	}
	
	return null;   
    }

}