JDBC

1. JDBC

2. JDBC is Java API twhich allows the Java programmers to access database management system from Java code. • It is a java API which enables the java programs to execute SQL statements. • Defines how a java programmer can access the   database in tabular format from Java code using a set of  standard interfaces and classes written in the Java programming language. • JDBC provides methods for querying and  updating the data in Relational Database Management system  such as SQL, Oracle etc

3. JDBC Driver Types • Type 1 JDBC-to-ODBC Driver. • Type 2 Java/Native Code Driver. • Type 3 JDBC Driver. • Type 4 JDBC Driver.

4. Type 1 JDBC-to-ODBC DriverThe JDBC type 1 driver, also known as the JDBC-ODBC bridge is a database driver implementation that employs the ODBC driver to connect to the database. The driver converts JDBC method calls into ODBC function calls. The driver is implemented in the sun.jdbc.odbc.JdbcOdbcDriver class and comes with the Java 2 SDK, Standard Edition. 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 the computer having the driver and the database which is being connected to must support an ODBC driver. Type 1 is the simplest of all but platform specific i.e only to Microsoft platform. Type 1 drivers are "bridge" drivers. They use another technology such as Open Database Connectivity (ODBC) to communicate  with a database. This is an advantage because ODBC drivers exist for many Relational Database Management System (RDBMS) platforms. A Type 1 driver needs to have the bridge driver installed and configured before JDBC can be used with it. This can be a serious drawback for a production application.

5. Functions: •  Translates query obtained by JDBC into corresponding ODBC query, which is then handled by the ODBC driver.  •  Sun provides a JDBC-ODBC Bridge driver. sun.jdbc.odbc.JdbcOdbcDriver. • Client -> JDBC Driver -> ODBC Driver -> Database • There is some overhead associated with the translation work to go from JDBC to ODBC. Advantages:Almost any database for which ODBC driver is installed, can be accessed. Disadvantages: • Performance overhead since the calls have to go through the JDBC overhead bridge to the ODBC driver, then to the native database connectivity interface. • The ODBC driver needs to be installed on the client machine.

6. Type 1 JDBC Driver

7. Type 2 Java/Native Code Driver • The JDBC type 2 driver, also known as the Native-API driver is a database driver implementation that uses the client-side libraries of the database. • The driver converts JDBC method calls into native calls of the database API. • A native-API partly Java technology-enabled driver converts JDBC calls into calls on the client API for Oracle, Sybase, Informix, DB2, or other DBMS. • Type 2 drivers use a native API to communicate with a database system.

8. Functions: • This type of driver converts JDBC calls into calls to the client API for that database. • Client -> JDBC Driver -> Vendor Client DB Library -> Database AdvantageBetter performance than Type 1 since no jdbc to odbc translation is needed. Disadvantages • The vendor client library needs to be installed on the client machine. • Not all databases give the client side library.

9. Type 2 JDBC Driver

10. Type 3 JDBC Driver • The JDBC type 3 driver, also known as the network-protocol driver is a database driver implementation which makes use of a middle-tier between the calling program and the database. • The middle-tier (application server) converts JDBC calls directly or indirectly into the vendor-specific database protocol. • The same driver can be used for multiple databases. It depends on the number of databases the middleware has been configured to support. • Making use of the middleware provides additional advantages of security and firewall access. • A net-protocol fully Java technology-enabled driver translates JDBC API calls into a DBMS-independent net protocol which is then translated to a DBMS protocol by a server.

11. Functions: • Follows a three tier communication approach. • Can interface to multiple databases - Not vendor specific. • The JDBC Client driver written in java, communicates with a middleware-net-server using a database independent  protocol, and then this net server translates this request into database commands for that database. • Thus the client driver to middleware communication is database independent. • Client -> JDBC Driver -> Middleware-Net Server -> Any Database • Advantages • 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. Also the client to middleware need'nt be changed for a new database. eg. Weblogic. • At client side a single driver can handle any database.(It works provided the middlware supports that database) Disadvantages • Requires database-specific coding to be done in the middle tier. •  An extra layer added may result in a time-bottleneck. But typically this is overcome by providing efficient middleware

12. Type 3 JDBC Driver

13. Type 4 JDBC Driver • The JDBC type 4 driver, also known as the native-protocol driver is a database driver implementation that converts JDBC calls directly into the vendor-specific database protocol. • The type 4 driver is written completely in Java and is hence platform independent. • Since many of these protocols are proprietary the database vendors themselves will be the primary source for this style of driver.

14. Functions • Type 4 drivers are entirely written in Java that communicate directly with a vendor's database. No translation or middleware layers, are required, improving performance. • The driver converts JDBC calls into the vendor-specific database protocol so that client applications can communicate directly with the database server. • Completely implemented in Java to achieve platform independence. • e.g include the widely used Oracle thin driver - oracle.jdbc.driver. OracleDriver which connect to jdbc:oracle:thin URL format. • Client Machine -> Native protocol JDBC Driver -> Database server AdvantagesThese drivers don't translate the requests into db request to ODBC or pass it to client api for the db, nor do they need a middleware layer for request indirection. Thus the performance is considerably improved. DisadvantageAt client side, a separate driver is needed for each database.

15. Type 4 JDBC Driver

16. JDBC Drivers JDBC Type I “Bridge” ODBC ODBC Driver Type II “Native” .lib Middleware Server Type III “Middleware” Type IV “Pure”

17. Brief Overview of the JDBC Process • Loading the JDBC driver • Connecting to the DBMS • Creating and executing a statement. • Processing data returned by the DBMS • Terminating the connection with the DBMS

18. Loading the JDBC driver The JDBC driver must be loaded before connect to the DBMS. The driver is loaded by calling the Class.forName() method and passing it the name of the driver. Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

19. Connecting to the DBMS Once the driver is loaded, connect to the DBMS using method DriverManager.getConnection() The DriverManager.getConnection() method is passed the URL of the database, and the userID and password if required by the DBMS. The URL is a String object that contains the driver name and the name of the database that is being accessed. The DriverManager.getConnection() method returns a Connection interface that is used throughout the process to reference the database.

20. String url =“jdbc:odbc:CustomerInformation”; String userID=“sjec”; String password = “comp” try { Class.forName(“sun.jdbc.odbc.JdbcOdbcDrive”); Connection conn = DriverManager.getConnection(url,userID,password); }

21. Creating and executing a statement. The con.createStatement() method is used to create a statement object. The statement object is then used to execute a query and return a resultset object that contains the response from the DBMS. Statement stmt; ResultSet rs; Try { String query = “SELECT * FROM customers”; stmt = con.createStatement(); rs = stmt.executeQuery(query); con.close(); }

22. Processing data returned by the DBMS The java.sql.ResultSet object is assigned the results received from the DBMS after the query is processed. The java.sql.ResultSet object consists of methods used to interact with data. ResultSet rs; String FirstName; String LastName; while(rs.next()); { FirstName = rs.getString(FirstName); LastName = rs.getString(LastName); System.out.println(FirstName+” “+LastName); }

23. Terminating the connection with the DBMS The connection to the DBMS is terminated by using close() method of the Connection object once finished accessing the DBMS. con.close();

24. JDBC Architecture Java Application JDBC Driver Manager (T1) JDBC- ODBC Bridge (T2) Java Portion (T3) Java Client (T4) Java Driver to vendor's protocol JDBC Drivers Native Portion ODBC Driver Server Component Proprietary, vendor-specific database access protocol Provided with Sun's JDK

25. Database Connection Before the connection is made, the JDBC driver must be loaded and registered with the DriverManager. The purpose of loading and registering the JDBC driver is to bring the JDBC driver into the JVM. The JDBC driver is automatically registered with the DriverManager once the JDBC driver is loaded and is therefore available to the JVM and can be used. The Class.forName() methods throws a ClassNotFoundException. try { Class.forName(“sun.jdbc.odbc.JdbcOdbcDrive”); } catch(ClassNotFoundException error) { System.out.println(“Unable to load JDBC/ODBC bridge”+error.getMessage()); }

26. The Connection • After the JDBC driver is successfully loaded and registered, connect to the database. • A Connection object represents a connection with a database. When we connect to a database by using connection method, we create a Connection Object, which represents the connection to the database. • The datasource that the JDBC component will connect to is defined using the URL format. The URL consist of 3 parts. These are jdbc which indicates that the JDBC protocol is to be used to read the URL. <subprotocol> which is the JDBc driver name. <subname> which is the name of the database. ex:String url = “jdbc:odbc:sjec”;

27. The connection to the databse is established by using one of the 3 getConnection method. getConnection(String url) getConnection(String url,String id,String password); getConnection(String url, Properties prop); Ex: getConnection(“jdbc:odbc:sjec”, ”cse”, ”sem7”);

28. The getConnection() method request access to the database from the DBMS. It is up to the DBMS to grant or reject access. • A connection object is returned by the getConnection() method if access is granted. otherwise getConnection() method throws a SQLException. • Sometimes the DBMS grants access to a database to anyone. • In this case getConnection(String url) method is used.

29. String url = “jdbc:odbc:sjec”; try { Class.forName(“sun.jdbc.odbc.JdbcOdbcDrive”); conn = DriverManager.getConnection(url) } catch(ClassNotFoundException error) { System.out.println(“Unable to load JDBC/ODBC bridge”+error.getMessage()); } catch(SQLException error) { System.out.println(“can not connect to the datanase”+error.getMessage()); }

30. Some databases limit access to authorized users and require to supply a userID and password . String url =“jdbc:odbc:CustomerInformation”; String userID=“blde”; String password = “cse” Private Connection conn; try { Class.forName(“sun.jdbc.odbc.JdbcOdbcDrive”); conn = DriverManager.getConnection(url,userID,password); } catch(ClassNotFoundException error) { System.out.println(“Unable to load JDBC/ODBC bridge”+error.getMessage()); } catch(SQLException error { System.out.println(“can not connect to the datanase”+error.getMessage()); }

32. Some DBMS requires additional information. This additional information is referred to as properties and must be associated with a Properties object, which is passed to the DBMS as a getConnection() parameter. Connection con ; Properties props = new Properties(); try { FileInputStream fis = new FileInputStream(“DBProps.txt”); props.load(fis); } catch(IOException err) { System.out.println(“error loading property file”); } try { Class.forName(“sun.jdbc.odbc.JdbcOdbcDrive”); con = DriverManager.getConnection(url,props); } catch(ClassNotFoundException error) { System.out.println(“Unable to load JDBC/ODBC bridge”+error.getMessage()); } catch(SQLException error) { System.out.println(“can not connect to the datanase”+error.getMessage()); }

33. Associating the JDBC/ODBC bridge with the Database • select start | settings | control panel • select ODBC 32 to display the ODBC DataSourceAdministrator • Add new user by selecting the add button • select the driver and then finish • Enter the name of the datasourcename • . … . .. ..

34. Associating the JDBC/ODBC bridge with the Database 1. Make sure you have the Access 95 ODBC drivers installed. These ODBC drivers can be installed from theAccess install program. 2. Select Start Menu|Settings|Control Panels. 3. Click on 32 bit ODBC. 4. Click on the Add button and choose the Access Driver. 5. Type in a Data Source Name and Description (anything you like). 6. In the Database area, click on Select. 7. Select the Access database file; a sample database is located in MSoffice\ACCESS\Samples (if you installed it during the Access installation). However, you can specify any Access database you want. 8. You may want to click on the Advanced button and set the Username and Password. Click on OK and then on Close to complete the configuration

35. Statement Objects Once a connection to the database is opened, then create and sends a query to access data contained in the database. The query is written SQL. One of the 3 types of Statement objects is used to execute the query. • Statement which executes a query immediately. • PrepapedStatement which is used to execute a compiled query • CallableStatement, which is used to execute stored procedures.

36. The Statement Object • It is used whenever a J2EE component needs to immediately execute a query without first having the query compiled. • There are 3 methods in Statement Object 1. executeQuery() : Returns one ResultSet object that contains rows, columns and metadata. 2. executeUpdate(); method is used to execute queries that contain UPDATE and DELETE SQL statement. • execute(); method is used when there may be multiple results returned. file:///C:\Java\jdk1.6.0_12\bin\statement.java

37. PreparedStatement Object • A SQL query must be compiled before the DBMS processes the query. • A SQL query can be precompiled and executed by using the PreparedStatement object. • Here we use a question mark as place holder for a value that is inserted into the query after the query is compiled. This value changes each time the query is executed. • We use preparedStatement() method to replace the question mark value with the value passed to the setXXX() method. • The setXXX() method takes two parameters. First is an integer that identifies question mark place holder. And second is value that replaces question mark placeholder. • file:///C:\Java\jdk1.6.0_12\bin\PreparedStatement.java

38. CallableStatement • The CallableStatement object is used to used to call a stored procedure. • The CallableStatement object uses 3 types of parameters when calling a stored procedure. These parameters are IN, OUT and INOUT. • The IN parameter contain any data that needs to be passed to the stored procedure and whose value is assigned using the setxxx() method. • The OUT parameter contain the value returned by the stored procedure. The OUT parameter must be registered using the registerOutParameter() and is retrieved by getxxx() method. • The INOUT parameter is a single parameter used to both pass information to the stored procedure and retrieve information from a stored procedure.The registerOutParameter () requires 2 parameters. The first parameter is the integer that represents the number of the parameter. The second parameter is the data type of the value returned by the stored procedure. • file:///C:\Java\jdk1.6.0_12\bin\CallableStatement.java

39. ResultSet • The ResultSet object contains methods that are used to copy data from the Result into a Java variable for further processing. • Data in a ResultSet object is logically organized into a virtual table consisting of rows and column. • Resultset object also contains metadata such as column names, column size, and column data types. • The ResultSet uses a virtual curser to point to a row of the virtual table. • We must move the virtual curser to each row and use other methods of the ResultSet object to interact with the data stored in columns of that row. • The virtual curser is positioned above the first row of the data when the ResultSet is returned by the executeQuery() method. This means that virtual curser must be moved to the first row using next() method.

40. The next() method returns a boolean true if the row contains a data. Otherwise, a boolean false is returned indicating that no more rows exist in the ResultSet. • Once the virtual curser points to a row, the getxxx() method is used to copy data from the row to a collection , object or variable. • The getxxx() parameter requires one parameter, which is an integer that represent he number of column that contains the data. Ex: getInt(1); getString(2); getInt(3);

41. String url =“jdbc:odbc:CustomerInformation”; String userID=“blde”; String password = “ise” Private Connection conn; try { Class.forName(“sun.jdbc.odbc.JdbcOdbcDrive”); conn = DriverManager.getConnection(url,userID,password); } catch(ClassNotFoundException error) { System.out.println(“Unable to load JDBC/ODBC bridge”+error.getMessage()); } catch(SQLException error) { System.out.println(“can not connect to the datanase”+error.getMessage()); }

42. try { String query = “SELECT FirstName, LastName FROM Customers”; stmt = con.createStatement(); rs = stmt.executeUpdate(query); con.close(); } catch(SQLException error) { System.out.priintln( “cannot connect to the database”); } try { while(rs.next()) { FirstName = rs.getString(1); LastName = rs.getString(2); System.out.println(FirstName+” “+LastName); } } Catch(SQLException error) { System.out.println(“data entry error”); }

43. Scrollable ResultSet There are 6 methods of the ResultSet object that are used to position the virtual cluster. next() previous() first() last() absolute() relative() getRow() The absolute method positions the virtual curster at the row specified by the integer passed as a parameter to the absolute() method. The relative() method moves the virtual curser the specified number of rows contained in the parameter. The parameter is a positive or negative integer where the sign represents the direction the virtual curser is moved. getRow() method returns an integer that represents the number of current rows in the ResultSet.

44. The Statement object that is created using the createStatement() of the Connection object must be setup to handle a scrollable ResultSet by passing the createStatement() method one of three constant. TYPE_FORWARD_ONLY TYPE_SCROLL_INSENSITIVE TYPE_SCROLL_SENSITIVE. TYPE_FOREWARD_ONLY is the default setting. The TYPE_SCROLL_INSENSITIVE constant makes the Resultset insensitive to the changes made by another component.

45. String url =“jdbc:odbc:CustomerInformation”; String userID=“blde”; String password = “ise” Private Connection conn; try { Class.forName(“sun.jdbc.odbc.JdbcOdbcDrive”); conn = DriverManager.getConnection(url,userID,password); } catch(ClassNotFoundException error) { System.out.println(“Unable to load JDBC/ODBC bridge”+error.getMessage()); } catch(SQLException error) { System.out.println(“can not connect to the datanase”+error.getMessage()); }

46. try { String query = “SELECT FirstName, LastName FROM Customers”; stmt = con.createStatement(TYPE_SCROLL_INSENSITIVE); rs = stmt.executeUpdate(query); con.close(); } catch(SQLException error) { System.out.priintln( “cannot connect to the database”); } try { while(rs.next()) { rs.last(); rs.first(); FirstName = rs.getString(1); LastName = rs.getString(2); System.out.println(FirstName+” “+LastName); } } catch(SQLException error) { System.out.println(“data entry error”); }

47. Updatable ResultSet Rows contained in the ResultSet can be updatable by passing the createStatemenet() method of the method of the Connection object the CONQUER_UPDATABLE. Alternatively , the CONQUER_READ_ONLY constant can be passed to the createStatement method to prevent the ResultSet from being updated. There are 3 ways in which a ResultSet can be channged. 1 updating values in a row 2 deleting a row 3 inserting a new row.

48. Update ResultSet • Once a executeQuery method of the Statement object returns a ResultSet, the updatexxx() method is used to change the value of a column in the current row of the resultset. • The updatexxx method required 2 parameters. • The update Row() method is called after all the updatexxx() methods are called. • The changes only occur in the ResultSet. The corresponding row in the table remains unchanged.

49. Update ResultSet String url = "jdbc:odbc:CustomerInformation"; String userID = "jim"; String password = "keogh"; Statement st; ResultSet rs; Connection con; try { Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver"); con = DriverManager.getConnection(url,userID,password); } catch (ClassNotFoundException error) { System.err.println("Unable to load the JDBC/ODBC bridge." + error); System.exit(1); } catch (SQLException error) { System.err.println("Cannot connect to the database." + error); System.exit(2); }

50. try { String query = "SELECT FirstName,LastName FROM Customers WHERE FirstName = 'Mary' and LastName = 'Smith'"; st = con.createStatement(ResultSet.CONCUR_UPDATABLE); rs = st.executeQuery (query); } catch ( SQLException error ) { System.err.println("SQL error." + error); System.exit(3); } boolean Records = Results.next(); try { Results.updateString ("LastName", "Smith"); Results.updateRow(); st.close(); } catch (SQLException error ) { System.err.println("Data display error." + error); System.exit(5); }