JDBC

What is JDBC?

JDBC stands for Java Database Connectivity.

It is a Java API used to:

• connect Java applications to databases,
• execute SQL queries,
• retrieve data,
• update/delete/insert records.

JDBC allows Java programs to communicate with databases such as:

• MySQL
• Oracle
• PostgreSQL
• Microsoft SQL Server

JDBC Architecture

Flow:

Java Application
       ↓
JDBC API
       ↓
JDBC Driver
       ↓
Database

The JDBC Driver acts like a translator between Java and the database.

JDBC Driver

A JDBC driver is required to connect Java to a database.

Example:

For MySQL:

<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>9.0.0</version>
</dependency>

Important JDBC Classes & Interfaces

Component	Purpose
DriverManager	Creates database connection
Connection	Represents database connection
Statement	Executes SQL
PreparedStatement	Executes parameterized SQL
ResultSet	Stores query result
SQLException	Handles database errors

JDBC Basic Steps

Step 1 — Import Package

import java.sql.*;

Step 2 — Create Connection

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

Step 3 — Create Statement

Statement stmt = conn.createStatement();

Step 4 — Execute SQL

SELECT

ResultSet rs = stmt.executeQuery(
    "SELECT * FROM students"
);

INSERT / UPDATE / DELETE

int rows = stmt.executeUpdate(
    "INSERT INTO students VALUES (1,'Ali')"
);

Step 5 — Read Result

while(rs.next()) {
    System.out.println(rs.getInt("id"));
    System.out.println(rs.getString("name"));
}

Step 6 — Close Resources

rs.close();
stmt.close();
conn.close();

Statement vs PreparedStatement

Statement

Statement stmt = conn.createStatement();

Problem:

• vulnerable to SQL Injection
• slower for repeated queries

PreparedStatement (IMPORTANT)

PreparedStatement ps =
conn.prepareStatement(
    "SELECT * FROM students WHERE id=?"
);

ps.setInt(1, 1);
ResultSet rs = ps.executeQuery();

Advantages:

• More secure
• Faster
• Prevents SQL Injection
• Easier parameter handling

Transactions

Used when multiple SQL operations must succeed together.

conn.setAutoCommit(false);

conn.commit();
conn.rollback();

Example:

• banking system
• payment transfer
• inventory system

Batch Processing

Useful for large data insertion.

ps.addBatch();
ps.executeBatch();

Good for:

• importing Excel data
• bulk insert
• processing thousands of records

JDBC with MVC Architecture

Common in:

• Servlet/JSP
• Java EE
• Spring Boot

Architecture:

Servlet → DAO → JDBC → Database

Common Databases with JDBC URLs

1. MySQL

jdbc:mysql://localhost:3306/dbname

2. PostgreSQL

jdbc:postgresql://localhost:5432/dbname

3. Oracle

jdbc:oracle:thin:@localhost:1521/XEPDB1

4. SQL Server

jdbc:sqlserver://localhost:1433;databaseName=testdb