import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.PreparedStatement;

/*
	NOTES
	-JDBC API is available in two packages, java.sql and javax.sql
	-If no. of rows of resultset isn't known, use ResultSet metadata to keep track of ResultSet itself. like rows, tables, columns and all.
	-PreparedStatement is a child class of Statement, which reduces query compilation time because
		it works on pre-compiled queries, unlike Statement

*/

class JDBCTest {

	public static void main(String[] args) throws Exception {
		Class.forName("oracle.jdbc.driver.OracleDriver");
		String connString= "jdbc:oracle:thin:@localhost:1521:xe";
		Connection connection= null;
		Statement statement= null;
		ResultSet rs= null;
		ResultSetMetaData data= null;
		PreparedStatement pstatement= null;

		try{
			connection = DriverManager.getConnection(connString, "system", "manager");

			///*
			pstatement= connection.prepareStatement("select * from employee where empid= ?");
			pstatement.setInt(1, 124);
			rs= pstatement.executeQuery();
			if(rs.next()){
				System.out.println(rs.getString(1)+" "+ rs.getString(3));
			}
			//*/
			// to set autocommit off
			//connection.setAutoCommit(false);
			//update statement
			/*pstatement= connection.prepareStatement("update employee set salary=? where empid> ?");
			pstatement.setInt(1, 900);
			pstatement.setInt(2, 103);
			int rows= pstatement.executeUpdate();
			connection.commit();
			pstatement.setInt(1, 0);
			pstatement.setInt(2, 120);
			connection.rollback(); // previous DML wont save
			System.out.println(rows+ " rows updated");
			*/



			// insert into table using preparedStatement
			/*
			pstatement= connection.prepareStatement("insert into employee values(?, ?, ?)");
			pstatement.setInt(1, 104);
			pstatement.setString(2, "Voila");
			pstatement.setDouble(3, 18000);
			int rows= pstatement.executeUpdate();

			pstatement.setInt(1, 105);
			pstatement.setString(2, "Voodo");
			pstatement.setDouble(3, 18500);
			rows+= pstatement.executeUpdate();
			System.out.println(rows+" rows inserted");
			*/

			// Display all records, and using metadata usage
			/*statement = connection.createStatement();
			rs = statement.executeQuery("select * from employee");
			data= rs.getMetaData();	//now unknown number of columns can be identified using this.
			int columnCount= data.getColumnCount();

			while(rs.next()) {
				//System.out.println(rs.getInt("empid")+ "\t"+ rs.getString(2)+ "\t"+ rs.getString(3));
				for(int i=1; i<=columnCount; i++) {
					System.out.print(rs.getString(i)+"\t");
				}
				System.out.println();
			}
			*/

		}  catch(Exception e) {
			System.out.println("Oops");
		} finally {
			try{
				if(rs!=null)
				rs.close();
				if(statement!=null)
					statement.close();
				if(pstatement!=null)
					pstatement.close();
				if(connection!=null)
					connection.close();
			} catch( Exception e) {
				System.out.println("Oops");
			}
		}

	}
}