ADVANCED JAVA-R23 DEPARTMENT OF INFORMATION TECHNOLOGY

ADVANCED JAVA
Unit I:
JDBC Programming : JDBC Architecture, Types of JDBC Drivers, Introduction to major JDBC
Classes and Interface, Creating simple JDBC Application, Types of Statement (Statement Interface,
Prepared Statement, Callable Statement), Exploring Result Set Operations, Batch Updates in
JDBC, Creating CRUD Application, Using Rowsets Objects, Managing Database Transaction.

1. JDBC Architecture
2. Types of JDBC Drivers
3. Introduction to major JDBC Classes and Interface
4. Creating simple JDBC Application
5. Types of Statement (Statement Interface, Prepared Statement, Callable Statement)
6. Exploring Result Set Operations
7. Batch Updates in JDBC
8. Creating CRUD Application
9. Using Rowsets Objects
10. Managing Database Transaction.

SIR CRRCOE
ADVANCED JAVA-R23 DEPARTMENT OF INFORMATION TECHNOLOGY

JDBC :
Introduction:
JDBC stands for Java Database Connectivity.
Java Database Connectivity (JDBC) is a Java API(Application Programming Interface) used to
interact with databases. JDBC is a specification from Sun Microsystems and it is used by Java
applications to communicate with relational databases from different vendors. We can use JDBC
to execute queries on various databases and perform operations like SELECT, INSERT, UPDATE
and DELETE.
JDBC API helps Java applications interact with different databases like MSSQL, ORACLE,
MYSQL, etc. It consists of classes and interfaces of JDBC that allow the applications to access
databases and send requests made by users to the specified database.
Purpose of JDBC
Java programming language is used to develop enterprise applications. These applications are
developed with intention of solving real-life problems and need to interact with databases to store
required data and perform operations on it. Hence, to interact with databases there is a need for
efficient database connectivity, ODBC(Open Database Connectivity) driver is used for the same.
ODBC is an API introduced by Microsoft and used to interact with databases. It can be used by
only the windows platform and can be used for any language like C, C++, Java, etc. ODBC is
procedural.
JDBC is an API with classes and interfaces used to interact with databases. It is used only for Java
languages and can be used for any platform. JDBC is highly recommended for Java applications as
there are no performance or platform dependency problems.
We can use JDBC API to handle database using Java program and can perform the following
activities:
1. Connect to the database
2. Execute queries and update statements to the database
3. Retrieve the result received from the database.
Key Features of JDBC:
 Platform Independence: JDBC can perform database operation on any platform
 Standard API: It provides different ways to interact with different databases.
 Support for Multiple Databases: JDBC provide support to work with different databases
like MySQL, PostgreSQL, Oracle, etc.
Components of JDBC:
JDBC has four main components that are used to connect with a database as follows:
1. JDBC API
2. JDBC Driver Manager
3. JDBC Test Suite
4. JDBC-ODBC Bridge Drivers

SIR CRRCOE
ADVANCED JAVA-R23 DEPARTMENT OF INFORMATION TECHNOLOGY

1) JDBC API: JDBC API provides various interfaces and methods to establish easy connection
with different databases.

[Link].*;
[Link].*;

2)JDBC Driver Manager

JDBC DriverManager is a class in JDBC API that loads a database-specific driver in Java
application and establishes a connection with a database. DriverManager makes a call to a specific
database to process the user request.

3) JDBC Test suite: JDBC Test suite facilitates the programmer to test the various operations such
as deletion, updation, insertion that are being executed by the JDBC Drivers.
4) JDBC-ODBC Bridge Drivers: JDBC-ODBC Bridge Drivers are used to connect the database
drivers to the database. The bridge does the translation of the JDBC method calls into the ODBC
method call. It makes the usage of the [Link] package that encompasses the native library
in order to access the ODBC (Open Database Connectivity) characteristics.
Note: Since Java 8, the JDBC-ODBC drivers have been removed. Oracle suggests using drivers
provided by the vendor of the database.

JDBC Architecture:
The JDBC architecture in java is as follows

The major components of JDBC architecture are as follows:

1. Application
2. The JDBC API

3. DriverManager
4. JDBC Drivers
5. Data Sources

SIR CRRCOE
ADVANCED JAVA-R23 DEPARTMENT OF INFORMATION TECHNOLOGY

1)Application:Applications in JDBC architecture are java applications like applets or servlet that
communicates with databases.
2) The JDBC API: The JDBC API is an Application Programming Interface used to create
Databases.
It allows the Java programs to perform the execution of the SQL statements and then get the
results.
JDBC API uses classes and interfaces to connect with databases. Some of the important classes
and interfaces defined in JDBC architecture in java are the DriverManager class, Connection
Interface, etc.
A few of the crucial interfaces and classes defined in the JDBC API are the following:
o Drivers
o DriverManager
o Statement
o Connection
o CallableStatement
o PreparedStatement
o ResultSet
o SQL data
3) DriverManager: DriverManager plays a crucial role in the architecture of JDBC.
DriverManager class in the JDBC architecture is used to establish a connection between Java
applications and databases. Using the getConnection method of this class a connection is
established between the Java application and data sources.
It uses database-specific drivers to connect the enterprise applications to various databases.
4) JDBC drivers: To interact with a data source with the help of the JDBC, one needs a JDBC
driver which conveniently interacts with the respective data source.
5) Data Sources:
Data Sources in the JDBC architecture are the databases that we can connect using this API. These
are the sources where data is stored and used by Java applications. JDBC API helps to connect
various databases like Oracle, MYSQL, MSSQL, PostgreSQL, etc.
Types of JDBC Architecture
The JDBC Architecture can be of two types based on the processing models it uses. These models
are
1. 2-tier model
2. 3-tier model
2-tier model:
 2-tier JDBC architecture model is a basic model.

 In this model, a java application communicates directly to the data sources. JDBC driver is
used to establish a connection between the application and the data source.

SIR CRRCOE
ADVANCED JAVA-R23 DEPARTMENT OF INFORMATION TECHNOLOGY

 When an application needs to interact with a database, a query is directly executed on the
data source and the output of the queries is sent back to the user in form of results.
 In this model, the data source can be located on a different machine connected to the same
network the user is connected to.
 This model is also known as a client/server configuration. Here user's machine acts as a
client and the machine on which the database is located acts as a server.
3 Tier Model:
 3-tier model is a complex and more secure model of JDBC architecture in java.

 In this model the user queries are sent to the middle tier and then they are executed on the
data source.
 Here, the java application is considered as one tier connected to the data source(3rd tier)
using middle-tier services.
 In this model user queries are sent to the data source using middle-tier services, from where
the commands are again sent to databases for execution.
 The results obtained on the database are again sent to the middle-tier and then to the
user/application.

SIR CRRCOE
ADVANCED JAVA-R23 DEPARTMENT OF INFORMATION TECHNOLOGY

Types of JDBC Drivers:

JDBC Drivers: JDBC drivers are client-side adapters (installed on the client machine rather than
the server) that translate requests from Java programs into a protocol understood by the DBMS.
These drivers are software components that implement the interfaces in the JDBC API, allowing
Java applications to interact with a database. Sun Microsystems (now Oracle) defines four types of
JDBC drivers, which are outlined below:
1. Type-1 driver or JDBC-ODBC bridge driver
2. Type-2 driver or Native-API driver
3. Type-3 driver or Network Protocol driver
4. Type-4 driver or Thin driver

1. JDBC-ODBC Bridge Driver - Type 1 Driver:

 This Type 1 driver is developed by SUN Microsystems.
 The name of the Type-1 driver is JDBC-Odbc Bridge Driver.

 The JDBC-ODBC bridge driver uses ODBC driver to connect to the database. The JDBC-
ODBC bridge driver converts JDBC method calls into the ODBC function calls. This is
now discouraged because of thin driver.
 Type-1 driver is also called Universal driver because it can be used to connect to any of the
databases.
 The JDBC-Odbc Bridge Driver is found in a package called [Link]. By using this
driver we can develop only 2-tier applications (a Java program and database).
 This type of driver is purely implemented in ‘C’ language and this is platform dependent in
nature.
 This type-1 driver is used to connect the database with the help of the ODBC API. Hence it
is called a JDBC-ODBC bridge driver.

Advantages
 This driver software is built-in with JDK so no need to install separately.
 It is a database independent driver.
Disadvantages
 The Type-1 driver is very slow, because of JDBC-ODBC transactions.
 The Type-1 driver is the dependent driver since its depending on ODBC support.(i.e; The
ODBC bridge driver is needed to be installed in individual client machines.)
 The Type-1 driver is not suitable for real-time applications.(i.e; Type-1 driver isn't written
in java, that's why it isn't a portable driver.)
6

SIR CRRCOE
ADVANCED JAVA-R23 DEPARTMENT OF INFORMATION TECHNOLOGY

2. Native-API Driver - Type 2 Driver ( Partially Java Driver)

 The Native API driver uses the client -side libraries of the database.
 This driver converts JDBC method calls into native calls of the database API.
 In order to interact with different database, this driver needs their local API, that's why
data transfer is much more secure as compared to type-1 driver.
 This driver is not fully written in Java that is why it is also called Partially Java driver.

Advantage
 Native-API driver gives better performance than JDBC-ODBC bridge driver.(faster)
 More secure compared to the type-1 driver.
Disadvantages

 The Type-2 driver is both platform and database dependent. Hence it is not suitable for
real-time applications.
 This driver is slower than the Type-3 and Type-4 drivers.

3. Network Protocol Driver - Type 3 Driver (Fully Java Driver)

 Net Protocol is a Type-3 JDBC driver. It was written in Java programming.
 This network protocol driver uses the middle software like an application server.
 The JDBC clients use the standard network sockets to communicate with the middleware
application server.
 The middleware application server internally converts the JDBC calls directly or indirectly
to the vendor’s specific database protocol.

Advantages
 Type-3 drivers are fully written in Java, hence they are portable drivers.
 No client side library is required because of application server that can perform many tasks
like auditing, load balancing, logging etc.
 Switch facility to switch over from one database to another database.

SIR CRRCOE
ADVANCED JAVA-R23 DEPARTMENT OF INFORMATION TECHNOLOGY

Disadvantages
 Network support is required on client machine.
 Maintenance of Network Protocol driver becomes costly because it requires database-
specific coding to be done in the middle tier.
4. Thin Driver - Type 4 Driver (Fully Java Driver)
 Type-4 drivers are supplied by Oracle Corporation by developing into Java language.
OracleDriver is the name of Type-4 driver which is released by Oracle Corporation in the
form of [Link].
 In order to work with the Type-4 driver, we need to add the [Link] to our classpath.
 Type-4 driver is also called native protocol driver. This driver interact directly with
database. It does not require any native database library, that is why it is also known as
Thin Driver.
 It is a pure Java driver because it completely implemented by the Java programming
language

Advantages
 Does not require any native library and Middleware server, so no client-side or server-side
installation.
 It is fully written in Java language, hence they are portable drivers.
Disadvantage
 If the database changes, a new driver may be needed.

Example to get the Connection :

Connection con=[Link] (“jdbc : oracle : thin :@ localhost : 1521 :
chandrashekhar”, “scott”, “tiger”);
On the above connection syntax, “thin” is a driver name. The thin driver converts the JDBC calls
to the vendor’s specific database calls.

Which Driver to use When?

 If you are accessing one type of database, such as Oracle, Sybase, or IBM, the preferred
driver type is type-4.
 If your Java application is accessing multiple types of databases at the same time, type 3 is
the preferred driver.
 Type 2 drivers are useful in situations, where a type 3 or type 4 driver is not available yet
for your database.
 The type 1 driver is not considered a deployment-level driver, and is typically used for
development and testing purposes only.

SIR CRRCOE
ADVANCED JAVA-R23 DEPARTMENT OF INFORMATION TECHNOLOGY

Introduction to major JDBC Classes and Interface:

The interfaces and classes in JDBC API are used to establish a connection and interact with
databases.
A list of popular interfaces of JDBC API are given below:
o Driver interface
o Connection interface
o Statement interface
o PreparedStatement interface
o CallableStatement interface
o ResultSet interface
o ResultSetMetaData interface
o DatabaseMetaData interface
o RowSet interface
A list of popular classes of JDBC API are given below:
o DriverManager class
o Blob class
o Clob class
o Types class
Interfaces
The [Link] package consists of many interfaces. Some popular interfaces are mentioned below:
 Driver Interface: This interface allows for multiple database drivers. DriverManager
objects are created to communicate with the database. These objects are created by
[Link]();
 Connection interface - The connection interface is used to create a connection with the
database. getConnection() method of DriverManager class of the Connection interface is
used to get a Connection object
This interface provides methods such as close(), commit(), rollback(), createStatement(),
prepareCall(), prepareStatement(), setAutoCommit() setSavepoint() etc.
 Statement Interface: This interface represents a static SQL statement. Using the Statement
object and its methods, you can execute an SQL statement and get the results of it.
It provides methods such as execute(), executeBatch(), executeUpdate() etc. To execute the
statements.
 PreparedStatement Interface:
This interface helps when the SQL queries need to implement many times. It accepts input
parameters during runtime.
This represents a precompiled SQL statement. An SQL statement is compiled and stored in
a prepared statement and you can later execute this multiple times. You can get an object of
this interface using the method of the Connection interface named prepareStatement().
This provides methods such as executeQuery(), executeUpdate(), and execute() to execute
the prepared statements and getXXX(), setXXX() (where XXX is the datatypes such as
long int float etc..) methods to set and get the values of the bind variables of the prepared
statement.
 CallableStatement Interface: This interface is used when stored procedures are to be
accessed. It also accepts parameters during run time.
This returns single or multiple results. It will accept input parameters too. You can create a
CallableStatement using the prepareCall() method of the Connection interface.
Just like Prepared statement, this will also provide setXXX() and getXXX() (where XXX is
the datatypes such as long int float etc..) methods to pass the input parameters and to get
the output parameters of the procedures.
 ResultSet Interface: This interface helps to store the result returned after the execution of
the SQL queries.(i.e;This contains the results retrieved after the execution of the SQL
statements or queries.) .executeQuery() method of statement interface returns a resultset
object.

SIR CRRCOE
ADVANCED JAVA-R23 DEPARTMENT OF INFORMATION TECHNOLOGY

 RowSet interface - RowSet interface is a component of Java Bean. It is a wrapper of

ResultSet and is used to keep data in tabular form.
 SQLException: Any errors that occur in database application are handled by this class.
 ResultSetMetaData:This interface is used to get the information about the result set such
as, number of columns, name of the column, data type of the column, schema of the result
set, table name, etc
It provides methods such as getColumnCount(), getColumnName(), getColumnType(),
getTableName(), getSchemaName() etc.

Classes of JDBC API

Along with interfaces, JDBC API uses various classes that implement the above interfaces.
Methods of these classes in JDBC API are used to create connections and execute queries on
databases. A list of most commonly used class in JDBC API are as follows:
 DriverManager class - DriverManager class is a member of the [Link] package. It is
used to establish a connection between the database and its driver.
 Blob class - A [Link] is a binary large object that can store large amounts of binary
data, such as images or other types of files. Fields defined as TEXT also hold large
amounts of data.
 Clob class - The [Link] interface of the JDBC API represents the CLOB datatype.
Since the Clob object in JDBC is implemented using an SQL locator, it holds a logical
pointer to the SQL CLOB (not the data).
 Types class - Type class defined and store constants that are used to identify generic SQL
types also known as JDBC types.

Creating simple JDBC Application:

Creating JDBC Application:
To connect the java program or application with the database there are five steps to be followed:
1. Load a JDBC Driver class
2. Establish a Connection
3. Create a Statement
4. Executing Queries:
5. Close Connection

1. Load the Driver: Driver helps to make a connection to the database hence driver must be
loaded once in the program. This can be done by two methods:
 [Link](): By using this, the driver’s class file is loaded in the memory during run
time. There is no need to create a new object.
For example: [Link]("[Link]");// oracle database
[Link](" [Link] "); // mysql database
 [Link](): Here DriverManager is an inbuilt Java class where the
register is its static member. By using this, the constructor of the driver class is called
during compile time. In this new object is created.
For example: [Link](new [Link]());
NOTE: If you are using JDBC 4.0 or higher: Since JDBC 4.0,you do not need to call
[Link]() explicitly if the driver JAR us present in classpath- driver auto loading works.

2. Creating Connections: After the driver is loaded, the connection is set up. The connection
object uses username, password, and URL to set up the connection. URL has a predefined format
which contains database name, the driver used, IP address where the database is stored, Port
number and the service provider.
10

SIR CRRCOE
ADVANCED JAVA-R23 DEPARTMENT OF INFORMATION TECHNOLOGY

The connection can be set up by using the command:

Connection con = [Link](URL, user, password);
Ex: create connection for mysql:
Connection con = [Link]
("jdbc:mysql://localhost:3306/student", "root", "root");
3. Creating Statement: After establishing the connection, the user can interact with the database.
The interfaces such as JDBC statement, PreparedStatement, CallableStatement provides methods
that allow a user to send SQL statements and get data from the database. Command used to create
statement is;
Statement stmt = [Link]();
4. Executing Query: The SQL query is executed to interact with the database. A query can be for
updating/inserting in the database or for retrieving data.
To execute SQL statements on the database we can use two methods depending on which type of
query we are executing.
 Execute Update: Execute update method is used to execute queries like insert, update,
delete, etc. Return type of executeUpdate() method is int.
Syntax: int check = [Link](sql);
 Execute Query: Execute query method is used to execute queries used to display data from
the database (retrieving data), such as select. Return type of executeQuery() method is
result set.
Syntax: Resultset = [Link](sql);
For Example:
int n = [Link]("DELETE TABLENAME");
if(n==1)
[Link]("Success");
else
[Link]("Failed");
5. Closing Connection: After executing our query, the data user wanted to update or retrieve has
been done so now it’s time to close the established connection. The connection interface provides a
method close() to close the connection.
For example:
[Link]();
Example Program:
Let's see how to connect a Java program using JDBC API (with mysql):
import [Link].*;
public class JdbcExe1
{
public static void main(String[] args) throws SQLException
{
//[Link] ("[Link]");
Connection con = [Link] ("jdbc:mysql://localhost:3306", "root",
"root");
[Link]("Connected Established ");
[Link]();
}
}
11

SIR CRRCOE
ADVANCED JAVA-R23 DEPARTMENT OF INFORMATION TECHNOLOGY

Result:

Creating Database (in mysql) from Java program in eclipse:

import [Link].*;
public class JdbcConn
{
public static void main(String[] args) throws SQLException
{
//[Link] connection with getConnection
Connection con = [Link]("jdbc:mysql://localhost:3306", "root", "root");
// 2. Create a Statement object
Statement stmt = [Link]();

// 3. Execute SQL command to create database

String sql = "CREATE DATABASE Student"; //Here data base name is RegNo
[Link](sql);
[Link]("Data base created");
[Link]();
}

Result:

12

SIR CRRCOE
ADVANCED JAVA-R23 DEPARTMENT OF INFORMATION TECHNOLOGY

Example program to Create a table in Data base using JDBC API:

import [Link].*;
public class Prog1
{
public static void main(String[] args) throws SQLException
{
// Step 1: Load and register JDBC driver (optional in newer versions)
// [Link]("[Link]");
//[Link] connection with Database
Connection con = [Link]("jdbc:mysql://localhost:3306/Student", "root",
"root");
[Link](" database Connected ");
// 3. Create a Statement object
Statement stmt = [Link]();
// SQL query to create a table
String createTableSQL = "CREATE TABLE Studentinfo (" + "id INT PRIMARY KEY
AUTO_INCREMENT, " + "name VARCHAR(100), "
+ "email VARCHAR(100), " + "age INT)";
//[Link] the query
[Link](createTableSQL);
[Link]("Table 'StudentInfo' created successfully!");
// 5. Close resources
[Link]();

Result:

13

SIR CRRCOE
ADVANCED JAVA-R23 DEPARTMENT OF INFORMATION TECHNOLOGY

Types of Statements in JDBC :

In Java, the Statement interface in JDBC (Java Database Connectivity) is used to create and
execute SQL queries in Java applications.
JDBC provides three types of statements to interact with the database:
 Statement
 Prepared Statement
 Callable Statement
1. Statement
A Statement object is used for general-purpose access to databases and is useful for executing
static SQL statements at runtime.
Syntax:
Statement statement = [Link]();

Implementation: Once the Statement object is created, there are three ways to execute it.
 execute(String SQL): It is used to executes any SQL statements (like SELECT,
INSERT, UPDATE or DELETE). If the ResultSet object is retrieved, then it returns
true else false is returned.
 executeUpdate(String SQL): It is used to executes SQL statements (like INSERT,
UPDATE or DELETE). It returns the number of rows affected by the SQL statement.
 ResultSet executeQuery(String SQL): It is used to executes the SELECT query. It
returns a ResultSet object that contains the data retrieved by the query.
Example:
// Java Program illustrating Create Statement in JDBC
import [Link].*;
public class Program3
{
public static void main(String[] args) throws SQLException
{
// [Link]("[Link]");
// Establish JDBC connection and create statement
Connection con =
[Link]("jdbc:mysql://localhost:3306/mydatabase", "root", "root");

// SQL CREATE TABLE statement

String createSQL = "CREATE TABLE Studentinfo ("
+ "id INT PRIMARY KEY AUTO_INCREMENT, "
+ "name VARCHAR(100) NOT NULL, "
+ "email VARCHAR(100), "
+ "age INT"
+ ")";

Statement stmt = [Link]();

// Execute the CREATE statement

[Link](createSQL);
[Link]("Table 'Student' created successfully.");

// Close resources
[Link]();
[Link]();
}
}
14

SIR CRRCOE
ADVANCED JAVA-R23 DEPARTMENT OF INFORMATION TECHNOLOGY

Result:

2. Prepared Statement
A PreparedStatement represents a precompiled SQL statement that can be executed multiple
times. It accepts parameterized SQL queries, with ? as placeholders for parameters, which can be
set dynamically.
Prepared Statement will improve the performance of the application, because query is compiled
only once.
Syntax:
PreparedStatement pstmt = [Link](query);

Illustration:
Considering in the people database if there is a need to INSERT some values, SQL statements such
as these are used:
INSERT INTO people VALUES ("Ayan",25);
INSERT INTO people VALUES("Kriya",32);
To do the same in Java, one may use Prepared Statements and set the values in the ? holders,
setABC() of a prepared statement is used as shown:
String query = "INSERT INTO people(name, age)VALUES(?, ?)";
PreparedStatement pstmt = [Link](query);
// where pstmt is an object name
[Link](1,"Ayan");
[Link](2,25);
Implementation: Once the PreparedStatement object is created, there are three ways to execute it:
 execute(): This returns a boolean value and executes a static SQL statement that is present
in the prepared statement object.
 executeQuery(): This returns a ResultSet from the current prepared statement.
 executeUpdate(): This returns the number of rows affected by the DML statements such as
INSERT, DELETE, and more that is present in the current Prepared Statement.

15

SIR CRRCOE
ADVANCED JAVA-R23 DEPARTMENT OF INFORMATION TECHNOLOGY

Example:
//Create Student Table with RollNo, Name, and Address field and insert few records using Prepared
Statement

[Link].*;
public class Example
{
public static void main(String[ ] args)
{

// [Link]("[Link]");
try (Connection conn =
[Link]("jdbc:mysql://localhost:3306/student","root", "root"))
{
String createSQL = "CREATE TABLE IF NOT EXISTS Student1 (" +
"RollNo INT PRIMARY KEY, " +
"Name VARCHAR(100), " +
"Address VARCHAR(255))";
try (PreparedStatementstmt = [Link](createSQL))
{
[Link]();
[Link]("Student table created.")
}

String insertSQL = "INSERT INTO Student (RollNo, Name, Address) VALUES (?, ?, ?)";
try (PreparedStatementstmt = [Link](insertSQL))
{
[Link](1, 1);
[Link](2, "Alice Johnson");
[Link](3, "123 Main St");
[Link]();

[Link](1, 2);
[Link](2, "Bob Smith");
[Link](3, "456 Oak St");
[Link]();

[Link]("Initial records inserted.");

}
}
catch (SQLExceptione)
{
[Link]();
}
}
}

16

SIR CRRCOE
ADVANCED JAVA-R23 DEPARTMENT OF INFORMATION TECHNOLOGY

Example2:
//Using PreparedStatement Object display the content of Record.

[Link].*;

publicclass Example2
{
publicstaticvoid main(String[] args)
{

try (Connection conn =

[Link]("jdbc:mysql://localhost:3306/student","root", "root"))
{
String selectSQL = "SELECT * FROM Student";
try (PreparedStatementstmt = [Link](selectSQL))
{
ResultSetrs = [Link]();
// Print headers
[Link]("%-8s %-15s %-20s%n", "RollNo", "Name", "Address");

// Print each record

while ([Link]()) {
[Link]("%-8d %-15s %-20s%n",
[Link]("RollNo"),
[Link]("Name"),
[Link]("Address"));
}
}
}
catch (SQLExceptione)
{
[Link]();
}
}
}

/*
Explanation:
% -8s means print a left-aligned string in a width of 8 characters.

% -15s is left-aligned with 15 spaces for the Name.

% -20s is left-aligned with 20 spaces for the Address.

%n adds a newline.
*/

17

SIR CRRCOE
ADVANCED JAVA-R23 DEPARTMENT OF INFORMATION TECHNOLOGY

3. Callable Statement
A CallableStatement is used to execute stored procedures in the database. Stored procedures are
precompiled SQL statements that can be called with parameters. They are useful for executing
complex operations that involve multiple SQL statements.
 It is an interface in JDBC API which is used to call the stored procedures form the Java
code. CallableStatement extends PreparedStatement which in turn extends Statement.
Syntax:
CallableStatement cstmt = [Link]("{call ProcedureName(?, ?)}");
 {call ProcedureName(?, ?)}: Calls a stored procedure named ProcedureName with
placeholders ? for input parameters.
Methods to Execute:
 execute(): Executes the stored procedure and returns a boolean indicating whether the
result is a ResultSet (true) or an update count (false).
 executeQuery(): Executes a stored procedure that returns a ResultSet.
 executeUpdate(): Executes a stored procedure that performs an update and returns the
number of rows affected.

Stored Procedures:
 The [Link] package provides the callable statement interface that contains various
methods to enable you to call database stored procedures.
 The CallableStatement interface is derived from the prepared Statement Interface
What is stored procedure?
• Stored procedure is a set of SQL statements to be executed to perform a specific
task on a database.
• Stored procedures are precompiled and stored in the database.

Like dynamic SQL queries , you can pass the parameters to stored procedures. Stored procedures
take 3 types of parameters.
1. IN Parameter(Default)
2. OUT Parameter
3. IN OUT Parameter
1. IN parameter: It is the default parameter type used to provide input values to a
procedure or a function.
2. OUT parameter: It is used to fetch output values from a procedure. No initial value is
provided to an OUT parameter. Its value is set by the procedure and returned.
3. INOUT parameter: It is similar to the OUT parameter except that it is provided with an
initial value that is modified by the procedure and returned.

--When you use the stored procedures to perform database operations, it reduces the network
traffic because instead of sending multiple SQL Statements to a database, a single stored procedure
is executed.

How to Call Stored Functions and Stored Procedures using JDBC

Stored Procedure
Stored procedure is a set of SQL statements stored together as a single block of code in the
database which can be reused multiple times without having to write the queries again. A stored
procedure may provide multiple output values and accepts input as well as output parameters.
Stored Function
Stored function is a set of SQL statements that perform a particular task. It is similar to a stored
procedure except that it returns a single value and accepts only input parameters.
18

SIR CRRCOE
ADVANCED JAVA-R23 DEPARTMENT OF INFORMATION TECHNOLOGY

Advantages of using Stored Procedures and Stored Functions

 Stored procedures and functions are stored in the database and can be called as and when
required.
 Business and database logic can be stored in the database itself which can be used by
multiple applications.
 Reduces traffic. No need to send the set of queries over the network. Rather procedures
and functions are stored in the database and a call to these stored procedures and functions
can be made to fetch the results.
 This leads to fast execution as they are compiled once and used multiple times.

Stored Procedures vs Stored Functions

Stored Procedure Stored Function

A stored procedure can return zero or more

A stored function returns a single value.
values.

A stored procedure accepts IN, OUT, and

A stored function accepts IN parameters only.
INOUT parameters

A stored procedure can call a stored function A stored function cannot call a stored
within its body. procedure.

A stored procedure can use all DML statements A stored function can only use the SELECT
within its body. statement.

A stored procedure can use try-catch blocks for

A stored function cannot use try-catch blocks.
exception handling.

A stored procedure cannot be embedded into A stored function can be embedded into
SELECT/WHERE/HAVING etc. clauses. SELECT/WHERE/HAVING etc. clauses.

Creating Stored Procedures:

[Link] a Stored Procedure (in your Database)
// SQL
DELIMITER //

CREATE PROCEDURE add_numbers(IN num1 INT, IN num2 INT, OUT result INT)
BEGIN
SET result = num1 + num2;
END //

DELIMITER ;

19

SIR CRRCOE
ADVANCED JAVA-R23 DEPARTMENT OF INFORMATION TECHNOLOGY

Step [Link] the Stored Procedure Using JDBC CallableStatement:

// Java Program illustrating Callable Statement in JDBC
Example 1:
import [Link].*;

public class CallableExample

{
public static void main(String[] args)
{
String url = "jdbc:mysql://localhost:3306/DatabaseName";
String user = "your_username";
String password = "your_password";
try (Connection con = [Link] ("jdbc:mysql://localhost:3306", "root",
"root"); )
{

// Prepare CallableStatement
CallableStatement stmt = [Link]("{call add_numbers(?, ?, ?)}");

// Set IN parameters
[Link](1, 10);
[Link](2, 20);

// Register OUT parameter

[Link](3, [Link]);

// Execute
[Link]();

// Retrieve output
int result = [Link](3);
[Link]("Result: " + result);

} catch (SQLException e)
{
[Link]();
}
}
}
Here,

 ? placeholders are used for input and output parameters.

 Use [Link]() to register output parameters.

 Use [Link]() methods to retrieve output values (e.g., getInt() for INT).

20

SIR CRRCOE
ADVANCED JAVA-R23 DEPARTMENT OF INFORMATION TECHNOLOGY

Example 2: Procedure having No parameters:

// SQL
CREATE PROCEDURE say_hello()
BEGIN
SELECT 'Hello from Procedure!';
END;

//Java code:
CallableStatement stmt = [Link]("{call say_hello()}");
ResultSet rs = [Link]();
while ([Link]()) {
[Link]([Link](1));
}

Example 3: Procedure having INOUT parameters:

// SQL
CREATE PROCEDURE double_number(INOUT num INT)
BEGIN
SET num = num * 2;
END;

//Java code:
CallableStatement stmt = [Link]("{call double_number(?)}");
[Link](1, 5);
[Link](1, [Link]);
[Link]();
[Link]("Doubled: " + [Link](1));

NOTE:

Interfaces Recommended Use

Use this for general-purpose access to your database. Useful when you
Statement are using static SQL statements at runtime. The Statement interface
cannot accept parameters.

Use this when you plan to use the SQL statements many times. The
PreparedStatement
PreparedStatement interface accepts input parameters at runtime.

Use this when you want to access the database stored procedures. The
CallableStatement
CallableStatement interface can also accept runtime input parameters.

21

SIR CRRCOE
ADVANCED JAVA-R23 DEPARTMENT OF INFORMATION TECHNOLOGY

Exploring Result Set Operations:

Java Database Connectivity is Java-based technology and that provides a standard API for
accessing databases in Java applications. The Key Component of Java Database Connectivity is
the ResultSet. JDBC driver allows developers to read and manipulate data from the database.
The JDBC ResultSet is Object which represents the result of a SQL Query executed on a database.
It acts as a cursor to navigate through the retrieved data, manipulate data, Fetching Specific
columns and others. The [Link] interface represents the result set of a database query.

JDBC Result Set in Java

The ResultSet is essentially a table of data where each row represents a record and each column
represents a field in the database. The ResultSet has a cursor that points to the current row in the
ResultSet and we can able to navigate in ResultSet by using the next(), previous(),
first(), and last() methods. We can retrieve data by using different methods like getString(),
getInt(), getDouble() and other methods.
In Java, the ResultSet is the Object which is used for holding the result of a database query
typically the SQL select statement. And It is the part of JDBC API which is used for interacting
with relational databases. The ResultSet allows us over the rows of tables returned by the SQL
query and extract a specific column from the SQL query result.
Syntax:
try {
//[Link]("[Link]");

Connection con = [Link] ("jdbc:mysql://localhost:3306", "root", "root");

Statement stmt = [Link]();
ResultSet rs = [Link]("SELECT * FROM your_table");

while ([Link]())
{
// Process the result set
}

[Link]();
[Link]();
[Link]();
}
catch (SQLException e)
{
[Link](); // Handle the exception
}

Common Operations with ResultSet:

 Fetching data from database: We can fetch data from the database based on the
requirements by using conditional statements.
 Navigating the ResultSet: We can able navigating the ResultSet by using methods like
next(), previous(), first(), and last().
 Getting column values: We can fetch column values with specific conditions or without
conditions.

22

SIR CRRCOE
ADVANCED JAVA-R23 DEPARTMENT OF INFORMATION TECHNOLOGY

 Closing the result: Once database operations are completed we need close the connections
related to database here we close the ResultSet connection. By using close method.
Types of ResultSet
There are three different characteristics by which ResultSet types are differentiated
1. Scrollability: Determines whether you can move back and forth in the ResultSet
 TYPE_FORWARD_ONLY: Can only move forward through the rows
 TYPE_SCROLL_INSENSITIVE: Can move forward and backward but changes
are not reflect ResultSet
 TYPE_SCROLL_SENSITIVE: Can move forward and backward but changes are
affect the ResultSet
2. Concurrency: Determines whether you can update the ResultSet
 CONCUR_READ_ONLY: Can only read data
 CONCUR_UPDATABLE: Allows updates to the ResultSet
3. Holdability: Determines what happens to the ResultSet when a Transaction is committed.
 HOLD_CURSORS_OVER_COMMIT: The ResultSet remains open after a
commit
 CLOSE_CURSORS_AT_COMMIT: The ResultSet closes after a commit
Category of Methods in Result Set:
The methods of the ResultSet interface can be broken down into three categories −
 Navigational methods − Used to move the cursor around.
 Get methods − Used to view the data in the columns of the current row being pointed by
the cursor.
 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.

i). Navigating a ResultSet:

Basically these methods are allow is to navigating through the ResultSet and we can navigate in
different ways, Below We provide those methods to navigate in the ResultSet.

Method Description

next() used for move next row in the ResultSet.

previous() used for move to previous row in the ResultSet

first() used for move to first row in the ResultSet

last() used for move to last row in the ResultSet

absolute(int row) used to move to specific row

used for Moves forward or backward by the specified number of

relative(int rows) rows

beforeFirst() used for Positions the cursor before the first row

23

SIR CRRCOE
ADVANCED JAVA-R23 DEPARTMENT OF INFORMATION TECHNOLOGY

Method Description

afterLast() used for Positions the cursor after the last row

ii). Retrieving Data from a ResultSet:

These methods retrieve data from the current row in the ResultSet. And also You can retrieve data
by column index or column name.

Method Description

getInt(int columnIndex) used for Retrieves an integer from the specified column

getString(int columnIndex) used for Retrieves a string from the specified column

getDouble(int columnIndex) used for Retrieves a double from the specified column

used for Retrieves true or false from the specified

getBoolean(int columnIndex) column

getDate(int columnIndex) used for Retrieves a [Link]

getObject(int columnIndex) used for Retrieves any type of object

getArray(int columnIndex) used for Retrieves a SQL array

iii). Updating Data in a ResultSet:

These methods allow you to update data in the Result.

Method Description

used for Updates an integer value in the

updateInt(int columnIndex, int x) specified column

updateString(int columnIndex, String x) used for Updates a string value

used for Updates a boolean value

updateBoolean(int columnIndex, boolean x)

updateRow() used for Updates a row

deleteRow() used for delete a row

24

SIR CRRCOE
ADVANCED JAVA-R23 DEPARTMENT OF INFORMATION TECHNOLOGY

Example:
//[Link] create a students table in work database. Below we provide table structure
CREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR(50),
grade FLOAT
);
INSERT INTO students VALUES
(1, 'Krishna', 87.5),
(2, 'Anjali', 91.2),
(3, 'Ravi', 76.8);
//Program on Result set operations:
import [Link].*;
public class ResultSetExample {
public static void main(String[] args)
{
try (Connection con = [Link] ("jdbc:mysql://localhost:3306", "root", "root");
Statement stmt = [Link]( ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY );
ResultSet rs = [Link]("SELECT * FROM students"); )
{
[Link]("Forward Iteration (next):");
while ([Link]())
{
int id = [Link]("id");
String name = [Link]("name");
float grade = [Link]("grade");
[Link](id + " | " + name + " | " + grade);
}

[Link]("\nMove to First Row:");

[Link]();
[Link]("Name: " + [Link]("name"));

[Link]("\nMove to Last Row:");

[Link]();
[Link]("Name: " + [Link]("name"));

[Link]("\nMove to 2nd Row (absolute):");

[Link](2);
[Link]("Name: " + [Link]("name"));

} catch (SQLException e) {
[Link]();
}
}
}
Output :
Forward Iteration (next):
1 | Krishna | 87.5
2 | Anjali | 91.2
3 | Ravi | 76.8

25

SIR CRRCOE
ADVANCED JAVA-R23 DEPARTMENT OF INFORMATION TECHNOLOGY

Move to First Row:

Name: Krishna

Move to Last Row:

Name: Ravi

Move to 2nd Row (absolute):

Name: Anjali

Example2:
We create a employees table in work database. Below we provide table structure

Program Implementing the JDBC Result Set

In this example we perform CRUD operations by using ResultSet. After running this program as
java application It show four options to you. Need to select 1 to 4 based on your requirement
below I provide the example with related images.
package mypack;
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];

public class JDBCOperations {

// Establish database connection

private static Connection getConnection()
throws Exception {

String jdbcUrl = "jdbc:mysql://localhost:3306/data";

String jdbcUser = "root";
String jdbcPassword = "password";

return [Link](jdbcUrl, jdbcUser, jdbcPassword);

}

// Insert record into the database

private static void insertRecord(Connection connection,
26

SIR CRRCOE
ADVANCED JAVA-R23 DEPARTMENT OF INFORMATION TECHNOLOGY

String name, double salary)

throws Exception {
String query = "INSERT INTO employees (name, salary) VALUES (?, ?)";

PreparedStatement preparedStatement
= [Link](query);
[Link](1, name);
[Link](2, salary);
[Link]();

[Link]("Record inserted successfully.");

}

// Update record in the database

private static void updateRecord(Connection connection,
int id, String name,double salary)
throws Exception {

String query
= "UPDATE employees SET name = ?, salary = ? WHERE id = ?";

PreparedStatement preparedStatement
= [Link](query);
[Link](1, name);
[Link](2, salary);
[Link](3, id);
[Link]();

[Link]("Record updated successfully.");

}

// Retrieve records from the database

private static void
retrieveRecords(Connection connection) throws Exception {
Statement statement = [Link]();
ResultSet resultSet = [Link]("SELECT * FROM employees");

[Link]("Records in the database:");

while ([Link]()) {
int id = [Link]("id");
String name = [Link]("name");
double salary = [Link]("salary");
[Link]("ID: " + id + ", Name: " + name +
", Salary: " + salary);

}
}

// Delete record from the database

private static void deleteRecord(Connection connection,
int id)
throws Exception {
String query = "DELETE FROM employees WHERE id = ?";
PreparedStatement preparedStatement
= [Link](query);
27

SIR CRRCOE
ADVANCED JAVA-R23 DEPARTMENT OF INFORMATION TECHNOLOGY

[Link](1, id);
[Link]();
[Link]("Record deleted successfully.");
}

public static void main(String[] args) {

Scanner scanner = new Scanner([Link]);
try {
Connection connection = getConnection();

[Link]("Select an operation:");
[Link]("1. Insert");
[Link]("2. Update");
[Link]("3. Retrieve");
[Link]("4. Delete");

int choice = [Link]();

switch (choice) {
case 1:
[Link]("Enter name:");

String nameToInsert = [Link]();

[Link]("Enter salary:");
double salaryToInsert = [Link]();
insertRecord(connection, nameToInsert,
salaryToInsert);
break;

case 2:
[Link]("Enter ID to update:");
int idToUpdate = [Link]();
[Link]("Enter new name:");
String nameToUpdate = [Link]();
[Link]("Enter new salary:");
double salaryToUpdate
= [Link]();
updateRecord(connection, idToUpdate,
nameToUpdate, salaryToUpdate);
break;

case 3:
retrieveRecords(connection);
break;

case 4:
[Link]("Enter ID to delete:");
int idToDelete = [Link]();
deleteRecord(connection, idToDelete);
break;

default:
[Link]("Invalid choice.");
break;
}
28

SIR CRRCOE
ADVANCED JAVA-R23 DEPARTMENT OF INFORMATION TECHNOLOGY

// Close the connection at the end

[Link]();
} catch (Exception e) {
[Link]();
} finally {
[Link]();
}
}
}
Explanation of the above Program:
 Database Connection: Create Database connection by using configuration details like
username, password, database name and other information. Below we provide the
database connection java code.
 Insert Record: Now we implement logic for inserting new records by using this connection
object. And we use PreparedStatement for preventing SQL Injection and It will takes input
data in the form placeholders. After that we execute the SQL query by using
executeUpdate method from PreparedStatement.
 Update Record: Now we implement logic for update function. We employees details
based on their existing employee id only. It is same like inserting new record but before
that we fetch existing data then only we can able to update the data.
 Fetch All Records: Now we develop logic for fetching all records from the table by
using ResultSet object. First we execute the SQL query then hold that result in ResultSet
Object. Then we iterate that data and print line by line the entire data in the table.
 Delete Record by Using ID: In this function we can delete an existing employee record by
using employee ID from the Table.
 Main Function: In main function of the class, We develop logic for selecting operation
from the console by using switch statement in java. Based on the selection you can
perform related database operation. Below we provide that entire code for your
reference.
Output:
Showing available database operations

Now enter option 1, That means you can ready to insert data into database.

29

SIR CRRCOE
ADVANCED JAVA-R23 DEPARTMENT OF INFORMATION TECHNOLOGY

Now enter option 2, That means you can ready to update employee data by employee ID

Now enter option 3, That means you can fetch all data from table.

Now enter option 4, That means you can delete an employee details by using employee ID.

30

SIR CRRCOE
ADVANCED JAVA-R23 DEPARTMENT OF INFORMATION TECHNOLOGY

Batch Updates in JDBC:

Grouping a set of INSERT or, UPDATE or, DELETE commands (those produce update count
value) and execute them at once this mechanism is known as a batch update.
Adding statements to the batch:
The statement, PreparedStatement, and CallableStatement objects hold a list (of commands) to
which you can add related statements (those return update count value) using the addBatch( )
method.
[Link](insert1);
[Link](insert2);
[Link](insert3);
Executing the batch
After adding the required statements, you can execute a batch using the executeBatch() method of
the Statement interface.
[Link]();
Using batch updates, we can reduce the communication overhead and increase the performance of
our Java application.
Note: Before adding statements to the batch you need to turn the auto commit off using
the [Link](false) and, after executing the batch you need to save the changes using
the [Link]() method.
Example:
Assume we have created a table named Sales in the database with the following description:

This Example tries to insert a set of statements into the above mentioned table using batch update.
import [Link];
import [Link];
import [Link];
public class BatchUpdates
{
public static void main(String args[ ])throws Exception
{
//Getting the connection
Connection con = [Link]("jdbc:mysql://localhost:3306/mydb", "root",
"root");
[Link]("Connection established......");
//Creating a Statement object
Statement stmt = [Link]();
//Setting auto-commit false
[Link](false);
//Statements to insert records
String insert1 = "INSERT INTO Dispatches VALUES ('KeyBoard', 'Amith', 'January', 1000,
'Hyderabad')";

31

SIR CRRCOE
ADVANCED JAVA-R23 DEPARTMENT OF INFORMATION TECHNOLOGY

String insert2 = "INSERT INTO Dispatches VALUES ('Earphones', 'SUMITH', 'March', 500,
'Vishakhapatnam')";
String insert3 = "INSERT INTO Dispatches VALUES ('Mouse', 'Sudha', 'September', 200,
'Vijayawada')";
//Adding the statements to batch
[Link](insert1);
[Link](insert2);
[Link](insert3);
//Executing the batch
[Link]();
//Saving the changes
[Link]();
[Link]("Records inserted......");
}
}

Output
Connection established......
Records inserted......
If you verify the contents of the table, you can find the inserted records in it as:

Creating CRUD Application:

What are CRUD Operations?
CRUD stands for Create, Read, Update, and Delete — the four basic operations used to manage
data in a database. These actions allow users and programs to add new records, retrieve existing
ones, modify data, and remove unwanted entries. CRUD operations are the foundation of any
application that interacts with a database, whether it’s a banking system, an e-commerce platform,
or a simple contact manager. In Java, CRUD operations are typically implemented using JDBC
(Java Database Connectivity), which provides the tools to connect to databases and execute SQL
statements to perform these fundamental tasks efficiently and securely.
CRUD Operation with JDBC:
Creating, reading, updating, and deleting data in a database is a common task in many
applications, and JDBC (Java Database Connectivity) is a Java API that allows you to connect to a
database and perform these operations. In this blog post, we will walk through the steps of setting
up a simple CRUD (create, read, update, delete) operation using JDBC.
1. Connect to the database
The first step is to establish a connection to the database. You can do this by loading the JDBC
driver and creating a connection object.
try
{
[Link]("[Link]");

32

SIR CRRCOE
ADVANCED JAVA-R23 DEPARTMENT OF INFORMATION TECHNOLOGY

Connection con = [Link]("jdbc:mysql://localhost:3306/mydb",

"username","password");
[Link]("Connection established.");
}
catch (Exception e)
{
[Link]();
}
2. Create a new record
Once you have a connection to the database, you can use the connection object to create a new
record in the database. To do this, you will need to use an SQL INSERT statement and execute it
using the connection object.
try {
String sql = "INSERT INTO table_name (column1, column2, column3) VALUES (?, ?, ?)";
PreparedStatement statement = [Link](sql);
[Link](1, "value1");
[Link](2, "value2");
[Link](3, 123);
[Link]();
[Link]("Record created.");
} catch (SQLException e) {
[Link]();
}
3. Read a record
To read a record from the database, you will need to use an SQL SELECT statement and execute it
using the connection object. The result of the query will be a ResultSet object that you can use to
access the data in the record.

try
{
String sql = "SELECT column1, column2, column3 FROM table_name WHERE id = ?";
PreparedStatement statement = [Link](sql);
[Link](1, 1);
ResultSet result = [Link]();
if ([Link]()) {
String column1 = [Link]("column1");
String column2 = [Link]("column2");
int column3 = [Link]("column3");
[Link]("Column 1: " + column1);
[Link]("Column 2: " + column2);
[Link]("Column 3: " + column3);
}
}
catch (SQLException e)
{
[Link]();
}

33

SIR CRRCOE
ADVANCED JAVA-R23 DEPARTMENT OF INFORMATION TECHNOLOGY

4. Update a record
To update a record in the database, you will need to use an SQL UPDATE statement and execute it
using the connection object.
try {
String sql = "UPDATE table_name SET column1 = ?, column2 = ?, column3 = ? WHERE id =
?";
PreparedStatement statement = [Link](sql);
[Link](1, "new_value1");
[Link](2, "new_value2");
[Link](3, 456);
[Link](4, 1);
[Link]();
[Link]("Record updated.");
} catch (SQLException e) {
[Link]();
}

5. Delete a record
To delete a record from the database, you will need to use an SQL DELETE statement and execute
it using the connection object.
try {
String sql = "DELETE FROM table_name WHERE id = ?";
PreparedStatement statement = [Link](sql);
[Link](1, 1);
[Link]();
[Link]("Record deleted.");
} catch (SQLException e) {
[Link]();
}

Using Rowsets Objects:

RowSet is an interface in java that is present in the [Link] package.

Note: RowSet is present in package [Link] while ResultSet is present in package [Link].

The instance of RowSet is the java bean component because it has properties and a java bean
notification mechanism. It is introduced in JDK5. A JDBC RowSet provides a way to store the
data in tabular form. It makes the data more flexible and easier than a ResultSet. The connection
between the RowSet object and the data source is maintained throughout its life cycle.

RowSets are classified into five categories based on how they are implemented which are listed
namely as below:
JdbcRowSet
CachedRowSet
WebRowSet
FilteredRowSet
JoinRowSet

34

SIR CRRCOE
ADVANCED JAVA-R23 DEPARTMENT OF INFORMATION TECHNOLOGY

The advantage of RowSet is as follows:

[Link] is easy and flexible to use.
[Link] is by default scrollable and can be updated by default whereas ResultSet by default is only
forwardable and read-only operation is valid there only.

The JDBC RowSet interface is a RowSet extension. It's a wrapper for the ResultSet object that
adds some extra features.
Syntax: Declaration of Jdbc RowSet interface
public interface JdbcRowSet
extends RowSet, Joinable
In order to connect RowSet with the database, the RowSet interface provides methods for
configuring Java bean properties which are depicted below:
void setURL(String url):
void setUserName(String user_name):
void setPassword(String password):
Lastly, we just need to create a JdbcRowSet object where a sample is shown below illustration as
follows:
Illustration:
JdbcRowSetrowSet = [Link]().createJdbcRowSet();

// 1. Oracle database considered

[Link]("jdbc:oracle:thin:@localhost:1521:xe");

// 2. username is set customly as - root

[Link]("root");

// 3. Password is set customly as - pass

[Link]("pass");

// 4. Query
[Link]("select * from Students");

Implementation: Assume we have a table named student in the database as:

35

SIR CRRCOE
ADVANCED JAVA-R23 DEPARTMENT OF INFORMATION TECHNOLOGY

Implementing JdbcRowSet and retrieving the records

// Java Program to Illustrate RowSet in JDBC

// Importing database
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];

// Main class
class RowSetDemo {

// Main driver method

public static void main(String args[])
{

// Try block to check for exceptions

try {

// Loading and registering drivers

[Link]("[Link]");

// Creating a RowSet
JdbcRowSetrowSet = [Link]()
.createJdbcRowSet();

// Setting URL, username, password

[Link](
"jdbc:oracle:thin:@localhost:1521:xe");
[Link]("root");
[Link]("pass");

// Creating a query
[Link]("select * from Student");

// Executing the query

[Link]();

// Processing the results

while ([Link]()) {

// Print and display commands

[Link]("RollNo: "
+ [Link](1));
[Link]("Name: "
+ [Link](2));
[Link]("Marks: "
+ [Link](3));
}
}

36

SIR CRRCOE
ADVANCED JAVA-R23 DEPARTMENT OF INFORMATION TECHNOLOGY

// Catch block to handle the exceptions

catch (Exception e) {

// Print and display the exception along with

// line number using printStackTrace() method
[Link]();
}
}
}

Output:
RollNo: 1
Name: jack
Marks: 92
RollNo: 2
Name: jenny
Marks: 90
RollNo: 3
Name: mark
Marks: 80
RollNo: 4
Name: joe
Marks: 82

EXAMPLE2:
Step: Table set up
//SQL
CREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR(50),
grade FLOAT
);

INSERT INTO students VALUES

(1, 'Krishna', 88.5),
(2, 'Anjali', 92.3),
(3, 'Ravi', 79.4);

Step 2: Java Example Using RowSet:

//Code Using JdbcRowSet (Connected RowSet)

import [Link];
import [Link];
import [Link];

public class JdbcRowSetExample {

public static void main(String[] args) {
try {
// Creating JdbcRowSet instance
JdbcRowSet rowSet = new JdbcRowSetImpl();
37

SIR CRRCOE
ADVANCED JAVA-R23 DEPARTMENT OF INFORMATION TECHNOLOGY

// Set DB connection details

[Link]("jdbc:mysql://localhost:3306/your_database");
[Link]("your_username");
[Link]("your_password");

// Set SQL command and execute

[Link]("SELECT * FROM students");
[Link]();

// Process the results

[Link]("ID | Name | Grade");
[Link]("------------------------");
while ([Link]()) {
[Link]("%d | %-8s | %.2f\n",
[Link]("id"),
[Link]("name"),
[Link]("grade"));
}

[Link]();
} catch (SQLException e) {
[Link]();
}
}
}

//Code Using CachedRowSet (Disconnected RowSet)

import [Link];
import [Link];

public class CachedRowSetExample {

public static void main(String[] args) {
try {
// Creating CachedRowSet instance
CachedRowSet crs = new CachedRowSetImpl();

// Set DB connection details

[Link]("jdbc:mysql://localhost:3306/your_database");
[Link]("your_username");
[Link]("your_password");

// Set SQL command and execute

[Link]("SELECT * FROM students");
[Link](); // disconnects after fetching

// Process the results (now offline)

[Link]("Cached RowSet Results:");
38

SIR CRRCOE
ADVANCED JAVA-R23 DEPARTMENT OF INFORMATION TECHNOLOGY

while ([Link]()) {
[Link]("Name: " + [Link]("name") + ", Grade: " +
[Link]("grade"));
}

[Link]();
} catch (Exception e) {
[Link]();
}
}
}

Requirements
1. JAR dependency for [Link]:

If you're using a build tool like Maven:

xml
<dependency>
<groupId>[Link]</groupId>
<artifactId>rowset</artifactId>
<version>1.0.1</version>
</dependency>
2. Add mysql-connector-java to your classpath.

Output:
ID | Name | Grade
------------------------
1 | Krishna | 88.50
2 | Anjali | 92.30
3 | Ravi | 79.40

Cached RowSet Results:

Name: Krishna, Grade: 88.5
Name: Anjali, Grade: 92.3
Name: Ravi, Grade: 79.4

39

SIR CRRCOE
ADVANCED JAVA-R23 DEPARTMENT OF INFORMATION TECHNOLOGY

Managing Database Transaction:

Transaction Management in JDBC:
Transaction represents a single unit of work.

The ACID properties describes the transaction management well. ACID stands for Atomicity,
Consistency, isolation and durability.
Atomicity means either all successful or none.
Consistency ensures bringing the database from one consistent state to another consistent state.
Isolation ensures that transaction is isolated from other transaction.
Durability means once a transaction has been committed, it will remain so, even in the event of
errors, power loss etc.

Advantage of Transaction Mangaement

fast performance It makes the performance fast because database is hit at the time of commit.
Transaction management in jdbc

In JDBC, Connection interface provides methods to manage transaction.

Method Description

void setAutoCommit(boolean status) It is true bydefault means each

transaction is committed bydefault.

void commit() commits the transaction.

void rollback() cancels the transaction.

//simple JDBC program that shows how to manage transactions using setAutoCommit(false),
commit(), and rollback().
Step1: SQL Table
CREATE TABLE bank (
acc_no INT PRIMARY KEY,
name VARCHAR(50),
balance DOUBLE
);

INSERT INTO bank VALUES (1, 'Account A', 1000);

INSERT INTO bank VALUES (2, 'Account B', 500);

40

SIR CRRCOE
ADVANCED JAVA-R23 DEPARTMENT OF INFORMATION TECHNOLOGY

Step 2: Simple Java Program

import [Link].*;

public class SimpleTransactionExample

{
public static void main(String[ ] args)
{
try ( Connection con =
[Link]("jdbc:mysql://localhost:3306/your_database","your_username","yo
ur_password");)
{
[Link](false); // Start transaction

try {
// Debit ₹100 from Account A
PreparedStatement debit = [Link](
"UPDATE bank SET balance = balance - 100 WHERE acc_no = 1");
[Link]();

// Credit ₹100 to Account B

PreparedStatement credit = [Link](
"UPDATE bank SET balance = balance + 100 WHERE acc_no = 2");
[Link]();

[Link](); // Commit both operations

[Link]("Transaction successful!");

}
catch (SQLException e)
{
[Link](); // Rollback if any error
[Link]("Transaction failed. Rolled back.");
[Link]();
}

}
catch (SQLException e)
{
[Link]();
}
}
}

Output (if successful):

Transaction successful!
If something fails (like a wrong account number):
Transaction failed. Rolled back.

41

SIR CRRCOE