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, PreparedStatement, CallableStatement), Exploring ResultSet Operations, Batch
Updates in JDBC, Creating CRUD Application, Using Rowsets Objects, Managing Database
Transaction

Introduction:
JDBC stands for Java Database Connectivity, which is a standard Java API for database-
independent connectivity between the Java programming language and a wide range of
databases.

The JDBC library provides a set of APIs to perform various common database operations, such
as:

 Establishing a connection with the database

 Creating SQL or MySQL statements
 Executing queries on the database
 Retrieving and updating the resulting data records

At its core, JDBC is a specification that defines a comprehensive set of interfaces enabling
platform-independent access to various databases. Java supports the creation of multiple types of
executables, including:

 Java Applications
 Java Applets
 Java Servlets
 JavaServer Pages (JSPs)
 Enterprise JavaBeans (EJBs)

Each of these Java-based components can utilize a JDBC driver to connect to a database and
interact with the stored data effectively.

JDBC Architecture

The JDBC API supports both two-tier and three-tier processing models for database access but in
general, JDBC Architecture consists of two layers - JDBC API and JDBC Driver API.

JDBC API − Provides the application-to-JDBC Manager connection.

JDBC Driver API − Supports the JDBC Manager-to-Driver Connection.

JDBC Programming
Page 1
Figure 1.1 JDBC Architecture

The JDBC API uses a driver manager and database-specific drivers to provide transparent
connectivity to heterogeneous databases.

The JDBC driver manager ensures that the correct driver is used to access each data source. The
driver manager is capable of supporting multiple concurrent drivers connected to multiple
heterogeneous databases.

JDBC Components
The JDBC API provides the following interfaces and classes −

DriverManager − This class manages a list of database drivers. Matches connection requests
from the java application with the proper database driver using communication sub protocol. The
first driver that recognizes a certain subprotocol under JDBC will be used to establish a database
Connection.

Driver − This interface handles the communications with the database server. You will interact
directly with Driver objects very rarely. Instead, you use DriverManager objects, which manages
objects of this type. It also abstracts the details associated with working with Driver objects.

Connection − This interface with all methods for contacting a database. The connection object
represents communication context, i.e., all communication with database is through connection
object only.

Statement − You use objects created from this interface to submit the SQL statements to the
database. Some derived interfaces accept parameters in addition to executing stored procedures.

ResultSet − These objects hold data retrieved from a database after you execute an SQL query
using Statement objects. It acts as an iterator to allow you to move through its data.

SQLException − This class handles any errors that occur in a database application.

JDBC Programming
Page 2
Types of JDBC Drivers

 A JDBC Driver is a middleware driver that translates the JDBC calls to the vendor –
specific APIs.
 To access the data there is a need of database driver, probably supplied by the database
vendor or by a J2EE service provider. A driver is nothing but an implementation of
various interfaces specified in java.sql and javax.sql packages.
 JDBC drivers are client-side adapters that translate requests from Java programs into a
protocol understood by the DBMS.
 JDBC drivers are software components that implement the interfaces in the JDBC API,
allowing Java applications to interact with a database

There are four different approaches to connect an application to a database server via database
driver.
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 Pure Java driver

Type 1 – JDBC-ODBC Bridge

Open Database Connectivity(ODBC) : was originally created to provide API standard for SQL
on Microsoft Windows platforms and was later enhanced to provide SDKs for the other
platforms.
The first category of JDBC drivers provides a bridge between the JDBC API and the ODBC
API. This bridge translates the standard JDBC calls to corresponding ODBC calls, and sends
them to the ODBC data source via ODBC libraries:

• Type-1 driver or JDBC-ODBC bridge driver uses ODBC driver to connect to the
database.

JDBC Programming
Page 3
 • The JDBC-ODBC bridge driver converts JDBC method calls into the ODBC function
calls.
• Type-1 driver is also called Universal driver because it can be used to connect to any of
the databases.
Advantages
• This driver software is built-in with JDK so no need to install separately.
• It is a database independent driver.
Disadvantages
• A common driver is used to connect with different databases, but this makes the data
transfer less secure. Also, the ODBC bridge driver needs to be installed on every client
computer separately.
• Since the Type-1 driver is not written in Java, it cannot run on all platforms, so it's not
portable
2. Native-API Driver - Type 2 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, so 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.
• More secure compared to the type-1 driver.
Disadvantages
• Driver needs to be installed separately in individual client machines
• The Vendor client library needs to be installed on client machine.
• Type-2 driver isn't written in java, that's why it isn't a portable driver
• It is a database dependent driver.
Network Protocol Driver - Type 3 Driver

JDBC Programming
Page 4
 •
The Network Protocol driver uses middleware that converts JDBC calls directly or
indirectly into the vendor-specific database protocol.
• No need of individual client-side installation because here all the database connectivity
drivers are present in a single server.
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.
• Switch facility to switch over from one database to another database.
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.
Pure Java Driver - Type 4 Driver

JDBC Programming
Page 5
 •
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
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

Introduction to major JDBC Classes and Interface

DriverManager: This class is responsible for managing all the JDBC drivers. Before using a
database, you must register its driver with the DriverManager.
It offers useful methods like registerDriver() to register drivers and getConnection() to
establish a connection to the database.
• Driver: This is the base interface for all JDBC drivers.
 If you're developing your own JDBC driver, you must implement this interface.
 When a driver class is loaded, it automatically creates an instance of itself and
registers with the DriverManager.
 Ex: Class.fonName(“com.mysql.cj.jdbc.Driver”); is used to load the Jdbc Driver
• Statement: Used to execute static SQL queries.
 We can create a Statement object and then use it to run SQL commands like SELECT,
INSERT, UPDATE, etc. Common methods include execute(), executeUpdate(), and
executeBatch().
 We can create a statement by using Statement stmt=con.CreateStaement();
• PreparedStatement: This interface represents a precompiled SQL statement.
 We can prepare the SQL once and can execute it multiple times with different values.
 It is safer and faster than Statement, especially for dynamic queries.
JDBC Programming
Page 6
  Use Connection.prepareStatement() to create it.
 It supports methods like executeQuery(), executeUpdate() for execution, and
setXXX(), getXXX() to set or retrieve parameter values.
• CallableStatement: Used to execute stored procedures in the database.
 These can return single or multiple results and also take input and output parameters.
 We can create a CallableStatement using Connection.prepareCall().
 Like PreparedStatement, it also supports setXXX() and getXXX() methods.
• Connection: This interface represents the actual connection to a database.
 All database operations are done through this connection.
 It provides methods like createStatement(), prepareStatement(), prepareCall() to create
SQL statements, and close(), commit(), rollback() to manage transactions.
 We can also control auto-commit and set savepoints using setAutoCommit() and
setSavepoint().
• ResultSet: Represents the data retrieved from the database after executing a query.
 It acts like a table where we can move through rows and access column data.
 Use methods like getInt(), getString(), etc., to read data, and update methods to modify it.

Creating simple JDBC Application

Creating a Simple JDBC Application involves a basic steps:
1. Loading the JDBC driver
2. Establishing a connection to the database
3. Executing SQL queries
4. Handling the results
Setup Requirements
• JDK installed
• MySQL installed
• MySQL JDBC Driver (Connector/J) added to your project (e.g., in Eclipse or IntelliJ)
• Database & table created in MySQL

Example Application: Insert and Display Data from MySQL Table

• Create a MySQL Database and Table
CREATE DATABASE studentdb;
USE studentdb;
CREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR(50),
marks INT
);

Java Code (SimpleJDBCApp.java)

import java.sql.*;
public class SimpleJDBCApp {
public static void main(String[] args) {
// Database credentials
String url = "jdbc:mysql://localhost:3306/studentdb";
String username = "root"; // replace with your MySQL username
JDBC Programming
Page 7
 String password = "password"; // replace with your MySQL password
try {
// Step 1: Load JDBC Driver (optional for newer versions)
Class.forName("com.mysql.cj.jdbc.Driver");
// Step 2: Establish Connection
Connection conn = DriverManager.getConnection(url, username, password);
System.out.println("Connected to database.");
// Step 3: Create a Statement
Statement stmt = conn.createStatement();
// Step 4: Execute Insert Query
String insertQuery = "INSERT INTO students VALUES (1, „sai', 85)";
stmt.executeUpdate(insertQuery);
System.out.println("Record inserted.");
// Step 5: Execute Select Query
String selectQuery = "SELECT * FROM students";
ResultSet rs = stmt.executeQuery(selectQuery);
// Step 6: Process ResultSet
System.out.println("ID\tName\tMarks");
while (rs.next()) {
System.out.println(rs.getInt("id") + "\t" + rs.getString("name") + "\t" +
rs.getInt("marks"));
}
// Step 7: Close Resources
rs.close();
stmt.close();
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}

OUTPUT
Connected to database.
Record inserted.
ID Name Marks
1 sai 85

Types of Statements
• Statement interface is used to create and execute SQL queries in Java applications.
• JDBC provides three types of statements to interact with the database:
1. Statement
2. Prepared Statement
3. Callable Statement

JDBC Programming
Page 8
 Statement
Statement object is used for general-purpose access to databases and is useful for executing
static SQL statements at runtime.
Syntax:
• Statement statement = connection.createStatement();
• Once the Statement object is created, there are three ways to execute it.
i. 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.
ii. 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.
iii. 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.
// Java Program illustrating Create Statement in JDBC
import java.sql.*;
public class statex {
public static void main(String[] args) {
try {

// Load the driver

Class.forName("com.mysql.cj.jdbc.Driver");
// Establish the connection
Connection con = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/stddb", "root", "root");
// Create a statement
Statement st = con.createStatement();
// Execute a query

String sql = "SELECT * FROM studentsdetails";

ResultSet rs = st.executeQuery(sql);
// Process the results
while (rs.next()) {
System.out.println("Name: " + rs.getString("name") +
", Age: " + rs.getInt("age"));
}
// Close resources
rs.close();
st.close();
con.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}

JDBC Programming
Page 9
 Prepared Statement
• A Prepared Statement 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.

Methods of PreparedStatement

Method Description

setInt(int, int) Set an int value

setString(int, String) Set a String value

setDouble(int, double) Set a double value

executeUpdate() Executes INSERT, UPDATE, DELETE

executeQuery() Executes SELECT and returns a ResultSet

close() Closes the statement

Syntax
Connection con = DriverManager.getConnection(DB_URL, USER, PASS);
String sql = "INSERT INTO table_name (column1, column2) VALUES (?, ?)";
PreparedStatement pstmt = con.prepareStatement(sql);
// Set parameters
pstmt.setString(1, "John");
pstmt.setInt(2, 25);
// Execute the query
pstmt.executeUpdate();

Example SQL Queries Using PreparedStatement

Insert:
String sql = "INSERT INTO students (id, name, age) VALUES (?, ?, ?)";
Update:
String sql = "UPDATE students SET name = ? WHERE id = ?";
Delete:
String sql = "DELETE FROM students WHERE id = ?";
Select:
String sql = "SELECT * FROM students WHERE age > ?";

JDBC Programming
Page 10
Example program for INSERT using PreparedStatement
import java.sql.*;
public class PreparedStatementExample {
public static void main(String[] args) {
// JDBC URL, username and password of MySQL server
String url = "jdbc:mysql://localhost:3306/studentdb";
String user = "root";
String password = "your_password_here";
// SQL Insert Query
String query = "INSERT INTO students (id, name, age) VALUES (?, ?, ?)";
try {
// Step 1: Load JDBC Driver (optional for newer versions)
Class.forName("com.mysql.cj.jdbc.Driver");
// Step 2: Establish Connection
Connection con = DriverManager.getConnection(url, user, password);
// Step 3: Create PreparedStatement
PreparedStatement pstmt = con.prepareStatement(query);
// Step 4: Set values to the placeholders (?)
pstmt.setInt(1, 101); // Set id
pstmt.setString(2, "John Doe"); // Set name
pstmt.setInt(3, 20); // Set age
// Step 5: Execute the query
int rowsInserted = pstmt.executeUpdate();
System.out.println(rowsInserted + " row(s) inserted successfully!");
// Step 6: Close resources
pstmt.close();
con.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}

Example program for SELECT using PreparedStatement

import java.sql.*;
public class SelectPreparedStatementExample {
public static void main(String[] args) {
// JDBC URL, username, and password
String url = "jdbc:mysql://localhost:3306/studentdb";
String user = "root";
String password = "your_password_here";
// SQL SELECT query with a placeholder
String query = "SELECT * FROM students WHERE age > ?";
try {
// Step 1: Load the JDBC driver
JDBC Programming
Page 11
 Class.forName("com.mysql.cj.jdbc.Driver");
// Step 2: Establish a connection
Connection con = DriverManager.getConnection(url, user, password);
// Step 3: Create PreparedStatement
PreparedStatement pstmt = con.prepareStatement(query);
// Step 4: Set value for the placeholder
pstmt.setInt(1, 18); // Selecting students with age > 18
// Step 5: Execute the query
ResultSet rs = pstmt.executeQuery();
// Step 6: Process the result
System.out.println("ID\tName\t\tAge");
System.out.println("-----------------------------");
while (rs.next()) {
int id = rs.getInt("id");
String name = rs.getString("name");
int age = rs.getInt("age");
System.out.println(id + "\t" + name + "\t\t" + age);
}
// Step 7: Close resources
rs.close();
pstmt.close();
con.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
3. Callable Statement
 A Callable Statement 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.
Syntax:
• CallableStatement cstmt = con.prepareCall("{call ProcedureName(?, ?)}");
• {call ProcedureName(?, ?)}: Calls a stored procedure named ProcedureName with
placeholders ? for input parameters.

Methods of CallableStatement

Method Purpose
setInt(paramIndex, value) Sets input parameter
registerOutParameter(index, type) Registers an output parameter
getInt(index) or getString(index) Retrieves output value

JDBC Programming
Page 12
execute() Executes the stored procedure
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

Example: stored procedure in MySQL

DELIMITER //
CREATE PROCEDURE getStudentName(IN studentId INT, OUT studentName
VARCHAR(100))
BEGIN
SELECT name INTO studentName FROM students WHERE id = studentId;
END //
DELIMITER ;

Java program to call the above procedure

import java.sql.*;
public class CallableStatementExample {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/studentdb";
String user = "root";
String password = "your_password_here";
try {
// Load Driver
Class.forName("com.mysql.cj.jdbc.Driver");
// Connect to Database
Connection con = DriverManager.getConnection(url, user, password);
// Prepare the CallableStatement
CallableStatement cstmt = con.prepareCall("{call getStudentName(?, ?)}");
// Set input parameter (IN)
cstmt.setInt(1, 101);
// Register output parameter (OUT)
cstmt.registerOutParameter(2, Types.VARCHAR);
// Execute
cstmt.execute();
// Get output parameter value
String name = cstmt.getString(2);
System.out.println("Student Name: " + name);
// Close resources
cstmt.close();
con.close();
} catch (Exception e) {
e.printStackTrace();

JDBC Programming
Page 13
 }
}
}
Advantages of callable statements
• Reuse of logic stored in the database.
• Reduces network traffic (less SQL code sent).
• Improves performance with precompiled logic.
• Better security through stored procedures.
Exploring ResultSet Operations
• 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
• Syntax
• try {
Connection conn = DriverManager.getConnection(url, username, password);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM your_table");

while (rs.next()) {
// Process the result set
}

rs.close();
stmt.close();
conn.close();
}
catch (SQLException e) {
e.printStackTrace(); // Handle the exception
}

Navigating a ResultSet
• In JDBC, when we execute a query like SELECT * FROM students, the result is stored in
a ResultSet object.
• The ResultSet object has a cursor that starts before the first row, and we can move it
using:

JDBC Programming
Page 14
 Example Program: Navigating ResultSet
import java.sql.*;
public class ResultSetNavigationExample {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/testdb"; // your DB name
String user = "root";
String password = "password"; // replace with your MySQL password
try
{
Connection conn = DriverManager.getConnection(url, user, password);
// Create scrollable ResultSet
Statement stmt = conn.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY );
ResultSet rs = stmt.executeQuery("SELECT * FROM student");
){
System.out.println("Using next():");
while (rs.next()) {
System.out.println(rs.getInt("id") + " - " + rs.getString("name"));
}
System.out.println("\nUsing last():");
rs.last(); // Move to last row
System.out.println(rs.getInt("id") + " - " + rs.getString("name"));
System.out.println("\nUsing first():");
rs.first(); // Move to first row
System.out.println(rs.getInt("id") + " - " + rs.getString("name"));
System.out.println("\nUsing previous():");
if (rs.previous()) { // Move before first, so check
System.out.println(rs.getInt("id") + " - " + rs.getString("name"));
} else {
System.out.println("No previous row (cursor was at first row)");
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}

JDBC Programming
Page 15
 Batch Updates in JDBC
Grouping a set of INSERT or, UPDATE or, DELETE commands and execute them at
once this mechanism is known as a batch update.

Steps to perform Batching with Statement Object

1. Create a Statement object using either createStatement() methods.
2. Set auto-commit to false using setAutoCommit().
3. Add as many as SQL statements you like into batch using addBatch() method on created
statement object.
4. Execute all the SQL statements using executeBatch() method on created statement object.
5. Finally, commit all the changes using commit() method.

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.
Syntax
stmt.addBatch(insert1); stmt.addBatch(insert2); stmt.addBatch(insert3);
Executing the batch
• After adding the required statements, we can execute a batch using
the executeBatch() method of the Statement interface.
Syntax:
stmt.executeBatch();

Advantages of BatchUpdate
• Reduces network overhead.
• Improves execution speed for bulk operations.
• Especially useful in loops when inserting/updating many records.

Example Program
import java.sql.*;
public class BatchInsertExample {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/studentdb";
String user = "root";
String password = "your_password_here";
try {
// Load Driver
Class.forName("com.mysql.cj.jdbc.Driver");
// Connect to DB
Connection con = DriverManager.getConnection(url, user, password);
// Disable auto-commit for batch
con.setAutoCommit(false);
// Create Statement
Statement stmt = con.createStatement();
JDBC Programming
Page 16
 // Add multiple insert statements to batch
stmt.addBatch("INSERT INTO students (id, name) VALUES (201, 'Alice')");
stmt.addBatch("INSERT INTO students (id, name) VALUES (202, 'Bob')");
stmt.addBatch("INSERT INTO students (id, name) VALUES (203, 'Charlie')");

// Execute batch
int[] result = stmt.executeBatch();
// Commit the batch
con.commit();
System.out.println("Batch executed successfully!");
// Close resources
stmt.close();
con.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
Output: Batch executed successfully!

Table Content After Execution

SELECT * FROM students WHERE id IN (201, 202, 203)
Will get
| id | name |
| 201 | Alice |
| 202 | Bob |
| 203 | Charlie |

Creating CRUD Application

• CRUD (Create, Read, Update, Delete)
• create a complete CRUD (Create, Read, Update, Delete) application using JDBC in
Java.
Mysql code
CREATE DATABASE testdb;
USE testdb;
Java Application Program
import java.sql.*;
import java.util.Scanner;

public class MySQLCrudApp {

private static final String DB_URL = "jdbc:mysql://localhost:3306/testdb";

private static final String DB_USER = "root";
private static final String DB_PASSWORD = "root"; // Replace with your actual MySQL
password

JDBC Programming
Page 17
 public static void main(String[] args) {
Connection conn = null;
try {
conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
createTable(conn);

Scanner scanner = new Scanner(System.in);

while (true) {
System.out.println("\n1. Create");
System.out.println("2. Read");
System.out.println("3. Update");
System.out.println("4. Delete");
System.out.println("5. Exit");
System.out.print("Enter your choice: ");
int choice = scanner.nextInt();
scanner.nextLine(); // consume newline

if (choice == 1) {
insertData(conn, scanner);
} else if (choice == 2) {
readData(conn);
} else if (choice == 3) {
updateData(conn, scanner);
} else if (choice == 4) {
deleteData(conn, scanner);
} else if (choice == 5) {
System.out.println("Exiting...");
break;
} else {
System.out.println("Invalid choice.");
}
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (conn != null) conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}

private static void createTable(Connection conn) throws SQLException {

String sql = "CREATE TABLE IF NOT EXISTS users (" +
JDBC Programming
Page 18
 "id INT AUTO_INCREMENT PRIMARY KEY," +
"name VARCHAR(100) NOT NULL," +
"email VARCHAR(100) NOT NULL UNIQUE)";
Statement stmt = conn.createStatement();
stmt.execute(sql);
stmt.close();
}

private static void insertData(Connection conn, Scanner scanner) throws SQLException {

System.out.print("Enter name: ");
String name = scanner.nextLine();
System.out.print("Enter email: ");
String email = scanner.nextLine();

String sql = "INSERT INTO users (name, email) VALUES (?, ?)";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, name);
pstmt.setString(2, email);
pstmt.executeUpdate();
pstmt.close();
System.out.println("User added.");
}

private static void readData(Connection conn) throws SQLException {

String sql = "SELECT * FROM users";
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);

System.out.println("\nUsers:");
while (rs.next()) {
System.out.printf("ID: %d | Name: %s | Email: %s%n",
rs.getInt("id"),
rs.getString("name"),
rs.getString("email"));
}

rs.close();
stmt.close();
}

private static void updateData(Connection conn, Scanner scanner) throws SQLException {

System.out.print("Enter ID of user to update: ");
int id = scanner.nextInt();
scanner.nextLine(); // consume newline

System.out.print("Enter new name: ");

JDBC Programming
Page 19
 String name = scanner.nextLine();
System.out.print("Enter new email: ");
String email = scanner.nextLine();

String sql = "UPDATE users SET name = ?, email = ? WHERE id = ?";

PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, name);
pstmt.setString(2, email);
pstmt.setInt(3, id);

int rows = pstmt.executeUpdate();

if (rows > 0) {
System.out.println("User updated.");
} else {
System.out.println("User not found.");
}
pstmt.close();
}

private static void deleteData(Connection conn, Scanner scanner) throws SQLException {

System.out.print("Enter ID of user to delete: ");
int id = scanner.nextInt();
scanner.nextLine(); // consume newline

String sql = "DELETE FROM users WHERE id = ?";

PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, id);

int rows = pstmt.executeUpdate();

if (rows > 0) {
System.out.println("User deleted.");
} else {
System.out.println("User not found.");
}
pstmt.close();
}
}

Using Rowsets Objects

• RowSet is an interface, which is like ResultSet, which resides
in javax.sql.rowset under java.sql.
• It inherits from ResultSet, and has more capabilities than a ResultSet.
• RowSets can be scrollable and updatable.
• Some DBMS do not support scrollability and updatability of ResultSets.
• In that case, RowSets have to be used. Also, RowSets have Java Beans functionality.
• They have properties. Properties can be set or retrieved using setter and getter methods.
JDBC Programming
Page 20
 • RowSet uses event model, where events are propagated to when certain events occur.
The events are of following three types:
i. Update, insert, or deletion of a row
ii. Cursor movement
iii. Change in RowSet contents
It has a methods like
setUrl("jdbc:mysql://localhost:3306/mydb");
Specifies the JDBC connection URL of the database that you want to connect to.
setUsername("root");
Sets the username used for authenticating the connection to the database.
setPassword("password");
Sets the password corresponding to the given username for authentication.
setCommand("SELECT * FROM students");
Sets the SQL query that the JdbcRowSet will execute.
execute();
Executes the SQL query provided by setCommand() using the connection details
provided.

Types of RowSet Interfaces

There are 5 major types
1. JdbcRowSet
2. CachedRowSet
3. WebRowSet
4. JoinRowSet
5. FilteredRowSet
JdbcRowSet
• It is a connected RowSet, meaning it maintains a constant connection with the database.
• Acts like a wrapper around a ResultSet object.

JdbcRowSet Example

import javax.sql.rowset.JdbcRowSet;
import com.sun.rowset.JdbcRowSetImpl;

public class JdbcRowSetExample {

public static void main(String[] args) throws Exception {
JdbcRowSet jrs = new JdbcRowSetImpl();
jrs.setUrl("jdbc:mysql://localhost:3306/mydb");
jrs.setUsername("root");
jrs.setPassword("password");

jrs.setCommand("SELECT * FROM students");

jrs.execute();

while (jrs.next()) {
System.out.println("ID: " + jrs.getInt("id") + ", Name: " + jrs.getString("name"));
JDBC Programming
Page 21
 }
jrs.close();
}
}
CachedRowSet
• Disconnected RowSet.
• Retrieves data from DB and stores it in memory; does not require a continuous
connection.

CachedRowSet Example

import javax.sql.rowset.CachedRowSet;
import com.sun.rowset.CachedRowSetImpl;

public class CachedRowSetExample {

public static void main(String[] args) throws Exception {
CachedRowSet crs = new CachedRowSetImpl();
crs.setUrl("jdbc:mysql://localhost:3306/mydb");
crs.setUsername("root");
crs.setPassword("password");

crs.setCommand("SELECT * FROM students");

crs.execute();

while (crs.next()) {
System.out.println("ID: " + crs.getInt("id") + ", Name: " + crs.getString("name"));
}

crs.close(); // No active DB connection needed after execution

}
}

WebRowSet
• A subclass of CachedRowSet.
• Can read/write data in XML format.
• Useful for web-based applications and data transfer.
WebRowSet Example
import javax.sql.rowset.WebRowSet;
import com.sun.rowset.WebRowSetImpl;
import java.io.FileWriter;

public class WebRowSetExample {

public static void main(String[] args) throws Exception {
WebRowSet wrs = new WebRowSetImpl();
wrs.setUrl("jdbc:mysql://localhost:3306/mydb");
wrs.setUsername("root");
JDBC Programming
Page 22
 wrs.setPassword("password");

wrs.setCommand("SELECT * FROM students");

wrs.execute();

FileWriter fw = new FileWriter("students.xml");

wrs.writeXml(fw); // writes to XML
fw.close();

wrs.close();
System.out.println("Data written to students.xml");
}
}

JoinRowSet
• Allows joining data from multiple RowSets.
• Helps perform SQL-like JOIN operations in Java code

JoinRowSet Example

import javax.sql.rowset.JoinRowSet;
import com.sun.rowset.JoinRowSetImpl;
import com.sun.rowset.CachedRowSetImpl;

public class JoinRowSetExample {

public static void main(String[] args) throws Exception {
CachedRowSetImpl students = new CachedRowSetImpl();
students.setUrl("jdbc:mysql://localhost:3306/mydb");
students.setUsername("root");
students.setPassword("password");
students.setCommand("SELECT id, name FROM students");
students.execute();

CachedRowSetImpl marks = new CachedRowSetImpl();

marks.setUrl("jdbc:mysql://localhost:3306/mydb");
marks.setUsername("root");
marks.setPassword("password");
marks.setCommand("SELECT student_id, marks FROM results");
marks.execute();

JoinRowSet jrs = new JoinRowSetImpl();

jrs.addRowSet(students, "id");
jrs.addRowSet(marks, "student_id");

while (jrs.next()) {

JDBC Programming
Page 23
 System.out.println("Name: " + jrs.getString("name") + ", Marks: " +
jrs.getInt("marks"));
}
}
}

FilteredRowSet
• Subtype of CachedRowSet.
• Allows filtering rows using custom logic without writing SQL WHERE clause.

FilteredRowSet Example

import javax.sql.rowset.FilteredRowSet;
import com.sun.rowset.FilteredRowSetImpl;
import com.sun.rowset.RowSetFilterImpl;

public class FilteredRowSetExample {

public static void main(String[] args) throws Exception {
FilteredRowSet frs = new FilteredRowSetImpl();
frs.setUrl("jdbc:mysql://localhost:3306/mydb");
frs.setUsername("root");
frs.setPassword("password");

frs.setCommand("SELECT * FROM students");

frs.execute();

RowSetFilterImpl filter = new RowSetFilterImpl(100, 200); // Accepts only ID between

100 and 200
frs.setFilter(filter);

while (frs.next()) {
System.out.println("ID: " + frs.getInt("id") + ", Name: " + frs.getString("name"));
}
}
}

RowSetFilterImpl is a custom class you need to define. Here's an example:

import javax.sql.rowset.Predicate;

public class RowSetFilterImpl implements Predicate {

private int low;
private int high;

public RowSetFilterImpl(int low, int high) {

this.low = low;
JDBC Programming
Page 24
 this.high = high;
}

public boolean evaluate(Object value, int column) {

if (value instanceof Integer) {
int val = (Integer) value;
return val >= low && val <= high;
}
return false;
}

public boolean evaluate(Object value, String columnName) {

return true; // not used
}

public boolean evaluate(Object value) {

return true; // not used
}
}

Managing Database Transaction

 Database transactions allow you to group a set of operations so that they either all
succeed (commit) or all fail (rollback) together.
 This is crucial for maintaining data integrity.
A transaction is a sequence of operations performed as a single logical unit of work. A
transaction must follow the ACID properties:
1. Atomicity: All operations in a transaction happen, or none do.
2. Consistency: The database moves from one consistent state to another.
3. Isolation: Transactions don‟t interfere with each other.
4. Durability: Once committed, the changes are permanent.

Auto-commit Mode in JDBC

If JDBC Connection is in auto-commit mode, which it is by default, then every SQL
statement is committed to the database upon its completion.
Syntax
Connection conn = DriverManager.getConnection(url, user, password);
System.out.println(conn.getAutoCommit()); // true by default

Turning Off Auto-commit

There are three reasons why we may want to turn off the auto-commit and manage your own
transactions −
1. To increase performance.
2. To maintain the integrity of business processes.
3. To use distributed transactions.

JDBC Programming
Page 25
 • Transactions enable us to control if, and when, changes are applied to the database. It
treats a single SQL statement or a group of SQL statements as one logical unit, and if any
statement fails, the whole transaction fails.
• To enable manual- transaction support instead of the auto-commit mode that the JDBC
driver uses by default, use the Connection object's setAutoCommit() method.
• If we pass a boolean false to setAutoCommit( ), we turn off auto-commit. we can pass a
boolean true to turn it back on again.
Syntax:
conn.setAutoCommit(false);

Commit & Rollback

• Once our changes are done and want to commit the changes then call commit() method
on connection object as - conn.commit( );
• Otherwise, to roll back updates to the database made using the Connection named conn,
use the following code − conn.rollback();
Steps for Manual Transaction Management
1. Disable auto-commit
2. Execute SQL statements
3. Commit or rollback the transaction

Example for manual transaction using commit() and rollback()

Connection conn = null;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class ManualTransactionExample {

public static void main(String[] args) {
Connection conn = null;

try {
// Load MySQL JDBC Driver
Class.forName("com.mysql.cj.jdbc.Driver");

// Step 1: Establish a connection

conn = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/mydb", "root", "password");

// Step 2: Disable auto-commit mode

conn.setAutoCommit(false);

// Step 3: Create and execute SQL statements

Statement stmt = conn.createStatement();

JDBC Programming
Page 26
 // Inserting a new account
stmt.executeUpdate("INSERT INTO accounts VALUES (101, 'Alice', 1000)");

// Updating balance
stmt.executeUpdate("UPDATE accounts SET balance = balance - 500 WHERE id =
101");

// Step 4: Commit the transaction

conn.commit();
System.out.println("Transaction committed successfully!");

} catch (Exception e) {
// Step 5: Rollback in case of error
if (conn != null) {
try {
conn.rollback();
System.out.println("Transaction rolled back due to error!");
} catch (SQLException se) {
System.out.println("Error during rollback: " + se.getMessage());
}
}
System.out.println("Error: " + e.getMessage());
} finally {
// Step 6: Clean up resources
if (conn != null) {
try {
conn.setAutoCommit(true); // Restore auto-commit
conn.close();
} catch (SQLException ignore) {
// Ignored
}
}
}
}
}
Using saveponits
• When we set a savepoint we define a logical rollback point within a transaction.
• If an error occurs past a savepoint, we can use the rollback method to undo either all the
changes or only the changes made after the savepoint.
The Connection object has two new methods that help to manage savepoints −
1. setSavepoint(String savepointName) − Defines a new savepoint. It also returns a
Savepoint object.
2. releaseSavepoint(Savepoint savepointName) − Deletes a savepoint. Notice that it
requires a Savepoint object as a parameter. This object is usually a savepoint generated
by the setSavepoint() method.
Example
JDBC Programming
Page 27
 import java.sql.*;

public class JDBCTransactionSavepointExample {

public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;

try {
// Load and register JDBC driver (optional for newer versions)
Class.forName("com.mysql.cj.jdbc.Driver");

// Establish the connection

conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", "root",
"password");

// Disable auto-commit mode

conn.setAutoCommit(false);

// Create statement object

stmt = conn.createStatement();

// Set a savepoint before executing risky SQL

Savepoint savepoint1 = conn.setSavepoint("Savepoint1");

// First valid insert

String sql1 = "INSERT INTO Employees VALUES (106, 20, 'Rita', 'Tez')";
stmt.executeUpdate(sql1);

// Malformed SQL to cause exception

String sql2 = "INSERTED IN Employees VALUES (107, 22, 'Sita', 'Tez')"; //
Incorrect SQL syntax
stmt.executeUpdate(sql2);

// Commit only if all statements are successful

conn.commit();
System.out.println("Transaction committed successfully.");

} catch (SQLException se) {

try {
// Rollback to savepoint on error
if (conn != null) {
System.out.println("Exception occurred. Rolling back to Savepoint1...");
conn.rollback(); // or conn.rollback(savepoint1); for partial rollback
}
} catch (SQLException e) {
System.out.println("Error while rolling back transaction: " + e.getMessage());
JDBC Programming
Page 28
 }
System.out.println("SQLException: " + se.getMessage());
} catch (Exception e) {
System.out.println("Exception: " + e.getMessage());
} finally {
try {
if (stmt != null)
stmt.close();
if (conn != null) {
conn.setAutoCommit(true); // Restore default auto-commit mode
conn.close();
}
} catch (SQLException e) {
System.out.println("Error closing resources: " + e.getMessage());
}
}
}
}
QUIZ

1. What is the main purpose of JDBC architecture?

a) To manage database connections

b) To provide a way to interact with relational databases
c) To design user interfaces
d) To handle error messages in Java applications

Answer: b) To provide a way to interact with relational databases

2. Which JDBC driver type requires native DB API to communicate with the database?

a) Type 1
b) Type 2
c) Type 3
d) Type 4

Answer: b) Type 2

3. Which of the following is a valid JDBC driver type for Java applications?

a) Type 1
b) Type 3
c) Both a and b
d) Type 5

Answer: c) Both a and b

JDBC Programming
Page 29
4. Which JDBC interface is used to execute SQL queries and update statements?

a) ResultSet
b) Statement
c) Driver
d) Connection

Answer: b) Statement

5. Which JDBC class is used to represent the result set of an SQL query?

a) Statement
b) ResultSet
c) CallableStatement
d) Connection

Answer: b) ResultSet

6. Which of the following methods does not belong to the Statement interface?

a) executeQuery()
b) executeUpdate()
c) prepareStatement()
d) execute()

Answer: c) prepareStatement()

7. What is the use of the PreparedStatement interface in JDBC?

a) To prepare SQL statements dynamically at runtime

b) To execute only SELECT queries
c) To manage database connections
d) To create stored procedures

Answer: a) To prepare SQL statements dynamically at runtime

8. Which of the following is NOT a valid type of statement in JDBC?

a) Statement
b) PreparedStatement
c) CallableStatement
d) DataStatement

Answer: d) DataStatement

JDBC Programming
Page 30
9. Which JDBC method is used to update the database using a batch of SQL commands?

a) addBatch()
b) executeBatch()
c) executeUpdate()
d) executeQuery()

Answer: b) executeBatch()

10. What is the default fetch direction for a ResultSet object?

a) Forward
b) Backward
c) Both
d) None

Answer: a) Forward

11. What does a ResultSet object contain?

a) Only updateable results

b) Only query results
c) Data returned from executing an SQL query
d) Database connection information

Answer: c) Data returned from executing an SQL query

12. Which method is used to execute a stored procedure in JDBC?

a) executeQuery()
b) executeUpdate()
c) execute()
d) executeCallable()

Answer: c) execute()

13. What is a Batch Update in JDBC?

a) Executing multiple SQL statements in a single operation

b) Adding a new record to the database
c) Fetching data from a database
d) Updating a single row of data

Answer: a) Executing multiple SQL statements in a single operation

JDBC Programming
Page 31
14. In a CRUD application, what does "C" stand for?

a) Create
b) Change
c) Copy
d) Classify

Answer: a) Create

15. Which of the following classes/interfaces is used to manage database transactions in

JDBC?

a) Statement
b) Connection
c) DriverManager
d) ResultSet

Answer: b) Connection

16. Which JDBC interface is used to call a stored procedure?

a) CallableStatement
b) Statement
c) PreparedStatement
d) ResultSet

Answer: a) CallableStatement

17. Which of the following is an example of a type of RowSet in JDBC?

a) WebRowSet
b) SqlRowSet
c) DataRowSet
d) CacheRowSet

Answer: a) WebRowSet

18. What does the executeUpdate() method return?

a) A ResultSet
b) The number of rows affected
c) The SQL query executed
d) A boolean value

Answer: b) The number of rows affected

JDBC Programming
Page 32
19. Which method in the Connection interface is used to start a transaction in JDBC?

a) setAutoCommit(false)
b) commit()
c) rollback()
d) setTransactionIsolation()

Answer: a) setAutoCommit(false)

20. Which of the following statements about the JdbcRowSet is true?

a) It is a disconnected RowSet.
b) It always works in connected mode.
c) It requires a database connection to fetch data.
d) It is not used for data manipulation.

Answer: b) It always works in connected mode..

JDBC Programming
Page 33