【后端之旅】数据库操作 JDBC 篇

ZhongyiChen
229 阅读9分钟

使用 Java 编写的业务，大多数时候都会运行很长的时间。而这个过程中产生的数据，就需要保存起来，并在需要的时候再次获取。而最适合保存业务数据的应用，就是数据库。JDBC，就是 Java 程序访问数据库的标准接口。

关键词：Java、MySQL、JDBC、Druid

在【后端之旅】系列中，我们已经学习过了 Java 的基础知识：

【后端之旅】一、Java 基础概念（上篇）

【后端之旅】二、Java 基础概念（下篇）

也学习过了数据库 (MySQL) 的主要命令：

【后端之旅】三、MySQL 该怎么写（上篇）

【后端之旅】四、MySQL 该怎么写（中篇）

【后端之旅】五、MySQL 该怎么写（下篇）

现在是适合把它们结合在一起理解了。

JDBC 定义

JDBC 的全称是 Java DataBase Connectivity。这是一个 Java 提供的标准接口，开发者可以根据不同的数据库下载不同的 JDBC 驱动来实现业务代码与数据库的对接。所以学习 JDBC 就两点：一是熟悉 Java 为 JDBC 提供哪些接口；二是了解所用的数据库需要哪个 JDBC 驱动（即要下载哪个 jar 包）。

JDBC 接口

要理解 JDBC 接口，就必须理解 Java 操作一次数据库要经历哪些步骤：加载 JDBC 驱动到内存；利用 jdbc: 协议并携带账号与密码与数据库建立 socket 连接；创建语句对象用以准备执行 SQL；语句对象执行 SQL；执行并获得结果。

  • DriverManager
    • JDBC 的管理层，主要用于 Java 业务代码与驱动代码(jar 包)的交互，即与数据库建立连接。
  • Connection
    • Java 与数据库建立物理连接的承载接口，主要用于创建 Statement 对象，为接下来执行 SQL 做准备。
    • 也可以实现数据库的事务提交与事务回滚。
  • Statement
    • SQL 的承载接口，主要用于解析并执行静态 SQL。
    • 适用于增删改组织内部提供的数据。
  • PreparedStatement
    • Statement 的加强版接口（即继承于 Statement）。
    • 可预编译 SQL 语句，主要用于提高数据库性能以及防止 SQL 注入攻击。
    • 适用于增删改用户提交的数据。
  • ResultSet
    • 查询数据集接口，方便 Java 程序遍历查询返回的数据。

JDBC 最小例子

如果您尚未在计算机上安装 MySQL，可以参照以下方案进行安装（示例版本为 5.7）：

安装完成后可以创建名为 ego_example 的数据库并导入以下数据：

DROP TABLE IF EXISTS `t_student`;
CREATE TABLE `t_student` (
  `id` int NOT NULL COMMENT '学号',
  `name` char(50) COLLATE utf8mb4_general_ci NOT NULL COMMENT '姓名',
  `age` tinyint NOT NULL COMMENT '年龄',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='学生表';

LOCK TABLES `t_student` WRITE;
INSERT INTO `t_student` VALUES (1,'李土鸡',10),(2,'张带娣',12);
UNLOCK TABLES;

DROP TABLE IF EXISTS `t_teacher`;
CREATE TABLE `t_teacher` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '教师 ID',
  `name` char(50) COLLATE utf8mb4_general_ci NOT NULL COMMENT '姓名',
  `age` tinyint NOT NULL COMMENT '年龄',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='教师表';

LOCK TABLES `t_teacher` WRITE;
INSERT INTO `t_teacher` VALUES (1,'陈得劲',18),(2,'黄大美',23);
UNLOCK TABLES;

在项目中，下载并导入 MySQL 8.0 的相关 jar 包 (对于 MySQL 5.7 请点此下载)，创建 Application.java 文件并编写如下内容：

import java.sql.*;

public class Application {
    public static void main(String[] args) {
        // JDBC 6.0 版本以上必须配置 serverTimezone 参数
        String url = "jdbc:mysql://localhost:3306/ego_school?serverTimezone=UTC";
        String user = "root";
        String pass = "123456";
        String sql = "SELECT * FROM t_teacher";
        try (Connection conn = DriverManager.getConnection(url, user, pass)) {
            try (Statement stmt = conn.createStatement()) {
                try (ResultSet rs = stmt.executeQuery(sql)) {
                    while (rs.next()) {
                        System.out.println(rs.getInt(1) + "|" + rs.getString(2) + "|" + rs.getString(3));
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

编译并执行，就可成功展示全部的教师信息：

image.png

JDBC 实现事务

在上一个例子的基础上，我们为 t_student 表要插入一条学生记录以及删除一条学生记录，要求要么都实现，要么都放弃。写法如下：

import java.sql.*;

public class Application {
    public static void main(String[] args) {
        // JDBC 6.0 版本以上必须配置 serverTimezone 参数
        String url = "jdbc:mysql://localhost:3306/ego_school?serverTimezone=UTC";
        String user = "root";
        String pass = "123456";
        String sql_insert = "INSERT INTO t_student (id, name, age) VALUES (?,?,?)";
        String sql_delete = "DELETE FROM t_student WHERE name=?";
        try (Connection conn = DriverManager.getConnection(url, user, pass)) {
            boolean autoCommit = conn.getAutoCommit();
            try (
                PreparedStatement psd = conn.prepareStatement(sql_delete);
                PreparedStatement psi = conn.prepareStatement(sql_insert);
            ) {
                // 关闭自动提交
                conn.setAutoCommit(false);
                // 执行多条 SQL 语句
                psd.setObject(1, "张带娣");
                if (psd.executeUpdate() < 1) {
                    throw new Exception("Cannot delete from t_student");
                }
                psi.setObject(1, 20150003);
                psi.setObject(2, "王大锤");
                psi.setObject(3, 16);
                if (psi.executeUpdate() < 1) {
                    throw new Exception("Cannot insert into t_student");
                }
                // 提交事务
                conn.commit();
            } catch (Exception e) {
                // 回滚事务
                conn.rollback();
                throw e;
            } finally {
                conn.setAutoCommit(autoCommit);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

执行代码成功前：

image.png

执行代码成功后：

image.png

JDBC 批处理

在学习数据库的时候，我们知道了添加数据时可以一次添加单个记录，也可以一次添加批量记录：

-- 添加单个记录
INSERT INTO 表名 (字段1, 字段2, ...) VALUES (值1, 值2, ...);
INSERT INTO 表名 VALUES (值1, 值2, ...);

-- 添加批量记录
INSERT INTO 表名 (字段1, 字段2, ...) VALUES (值1, 值2, ...), (值1, 值2, ...), ...;
INSERT INTO 表名 VALUES (值1, 值2, ...), (值1, 值2, ...), ...;

那么 JDBC 也应该提供批量操作的实现。首先创建一个 Student 的实体类：

public class Student {
    private int id;
    private String name;
    private int age;

    public Student() {
    }

    public Student(int id, String name, int age) {
        this.id = id;
        this.name = name;
        this.age = age;
    }

    public int getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }
}

然后使用该 Student 实体类创建多个学习记录并插入到数据库中：

import java.sql.*;
import Student;

public class Application {
    public static void main(String[] args) {
        // JDBC 6.0 版本以上必须配置 serverTimezone 参数
        String url = "jdbc:mysql://localhost:3306/ego_school?serverTimezone=UTC";
        String user = "root";
        String pass = "123456";
        String sql_insert = "INSERT INTO t_student (id, name, age) VALUES (?,?,?)";
        Student[] students = {
            new Student(20250005, "龙五", 13),
            new Student(20250006, "龙六", 14),
            new Student(20250007, "龙七", 15),
        };
        try (Connection conn = DriverManager.getConnection(url, user, pass)) {
            try (PreparedStatement psi = conn.prepareStatement(sql_insert);) {
                for (Student stu : students) {
                    psi.setInt(1, stu.getId());
                    psi.setString(2, stu.getName());
                    psi.setInt(3, stu.getAge());
                    // 关键代码 addBatch() 用于指定一个记录的末端
                    psi.addBatch();
                }
                // 批量插入，使用的是 executeBatch()
                int[] ns = psi.executeBatch();
                for (int n : ns) {
                    System.out.println("Inserted " + (n > 0 ? "succeed" : "failed"));
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

JDBC 连接池

在实际项目中，对数据库的请求是长期存在的、断断续续的。如果每次客户端请求都要创建一个数据库连接对象，其创建代价是高昂的。因此，在处处有缓存的计算机，将连接对象池化也是必要的。这里以 Druid 数据库连接池为例，展示如何提高 Java 访问数据库的性能。

首先下载并引入 druid-1.1.23.jar 到项目中。然后改造 JDBC 最小例子 小节的代码为：

import com.alibaba.druid.pool.DruidDataSource;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Application {
    public static void main(String[] args) throws SQLException {
        // JDBC 6.0 版本以上必须配置 serverTimezone 参数
        String url = "jdbc:mysql://localhost:3306/ego_school?serverTimezone=UTC";
        String user = "root";
        String pass = "123456";
        String sql = "select * from t_teacher";

        DruidDataSource dds = new DruidDataSource();

        dds.setUrl(url);
        dds.setUsername(user);
        dds.setPassword(pass);

        dds.init();

        // 我们不再使用 DriverManager.getConnection() 方法来获取连接对象，而是在前面加了一层 dds 连接管理池
        try (Connection conn = dds.getConnection()) {
            try (Statement stmt = conn.createStatement()) {
                try (ResultSet rs = stmt.executeQuery(sql)) {
                    while (rs.next()) {
                        System.out.println(rs.getInt(1) + "|" + rs.getString(2) + "|" + rs.getString(3));
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

现在代码看起来差别不大。但我们在实际的项目中会把 dds 提取为一个全局单例，使得获取的连接对象 conn 都是来自于 Druid 池，而该连接对象调用 close() 方法时只会把自身放回池子而非真正销毁，从而避免了 Connection 实例的频繁创建与销毁。

但连接池提供的远不止缓存连接对象这一功能。以当前的 Druid 连接池为例，它提供了监控 SQL 语句的执行时间、监控 ResultSet 的持有时间、配置初始连接数、配置最大连接数、配置最小连接数、配置连接的最大等待时间等一系列功能：

// ...

dds.setUrl(url);
dds.setUsername(user);
dds.setPassword(pass);

dds.setInitialSize(5);                  // 初始连接数，默认 0
dds.setMaxActive(20);                   // 最大连接数，默认 8
dds.setMinIdle(12);                     // 最小闲置数
dds.setMaxWait(2500);                   // 获取连接的最大等待时间，单位毫秒
dds.setPoolPreparedStatements(true);    // 缓存 PreparedStatement，默认 false
dds.setMaxOpenPreparedStatements(20);   // 缓存 PreparedStatement 的最大数量，默认 -1（不缓存）。大于 0 时自动开启缓存 PreparedStatement，就能省略上一句代码

dds.init();

// ...

SpringBoot 集成 Druid

首先，在项目根目录下的 pom 文件中引入 jdbc 与 druid 依赖：

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.18</version>
</dependency>

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.1.23</version>
</dependency>

然后在配置模块的资源目录（如 config/src/main/resources）中的配置文件 application.yml 中加入以下配置：

spring:
  datasource:
    # 当前 MySQL 版本为 8.0
    driver-class-name: com.mysql.cj.jdbc.Driver
    type: com.alibaba.druid.pool.DruidDataSource
    url: jdbc:mysql://localhost:3306/ego_school?serverTimezone=UTC
    username: root
    password: 123456
    druid:
      initial-size: 5
      max-active: 20
      min-idle: 12
      max-wait: 2500
      pool-prepared-statements: true
      max-open-prepared-statements: 20

如果您使用的配置文件不是 application.yml，而是 application.properties，则添加以下配置：

# JDBC 配置
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.url=jdbc:mysql://localhost:3306/ego_school?serverTimezone=UTC
spring.datasource.username=root
spring.datasource.password=123456

# Druid 配置
spring.datasource.druid.initial-size=5
spring.datasource.druid.max-active=20
spring.datasource.druid.min-idle=12
spring.datasource.druid.max-wait=2500
spring.datasource.druid.pool-prepared-statements=true
spring.datasource.druid.max-open-prepared-statements=20

将这些配置统一导入到 DruidConfig 配置类：

import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;

@Configuration
public class DruidConfig {
    @Value("${spring.datasource.driver-class-name}")
    private String driverClassName;
    @Value("${spring.datasource.url}")
    private String url;
    @Value("${spring.datasource.username}")
    private String username;
    @Value("${spring.datasource.password}")
    private String password;
    @Value("${spring.datasource.druid.initial-size}")
    private int initialSize;
    @Value("${spring.datasource.druid.max-active}")
    private int maxActive;
    @Value("${spring.datasource.druid.min-idle}")
    private int minIdle;
    @Value("${spring.datasource.druid.max-wait}")
    private int maxWait;
    @Value("${spring.datasource.druid.pool-prepared-statements}")
    private boolean poolPreparedStatements;
    @Value("${spring.datasource.druid.max-open-prepared-statements}")
    private int maxOpenPreparedStatements;

    @Bean
    public DataSource dataSource() {
        DruidDataSource datasource = new DruidDataSource();

        datasource.setUrl(url);
        datasource.setDriverClassName(driverClassName);
        datasource.setUsername(username);
        datasource.setPassword(password);
        datasource.setInitialSize(initialSize);
        datasource.setMaxActive(maxActive);
        datasource.setMinIdle(minIdle);
        datasource.setMaxWait(maxWait);
        datasource.setPoolPreparedStatements(poolPreparedStatements);
        datasource.setMaxOpenPreparedStatements(maxOpenPreparedStatements);

        return datasource;
    }
}

接下来就是在 DAO 代码中注入 DataSource 并操作相关的 SQL：

import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.stereotype.Repository;
import Student;

import javax.annotation.Resource;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

@Repository
public class StudentDAO {
    @Resource
    private DataSource dds;

    public List<Student> getList() throws SQLException {
        List<Student> list = new ArrayList<Student>();

        try (Connection conn = dds.getConnection()) {
            try (Statement stmt = conn.createStatement()) {
                try (ResultSet rs = stmt.executeQuery("SELECT id, name, age FROM t_student")) {
                    while (rs.next()) {
                        list.add(new Student(rs.getInt(1), rs.getString(2), rs.getInt(3)));
                    }
                }
            }
        }

        return list;
    }
}

再然后把接口添加到 Controller 代码中：

import StudentDAO;
import Student;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;

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

@Controller
@RequestMapping("/student")
public class StudentController {
    @Autowired
    private StudentDAO dao;

    @GetMapping("/getList")
    @ResponseBody
    public List<Student> getStudentList() throws SQLException {
        return dao.getList();
    }
}

最后把入口 Application 跑起来：

import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.ComponentScan;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

@SpringBootApplication
public class Application {
    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }
}

在浏览器中访问 http://localhost:8080/student/getList 即可看到查询结果：

image.png

小结

我们详细地学习了一遍从 JDBC 到 Druid、从无框架 Java 到 SpringBoot 操作数据库的用法。当然，这仅仅是入门的做法，在实际生产中还需要使用 JPA、HQL 做进一步的封装。而这进一步的封装，在未来的文章中再和大家探讨吧。

avatar
文章
阅读
粉丝