Java Database Connectivity

Module5: JDBC Objects

 The Concept of JDBC

• Many industrial-strength DBMS available in the market

– Oracle, DB2, Sybase, MySQL
• Each DBMS defines its own low-level way to interact
with programs to access data stored in its databases
• JDBC driver is a translator that converts low level
proprietary DBMS messages to low-level messages
understood by the JDBC API, and vice versa
• Thus JDBC drivers and the JDBC API allow Java
developers to write high level code that accesses any
DBMS
Rakhi Saxena (Internet 2
Technologies)
 JDBC: Java Database Connectivity
• JDBC is a standard Java API for handling
database related activities

• Note: The JDBC driver for different database is

different. But, as an end-user, we don’t have
to bother about their implementation.
Rakhi Saxena (Internet 3
Technologies)
JDBC Architecture

Figure reference
Advanced Java Programming
by Mr. Kute T. B.

Rakhi Saxena (Internet 4

Technologies)
 JDBC Driver Types – Type 1
• Type 1 JDBC/ODBC Bridge

– uses bridge technology to connect a Java client

to a third-party API such as Open DataBase
Connectivity (ODBC)
– eg - Sun's JDBC-ODBC bridge

Rakhi Saxena (Internet 5

Technologies)
 JDBC Driver Types – Type 2
• Type 2 Java/Native Code Driver

– This type of driver wraps a native API with Java

classes
– eg : Oracle Call Interface (OCI) driver

Rakhi Saxena (Internet 6

Technologies)
 JDBC Driver Types – Type 3
• Type 3 JDBC Driver

– This type of driver communicates using a network

protocol to a middle tier server
– middle tier in turn communicates to the database

Rakhi Saxena (Internet Technologies)7

 JDBC Driver Types – Type 4
• Type 4 JDBC Driver

– These convert JDBC requests to database-specific

network protocols, so that Java programs can
connect directly to a database
– written entirely in Java

Rakhi Saxena (Internet 8

Technologies)
 JDBC Packages
• Two packages named java.sql and javax.sql
– Java.sql
• contains core data objects of JDBC API
• Provides basics for connecting to the DBMS and
interacting with data stored in the DBMS
– Javax.sql :
• Extends java.sql
• contains Java data objects that interact with Java
Naming and Directory Interface (JNDI) and that
manage connection pooling, etc.

Rakhi Saxena (Internet 9

Technologies)
Brief Overview of the JDBC process
• Process divided into five routines
– Load the driver
– Connect to the DBMS
– Create and Execute a Statement object
– Process data returned by the DBMS
– Terminate the connection with the DBMS

Rakhi Saxena (Internet 10

Technologies)
 Steps to use JDBC in Java Program
Phase Task Relevant java.sql classes
Initialisation Load Driver DriverManager
Create connection Connection
Processing Generate SQL statements Statement
Process result data ResultSet
Termination Terminate connection Connection
Release data structures Statement

Rakhi Saxena (Internet 11

Technologies)
 Loading the JDBC driver
• JDBC drivers must be loaded before the
Java application connects to the DBMS
• The Class.forName() is used to load the JDBC
driver

Class.forName("com.mysql.jdbc.Driver");

Rakhi Saxena (Internet 12

Technologies)
 Connect to the DBMS
• Use DriverManager.getConnection() method
– DriverManager is highest class in Java.sql
hierarchy; responsible for managing driver related
information
– method is passed the URL of the database and
user ID and password required by the database
– URL is the string object that contains the driver
name that is being accessed
– method returns Connection interface that is used
throughout the process to reference the
database Rakhi Saxena (Internet
Technologies)
13
 Connect to the DBMS
• Parameters of
DriverManager.getConnection() method
– (String url, String userID, String password);
– URL format : <protocol>:<subprotocol>:<dsn-
name>
• Conncetion con =
DriverManager.getConnection(“jdbc:odbc:cu
s tomer”, “root”, “password”);
• Connection con =
DriverManager.getConnection(
"jdbc:mysql://localhost/TEST","root",
"password");
Rakhi Saxena (Internet 14
Technologies)
 Create Statement object
• Create Statement object which will be used
to execute the query

Statement
stmt=con.createStatement();

Rakhi Saxena (Internet 15

Technologies)
 Execute the query
• Execute and process the query which returns
the ResultSet object
• ResultSet object is assigned the results
received from the DBMS after the query is
processed

ResultSet rs =
stmt.executeQuery("select * from
Employees");

• Employees is the table name

Rakhi Saxena (Internet 16
Technologies)
 Process the results - 1
• next() method of ResultSet is used to iterate
throughout the result set

while(rs.next()) {
System.out.println(
rs.getInt(1)+" "+
rs.getString(2) )+" "+
rs.getString(3)+" "+
rs.getString(4));
}

Rakhi Saxena (Internet 17

Technologies)
 Process the results - 2
• ResultSet also contains several getXxx( ) methods to read the value
from particular column of current row
• Eg - getString(“name”) will read the value from column ‘name’ in
the form of string

String name;
int age;
do
{
name = rs.getString(“name”);
age = rs.getInt(“age”);
System.out.println(name+“--”+age);
} while(rs.next());

Rakhi Saxena (Internet 18

Technologies)
 Terminate the Connection
• Use close() method of Connection object once
Java program has finished accessing the DBMS
• Method throws as exception if problem is
encountered

con.close();

Rakhi Saxena (Internet 19

Technologies)
 Exception Handling- 1
• Class.forName() method throws a
ClassNotFoundException if an error occurs while
loading the JDBC driver

try
{
Class.forName("com.mysql.jdbc.Driver");
} catch(ClassNotFoundException e) {
System.err.println(“Unable to load the driver” + e);
System.exit(1);
}

Rakhi Saxena (Internet 20

Technologies)
 Exception Handling - 2
• DriverManager.getConnection() method throws a
SQLException if the driver rejects access to the DBMS

try
{
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection
("jdbc:mysql://localhost/TEST","root","password");

} catch(ClassNotFoundException e) {
System.err.println(“Unable to load the driver”+ e);
System.exit(1);
} catch(SQLException e) {
System.err.println(“Cannot connect to the database”+ e);
System.exit(1);
}
Rakhi Saxena (Internet 21
Technologies)
 Complete
class JDBCDemo{
Program
import static
public java.sql.*;
void main(String
args[]){ try{
Class.forName("com.mysql.jdbc.Driver")
; Connection con =
DriverManager.getConnection("jdbc:mysql://localhost/TEST
",
"root","password");
//here TEST is the database name,
//root is the username and root is the password
Statement stmt=con.createStatement();
ResultSet rs=stmt.executeQuery("select * from
Employees"); while(rs.next())
System.out.println(rs.getInt(1)+" "+rs.getString(2) +"
"+rs.getString(3)+rs.getString(4));
con.close();
}catch(Exception {
e) System.out.println(e);}
}
} Rakhi Saxena (Internet 22
Technologies)
 Statement objects
• Once the connection to the database is opened,
Java application creates and sends a query to
access data contained in the database.
• Three type of statement objects:
– Statement Object: Executes a query immediately
– PreparedStatement Object: Executes a compiled query
– CallableStatement Object: Executes a
stored procedure

Rakhi Saxena (Internet 23

Technologies)
 The Statement Object - 1
• Statement object is used whenever a Java program needs
to immediately execute a query without first having query
compiled
• Three different methods available
1. executeQuery()
– This method returns the ResultSet object that contains rows,
columns and metadata that represent data requested by the
query.
– Generally, this method is used to execute only the ‘SELECT’
query of SQL
– Method Signature:

ResultSet executeQuery(String query);

Rakhi Saxena (Internet 24
Technologies)
 The Statement Object - 2
2. executeUpdate()
– This method is used to execute the queries that
contain INSERT, DELETE and UPDATE statements
– Method returns integer indicating the number of
rows that were updated by the query
– Signature: int executeUpdate(String
query);
For example:
int rows = st.executeUpdate("DELETE
FROM EMPLOYEES WHERE STATUS=0");
Rakhi Saxena (Internet 25
Technologies)
 The Statement Object - 3
3. execute()
– This method is used to execute SQL statement which may return
multiple results
– Signature :
public boolean execute(String sql)
For example:
if(st.execute()) rs = st.getResultSet();

– Signatures of other methods:

• public ResultSet getResultSet()
• public int getUpdateCount()
• public boolean getMoreResults()
Rakhi Saxena (Internet 26
Technologies)
 PreparedStatement object -1
• SQL query must be compiled before the DBMS
processes it
• Compiling done after Statement object’s
execution method is called
• Compiling a query is an overhead that is
acceptable if the query is called once
• Compiling can become an expensive overhead
if the query is executed several times by the
same program during the same session
Rakhi Saxena (Internet 27
Technologies)
 PreparedStatement object -2
• PreparedStatement object can be used to precompile
and execute SQL query
• Query is created similar to other queries
• However, a question mark is given on the place for the
value that is inserted into the query after it is compiled
• It is the value that changes each time the query
is executed
• For example
“select * from nation where population > ?”

Rakhi Saxena (Internet 28

Technologies)
 PreparedStatement object -3
• This type of the query is passed as the parameter to
the prepareStatement( ) method of the Connection
object which then returns the PreparedStatement
object
• For example
“select * from nation where population > ?”
PreparedStatement ps = prepareStatement(query);
• Once the PreparedStatement object is obtained, the
setXxx( ) methods is used to replace question mark
with the value passed to setXxx() method

Rakhi Saxena (Internet 29

Technologies)
 PreparedStatement object -4
• Each setXxx() method specifies the data type of value that is being
passed
• For example
ps.setInt(1, 100000);
– First parameter (int--identifies position of the question mark
placeholder )
– Second Parameter (value that replaces the question mark)
• Next, use appropriate execute method depending upon type of the
query without any parameters
ResultSet rs = ps.executeQuery();
• This will generate the ResultSet object as the execution of the query
• PreparedStatement contains all three execute methods but without any
parameters
ResultSet executeQuery( ) int executeUpdate( ); boolean
Rakhi Saxena (Internet 30
execute( ) Technologies)
 PreparedStatement object -5
• The setXxx( ) methods:

void setBoolean(int index, boolean value);

void setByte(int index, byte value);
void setDate(int index, Date value);
void setDouble(int index, double value);
void setFloat(int index, float value);
void setInt(int index, int value);
void setLong(int index, long value);
void setObject(int index, Object value);
void setShort(int index, short value);
void setString(int index, String value);

Rakhi Saxena (Internet 31

Technologies)
 Example: Prepared Statement
import java.sql.*;
class JDBCPreparedStatement{
public static void main(String args[]){
try{ Class.forName("com.mysql.jdbc.Driver");
Connection con=DriverManager.getConnection(
"jdbc:mysql://localhost/TEST","root","password");
PreparedStatement ps = con.prepareStatement("select * from
Employees where Age > ?");
ps.setInt(1,18); //set question marks place holder
ResultSet rs = ps.executeQuery(); //execute
System.out.println("Employees having age > 5 are:");
while(rs.next()) {
System.out.println(rs.getInt(1)+" "+ rs.getString(2)+"
"+
rs.getString(3)+" "+ rs.getString(4));
}
con.close();
}catch(Exception e){ System.out.println(e);}
}
} Rakhi Saxena (Internet 32
Technologies)
 CallableStatement Object
• CallableStatement - used to call stored procedures
from JDBC application program
• Stored procedure - block of code identified by a unique
name (can be written in PL/SQL, Transact-SQL, C, etc)
• CallableStatement object uses three types of
parameters when calling a stored procedure
– IN, OUT, INOUT
– IN parameter contains the data that needs to be passed to the
stored procedure whose value is assigned using setXxx() method
– OUT parameter contains the value returned by the stored
procedure, if any
– INOUT parameter is a single parameter that is used to both pass
information and retrieve information from a stored procedure
Rakhi Saxena (Internet 33
Technologies)
 ResultSet
• SQL statements that read data from a database
query, return the data in a result set
• ResultSet object maintains a cursor (virtual
pointer) that points to the current row in the result
set
• Initially, cursor points to before the first row
• next() method used to move the cursor to the one
row next from the current position
– Returns true if row contains data, false otherwise
– getXxx() method used to copy data from the row to a
collection, object, or variable
– Columns appear in the ResultSet in the order in which
Rakhi Saxena (Internet 34
column names appear in the SELECT statement in the
Technologies)
 Scrollable ResultSet
• Three ResultSet types:
• TYPE_FORWARD_ONLY (default type )
– ResultSet can only be navigated forward, move from row 1, to row 2, to row 3 etc
• TYPE_SCROLL_INSENSITIVE
– ResultSet can be navigated (scrolled) both forward and backwards
– Can also jump to a position relative to the current position, or jump to an absolute
position
– The ResultSet is insensitive to changes in the underlying data source
– if a record in the ResultSet is changed in the database by another thread or process, it
will not be reflected in already opened ResulsSet's of this type
• TYPE_SCROLL_SENSITIVE
– ResultSet can be navigated (scrolled) both forward and backwards
– can also jump to a position relative to the current position, or jump to an absolute
position
– The ResultSet is sensitive to changes in the underlying data source
– if a record in the ResultSet is changed in the database by another thread or process, it
will be reflected in already opened ResulsSet's of this type.
Rakhi Saxena (Internet 35
Technologies)
 Navigation Methods
METHOD DESCRIPTION
absolute() Moves the ResultSet to point at an absolute position. The position is a
row number passed as parameter to the absolute() method.
afterLast() Moves the ResultSet to point after the last row in the ResultSet.

beforeFirst() Moves the ResultSet to point before the first row in the ResultSet.

first() Moves the ResultSet to point at the first row in the ResultSet.

last() Moves the ResultSet to point at the last row in the ResultSet.

next() Moves the ResultSet to point at the next row in the ResultSet.

previous() Moves the ResultSet to point at the previous row in the ResultSet.

relative() Moves the ResultSet to point to a position relative to its current

position. The relative position is passed as a parameter to the relative
method, and can be both positive and negative.

Rakhi Saxena (Internet 36

Technologies)
 Other ResultSet interface methods
METHOD DESCRIPTION
getRow() Returns the row number of the current row - the row currently
pointed to by the ResultSet.
getType() Returns the ResultSet type.
isAfterLast() Returns true if the ResultSet points after the last row. False if not.
isBeforeFirst() Returns true if the ResultSet points before the first row. False if not.
isFirst() Returns true if the ResultSet points at the first row. False if not.

METHOD DESCRIPTION
refreshRow() Refreshes the column values of that row with the latest values from
the database.

NOTE: Not all JDBC Drivers are not Scrollable

Rakhi Saxena (Internet 37
Technologies)
 Updatable ResultSet
• ResultSet concurrency determines
whether the ResultSet can be updated, or
only read
• Not all databases and JDBC drivers support that
the ResultSet is updated
• ResultSet can have one of two concurrency levels
– CONCUR_READ_ONLY – means the ResultSet can only
be read
– CONCUR_UPDATABLE - means that the ResultSet can
be both read and updated
Rakhi Saxena (Internet 38
Technologies)
 Updating a ResultSet
• Can update the columns of each row in
the ResultSet by using updateXXX() methods
result.updateString ("name" , "Alex");
result.updateInt ("age" , 55);
result.updateRow();

• Can also update a column using column

index instead of column name
result.updateString (1, "Alex");
result.updateInt (2, 55);
result.updateRow();

• It is when updateRow() is called that the

database is updated with the values of the row.
– If this method isRakhi
notSaxena
called,
(Internet
values updated in 39
Technologies)
 Inserting Rows into a ResultSet
• If ResultSet is updatable it is also possible to insert
rows into it
result.moveToInsertRow();
result.updateString (1,
"Alex");
result.updateInt (2, 55);
result.insertRow();
result.beforeFirst();

• The row pointed to after calling moveToInsertRow() is

a special row, a buffer, which can be used to build up
the row until all column values has been set on the
row.
• Once the row is ready to be inserted into the
ResultSet, the insertRow() method is called
• Important to moveRakhi
the ResultSet
Saxena (Internet to a valid position 40
Technologies)