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 the java.sql.Connection class.
• 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.getConnec
tion(jdbcoracleoci9_at_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.createStatemen
  t()
• 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

• 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
  "jdbcoracleoci_at_ORCL"
• private static String USER_ID "hruser"
• private static String PASSWORD "hruser"
• OracleOCIConnectionPool cpool null
• /
• constructor
• _at_throws DBAccessException
• /
• protected OracleOCIDBAccess() throws
  DBAccessException
• dbConnect()

Import Packages
Declaration
OCI driver connection pooling functionality, provi
ded 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.conne
  ction.exception",e.getCause(),
• DBAccessException.ERROR, true)
• catch (Exception e)
• e.printStackTrace()
• throw new DBAccessException ("dbaccess.oracle.c
  onnection.exception",e.getCause(),DBAccessExceptio
  n.ERROR, true)

Initialize the Database Connection
15
Cont

• /
• Returns database connection from oci
  connection pool
• _at_return Connection - database connection
• /
• public Connection getConnection() throws
  DBAccessException
• Connection conn null
• try
• conn cpool.getConnection()
• catch(SQLException e)
• throw new DBAccessException ("dbaccess.getconne
  ction.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()
• Disconnected RowSet 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.p
  roviders.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. A JoinRowSet 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 range as the filter
  for frs.
• frs.setFilter(range)