Contents

0 Overview
0 History of JDBC
0 JDBC Model
0 JDBC Driver Type
0 JDBC Programming Steps
0 Step 1 : Loading a JDBC Driver
0 Step 2 : Connecting to a Database
0 Step 3 : Executing SQL
0 Step 4 : Processing the Results
0 Step 5 : Closing Database Connection
0 The PreparedStatement Object
0 Transaction and JDBC
0 Summary
0 Online Resources

Adv. Prog : JDBC 2

 Overview
0 JDBC stands for Java Database Connectivity, which is a standard Java
API for database-independent connectivity between the Java
programming language and a wide range of databases.
0 The JDBC library includes APIs for each of the tasks commonly
associated with database usage:
 Making a connection to a database
 Creating SQL or MySQL statements
 Executing that SQL or MySQL queries in the database
 Viewing & Modifying the resulting records

Adv. Prog : JDBC 3

 Overview(cont)
0 Fundamentally, JDBC is a specification that provides a complete set of
interfaces that allows for portable access to an underlying database.
Java can be used to write different types of executables, such as:
 Java Applications
 Java Applets
 Java Servlets
 Java ServerPages (JSPs)
 Enterprise JavaBeans (EJBs)
0 All of these different executables are able to use a JDBC driver to access
a database and take advantage of the stored data.
0 JDBC provides the same capabilities as ODBC, allowing Java programs to
contain database-independent code.

Adv. Prog : JDBC 4

 Overview(cont)
0 JDBC
0 JDBC is a standard interface for connecting to relational databases
from Java
0 The JDBC Classes and Interfaces are in the java.sql package
0 JDBC is Java API for executing SQL statements
0 Provides a standard API for tool/database developers
0 Possible to write database applications using a pure Java API
0 Easy to send SQL statements to virtually any relational database
0 What does JDBC do?
0 Establish a connection with a database
0 Send SQL statements
0 Process the results

Database
JAVA Applet/ JDBC Call Command
Application JDBC Driver Database

Adv. Prog : JDBC 5

 Overview (cont)
0 Reason for JDBC
0 Database vendors (Microsoft Access, Oracle etc.) provide
proprietary (non standard) API for sending SQL to the server
and receiving results from it
0 Languages such as C/C++ can make use of these proprietary
APIs directly
0 High performance
0 Can make use of non standard features of the database
0 All the database code needs to be rewritten if you change
database vendor or product
0 JDBC is a vendor independent API for accessing relational
data from different database vendors in a consistent way

Adv. Prog : JDBC 6

CCTM: Course material developed by James King (james.king@londonmet.ac.uk)

 History of JDBC
0 JDBC 1.0 released 9/1996.
0 Contains basic functionality to connect to database, query database,
process results
0 JDBC classes are part of java.sql package
0 Comes with JDK 1.1
0 JDBC 2.0 released 5/1998
0 Comes with JDK 1.2
0 javax.sql contains additional functionality
0 Additional functionality:
0 Scroll in result set or move to specific row
0 Update database tables using Java methods instead of SQL
commands
0 Send multiple SQL statements to the database as a batch
0 Use of SQL3 datatypes as column values

Adv. Prog : JDBC 7

 History of JDBC (cont)
0 JDBC 3.0 released 2/2002
0 Comes with Java 2, J2SE 1.4
0 Support for:
0 Connection pooling
0 Multiple result sets
0 Prepared statement pooling
0 Save points in transactions

Adv. Prog : JDBC 8

 History of JDBC (cont)
Bundled Package
JDBC Version Contents
with Name

JDBC 1.0 (previously

JDK 1.1 java.sql Basic Java client to database connectivity.
called 1.2)

Added features such as scrollable result sets,

JDK 1.2
JDBC 2.0 core API java.sql batch updates, new data types for SQL-3, and
and later
programmable updates using the result set.
Can be downloaded from
J2EE and www.java.sun.com/products/jdbc/ . Contains
JDBC 2.0 optional API javax.sql
later database server-side functionality. Prepares the
ground for the use of database-aware Java beans.

Not Incremental improvement and additions over the

JDBC 2.1 optional API javax.sql
bundled 2.0 API.

Adds support for connection pooling, statement

JDK 1.4
JDBC 3.0 core API java.sql pooling, and a migration path to the Connector
and later
Architecture.

Adv. Prog : JDBC 9

 JDBC Model
JAVA Applet/ Java Application
Application Developer
0 JDBC consists of two parts:
0 JDBC API, a purely Java-based API JDBC API
0 JDBC driver manager
JDBC Developer
Driver Manager
 Communicates with vendor-specific drivers
0 NB: translation to vendor Driver API
format is performed on Vender Specific JDBC
the client developer
Vendor Specific JDBC-ODBC Bridge
 No changes needed JDBC Driver
to server
Vender Specific
 Driver (translator) needed ODBC Driver
on client
Database

Adv. Prog : JDBC Database 10

 JDBC Model(cont) We will
use this one…

Oracle
Driver

Oracle

Java DB2
Application JDBC
Driver

DB2
Network
MySQL
Driver

Adv. Prog : JDBC MySQL

 JDBC Model(cont)

Application JDBC Driver

0 Java code calls JDBC library

0 JDBC loads a driver
0 Driver talks to a particular database
0 An application can work with several databases by using
all corresponding drivers
0 Ideal: can change database engines without changing any
application code (not always in practice)
Adv. Prog : JDBC 12
 Connecting To A Database
0 A database works in the classic client/server fashion.
There is one database and many clients talk to it. (Larger
applications may have multiple databases, but they can
be considered independently for our purposes.)
0 In a 2-tier system, the clients talk directly to the database
while in a 3-tier system, the clients talk to a business logic
server which in turn talks to the database. The business
logic server would also contain server-side JDBC
functionality.

Adv. Prog : JDBC 13

 Connecting To A Database (cont.)
0 A JDBC driver is typically available from the database
vendor of the database to which you wish to connect.
0 There are several different kinds of drivers depending on
whether it was written in Java or native code, or whether
it talks directly to the database or through another
database access protocol (such as Microsoft’s ODBC).
From an application programmer’s point of view, none of
this matters very much as long as you have a working
JDBC driver.
0 JDBC supports four categories of drivers which are
detailed in the table on the next slide.

Adv. Prog : JDBC 14

 JDBC Driver Types
0 JDBC drivers implement the defined interfaces in the JDBC API
for interacting with your database server.
0 For example, using JDBC drivers enable you to open database
connections and to interact with it by sending SQL or database
commands then receiving results with Java.
0 The Java.sql package that ships with JDK contains various
classes with their behaviours defined and their actual
implementaions are done in third-party drivers. Third party
vendors implements the java.sql.Driver interface in their
database driver.

Adv. Prog : JDBC 15

 JDBC Driver Types(cont)
Type Description
JDBC-to-ODBC Bridge Driver – connects Java to a Microsoft ODBC (Open
Database Connectivity) data source. This driver requires the ODBC driver to
be installed on the client computer and configuration of the ODBC data
1
source. This driver is used to allow Java programmers to build data-driver
Java applications before the database vendor supplies a Type 3 or Type 4
driver. In general, this will not be used too much these days.
Native-API, Part Java Drivers – enable JDBC programs to use database-
specific APIs (normally written in C or C++) that allow client programs to
2
access databases via the Java Native Interface. This driver translates JDBC
into database-specific code. Reasons for use are similar to Type 1.
JDBC-Net Pure Java Drivers – take JDBC requests and translate them into
a network protocol that is not database specific. These requests are sent to
3
a server, which translates the database requests into a database-specific
protocol.
Native-protocol Pure Java Drivers – convert JDBC requests to database-
4 specific network protocols, so that Java programs can connect directly to a
database.
 JDBC Driver Types(cont)
Which Driver should be used?
0 If you are accessing one type of database, such as Oracle, Sybase, or
IBM, the preferred driver type is 4.
0 If your Java application is accessing multiple types of databases at
the same time, type 3 is the preferred driver.
0 Type 2 drivers are useful in situations where a type 3 or type 4 driver
is not available yet for your database.
0 The type 1 driver is not considered a deployment-level driver and is
typically used for development and testing purposes only.

Adv. Prog : JDBC 17

 Some Popular JDBC Drivers
RDBMS JDBC Driver Name
Driver Name : com.mysql.jdbc.Driver
MySQL Database URL format:
jdbc:mysql//hostname/databaseName
Driver Name : oracle.jdbc.driver.OracleDriver
Oracle Database URL format:
jdbc:oracle:thin@hostname:portnumber:databaseName
Driver Name : com.ibm.db2.jdbc.net.DB2Driver
DB2 Database URL format:
jdbc:db2:hostname:portnumber/databaseName
Driver Name : com.jdbc.odbc.JdbcOdbcDriver
Access Database URL format:
jdbc:odbc:databaseName
Driver Name : com.org.derby.jdbc.ClintDriver
Java DB Database URL format:
jdbc:derby:databaseName
1. Application
causes driver to be
loaded Java application
2. Application asks DriverManager
for a connection to a particular DB.
3. DriverManager DriverManager asks all loaded
gives connection drivers, and one of them responds
to the application. with a connection.

java.sql.package
java.sql.DriverManager
(JDBC library)

4. Connection
supplied by
Driver for MySQL Driver for Oracle DriverManager is
databases databases used by
application to talk
How JDBC establishes a JDBC through
connection between your the driver to the
MySQL
code and a database database.
DB
JDBC Programming Steps
1) Register the driver
Connect 2) Create a connection to the database

1) Create a statement
Query 2) Query the database

1) Get a result set

Process Results 2) Assign results to Java variables

1) Close the result set

Close 2) Close the statement
3) Close the connection

Adv. Prog : JDBC 20

 Primary JDBC classes
0 Java classes used for JDBC
0 DriverManager: used to load the class which is the driver
for a specific database.
0 Connection: the database connection interface.
0 Statement: An SQL statement to be executed on the
connection.
0 ResultSet: The set of results that are returned by a query.
0 If supported by the driver, the result set can be updated and
saved back to the data base as necessary.
0 The DataSource interface can be used for any object that can
be used as a database connection.

Adv. Prog : JDBC 21

 Let’s Do this:
0 Create a database called StudentDB.
0 Create a table called StudentInfo which has only two
columns Id and Name.
0 After this we want to insert students’ information to
this StudetnInfo table from the java application.
0 And we want to fetch students’ information from
StudetnInfo table and display them onto the consol.

Adv. Prog : JDBC 22

 Task One: Creating the DB
0 First open the services window like this

Click on Windows->
Services.

Adv. Prog : JDBC 23

 Task One: Creating the DB
0 Now start the Apache Derby Network Server and create database called
StudentDB

Adv. Prog : JDBC 24

 Task One: Creating the DB
0 Create the database like this by giving the password “student” then press ok

Adv. Prog : JDBC 25

 Task One: Creating the DB
0 The database and the database connection will be created. Then connect the
database connection like this.

Adv. Prog : JDBC 26

 Task One: Creating the DB
0 When you connect the database connection then many schemas will be
visible. Now click on Excecute Command… to create a table into the default
schema STUDENT which is bolded by default.

Adv. Prog : JDBC 27

 Task One: Creating the DB
0 Now write a SQL statement to create a table called StudentInfo which has
two columns called Id and Name and then click on Run SQL. Then you will
see the excution is correct in the excution toolbar. If it is correctly executed,
you can see the table that you have created in the default schema.

Adv. Prog : JDBC 28

 Task One: Creating the DB
0 Now you can see the table in the default schema.

Adv. Prog : JDBC 29

 Task Two : Adding a Driver
0 Normally, the driver is provided by the database vendor, and is specific to a
particular database and version.
0 The file needs to be on the class path.
0 For Derby that we’ll going to use for our examples:
0 Driver class is in the derbyclient.jar file, and this archive must be on
the class path.
0 You can download the driver derbyclient.jar file from the internet and
add it to your project like this. The latest derbyclient version now is
derbyclient-10.1.2.1.
0 Assume I have downloaded the jar file in my “Libraries\My Documents”.
0 So this is the way that I will add the jar file.

Adv. Prog : JDBC 30

 Task Two : Adding a Driver
0 Let’s create a project called StudentDerbyDemo then right click on the
project -> properties->select “Library” -> click on “Add jar/Folder” button->
then select the jar file->ok.

Adv. Prog : JDBC 31

 Task Three:
Setting up the JDBC connection
Step 1: Load the appropriate driver class.
0 A JDBC driver is needed to connect to a database
0 Loading a driver requires the class name of the driver.
Ex) JDBC-ODBC: sun.jdbc.odbc.JdbcOdbcDriver
Oracle driver: oracle.jdbc.driver.OracleDriver
MySQL: com.mysql.jdbc.Driver
Java DB : org.apache.derby.jdbc.ClientDriver

0 Loading the driver class

Class.forName("DriverName");
0 It is possible to load several drivers.
0 The class DriverManager manages the loaded driver(s)

Adv. Prog : JDBC 32

 Task Three:
Setting up the JDBC connection
Step 1: Load the appropriate driver class.
0 Normally, the driver is provided by the database vendor, and is specific to a
particular database and version.
0 The class needs to be on the class path.
0 For Derby that we’ll going to use for our example:
0 Driver class is in the derbyclient.jar file, and this archive must be on
the class path.
0 Class name is org.apache.derby.jdbc.ClientDriver
0 To load the driver in Java, include the following statements:
String driver = "org.apache.derby.jdbc.ClientDriver";
Class.forName( driver );
or
Driver myDriver = new org.apache.derby.jdbc.ClientDriver( );
DriverManager.registerDriver(myDriver);
Adv. Prog : JDBC 33
 Task Three:
Setting up the JDBC connection
Step 2: Setup the URL for the database:
0 format:
jdbc:db_vendor://host:port/location
0 For Derby, running as a client on the same computer using the default Derby
port, the first part is:
jdbc:derby://localhost:1527/location
0 The location can be specified exactly as a folder name:
jdbc:derby://localhost:1527/C:\\DerbyDatabases\\seg3203
0 A default database location folder is also set by the Derby server. Locations
relative to that folder can be specified directly:
jdbc:derby://localhost:1527/seg3203
0 So for our StudentDB, it will be like this.
String url = "jdbc:derby://localhost:1527/StudentDB“;

Adv. Prog : JDBC 34

 Task Three:
Setting up the JDBC connection
Step 3: Connect to the database.
0 Ask the DriverManager for a connection.
Connection connection;
connection = DriverManager.getConnection(url,
username, password );

Adv. Prog : JDBC 35

 Task Three:
Setting up the JDBC connection
Step 4: Create a statement from the connection.
0 Statement object
0 Can be obtained from a Connection object
0 Sends SQL to the database to be executed
0 Statement has three methods to execute a SQL statement:
0 executeQuery() for QUERY statements
0 Returns a ResultSet which contains the query results
0 executeUpdate() for INSERT, UPDATE or DELETE statements
0 Returns an integer, the number of affected rows from the SQL
0 execute() for either type of statement
0 You need to create a statement to execute queries.
Statement stmt;
stmt = connection.createStatement();

Adv. Prog : JDBC 36

 Task Three:
Setting up the JDBC connection
Step 4: Create a statement from the connection.
0 Insertion
String insertQuery;
insertQuery = "insert into StudentsInfo values(1,‘Kebede')";
stmt.execute(insertQuery);
0 Updation
String updateQuery;
updateQuery = "update StudentsInfo set Name=‘Ayele' where Id=1";
stmt.execute(updateQuery);
0 Deletion
String deleteQuery;
deleteQuery = "delete from StudentsInfo where Id=1";
stmt.execute(deleteQuery);
0 Selection
 The return value is a ResultSet object containing rows of the database table
that are retrieved from the database.
ResultSet rs = stmt.executeQuery("seletct * from StudentInfo");
Adv. Prog : JDBC 37
 Task Three:
Setting up the JDBC connection
Step 5: Closing Database Connection.

h rs.close();
stmt.close();
connection.close();

Adv. Prog : JDBC 38

 Skeleton Code
Loading a JDBC driver

Class.forName(DRIVERNAME);
Connecting to a database
Connection con ;
con = DriverManager.getConnection(CONNECTIONURL, DBID,DBPASS);

Statement stmt = con.createStatement();

ResultSet rs = stmt.executeQuery(“SELECT a, b, c FROM member);

While(rs.next()) Executing SQL

{
Int x = rs.getInt(“a”);
String s = rs.getString(“b”); Processing the result set
Float f = rs.getFloat(“c”);
}

rs.close();
stmt.close(); Closing the connections
con.close();
Adv. Prog : JDBC 39
 Processing the Results (1/2)
0 JDBC returns the results of a query in a ResultSet object
0 ResultSet object contains all of the rows which satisfied the
conditions in an SQL statement
0 A ResultSet object maintains a cursor pointing to its current row of data
0 Use next() to step through the result set row by row
0 next() returns TRUE if there are still remaining records
0 getString(), getInt(), and getXXX() assign each value to a Java
variable

Internal Pointer ResultSet

Record 1 Record 2 Record 3 Record 4

The internal pointer starts one before the first record 40

Adv. Prog : JDBC
 Processing the Results (2/2)
0 Example

Statement stmt = con.createStatement();

ResultSet rs = stmt.executeQuery(“SELECT * FROM StudentInfo”);
NOTE
While (rs.next()){ You must step the cursor to the first record before read the
results This code will not skip the first record
int id = rs.getInt(“Id”);
String name = rs.getString(“Name”);
System.out.println(“ID= ” + id + “ \t Name= ” + Name );
}

Output
ID Name
1 Deribe
ID=1 Name=Deribe
2 Adenagir
ID=2 Name=Adenagir
3 Tase ID=3 Name=Tase
Table1 Adv. Prog : JDBC 41
 Sample Full Program
import java.sql.*;
public class StudentDerbyDemo {

public static void main(String[] args) {

try{// let's try to register the derby client driver
Class.forName("org.apache.derby.jdbc.ClientDriver");
}catch(ClassNotFoundException e){
System.out.println("Driver Missing!");
}
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
try{
//let's connect to the database via our registered driver
String url = "jdbc:derby://localhost:1527/StudentDB“;
con = DriverManager.getConnection (url, "student", "student");
//let's create a statement in order to query the tables
stmt = con.createStatement();
Adv. Prog : JDBC 42
 Sample Full Program
//********INSERTION********//
//let's first insert two students' information
stmt.execute("INSERT INTO StudentInfo VALUES (1,'Atnafu')");
stmt.execute("INSERT INTO StudentInfo VALUES (4,'Gume')");
System.out.println("insertion successfull");
//********SEARCHING********//
rs = stmt.executeQuery("SELECT * FROM StudentInfo WHERE Id=2");
System.out.println("Searching successful");
rs.next();//will point to the first fetched row(s)
System.out.println("Id : " + rs.getInt("Id") + "\t Name :" + rs.getString("Name"));
// rs.getInt(“Id”) : - Id is the name of the column in StudentInfo table.
//********UPDATION********//
stmt.executeUpdate("UPDATE StudentInfo SET Name = 'Biritu' WHERE Id = 1");
System.out.println("Updation successfull");
//********DELETION********//
stmt.executeUpdate("DELETE FROM StudentInfo WHERE Id=1");
System.out.println("Deletion successfull");
Adv. Prog : JDBC 43
 Sample Full Program
}catch(SQLException e){
System.out.println(e.toString());
}
finally{
try{ //Closing the connection
rs.close();
stmt.close();
con.close();
}catch(SQLException e){
System.out.println(e.toString());
}
}
}
}

Adv. Prog : JDBC 44

 JDBC - Statements
0 Once a connection is obtained we can interact with the
database. The JDBC Statement, CallableStatement, and
PreparedStatement interfaces define the methods and
properties that enable you to send SQL or PL/SQL commands
and receive data from your database.
0 They also define methods that help bridge data type
differences between Java and SQL data types used in a
database.
0 Following table provides a summary of each interface's
purpose to understand how do you decide which interface to
use?

Adv. Prog : JDBC 46

 JDBC - Statements
Interfaces Recommended Use

Use for general-purpose access to your

database. Useful when you are using static SQL
Statement
statements at runtime. The Statement interface
cannot accept parameters.
Use when you plan to use the SQL statements
PreparedStatement many times. The PreparedStatement interface
accepts input parameters at runtime.
Use when you want to access database stored
CallableStatement procedures. The CallableStatement interface
can also accept runtime input parameters.
Adv. Prog : JDBC 47
 The Statement Objects:
Creating Statement Object:
0 Before you can use a Statement object to execute a SQL statement,
you need to create one using the Connection object's
createStatement( ) method, as in the following example:

Statement stmt = null;

try {
stmt = conn.createStatement( );
...
}
catch (SQLException e) {
...
}
finally {
...
} Adv. Prog : JDBC 48
 The Statement Objects:
0 Once you've created a Statement object, you can then use it to
execute a SQL statement with one of its three execute methods.
1. boolean execute(String SQL) : Returns a boolean value of
true if a ResultSet object can be retrieved; otherwise, it
returns false. Use this method to execute SQL DDL statements
or when you need to use truly dynamic SQL.
2. int executeUpdate(String SQL) : Returns the numbers of
rows affected by the execution of the SQL statement. Use this
method to execute SQL statements for which you expect to get
a number of rows affected - for example, an INSERT, UPDATE,
or DELETE statement.
3. ResultSet executeQuery(String SQL) : Returns a ResultSet
object. Use this method when you expect to get a result set, as
you would with a SELECT statement.
Adv. Prog : JDBC 49
 The Statement Objects:
Closing Statement Obeject:
0 Just as you close a Connection object to save database resources, for
the same reason you should also close the Statement object.
0 A simple call to the close() method will do the job. If you close the
Connection object first it will close the Statement object as well.
However, you should always explicitly close the Statement object to
ensure proper cleanup.
Statement stmt = null;
try {
stmt = conn.createStatement( );
...
}
catch (SQLException e) {
...
}
finally {
stmt.close();
} Adv. Prog : JDBC 50
 The Statement Objects:
…..
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
try{
//let's create a statement in order to query the tables
stmt = con.createStatement();
stmt.execute("INSERT INTO StudentInfo VALUES (1,'Atnafu')");
…
}
…..

Adv. Prog : JDBC 51

 The PreparedStatement Objects
0 The PreparedStatement interface extends the Statement
interface which gives you added functionality with a couple of
advantages over a generic Statement object.
0 This statement gives you the flexibility of supplying arguments
dynamically.
Creating PreparedStatement Object:
PreparedStatement pstmt = null;
try {
String SQL = "Update Employees SET age = ? WHERE id = ?";
pstmt = conn.prepareStatement(SQL);
...
}catch (SQLException e) {
...
}finally { . . .}
Adv. Prog : JDBC 52
 The PreparedStatement Objects
0 All parameters in JDBC are represented by the ? symbol, which is
known as the parameter marker. You must supply values for every
parameter before executing the SQL statement.
0 The setXXX() methods bind values to the parameters, where XXX
represents the Java data type of the value you wish to bind to the
input parameter. If you forget to supply the values, you will receive an
SQLException.
0 Each parameter marker is referred to by its ordinal position. The first
marker represents position 1, the next position 2, and so forth. This
method differs from that of Java array indices, which start at 0.
0 All of the Statement object's methods for interacting with the
database (a) execute(), (b) executeQuery(), and (c) executeUpdate()
also work with the PreparedStatement object. However, the methods
are modified to use SQL statements that can take input the
parameters.
Adv. Prog : JDBC 53
 The PreparedStatement Objects
Closing PreparedStatement Obeject:
0 Just as you close a Statement object, for the same reason you should also
close the PreparedStatement object.
0 A simple call to the close() method will do the job. If you close the
Connection object first it will close the PreparedStatement object as well.
However, you should always explicitly close the PreparedStatement object to
ensure proper cleanup
PreparedStatement pstmt = null;
try {
String SQL = "Update Employees SET age = ? WHERE id = ?";
pstmt = conn.prepareStatement(SQL);
...
}catch (SQLException e) {
...
}finally {
pstmt.close();
Adv. Prog : JDBC 54
}
 The PreparedStatement Objects
Eg 1:
Try{
int price = 560000, id =1281;
….
PreparedStatement pst= null;
pst = con.prepareStatement("UPDATE CARS SET PRICE = ? WHERE ID = ?");
pst.setInt(1, price);
pst.setInt(2, id);
pst.executeUpdate();
…
Eg 2
PreparedStatement updateemp;
updateemp = con.prepareStatement ("insert into emp values(?,?,?)");
updateemp.setInt(1,23);
updateemp.setString(2,"Roshan");
updateemp.setString(3, "CEO");
updateemp.executeUpdate();
Adv. Prog : JDBC 55
 The CallableStatement Objects:
0 Just as a Connection object creates the Statement and PreparedStatement
objects, it also creates the CallableStatement object which would be used to
execute a call to a database stored procedure.
0 A stored procedure is nothing more than prepared SQL code that you save so
you can reuse the code over and over again. So if you think about a query
that you write over and over again, instead of having to write that query
each time you would save it as a stored procedure and then just call the
stored procedure to execute the SQL code that you saved as part of the
stored procedure.
0 In addition to running the same SQL code over and over again you also have
the ability to pass parameters to the stored procedure, so depending on
what the need is the stored procedure can act accordingly based on the
parameter values that were passed.

Adv. Prog : JDBC 56

 The CallableStatement Objects:
0 Just as a Connection object creates the Statement and PreparedStatement
objects, it also creates the CallableStatement object which would be used to
execute a call to a database stored procedure.
Creating CallableStatement Object:
0 Three types of parameters exist: IN, OUT, and INOUT. The
preparedStatement object only uses the IN parameter. The
CallableStatement object can use all three.
0 Here are the definitions of each:
Parameter Description

A parameter whose value is unknown when the SQL statement is created.

IN
You bind values to IN parameters with the setXXX() methods.

A parameter whose value is supplied by the SQL statement it returns. You

OUT
retrieve values from the OUT parameters with the getXXX() methods.

A parameter that provides both input and output values. You bind
INOUT variables with the setXXX() methods and retrieve values with the
Adv. Prog : JDBC 57
getXXX() methods.
 The CallableStatement Objects:
0 The following code snippet shows how to employ the
Connection.prepareCall() method to instantiate a CallableStatement
object based on the preceding stored procedure:
CallableStatement cstmt = null;
try {
String SQL = "{call getEmpName (?, ?)}";
cstmt = conn.prepareCall (SQL);
...
}
catch (SQLException e) {
...
}
finally { . . .}

Adv. Prog : JDBC 58

 The CallableStatement Objects:
0 The String variable SQL represents the stored procedure, with parameter
placeholders.
0 Using CallableStatement objects is much like using PreparedStatement
objects. You must bind values to all parameters before executing the
statement, or you will receive an SQLException.
0 If you have IN parameters, just follow the same rules and techniques that
apply to a PreparedStatement object; use the setXXX() method that
corresponds to the Java data type you are binding.
0 When you use OUT and INOUT parameters you must employ an additional
CallableStatement method, registerOutParameter(). The
registerOutParameter() method binds the JDBC data type to the data type
the stored procedure is expected to return.
0 Once you call your stored procedure, you retrieve the value from the OUT
parameter with the appropriate getXXX() method. This method casts the
retrieved value of SQL type to a Java data type.

Adv. Prog : JDBC 59

 The CallableStatement Objects:
Closing CallableStatement Obeject:
0 Just as you close other Statement object, for the same reason you should also
close the CallableStatement object.
0 A simple call to the close() method will do the job. If you close the Connection
object first it will close the CallableStatement object as well. However, you
should always explicitly close the CallableStatement object to ensure proper
cleanup.
CallableStatement cstmt = null;
try {
String SQL = "{call getEmpName (?, ?)}";
cstmt = conn.prepareCall (SQL);
...
}
catch (SQLException e) {
...
}
finally {
cstmt.close();
} Adv. Prog : JDBC 60
 JDBC - Result Sets
0 The SQL statements that read data from a database query return the data in
a result set. The SELECT statement is the standard way to select rows from a
database and view them in a result set. The java.sql.ResultSet interface
represents the result set of a database query.
0 A ResultSet object maintains a cursor that points to the current row in the
result set. The term "result set" refers to the row and column data contained
in a ResultSet object.
0 The methods of the ResultSet interface can be broken down into three
categories:
1. Navigational methods: used to move the cursor around.
2. Get methods: used to view the data in the columns of the current row
being pointed to by the cursor.
3. Update methods: used to update the data in the columns of the current
row. The updates can then be updated in the underlying database as well.

Adv. Prog : JDBC 61

 JDBC - Result Sets
0 The cursor is movable based on the properties of the ResultSet. These
properties are designated when the corresponding Statement that generated
the ResultSet is created.
0 JDBC provides following connection methods to create statements with
desired ResultSet:
1. createStatement(int RSType, int RSConcurrency);
2. prepareStatement(String SQL, int RSType, int RSConcurrency);
3. prepareCall(String sql, int RSType, int RSConcurrency);
0 The first argument indicate the type of a ResultSet object and the second
argument is one of two ResultSet constants for specifying whether a result
set is read-only or updatable.

Adv. Prog : JDBC 62

 JDBC - Result Sets
Type of ResultSet:
0 The possible RSType are given below, If you do not specify any ResultSet
type, you will automatically get one that is TYPE_FORWARD_ONLY.

Type Description

ResultSet.TYPE_FORWARD_ONLY The cursor can only move forward in the result set.

The cursor can scroll forwards and backwards, and

the result set is not sensitive to changes made by
ResultSet.TYPE_SCROLL_INSENSITIVE
others to the database that occur after the result set
was created.

The cursor can scroll forwards and backwards, and

the result set is sensitive to changes made by others
ResultSet.TYPE_SCROLL_SENSITIVE.
to the database that occur after the result set was
created.

Adv. Prog : JDBC 63

 JDBC - Result Sets
Concurrency of ResultSet:
0 The possible RSConcurrency are given below, If you do not specify any
Concurrency type, you will automatically get one that is
CONCUR_READ_ONLY.

Concurrency Description

ResultSet.CONCUR_READ_ONLY Creates a read-only result set. This is the default

ResultSet.CONCUR_UPDATABLE Creates an updateable result set.

Adv. Prog : JDBC 64

 JDBC - Result Sets
0 Our all the examples written so far can be written as follows which
initializes a Statement object to create a forward-only, read only ResultSet
object:
try {
Statement stmt = conn.createStatement(
ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY);
}
catch(Exception ex) {
....
}
finally { ....}

Adv. Prog : JDBC 65

 JDBC - Result Sets
Navigating a Result Set:
0 There are several methods in the ResultSet interface that involve moving the
cursor, including:

S.N. Methods & Description

public void beforeFirst() throws SQLException

1
Moves the cursor to just before the first row

public void afterLast() throws SQLException

2
Moves the cursor to just after the last row

public boolean first() throws SQLException

3
Moves the cursor to the first row

public void last() throws SQLException

4
Moves the cursor to the last row.

public boolean absolute(int row) throws SQLException

5
Moves the cursor to the specified row

Adv. Prog : JDBC 66

 JDBC - Result Sets
Navigating a Result Set:
S.N. Methods & Description
public boolean relative(int row) throws SQLException
6 Moves the cursor the given number of rows forward or backwards from where it currently is
pointing.
public boolean previous() throws SQLException
7 Moves the cursor to the previous row. This method returns false if the previous row is off the
result set
public boolean next() throws SQLException
8 Moves the cursor to the next row. This method returns false if there are no more rows in the
result set
public int getRow() throws SQLException
9
Returns the row number that the cursor is pointing to.
public void moveToInsertRow() throws SQLException
10 Moves the cursor to a special row in the result set that can be used to insert a new row into the
database. The current cursor location is remembered.
public void moveToCurrentRow() throws SQLException
11 Adv. ifProg
Moves the cursor back to the current row the: JDBC 67
cursor is currently at the insert row; otherwise,
this method does nothing
 JDBC - Result Sets
Viewing a Result Set:
0 The ResultSet interface contains dozens of methods for getting the data of
the current row.
0 There is a get method for each of the possible data types, and each get
method has two versions:
1. One that takes in a column name.
2. One that takes in a column index.
0 For example, if the column you are interested in viewing contains an int, you
need to use one of the getInt() methods of ResultSet:

S.N. Methods & Description

public int getInt(String columnName) throws SQLException

1
Returns the int in the current row in the column named columnName

public int getInt(int columnIndex) throws SQLException

2 Returns the int in the current row in the specified column index. The column index starts at
1, meaning the first column of a row is 1,
Adv. the
Prog second column of a row is 2, and so on. 68
: JDBC
 JDBC - Result Sets
0 Similarly there are get methods in the ResultSet interface for each of the
eight Java primitive types, as well as common types such as java.lang.String,
java.lang.Object, and java.net.URL
0 There are also methods for getting SQL data types java.sql.Date,
java.sql.Time, java.sql.TimeStamp, java.sql.Clob, and java.sql.Blob. Check the
documentation for more information about using these SQL data types.
ResultSet Methods
• String getString(int columnIndex)
• boolean getBoolean(int columnIndex)
• byte getByte(int columnIndex)
• short getShort(int columnIndex)
• int getInt(int columnIndex)
• long getLong(int columnIndex)
• float getFloat(int columnIndex)
• double getDouble(int columnIndex)
• Date getDate(int columnIndex)
• Time getTime(int columnIndex)
• Timestamp getTimestamp(int columnIndex)
Adv. Prog : JDBC 69
 JDBC - Result Sets
ResultSet Methods
0 isNull
0 In SQL, NULL means the field is empty
0 Not the same as 0 or “”
0 In JDBC, you must explicitly ask if a field is null by calling
ResultSet.isNull(column)

0 getMaxRows/setMaxRows
– Maximum number of rows a ResultSet may contain
– Unless explicitly set, the number of rows is unlimited (return value
of 0)

Adv. Prog : JDBC 70

 JDBC - Result Sets
Updating a Result Set:
0 The ResultSet interface contains a collection of update methods for updating
the data of a result set.
0 As with the get methods, there are two update methods for each data type:
1. One that takes in a column name.
2. One that takes in a column index.
0 For example, to update a String column of the current row of a result set, you
would use one of the following updateString() methods:

S.N. Methods & Description

public void updateString(int columnIndex, String s) throws SQLException

1
Changes the String in the specified column to the value of s.

public void updateString(String columnName, String s) throws SQLException

2 Similar to the previous method, except that the column is specified by its name
instead of its index.
Adv. Prog : JDBC 71
 JDBC - Result Sets
Updating a Result Set:
0 There are update methods for the eight primitive data types, as well as String, Object,
URL, and the SQL data types in the java.sql package.
0 Updating a row in the result set changes the columns of the current row in the
ResultSet object, but not in the underlying database. To update your changes to the
row in the database, you need to invoke one of the following methods.

S.N. Methods & Description

public void updateRow()
1
Updates the current row by updating the corresponding row in the database.
public void deleteRow()
2
Deletes the current row from the database
public void refreshRow()
3
Refreshes the data in the result set to reflect any recent changes in the database.
public void cancelRowUpdates()
4
Cancels any updates made on the current row.
public void insertRow()
5 Inserts a row into the database. This method can only be invoked when the cursor is
Adv. Prog : JDBC 72
pointing to the insert row.
 Date & Time Data Types:
0 The java.sql.Date class maps to the SQL DATE type, and the java.sql.Time and
java.sql.Timestamp classes map to the SQL TIME and SQL TIMESTAMP data
types, respectively.
0 JDBC in Java has three date/time types corresponding to DATE, TIME and TIMESTAMP
type of ANSI SQL. These types are used to convert SQL types into Java types.
1. First difference on java.sql.Time vs java.sql.Timestamp vs java.sql.Date is about
information they represent :
JDBC TIME or java.sql.Time represent only time information e.g. hours, minutes and
seconds without any date information.
JDBC DATE or java.sql.Date represent only date information e.g. year, month and day
without any time information.
JDBC TIMESTAMP or java.sql.Timestamp represent both date and time information
including nanosecond details.
2. java.sql.Time and java.sql.Timestamp extends java.util.Date class but java.sql.Date is
independent.
3 Time information from java.sql.Date and Date information from java.sql.Time is
normalized and may set to zero in order to confirm ANSI SQL DATE and TIME types.
0 So difference between Time, Timestamp and Date of SQL package is clear in terms of what
they represent. On contrary java.util.Date also represent Date and time information but
without nanosecond details and that's why many people prefer to store date as long
value (millisecond passed from epoch January 1, 1970 00:00:00.000 GMT). If you compare
to java.sql.Timestamp with equals() method it will return false as value of nanosecond73is
unknown.
 Handling NULL Values
0 SQL's use of NULL values and Java's use of null are different concepts. So how do
you handle SQL NULL values in Java? There are three tactics you can use:
0 Avoid using getXXX( ) methods that return primitive data types.
0 Use wrapper classes for primitive data types, and use the ResultSet object's
wasNull( ) method to test whether the wrapper class variable that received the
value returned by the getXXX( ) method should be set to null.
0 Use primitive data types and the ResultSet object's wasNull( ) method to test
whether the primitive variable that received the value returned by the getXXX( )
method should be set to an acceptable value that you've chosen to represent a
NULL.
0 Here is one example to handle a NULL value:
Statement stmt = conn.createStatement( );
String sql = "SELECT id, first, last, age FROM Employees";
ResultSet rs = stmt.executeQuery(sql);

int id = rs.getInt(1);
if( rs.wasNull( ) ) {
id = 0;
} Adv. Prog : JDBC 74
 JDBC - Transactions
0 If your JDBC Connection is in auto-commit mode, which it is by default, then
every SQL statement is committed to the database upon its completion.
0 That may be fine for simple applications, but there are three reasons why
you may want to turn off auto-commit and manage your own transactions:
 To increase performance
 To maintain the integrity of business processes
 To use distributed transactions
0 Transactions enable you to control if, and when, changes are applied to the
database. It treats a single SQL statement or a group of SQL statements as
one logical unit, and if any statement fails, the whole transaction fails.
0 To enable manual- transaction support instead of the auto-commit mode
that the JDBC driver uses by default, use the Connection object's
setAutoCommit() method. If you pass a boolean false to setAutoCommit( ),
you turn off auto-commit. You can pass a boolean true to turn it back on
again.
0 For example, if you have a Connection object named conn, code the following
to turn off auto-commit:
conn.setAutoCommit(false);
Adv. Prog : JDBC 75
 JDBC - Transactions
• By default, after each SQL statement is executed the changes
are automatically committed to the database
– Turn auto-commit off to group two or more statements together
into a transaction:
connection.setAutoCommit(false);
– Call commit to permanently record the changes to the database
after executing a group of statements
– Call rollback if an error occurs
0 Transaction: more than one statement that must all succeed
(or all fail) together
Ex) updating several tables due to customer purchase
0 If one fails, the system must reverse all previous actions
0 Also can’t leave DB in inconsistent state halfway through a
transaction
0 COMMIT = complete transaction
0 ROLLBACK = cancel all actions
Adv. Prog : JDBC 76
 JDBC - Transactions
0 The connection has a state called AutoCommit mode
0 If AutoCommit is true, then every statement is automatically
committed
0 If AutoCommit is false, then every statement is added to an ongoing
transaction
0 Default: true

con.setAutoCommit(false);
try {
PreparedStatement pstmt = con.prepareStatement(
"update BankAccount set amount = amount + ? where accountId = ?");
pstmt.setInt(1,-100); pstmt.setInt(2, 13);
pstmt.executeUpdate();
pstmt.setInt(1, 100); pstmt.setInt(2, 72);
pstmt.executeUpdate();
con.commit();
catch (SQLException e)
{ con.rollback(); }
Adv. Prog : JDBC 77
Useful Transaction Methods
• These methods apply to the Connection
• getAutoCommit/setAutoCommit
– By default, a connection is set to auto-commit
– Retrieves or sets the auto-commit mode
• Commit
– All changes since last commit to become permanent
– Database locks held by this Connection are released
• rollback
– Drops all changes since the previous call to commit
– Releases database locks held by this Connection object

Adv. Prog : JDBC 78

 Example:
Connection connection = DriverManager.getConnection(url, username, passwd);
connection.setAutoCommit(false);
try {
statement.executeUpdate(...);
statement.executeUpdate(...);
connection.commit();
} catch (Exception e) {
try {
connection.rollback();
} catch (SQLException sqle) {
// report problem
}
} finally {
try {
connection.close();
} catch (SQLException sqle) { }
}
Adv. Prog : JDBC 79
 Commit & Rollback
0 Once you are done with your changes and you want to commit the changes
then call commit() method on connection object as follows:
conn.commit( );
0 Otherwise, to roll back updates to the database made using the Connection
named conn, use the following code:
conn.rollback( );
0 The following example illustrates the use of a commit and rollback object:

Adv. Prog : JDBC 80

 Commit & Rollback
try{
//Assume a valid connection object conn
conn.setAutoCommit(false);
Statement stmt = conn.createStatement();

String SQL = "INSERT INTO Employees " +

"VALUES (106, 20, 'Rita', 'Tez')";
stmt.executeUpdate(SQL);
//Submit a malformed SQL statement that breaks
String SQL = "INSERTED IN Employees " +
"VALUES (107, 22, 'Sita', 'Singh')";
stmt.executeUpdate(SQL);
// If there is no error.
conn.commit();
}catch(SQLException se){
// If there is any error.
conn.rollback();
}
In this case none of the above INSERT statement would success and everything
would be rolled back. Adv. Prog : JDBC 81
 Using Savepoints
0 The new JDBC 3.0 Savepoint interface gives you additional transactional
control. Most modern DBMS support savepoints within their environments
such as Oracle's PL/SQL.
0 When you set a savepoint you define a logical rollback point within a
transaction. If an error occurs past a savepoint, you can use the rollback
method to undo either all the changes or only the changes made after the
savepoint.
0 The Connection object has two new methods that help you manage
savepoints:
1. setSavepoint(String savepointName): defines a new savepoint. It also
returns a Savepoint object.
2. releaseSavepoint(Savepoint savepointName): deletes a savepoint.
Notice that it requires a Savepoint object as a parameter. This object is
usually a savepoint generated by the setSavepoint() method.
0 There is one rollback ( String savepointName ) method which rolls back
work to the specified savepoint.
0 The following example illustrates the use of a Savepoint object:
Adv. Prog : JDBC 82
try{
Using Savepoints
//Assume a valid connection object conn
conn.setAutoCommit(false);
Statement stmt = conn.createStatement();

//set a Savepoint
Savepoint savepoint1 = conn.setSavepoint("Savepoint1");
String SQL = "INSERT INTO Employees " +
"VALUES (106, 20, 'Rita', 'Tez')";
stmt.executeUpdate(SQL);
//Submit a malformed SQL statement that breaks
String SQL = "INSERTED IN Employees " +
"VALUES (107, 22, 'Sita', 'Tez')";
stmt.executeUpdate(SQL);
// If there is no error, commit the changes.
conn.commit();

}catch(SQLException se){
// If there is any error.
conn.rollback(savepoint1);
}
In this case none of the abobe INSERT statement would success and everything would be rolled back.
Adv. Prog : JDBC 83
JDBC Class Diagram

Whoa! 84
Adv. Prog : JDBC
 Using JDBC MetaData
• Metadata: “data about the data”
– Not “How many customers have sales over $100K?” but
“How many columns have a datatype of BIT (boolean)?”
• System-wide data
– connection.getMetaData().getDatabaseProductName()
– connection.getMetaData().getDatabaseProductVersion()
• Table-specific data
– resultSet.getMetaData().getColumnCount()
• When using the result, remember that the index starts at 1,
not 0
– resultSet.getMetaData().getColumnName()

Adv. Prog : JDBC 85

•
ResultSetMetaData answers:
What's the number of columns in the ResultSet?
• What's a column's name?
• What's a column's SQL type?
• What's the column's normal max width in chars?
• What's the suggested column title for use in printouts and displays?
• What's a column's number of decimal digits?
• Does a column's case matter?
• Is the column a cash value?
• Will a write on the column definitely succeed?
• Can you put a NULL in this column?
• Is a column definitely not writable?
• Can the column be used in a where clause?
• Is the column a signed number?
• Is it possible for a write on the column to succeed?
• and so on...
Adv. Prog : JDBC 86
DatabaseMetaData answers:
0 What tables are available?
0 What's our user name as known to the database?
0 Is the database in read-only mode?
0 If table correlation names are supported, are they
restricted to be different from the names of the
tables?
0 and so on…

Adv. Prog : JDBC 87

 Summary
0 JDBC
0 Standard interface for connecting to relational
databases from Java
0 Vendor independent API for accessing relational data
0 JDBC has four driver type
0 JDBC-ODBC bridge plus ODBC driver
0 Native-API partly-Java driver
0 JDBC-Net pure Java driver
0 Native Protocol pure Java API driver
0 JDBC support transaction and PreparedStatement

Adv. Prog : JDBC 88

 Online Resources
0 Tutorialspoint.com
http://www.tutorialspoint.com/javaexamples/java_jdbc.htm
0 Sun’s JDBC site
0 http://java.sun.com/products/jdbc/
0 JDBC tutorial
0 http://java.sun.com/docs/books/tutorial/jdbc/
0 List of available JDBC drivers
0 http://developers.sun.com/product/jdbc/drivers
0 API for java.sql
0 http://java.sun.com/j2se/1.5.0/docs/api/java/sql/package-summary.html

Adv. Prog : JDBC 89