ADVANCED JAVA

Module 5
JDBC Objects
JDBC Objects: The Concept of JDBC; JDBC Driver Types; JDBC Packages; A
Brief Overview of the JDBC process; Database Connection; Associating the
JDBC/ODBC Bridge with the Database; Statement Objects; ResultSet;
Transaction Processing; Metadata, Data types; Exceptions.

What is JDBC (Java Database Connectivity)

- The JDBC API defines Interfaces and classes for writing database
applications in Java by making database connections.

- It helps in embedding SQL code into Java Programs.

- For the connection, different JDBC connectors are required.

JDBC Driver Types *

Type 1 – JDBC to ODBC Driver

- Microsoft developed ODBC. JDBC was developed by Sun Microsystems

and based on ODBC. Both ODBC and JDBC have similar driver
specifications and an API. The JDBC-to-ODBC driver, also called the
JDBC/ODBC Bridge and is used to translate DBMS calls between JDBC
and ODBC. The JDBC-to-ODBC driver receives messages from a J2EE
component. Those messages are translated by the JDBC-to- ODBC
driver into the ODBC message format, which is then translated into the
message format understood by the DBMS.

Type 2 – Java/Native Code Driver

- Uses Java classes to generate platform-specific code for specific DBMS.

The manufacturer of the DBMS provides both the Java/Native Code
driver and API classes so the J2EE component can generate the
platform-specific code. The disadvantage of using a Java/Native Code
driver is the loss of some portability of code.

Type 3 – JDBC Driver

5
 ADVANCED JAVA

- Also referred to as the Java Protocol and is most commonly used JDBC
driver. This converts SQL queries into JDBC formatted statements.
These statements are then translated to format required by DBMS.

Type 4 – JDBC Driver

- This is also known as Type 4 database protocol. Similar to Type 3 but

SQL queries are directly translated to format required by DBMS. SQL
queries do not need to be converted to JDBC-formatted systems. This is
the fastest way to communicate SQL queries to the DBMS.

JDBC Packages

- Consists of 2 packages

- [Link] – consists of core Java objects and JDBC API. This is part of J2SE.
These include Java data objects that provide the basics for connecting
to the DBMS and interacting witfh data stored in the DBMS. [Link] is
part of the J2SE.

- [Link] – Extends [Link] and resides in J2EE. This has objects that
interact with Java Naming and Directory Interface and manage
connection pooling.

Steps in Loading 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

1- Loading the JDBC driver:

 The JDBC driver must be loaded before the J2EE component can
connect to the DBMS

6
 ADVANCED JAVA

 [Link]() method is used to load the JDBC driver.


 Appropriate driver is loaded by passing to the [Link]
method.

2- Connect to the DBMS

 After driver is loaded, J2EE must connect to DBMS using

[Link]() method.

 The [Link]() method is passed with URL

of the database, the user ID and password. The URL is a String object
that contains the driver name and the name of the database that
is being accessed by the J2EE component.

 The Dri,·[Link]() method returns a Connec tion

interface that is used throughout the process to reference the
database.

 Eg: Code Snippet

3. Creating and Executing a statement

 Next step is to send a SQL query to the DBMS for processing.

 The [Link]() method is used to create

a Statement object

7
 ADVANCED JAVA

 The Statement object is then used to execute a query and return

a ResultSet object with rows of information from database.

 Query is assigned to the string object which is then passed to

statement object.

 Once the ResultSet is received from the DBMS, the close()

method is called to terminate the statement.

Eg:

4. Process Data Returned by the DBMS

 The [Link] object is assigned the results received from the

DBMS after the query is processed.

 The first time that the next() method of the ResultSet is called, the
ResultSet pointer is positioned at the first row in the ResultSet and returns
a boolean value that if false indicates that no rows are present in the
ResultSet.

 The getString() method of the ResultSet object is used to copy the value of
a specified column in the current row of the ResultSet to a String object.

 This process continues until the next() method, called as the conditional
argument to the while statement, returns a false, which means the
pointer is at the end of the ResultSet.

8
 ADVANCED JAVA

Eg:

5. Terminate the Connection to the DBMS

 The connection to the DBMS is terminated by using the close() method

of the Connection Object.

 The close() method throws an exception if a problem is encountered.

Statement Objects***

1) Statement Object

 The Statement object is used whenever a query needs to be

immediately executed without compiling it first.

 executeQuery() method:

 SQL query is passed as an argument.

 Returns one ResultSet object with rows, columns and
metadata.

 execute() method: Multiple results are returned.

9
 ADVANCED JAVA

 executeUpdate() method: execute queries that contain

INSERT,UPDATE ,DELETE and DDL SQL statements. This returns an
integer indicating number of rows updated/deleted by the query.

 




2) PreparedStatement Object
 Compiling queries is an additional overhead.

 Hence a SQL query can be precompiled and executed using

PreparedStatement object.

 The values are inserted after compilation. Instead a question mark is

inserted in its place.

 The setxxx() method of the PreparedStatement object is used to replace

the question mark with the value passed to the setxxx() method.

 The setxxx() requires two parameters. The first parameter is an integer

that identifies the position of the question mark placeholder and the
second parameter is the value that replaces the question mark
placeholder.

 The advantage of using the PreparedStatement object is that the query

is precompiled once and the setxxx() method called as needed to change
the specified values of the query without having to recompile the query.

 The PreparedStatement object also has executeQuery(), execute() and

executeUpdate().

10
ADVANCED JAVA

 Precompiling performed by DBMS is called as “Late Binding”. Request

received is matched to the precompiled query. If match is found, then
parameters are passed to the query. During late binding, JDBC driver
passes 2 parameters to DBMS. First is the query and second is the array
of late binding variables.

 

3) CallableStatement Object

 Used to call a stored procedure from within a J2EE object.

 A stored procedure is a block of code and is identified by a unique

name.

 The CallableStatement object uses three types of parameters when

calling a stored procedure. These parameters are IN, OUT, and
INOUT.

 The IN parameter contains any data that needs to be passed to the

stored procedure and whose value is assigned using the setxxx()
method.

 The OUT parameter contains the value returned by the stored procedures.
It is retrieved using getxxx() method.

 The INOUT parameter is a single parameter that is used to both pass

information to the stored procedure and retrieve information from a
stored procedure.

 The preparedCall() method of the Connection object is used to pass query.

11
 ADVANCED JAVA

 

ResultSet**
 Data in a ResultSet object is logically organized into a virtual table
consisting of rows and columns.

 In addition to data, the ResultSet object also contains metadata such

as column names, column size and column data type.

 The ResultSet uses a virtual cursor to point to a row of the virtual

table.

 The virtual cursor is positioned above the first row of data when the
ResultSet is returned by the executeQuery() method

 Cursor is moved using next() method.

 The next() method returns a boolean true if the row contains data;
Else a boolean false is returned indicating that no more rows exist in
the ResultSet.

 Once the virtual cursor points to a row, the getxxx() method is used
to copy data from the row to a collection, object, or variable.

 The getxxx() method requires one parameter, which is an integer that

represents the number of the column that contains the data. For example,
getString(l) copies the data from the first column of the ResultSet.

12
ADVANCED JAVA

1) Scrollable ResultSet**

 The virtual cursor can be moved backwards or even positioned at a

specific row.

 There are six methods of the ResultSet object that are used to position
the virtual cursor in addition to the next() method These are

1. first(): The first() method moves the virtual cursor to the first row in
the ResultSet.

2. last(): The last() method positions the virtual cursor at the last row in
the ResultSet.
3. previous(): The previous() method moves the virtual cursor to the
previous row.

4. absolute(): The absolute() method positions the virtual cursor at the

number specified by the integer passed as a parameter to the absolute()
method .

5. relative(): The relative() method moves the virtual cursor 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 cursor is moved.

13
ADVANCED JAVA

6. getRow(): Returns an integer that represents the number of the current

row in the ResultSet.

 The createStatement() of the Connection object must be set up to handle

a scrollable ResultSet by passing the createStatement() method, one of
three constants. These constants are

- TYPE_FORWARD_ONLY: Restricts the virtual cursor to downward

movement
- TYPE_SCROLL_INSENSITIVE: This makes ResultSet insensitive to
changes made by another J2EE component to data in the table whose
rows are reflected in the ResultSet.
- TYPE_SCROLL_SENSITIVE: This makes the ResultSet sensitive to
changes in J2EE component.

14
 ADVANCED JAVA

Not All JDBC Drivers Are Scrollable

 Some JDBC drivers may not support some or all of these features and
therefore are unable to return a scrollable ResultSet.

 The maximum row setting is for rows in the ResultSet and not for the
number of rows returned by the DBMS.

 For eg: if [Link] rows is set to 100 and DBMS returns 500 rows, then
400 rows are dropped off by the ResultSet. To avoid this, flexible
fetchsize() is selected.

15
ADVANCED JAVA

2) Updatable ResultSet***

 Rows contained in the ResultSet can be updatable similar to how rows

in a table can be updated.

 This is made possible by passing the createStatement() method of the

Connection object the CONCUR_UPDATABLE.

 To prevent updation CONCUR_READ_ONLY constant can be passed to

the createStatement() method.

 3 ways to change the ResultSet.

 Update ResultSet : updatexxx() method is used to change the

value of a column in the current row of the ResultSet. The
updatexxx() method requires two parameters.

 The first is either the number or name of the column of the

ResultSet that is being updated and the second parameter is the
value that will replace the value in the column of the ResultSet.

 A value in a column of the ResultSet can be replaced with a NULL

value by using the updateNull() method.

 The updateRow() method changes values in columns of the current

row of the ResultSet based on the values of the updatexxx()
methods.

 The deleteRow() method is used to remove a row from a

ResultSet.
 deleteRow() method can be used with absolute() method for
moving the cursor to the right position.
 The updatexxx() method is also used for inserting values into the
ResultSet.

16
ADVANCED JAVA

 The insertRow() method is called after the updatexxx() methods,

which causes a new row to be inserted into the ResultSet having
values that reflect the parameters in the updatexxx() methods.

Transaction Processing***
 A transaction may involve several tasks similar to the tasks that
are required to complete a transaction at a supermarket.

 The transaction is successfully completed only if each task is

completed
successfully. If one task fails, the entire transaction fails.

 A database transaction consists of a set of SQL statements, each

of which must be successfully completed for the transaction to be
completed.

 If one fails, SQL statements that executed successfully up to that

point in the transaction must be rolled back.

 All SQL statements executed prior to the call to the commit()

method can be rolled back. Once the commit() method is called,
none of the SQL statements can be rolled back.


Explain the code below with code snippet

17
 ADVANCED JAVA

 The J2EE component can control the number of tasks that are rolled
back by using savepoints. A savepoint, introduced in JDBC 3.0, is a
virtual marker that defines the task at which the rollback stops.

 There can be many savepoints used in a transaction. Each savepoint is

identified by a unique name. The savepoint name is then passed to the
rollback() method to specify the point within the transaction where the
rollback is to stop.

18
ADVANCED JAVA

 Another way to combine SQL statements into a transaction is to batch

together these statements into a single transaction and then execute
the entire transaction.

 The addBatch() method receives a SQL statement as a parameter and

places the SQL statement in the batch.

 The executeBatch() method is called to execute the entire batch at the

same time

19
 ADVANCED JAVA

Metadata
 Metadata is data about data.

 A J2EE component can access metadata by using the DatabaseMetaData

interface. The DatabaseMeta Data interface is used to retrieve
information about databases, tables, columns, and indexes among other
information about the DBMS.

 AJ2EE component retrieves metadata about the database by calling the

getMetaData() method of the Connection object.

ResultSet Metadata
 Metadata that describes the ResultSet is retrieved by calling the
getMetaData() method of the ResultSet object. This returns a ResultSet
MetaData object.

20
 ADVANCED JAVA

Exceptions
 There are three kinds of exceptions that are thrown by JDBC methods.
 These are
o SQLExceptions : SQLExceptions commonly reflect a SQL syntax
error in the query and are thrown by many of the methods
contained in the
[Link] package. This exception is most commonly caused by
connectivity issues with the database. It can also be caused by
subtle coding errors like trying to access an object that's been
closed. For example, you try to roll back a transaction in a catch
clause and don't
check first if the database connection is still valid. The
getNextException() method of the SQLExceptions object is used to
return details about the SQL error or a null if the last exception was
retrieved.
o SQLWarnings : The SQLWarning throws warnings received by
the Connection from the DBMS. The getWarnings() method of
the Connection object retrieves the warning and the
getNextWarning() method of the Connection object retrieves
subsequent warnings.
o DataTruncation :

21