Advanced Programming
JDBC
ER/CORP/CRS/LA46/003
JDBC
• The JDBC (Java Database Connectivity) API helps a
Java program to access a database in a standard
way
• JDBC is a specification that tells the database
vendors how to write a driver program to interface
Java programs with their database
• A Driver written according to this standard is called
the JDBC Driver
• All related classes and interfaces are present in the
[Link] package
• All JDBC Drivers implement the interfaces of [Link]
2
JDBC Drivers
• There are 4 types of drivers –
– Type1
– Type2
– Type3
– Type4
3
Type1 Driver (JDBC-ODBC bridge driver)
Calling Java Application
JDBC API
JDBC Driver Manager
JDBC - ODBC Bridge
(Type I Driver)
ODBC Driver
Database Library APIs
DataBase
4
Type2 Driver (Native-API driver )
Calling Java Application
JDBC API
JDBC Driver Manager
Native - API driver
(Type II Driver)
Database Library APIs
DataBase
5
Type3 Driver (Network-protocol driver )
Calling Java Application
JDBC API
JDBC Driver Manager
Network-Protocol driver
(Type III Driver)
MiddleWare
(Application Server)
Different DataBase Vendors
6
Type4 Driver (Native-protocol driver )
Calling Java Application
JDBC API
JDBC Driver Manager
Native - Protocol driver
(Type 4 Driver)
direct calls using specific
database protocol
DataBase
7
Database interaction
• The steps involved in a database interaction are:
– Loading the specific driver
– Making a connection to the database
– Sending SQL statements to the database
– Processing the results
8
JDBC - classes and interfaces
• DriverManager class :
– Manages all the JDBC Drivers that are loaded
in the memory
– Helps in dynamic loading of Drivers
• Data Source :
– Offer the user considerably more capability
than the basic Connection objects that the
DriverManager provides.
– It supports connection pooling and
distributed transactions
9
JDBC - classes and interfaces [Contd..]
• Methods in DriverManager class -
– getConnection() : to establish a connection to a
database.
• Connection getConnection(String url,
Properties info)
• Connection getConnection(String url)
• Connection getConnection(String url, String
userID, String password)
– registerDriver([Link])
10
JDBC - classes and interfaces [Contd..]
• Connection interface - defines methods for
interacting with the database via the established
connection.
– A connection object represents a connection with
a database.
– A connection session includes the SQL
statements that are executed and the results that
are returned over that connection.
– A single application can have one or more
connections with a single database, or it can have
many connections with many different databases.
11
JDBC - classes and interfaces [Contd..]
• The different methods of Connection interface are:
– close() - closes the database connection
– createStatement() - creates an SQL Statement
object
– prepareStatement() - creates an SQL
PreparedStatement object. (PreparedStatement
objects are precompiled SQL statements)
– prepareCall() - creates an SQL CallableStatement
object using an SQL string. (CallableStatement
objects are SQL stored procedure call statements)
12
Statement
• A statement object is used to send SQL statements
to a database.
• Three kinds :
Statement
– Execute simple SQL without parameters
PreparedStatement
– Used for pre-compiled SQL statements with or
without parameters
CallableStatement
– Execute a call to a database stored procedure or
function
13
JDBC - classes and interfaces [Contd..]
• Statement interface - defines methods that are used to interact
with database via the execution of SQL statements.
• The different methods are:
– executeQuery(String sql) - executes an SQL statement
(SELECT) that queries a database and returns a ResultSet
object.
– executeUpdate(String sql) - executes an SQL statement
(INSERT,UPDATE,or DELETE) that updates the database and
returns an int, the row count associated with the SQL
statement
– execute(String sql) - executes an SQL statement that is
written as String object
– getResultSet() - used to retrieve the ResultSet object
14
JDBC - classes and interfaces [Contd..]
Example:
Connection connection = [Link](“jdbc:odbc:emp”, “”, “”);
/* create statement */
Statement statement = [Link]();
/* get all records from the Employee table */
ResultSet resultSet = [Link]("select * from Employee");
/* update the age in empcode 1 and check no of records affected by change */
String sql = “update Employee set empage = 25 where empcode = 1”;
int recordsAffected = [Link](sql);
if(recordsAffected == 0)
[Link](“Update failed”);
/* delete employee record with empcode = 2 */
String sql = “delete from employee where empcode = 2”;
int recordsAffected = [Link](sql);
/* we have to commit the transaction once we delete the record, otherwise the
record is just marked for deletion but not physically deleted. we achieve this by
using the commit() method of the Connection interface */
[Link]();
15
JDBC - classes and interfaces [Contd..]
• ResultSet Interface - maintains a pointer to a row
within the tabular results. The next() method is
used to successively step through the rows of the
tabular results.
• The different methods are:
– getBoolean(int) - Get the value of a column in
the current row as a Java boolean.
– getByte(int) - Get the value of a column in the
current row as a Java byte.
– getDouble(int) - Get the value of a column in the
current row as a Java double.
– getInt(int) - Get the value of a column in the
current row as a Java int.
16
JDBC - classes and interfaces [Contd..]
The following example illustrates the use of the ResultSet interface and
its methods.
Connection connection = [Link](“jdbc:odbc:emp”,
“”, “”);
Statement statement = [Link]();
ResultSet resultSet = [Link]("select * from Employee");
/* Employee table has three columns first is a code (number), second is
name (String) and third is age (number)*/
while([Link]()){
int code = [Link](1);
String name= [Link](2);
int age = [Link](3);
[Link](“Code : ”+ code + “Name: ” + name + “ Age :”+age);
}
[Link]();
[Link]();
17
JDBC - classes and interfaces [Contd..]
• Types of ResultSet Objects
– ScrollableResultSet
It supports the ability to move a result set’s cursor
in either direction and there are methods for
getting the cursor position and moving the cursor
to a particular row.
– Updatable ResultSets
This allows to make the updates to the values in
the ResultSet itself, and these changes are
reflected in the database.
18
JDBC - classes and interfaces [Contd..]
• Cursor control methods
– next()
– previous()
– first()
– last()
– beforeFirst()
– afterLast()
– absolute(int rowNumber)
– relative(int rowNumber)
19
Using Statement and ResultSet
impvvort [Link].*;
class JDBCTest{
public static void main(String args[]) {
try{
[Link]("[Link]");
Connection connection =
[Link]("jdbc:oracle:thin:@
DB IPaddress:port_no:host string",“uid",“password");
Statement statement = [Link]();
ResultSet resultSet = [Link]("select * from Student");
while([Link]()){
[Link]([Link]("ClassNo"));
} }
catch(Exception exception) {
[Link](exception); } }}
20
JDBC - classes and interfaces [Contd..]
• PreparedStatement interface -- helps us to work with
precompiled SQL statements
• Precompiled SQL statements are faster than normal
statements
• So, if a SQL statement is to be repeated, it is better
to use PreparedStatement
• Some values of the statement can be represented by
a ? character which can be replaced later using
setXXX method
21
Using PreparedStatement
import [Link].*;
class PreparedStatementTest{
public static void main(String args[]) throws Exception{
try{
[Link]("[Link]");
Connection connection = [Link](“url", “UID",
“password");
PreparedStatement preparedStatement =
[Link]("select * from Emp where
ename=?");
[Link](1,str);
ResultSet resultSet = [Link]();
while([Link]()){
[Link]([Link]("ename"));
} }
catch(Exception exception){
[Link](exception); }
} }
22
JDBC - classes and interfaces [Contd..]
• CallableStatement interface -- helps us to call stored
procedures and functions
CallableStatement callableStatement =
[Link](“execute proc ?”);
[Link](1,50);
[Link]();
23
JDBC - classes and interfaces [Contd..]
• The out parameters are to be registered
[Link](int
parameterIndex, int SQLType);
• To get the value stored in the out parameter--
[Link](int parameterIndex);
24
Using CallableStatement
• Example - Calling a stored procedure named
GetSalary. The procedure queries on the Employee
table and returns the salary of an employee. It has
one input parameter that takes the EmpCode and an
out parameter that returns the salary
CallableStatement callableStatement =
[Link]("begin GetSalary(?,?); end;");
[Link](1,29418);
// OUT parameters must be registered.
[Link](2,[Link]);
[Link]();
[Link]("Salary : " +
[Link](2));
25
JDBC - classes and interfaces [Contd..]
• ResultSetMetaData Interface - holds information on
the types and properties of the columns in a
ResultSet.
• The following code creates a ResultSet obect and
ResultSetMetaData object.
ResultSet rs = [Link]("SELECT * FROM
TABLE2");
ResultSetMetaData rsmd = [Link]();
• The different methods are:
– getColumnName(int column)
– getColumnType(int column)
26
Transaction Management using JDBC
• Transactions
– The capability to group SQL statements for
execution as a single entity is provided through
SQL’s transaction mechanism.
– A transaction consists of one or more statements
that are executed, completed and either
committed or rolled back as a group.
– The commit means that the change is made
permanently in the database, and the term
rollback means that no change is made in the
database.
27
Transaction Management using JDBC [Contd..]
• By default, auto commit mode of the connection
reference is set to true
• A transaction can be done as follows using methods
of the Connection interface
...
[Link](false); //by default it is true
try{
//Statements
[Link]();
}
catch(Exception exception){
[Link]();
}
28
Transaction Savepoints
• During a transaction, a named savepoint may be inserted
between operations to act as a marker, so that the
transaction may be rolled back to that marker, leaving all
of the operations before the marker in effect.
• Transaction savepoints are JDBC enhancements that
offer finer control over transaction commit and rollback.
[Link](false);
Statement stmt = [Link]();
[Link](update1);
Savepoint savepoint1 = [Link](“SavePoint1”);
[Link](update2);
[Link](update3);
[Link](savePoint1);
29