UNIT –II JDBC Programming

Syllabus

2
 Introduction
• Java EE technology is used to built enterprise applications.
• Such applications requires the need to interact with the database to store

application specific information.

• To interact with database, database connectivity is required.
• JDBC stands for Java Database Connectivity. JDBC is a Java API to connect and
execute the query with the database.
• JDBC is a specification from Sun Microsystems
• It can also be defined as a platform independent interface between
relational database and the Java programming language.
 What is JDBC?
• We can use JDBC API to access tabular data stored in any relational
database.
• By the help of JDBC API, we can save, update, delete and fetch data
from the database.
• It is like Open Database Connectivity (ODBC) provided by
Microsoft.
 JDBC Topics
• JDBC Architecture & Components
• JDBC Drivers Types
• Comparison of JDBC Drivers types
• Simple connection program to display records
• Interfaces required to use JDBC
• Classes required to use JDBC
• Error Checking and the SQLException Class
• The SQLWarning Class
• Transaction management
 JDBC Architecture
• JDBC API provides classes and interfaces to handle database-
specific calls from users. Few of them are : DriverManager,
Driver, Connection, Statement, Prepared Statement, Callable
Statement, ResultSet, DatabaseMetaData, ResultSetMetaData,
SQLData, Blob (Binary Large Object) and Clob (Character Large
Object).
• Driver Manager uses some database specific drivers to effectively
connect enterprise applications to database.
• JDBC Driver is required to process the SQL requests and generate
results.
• JDBC driver (third party vendor implementation) supporting data
source such as Oracle and SQL has to be added in Java
application for JDBC support which can be done dynamically at
run time. This shows that Java application is vendor independent.
10
 Components of JDBC
• JDBC has four main components through which it can communicate with a database.
o JDBC API:
• Provides various methods and interface for easy and effective communication
with the database.
• Provides a standard to connect a database to a client application.
• The application-specific user processes the SQL commands according to his need
and retrieves the result in the ResultSet object.
• It provides two main packages to interact with databases: java.sql and javax.sql

o JDBC DriverManager:
• Loads database specific drivers in an application to establish a connection with
the database.
• Selects most appropriate database-specific driver from the previous loaded
drivers when a new connection to the database is established.
• To make database specific calls to database to process user request.
7
 Components of JDBC (…)
o JDBC test suite:
• It is used to test the operation(such as insertion, deletion, updation) being
performed by JDBC Drivers.
o JDBC-ODBC bridge:
• Connects database drivers to the database.
• It translates JDBC method calls to ODBC function calls.
• It is used to implement JDBC for any database for which an ODBC driver is
available.
• The bridge for an application can be availed by importing the sun.jdbc.odbc
package, which contains a native library to access the ODBCfeatures.

8
 JDBC Drivers
Table 1: Types of JDBC Drivers
JDBC Driver Types Description

Type-1 Driver Refers to the Bridge Driver (JDBC-ODBC bridge)

Type-2 Driver Refers to a Partly Java and Partly Native code driver (Native-API Partly Java driver)

Type-3 Driver Refers to pure Java driver that uses a middleware driver to connect to a database (Pure
Java Driver for Database Middleware)
Type-4 Driver Refers to a Pure Java driver (Pure), which is directly connected to a database.

Note : Type-1 and Type-2 JDBC drivers are called as thick drivers. The reason for it is - it provides JDBC
access via ODBC drivers. ODBC binary code, and in many cases, database client code, must be loaded on
each client machine that uses such a driver. As the program is connected to database by an extra layer, in the
client; they are called thick drivers. While Type -3 and Type-4 JDBC driver is called as thin driver as these
drivers converts the JDBC calls into network protocol, used directly by DBMS, allowing a direct call from the 11
client machine to the DBMS server.
 JDBC Drivers :Type -1 Driver
• This driver acts as a bridge between JDBC and other database connectivity such as ODBC. e.g.
Sun JDBC-ODBC bridge driver.
• This driver converts JDBC calls into ODBC calls and redirects the request to the ODBC driver.
• This driver is included in the Java2 SDK within sun.jdbc.odbc package. JDBCServlet
(Java)

Type 1 JDBCDriver
(Java)

JDBC-ODBCLibrary
(Native Code)

ODBCDriver
(Native Code)

Fig. 1 : Architecture of JDBC Type-1 Driver (JDBC-ODBC Bridge) D/B 12

 JDBC Drivers : Type -1 Driver (…)
• Steps:
1. Java application makes the JDBC call to the JDBC-ODBC bridge driver to access a data source.
2. The JDBC-ODBC bridge driver resolves the JDBC call and makes an equivalent ODBC call to the
ODBC driver.
3. The ODBC driver completes the request and sends response to the JDBC-ODBC bridge driver.
4. The JDBC-ODBC bridge driver converts the response into JDBC standards and displays the result
to the requesting Java applications.

• Advantages:
o Represents single driver implementation to interact with different data stores.
o Allows us to communicate with all the databases supported by the ODBC driver.
o Represents a vendor independent driver.

11
 JDBC Drivers : Type -1 Driver (…)
• Disadvantages:
o Decreases the execution speed due to a large number of translations
o Depends on the ODBC driver; and therefore, Java applications also become indirectly dependent on
ODBC drivers
o Requires the ODBC binary code or ODBC client library that must be installed on every client
o Uses Java Native Interface (JNI) to make ODBC calls.

• Note:
o Due to above disadvantages, Type-1 driver is not suitable for production environment.
o It should be used only when no other drivers are available.
o This driver is also not recommended for Java with auto-installation application, such as applets.

12
 JDBC Drivers : Type -2 Driver
• JDBC call can be converted into the database specific native call with the help of the Type-2 driver.
(Java to native API).
• It makes JNI calls on database specific native client API. These database specific native client API
are usually written in C/C++. It follows 2-tier architecture model. JDBCServlet
(Java)

Type 2 JDBCDriver
(Java)

Database Library
(Native Code)

Fig. 2 : Architecture of JDBC Type-2 Driver (Native API) D/B 13

 JDBC Drivers : Type -2 Driver (…)
• Steps:
o Java application that needs to communicate with the database is programmed using JDBC API.
o These JDBC calls (programs written by using JDBC API) are converted into database specific
native calls in the client machine.
o This converted request is then send to the database specific native libraries.
o These native libraries present in the client are intelligent enough to send the request to the database
server by using native protocol.
• Advantages:
o Helps to access the data faster as compared to other types of drivers
o Contains additional features provided by the specific database vendor, which are also supported by
JDBC specification

14
 JDBC Drivers : Type -2 Driver (…)
• Disadvantages:
o Requires native libraries to be installed on client machines, since the conversion from JDBC calls to
specific native calls is done on client machines
o Executes the database specific native functions on the client JVM, implying that any bug in the
Type-2 driver might crash the JVM
o Increases the cost of the application in case it is run on different platforms
• Examples of type -2 driver:
o OCI ( Oracle Call Interface) Driver – Communicates with the Oracle database server. This driver
converts JDBC calls into Oracle native library calls.
o Weblogic OCI Driver for Oracle- Makes JNI calls to Weblogic library functions.
o Type-2 Driver for Sybase: Converts JDBC calls to Sybase dblib or ctlib calls, which are native
libraries to connect to Sybase
• Note:
o This driver can be used with server side application. It is not recommended for client side as it
requires specific native libraries to be installed on client machines.
15
 JDBC Drivers : Type -3 Driver
• This driver translates the JDBC calls into a database server independent and middleware server-
specific calls. (Java to Network Protocol/ All Java Driver)
• With the help of the middleware server, the translated JDBC calls are further translated into
database server specific calls. JDBCServlet
• This driver follows 3-tier architecture model. (Java)

Type 3 JDBCDriver
(Java)

Network Pr otocol
JDBCMiddleware
(Java)

D/B 18
Fig. 3 : Architecture of JDBC Type-3 Driver (Network Protocol)
 JDBC Drivers : Type -3 Driver (…)
• Steps:
o JDBC type-3 driver listens for JDBC calls from the Java application and translates them into
middleware server specific calls.
o Driver then communicates with the middleware server over a socket.
o The middleware server convert these calls into database specific calls.
• Features:
o These type of drivers are also known as net-protocol fully Java technology-enabled or net-
protocol drivers.
o These drivers are more useful in enterprise application by adding more functionality, such as pool
management, performance improvement and connection availability.
o This driver is recommended to be used with applets, since this type of driver is auto downloadable.

17
 JDBC Drivers : Type -3 Driver (…)
• Advantages:
o Serves as a all Java driver and is auto downloadable.
o Does not require any native library to be installed on the client machine.
o Ensures database independency, because a single driver provides accessibility to different types of
databases.
o Does not provide the database details, such as username, password and database server location to
the client. These details are automatically configured in the middleware server.
o Provides the flexibility to switch over from one database to another without changing the client side
driver classes. Switching of databases can be implemented by changing the configurations of the
middleware server.

18
 JDBC Drivers : Type -3 Driver (…)
• Disadvantages:
o This driver performs the task slowly when compared to type-2 drivers due to increased number of
network calls.
o It is costlier than other drivers.
• Examples:
o IDS Driver- Listens for JDBC calls and converts them into IDS Server specific network calls. This
driver communicates over a socket to IDS Server, which acts as a middleware server.
o Weblogic RMI Driver-Listens for JDBC calls and sends the request from the client to the
middleware server by using the RMI protocol. The middleware server uses a suitable JDBC driver
to communicate with a database.

19
 JDBC Drivers : Type - 4 Driver
• This driver is also called as Pure Java Driver because this driver interact directly with database.
• It does not require any native database library, that is why it is also known as Thin Driver.

JDBCServlet
(Java)

Type 4 JDBCDriver
(Native Code)

Database Protocol

D/B
Fig. 4 : Architecture of JDBC Type-4 Driver (Java to Database/Pure
20
Java/Thin)
 JDBC Drivers : Type -4 Driver (…)
• Advantages:
• Serves as a pure Java driver and is auto downloadable
• Does not require any native library to be installed on the client machine.
• Uses database server specific protocol
• Doesnot require a middleware server
• Disadvantages:
o It uses database specific proprietary protocol
o It is DBMS vendor dependent
• Examples:
o Thin driver for Oracle from Oracle Corporation
o Weblogic and MSSqlserver4 for MS SQL server from BEA systems

21
 Major Classes and Interface
Interfaces of JDBC API
A list of popular interfaces of JDBC
API is given below:
•Driver interface
•Connection interface
•Statement interface
•ResultSet interface
•ResultSetMetaData interface
•DatabaseMetaData interface Classes of JDBC API
•PreparedStatement interface A list of popular classes of JDBC API is
•CallableStatement interface given below:
•RowSet interface •DriverManager class
•Blob class
•Clob class
•Types class 22
 Java Database Connectivity

There are 5 steps to connect any java application with the database using
JDBC. These steps are as follows:
• Register the Driver class
• Create connection
• Create statement
• Execute queries
• Close connection
1) Register the driver class
• The forName() method of Class is used to register the driver class. This method is used to
dynamically load the driver class.
• Syntax of forName() method
public static void forName(String className)throws ClassNotFoundException
• Example to register the OracleDriver class:Here, Java program is loading oracle driver to
esteblish database connection.
Class.forName("oracle.jdbc.driver.OracleDriver");
2) Create the connection object
• The getConnection() method of DriverManager class is used to establish connection with the
database.
• Syntax of getConnection() method
1) public static Connection getConnection(String url)throws SQLException
2) public static Connection getConnection(String url,String name,String
password) throws SQLException
• Example to establish connection with the Oracle database Connection
con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe",
"system","password");
3) Create the Statement object
• The createStatement() method of Connection interface is used to create statement. The
object of statement is responsible to execute queries with the database.
• Syntax of createStatement() method
public Statement createStatement()throws SQLException
• Example to create the statement object
Statement stmt=con.createStatement();
4) Execute the query
• The executeQuery() method of Statement interface is used to execute queries to the
database. This method returns the object of ResultSet that can be used to get all the
records of a table.
• Syntax of executeQuery() method
public ResultSet executeQuery(String sql)throws SQLException
• Example to execute query
ResultSet rs=stmt.executeQuery("select * from emp");
while(rs.next()){
System.out.println(rs.getInt(1)+" "+rs.getString(2));
}
5) Close the connection object
• By closing connection object statement and ResultSet will be closed
automatically. The close() method of Connection interface is used to close
the connection.
• Syntax of close() method
public void close()throws SQLException
• Example to close connection
con.close();
 JDBC SQL Select Program
import java.sql.*;
public class Select {
public static void main(String[] args) throws SQLException{
try {
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost/test","root","");
String query = "Select * fromstudent_record";
Statement stmt=con.createStatement();
ResultSet rs=stmt.executeQuery(query);
while(rs.next()) {
System.out.println(rs.getInt(1)+" "+rs.getString(2)+" "+rs.getString(3)); }
con.close();
}catch(Exceptione){ System.out.println(e);}
}} } 27
 Examples of JDBC Driver Implementation
JDBC Driver
Driver Class Name JDBC URL
Type

Type -1 sun.jdbc.odbc.JdbcOdbcDriver jdbc:odbc:dsn

Type-2 oracle.jdbc.OracleDriver jdbc:oracle:oci:@localhost:1521:XE

ids.sql.IDSDriver jdbc:ids://127.0.0.0:8889/odbc?dsn='IDSExamples'
Type-3

oracle.jdbc.driver.OracleDriver
Type-4 Or jdbc:oracle:thin:@localhost:1521:XE
oracle.jdbc.OracleDriver
28
 Major Classes and Interface
1. DriverManager class:
• DriverManager is a non-abstract class in JDBC API.
• It contains only one constructor which is declared as private to
imply that this class cannot be inherited or initialized directly.
• All the methods and properties of this class are declared static.
• Key responsibilities:
oMaintains a list of DriverInfo objects, where each DriverInfo
object holds one Driver implementation class object and its
name
oPrepares a connection using the Driver implementation that
accepts the given JDBC URL.
29
2. Driver interface:
• The Driver interface is used to create connection objects that provide
an entry point for database connectivity.
• Generally, all drivers provide the DriverManager class that
implements the Driver interface and helps to load the driver in a
JDBC application.
• The drivers are loaded for any given connection request with the
help of the DriverManager class.
• After the Driver is loaded,its instance is created and registered
with the Driver Manager class.
3. Connection interface:
• Connection interface is a standard type that defines an abstraction to
access the session established with a database server.
• It provides method to handle the connection object
30
4. Statement interface:
• The Statement interface defines a standard abstraction to execute the SQL
statements requested by a user and return the results by using the ResultSet
object.
• The Statement object contains a single ResultSet object at a time.
• In case, when the data reading done with the help of one ResultSet is interleaved
with the reading done by other, then ResultSet object must be generated by
different Statement object.
• The execute() method of all statements implicitly closes the current ResultSet
object of a statement, if open.
• This interface provides specific methods to execute and retrieve results from
database:
oPreparedStatement interface provides methods to handle IN parameters
oCallableStatement interface provides methods to deal with IN and OUT
parameters.
31
 Statements Object
• To execute the requested SQL query, statement objects are used.
• There are three types of statement object:
• Statement – executes the given query immediately
• PreparedStatement – executes a compiled query
• CallableStatement – executes stored procedure

59
 Statement Interface
• Statement Object: It is used to execute a query immediately without first
having the query compiled.
1. executeQuery():
1. Query is passed as an argument to this function which is later sent to
DBMS for processing
2. This method returns a ResultSet object that has rows, columns and
metadata which represent the data requested by query.
3. ResultSet object contains methods that are used to manipulate data
in the ResultSet.
2. execute(): It is used, if multiple results are to be returned.
3. executeUpdate():
1. It is used to execute UPDATE, DELETE, INSERT and DDL
statements.
2. It returns an integer indicating the number of rows that were
60
updated by the query.
Statement Interface (…)

61
 Program to insert and display all records in the table using Statement object

public class InsertStudent {

public static void main(String[] args) throws SQLException {
Class.forName("com.mysql.jdbc.Driver");

Connection con = DriverManager.getConnection("jdbc:mysql://localhost/test","root","");

Statement stmt = con.createStatement();
String query = "Insert into student_record values(5,'ABC')";
int count = stmt.executeUpdate(query);
System.out.println(count + "Records Updated Successfully");
String query2 = "Select * from student_record";
ResultSet rs= stmt.executeQuery(query2);
while(rs.next())
{
System.out.println("Student ID: "+rs.getInt(1));
System.out.println("Student Name: "+rs.getString(2));
System.out.println(" ");
}
} con.close(); } 50
 Program to insert multiple student records using Statement
public class StudentRecord {
public static void main(String[] args) throws ClassNotFoundException,
SQLException {
Class.forName("oracle.jdbc.OracleDriver");
Connection con =
DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521",””,””)
//Creating table
String query1 = "create table student_record(roll_no number primary
key,name varchar2(20),branch_name varchar2(5))";
Statement stmt = con.createStatement(); stmt.executeUpdate(query1);
System.out.println("Table created successfully!");
//Getting number of records from the user
System.out.println("How many records need to be entered? ...");
Scanner scan = new Scanner(System.in);
int record = scan.nextInt();
int i,roll_no,total_record=0,count=0;
String name,branch_field,query2;

63
 Program to insert multiple student records using Statement (…)
//Getting data
for(i=0;i<record;i++)
{
System.out.println("Enter ID, name and branch for student: ");
roll_no = scan.nextInt()
name = scan.next();
branch = scan.next()
//Inserting received data into table
query2 = "insert into student_record values(" + roll_no + ",'" +
name + "','" + branch +"')";
count = stmt.executeUpdate(query2);
total_record = total_record + count;
}
System.out.println("Total number of records updated =
"+total_record);

64
//Retrieving data from the created table
String query3 = "Select * from student_record";
ResultSet rs = stmt.executeQuery(query3);
while(rs.next())
{
System.out.println("ID :" +rs.getInt(1)+" Name :
"+rs.getString(2)+" Branch : "+rs.getString(3));
}
stmt.close();
con.close();
}
 ResultSet Interface
• ResultSet is an interface provided in java.sql package, and it is used to represent data
retrieved from a database in a tabular format from execution of the query.

• ResultSet object can hold zero or more objects, where each object represents one row that
spans over one or more table columns.

• It can be obtained using executeQuery or getResultSet method of a statement.

• Important points related to ResultSet are as follows:

• ResultSet object is associated with a statement within a connection.
• It follow the iterate pattern
• You can obtain any number of ResultSet using one statement i.e. one ResultSet at a time.
• ResultSet is automatically closed when its associated statement is closed.
101
 ResultSet Interface (…)
• Using ResultSets:

There may not be always a step 9.

There are chances that one buffer
is stored at client side and data is
fetched from that buffer.

Fetch size can be set using

setFetchSize(int) method of the
ResultSet.

Fig. 8 : Process flow of ResultSet object

102
 ResultSet Interface (…)
• Retrieval of data using ResultSet object: It can be done in two ways:
• Move the cursor position to the required row:
o next() method of ResultSet is used to move the cursor position to the next record in
the database.
o It returns true if it successfully positions the cursor on the next row; otherwise it
returns false.
• Read the column data using getXXX methods:
o We can pass column index or column name as a parameter to this function.
o JDBC driver attempts to convert the requested columns value into the
respective Java type and returns the Java value.
o However, if it fails to convert the column value into its respective Java type, it
throws the SQL Exception exception and describes it as a conversion error.
41
 ResultSet Interface (…)
Common Methods of the ResultSet object

Methods Description
getInt(int); Returns contents of the column numbered int as an integer
getInt(String); Returns contents of the column named String as an integer
getFloat(int); Returns contents of the column numbered int as a float
getFloat(String); Returns contents of the column named String as a float
getDate(int); Returns contents of the column numbered int as a date
getDate(String); Return contents of the column named String as a date
next(); Moves the row pointer to the next row.
close(); Close the ResultSet
getMetaData(); Returns the ResultSetMetaData object

42
 Navigating a ResultSet
There are several methods in the ResultSet Interface that involve moving the cursor, including:
Common Methods for Navigating ResultSet
Methods Description
beforeFirst() Moves the cursor just before the first row
afterLast() Moves the cursor just after the last row
first() Moves the cursor to the first row
last() Moves the cursor to the last row
absolute(int) Moves the cursor to the specified row
relative(int) Moves the cursor the given number of rows forward or backward, from
where it is currently pointing
previous() Moves the cursor to the previous row. This method returns false if
the previous row is off the result set
next() Moves the cursor to the next row. This method returns false if there are
no more rows in the result set
43
 Navigating a ResultSet (…)
Common Methods for Navigating ResultSet (…)

Methods Description
getRow() Returns the row number that the cursor is pointing to
moveToInsertRow() 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
moveToCurrentRow() Moves the cursor back to the current row if the cursor is
currently at the insert row; otherwise, this method does
nothing

44
 Scrollable Result Set
Program to reverse print the resultSet

Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost");
String query1 = "select * from book";
Statement st = con.createStatement();
ResultSet rs = st.executeQuery(query1);
rs.afterLast();
while(rs.previous())
{
System.out.println("ID " +rs.getInt(1) + " BookName : "+rs.getString(2));
}

45
 Scrollable Result Set (…)
Program to reverse print the resultSet

Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con =
DriverManager.getConnection("jdbc:oracle:thin:nitya/nitya@localhost");
String query1 = "select * from book";
Statement st = con.createStatement();
ResultSet rs = st.executeQuery(query1);
rs.afterLast();
while(rs.previous())
{
System.out.println("ID " +rs.getInt(1) + " BookName : "+rs.getString(2));
}
Error:
java.sql.SQLException: Invalid operation for forward only resultset : afterLast
at oracle.jdbc.driver.BaseResultSet.afterLast(BaseResultSet.java:76)
46
 Scrollable Result Set (…)
• Types of ResultSet:
• ResultSet.TYPE_FORWARD_ONLY : The cursor can only move forward in the result
set.
• ResultSet.TYPE_SCROLL_INSENSITIVE: The cursor can scroll forward and
backward, and the result set is not sensitive to changes made by others to the database
that occur after the result set was created.

• ResultSet.TYPE_SCROLL_SENSITIVE: The cursor can scroll forward and backward,

and the result set is sensitive to changes made by others to the database that occur after the
result set was created.
• Concurrency of ResultSet:
• ResultSet.CONCUR_READ_ONLY : Creates a read-only result set. This is the default
• ResultSet.CONCUR_UPDATABLE : Creates an updateable result set
 Scrollable Result Set (…)
• To make this object to move forward and backward direction, pass either
TYPE_SCROLL_INSENSITIVE or TYPE_SCROLL_SENSITIVE in createStatement(int, int)
method.

Statement stmt =
con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
String query = “select students from class where type=‘not sleeping’ “;
ResultSet rs = stmt.executeQuery( query );
rs.previous(); / / go back in the RS (not possible in JDBC 1…)
rs.relative(-5); / / go 5 records back
rs.relative(7); / / go 7 records forward
rs.absolute(100); / / go to 100th record
113
 Scrollable Result Set (…)
Program to reverse print the ResultSet

import java.sql.*;
public class Except {
public static void main(String[] args) throws ClassNotFoundException, SQLException{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost");
String query1 = "select * from book";
Statement st =
con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
ResultSet rs = st.executeQuery(query1);
rs.afterLast();
while(rs.previous())
{
System.out.println("ID " +rs.getInt(1) + " BookName : "+rs.getString(2));
}
}
}
 Updatable Result Set
…
Statement stmt =
con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);

String query = " select students, grade from class

where type=‘really listening this presentation’ ";
ResultSet rs = stmt.executeQuery( query );
…
while ( rs.next() )
{
int grade = rs.getInt(“grade”);
rs.updateInt(“grade”, grade+10);
rs.updateRow();
}
50
import java.sql.*; Program to insert a row in a ResultSet
public class UpdatableResultSetExample {
public static void main(String[] args) {
String sql = "select id, name from employee";
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connectioncon=DriverManager.getConnection("jdbc:oracle:thin:@localhost");
Statement stmt =
conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery(sql);
// Save the current cursor position, and move cursor to the insert row,
rs.moveToInsertRow();
//Set columns values
rs.updateInt("ID", 5);
rs.updateString("NAME", “Helen");
//Insert new row
rs.insertRow();
// Move cursor back to saved position
rs.moveToCurrentRow();
} catch (SQLException e) { e.printStackTrace(); } } }
51
Metadata from RS - Dynamic SQL Select Program
• Steps for executing this program:
1. Load driver and get database connection
2. Retrieve table name from command line argument
3. Build select statement
4. Create statement object and execute SQL statement
5. Create a ResultSetMetaData object.
• Since the query is built at run time, the number and names of the columns
are not known at the time when the program is compiled.
• ResultSetMetaData object is created to determine the characteristics of the
ResultSet that has been retrieved. It is as follow:
ResultSetMetaData resdata = rs.getMetaData();
6. Traverse the ResultSet.
52
 ResultSetMetaData
ResultSetMetaData object is created to determine the characteristics of the ResultSet that has
been retrieved.
Table 13: Common Methods of the ResultSetMetaData object

Methods Description
getColumnCount(); Returns the number of column in the ResultSet
getColumnName(int); Returns the name of column number int.
getColumnLabel(int); Returns the suggested label for that column
isCurrency(int); Returns true if the column contains a number in currency
units
isReadOnly(int); Returns true if the column is read only
isAutoIncrement(int); Returns true if the this column is auto increment.
getColumnType(int); Returns the SQL data type for this column
getColumnTypeName(int); Returns the SQL data type name for this column
53
import java.sql.*; import Program to retrieve information from ResultSet about
java.util.Scanner; retrieved no. of records, no. of columns and its data types
public class JavaDynamicSelect {
public static void main(String[] args) throws SQLException{
int i, count =0;
Scanner scan = new Scanner(System.in);
System.out.println("Enter table name:"); String
table_name = scan.nextLine();
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","system"," ");
String query = "Select * from"+table_name;
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(query);
ResultSetMetaData resdata =rs.getMetaData();
while(rs.next()) { count++; }
System.out.println("Total No. of records in the given table is :"+count);
System.out.println(“Total No. of columns in the given table is: "+resdata.getColumnCount());
System.out.println(“Column type for each column is as follows: ”);
for(i=1;i<=resdata.getColumnCount();i++)
{
System.out.println("Col No. "+i+" has data type "+
resdata.getColumnTypeName(i));
}
con.close(); } 107
 Metadata from DB- DatabaseMetaData
• A Connection's database is able to provide schema information describing its tables, its supported
SQL grammar, its stored procedures the capabilities of this connection, and so on. This information
is made available through a DatabaseMetaData object.

• DatabaseMetaData interface provides methods to get meta data of a database such as database
product name, database product version, driver name, name of total number of tables, views etc.
…
Connection con = …. ;
DatabaseMetaData dbmd = con.getMetaData();
String catalog = null;
String schema = null;
String table = “sys%”;
String[ ] types = null;
ResultSet rs =
dbmd.getTables(catalog , schema , table , types );
119
…
 PreparedStatement Interface
• Before data can be retrieved or updated, each SQL query presented to the database engine must
be processed.

• Whether or not the SQL statement presented to it is syntactically correct, whether the database
objects referenced exist in the engine, and whether the data type conversions necessary can be
performed is must be determined by the database engine. These basic operations are known as
parsing the SQL statement.

• The database engine must make decisions about what is the best access path to process the SQL
statement, in addition to parsing the query. This process is known as optimizing the SQL
statement.

56
 PreparedStatement Interface (…)
• A certain amount of overhead in the database engine is required by both of these operations.

• If with the same structure, a query is to be performed for many times, then it may be better to
perform these operations once and merely substitute parameters for the portions of the query
that change with successive execution.

• PreparedStatement: It is a subclass of the Statement interface, can be used to represent a

precompiled query, which can be executed multiple times.

• Being precompiled, their execution is much faster than the execution of theStatement object.

• So, it can also prevent SQL dependency injection attacks.

57
 PreparedStatement Interface (…)
• Using PreparedStatement:

• Instead of hard coding SQL values:

Select * from employees where department = “IT” and salary > 35000;

• Set parameter place holders:

- Use a question mark for place holder: ?

Select * from employees where department = ? And salary > ?

58
 PreparedStatement Interface (…)
• Steps to program JDBC using PreparedStatement:

1. Load driver and create connection

Class.forName(“oracle.jdbc.OracleDriver”);
Connection con = DriverManager.getConnection(url,username,password);
2. Create query string with parameters

String query = “insert into mytable values(?,?,?)”;

3. Create PreparedStatement object

PreparedStatement ps = con.prepareStatement(query);

59
 PreparedStatement Interface (…)
• Steps to program JDBC using PreparedStatement: (…)

4. Provide values for PreparedStatement parameters

ps.setString(1, “abc”);
ps.setInt(2,38);
ps.setDouble(3,158.45);
5. Execute the SQL Statements

int count = ps.executeUpdate();

60
 Program to select a record using PreparedStatement object

package prestate;

import java.sql.*;
public class PreState {

public static void main(String[] args) throws ClassNotFoundException, SQLException {

Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","system"," ");
String query = "select * from EMPLOYEEwhere emp_id = ?";
PreparedStatement ps = con.prepareStatement(query);
ps.setInt(1, 2);
ResultSet rs = ps.executeQuery();
while(rs.next())
{
System.out.println("Name of the employee having given id is "+rs.getString(2));
}
con.close();
}

}
58
PreparedStatement Interface (…)

Fig. 7 : Process flow of PreparedStatement object 74

 PreparedStatement Interface (…)
• When a PreparedStatement object is used to execute a query, the query is processed as
follows:

1. The PrepareStatement() method of the connection object is used to get the object of
the PreparedStatement interface.

2. The connection object submits the given SQL statements to thedatabase.

3. The database compiles the given SQL statement

4. An execution plan is prepared by the database to execute the SQL statements.

5. The database stores the execution plan with a unique ID and returns the identity to
the Connection object
75
 PreparedStatement Interface (…)
• Thus, the Connection object prepares a PreparedStatement object, initializes it with the execution
plan identity, and returns the reference of the PreparedStatement object to the Javaapplication.

• The setXXX() method of the PreparedStatement object are used to set the parameters of the SQL
statement it is representing.

• The executeXXX() method of the PreparedStatement object is used to execute the SQL statement
with the parameters set to the PreparedStatement object.

• The PreparedStatement delegates the request sent by a client to the database.

• The database locates and executes the execution plan with the givenparameters.

• Finally, the result of the SQL statements is sent to the Java application in the form ofResultSet. 76
 Program to insert multiple student records using PreparedStatement
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

public class StudentRecord {

public static void main(String[] args) throws ClassNotFoundException, SQLException {

Class.forName("oracle.jdbc.OracleDriver");
Connection con = DriverManager.getConnection("jdbc:oracle:thin:nitya/nitya@localhost:1521");

//Creating table
String query1 = "create table student_record(roll_no number primary key,name
varchar2(20),branch_name varchar2(5))";
Statement stmt = con.createStatement();
stmt.executeUpdate(query1);
System.out.println("Table created successfully!");

//Input number of records needed to be inserted

System.out.println("How many records need to be entered? ...");
Scanner scan = new Scanner(System.in);
int record = scan.nextInt();
int i,roll_no,total_record=0,count=0; 78
 Program to insert multiple student records using PreparedStatement (…)
String name,branch_field,query2;
//Preparing Statement for insertion
query2 = "insert into student_record values(?,?,?)";
PreparedStatement ps = con.prepareStatement(query2);
for(i=0;i<record;i++){
System.out.println("Enter ID, name and branch for student: ");
roll_no = scan.nextInt();
name = scan.next();
branch_field = scan.next();
ps.setInt(1, roll_no);
ps.setString(2, name);
ps.setString(3, branch_field);
count = ps.executeUpdate(); //Executing insert
total_record = total_record + count;
}
System.out.println("Total number of records updated = "+total_record);
String query3 = "Select * from student_record";
ResultSet rs = stmt.executeQuery(query3);
while(rs.next())
{
System.out.println("ID :" +rs.getInt(1)+" Name : "+rs.getString(2)+" Branch :
"+rs.getString(3));
}
stmt.close();
con.close();
}} 79
 CallableStatement Interface
• CallableStatement: This interface extends the PreparedStatement interface.

o It also provides support for both input as well as output parameters.

o It provides a standard abstraction for all the data sources to call stored procedures
and functions, irrespective of the vendor of the data source.

o This interface is used to access, invoke and retrieve results of SQL stored procedures,
functions and cursors.

• Difference between procedures and functions: Stored procedure lets you write queries
that are quick to run and easy to invoke. It is often easier to update an application by
altering or making a few changes in the stored procedures. Function is similar to

procedure but they return scalar values. 80

 CallableStatement Interface (…)
• Stored Procedure Description:

 A stored procedure is a subroutine used by applicationsto access data from a database.

 It is called by CallableStatement interface in Java. It has the following properties:

o Contains input, output or both of these parameters

o Returns a value through the OUT parameter after executing the SQLstatements

o Return multiple ResultSets when required.

 Stored procedures are generally a group of SQL statements that allow to make a single call to a
database.

 SQL statements in a stored procedure are executed statically for better performance.
68
 CallableStatement Interface (…)
• Stored Procedure Description: (…)

 A stored procedure encapsulates the values of the following types of parameters:

o IN:- Refers to the parameters whose value cannot be overwritten and referenced by a stored
procedure.
o OUT:- Refers to the parameter whose value can be overwritten; however, cannot be
referenced by a stored procedure.

o IN OUT:- Refers to the parameter whose value can be overwritten and referenced by the
stored procedure.

69
 CallableStatement Interface (…)
• Stored Procedure Description: (…)

 The following code snippet shows how to create or replace a stored procedure:
Create or [Replace] Procedure procedure_name
[(parameter [,parameter])] CREATE OR REPLACE PROCEDURE
IS SYSTEM.getEmpName(id IN number,
emp_n OUT varchar) as
[Declarations] BEGIN BEGIN
executables SELECT emp_name INTO emp_n FROM
EMPLOYEE where emp_id = id;
[EXCEPTION exceptions] end;
END [ procedure_name]

70
 CallableStatement Interface (…)
• Using the CallableStatement Interface

 CallableStatement interface is used to call the stored procedures andfunctions.

 The broad level steps to use the CallableStatement interface in an application are:

1. Creating the CallableStatement object

2. Setting the values of the parameters

3. Registering the OUT parameters type

4. Executing the procedure or function

5. Retrieving the parameter values.

71
 CallableStatement Interface (…)
• Using the CallableStatement Interface (…)
 Creating the CallableStatement object: Can be created by invoking the prepareCall(String)
method of the Connection object.

//Calling the prepareCall method with parameters

CallableStatement cs = con.prepareCall(“{call procedure_name(?,?,?,…)}”);

//Calling the prepareCall with no parameters

Callable Statement cs = con.prepareCall(“{call procedure_name}”);

72
 CallableStatement Interface (…)
• Using the CallableStatement Interface (…)
 Setting the value of the Parameters: After creating the CallableStatement object, setXXX()
method of the CallableStatement interface is used to set IN and IN OUT parameters in the
stored procedure.

o setXXX() is used to pass the values to the IN, OUT and IN OUT parameters.

cs.setXXX(int index, XXX value)

 Registering the OUT Parameter type: OUT or IN OUT parameter used in the procedure must
be registered to collect the values of the parameters after the stored procedure is executed. It is
done using registerOutParameter() of the CallableStatement interface.

cs.registerOutParameter(int index, int type) 73

 CallableStatement Interface (…)
• Using the CallableStatement Interface (…)
 Executing the Procedure or function: execute() of the CallableStatement interface is used to
execute the procedure and doesnot take any argument.

cs.execute();
 Retrieving the Parameter values: After executing the sored procedure, OUT or IN OUT
parameter values can be retrieved using getXXX() method of the CallableStatement interface.

cs.getXXX();

74
 CallableStatement Interface (…)
import java.sql.*;
class storedProc
{
public static void main(String args[])
{
Connection con=null;
CallableStatement cstmt=null;
ResultSet rs=null;
String query=null;
try{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
}catch(ClassNotFoundException e){
System.out.println(e);
}
try{
con=DriverManager.getConnection("Jdbc:Odbc:dsn");
query="{call mypro(?,?)}";
cstmt=con.prepareCall(query);
cstmt.registerOutParameter(2,Types.VARCHAR);
cstmt.setString(1,"hello1");
cstmt.execute()
String str=cstmt.getString(2);
}catch(SQLException e){System.out.println(e);
}}}
 Program to get employee name through employee ID using CallableStatement interface via procedure

package callstat;

import java.sql.*;

public class CallStat {

public static void main(String[] args) throws ClassNotFoundException, SQLException {

Class.forName("oracle.jdbc.driver.OracleDriver");
try (Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","system"," ")) {
CallableStatement cs= con.prepareCall("{call SYSTEM.getEmpName(?,?)}");
cs.registerOutParameter(2, java.sql.Types.VARCHAR);
cs.setInt(1,1);
cs.execute();
System.out.println("Employee ID is "+cs.getString(2));
}
}

72
 Program to update Employee name using CallableStatement interface via procedure

Stored Procedure Code

DELIMITER @@
CREATE OR REPLACE PROCEDURE SYSTEM.updateName(id IN number, emp_n IN varchar) as
BEGIN
Update employee set emp_name = emp_n where emp_id = id;
end; @@
DELIMITER ;

Record Before Implementing Program

73
 Program to update Employee name using CallableStatement interface via procedure (…)
package updatenamecs;

import java.sql.*;
import java.util.Scanner;

public class UpdateNameCS {

public static void main(String[] args) throws ClassNotFoundException, SQLException {

Scanner s = new Scanner(System.in);
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","system"," ");
CallableStatement cs= con.prepareCall("{call updateName(?,?)}");
System.out.println("Enter ID : ");
int id = s.nextInt();
cs.setInt(1,id);
System.out.println("Enter Name : ");
String name = s.next();
cs.setString(2,name);
cs.execute();
System.out.println("Name Updated");
}
} 74
Program to update Employee name using CallableStatement interface via procedure (…)

Output of Implemented Program

Record After Implementing Program

75
 Error Checking and SQL Exception Class
• Programs should recover and leave the database in a consistent state.

• If a statement in the try block throws an exception or warning, it can be caught in one of the
corresponding catch statements

• How might a finally {…} block be helpful here? e.g., you could rollback your transaction in a catch
{ …} block or close database connection and free database related resources in finally {…} block

• An SQLException can occur both in the driver and the database. When such an exception occurs, an
object of type SQLException will be passed to the catch clause.

• The passed SQLException object has various methods available for retrieving additional information
about the exception − getMessage(), getErrorCode(), printStackTrace() etc.
53
 Program for exception handling using SQLException
public class Except {
public static void main(String[] args){
Connection con = null;
try {
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost/test","root","");
String query1 = "select * from book";
Statement st = con.createStatement();
ResultSet rs = st.executeQuery(query1);
while(rs.next()){
System.out.println("ID " +rs.getInt(1) + " BookName : "+rs.getString(2));
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
finally{
try{
if(con!=null)
{
con.close();
}
}catch (SQLException e){
e.printStackTrace();
}}}} 54
 SQL Warning Class
• Represents database access warnings. Instead of catching the SQLWarning exception, we can use the appropriate
methods on java.sql.Connection, java.sql.Statement and java.sql.ResultSet to access the warnings.

• Steps:

• Load JDBC driver

• Obtain Connection

• Get the SQL Warning risen while connecting to the database using getWarning API method of the Connection

• Check the SQL warning message using getMessage(), getErrorCode(), getSQLState() and
getNextWarning() API methods of SQL WarningAPI

• Execute a Statement that returns a ResultSet. For each row in ResultSet, get the SQL Warning using
getWarning() API of ResultSet
• Check the SQL warning message using getMessage(), getErrorCode(), getSQLState() and
getNextWarning() API methods of SQL WarningAPI
55
 Program for warning handling using SQLWarning
import java.sql.*;
public class SQLWarning {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
String driverName = "oracle.jdbc.OracleDriver";
String serverName = "localhost";
String serverPort = "1521";
String sid = "mySchema";
String url = "jdbc:oracle:thin:@" + serverName + ":" + serverPort + ":" + sid;
String username = "username";
String password = "password";
String warningMessage, warningSQLState;
int warningErrorCode;

//Obtaining Connection
Class.forName(driverName);
Connection connection = DriverManager.getConnection(url, username, password);
System.out.println("Successfully Connected to the database!");

//SQL Warning check on Connection

SQLWarning connectionWarning = connection.getWarnings();
while (connectionWarning != null)
{ 56
warningMessage = connectionWarning.getMessage();
Program for warning handling using SQLWarning (…)
warningSQLState = connectionWarning.getSQLState();
warningErrorCode = connectionWarning.getErrorCode();
System.out.println("Connection warning : " + warningErrorCode +" Message : "
+ warningMessage + " SQL state " + warningSQLState);
connectionWarning = connectionWarning.getNextWarning();
}
Statement statement = connection.createStatement();
SQLWarning statementWarning = statement.getWarnings();

//SQL Warning check on Statement

while (statementWarning != null)
{
warningMessage = statementWarning.getMessage();
warningSQLState = statementWarning.getSQLState();
warningErrorCode = statementWarning.getErrorCode();
System.out.println("Statement warning : " + warningErrorCode +" Message : "
+ warningMessage + " SQL state " + warningSQLState);
statementWarning = statementWarning.getNextWarning();
}

//SQL Warning check on ResultSet but for each record fetched

57
ResultSet resultSet = statement.executeQuery("SELECT * FROM test_table");
 Program for warning handling using SQLWarning (…)
while (resultSet.next())
{
SQLWarning resultsetWarning = resultSet.getWarnings();

while (resultsetWarning != null)

{
warningMessage = resultsetWarning.getMessage();

warningSQLState = resultsetWarning.getSQLState();

warningErrorCode = resultsetWarning.getErrorCode();

System.out.println("Resultset warning : " + warningErrorCode +" Message : "

+ warningMessage + " SQL state " + warningSQLState);

resultsetWarning = resultsetWarning.getNextWarning();
}

}
}

58
 JDBC processes with the javax.sql package
• The javax.sql package is also known as JDBC extension package which is used to develop the
client/server sided applications.
• It also provide server sided extension facilities, such as connection pooling and RowSet
implementation.

• It uses XA enabled connections for distributed transactions.

• Implementations provided by this package to build server side applications are:

 JNDI (Java Naming & Directory Interface) based lookup to access databases via logical names:
o Allow to access database resources by using logical names assigned to these resources.
o In other words, instead of allowing each client to load the driver classes in the respective local virtual
machines, we can use logical names assigned to each resource.
144
 Transaction Management
• Transactionsare used to ensure data integrity when multiple users access and modify data in DBMS.

• Three types of transaction: Local, Distributed and Nested.

• DBMS provides ACID property for each transaction in the database.

• ACID Properties:
o Atomicity: They guarantee of either all or none of the tasks of a transaction to be performed.
o Consistency: Guarantees that data remain in legal state before start and after end of the transaction.
o Isolation: It is the ability of the transaction to isolate or hide the data used by it from other transactions until
the transaction ends. The isolation is done by preparing locks on the data. Following set of problems may
occur when the user performs concurrent operations on the data:
• Dirty Read: Specifies that a transaction tries to read data from a row that has been modified but yet to be committed
by other transactions.
87
 Transaction Management (…)
• Non-repeatable read: Occurs when the read lock is not acquired while performing the SELECT operation e.g. If
you have selected data under transaction T1, and meanwhile if the same is being updated by some other transaction
T2, the T1 transaction reads two versions of data. This type of data read is called as non-repeatable read.

It can be avoided by preparing a read lock by transaction T1 on the data that it has selected.
• Phantom Read: Specifies the situation when the collection of rows, returned by the execution of two identical
queries are different. (occurs if some other transaction inserts a new record that is being used by an already running
transaction). This can happen when range locks are not acquired while executing SELECT query.

o Durability: This specifies that after successful execution of the transaction, the system guarantees the
updation of data in the database even if the computer crashes after the execution of the transaction.

88
 Transaction Management (…)
• Transaction management in the database operation is necessary to maintain the integrity and security of
the data from unauthorized access.

• JDBC API includes the support for transaction semantics associated with single Connection( Local
Transaction) and support to participate in transactions involving multiple resource objects (Distributed
Transactions).

• JDBC allows the following operations to execute a transaction containing multiple resource objects:

 Setting the Auto Commit attribute

 Setting the Isolation levels

89
 Transaction Management (…)
 Setting the Auto Commit attribute:
– Allows you to specify when to end a transaction.
– Executing a transaction is either dependent on a JDBCdriver or the underlying data source.
– This attribute of connection can be set by using setAutoCommit(Boolean) method of connection.
– If true is passed as argument, this method will enable auto commit i.e. con.setAutoCommit(true);
– If false argument is passed then it disables auto commit i.e. con.setAutoCommit(false);
o If auto commit is disabled, the transaction must be explicitly ended by using the commit or
rollback method.

 Setting the isolation levels:

– Notify the visible data within a transaction.
– There are four isolation levels used in transaction management:
o READ UNCOMMITED : Notifies the occurrence of dirty, non-repeatable and phantom reads.
o READ COMMITED : Notifies the occurrence of non-repeatable and phantom reads
90
 Transaction Management (…)
 Setting the isolation levels: (…)
o REPEATABLE READ : Notifies the occurrence of only phantom reads.
o SERIALIZABLE : Specifies that all transactions occur in a completely isolated fashion i.e.
Dirty, Non-repeatable and phantom reads cannot occur at this level.
⁎Isolation level in a transaction can be specified by using the connection object passed by
the connection.
⁎JDBC API provides the following methods:
1. setTransactionIsolation(int) – To set transaction isolation for a transaction. If the
driver used in a connection does not support the isolation level, the method throws a
SQL Exception
2. getTransactionIsolation() – To retrieve the transaction isolation associated with a
connection.
o Savepoints : Set points within a transaction. A Savepoint specifies a mark up to which the user
can roll back without affecting the rest of the changes of a transaction.
91
 Transaction Management (…)
 Setting the isolation levels: (…)
o Savepoints : (…)
⁎DatabaseMetaData interface available in JDBC API, provides the methods to support the
Savepoint within the transaction.
1. setSavepoint(String) method of the Connection interface is used to set a Savepoint in
a transaction.
2. rollback(Savepoint) – the transaction can be rolled back up to the given Savepoint
3. releaseSavepoint() method of the Connection interface can be called to release the
Savepoint.
- After a savepoint has been released, the attempts to reference the current
transaction in a rollback operation causes a SQLException to be thrown.

92
Example of transaction management
import java.sql.*;
public class TransMgmt {

public static void main(String[] args) throws ClassNotFoundException,

SQLException {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con =
DriverManager.getConnection("jdbc:oracle:thin:@localhost");
String query1 = "Insert into book values(2,'OOPC',23)";
String query2 = "Insert into book values(3,'CST',45)";
con.setAutoCommit(false);
Statement stmt = con.createStatement();
int r1 = stmt.executeUpdate(query1);
System.out.println(r1+" Record added Successfully");
Savepoint sp = con.setSavepoint("Savept1");
int r2 = stmt.executeUpdate(query2);
System.out.println(r2+" Record added Successfully");
con.rollback(sp);
con.commit();
con.close();
}
136
}
Example of transaction management

Previous and current status of table book after program execution

137