连接数据库

摘要：本文学习了如何使用JDBC连接数据库。

环境

Windows 10 企业版 LTSC 21H2
Java 1.8
MySQL 5.7.40

1 持久化

数据持久化就是把数据保存到可掉电式存储设备中以供之后使用，大多数情况下，数据持久化意味着将内存中的数据保存到硬盘上进行存储。

持久化的主要应用是将内存中的数据存储在关系型数据库中，当然也可以存储在磁盘文件中，比如XML文件和Excel文件等等。

持久化的实现主要有两种：

通过IO流技术将数据存储在本地磁盘，优点是比较简单，缺点是不方便管理和维护。
通过各种数据库将数据存储在关系型数据库，优点是方便管理和维护，缺点是需要学习相关的方法。

2 简介

2.1 概念

JDBC的全称是Java Database Connectivity，意为Java和数据库的连接，其定义了用来访问数据库的标准Java类库，使用这个类库可以更加方便地访问数据库资源。

在最开始的时候，程序员使用数据库需要安装数据库驱动，不同厂商的数据库有其各自的驱动程序。

为了方便开发，Sun公司提供了JDBC接口，让数据库厂商实现JDBC接口，程序员通过JDBC接口就可以操作不同的数据库，不需要关注底层数据库驱动的安装，从而大大简化和加快了开发过程。

2.2 架构

JDBC接口包括两个层次：

JDBC API：即面向应用的API，这是一个抽象的接口，用于给程序员使用，提供了程序到JDBC管理器的连接。
JDBC Driver API：即面向数据库驱动的API，数据库厂商需要实现这个接口，提供了JDBC管理器到数据库驱动程序的连接。

架构图：
20250701095527-架构

2.3 规范

JDBC相关的接口在java.sql包下。

主要有四个核心对象：

DriverManager类：用于加载驱动，创建连接对象。
Connection接口：表示与数据库创建的连接。
Statement接口：执行数据库SQL语句，并返回相应结果的对象。
ResultSet接口：结果集或一张虚拟表，用于存储表数据的对象。

其中，Statement接口还有两个子接口：

PreparedStatement接口：预编译对象，是Statement接口的子接口，用于解决SQL注入问题。
CallableStatement接口：支持带参数的SQL操作，支持调用存储过程，是PreparedStatement接口的子接口。

3 建立连接

下面的说明以连接MySQL数据库为例。

3.1 准备工作

安装MySQL数据库，保证配置文件包含如下配置：

my.ini

[mysql]
# 客户端默认字符集
default-character-set=utf8mb4
[client]
# 客户端连接服务端时默认字符集
default-character-set=utf8mb4

创建数据库，字符集和配置文件保持一致：

sql

1	CREATE DATABASE `test` CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci';

创建数据表并插入数据，字符集和配置文件保持一致：

sql

CREATE TABLE `user`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `username` varchar(255) COMMENT '用户名称',
  `password` varchar(255) COMMENT '用户密码',
  `create_time` datetime COMMENT '创建时间',
  `update_time` datetime COMMENT '修改时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB COMMENT = '用户表';
INSERT INTO `user` VALUES (1, '张三', '123456', '2020-12-17 16:35:23', '2020-12-17 16:35:23');

使用IDEA创建Java项目，然后在File | Settings | Editor | File Encodings里修改编码：
20250702100301-架构

在Java项目中创建src目录，在src目录创建JDBCTest类，在类中创建main()方法。

3.2 导入Jar包

下载用于连接数据库的驱动Jar包，下载地址：

本地安装的数据库是5.7.40版本，对应下载5.1.49版本的驱动Jar包即可，将下载后的文件解压，目录中的mysql-connector-java-5.1.49.jar文件就是所需的驱动Jar包。

创建lib目录，需要先将下载的mysql-connector-java-5.1.49.jar文件复制到lib目录，然后才能在lib目录右键找到Add as Library...选项，将lib目录设置为Project Library并保存。

3.3 编写代码

示例：

java

public static void main(String[] args) {
    // 加载驱动
    try {
        Class.forName("com.mysql.jdbc.Driver");
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    }
    // 建立连接
    String url = "jdbc:mysql://127.0.0.1:3306/test?useSSL=false&serverTimezone=GMT%2B8";
    String user = "root";
    String password = "123456";
    Connection conn = null;
    try {
        conn = DriverManager.getConnection(url, user, password);
        // 执行增删改查操作
        // ...
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        try {
            // 释放连接
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

步骤：

加载驱动，使用数据库对应的驱动名称注册驱动。
建立连接，通过用户名和密码以及URL协议连接数据库。
执行增删改查，获取查询的结果集和增删改的更新记录数。
释放连接，主动关闭连接资源，避免内存泄漏。

3.4 配置文件

如果不想将配置文件写在代码中，可以在src目录下创建jdbc.properties配置文件：

jdbc.properties

driverClass=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/test?useSSL=false&serverTimezone=GMT%2B8
user=root
password=123456

通过配置文件建立连接：

java

public static void main(String[] args) {
    // 读取配置文件
    Properties pros = new Properties();
    try {
        pros.load(JDBCTest.class.getClassLoader().getResourceAsStream("jdbc.properties"));
    } catch (IOException e) {
        throw new RuntimeException(e);
    }
    // 加载驱动
    try {
        Class.forName(pros.getProperty("driverClass"));
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    }
    // 建立连接
    Connection conn = null;
    String url = pros.getProperty("url");
    String user = pros.getProperty("user");
    String password = pros.getProperty("password");
    try {
        conn = DriverManager.getConnection(url, user, password);
        // 执行增删改查操作
        // ...
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        try {
            // 释放连接
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

4 核心接口

4.1 加载驱动

加载驱动程序的目的是为了注册驱动程序，使得JDBC能够识别并与特定的数据库进行交互。

在加载驱动时，不同数据库对应不同驱动名称：

MySQL驱动：com.mysql.jdbc.Drive
Oracle驱动：oracle.jdbc.driver.OracleDriver
SQLServer驱动：com.microsoft.sqlserver.jdbc.SQLServerDriver
PostgreSQL驱动：org.postgresql.Driver
DB2驱动：com.ibm.db2.jdbc.net.DB2Driver
Sybase驱动：com.sybase.jdbc.SybDriver

从1.6开始，不再需要显式地调用加载驱动，只要在类路径中集成了数据库对应的Jar文件，程序会自动在初始化时注册驱动程序。

4.2 建立连接

Connection接口是JDBC的重要接口，用于建立与数据库的通信通道。

在建立连接时，不同的数据库对应不同URL协议：

MySQL格式：jdbc:mysql://地址或主机名:端口号/数据库名
Oracle格式：jdbc:oracle:thin:@地址或主机名:端口号:数据库名
SQLServer格式：jdbc:sqlserver://地址或主机名:端口号;databaseName=数据库名
PostgreSQL格式：jdbc:postgresql://地址或主机名:端口号/数据库名
DB2格式：jdbc:db2:地址或主机名:端口号/数据库名
Sybase格式：jdbc:sybase:Tds:地址或主机名:端口号/数据库名

连接MySQL数据库：

在使用5.x版本的数据库时，需要在URL协议后追加useSSL=false配置，否则会报错。
在URL协议后追加serverTimezone=GMT%2B8配置将时区设置为东八区，保证时间与当前时间一致。

Connection接口的作用：

数据库交互，Connection接口可以创建Statement对象或者PreparedStatement对象，用于执行SQL语句。
管理事务，Connection接口提供了commit()方法和rollback()方法，用于提交事务和回滚事务。

在连接使用完毕后，需要手动释放资源和关闭连接，避免资源占用导致内存泄漏。

4.3 执行SQL语句

4.3.1 说明

Statement接口用于执行SQL语句并获取执行结果。

获取的结果可以是一个或多个：

查询：返回的是ResultSet结果集。
增删改：返回的是受影响的行数。

使用后需要手动关闭。

4.3.2 增删改查

执行增删改查示例：

java

// 为了代码阅读简洁，直接抛出异常
public static void main(String[] args) throws Exception {
    // 读取配置文件
    Properties pros = new Properties();
    pros.load(JDBCTest.class.getClassLoader().getResourceAsStream("jdbc.properties"));
    // 加载驱动，可以省略
    // 建立连接
    String url = pros.getProperty("url");
    String user = pros.getProperty("user");
    String password = pros.getProperty("password");
    Connection conn = DriverManager.getConnection(url, user, password);
    // 获取执行器
    Statement st = conn.createStatement();
    // 执行新增
    int insert = st.executeUpdate("insert into user values (2, '李四', '123456', now(), now())");
    System.out.println("insert=" + insert);
    // 执行修改
    int update = st.executeUpdate("update user set password = '666666' where id = 2");
    System.out.println("update=" + update);
    // 执行删除
    int delete = st.executeUpdate("delete from user where id = 2");
    System.out.println("delete=" + delete);
    // 执行查询，获取结果集
    ResultSet rs = st.executeQuery("select * from user where id = 1");
    while (rs.next()) {
        System.out.println(rs.getInt("id") + "-" + rs.getString(2) + "-" + rs.getString(3));
    }
    // 关闭结果集
    rs.close();
    // 关闭执行器
    st.close();
    // 释放连接
    conn.close();
}

4.3.3 理解SQL注入问题

正常情况下，如果用户名和密码输入错误，会查不到数据，返回密码错误。

假设输入的密码为123456，拼接后的SQL示例：

sql

1	select * from user where username = '张三' and password = '123456'

在恶意登录的场景下，可以通过SQL注入规避验证用户名和密码的逻辑，保证始终都能查到数据，不会返回密码错误。

假设输入的密码为张三' or '1' = '1，拼接后的SQL示例：

sql

1	select * from user where username = '张三' and password = '张三' or '1' = '1'

4.4 执行预编译SQL语句

4.4.1 说明

PreparedStatement是Statement接口的子接口，用于执行预编译的SQL查询，作用如下：

预编译SQL语句：在创建PreparedStatement时，会根据模板预编译SQL语句，后续只能填充参数，不能拼接。
防止SQL注入：在使用PreparedStatement时，会通过?占位符将传入的参数使用单引号包裹起来，有效防止SQL注入问题。
性能提升：预编译SQL语句在多次执行的情况下可以复用，不必每次重新编译和解析。

使用后需要手动关闭。

4.4.2 增删改查

使用参数新增示例：

java

// 为了代码阅读简洁，直接抛出异常
public static void main(String[] args) throws Exception {
    // 读取配置文件
    Properties pros = new Properties();
    pros.load(JDBCTest.class.getClassLoader().getResourceAsStream("jdbc.properties"));
    // 加载驱动，可以省略
    // 建立连接
    String url = pros.getProperty("url");
    String user = pros.getProperty("user");
    String password = pros.getProperty("password");
    Connection conn = DriverManager.getConnection(url, user, password);
    // 获取预编译执行器
    String sql = "insert into user values (null, ?, ?, now(), now())";
    PreparedStatement ps = conn.prepareStatement(sql);
    // 设置参数
    ps.setString(1, "李四");
    ps.setString(2, "123456");
    // 执行新增
    int insert = ps.executeUpdate();
    System.out.println("insert=" + insert);
    // 关闭执行器
    ps.close();
    // 释放连接
    conn.close();
}

使用参数新增并返回自增主键示例：

java

// 为了代码阅读简洁，直接抛出异常
public static void main(String[] args) throws Exception {
    // 读取配置文件
    Properties pros = new Properties();
    pros.load(JDBCTest.class.getClassLoader().getResourceAsStream("jdbc.properties"));
    // 加载驱动，可以省略
    // 建立连接
    String url = pros.getProperty("url");
    String user = pros.getProperty("user");
    String password = pros.getProperty("password");
    Connection conn = DriverManager.getConnection(url, user, password);
    // 获取预编译执行器，设置需要获取自增主键
    String sql = "insert into user values (null, ?, ?, now(), now())";
    PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
    // 设置参数
    ps.setString(1, "李四");
    ps.setString(2, "123456");
    // 执行新增
    int insert = ps.executeUpdate();
    System.out.println("insert=" + insert);
    // 获取主键结果集
    ResultSet rs = ps.getGeneratedKeys();
    while (rs.next()) {
        System.out.println("id=" + rs.getInt(1));
    }
    // 关闭结果集
    rs.close();
    // 关闭执行器
    ps.close();
    // 释放连接
    conn.close();
}

使用参数修改示例：

java

// 为了代码阅读简洁，直接抛出异常
public static void main(String[] args) throws Exception {
    // 读取配置文件
    Properties pros = new Properties();
    pros.load(JDBCTest.class.getClassLoader().getResourceAsStream("jdbc.properties"));
    // 加载驱动，可以省略
    // 建立连接
    String url = pros.getProperty("url");
    String user = pros.getProperty("user");
    String password = pros.getProperty("password");
    Connection conn = DriverManager.getConnection(url, user, password);
    // 获取预编译执行器
    String sql = "update user set password = ? where id = ?";
    PreparedStatement ps = conn.prepareStatement(sql);
    // 设置参数
    ps.setString(1, "666666");
    ps.setInt(2, 2);
    // 执行修改
    int update = ps.executeUpdate();
    System.out.println("update=" + update);
    // 关闭执行器
    ps.close();
    // 释放连接
    conn.close();
}

使用参数删除示例：

java

// 为了代码阅读简洁，直接抛出异常
public static void main(String[] args) throws Exception {
    // 读取配置文件
    Properties pros = new Properties();
    pros.load(JDBCTest.class.getClassLoader().getResourceAsStream("jdbc.properties"));
    // 加载驱动，可以省略
    // 建立连接
    String url = pros.getProperty("url");
    String user = pros.getProperty("user");
    String password = pros.getProperty("password");
    Connection conn = DriverManager.getConnection(url, user, password);
    // 获取预编译执行器
    String sql = "delete from user where id = ?";
    PreparedStatement ps = conn.prepareStatement(sql);
    // 设置参数
    ps.setInt(1, 2);
    // 执行删除
    int delete = ps.executeUpdate();
    System.out.println("delete=" + delete);
    // 关闭执行器
    ps.close();
    // 释放连接
    conn.close();
}

使用参数查询示例：

java

// 为了代码阅读简洁，直接抛出异常
public static void main(String[] args) throws Exception {
    // 读取配置文件
    Properties pros = new Properties();
    pros.load(JDBCTest.class.getClassLoader().getResourceAsStream("jdbc.properties"));
    // 加载驱动，可以省略
    // 建立连接
    String url = pros.getProperty("url");
    String user = pros.getProperty("user");
    String password = pros.getProperty("password");
    Connection conn = DriverManager.getConnection(url, user, password);
    // 获取预编译执行器
    String sql = "select * from user where id = ?";
    PreparedStatement ps = conn.prepareStatement(sql);
    // 设置参数
    ps.setInt(1, 1);
    // 执行查询，获取结果集
    ResultSet rs = ps.executeQuery();
    while (rs.next()) {
        System.out.println(rs.getInt("id") + "-" + rs.getString(2) + "-" + rs.getString(3));
    }
    // 关闭结果集
    rs.close();
    // 关闭执行器
    ps.close();
    // 释放连接
    conn.close();
}

4.4.3 解决SQL注入问题

假设输入的密码为张三' or '1' = '1，经过预编译后的SQL示例：

sql

1	select * from user where username = '张三' and password = '张三\' or \'1\' = \'1'

在替换参数前会自动对单引号进行转义，从而解决SQL注入的问题。

4.5 获取结果集

ResultSet接口用于表示从数据库中执行查询语句所返回的结果集。

使用后需要手动关闭。

5 使用事务

5.1 基础知识

事务有四种特性：

原子性（Atomicity）：指事务包含的所有操作要么全部成功提交，要么全部失败回滚。
一致性（Consistency）：指事务必须使数据库的数据和资源从一个一致性状态变换到另一个一致性状态。
隔离性（Isolation）：指当多个用户并发访问数据库并且操作同一张表时，数据库为每一个用户开启事务，不会被其他事务的操作干扰，多个并发事务之间相互隔离。
持久性（Durability）：指一个事务一旦被提交了，对数据的改变就是永久性的，哪怕数据库遇到故障也不会丢失提交的改动。

涉及事务的几个术语：

保存点（Savepoint）：指在事务执行前或者事务执行后，数据在数据库里的存储情况，有时也称为状态。
回退（Rollback）：指撤销事务的操作，事务期间执行的操作都将失效，事务恢复到上个状态。
提交（Commit）：指提交事务的操作，事务期间执行的操作全部生效，事务进入新的状态。

5.2 操作事务

常用方法：

java

// 查询事务隔离级别
int getTransactionIsolation();
// 设置事务隔离级别
void setTransactionIsolation(int level);
// 查询自动提交状态
boolean getAutoCommit();
// 设置自动提交状态
void setAutoCommit(boolean autoCommit);
// 创建还原点
Savepoint setSavepoint();
// 创建指定名称的还原点
Savepoint setSavepoint(String name);
// 删除指定名称的还原点
void releaseSavepoint(Savepoint savepoint);
// 提交
void commit();
// 回滚
void rollback();
// 回滚到指定名称的还原点
void rollback(Savepoint savepoint);

示例：

java

// 为了代码阅读简洁，直接抛出异常
public static void main(String[] args) throws Exception {
    // 读取配置文件
    Properties pros = new Properties();
    pros.load(JDBCTest.class.getClassLoader().getResourceAsStream("jdbc.properties"));
    // 加载驱动，可以省略
    // 建立连接
    String url = pros.getProperty("url");
    String user = pros.getProperty("user");
    String password = pros.getProperty("password");
    Connection conn = DriverManager.getConnection(url, user, password);
    // 取消自动提交
    conn.setAutoCommit(false);
    // 获取预编译执行器
    String sql = "update user set password = ? where id = ?";
    PreparedStatement ps = conn.prepareStatement(sql);
    // 设置参数
    ps.setString(1, "666666");
    ps.setInt(2, 2);
    // 执行修改
    int update = ps.executeUpdate();
    System.out.println("update=" + update);
    if (update == 1) {
        // 提交事务
        conn.commit();
    } else {
        // 回滚事务
        conn.rollback();
    }
    // 关闭执行器
    ps.close();
    // 释放连接
    conn.close();
}