JDBC

Introduction

JDBC (Java Database Connectivity) is the standard API for accessing relational databases from Java. JDBC defines a set of interfaces in the java.sql package — Connection, Statement, ResultSet, and others — and each database vendor provides a driver class that implements these interfaces.

Types of JDBC Drivers

1. Type 1 — JDBC–ODBC Bridge: translates JDBC calls to ODBC; requires ODBC driver; deprecated.
2. Type 2 — Native API: translates JDBC to database-vendor native calls via native libraries; platform-dependent.
3. Type 3 — Network Protocol: pure Java client talks to a middleware server that talks to the database.
4. Type 4 — Thin Driver: pure Java driver that speaks the database's wire protocol directly. Most modern drivers (MySQL Connector/J, PostgreSQL JDBC, Oracle Thin) are Type 4.

JDBC Workflow

The standard JDBC workflow is: (1) load the driver, (2) get a Connection, (3) create a Statement, (4) execute a query, (5) process the ResultSet, (6) close all resources.

import java.sql.*;
public class JdbcDemo {
    public static void main(String[] args) throws Exception {
        String url = "jdbc:mysql://localhost:3306/sample";
        try (Connection con = DriverManager.getConnection(url, "user", "pass");
             Statement st = con.createStatement();
             ResultSet rs = st.executeQuery("SELECT id, name FROM student")) {
            while (rs.next()) {
                System.out.println(rs.getInt("id") + " " + rs.getString("name"));
            }
        }
    }
}

Statement Types

• Statement — for simple SQL without parameters. Vulnerable to SQL injection if you concatenate user input.
• PreparedStatement — precompiled SQL with ? placeholders. Safer and faster for repeated execution.
• CallableStatement — calls stored procedures.

PreparedStatement ps = con.prepareStatement(
    "INSERT INTO student(name, age) VALUES(?, ?)");
ps.setString(1, "Amit");
ps.setInt(2, 20);
int rows = ps.executeUpdate();

CRUD Operations

Create: INSERT INTO ... with executeUpdate().
Read: SELECT ... with executeQuery() returning a ResultSet.
Update: UPDATE ... SET ... WHERE ... with executeUpdate().
Delete: DELETE FROM ... WHERE ... with executeUpdate().

ResultSet Types

A ResultSet can be TYPE_FORWARD_ONLY (default, iterate once in order), TYPE_SCROLL_INSENSITIVE (scrollable, doesn't see updates), or TYPE_SCROLL_SENSITIVE (scrollable, sees updates). Concurrency can be CONCUR_READ_ONLY or CONCUR_UPDATABLE.

ResultSetMetaData

ResultSetMetaData md = rs.getMetaData();
for (int i=1; i<=md.getColumnCount(); i++)
    System.out.println(md.getColumnName(i) + " : " + md.getColumnTypeName(i));

Transactions

By default, JDBC auto-commits each statement. For multi-statement transactions, call con.setAutoCommit(false), then commit() or rollback().

Connection Pooling

Opening a database connection is expensive. Production apps reuse a small pool of connections managed by libraries like HikariCP, DBCP, or C3P0. The pool hands out idle connections on demand and recycles them when returned.

Summary

JDBC provides a vendor-neutral API for Java applications to talk to SQL databases. Use PreparedStatement to avoid SQL injection, iterate results via ResultSet, manage multi-step work with explicit transactions, and use a connection pool in production.

Important Questions

1. What is JDBC? Explain the four driver types.
2. Describe the JDBC workflow step by step.
3. Differentiate Statement, PreparedStatement, and CallableStatement.
4. Write CRUD operations on a student table using PreparedStatement.
5. Explain ResultSet types and concurrency modes.
6. What is ResultSetMetaData? Show an example.
7. How do you implement a JDBC transaction?
8. What is connection pooling and why is it important?