JDBC Fundamentals

Java Database Connectivity (JDBC) provides a standard API for Java programs to interact with any relational database. This guide covers the core concepts, key classes, and practical usage of JDBC for database operations.

What is JDBC?

JDBC is a set of interfaces and classes defined by Oracle (originally Sun Microsystems) that allows Java applications to send SQL statements to a database and process the results. Each database vendor provides a concrete implementation of these interfaces in the form of a JDBC driver JAR file. The developer writes code against the JDBC API, and the driver translates those calls into database-specific commands.

Advantages of Using JDBC

• Database Independence: Code written using JDBC interfaces works with any database for which a driver is available. To switch databases, you only need to replace the driver JAR file.
• Portability: The same Java code can connect to Oracle, MySQL, PostgreSQL, or any other relational database as long as the appropriate driver is on the classpath.
• Standardization: The API provides a consistent way to execute SQL, handle transactions, and retrieve results.

Core Steps for Using JDBC

1. Load the JDBC driver (optional from JDBC 4.0 and MySQL 5+ as the driver can be autoloaded).
2. Establish a connection to the database using DriverManager.getConnection().
3. Create a statement object (Statement, PreparedStatement, or CallableStatement).
4. Execute an SQL statement (executeQuery(), executeUpdate(), or execute()).
5. Process the results (for queries, iterate through the ResultSet).
6. Close all resources (ResultSet, Statement, Connection) in reverse order of creation.

Example: Basic JDBC Program

import java.sql.*;

public class BasicJdbcExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/testdb?useSSL=false&serverTimezone=UTC";
        String user = "root";
        String pass = "password";

        try (Connection conn = DriverManager.getConnection(url, user, pass);
             Statement stmt = conn.createStatement()) {

            // Execute an UPDATE
            String sql = "UPDATE accounts SET balance = 500 WHERE id = 1";
            int affectedRows = stmt.executeUpdate(sql);
            System.out.println("Rows updated: " + affectedRows);

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

JDBC API in Detail

DriverManager Class

This utility class manages the set of JDBC drivers. All its method are static.

Method	Description
static void registerDriver(Driver driver)	Registers a driver with the DriverManager
static Connection getConnection(String url, String user, String password)	Attempts to establish a connection to the given database URL

Connection URL Format:

• Standard format: jdbc:mysql://host:port/database?parameter1=value1&parameter2=value2
• For a local MySQL instance on default port (3306), you can simplify to: jdbc:mysql:///database?parameters

Important URL Parameters:

Parameter	Description
useUnicode=true	Enables Unicode support
characterEncoding=UTF-8	Specifies character encoding when useUnicode is true
useSSL=false	Disables SSL (simplifies local development)
serverTimezone=Asia/Shanghai	Sets the server timezone
useServerPreStmts=true	Enables server-side prepared statement caching

Connection Interface

Represents a session with a specific database. Use it to create statements, manage transactions, and get metadata.

Creating Statement Objects:

Method	Description
Statement createStatement()	Creates a simple Statement for static SQL
PreparedStatement prepareStatement(String sql)	Creates a PreparedStatement for parameterized SQL (prevents SQL injection)
CallableStatement prepareCall(String sql)	Creates a CallableStatement for stored procedures

Transaction Management:

Method	Description
void setAutoCommit(boolean autoCommit)	Sets auto-commit mode. false begins a transaction.
void commit()	Commits the current transaction.
void rollback()	Rolls back the current transaction.

Example: Transaction Handling

public class TransactionExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/testdb?useSSL=false";
        String user = "root";
        String pass = "password";

        try (Connection conn = DriverManager.getConnection(url, user, pass);
             Statement stmt = conn.createStatement()) {

            conn.setAutoCommit(false); // begin transaction

            try {
                String sql1 = "UPDATE accounts SET balance = balance - 100 WHERE id = 1";
                String sql2 = "UPDATE accounts SET balance = balance + 100 WHERE id = 2";

                stmt.executeUpdate(sql1);
                // Simulate an error
                // if (true) throw new SQLException("Simulated error");
                stmt.executeUpdate(sql2);

                conn.commit();
                System.out.println("Transaction committed.");
            } catch (SQLException e) {
                conn.rollback();
                System.out.println("Transaction rolled back.");
                e.printStackTrace();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Statement Interface

Used to execute static SQL strings. Its vulnerable to SQL injection if user input is concatenated directly.

Key Methods:

Method	Description
int executeUpdate(String sql)	Executes DML (INSERT, UPDATE, DELETE) or DDL (CREATE, ALTER, DROP) statements. Returns the number of affected rows for DML. For DDL, it may return 0.
ResultSet executeQuery(String sql)	Executes a DQL (SELECT) statement and returns a ResultSet.

ResultSet Interface

Encapsulates the result of a query. Data is presented in a tabular format with a cursor that moves row by row.

Key Methods:

Method	Description
boolean next()	Moves the cursor forward one row and returns true if the new row is valid.
Type getType(int columnIndex)	Retrieves the value of the specified column by index (starting at 1).
Type getType(String columnLabel)	Retrieves the value of the specified column by column name.

Example: Processing Query Results

public class ResultSetExample {
    public static void main(String[] args) throws Exception {
        String url = "jdbc:mysql:///testdb?useSSL=false";
        String user = "root";
        String pass = "password";

        try (Connection conn = DriverManager.getConnection(url, user, pass);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery("SELECT id, name, balance FROM accounts")) {

            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                double balance = rs.getDouble("balance");
                System.out.printf("ID: %d, Name: %s, Balance: %.2f%n", id, name, balance);
            }
        }
    }
}

Mapping Query Results to Java Objects

A common pattern is to map each row of a ResultSet to a Java object and collect them in a List.

import java.util.*;

// Assuming a simple Account class
class Account {
    private int id;
    private String name;
    private double balance;

    // Constructor, getters, setters, toString...
}

public class ObjectMappingExample {
    public static void main(String[] args) throws Exception {
        String url = "jdbc:mysql:///testdb?useSSL=false";
        String user = "root";
        String pass = "password";
        List<Account> accountList = new ArrayList<>();

        try (Connection conn = DriverManager.getConnection(url, user, pass);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery("SELECT * FROM accounts")) {

            while (rs.next()) {
                Account act = new Account();
                act.setId(rs.getInt("id"));
                act.setName(rs.getString("name"));
                act.setBalance(rs.getDouble("balance"));
                accountList.add(act);
            }
        }
        accountList.forEach(System.out::println);
    }
}

PreparedStatement Interface

Extends Statement and provides a more secure and efficient way to execute parameterized SQL. It uses ? as placeholders for input values, which prevents SQL injection.

Key Methods:

Method	Description
void setXxx(int parameterIndex, Xxx value)	Sets the value for the ? placeholder at the given index (1-based).
int executeUpdate()	Executes the DML/DDL SQL that was set during creation.
ResultSet executeQuery()	Executes the SELECT SQL and returns the result set.

Why PreparedStatement Prevents SQL Injection:

When a SQL string containing ? placeholders is sent to the database, the DBMS parses, compiles, and optimizes the statement template without the actual values. The values are then bound later, and the database treats them strictly as data, not as executable SQL. This makes it impossible for a user input like ' OR '1'='1 to alter the query logic.

Example: Safe Login

public class SafeLogin {
    public static boolean authenticate(String username, String password) {
        String sql = "SELECT * FROM users WHERE username = ? AND password = ?";
        try (Connection conn = DriverManager.getConnection(url, user, pass);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, username);
            pstmt.setString(2, password);

            try (ResultSet rs = pstmt.executeQuery()) {
                return rs.next(); // if a row exists, login successful
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }
}

Enabling Prepared Statement Caching for Better Performance:

Add useServerPreStmts=true to the connection URL. This instructs the MySQL server to cache the compiled statement, which improves performance when the same SQL template is executed multiple times with different values.

Database Connection Pooling

Creating a new database connection for every client request is inefficient. A connection pool maintains a set of reusable connections, dramatically improving responsiveness and resource management.

How Connection Pools Work

• The pool is initialized with a minimum number of connections (initialSize).
• When a thread requests a connection, the pool provides one from the pool (or creates a new one if all are busy, up to maxActive).
• When the thread finishes, it close() the connection, which returns it to the pool for reuse.
• Idle connections are periodically checked and those exceeding maxIdleTime are evicted.

Standard Interface: javax.sql.DataSource

All connection pool implementations must implement this interface. The key method is Connection getConnection().

Popular Connection Pool: Druid (Alibaba)

Druid is a high-performance, feature-rich connection pool widely used in Java projects. It provides comprehensive monitoring, SQL injection defense, and logging capabilities.

Using Druid:

1. Add the Druid JAR file to your project.
2. Create a configuration file (e.g., druid.properties).
3. Use DruidDataSourceFactory to create the DataSource object.

Example druid.properties file:

# Database driver class name
driverClassName=com.mysql.cj.jdbc.Driver
# JDBC URL
url=jdbc:mysql://localhost:3306/testdb?useSSL=false&serverTimezone=Asia/Shanghai
# Database credentials
username=root
password=123456
# Initial pool size
initialSize=5
# Maximum number of active connections
maxActive=10
# Maximum wait time for a connection in milliseconds (3000ms = 3s)
maxWait=3000
# Minimum idle connections
minIdle=5

Code Example:

import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.util.Properties;

public class DruidDemo {
    public static void main(String[] args) throws Exception {
        Properties props = new Properties();
        // Load properties file from classpath
        try (InputStream is = DruidDemo.class.getClassLoader().getResourceAsStream("druid.properties")) {
            props.load(is);
        }

        DataSource dataSource = DruidDataSourceFactory.createDataSource(props);

        try (Connection conn = dataSource.getConnection()) {
            System.out.println("Connection obtained: " + conn);
            // Use the connection for database operations
        }
    }
}

Practical Exercise: CRUD Operations on a Brand Table

This section demonstrates how to perform basic CRUD operations using JDBC with PreparedStatement and a Druid connection pool.

Step 1: Database Setup

CREATE TABLE tb_brand (
    id INT PRIMARY KEY AUTO_INCREMENT,
    brand_name VARCHAR(20),
    company_name VARCHAR(20),
    ordered INT,
    description VARCHAR(100),
    status INT
);

INSERT INTO tb_brand (brand_name, company_name, ordered, description, status) 
VALUES ('Three Squirrels', 'Three Squirrels Co., Ltd.', 5, 'Delicious and healthy', 0),
       ('Huawei', 'Huawei Technologies Co., Ltd.', 100, 'Building a connected world', 1);

Step 2: Java Entity Class

public class Brand {
    private Integer id;
    private String brandName;
    private String companyName;
    private Integer ordered;
    private String description;
    private Integer status;

    // Constructors, getters, setters, toString...
}

Step 3: CRUD Implementation

Read All Brands:

public List<Brand> findAll(DataSource ds) throws SQLException {
    String sql = "SELECT * FROM tb_brand";
    List<Brand> brands = new ArrayList<>();

    try (Connection conn = ds.getConnection();
         PreparedStatement pstmt = conn.prepareStatement(sql);
         ResultSet rs = pstmt.executeQuery()) {

        while (rs.next()) {
            Brand b = new Brand();
            b.setId(rs.getInt("id"));
            b.setBrandName(rs.getString("brand_name"));
            b.setCompanyName(rs.getString("company_name"));
            b.setOrdered(rs.getInt("ordered"));
            b.setDescription(rs.getString("description"));
            b.setStatus(rs.getInt("status"));
            brands.add(b);
        }
    }
    return brands;
}

Create a New Brand:

public boolean addBrand(DataSource ds, Brand brand) throws SQLException {
    String sql = "INSERT INTO tb_brand (brand_name, company_name, ordered, description, status) VALUES (?, ?, ?, ?, ?)";

    try (Connection conn = ds.getConnection();
         PreparedStatement pstmt = conn.prepareStatement(sql)) {

        pstmt.setString(1, brand.getBrandName());
        pstmt.setString(2, brand.getCompanyName());
        pstmt.setInt(3, brand.getOrdered());
        pstmt.setString(4, brand.getDescription());
        pstmt.setInt(5, brand.getStatus());

        return pstmt.executeUpdate() > 0;
    }
}

Update a Brand by ID:

public boolean updateBrand(DataSource ds, Brand brand) throws SQLException {
    String sql = "UPDATE tb_brand SET brand_name=?, company_name=?, ordered=?, description=?, status=? WHERE id=?";

    try (Connection conn = ds.getConnection();
         PreparedStatement pstmt = conn.prepareStatement(sql)) {

        pstmt.setString(1, brand.getBrandName());
        pstmt.setString(2, brand.getCompanyName());
        pstmt.setInt(3, brand.getOrdered());
        pstmt.setString(4, brand.getDescription());
        pstmt.setInt(5, brand.getStatus());
        pstmt.setInt(6, brand.getId());

        return pstmt.executeUpdate() > 0;
    }
}

Delete a Brand by ID:

public boolean deleteBrand(DataSource ds, int brandId) throws SQLException {
    String sql = "DELETE FROM tb_brand WHERE id = ?";

    try (Connection conn = ds.getConnection();
         PreparedStatement pstmt = conn.prepareStatement(sql)) {

        pstmt.setInt(1, brandId);
        return pstmt.executeUpdate() > 0;
    }
}