JDBC

JDBC Overview

JDBC is a set of database access specifications provided by Java and is mainly located in the java.sql and javax.sql packages. Database manufacturers implement the JDBC interface through drivers, and Java programs write code for the unified interface to connect different types of relational databases.

JDBC is commonly used to perform the following operations:

Establish and close database connections.
Executes add, modify, delete, and query statements.
Process query result sets.
Control transactions.
Gets metadata for the database and result set.

Prepare MySQL drivers

Before using JDBC to connect to MySQL, you need to add the MySQL Connector/J driver to the project dependency.

In traditional Java projects, you can put the driver JAR file into the project’s lib directory and add it to the class library. In the Maven project, versions should be driven through project dependency management.

Common driver class names for MySQL 8 are as follows:

com.mysql.cj.jdbc.Driver

Modern JDBC drivers usually support automatic registration, but explicit loading of drivers still helps understand the basic JDBC process.

try {
    Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
    throw new RuntimeException("未找到 MySQL JDBC 驱动", e);
}

establish a database connection

Use DriverManager.getConnection() to create a Connection object.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class ConnectionTest {
    public static void main(String[] args) {
        String url =
                "jdbc:mysql://localhost:3306/java2601" +
                "?serverTimezone=UTC&characterEncoding=utf8";
        String username = "root";
        String password = "root";

        try (Connection connection = DriverManager.getConnection(
                url,
                username,
                password
        )) {
            System.out.println("数据库连接成功：" + !connection.isClosed());
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Common configurations in JDBC URLs include user names, passwords, time zones, character encoding, and SSL settings. The username and password are usually passed in as independent parameters of getConnection(), and it is not recommended to write directly to the URL.

Database accounts and passwords should not be hard-coded in public repositories. Real projects typically use configuration files, environment variables, or key management services.

JDBC Basic Programming Steps

Take inserting a record into employee table as an example. The basic steps are as follows:

loaded for

Class.forName("com.mysql.cj.jdbc.Driver");

acquire a connection

Connection connection = DriverManager.getConnection(
        "jdbc:mysql://localhost:3306/java2601?serverTimezone=UTC&characterEncoding=utf8",
        "root",
        "root"
);

Create SQL execution objects

PreparedStatement is recommended.

String sql = "insert into employee(first_name, salary, department_id) " +
        "values(?, ?, ?)";
PreparedStatement statement = connection.prepareStatement(sql);

Set parameters and execute

statement.setString(1, "Tom");
statement.setDouble(2, 8000);
statement.setInt(3, 5001);

int affectedRows = statement.executeUpdate();
System.out.println("受影响行数：" + affectedRows);

close the asset

Connection, Statement and ResultSet are all resources that need to be closed. It is recommended to use try-with-resources to automatically shut down.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class InsertEmployee {
    public static void main(String[] args) {
        String url =
                "jdbc:mysql://localhost:3306/java2601" +
                "?serverTimezone=UTC&characterEncoding=utf8";
        String sql = "insert into employee(" +
                "first_name, salary, department_id" +
                ") values(?, ?, ?)";

        try (Connection connection = DriverManager.getConnection(
                url,
                "root",
                "root"
        ); PreparedStatement statement = connection.prepareStatement(sql)) {

            statement.setString(1, "张三");
            statement.setDouble(2, 8000);
            statement.setInt(3, 5001);

            int affectedRows = statement.executeUpdate();
            System.out.println("受影响行数：" + affectedRows);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Execute add, delete, and change statements

executeUpdate() is used to execute INSERT, UPDATE, and DELETE to return the affected rows.

new data

String sql = "insert into employee(" +
        "first_name, job_id, salary, department_id" +
        ") values(?, ?, ?, ?)";

try (PreparedStatement statement = connection.prepareStatement(sql)) {
    statement.setString(1, "Rose");
    statement.setString(2, "程序员");
    statement.setDouble(3, 9000);
    statement.setInt(4, 5001);
    statement.executeUpdate();
}

modify data

String sql = "update employee " +
        "set first_name = ?, job_id = ?, salary = ?, department_id = ? " +
        "where employee_id = ?";

try (PreparedStatement statement = connection.prepareStatement(sql)) {
    statement.setString(1, "Jack");
    statement.setString(2, "项目经理");
    statement.setDouble(3, 12000);
    statement.setInt(4, 5002);
    statement.setInt(5, 101);
    statement.executeUpdate();
}

delete data

String sql = "delete from employee where employee_id = ?";

try (PreparedStatement statement = connection.prepareStatement(sql)) {
    statement.setInt(1, 101);
    statement.executeUpdate();
}

Use `ResultSet` to process query results

Calling executeQuery() to execute the query statement will return the ResultSet object.

ResultSet can be understood as a two-dimensional result set with a cursor. The initial cursor is located before the first line and moves down one line after each call to next().

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class QueryEmployee {
    public static void main(String[] args) {
        String url =
                "jdbc:mysql://localhost:3306/java2601" +
                "?serverTimezone=UTC&characterEncoding=utf8";
        String sql = "select " +
                "employee_id as eid, first_name, job_id, salary " +
                "from employee";

        try (Connection connection = DriverManager.getConnection(
                url,
                "root",
                "root"
        ); PreparedStatement statement = connection.prepareStatement(sql);
             ResultSet resultSet = statement.executeQuery()) {

            while (resultSet.next()) {
                int employeeId = resultSet.getInt("eid");
                String firstName = resultSet.getString("first_name");
                String jobId = resultSet.getString("job_id");
                double salary = resultSet.getDouble("salary");

                System.out.println(
                        employeeId + "\t" +
                        firstName + "\t" +
                        jobId + "\t" +
                        salary
                );
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

getXxx() can obtain data by column sequence number or column name. Column numbers start from 1. Using column names or aliases is usually clearer.

For numeric columns that may be NULL, methods such as getInt() and getDouble() will return default values for basic types. When you need to distinguish NULL in the database, you can call wasNull() or use getObject() to obtain the packaging type.

SQL injection issues

The following string splicing method carries SQL injection risks:

String sql = "select * from db_users " +
        "where username = '" + username + "' " +
        "and password = '" + password + "'";

User input becomes directly part of the SQL syntax. An attacker may construct special input to change the semantics of the original query.

Using `PreparedStatement`

PreparedStatement uses placeholders to represent parameter values and sets parameters through the type-safe setXxx() method. Parameter values are not parsed as SQL structures, so most SQL injection problems can be effectively avoided.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

public class LoginTest {
    public static void main(String[] args) {
        Scanner scanner = new Scanner(System.in);

        System.out.println("请输入用户名：");
        String username = scanner.nextLine();

        System.out.println("请输入密码：");
        String password = scanner.nextLine();

        String sql = "select id from db_users " +
                "where username = ? and password = ?";

        try (Connection connection = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/java2601?serverTimezone=UTC&characterEncoding=utf8",
                "root",
                "root"
        ); PreparedStatement statement = connection.prepareStatement(sql)) {

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

            try (ResultSet resultSet = statement.executeQuery()) {
                if (resultSet.next()) {
                    System.out.println("登录成功");
                } else {
                    System.out.println("登录失败");
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

The example is only used to illustrate JDBC queries. Real systems should not store clear passwords in the database, but should store the results processed by a reliable password hashing algorithm and use random salts.

Differences between `Statement` and `PreparedStatement`

Comparison Items	`Statement`	`PreparedStatement`
SQL parameter	is usually spliced by string	using `?` placeholder
SQL injection risk	Higher	Parameter value separated from SQL structure
Readability	Dynamic splicing is more complex	Parameter position is clear
Repeat execution	processing complete SQL	driver or database may reuse execution plan
type settings	relies on string format	Use `setString()`, `setInt()` and other methods

PreparedStatement should be used first in business codes. SQL structures such as table names, column names, and sort directions cannot use placeholders and must be selected through a trusted whitelist.

Common JDBC APIs

`Connection`

Connection represents a database connection. Common methods include:

Method	Action
`prepareStatement()`	Creating a precompiled SQL object
`setAutoCommit(false)`	Turn off automatic submission and start manual control of transactions
`commit()`	Submission
`rollback()`	Rollback Transactions
`setSavepoint()`	Create save point
`rollback(Savepoint)`	Rollback to the specified save point
`getMetaData()`	Getting database metadata
`close()`	Close connection

To form the same local transaction, multiple SQL must be executed in the same Connection.

`PreparedStatement`

Common methods include setString(), setInt(), setObject(), executeUpdate() and executeQuery().

Parameter sequence numbers start from 1.

`ResultSet`

Common methods include next(), getString(), getInt(), getObject() and wasNull().

JDBC transaction control

The following example deletes two employee records in the same transaction. When any operation fails, all operations are rolled back.

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class TransactionTest {
    public static void deleteEmployees(
            Connection connection,
            int firstId,
            int secondId
    ) throws SQLException {
        String sql = "delete from employee where employee_id = ?";
        boolean originalAutoCommit = connection.getAutoCommit();

        try {
            connection.setAutoCommit(false);

            try (PreparedStatement statement = connection.prepareStatement(sql)) {
                statement.setInt(1, firstId);
                statement.executeUpdate();

                statement.setInt(1, secondId);
                statement.executeUpdate();
            }

            connection.commit();
        } catch (SQLException e) {
            connection.rollback();
            throw e;
        } finally {
            connection.setAutoCommit(originalAutoCommit);
        }
    }
}

When using connection pools, the connection state should be restored before returning the connection, otherwise subsequent borrowing of code from the same physical connection may be affected.

savepoint

Savepoints allow transactions to be rolled back to intermediate states.

import java.sql.Connection;
import java.sql.Savepoint;
import java.sql.SQLException;

public class SavepointTest {
    public static void execute(Connection connection) throws SQLException {
        connection.setAutoCommit(false);
        Savepoint savepoint = null;

        try {
            executeFirstSql(connection);
            savepoint = connection.setSavepoint("after_first_sql");
            executeSecondSql(connection);
            connection.commit();
        } catch (SQLException e) {
            if (savepoint != null) {
                connection.rollback(savepoint);
                connection.commit();
            } else {
                connection.rollback();
            }
            throw e;
        }
    }

    private static void executeFirstSql(Connection connection) {
    }

    private static void executeSecondSql(Connection connection) {
    }
}

database metadata

Through DatabaseMetaData, you can obtain database product name, version, driver name and table structure and other information.

import java.sql.DatabaseMetaData;

DatabaseMetaData metadata = connection.getMetaData();
System.out.println(metadata.getDatabaseProductName());
System.out.println(metadata.getDatabaseProductVersion());
System.out.println(metadata.getDriverName());

Result set metadata

Through ResultSetMetaData, you can obtain information such as the number of columns, column names, and column types in the result set.

import java.sql.ResultSetMetaData;

ResultSetMetaData metadata = resultSet.getMetaData();
int columnCount = metadata.getColumnCount();

for (int index = 1; index <= columnCount; index++) {
    System.out.println(metadata.getColumnLabel(index));
    System.out.println(metadata.getColumnTypeName(index));
}

JDBC encapsulation

Encapsulation connection creation process

package com.hyxy.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public final class DBUtil {
    private static final String URL =
            "jdbc:mysql://localhost:3306/java2601" +
            "?serverTimezone=UTC&characterEncoding=utf8";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "root";

    private DBUtil() {
    }

    static {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            throw new ExceptionInInitializerError(e);
        }
    }

    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(URL, USERNAME, PASSWORD);
    }
}

Real projects usually use database connection pools instead of creating physical connections directly through DriverManager each time.

DAO and VO

DAO is the abbreviation for Data Access Object and is used to encapsulate database access logic. The business layer completes additions, deletions, modifications, and inspections through the DAO method to avoid scattering SQL in the business code.

VO is the abbreviation of Value Object. In the current course example, VO is used to hold database records, and the fields usually correspond to the table structure. Real projects also often use names such as Entity, POJO, or DTO, but the meanings are not exactly the same.

Apache Commons DbUtils

DbUtils is a lightweight encapsulation of JDBC. The core class QueryRunner simplifies parameter binding, result set processing, and resource management.

When QueryRunner is created using an external connection, the caller is still responsible for closing the connection.

new data

import org.apache.commons.dbutils.QueryRunner;

import java.sql.Connection;
import java.sql.SQLException;

public class DbUtilsInsertTest {
    public static void main(String[] args) {
        QueryRunner runner = new QueryRunner();
        String sql = "insert into employee(first_name, job_id) values(?, ?)";

        try (Connection connection = DBUtil.getConnection()) {
            int affectedRows = runner.update(
                    connection,
                    sql,
                    "Smith",
                    "财务总监"
            );
            System.out.println("受影响行数：" + affectedRows);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Get self-added primary key

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.sql.SQLException;

public class DbUtilsPrimaryKeyTest {
    public static void main(String[] args) {
        QueryRunner runner = new QueryRunner();
        String sql = "insert into employee(first_name, job_id) values(?, ?)";

        try (Connection connection = DBUtil.getConnection()) {
            Number primaryKey = runner.insert(
                    connection,
                    sql,
                    new ScalarHandler<>(),
                    "Smith2",
                    "财务总监2"
            );
            System.out.println(primaryKey);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

The specific type of primary key value returned by the driver may vary, and it is usually safer to use Number than fix it to BigInteger.

Query multiple records

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

public class DbUtilsListTest {
    public static void main(String[] args) {
        QueryRunner runner = new QueryRunner();
        String sql = "select * from employee";

        try (Connection connection = DBUtil.getConnection()) {
            List<Employee> employees = runner.query(
                    connection,
                    sql,
                    new BeanListHandler<>(Employee.class)
            );

            for (Employee employee : employees) {
                System.out.println(employee);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

The JavaBean property name needs to match the column label of the query result. When column names are inconsistent, aliases can be used in SQL.

select employee_id as employeeId,
       first_name as firstName,
       department_id as departmentId
from employee;

Query by condition

String sql = "select * from employee " +
        "where department_id = ? and salary >= ?";

try (Connection connection = DBUtil.getConnection()) {
    List<Employee> employees = runner.query(
            connection,
            sql,
            new BeanListHandler<>(Employee.class),
            5001,
            3000
    );
}

Query by primary key

import org.apache.commons.dbutils.handlers.BeanHandler;

String sql = "select * from employee where employee_id = ?";

try (Connection connection = DBUtil.getConnection()) {
    Employee employee = runner.query(
            connection,
            sql,
            new BeanHandler<>(Employee.class),
            101
    );

    System.out.println(employee);
}

Comprehensive example of user management

Create user table

drop table if exists t_user;

create table t_user (
    id int not null auto_increment,
    username varchar(255) null,
    pwd varchar(255) null,
    email varchar(255) null,
    primary key (id)
) engine = InnoDB
  default character set = utf8mb4;

insert into t_user(username, pwd, email)
values('Jerry', '888888', 'jerry@126.com');

Create a user entity class

package com.hyxy.vo;

public class User {
    private int id;
    private String username;
    private String pwd;
    private String email;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPwd() {
        return pwd;
    }

    public void setPwd(String pwd) {
        this.pwd = pwd;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", email='" + email + '\'' +
                '}';
    }
}

It is not recommended to output passwords in toString().

Create user DAO

package com.hyxy.dao;

import com.hyxy.vo.User;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

public class UserDao {
    private final QueryRunner runner = new QueryRunner();

    public int register(User user) throws SQLException {
        String sql = "insert into t_user(username, pwd, email) values(?, ?, ?)";

        try (Connection connection = DBUtil.getConnection()) {
            return runner.update(
                    connection,
                    sql,
                    user.getUsername(),
                    user.getPwd(),
                    user.getEmail()
            );
        }
    }

    public User login(String username, String password) throws SQLException {
        String sql = "select id, username, email " +
                "from t_user where username = ? and pwd = ?";

        try (Connection connection = DBUtil.getConnection()) {
            return runner.query(
                    connection,
                    sql,
                    new BeanHandler<>(User.class),
                    username,
                    password
            );
        }
    }

    public int update(User user) throws SQLException {
        String sql = "update t_user " +
                "set username = ?, pwd = ?, email = ? where id = ?";

        try (Connection connection = DBUtil.getConnection()) {
            return runner.update(
                    connection,
                    sql,
                    user.getUsername(),
                    user.getPwd(),
                    user.getEmail(),
                    user.getId()
            );
        }
    }

    public int deleteByUsername(String username) throws SQLException {
        String sql = "delete from t_user where username = ?";

        try (Connection connection = DBUtil.getConnection()) {
            return runner.update(connection, sql, username);
        }
    }

    public List<User> findAll() throws SQLException {
        String sql = "select id, username, email from t_user";

        try (Connection connection = DBUtil.getConnection()) {
            return runner.query(
                    connection,
                    sql,
                    new BeanListHandler<>(User.class)
            );
        }
    }
}

Example considerations

The interaction layer should not directly splice SQL.
Data access logic should be placed in the DAO and business rules should be placed in the Service.
Neither database passwords nor user passwords should be hard-coded.
The login function should use password hashes rather than plaintext comparisons.
Each JDBC resource must be turned off correctly.
Multiple related updates require transactions to ensure consistency.

JDBC

JDBC

JDBC Overview

Prepare MySQL drivers

establish a database connection

JDBC Basic Programming Steps

loaded for

acquire a connection

Create SQL execution objects

Set parameters and execute

close the asset

Execute add, delete, and change statements

new data

modify data

delete data

Use ResultSet to process query results

SQL injection issues

Using PreparedStatement

Differences between Statement and PreparedStatement

Common JDBC APIs

Connection

PreparedStatement

ResultSet

JDBC transaction control

savepoint

database metadata

Result set metadata

JDBC encapsulation

Encapsulation connection creation process

DAO and VO

Apache Commons DbUtils

new data

Get self-added primary key

Query multiple records

Query by condition

Query by primary key

Comprehensive example of user management

Create user table

Create a user entity class

Create user DAO

Example considerations

Use `ResultSet` to process query results

Using `PreparedStatement`

Differences between `Statement` and `PreparedStatement`

`Connection`

`PreparedStatement`

`ResultSet`