Understanding JDBC: Basics, Core Interfaces, PreparedStatement, Connection Pools, Pagination, and DBUtils

JDBC (Java Database Connectivity) is the standard Java API for executing SQL statements, serving as the foundation for higher‑level ORM frameworks such as MyBatis, Hibernate, and Spring Data JPA; therefore, mastering JDBC remains essential.

ORM (Object‑Relational Mapping) creates a mapping between Java objects and relational database tables, allowing direct manipulation of Java objects without writing raw SQL.

JDBC Basics

JDBC provides a uniform set of interfaces that hide vendor‑specific details; any database can be accessed by loading the appropriate driver and using the same API.

Database data is consumed by programs, so Java programs need JDBC to connect and query.

JDBC defines a set of interfaces (Connection, Statement, ResultSet) that remain constant across databases; only the driver implementation changes.

The core workflow involves loading the driver, obtaining a Connection, creating a Statement (or PreparedStatement), executing SQL, processing the ResultSet, and finally closing resources.

Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;

try {
    // Load driver (recommended way)
    Class.forName("com.mysql.jdbc.Driver");
    // Obtain connection
    connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/zhongfucheng", "root", "root");
    // Create statement
    statement = connection.createStatement();
    // Execute query
    resultSet = statement.executeQuery("SELECT * FROM users");
    while (resultSet.next()) {
        System.out.println(resultSet.getString(1));
        System.out.println(resultSet.getString(2));
    }
} catch (SQLException e) {
    e.printStackTrace();
} catch (ClassNotFoundException e) {
    e.printStackTrace();
} finally {
    // Close resources in reverse order
    if (resultSet != null) try { resultSet.close(); } catch (SQLException e) { e.printStackTrace(); }
    if (statement != null) try { statement.close(); } catch (SQLException e) { e.printStackTrace(); }
    if (connection != null) try { connection.close(); } catch (SQLException e) { e.printStackTrace(); }
}

PreparedStatement vs. Statement

PreparedStatement uses placeholders, simplifying SQL with parameters.

It pre‑compiles SQL, improving performance by avoiding repeated compilation.

It prevents SQL injection by separating code from data.

Database Connection Pools

Opening and closing connections is resource‑intensive; connection pools (e.g., C3P0, DBCP, Druid) reuse connections to boost performance. Druid, an Alibaba open‑source pool, is recommended for learning.

Pagination

Common interview and production requirement; examples for Oracle and MySQL:

Oracle Pagination

SELECT * FROM (
    SELECT columns, ROWNUM rn FROM table_name WHERE ROWNUM <= (currentPage * lineSize)
) temp WHERE rn > (currentPage - 1) * lineSize;

MySQL Pagination

SELECT * FROM table_name LIMIT start, length;  // start = (currentPage-1)*lineSize

DBUtils

Apache DBUtils simplifies JDBC code, offering concise CRUD operations without writing boilerplate. Below is a JUnit‑style example demonstrating insert, query, delete, update, and batch operations.

public class Test {
    @org.junit.Test
    public void add() throws SQLException {
        QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "INSERT INTO student (id,name) VALUES(?,?)";
        queryRunner.update(sql, new Object[]{"100", "zhongfucheng"});
    }
    @org.junit.Test
    public void query() throws SQLException {
        QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "SELECT * FROM student";
        List list = (List) queryRunner.query(sql, new BeanListHandler(Student.class));
        System.out.println(list.size());
    }
    @org.junit.Test
    public void delete() throws SQLException {
        QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "DELETE FROM student WHERE id='100'";
        queryRunner.update(sql);
    }
    @org.junit.Test
    public void update() throws SQLException {
        QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "UPDATE student SET name=? WHERE id=?";
        queryRunner.update(sql, new Object[]{"zhongfuchengaaa", 1});
    }
    @org.junit.Test
    public void batch() throws SQLException {
        QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "INSERT INTO student (name,id) VALUES(?,?)";
        Object[][] objects = new Object[10][];
        for (int i = 0; i < 10; i++) {
            objects[i] = new Object[]{"aaa", i + 300};
        }
        queryRunner.batch(sql, objects);
    }
}

Overall, the article emphasizes that a solid grasp of JDBC fundamentals—core interfaces, prepared statements, connection pooling, pagination, and utility libraries—provides a strong foundation for any Java‑based data access work.