Java JDBC Database Connectivity
JDBC is the standard Java API used by application code and database drivers to exchange SQL operations and results. The driver translates those calls for a particular database. A useful JDBC program also defines parameters, resource lifetime, and transaction boundaries.
Using JDBC, a Java application can:
- Establish a connection with a database
- Execute SQL queries
- Insert data
- Retrieve data
- Update data
- Delete data
- Manage database transactions
- Process query results
JDBC provides a standard way for Java applications to communicate with different relational databases.
What is JDBC?
JDBC stands for Java Database Connectivity.
It is an API provided by Java that allows applications to communicate with databases using SQL.
For example, a Java application can use JDBC to communicate with databases such as:
- MySQL
- PostgreSQL
- Oracle Database
- Microsoft SQL Server
- SQLite and other JDBC-supported databases
The application sends SQL statements through JDBC, and the database returns the result.
Why Do We Need JDBC?
Suppose we have a Java application that stores student information.
Java Application
|
↓
JDBC
|
↓
DatabaseThe Java application can use JDBC to perform operations such as:
SELECT * FROM student;or:
INSERT INTO student(name, age) VALUES ('Rahul', 20);JDBC acts as the standard Java API through which the application communicates with the database.
JDBC Architecture
A simplified JDBC architecture looks like this:
+----------------------+
| Java Application |
+----------------------+
|
↓
+----------------------+
| JDBC API |
+----------------------+
|
↓
+----------------------+
| JDBC Driver |
+----------------------+
|
↓
+----------------------+
| Database |
+----------------------+Java Application
Contains the business logic and database operations.
JDBC API
Provides Java interfaces and classes for database connectivity.
JDBC Driver
Acts as the communication layer between JDBC and a particular database.
Database
Stores and manages the application data.
Important JDBC Components
Some important JDBC classes and interfaces are:
| Component | Purpose |
|---|---|
DriverManager | Creates database connections |
Connection | Represents a connection with the database |
Statement | Executes SQL statements |
PreparedStatement | Executes parameterized SQL statements |
CallableStatement | Calls stored procedures |
ResultSet | Stores results returned by a query |
SQLException | Represents database-related errors |
These classes and interfaces are mainly available in the java.sql package.
JDBC Steps
A typical JDBC operation follows these steps:
1. Add JDBC Driver
↓
2. Establish Connection
↓
3. Create Statement
↓
4. Execute SQL
↓
5. Process Result
↓
6. Close ResourcesLet's understand each step.
Step 1: Add the JDBC Driver
A Java application needs a JDBC driver for the database being used.
For example, if we are using MySQL, the application needs the MySQL Connector/J driver.
With Maven, the driver is normally added as a dependency in pom.xml.
The exact driver version should match the project's requirements.
Step 2: Establish a Database Connection
The DriverManager class can be used to create a connection.
Example:
Connection connection = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/studentdb",
"root",
"password"
);Here:
jdbc:mysql://localhost:3306/studentdbis the JDBC URL.
Breaking it down:
jdbc → JDBC protocol
mysql → Database type
localhost → Database server
3306 → MySQL port
studentdb → Database nameStep 3: Create a Statement
After establishing a connection, we can create a statement.
Statement statement = connection.createStatement();The Statement object can then execute SQL statements.
Step 4: Execute SQL
For a SELECT query:
ResultSet resultSet =
statement.executeQuery("SELECT * FROM student");For INSERT, UPDATE, or DELETE:
int rows = statement.executeUpdate(sql);Step 5: Process the Result
A SELECT query returns a ResultSet.
Example:
while (resultSet.next()) {
System.out.println(resultSet.getInt("id"));
System.out.println(resultSet.getString("name"));
}resultSet.next() moves the cursor to the next row.
Step 6: Close Resources
After completing the database operation, resources should be closed.
resultSet.close();
statement.close();
connection.close();However, modern Java code generally uses try-with-resources so that resources are closed automatically.
Complete JDBC SELECT Example
The examples require a running database, its matching JDBC driver, and the tables and rows used by the SQL. Output is illustrative and depends on that data. If row order matters, add an ORDER BY clause; a SELECT without it does not guarantee row order. Use a test database for examples that insert, update, or delete rows.
Suppose we have a database:
studentdband a table:
CREATE TABLE student (
id INT PRIMARY KEY,
name VARCHAR(100),
age INT
);Example data:
1 | Rahul | 20
2 | Priya | 21
3 | Amit | 22Java program:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class HelloWorld {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/studentdb";
String username = "root";
String password = "password";
String sql = "SELECT id, name, age FROM student";
try (
Connection connection =
DriverManager.getConnection(url, username, password);
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(sql)
) {
while (resultSet.next()) {
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
int age = resultSet.getInt("age");
System.out.println(
id + " " + name + " " + age
);
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}Output
1 Rahul 20
2 Priya 21
3 Amit 22What is ResultSet?
ResultSet represents the data returned by a database query.
For example:
SELECT id, name, age FROM student;might return:
id name age
------------------
1 Rahul 20
2 Priya 21
3 Amit 22We can read each row using:
while (resultSet.next()) {
...
}ResultSet Cursor
When a ResultSet is created, its cursor initially points before the first row.
Cursor
↓
-------------------------
1 | Rahul | 20
2 | Priya | 21
3 | Amit | 22
-------------------------Calling:
resultSet.next();moves the cursor to the next row.
1 | Rahul | 20 ← Cursor
2 | Priya | 21
3 | Amit | 22Calling it again moves to the next row.
Reading Different Data Types
ResultSet provides methods for retrieving different types of data.
resultSet.getInt("id");
resultSet.getString("name");
resultSet.getDouble("salary");
resultSet.getBoolean("active");
resultSet.getDate("joining_date");We can also retrieve values using column indexes.
resultSet.getInt(1);
resultSet.getString(2);Column indexes start from 1, not 0.
Statement
Statement is used to execute SQL statements that do not require parameters.
Example:
Statement statement = connection.createStatement();
ResultSet resultSet =
statement.executeQuery("SELECT * FROM student");Although Statement is useful, it should not normally be used to construct SQL by directly concatenating user input.
PreparedStatement
PreparedStatement is used to execute parameterized SQL statements.
Example:
String sql =
"SELECT * FROM student WHERE age = ?";
PreparedStatement statement =
connection.prepareStatement(sql);
statement.setInt(1, 20);
ResultSet resultSet =
statement.executeQuery();The ? represents a parameter.
age = ?
↑
ParameterThe value is supplied using:
statement.setInt(1, 20);Why Use PreparedStatement?
PreparedStatement provides several advantages:
- Supports parameters
- Helps prevent SQL injection
- Makes dynamic queries easier to write
- Can improve efficiency when the same SQL is executed repeatedly
- Handles parameter values more safely than string concatenation
SQL Injection
Consider this code:
String sql =
"SELECT * FROM student WHERE name = '" + name + "'";Building SQL this way with external input can create SQL injection vulnerabilities.
Instead, use:
String sql =
"SELECT * FROM student WHERE name = ?";
PreparedStatement statement =
connection.prepareStatement(sql);
statement.setString(1, name);The database receives the SQL structure and parameter value separately.
JDBC INSERT Operation
Let's insert a student.
String sql =
"INSERT INTO student(id, name, age) VALUES (?, ?, ?)";
try (PreparedStatement statement =
connection.prepareStatement(sql)) {
statement.setInt(1, 4);
statement.setString(2, "Ravi");
statement.setInt(3, 23);
int rows = statement.executeUpdate();
System.out.println(rows + " row inserted");
}Output
1 row insertedJDBC UPDATE Operation
Suppose we want to change a student's age.
String sql =
"UPDATE student SET age = ? WHERE id = ?";
try (PreparedStatement statement =
connection.prepareStatement(sql)) {
statement.setInt(1, 24);
statement.setInt(2, 4);
int rows = statement.executeUpdate();
System.out.println(rows + " row updated");
}Output
1 row updatedJDBC DELETE Operation
We can delete a student using:
String sql =
"DELETE FROM student WHERE id = ?";
try (PreparedStatement statement =
connection.prepareStatement(sql)) {
statement.setInt(1, 4);
int rows = statement.executeUpdate();
System.out.println(rows + " row deleted");
}Output
1 row deletedJDBC CRUD Operations
CRUD stands for:
C → Create
R → Read
U → Update
D → DeleteIn JDBC:
| Operation | SQL | JDBC Method |
|---|---|---|
| Create | INSERT | executeUpdate() |
| Read | SELECT | executeQuery() |
| Update | UPDATE | executeUpdate() |
| Delete | DELETE | executeUpdate() |
executeQuery()
executeQuery() is generally used for SQL statements that return a result set.
Example:
ResultSet resultSet =
statement.executeQuery("SELECT * FROM student");It returns:
ResultSetexecuteUpdate()
executeUpdate() is generally used for:
INSERT
UPDATE
DELETEExample:
int rows =
statement.executeUpdate(
"UPDATE student SET age = 25 WHERE id = 1"
);It returns the number of affected rows.
execute()
execute() can be used when the SQL statement may produce different kinds of results.
Example:
boolean result = statement.execute(sql);It returns:
true → ResultSet is available
false → update count or no ResultSetFor normal CRUD operations, executeQuery() and executeUpdate() are generally clearer.
Transactions in JDBC
A transaction is a group of database operations treated as one logical unit.
For example, transferring money between two accounts might involve:
Account A → subtract money
Account B → add moneyBoth operations should succeed together.
If one operation fails, the transaction can be rolled back.
commit() and rollback()
By default, a JDBC connection commonly operates with auto-commit enabled.
We can disable auto-commit:
connection.setAutoCommit(false);Then perform operations:
// Operation 1
// Operation 2If everything succeeds:
connection.commit();If something goes wrong:
connection.rollback();Transaction Example
This teaching example assumes a dedicated connection that originally has auto-commit enabled. In reusable code, preserve the original connection settings and handle rollback failures without hiding the first error. Check both update counts and business conditions before committing a transfer; an update affecting zero rows does not necessarily throw an exception. Use a suitable decimal representation for money rather than treating this double-based example as production payment code.
try {
connection.setAutoCommit(false);
String sql1 =
"UPDATE account SET balance = balance - ? WHERE id = ?";
String sql2 =
"UPDATE account SET balance = balance + ? WHERE id = ?";
try (
PreparedStatement statement1 =
connection.prepareStatement(sql1);
PreparedStatement statement2 =
connection.prepareStatement(sql2)
) {
statement1.setDouble(1, 1000);
statement1.setInt(2, 1);
statement1.executeUpdate();
statement2.setDouble(1, 1000);
statement2.setInt(2, 2);
statement2.executeUpdate();
connection.commit();
} catch (SQLException e) {
connection.rollback();
throw e;
}
} finally {
connection.setAutoCommit(true);
}The important idea is:
Start Transaction
↓
Operation 1
↓
Operation 2
↓
Everything OK?
/ \
Yes No
↓ ↓
Commit RollbackBatch Processing
JDBC can execute multiple SQL statements as a batch.
For example:
String sql =
"INSERT INTO student(id, name, age) VALUES (?, ?, ?)";
try (PreparedStatement statement =
connection.prepareStatement(sql)) {
statement.setInt(1, 10);
statement.setString(2, "Amit");
statement.setInt(3, 20);
statement.addBatch();
statement.setInt(1, 11);
statement.setString(2, "Neha");
statement.setInt(3, 21);
statement.addBatch();
int[] results = statement.executeBatch();
}Batch processing can be useful when many similar database operations need to be performed.
CallableStatement
CallableStatement is used to call stored procedures.
For example, suppose the database contains a stored procedure:
get_student_by_idJava can call it using:
CallableStatement statement =
connection.prepareCall(
"{call get_student_by_id(?)}"
);
statement.setInt(1, 1);
ResultSet resultSet =
statement.executeQuery();SQLException
Database operations can fail for many reasons.
For example:
- Invalid SQL
- Database unavailable
- Incorrect credentials
- Connection failure
- Constraint violation
- Invalid table name
- Invalid column name
JDBC represents many database-related problems using:
SQLExceptionExample:
try {
// JDBC operations
} catch (SQLException e) {
System.out.println(
"Database error: " + e.getMessage()
);
}Try-With-Resources
Try-with-resources is highly useful in JDBC.
Instead of manually closing:
resultSet.close();
statement.close();
connection.close();we can write:
try (
Connection connection = ...;
PreparedStatement statement = ...;
ResultSet resultSet = ...
) {
// JDBC operations
}Java automatically closes these resources when the block finishes.
This helps prevent resource leaks.
Complete JDBC CRUD Example
The following example demonstrates the basic CRUD operations using PreparedStatement.
import java.sql.*;
public class HelloWorld {
private static final String URL =
"jdbc:mysql://localhost:3306/studentdb";
private static final String USER =
"root";
private static final String PASSWORD =
"password";
public static void main(String[] args) {
try (Connection connection =
DriverManager.getConnection(
URL, USER, PASSWORD)) {
// INSERT
String insertSql =
"INSERT INTO student(id, name, age) " +
"VALUES (?, ?, ?)";
try (PreparedStatement statement =
connection.prepareStatement(insertSql)) {
statement.setInt(1, 1);
statement.setString(2, "Rahul");
statement.setInt(3, 20);
statement.executeUpdate();
}
// SELECT
String selectSql =
"SELECT id, name, age FROM student";
try (PreparedStatement statement =
connection.prepareStatement(selectSql);
ResultSet resultSet =
statement.executeQuery()) {
while (resultSet.next()) {
System.out.println(
resultSet.getInt("id") + " " +
resultSet.getString("name") + " " +
resultSet.getInt("age")
);
}
}
// UPDATE
String updateSql =
"UPDATE student SET age = ? WHERE id = ?";
try (PreparedStatement statement =
connection.prepareStatement(updateSql)) {
statement.setInt(1, 21);
statement.setInt(2, 1);
statement.executeUpdate();
}
// DELETE
String deleteSql =
"DELETE FROM student WHERE id = ?";
try (PreparedStatement statement =
connection.prepareStatement(deleteSql)) {
statement.setInt(1, 1);
statement.executeUpdate();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}More Complete JDBC Examples
These examples use MySQL and a separate practice table. Each program has its own HelloWorld class and main method. Run one program at a time.
Set Up the Practice Table
Start MySQL and add MySQL Connector/J to your project's dependencies or runtime classpath. Run this SQL in your MySQL client once before trying the programs:
CREATE DATABASE IF NOT EXISTS studentdb;
USE studentdb;
CREATE TABLE jdbc_practice_student (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
age INT NULL
);
INSERT INTO jdbc_practice_student (name, age)
VALUES ('Meera', 20), ('Kabir', 23), ('Tara', NULL);The examples connect to studentdb on port 3306. Replace root and password in each program with your local database username and password. The account needs permission to read and insert rows in this practice table. A fresh table contains IDs 1, 2, and 3.
Example 1: Find Students by Minimum Age
This program finds students who are at least 21 years old. We pass the age through a PreparedStatement parameter. If no rows match, the program prints a clear message.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class HelloWorld {
public static void main(String[] args) throws SQLException {
String url = "jdbc:mysql://localhost:3306/studentdb";
String sql = "SELECT id, name, age FROM jdbc_practice_student "
+ "WHERE age >= ? ORDER BY id";
try (Connection connection =
DriverManager.getConnection(url, "root", "password");
PreparedStatement statement = connection.prepareStatement(sql)) {
statement.setInt(1, 21);
try (ResultSet result = statement.executeQuery()) {
boolean found = false;
while (result.next()) {
found = true;
System.out.println(result.getInt("id") + " "
+ result.getString("name") + " "
+ result.getInt("age"));
}
if (!found) {
System.out.println("No students found");
}
}
}
}
}Output with the setup data:
2 Kabir 23Change 21 to 25 and run it again. With only the setup data, the result is No students found.
Example 2: Insert a Student and Read the Generated ID
Here the database creates the ID. We request generated keys when preparing the statement, then read the new ID after the insert succeeds.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class HelloWorld {
public static void main(String[] args) throws SQLException {
String url = "jdbc:mysql://localhost:3306/studentdb";
String sql = "INSERT INTO jdbc_practice_student (name, age) VALUES (?, ?)";
try (Connection connection =
DriverManager.getConnection(url, "root", "password");
PreparedStatement statement = connection.prepareStatement(
sql, Statement.RETURN_GENERATED_KEYS)) {
statement.setString(1, "Nila");
statement.setInt(2, 19);
if (statement.executeUpdate() != 1) {
throw new SQLException("Expected one student to be inserted");
}
try (ResultSet keys = statement.getGeneratedKeys()) {
if (!keys.next()) {
throw new SQLException("The driver did not return a student ID");
}
System.out.println("Student added with ID: " + keys.getInt(1));
}
}
}
}Example output after the initial setup:
Student added with ID: 4The ID can differ if rows have already been inserted. Every run adds another student. Auto-generated IDs can have gaps, so do not use them as a count of rows.
MySQL documents this approach in its generated keys guide.
Example 3: Read an Age That May Be NULL
Tara's age is not recorded. SQL NULL means no value is stored; it does not mean the age is zero. After calling getInt(), we immediately call wasNull() to check whether the age was missing.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class HelloWorld {
public static void main(String[] args) throws SQLException {
String url = "jdbc:mysql://localhost:3306/studentdb";
String sql = "SELECT name, age FROM jdbc_practice_student "
+ "WHERE age IS NULL ORDER BY id";
try (Connection connection =
DriverManager.getConnection(url, "root", "password");
PreparedStatement statement = connection.prepareStatement(sql);
ResultSet result = statement.executeQuery()) {
while (result.next()) {
String name = result.getString("name");
int age = result.getInt("age");
boolean ageMissing = result.wasNull();
if (ageMissing) {
System.out.println(name + ": age not provided");
} else {
System.out.println(name + ": " + age);
}
}
}
}
}Output with the setup data:
Tara: age not providedUse IS NULL in SQL to find missing values. A condition such as age = NULL does not perform the same check. The JDBC ResultSet documentation explains that wasNull() checks the last column value read.
JDBC Driver
A JDBC driver is a software component that allows JDBC to communicate with a specific database.
For example:
Java Application
↓
JDBC API
↓
MySQL JDBC Driver
↓
MySQLDifferent databases can use different JDBC drivers.
The application code can still use the standard JDBC API.
JDBC Driver Types
Historically, JDBC drivers were categorized into four types:
Type 1 — JDBC-ODBC Bridge
Used ODBC to communicate with databases.
This approach is obsolete and is no longer used in modern Java applications.
Type 2 — Native-API Driver
Uses database-specific native libraries.
Type 3 — Network Protocol Driver
Uses middleware to communicate with the database.
Type 4 — Thin Driver
Communicates directly with the database using its network protocol.
Type 4 drivers are commonly used in modern Java applications.Modern JDBC Driver Loading
Older Java code sometimes explicitly loaded a driver:
Class.forName("com.mysql.cj.jdbc.Driver");Modern JDBC drivers generally support automatic driver discovery through the Java service-provider mechanism.
Therefore, explicit Class.forName() is usually unnecessary when the JDBC driver is correctly included in the application.
JDBC Connection Pooling
Creating a database connection can be relatively expensive.
In a larger application, opening and closing a new connection for every operation may not be efficient.
A connection pool maintains a collection of reusable database connections.
Instead of:
Request
↓
Create Connection
↓
Use Connection
↓
Close Connectiona pool works approximately like:
Connection Pool
┌─────┬─────┬─────┬─────┐
│ C1 │ C2 │ C3 │ C4 │
└─────┴─────┴─────┴─────┘
↑
ApplicationThe application borrows a connection and returns it to the pool when finished.
Connection pooling is especially important in server-side applications.
JDBC and Database Independence
JDBC provides a standard API.
For example:
Connection connection;
PreparedStatement statement;
ResultSet resultSet;The application uses these standard interfaces rather than directly implementing the database communication protocol.
The JDBC driver handles database-specific communication.
JDBC vs Hibernate
JDBC and Hibernate are not the same thing.
| JDBC | Hibernate |
|---|---|
| Low-level database API | ORM framework |
| Uses SQL directly | Maps Java objects to database tables |
| More control over SQL | Reduces repetitive SQL |
| Part of Java's standard database APIs | External framework |
| Developer manages JDBC operations | Framework manages many persistence details |
Hibernate itself ultimately relies on database connectivity mechanisms underneath, commonly through JDBC.
JDBC in a Typical Application
A simple application may look like:
Controller
↓
Service
↓
Repository / DAO
↓
JDBC
↓
JDBC Driver
↓
DatabaseA DAO (Data Access Object) is a common design approach for keeping database-access code separate from business logic.
Common JDBC Mistakes
1. Concatenating User Input into SQL
Avoid:
String sql =
"SELECT * FROM student WHERE name = '" + name + "'";Prefer:
String sql =
"SELECT * FROM student WHERE name = ?";
PreparedStatement statement =
connection.prepareStatement(sql);
statement.setString(1, name);2. Not Closing Resources
Leaving connections or statements open can cause resource leaks.
Prefer try-with-resources.
3. Using getInt(0)
This is incorrect:
resultSet.getInt(0);JDBC column indexes start from 1.
Correct:
resultSet.getInt(1);4. Forgetting next()
This is incorrect:
ResultSet resultSet = statement.executeQuery();
System.out.println(
resultSet.getString("name")
);Move the cursor first:
if (resultSet.next()) {
System.out.println(
resultSet.getString("name")
);
}5. Using executeQuery() for INSERT
For an insert operation, use:
executeUpdate();rather than:
executeQuery();6. Hardcoding Database Credentials
Avoid putting production database passwords directly into source code.
For example:
String password = "myProductionPassword";Production applications should use appropriate configuration and secret-management mechanisms.
Important JDBC Methods
DriverManager
DriverManager.getConnection(...)Used to obtain a database connection.
Connection
connection.createStatement();
connection.prepareStatement(...);
connection.commit();
connection.rollback();
connection.close();PreparedStatement
statement.setInt(...);
statement.setString(...);
statement.executeQuery();
statement.executeUpdate();ResultSet
resultSet.next();
resultSet.getInt(...);
resultSet.getString(...);
resultSet.getDouble(...);JDBC Quick Flow
Remember JDBC using this sequence:
Connect
↓
Create Statement
↓
Execute SQL
↓
Process Result
↓
Close ResourcesFor parameterized SQL:
Connect
↓
PreparedStatement
↓
Set Parameters
↓
Execute SQL
↓
Process Result
↓
Close ResourcesKey Points
- JDBC stands for Java Database Connectivity.
- JDBC allows Java applications to communicate with relational databases.
- JDBC APIs are primarily provided through packages such as
java.sql. DriverManagercan create database connections.Connectionrepresents a database connection.Statementexecutes SQL statements.PreparedStatementexecutes parameterized SQL and is preferred when parameters are involved.ResultSetrepresents the result returned by a query.executeQuery()is generally used forSELECT.executeUpdate()is generally used forINSERT,UPDATE, andDELETE.commit()permanently applies a transaction.rollback()reverses uncommitted transaction changes.- Try-with-resources helps automatically close JDBC resources.
- JDBC drivers provide database-specific communication.
- Connection pooling is useful for applications that handle many database requests.
Conclusion
JDBC provides the standard Java API for communicating with relational databases.
The basic JDBC workflow is:
Java Application
↓
JDBC
↓
JDBC Driver
↓
DatabaseA typical database operation follows:
Connection
↓
PreparedStatement
↓
Set Parameters
↓
Execute SQL
↓
Process ResultSet
↓
Close ResourcesUnderstanding JDBC is important because it provides the foundation for database access in many Java applications and frameworks.
See you in next page....🙂