JDBC

1. JDBC

2. What is JDBC? • Java Database Connectivity (JDBC) is an API for the Java programming language that defines how a client may access a database. • provides methods for querying and updating data in a database. • JDBC has been part of the Java Standard Edition . • JDBC classes are contained in the Java package java.sql. • JDBC connections support creating and executing statements. • These may be update statements such as SQL's CREATE, INSERT, UPDATE and DELETE, or they may be query statements such as SELECT. • stored procedures may be invoked through a JDBC connection.

3. JDBC Driver Types • Type 1 JDBC-ODBC Driver • Type 2 Native API • Type 3 Net-protocol • Type 4 Native-protocol

4. Type 1 JDBC-ODBC Driver(1)

5. Type 1 JDBC-ODBC Driver(2) • The 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 bridge is usually used when there is no pure-Java driver available for a particular database. • 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. This driver is native code and not Java, and is closed source. • Client -> JDBC Driver -> ODBC Driver -> Database • There is some overhead associated with the translation work to go from JDBC to ODBC.

6. Type 1 JDBC-ODBC Driver(3) • Advantages • Almost any database for which ODBC driver is installed, can be accessed. • A type 1 driver is easy to install. • Disadvantages • Performance overhead since the calls have to go through the JDBC overhead bridge to the ODBC driver, then to the native db connectivity interface. • The ODBC driver needs to be installed on the client machine. • Considering the client-side software needed, this might not be suitable for applets. • Will not be suitable for internet applications.

7. Type 2 Native API (1)

8. Type 2 Native API (2) • 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. • The type 2 driver is not written entirely in Java as it interfaces with non-Java code that makes the final database calls. The driver is compiled for use with the particular operating system.

9. Type 2 Native API (3) • Advantages • Better 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. • Cannot be used in web-based application due the client side software needed. • Not all databases have a client side library

10. Type 3 Net-protocol(1)

11. Type 3 Net-protocol(2) • The JDBC type 3 driver, also known as the Pure Java Driver for Database Middleware, 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 type 3 driver is written entirely in Java. • 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. • Client -> JDBC Driver -> Middleware-Net Server -> Any Database

12. Type 3 Net-protocol(3) • 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 not be changed for a new database. • Eg. for the above include jdbc driver features in Weblogic. • Can be used in internet since there is no client side software needed. • 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.

13. Type 4 Native-protocol (1)

14. Type 4 Native-protocol (2) • The JDBC type 4 driver, also known as the Direct to Database Pure Java 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. It is installed inside the Java Virtual Machine of the client. Type 4 drivers are entirely written in Java that communicate directly with a vendor's database, usually through socket connections. 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. • Client Machine -> Native protocol JDBC Driver -> Database server

15. Type 4 Native-protocol (3) • Advantages • These drivers don't translate the requests into an intermediary format (such as ODBC), nor do they need a middleware layer to service requests. Thus the performance may be considerably improved. • All aspects of the application to database connection can be managed within the JVM; this can facilitate easier debugging. • Disadvantage • At client side, a separate driver is needed for each database.

16. JDBC API(1) • The JDBC API is available in the java.sql and javax.sql packages. Following are important JDBC classes, interfaces and exceptions in the java.sql package: • 1.DriverManager-Loads JDBC drivers in memory. Can also be used to open connections to a data source. • 2.Connection-Represents a connection with a data source. Is also used for creating Statement, PreparedStatement and CallableStatement objects. • 3.Statement-Represents a static SQL statement. Can be used to retrieve ResultSet object/s.

17. JDBC API(2) • 4.PreparedStatement-Higher performance alternative to Statement object, represents a precompiled SQL statement. • 5.CallableStatement-Represents a stored procedure. Can be used to execute stored procedures in a RDBMS which supports them. • 6.ResultSet-Represents a database result set generated by using a SELECT SQL statement. • 7.SQLException-An exception class which encapsulates database base access errors.

18. JDBC steps • Load the JDBC driver • Define the connection URL • Establish the connection • Create a Statement object • Execute a query or update • Process the results • Close the connection

19. 1.Load the JDBC driver • To load a driver, you specify the classname of the database driver in the Class.forName method. By doing so, you automatically create a driver instance and register it with the JDBC driver manager. • public static Class forName (StringclassName) throws ClassNotFoundException • Example: • try{Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");} • catch(ClassNotFoundExceptione) { }

20. 2.Define the connection URL • In JDBC, a connection URL specifies the server host, port, and database name with which to establish a connection. • Example:Stringurl="jdbc:odbc:test“ (test is DSN)

21. 3. Establish the connection • With the connection URL, username, and password, a network connection to the database can be established. Once the connection is established, database queries can be performed until the connection is closed. • public static Connection getConnection (Stringurl, Stringuser, • Stringpassword) throws SQLException • Example for Oracle: • Connection connection = DriverManager.getConnection(url, “system”,”manager”); • Example for Ms Access: • Connection connection = DriverManager.getConnection(url, “ ”,” ”);

22. 4. Create a Statement object • Creating a Statement object enables you to send queries and commands to the database. • Statement statement = connection.createStatement();

23. Execute Query using various execute methods of Statement Interface • Following are 3 different execute methods available in Statement Interface: 1.executeQuery( ):public ResultSetexecuteQuery(String sql) throws SQLExceptionUsed with select query 2.executeUpdate( ):public intexecuteUpdate(String sql) throws SQLExceptionUsed with insert, update, delete, alter table etc. 3.execute( ):public booleanexecute(String sql) throws SQLExceptionGenerally used with multiple results are generated. Also used with Create table query.

24. 5. Execute a query or update • Given a Statement object, you can send SQL statements to the database by using the execute, executeQuery, executeUpdate, or executeBatch methods. • String query = "SELECT col1, col2, col3 FROM sometable"; • ResultSet resultSet = statement.executeQuery(query);

25. 6.Process the results • When a database query is executed, a ResultSet is returned. The ResultSet represents a set of rows and columns that you can process by calls to next and various getXxx methods. • while(resultSet.next()) { System.out.println(resultSet.getString(1) + " " + resultSet.getString(2) + " " + resultSet.getString("firstname") + " " resultSet.getString("lastname")); }

26. 7.Close the Connection • When you are finished performing queries and processing results, you should close the connection, releasing resources to the database. • connection.close();

27. The Connection Interface • The Connection Interface used to connect to a database is available in the java.sql package. • This interface has methods which can be used to prepare statement. • This interface also has methods that makes changes to the database permanently or temporary. • Method: • close() • commit() • createStatement() • isClosed() • prepareCall() • prepareStatement() • rollback()

28. The Connection Interface Method(1) • close() • This method frees the Connection object’s database and other JDBC resources. • Eg. Connection.close(); • commit() • This method makes all the changes made since the last commit or rollback permanent. • Eg. Connection.commit();

29. The Connection Interface Method(2) createStatement() • This method creates a statement object for sending SQL statements to the database. • Eg. Statement st=Connection.createStatement(); • isClosed() • This method returns true if the connection is close else returns false. • Eg.booleani=Connection.isClose();

30. The Connection Interface Method(3) • prepareCall(String s) • This method creates a CallableStatement object for calling stored procedures. • Eg.CallableStatementcs=Connection.prepareCall(s); • prepareStatement(String s) • This method creates a PreparedStatement object fo sending SQL statements with or without IN parameter. • Eg.PreparedStatementps=Connection.prepareStatement(s);

31. The Connection Interface Method(4) • rollback() • This method undoes all changes made to the database. • Eg.Connection.rollback()

32. The Statement Interface • Statement:  to execute a single query one time. • The Statement interface lets you execute a simple SQL statement with no parameters. • It executes query immediately without having query compiled. • It executes SQL statement that are static & obtain results following their execution. • Method: • Close() • execute() • executeQuery() • executeUpdate() • getMaxRows() • getResultSet()

33. The Statement Interface Method(1) • close() • This method releases the statement object’s database and JDBC resources. • Eg.Statement.close(); • execute(String s) • Generally used with multiple results are generated. Also used with Create table query. • Eg. booleani=Statement.execute(String sql) throws SQLException

34. The Statement Interface Method(2) • executeQuery() • This method executes the SQL statement specified by s and returns the ResultSet object.Used with select query. • Eg.ResultSetrs=Statement.executeQuery(s); • executeUpdate() • Used with insert, update, delete, alter table etc. • Eg.int i=Statement.executeUpdate(s);

35. The Statement Interface Method(3) • getMaxRows() • This method returns the maximum number of rows that are generated by the executeQuery() method. • Eg.int i=Statement.getMaxRows(); • getResultSet() • This method retrives the ResultSet generated by the execute() method. • Eg.ResultSetrs=Statement. getResultSet();

36. The PreparedStatement Interface • Prepared statement: It extends Statement interface which execute a single query multiple times(here the statement will be precompiled and will get better performance) • Compiling a query is an overhead that is acceptable if query is called once, however compiling process becomes overhead if query is executed several times. • A Prepared Statement object is used when an application plans to reuse a statement multiple times. The application prepares the SQL it plans to use. Once prepared, the application can specify values for parameters in the prepared SQL statement. The statement can be executed multiple times with different parameter values specified for each execution. • Another advantage of the Prepared Statement class is the ability to create an incomplete query and supply parameter values at execution time.

37. The PreparedStatement Interface • A question mark is used as a placeholdar for a value that is inserted into the query after the query is compiled. • The prepareStatement() method of the Connection interface is called to return the PreparedStatement object. • The setString() method of the PreparedStatement object is used to replace the qustion mark with the value passed to the setString() method. • The setString() requires two parameters. • First parameter is an integer that identifies the position of the question mark placeholder. • second parameter is the value that replaces the question mark placeholder. • The query is precompiled once and the setString() method called as needed to change the specified values of the query without having to recompiled the query.

38. The PreparedStatement Interface Method(1) • Boolean execute()this method execute the SQL statement in this object. The getResult() method is used to retrive the result. • ResultSet executeQuery() • this method execute the SQL statement in this object. It returns the ResultSet object.

39. The PreparedStatement Interface Method(2) • intexecuteUpdate() • this method execute the SQL statement in this object. The SQL statement must be an SQL insert,update and delete statement. • ResultSetMetaDatagetMetaData() • This method retrives a ResultSetMetaData object that contains information about the columns of the ResultSet object that will be returned when this object is executed.

40. ResultSet • It contains virtual table consisting of rows and columns. • A virtual cursor points to a row of the virtualtable. • 2 types of ResultSet • Scrollable ResultSet • Updatable ResultSet

41. How to make ResultSet Scrollable andUpdatable?

42. The statement object must be set to handle scrollable and updatable .ResultSet.createStatment(intResultSetType,intResultSetConcurrency)ResultSetType-3 ConstantsTYPE_FORWARD_ONLY TYPE_SCROLL_SENSITIVE TYPE_SCROLL_INSENSITIVEResultSetConcurrency-2 ConstantsCONCUR_READ_ONLY CONCUR_UPDATABLE

43. TYPE_FORWARD_ONLY • It is default setting • It restricts the virtual cursor to downward movement. • eg:Statementst=con.createStatement();

44. TYPE_SCROLL_INSENSITIVE • It permits virtual cursor to move in both directions. • It makes result set insensitive to the changes made by another J2EE component. • Eg:Statementst=con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONUR_UPDATABLE);

45. TYPE_SCROLL_SENSITIVE • It permits virtual cursor to move in both directions. • It makes result set sensitive to the changes made byanother J2EE component. • Eg:Statement st=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONUR_UPDATABLE);

46. CONCUR_READ_ONLY • The constant CONCUR_READ_ONLY indicating the concurrency mode for the ResultSet that may not be updated. it mens it is read only READ_ONLY we can’t make any changes in ResultSet. • Eg. • ResultSet.CONCUR_READ_ONLY

47. CONCUR_UPDATABLE • The constant CONCUR_UPDATABLE indicating the concurrency mode for a ResultSet that may be updated. It permits ResultSet for update operation. • Eg. • ResultSet.CONCUR_UPDATABLE

48. Scrollable ResultSet • Until JDBC 2.1 API, the cursor could onlymove down the ResultSet. • Six methods to position the cursor first() last() previous() absolute() relative() next() getrow()

49. Scrollable ResultSet Method(1) • first() • Moves to the first row of ResultSet • Syntax:publicboolean first() • Throws SQLException • Eg:rs.first() • Last() • Moves to the last row of ResultSet • Syntax:publicboolean last() • Throws SQLException • Eg:rs.last()

50. Scrollable ResultSet Method(2) • previous() • Moves to the previous row of ResultSet • Syntax:publicboolean previous() • Throws SQLException • Eg:rs.previous() • next() • Moves to the next row of ResultSet • Syntax:publicboolean next() • Throws SQLException • Eg:rs.next()