JDBC

1. JDBC

2. Introduction to JDBC • What is JDBC? JDBC stands for Java Database Connectivity, which is a standard Java API for database-independent connectivity between the Java programming language and a wide range of databases.

3. JDBC ARCHITECTURE • The JDBC API supports both two-tier and three-tier processing models for database access but in general JDBC Architecture consists of two layers: • JDBC API: This provides the application-to-JDBC Manager connection. • JDBC Driver API: This supports the JDBC Manager-to-Driver Connection.

4. Architecture of jdbc

5. Components of jdbc • JDBC API • Provides various methods and interfaces for communication with databases • JDBC DriverManager • Loads database-specific drivers • JDBC Test Suite • Used to test operation being performed by JDBC drivers • JDBC-ODBC Bridge • Connects database drivers to database.

6. Jdbc Driver types

7. DBMS Call • Type 1 Driver(JDBC-ODBC Bridge Driver) JDBC Call JDBC-ODBC Bridge Driver ODBC Driver JDBC API ODBC API Java APP

8. In this type, JDBC-ODBC bridge acts as an interface between client and database server. •  The driver converts JDBC method calls into ODBC function calls. The bridge is usually used when there is no pure-Java driver available for a particular database.

9. The driver is platform-dependent as it makes use of ODBC which in turn depends on native libraries of the operating system. • Also, using this driver has got other dependencies such as ODBC must be installed on client machine.

10. Advantage of type-1 driver • Allows you to communicate with all the databases supported by ODBC driver. • Represents vendor independent driver.

11. Disadvantage of type-1 driver • Performance overhead since the calls have to go through the JDBC overhead bridge to the ODBC driver. •  The ODBC driver needs to be installed on the client machine •  considering the client-side software needed, this might not be suitable for applets.

12. Native Call • Type 2 Driver(JAVA To Native API) JDBC Call Type-2 Driver DBMS Specific Native API JDBC API Java APP

13. The JDBC type 2 driver, is a database driver implementation that uses the client-side libraries or native libraries of the database. • The driver converts JDBC method calls into native calls which is written in C,C++

14. Advantage of type – 2 Driver • Better performance than Type 1 since no jdbc to odbc translation is needed

15. Disadvantage of type – 2 Driver • The vendor client library needs to be installed on the client machine. • Cannot be used in internet due the client side software needed  • Not all databases give the client side library 

16. Type – 3 Driver(java to network protocol) Server Specific DBMS Call • JDBC API JDBC Call Middle-ware Server Type-3 Driver Java APP Server Driver

17. Type-3 Driver translates JDBC calls into database server independent and middleware server specific calls. • With the help of middleware server the translated JDBC calls are further translated into database specific calls. • The middleware server can be added in an application with some additional functionality, such as pool management, performance improvement and connection availability.

18. Advantage of type – 3 driver • Since the communication between client and the middleware server is database independent, there is no need for the vendor db library on the client machine.  •  The Middleware Server (Can be a full fledged J2EE Application server) can provide typical middleware services like caching (connections, query results, and so on), load balancing etc

19. Disadvantage • Requires database-specific coding to be done in the middle tier.  • An extra layer added may result in a time-bottleneck  • It performs tasks slowly due to increased no. of n/w calls • It is costlier

20. Type- 4 Driver(java to database driver) DB Specific Call • Java APP JDBC Call Type-4 Driver DBMS Specific Network Protocol JDBC API

21. Type-4 Driver is pure java driver, which implements the database protocol to interact directly with database. • This type of driver does not require any native library • Type-4 Driver translates JDBC calls into database specific n/w calls. •  It is installed inside the Java Virtual Machine of the client.

22. Advantage of type-4 driver •  Web application mainly used this driver.  • Serves as pure java driver and auto downloadable • Does not require native library • Does not require middleware server

23. Disadvantage • There is a separate driver needed for each database at the client side.

24. JDBC API • If any java application or an applet wants to connect with a database then there are various classes and interfaces available in java.sql package. • Depending on the requirements these classes and interfaces can be used.

25. The java.sql package contains following classes. • Date • DriverManager • DriverPropertyInfo • SQLPermission • Time • TimeStamp (represents both time and date including nanoseconds ) • Types

26. The java.sql package contains following interfaces: • Driver • Connection • Statement • PreparedStatement • CallableStatement • ResultSet • Blob • Clob • ResultSetMetaData(display no.ofcols,name of cols and datatype of cols) • DatabaseMetaData(display the type of driver we are using)

28. The javax.sql package contains following classes and interfaces • DataSource (general-purpose mechanism for connecting to a database and making SQL based queries and updates.) • Connection and statement Pooling(connection pooling means that connections are reused rather than created each time a connection is requested. To facilitate connection reuse, a memory cache of database connections, called a connection pool, is maintained by a connection pooling.) • Distributed transaction • Rowsets

29. Statement Interface • There are three types of statement interfaces :- Simple Statement Prepared Statement Callable Statement

30. Step for using jdbc • Import java.sql package • import java.sql.*; • Load the driver • Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”); • Establish connection to the database • Connection con = Driver.getConnection(“jdbc:odbc:database”); • Create a statement • Statement stmt = con.createStatement();

31. Execute the statement • ResultSet res = stmt.executeQuery(“select * from database”); • Retrieve the results • while(res.next()) • Close the connection and statement • con.close();

32. Simple statement • The Statement interface is used to execute a static query. • It’s a very simple and easy so it is also called as “Simple Statement”. • The statement interface has several methods for execute the SQL statements and also get the appropriate result as per the query sent to the database.

33. Example java database connectivity • import java.sql.Connection; • import java.sql.DriverManager; • import java.sql.SQLException; • public class DBConnection { • String driverName = "com.mysql.jdbc.Driver"; • String url = "jdbc:mysql://localhost:3306/test"; • String userName = "root"; • String password = "admin"; String query = “select * from student” ;

34. public Connection getConnection() { • try { • Class.forName(driverName); • Connection conn = DriverManager.getConnection(url, userName, password); • Statement stmt = con.createStatement(); • ResultSet rs = stmt.executeUpdate(query); • System.out.println(“col1\tcol2\tcol3”); • while(rs.next()){ • System.out.println(rs.getString(“col1”)+”\t”); • System.out.println(rs.getInt(“col2”)+”\t”); • System.out.println(rs.getInt(“col3”)); • } • }

35. catch (ClassNotFoundException e) { • e.printStackTrace(); • } catch (SQLException e) { • e.printStackTrace(); • } • return conn; • } • public static void main(String[] args) { • DBConnectiondbc = new DBConnection(); • dbc.getConnection(); • } • }

36. Prepared statement • Use when you plan to use the SQL statements many times. • The PreparedStatement interface accepts input parameters at runtime. • The PreparedStatement interface, is subclass of the Statement interface, can be used to represent precompiled query, which can be executed multiple times.

38. import java.sql.*; public class PreparedStatement{ Class.forName(“oracle.jdbc.driver.OracleDriver”); Connection con = DriverManager.getConnection(“jdbc:oracle:thin:local host”,”scott”,’’tiger”); String query=“insert into stu values(?,?,?)”; PreparedStatement ps = con.prepareStatement(query); ps.setString(1,”abbc”); ps.setInt(2,38); ps.setDouble(3,12.34);

39. int i = ps.executeUpdate(); System.out.println(“record inserted successfully:”+i); ps.setString(1,”abbc2”); ps.setInt(2,39); ps.setDouble(3,14.34); i=ps.executeUpdate(); System.out.println(“record inserted once again”+i); con.Close(); } }

40. Callable statement • A java.sql.CallableStatement interface object is used to call stored procedures from the database. It is the standard way to execute stored procedure for all DBMS/RDBMS. A stored procedure is an object stored in a database. • A procedure with IN and OUT parameter can be executed only in this Callable Statement • An OUT parameter in the stored procedure is represented by the ? • An OUT parameter is registered using registerOUTParameter() method

41. After the CallableSatement() is executed, the OUT parameter are to be obtained using the getXXX() method • For eg : • registerOUTParamet(intindex,Type type) • where • index – is the relative position of OUT parameter in SQL statement • type – is the SQL data type of OUT parameter

42. CallableStatement csmt = con.prepareCall(Execute emp(?,?,?)); • csmt.setString(1,”Ruchi”); • csmt.setInt(2,5000); • csmt.registerOutParameter(3,Types.LONGVARCHAR); • ResultSet rs= csmt.executeQuery();

43. SQL statement for stored procedure  CREATE OR REPLACE PROCEDURE proc1( in_sregno NUMBER, out_sname OUT varchar2, out_m1 OUT number, out_m2 OUT number ) is temp_sregno number; temp_sname VARCHAR2(10); temp_m1 NUMBER; temp_m2 number; Declaration Section

44.  BEGIN  SELECT sregno,sname,m1,m2 INTO temp_sregno, temp_sname,temp_m1,temp_m2 FROM mark WHERE sregno = in_sregno; out_sname : = temp_sname; out_sname : = temp_m1; out_sname : = temp_m1; • END; Execution Section

45. public class Callb{ public static void main(String[] args) { int in_sregno; int ret_code; Connection con = null; try{ Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”); String url = “jdbc:odbc:stu”; conn = DriverManager.getConnection(url,”scott”,”tiger”); in_sregno=1111; CallableStatement csmt = con.prepareCall(“{call proc1(?,?,?,?) }”);

46. csmt.setInt(1,in_sregno); csmt.registerOutParameter(2, Types.VARCHAR); csmt.registerOutParameter(3, Types.INTEGER); csmt.registerOutParameter(4, Types.INTEGER); csmt.executeUpdate(); String o_sname = csmt.getString(2); int o_m1 = csmt.getInt(3); int o_m2 = csmt.getInt(4);

47. System.out.println(“sregno”+”\t”+”name”+”mark1”+\t+”mark2”);System.out.println(“sregno”+”\t”+”name”+”mark1”+\t+”mark2”); • System.out.println(in_sregno+”\t”+o_sname+”\t”+o_m1+”\t”+o_m2); • csmt.close(); • con.close(); • } • catch(SQLException e) • { ret_code = e.getErrorCode(); System.out.println(ret_code+e.getMessage()); con.close(); } } }

48. ResultSet Interface • The executeQuery() and getResultSet() when called on Statement, PreparedStatement and CallableStatement returns objects of type ResultSet. • The ResultSet objects contain results after the execution of SQL statements. • The next() method moves cursor to the next row of result set

49. Example using resultset import java.sql*; public class res{ public static void main(String[] args) { Statement stmt; ResultSet reset; String sql; try{ Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);

50. Connection con = DriverManager.getConnection(“jdbc:odbc:stu_base”); stmt=con.createStatement(); sql=“select name from stu”; reset=stmt.executeQuery(sql); System.out.println(“Name\n”); while(reset.next()) System.out.println(reset.getString(“name”)); stmt.close(); con.close(); }