/**
 * 
 */

/**
 * @author sven
 *
 */
import java.io.*;
import java.util.*;
import java.sql.*;

public class DB2P6 {
	
	static private Connection        con;
	
	static private Statement         stmt; 
	static private String            query;
	static private PreparedStatement pStmt;
	//static private CallableStatement cStmt;
	
	static private ResultSet         rs;
	
	static private BufferedReader	 bf;
	/**
	 * @param args
	 */
	public static void main(String[] args)
	throws SQLException, IOException {
		// TODO Auto-generated method stub
		bf = new BufferedReader(new InputStreamReader(System.in));
		System.out.println("Username: ");
		String username = bf.readLine();
		System.out.println("Password: ");
		String pw = bf.readLine();
		
	   // 1. Treiber-Klasse für JdbcOdbcDriver laden und registrieren
	   String driverClass =
		  "sun.jdbc.odbc.JdbcOdbcDriver";
	   try {
		  Class.forName(driverClass);
	   }
	   catch (ClassNotFoundException exc) {
		  System.out.println("c1: "+exc.getMessage());
		  System.exit(1);
	   }
	     
	   // Wieviele Elemente sind derzeit bei der Klasse DriverManager
	   // registriert?
	   Enumeration e = DriverManager.getDrivers();
	   while (e.hasMoreElements())
			System.out.println("Beim DriverManager registriert: " + 
			                    e.nextElement() + "\n");
			
	   // 2. Connection aufbauen
	   try {
		  String url = "jdbc:odbc:db2p6"; 
		  con = DriverManager.getConnection(url,username,pw);
		  System.out.println("Jetzt ist die Connection da");
		  // Transaktionsmanagement: AutoCommit off
		  con.setAutoCommit(false);
		  System.out.println("Isolation Level: ");
		  System.out.println("1: Read Committed");
		  System.out.println("2: Serializable");
		  String il = bf.readLine();
		  // Isolationlevel setzen
		  if (il.equals("1"))
			  con.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
		  else if (il.equals("2"))
			  con.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
		  else
			  con.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
		  //System.out.println(con.getTransactionIsolation());
		  con.commit();
	   }
	   catch (SQLException exc) {
		  System.out.println("c2: "+exc.getMessage());
	   }
	   //String vAcode;
	   String vAname;
	   try {
		   query = "SELECT aname FROM AUTOR";
		   stmt = con.createStatement();
		   rs = stmt.executeQuery(query);
		   while (rs.next()) {
			   //vAcode=rs.getString(1);
			   vAname=rs.getString(1);
			   System.out.println(vAname);
		   }
		   rs.close();
	   }
	   catch (SQLException exc) {
			  System.out.println("c2: "+exc.getMessage());
	   }
	   try {
		   pStmt = con.prepareStatement("insert into autor values (?,?)");
		   System.out.println("Autor code: ");
		   String vNewAcode = bf.readLine();
		   System.out.println("Autor Name: ");
		   String vNewAname = bf.readLine();
		   pStmt.setString(1,vNewAcode);
		   pStmt.setString(2,vNewAname);
		   pStmt.executeUpdate();
		   con.commit();
	   }
	   catch (SQLException exc) {
		   con.rollback();
		   System.out.println("c2: "+exc.getMessage());
	   }
	   System.out.println("Autorenliste nochmals anzeigen? [J/N]");
	   String auswahl = bf.readLine();
	   if (auswahl.toUpperCase().equals("J"))
	   {
		   try {
			   query = "SELECT aname FROM AUTOR";
			   stmt = con.createStatement();
			   rs = stmt.executeQuery(query);
			   while (rs.next()) {
				   //vAcode=rs.getString(1);
				   vAname=rs.getString(1);
				   System.out.println(vAname);
			   }
			   rs.close();
		   }
		   catch (SQLException exc) {
				  System.out.println("c2: "+exc.getMessage());
		   }
	   }
	}
}