16 Java Database Connectivity

1. 16 Java Database Connectivity

2. Objectives • Understand the JDBC • Understand the steps of the JDBC: 1.) Importing packages 2.) Opening a connection to a database 3.) Working with different types of database drivers 4.) Querying the database • Creating a Statement object to perform a query ResultSet or create a PreparedStatement • Executing a query and return a ResultSet object • Differentiating between a Statement and a PreparedStatement

3. Objectives (continued) (Steps of the JDBC, continued) 5.) Processing a ResultSet 6.) Closing the ResultSet and Statement 7.) Importance of closing the connection • Understand a JBDC row set.

4. What is JDBC? JDBC (Java Database Connectivity) • A standard Java interface for connecting from Java to relational databases. The JDBC standard was defined by Sun Microsystems, allowing individual providers to implement and extend the standard with their own JDBC drivers

5. Steps in JDBC • Import Packages Import statements at the beginning of your program: import java.sql.Connection; import java.sql.SQLException; or import java.sql.*; Import the ff. Oracle packages when you want to access the extended functionality provided by the Oracle drivers. import oracle.jdbc.pool.OracleOCIConnectionPool; or import oracle.jdbc.*;

6. Steps in JDBC 2. Open a Connection to a Database A call to this method creates an object instance of thejava.sql.Connectionclass. The getConnection() method is an overloaded method that takes • Three parameters, one each for the URL, username, and password • Only one parameter for the database URL. In this case, the URL contains the username and password The ff. lines of code illustrate using the getConnection() method: Connection conn = DriverManager.getConnection(URL, username, passwd); Or Connection conn = DriverManager.getConnetion(URL); where URL, username and password are of String data types.

7. Steps in JDBC Opening a Connection using the Oracle JDBC OCI: When using the OCI driver, the database can be specified using the TNSNAMES entry in the tnsnames.ora.file. For example, to connect to a database on a particular host as user oratest with password oratest that has a TNSNAMES entry of oracle.world, use the following code: Connection conn = DriverManager.getConnection(“jdbc:oracle:oci9:@oracle.world”, “oratest”, “oratest”);

8. Steps in JDBC 3. Querying the Database Querying the database involves the following steps: • Creating a Statement Object This is to instantiate objects that run the query against the database to which they are connected. This is done by the createStatement() method of the conn Connection object created above. A call to this method creates an object instance of the Statement class. The ff. line of code illustrates this: Statement sql_stmt = conn.createStatement(); Or Creating a PreparedStatement A PreparedStatement is associated as a channel with a connection and a compiled SQL statement. PreparedStatements are also created with a Connection method. The following snippet shows how to create a parameterized SQL statement with three input parameters: PreparedStatement prepareUpdatePrice = conn.prepareStatement( "UPDATE Sells SET price = ? WHERE bar = ? AND beer = ?");

9. Steps in JDBC • Executing the Query and Returning a ResultSet This is done by using the executeQuery() method of the Statement object. A call to this method takes as parameter a SQL SELECT statement and returns a JDBC ResultSet object. The ff. line of code illustrates this using the sql_stmt object created above: ResultSet rset = sql_stmt.executeQuery (“SELECT empno, ename, sal, deptno FROM emp ORDER BY ename”);

10. Steps in JDBC 4. Process the Result Set Once the query has been executed, there are two steps to be carried out: • Processing the output resultSet to fetch the rows next() method of the ResultSet object • Retrieving the column values of the current row getXXX() methods of the JDBC rset object Here getXXX() corresponds to the getInt(), getString() etc with XXX being replaced by a Java datatype while (rset.next()) System.out.println (rset.getString(“ename”));

11. Steps in JDBC 5. Closing the ResultSet and Statement Once the ResultSet and Statement objects have been used, they must be closed explicitly. This is done by calls to the close() method of the ResultSet and Statement classes. The ff. code illustrates this: rset.close(); sql_stmt.close(); If not closed explicitly, there are two disadvantages: • Memory leaks can occur • Maximum Open cursors can be exceeded

12. Steps in JDBC 6. Closing the Connection The last step is to close the database connection after importing the packages and loading the JDBC drivers. This is done by a call to the close() method of the Connection class. The ff. line of code does this: conn.close();

13. Example Import Packages package com.jds.architecture.service.dbaccess; import java.sql.Connection; import java.sql.SQLException; import javax.sql.ConnectionPoolDataSource; import oracle.jdbc.pool.OracleOCIConnectionPool; public class OracleOCIDBAccess implements DBAccessInterface { private static String JDBC_URL = "jdbc:oracle:oci:@ORCL"; private static String USER_ID = "hruser"; private static String PASSWORD = "hruser"; OracleOCIConnectionPool cpool = null; /** * constructor * @throws DBAccessException */ protected OracleOCIDBAccess() throws DBAccessException { dbConnect(); } Declaration OCI driver connection pooling functionality, provided by the OracleOCIConnectionPool class, is part of the JDBC client.

14. Cont… public void dbConnect() throws DBAccessException { try{ cpool = new OracleOCIConnectionPool(); cpool.setURL(JDBC_URL); cpool.setUser(USER_ID); cpool.setPassword(PASSWORD); } catch (SQLException e) { throw new DBAccessException ("dbaccess.sql.connection.exception",e.getCause(), DBAccessException.ERROR, true); } catch (Exception e) { e.printStackTrace(); throw new DBAccessException ("dbaccess.oracle.connection.exception",e.getCause(),DBAccessException.ERROR, true); } } Initialize the Database Connection

15. Cont… /** * Returns database connection from oci connection pool * @return Connection - database connection */ public Connection getConnection() throws DBAccessException { Connection conn = null; try{ conn = cpool.getConnection(); } catch(SQLException e) { throw new DBAccessException ("dbaccess.getconnection.sql.exception", e.getCause(), DBAccessException.ERROR, true); } return conn; } } Open a Connection To a Database

16. Row Set A JDBC RowSet object holds tabular data in a way that makes it more flexible and easier to use than a result set. A Row Set is an object which encapsulates a set of rows. Kinds of RowSet Objects Connected RowSet object uses a driver based on JDBC technology (“JDBC Driver”) to make a connection to a relational database and maintains that connection throughout its life span. Only one of the standard RowSet implementations is a connected RowSet: JdbcRowSet. Being always connected to a database, it is very similar to a ResultSet object and is often used as a wrapper to make an otherwise nonscrollable and read-only ResultSet object scrollable and updatable. You can create a JdbcRowSet object in two ways: • By using the reference implementation constructor that takes a ResultSet object • By using the reference implementation default constructor Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(select * from COFFEES); JdbcRowSet jdbcRs = new JdbcRowSetImpl(rs);

17. Row Set Using the Default Constructor The following line of code creates an empty JdbcRowSet object. JdbcRowSet jdbcRs2 = new JdbcRowSetImpl(); DisconnectedRowSet objects make a connection to a data source only to read in data from a ResultSet object or to write data back to the data source. The other four implementations are disconnected RowSet implementations. • A CachedRowSet object has all the capabilities of a JdbcRowSet object plus it can also do the following: • Obtain a connection to a data source and execute a query • Read the data from the resulting ResultSet object and populate itself with that data • Manipulate data and make changes to data while it is disconnected • Reconnect to the data source to write changes back to it • Check for conflicts with the data source and resolve those conflicts

18. Row Set You can create a new CachedRowSet object in two different ways: • By using the default constructor CachedRowSet crs = new CacheRowSetImpl(); • By Passing a SyncProvider implementation to the constructor CachedRowSet crs2 = CachedRowSetImpl(“com.fred.providers.HighAvailabilityProvider”); 2. A WebRowSet object has all the capabilities of a CachedRowSet object plus it can also do the following: • Write itself as an XML document • Read an XML document that describes a WebRowSet object WebRowSet pricelist = new WebRowSetImpl(); 3. AJoinRowSet object has all the capabilities of a WebRowSet object (and therefore also a CachedRowSet object) plus it can also do the following: • Form the equivalent of an SQL JOIN without having to connect to a data source JoinRowSet jrs = new JoinRowSetImpl();

19. Row Set 4. A FilteredRowSet object likewise has all the capabilities of a WebRowSet object (and therefore also a CachedRowSet object) plus it can also do the following: • Apply filtering criteria so that only selected data is visible. This is equivalent to executing a query on a RowSet object without having to use a query language or connect to a data source. FilterRowSet frs = new FilteredRowSetImpl(); This object is initialized with the following: • The high end of the range within which values must fall • The low end of the range within which values must fall • The column name or column number of the value that must fall within the range of values set by the high and low boundaries Filter1 range = new Filter(1000, 10999, “STORE_ID”); The next line of code sets rangeas the filter forfrs. frs.setFilter(range);