AI-reviewed An AI proof-read this post, but it still deserves a careful read.

JDBC

JDBC Overview

JDBC is a set of database access specifications provided by Java, mainly located in java.sql and javax.sql In the bag. 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 place the driver JAR file into the project’s lib Catalog 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() create 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. Usernames and passwords are usually used as getConnection() is passed in independent parameters of, and it is not recommended to write the URL directly.

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

Basic JDBC programming steps

to provide employee Take an example of inserting a record into the table. 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

recommended PreparedStatement。

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 All belong to resources that need to be closed. recommended try-with-resources is automatically turned off.

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() for performing INSERT、UPDATE、DELETE, returns the number of rows affected.

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` Processing query results

call executeQuery() Executing the query statement will return ResultSet object.

ResultSet It can be understood as a two-dimensional result set with a cursor. The initial cursor is located before the first line, and each call next() Then move down one row.

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() Data can be obtained by column ordinal or column name. Column sequence number from 1 Start. Using column names or aliases is often clearer.

For possible NULL value column of,getInt()、getDouble() Method such as returns the default value of the base type. You need to distinguish between NULL When, you can call wasNull(), or use getObject() Get 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.

use `PreparedStatement`

PreparedStatement Use placeholders to represent parameter values and use type-safe setXxx() Method sets parameters. 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.

`Statement` with `PreparedStatement` the difference between

comparison item	`Statement`	`PreparedStatement`
SQL parameter	Usually through string splicing	use `?` placeholder
SQL injection risk	higher	Parameter values are separated from SQL structure
readability	Dynamic splicing is more complex	Clear parameter locations
repeatedly performed	Processing complete SQL every time	Driver or database may reuse execution plans
type settings	Dependent on string format	use `setString()`、`setInt()` and other methods

Priority should be given to business codes PreparedStatement。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	role
`prepareStatement()`	Create a precompiled SQL object
`setAutoCommit(false)`	Turn off automatic commit and start manually controlling transactions
`commit()`	commit the transaction
`rollback()`	roll back the transaction
`setSavepoint()`	create savepoint
`rollback(Savepoint)`	Roll back to the specified savepoint
`getMetaData()`	Obtaining database metadata
`close()`	close the connection

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

`PreparedStatement`

Common methods include setString()、setInt()、setObject()、executeUpdate() and executeQuery()。

Parameter sequence number from 1 Start.

`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

by DatabaseMetaData You can obtain information such as database product name, version, driver name, and table structure.

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

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

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 typically use database connection pools instead of directly passing through each time DriverManager Create a physical connection.

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. core classes QueryRunner Parameter binding, result set processing, and resource management can be simplified.

Create using external connections QueryRunner When, the connection is still the responsibility of the caller to close.

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. Use the Number Generally, the ratio is fixed as BigInteger More secure.

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 + '\'' +
                '}';
    }
}

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

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 need to be consistent through transactions.

JDBC

JDBC

JDBC Overview

Prepare MySQL drivers

establish a database connection

Basic JDBC 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 Processing query results

SQL injection issues

use PreparedStatement

Statement with PreparedStatement the difference between

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

Random Posts

Related Posts

use `ResultSet` Processing query results

use `PreparedStatement`

`Statement` with `PreparedStatement` the difference between

`Connection`

`PreparedStatement`

`ResultSet`