Java JDBC

1. Java JDBC

2. JDBC • Java JDBC is a java API to connect and execute query with the database. • JDBC API uses jdbc drivers to connect with the database.

3. Server DB Client Java Application Data Source JDBC API JDBC Driver JDBC 3

4. Why use JDBC • Before JDBC, ODBC API was the database API to connect and execute query with the database. • But, ODBC API uses ODBC driver which is written in C language (i.e. platform dependent and unsecured). That is why Java has defined its own API (JDBC API) that uses JDBC drivers (written in Java language).

5. JDBC Driver • JDBC Driver is a software component that enables java application to interact with the database.There are 4 types of JDBC drivers: • JDBC-ODBC bridge driver • Native-API driver (partially java driver) • Network Protocol driver (fully java driver) • Thin driver (fully java driver)

6. JDBC-ODBC bridge driver • The JDBC-ODBC bridge driver uses ODBC driver to connect to the database. • The JDBC-ODBC bridge driver converts JDBC method calls into the ODBC function calls.

7. JDBC-ODBC bridge driver When Java first came out, this was a useful driver because most databases only supported ODBC access but now this type of driver is recommended only for experimental use or when no other alternative is available. Advantages: • easy to use. • can be easily connected to any database. Disadvantages: • Performance degraded because JDBC method call is converted into the ODBC function calls. • The ODBC driver needs to be installed on the client machine.

8. Native-API driver • The Native API driver uses the client-side libraries of the database. • The driver converts JDBC method calls into native calls of the database API. It is not written entirely in java. JDBC API calls are converted into native C/C++ API calls. drivers are provided by the database vendors and used in the same manner as the JDBC-ODBC Bridge

9. Native-API driver Advantage: • performance upgraded than JDBC-ODBC bridge driver. Disadvantage: • The Native driver needs to be installed on the each client machine. • The Vendor client library needs to be installed on client machine.

10. Network Protocol driver • The Network Protocol driver uses middleware (application server) that converts JDBC calls directly or indirectly into the vendor-specific database protocol. • It is fully written in java. This kind of driver is extremely flexible, since it requires no code installed on the client and a single driver can actually provide access to multiple databases.

11. Network Protocol driver

12. Network Protocol driver Advantage: • No client side library is required because of application server that can perform many tasks like auditing, load balancing, logging etc. Disadvantages: • Network support is required on client machine. • Requires database-specific coding to be done in the middle tier. • Maintenance of Network Protocol driver becomes costly because it requires database-specific coding to be done in the middle tier.

13. Thin driver • The thin driver converts JDBC calls directly into the vendor-specific database protocol. That is why it is known as thin driver. It is fully written in Java language.

14. Thin driver Advantage: • Better performance than all other drivers. • No software is required at client side or server side. Disadvantage: • Drivers depends on the Database.

15. Which Driver should be Used? • If you are accessing one type of database, such as Oracle, Sybase, or IBM, the preferred Thin driver. • If your Java application is accessing multiple types of databases at the same time, Network Protocol driver is the preferred driver.

16. Which Driver should be Used? • Native-API driver drivers are useful in situations, where Network Protocol driver or Thin driver is not available yet for your database. • JDBC-ODBC bridge driver driver is not considered a deployment-level driver, and is typically used for development and testing purposes only.

17. Typical JDBC Programming Procedure • Load the database driver • Obtain a connection • Create and execute statements (SQL queries) • Use result sets (tables) to navigate through the results • Close the connection 17

18. JDBC Programming Procedure • Load the database driver using ClassLoader: • Class.forName(“oracle.jdbc.driver.OracleDriver”); • Establish connection with the Oracle database • connection con=DriverManager.getConnection(  "jdbc:oracle:thin:@localhost:1521:xe","system","password");   • Create the statement object • Statement stmt=con.createStatement();   • Execute the query ResultSet rs=stmt.executeQuery("select * from emp");   while(rs.next()){   System.out.println(rs.getInt(1)+" "+rs.getString(2));   }   • Close the connection con.close();  

19. Register JDBC Driver Approach I - Class.forName() • The most common approach to register a driver is to use Java's Class.forName() method • Class.forName("oracle.jdbc.driver.OracleDriver"); • OR • Class.forName("oracle.jdbc.driver.OracleDriver").newInstance(); • Approach II - DriverManager.registerDriver() • Driver myDriver = new oracle.jdbc.driver.OracleDriver(); DriverManager.registerDriver( myDriver );

20. Example to connect to the Oracle database • Driver class: driver class for the oracle database is oracle.jdbc.driver.OracleDriver. • Connection URL: The connection URL for the oracle database is jdbc:oracle:thin:@IP:1521:xe • Where, 1521 is the port number and XE is the Oracle service name. • Username: username of the oracle database • Password: Password for the database user.

21. Database URL Formulation • DriverManager.getConnection() method • getConnection(String url) • getConnection(String url, Properties prop) • getConnection(String url, String user, String password) Following table lists down the popular JDBC driver names and database URL.

22. Create the statement object • The JDBC Statement, • CallableStatement, and • PreparedStatement interfaces define the methods and properties that enable you to send SQL or PL/SQL commands and receive data from your database.

23. Statement • Statement Statement stmt = null; try { stmt = conn.createStatement( ); . . . } catch (SQLException e) { . . . } finally { stmt.close(); }

24. PreparedStatement • PreparedStatement PreparedStatement pstmt = null; try { String SQL = "Update Employees SET age = ? WHERE id = ?"; pstmt = conn.prepareStatement(SQL); . . . } catch (SQLException e) { . . . } finally { pstmt.close(); }

25. Create a table in oracle database • create table emp(id number(10),name varchar2(40),age number(3));  

26. Example with Oracle database import java.sql.*;   class OracleCon{   public static void main(String args[]){   try{   //step1 load the driver class   Class.forName("oracle.jdbc.driver.OracleDriver");   //step2 create  the connection object   Connection con=DriverManager.getConnection(   "jdbc:oracle:thin:@localhost:1521:xe","system","oracle");   //step3 create the statement object   Statement stmt=con.createStatement();   //step4 execute query   ResultSet rs=stmt.executeQuery("select * from emp");   while(rs.next())   System.out.println(rs.getInt(1)+"  "+rs.getString(2)+"  "+rs.getString(3));   //step5 close the connection object   con.close();   } catch(Exception e){ System.out.println(e);}    }   } 

27. Load ojdbc14.jar Two ways to load the jar file: • paste the ojdbc14.jar file in jre/lib/ext folder • set classpath 1) paste the ojdbc14.jar file in JRE/lib/ext folder: • Firstly, search the ojdbc14.jar file then go to JRE/lib/ext folder and paste the jar file here. 2) set classpath: • There are two ways to set the classpath: temporary • permanent

28. set the classpath How to set the temporary classpath • Firstly, search the ojdbc14.jar file then open command prompt and write: • C:>set classpath=c:\folder\ojdbc14.jar;.;   How to set the permanent classpath • Go to environment variable then click on new tab. In variable name write classpath and in variable value paste the path to ojdbc14.jar by appending ojdbc14.jar;.; as C:\oraclexe\app\oracle\product\10.2.0\server\jdbc\lib\ojdbc14.jar;.;

29. Example to connect to the mysql database • Driver class: driver class for the mysql database is com.mysql.jdbc.Driver. • Connection URL: The connection URL for the mysql database is jdbc:mysql://IP:3306/db_name • Where 3306 is the port number and db_name is the database name. • Username: The default username for the mysql database is root. • Password: Password of the database

30. Example with mysql database import java.sql.*;   class MysqlCon{   public static void main(String[] args) { try{ Class.forName("com.mysql.jdbc.Driver"); Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/db_name","root","root"); Statement stmt=con.createStatement(); ResultSet rs=stmt.executeQuery("select * from emp"); while(rs.next()) System.out.println(rs.getInt(1)+"  "+rs.getString(2)+"  "+rs.getString(3)); con.close(); } catch (Exception e) { e.printStackTrace(); } } }  

31. Load the jar file Two ways to load the jar file: • paste the mysqlconnector.jar file in jre/lib/ext folder • set classpath jar file : mysql-connector-java-5.0.8-bin.jar As we describe before...

32. Connectivity with Access without DSN • There are two ways to connect java application with the access database. • Without DSN (Data Source Name) • With DSN

33. Example with access without DSN import java.sql.*;   class Test{   public static void main(String ar[]){    try{      String database="student.mdb";//Here database exists in the current directory      String url="jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};                       DBQ=" + database + ";DriverID=22;READONLY=true";      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");      Connection c=DriverManager.getConnection(url);      Statement st=c.createStatement();      ResultSet rs=st.executeQuery("select * from login");      while(rs.next()){       System.out.println(rs.getString(1));      }   }catch(Exception ee){System.out.println(ee);}   }}  

34. Example with access with DSN To connect java application with type1 driver, create DSN first, here we are assuming your dsn name is mydsn. import java.sql.*;   class Test{   public static void main(String ar[]){    try{      String url="jdbc:odbc:mydsn";      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");      Connection c=DriverManager.getConnection(url);      Statement st=c.createStatement();      ResultSet rs=st.executeQuery("select * from login");      while(rs.next()){       System.out.println(rs.getString(1));      }   }catch(Exception ee){System.out.println(ee);}   }}  

35. ResultSet interface • The object of ResultSet maintains a cursor pointing to a particular row of data. Initially, cursor points to before the first row. • But we can make this object to move forward and backward direction by passing either TYPE_SCROLL_INSENSITIVE or TYPE_SCROLL_SENSITIVE in createStatement(int,int) method as well as we can make this object as updatable by: Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,                        ResultSet.CONCUR_UPDATABLE);  

36. Type of ResultSet

37. Commonly used methods of ResultSet interface

38. Example of Scrollable ResultSet import java.sql.*;   class FetchRecord{   public static void main(String args[])throws Exception{   Class.forName("oracle.jdbc.driver.OracleDriver");   Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","oracle");   Statement stmt=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);   ResultSet rs=stmt.executeQuery("select * from emp765");   //getting the record of 3rd row   rs.absolute(3);   System.out.println(rs.getString(1)+" "+rs.getString(2)+" "+rs.getString(3));   con.close();   }}  

39. PreparedStatement interface • The PreparedStatement interface is a subinterface of Statement. It is used to execute parameterized query. • Let's see the example of parameterized query: • String sql="insert into emp values(?,?,?)";  

40. Why use PreparedStatement? • Improves performance: The performance of the application will be faster if you use PreparedStatement interface because query is compiled only once.

41. Methods of PreparedStatement interface

42. Example of PreparedStatement interface that inserts the record create table emp(id number(10),name varchar2(50));   import java.sql.*;   class InsertPrepared{   public static void main(String args[]){   try{   Class.forName("oracle.jdbc.driver.OracleDriver");   Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","oracle");   PreparedStatement stmt=con.prepareStatement("insert into Emp values(?,?)");   stmt.setInt(1,101);//1 specifies the first parameter in the query   stmt.setString(2,"Ratan");   int i=stmt.executeUpdate();   System.out.println(i+" records inserted");  con.close();   }catch(Exception e){ System.out.println(e);}     }}  

43. Example of PreparedStatement interface that updates the record PreparedStatement stmt=con.prepareStatement("update emp set name=? where id=?");   stmt.setString(1,"Sonoo");//1 specifies the first parameter in the query i.e. name   stmt.setInt(2,101);   int i=stmt.executeUpdate();   System.out.println(i+" records updated");  

44. CallableStatement • CallableStatement • Used to execute a call to a database stored procedure. • Three types of parameters exist: IN, OUT, and INOUT. • The PreparedStatement object only uses the IN parameter. • The CallableStatement object can use all the three.

45. CallableStatement • Suppose, you need to execute the following Oracle stored procedure − CREATE OR REPLACE PROCEDURE getEmpName (EMP_ID IN NUMBER, EMP_FIRST OUT VARCHAR) AS BEGIN SELECT first INTO EMP_FIRST FROM Employees WHERE ID = EMP_ID; END; CallableStatement cstmt = null; try { String SQL = "{call getEmpName (?, ?)}"; cstmt = conn.prepareCall (SQL); . . . } catch (SQLException e) { . . . } finally { cstmt.close(); }

46. How to get the instance of CallableStatement? • The prepareCall() method of Connection interface returns the instance of CallableStatement. Syntax is given below: • public CallableStatement prepareCall("{ call procedurename(?,?...?)}"); • The example to get the instance of CallableStatement is given below: • CallableStatement stmt=con.prepareCall("{call myprocedure(?,?)}");

47. Example call stored procedure using JDBC create table user420(id number(10), name varchar2(200));  create or replace procedure "INSERTR"   (id IN NUMBER,   name IN VARCHAR2)   is   begin   insert into user420 values(id,name);   end;   /      import java.sql.*;   public class Proc {   public static void main(String[] args) throws Exception{   Class.forName("oracle.jdbc.driver.OracleDriver");   Connection con=DriverManager.getConnection(   "jdbc:oracle:thin:@localhost:1521:xe","system","oracle");   CallableStatement stmt=con.prepareCall("{call insertR(?,?)}");   stmt.setInt(1,1011);   stmt.setString(2,"Amit");   stmt.execute();   System.out.println("success");   }   }  

48. Example to call the function using JDBC create or replace function sum4   (n1 in number,n2 in number)   return number   is    temp number(8);   begin   temp :=n1+n2;   return temp;   end;   /   import java.sql.*;   public class FuncSum {   public static void main(String[] args) throws Exception{   Class.forName("oracle.jdbc.driver.OracleDriver");   Connection con=DriverManager.getConnection(   "jdbc:oracle:thin:@localhost:1521:xe","system","oracle");   CallableStatement stmt=con.prepareCall("{?= call sum4(?,?)}");   stmt.setInt(2,10);   stmt.setInt(3,43);   stmt.registerOutParameter(1,Types.INTEGER);   stmt.execute();   System.out.println(stmt.getInt(1));   }   }  

49. Transaction Management in JDBC • Transaction represents a single unit of work. • The ACID properties describes the transaction management well. • ACID stands for Atomicity, Consistency, isolation and durability.

50. ACID • Atomicity means either all successful or none. • ConsistencyA transaction reaching its normal end, thereby committing its results, preserves the consistency of the database. In other words, each successful transaction by definition commits only legal results. • Isolation ensures that transaction is isolated from other transaction. users should be able to work in isolation, working as though he or she is the only user. Each set of changes must be isolated from those of the other users. • Durability means once a transaction has been committed, it will remain so, even in the event of errors, power loss etc.