Java 数据库连接基础实践
一. JDBC 介绍
JDBC 全称 Java 数据库连接，是 Java DataBase Connectivity 的简称。是一套用来规范客户端程序访问数据库的应用程序接口。抽象了对于数据库操作的一些列方法。JDBC 大多数情况下是面向关系型数据库的。
JDBC API 主要位于 JDK 中的 java.sql 包中（之后扩展的内容位于 javax.sql 包中），主要包括：
- DriverManager
负责加载各种不同驱动程序（Driver），并根据不同的请求，向调用者返回相应的数据库连接（Connection）。 - Driver
驱动程序，会将自身加载到DriverManager中去，并处理相应的请求并返回相应的数据库连接（Connection）。 - Connection
数据库连接，负责与进行数据库间通讯，SQL执行以及事务处理都是在某个特定Connection环境中进行的。可以产生用以执行SQL的Statement。 - Statement
用以执行SQL查询和更新（针对静态SQL语句和单次执行）。 - PreparedStatement
用以执行包含动态参数的SQL查询和更新（在服务器端编译，允许重复执行以提高效率）。 - CallableStatement
用以调用数据库中的存储过程。 - SQLException
代表在数据库连接的建立和关闭和SQL语句的执行过程中发生了例外情况（即错误）。
二. JDBC 基本实践
以 IDEA 为开发工具，使用 Mysql 8.0 与 Java 8 进行实践。
1. 在 IDEA 中导入 Mysql 的驱动包（不使用 maven）
前往 Mysql 官网可下载最新版本的 JDBC。
注意
Mysql 8.0 及其以上版本必须使用 8.0 版本及其以上的驱动包。
- 下载后，点击 IDEA 的 Project Structure。
- 在 Project Structure 中点击 Libraries。可以看见在右侧栏顶部有一个加号。
- 点击加号之后选择 Java。
-
选择下载好的数据库驱动。
图4. 选择下载好的数据库驱动 -
可以看见右侧两边栏中都已经有了我们的数据库驱动包，此时再点击 Project Structure 的 OK。
图5. 添加数据库驱动包完成 -
添加完成后项目下的扩展库中将会存在我们导入的驱动包。
2. 创建测试数据库、数据表
在 Mysql 数据库中创建测试数据 testDB（名称可自定）。执行以下 sql 语句创建数据表 user 与 并填入数据。
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`age` int DEFAULT NULL,
`address` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
LOCK TABLES `user` WRITE;
INSERT INTO `user` VALUES
(1,'刘一',18,'成都'),
(2,'陈二',20,'哈尔滨'),
(3,'张三',19,'上海'),
(4,'李四',20,'海南'),
(5,'王五',18,'广州');
3. 使用 JDBC 查找 Mysql 数据库
import java.sql.*;
public class Main {
// Mysql 数据库的 url，可以写 127.0.0.1:3306 或 loaclhost 表示本机。
// 这里关联了使用的数据库名，使用的是数据库 testDB。
// 有事出现 URL 错误可能是时区的问题，请仔细检查 serverTimezone 是否填写规范。
private final static String URL = "jdbc:mysql://127.0.0.1:3306/testDB?serverTimezone=GMT%2B8&characterEncoding=UTF-8&userSSL=false";
// 登录数据库的账号与密码信息.
private final static String USER = "root";
private final static String PASSWORD = "1024201";
public static void main(String[] args) throws Exception {
// 加载数据库驱动。
// 注意！"com.mysql.cj.jdbc.Driver" 是 Mysql 8.0 及其以上的写法
// 低版本 Mysql 请使用 "com.mysql.jdbc.Driver"
Class.forName("com.mysql.cj.jdbc.Driver");
// 创建数据库连接对象。若连接成功 Connection 即为数据库的抽象。
Connection connection = DriverManager.getConnection(URL, "root", "1244875112");
// Statement 对象是执行 sql 的对象。
Statement stmt = connection.createStatement();
// 将要由 Statement 对象执行的 sql 语句
String sql = "SELECT * FROM user";
// ResultSet 是执行 sql 语句的结果集
ResultSet rs = stmt.executeQuery(sql);
// 遍历结果集，格式化输出
while (rs.next()) {
System.out.println(
"id:" + rs.getString("id") +
"\t姓名:" + rs.getString("name") +
"\t年龄:" + rs.getInt("age") +
"\t性别:" + rs.getString("address"));
}
// 释放资源
rs.close();
stmt.close();
connection.close();
}
}
运行结果如下
id:1 姓名:刘一 年龄:18 性别:成都
id:2 姓名:陈二 年龄:20 性别:哈尔滨
id:3 姓名:张三 年龄:19 性别:上海
id:4 姓名:李四 年龄:20 性别:海南
id:5 姓名:王五 年龄:18 性别:广州
可见，数据表 user 中的记录都被查出来了。
三. JDBC 中的类与对象
1. Connection
Connection 是对数据库的抽象，这意味着所有对数据库的操作其都有提供方法来进行抽象，包括事务提交，事务回滚，等。我们在 IDEA 的自动语法提示中也能洞见。
2. Statement 与 PreparedStatement
Statement 与 PreparedStatement 都是对 SQL 执行的抽象（下文将会阐述其区别）。这意味着其应该能够提供一系列的方法，让我们可以顺利的使用所有合法的 SQL 语句。
我们可以看到 Statement 对象的 SQL 执行方法有多种。这些方法应用情况如下
| 方法名 | 作用 | 备注 |
|---|---|---|
| execute | 执行 SQL 语句 | 多重形参重载，返回布尔值 |
| executeQuery | 执行查询语句 | 返回查询结果集 |
| executeUpdate | 执行会改变数据表的 SQL 操作，例如增、删、改 | 返回受影响的行数 |
3. ResultSet
ResultSet 是对 SQL 执行结果集的抽象。这意味着其应该能够展现任意 SQL 语句执行的结果。ResultSet 支持多种结果集操作来方便我们获取结果。
我们可以看到一系列的 get 方法。这是为了从结果集中获取相应类型的值。下表展示出了数据库类型与 Java 类型的映射关系，可帮助你更好的使用 ResulSet 中的 get 系列方法。
| SQL 类型 | Java 类型 | ResultSet 方法 |
|---|---|---|
| Object | getObject | |
| CHAR | java.lang.String | getString |
| VARCHAR | java.lang.String | getString |
| LONGVARCHAR | java.lang.String | getString |
| NUMERIC | java.math.BigDecimal | getBigDecimal |
| DECIMAL | java.math.BigDecimal | getBigDecimal |
| BIT | boolean | getBoolean |
| TINYINT | byte | getByte |
| SMALLINT | short | getShort |
| INTEGER | int | getInt |
| BIGINT | long | getLong |
| REAL | float | getFloat |
| FLOAT | double | getDouble |
| DOUBLE | double | getDouble |
| BINARY | byte[] | getBytes |
| VARBINARY | byte[] | getBytes |
| LONGVARBINARY | byte[] | getBytes |
| DATE | java.sql.Date | getDate |
| TIME | java.sql.Time | getTime |
| TIMESTAMP | java.sql.Timestamp | getTimestamp |
| BLOB | java.sql.Blob | getBlob |
| CLOB | java.sql.Clob | getClob |
| Array | java.sql.Array | getArray |
| REF | java.sql.Ref | getRef |
| Struct | java.sql.Struct |
出了获取特定格式的结果，我们还可以看到在 ResultSet 中提供了一系列的遍历操作方法。其操作类似于迭代器。如果不知道什么是迭代器也可以将其理解为存在一个指针，在结果集中移动并获取结果。
| 方法名 | 作用 |
|---|---|
| next | 移动到并获取下一个结果 |
| previous | 移动到并获取前一个结果 |
| beforeFirst | 移动到获取第一个结果 |
| afterLast | 移动到并获取最后一个结果 |
| absolute | 获取特定位置的结果 |
四. SQL 注入与 PreparedStatement
1. 什么是 SQL 注入，手写 SQL 注入
SQL 注入，什么是 SQL 注入呢？首先，我们可以在程序中发现，在使用 Statement 执行 SQL 的时候，SQL 语句是以字符串的形式传入执行的。那么我们可以想象，如果使用 Statement 来创建一个删除操作的业务方法，方法如下。
...
// 仅举例使用，该种写法非常低效
public int deleteByName(String name) throws Exception {
Connection connection = DriverManager.getConnection(URL, "root", "1024201");
Statement stmt = connection.createStatement();
// 拼接 SQL 语句
String sql = "delete from user where name = '"+ name + "'";
// 执行 SQL 语句
int result = stmt.executeUpdate(sql);
// 关闭资源
stmt.close();
connection.close();
return result;
}
在上述方法中，我们可以发现，所需的名字参数传入后与 SQL 语句进行拼接，然后再执行。正是在此处，留下来巨大的安全隐患！正常调用的情况下，deleteByName 可以正常运行。例如输入的参数为张三，则 SQL 语句为：
delete from user where name = '张三'
但是如果我输入到 deleteByName 中的参数为："张三' or name !='ooo"，就会发现此时拼接起来的 SQL 语句变成了:
delete from user where name = '张三' or name != 'ooo'
这意味着，若执行该条 SQL，user 表中所有名字不等于 ‘ooo’ 的记录都将被删除！这就是 SQL 注入！
方法给程序员调用时，程序员当然不会恶意使用，触发 SQL 注入。但如果这个方法的参数是从用户输入来进行调用的，那就不能保证会不会触发 SQL 注入了。
2. 使用 PreparedStatement 避免 SQL 注入
PreparedStatement 也就是 预编译 SQL 可以有效的避免 SQL 注入。接下来介绍 PreparedStatement 的使用。
...
Connection connection = DriverManager.getConnection(URL, "root", "1024201");
// SQL 语句。注意其中 "?" 是占位符
String sql = "delete from user where name = ?";
//创建 PreparedStatement 对象
PreparedStatement stmt = connection.prepareStatement(sql);
// 设置第一个占位符的参数值为 "张三"
stmt.setString(1, "张三");
// 执行 SQL 语句
int result = stmt.executeUpdate();
// 关闭资源
stmt.close();
connection.close();
注意
这里有一个坑。占位符直接写问号就行了，不需要加单引号！
此外 IDEA 还会在使用 setString 方法时检查占位符的位置是否对应。
PreparedStatement 的执行原理非常的简单。首先我们可以看到，与 Statement 的创建不同，PreparedStatement 的创建需要先输入 SQL 语句。而输入的 SQL 语句并不是见的 SQL 语句，其包含了问号占位符。张占位符相当于要拼接的参数。
接着对 PreparedStatement 对象调用 setString 方法。setString(1, "张三") 代表着，设置第 1 个占位符的位置值为字符串 “张三”。
注意
这里的字符串参数是 “张三” 不是 “‘张三’”，没有单引号包裹！
通过这种方式，也可以执行 SQL，但是 PreparedStatemnt 防止了 SQL 注入！我们可以尝试以下代码，可以发现已经不能进行 SQL 注入。
String sql = "delete from user where name = ?";
PreparedStatement stmt = connection.prepareStatement(sql);
stmt.setString(1, "张三 or name != 'ooo'");
int result = stmt.executeUpdate();
3. PreparedStatement 中的占位符设置值方法及其使用
上例中我们使用了 setString 方法来设置特定位置的占位符对应的值。在 PreparedStatement 中还有多种类型的设置方法，这里列举一些常用的方法。
| 方法名 | 说明 | 备注 |
|---|---|---|
| setString | 设置 String 类型到占位符 | - |
| setInt | 设置 int 类型到占位符 | - |
| setDate | 设置 Date 类型到占位符 | 这个 Date 是 java.sql.Date |
| setTimestamp | 设置 Timestamp 类型到占位符 | Timestamp 是时间戳，也是java.sql 包中的类 |
| setLong | 设置 long 类型到占位符 | - |
| … | … | … |
接下来还提供一个多占位符的示例，方便大家学习。
// SQL 语句。注意其中 "?" 是占位符
String sql =
"insert into user(id, name, age, address) values(?,?,?,?)";
//创建 PreparedStatement 对象
PreparedStatement stmt = connection.prepareStatement(sql);
// 为了方便查看，设置了缩进。从第一个占位符一直设置到第四个占位符
stmt.setInt( 1, 6 );
stmt.setString( 2, "超六" );
stmt.setInt( 3, 20 );
stmt.setString( 4, "北京" );
// 执行 SQL 语句
int result = stmt.executeUpdate();
五. 事务操作
知识补充
说到事务， ACID 是老生常谈了。这里简单复习补充。
- 原子性（Atomicity，或称不可分割性）
事务的执行要么全部完成，要么全都不不完成。其无法拆分为一半完成一半不完成。- 一致性（Consistency）
在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。- 隔离性（Isolation，又称独立性）
数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。- 持久性（Durability）
Durability（持久性）：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。
1. JDBC 的事务操作
在 JDBC 编程中，要对事务进行操作，需要通过 Connection 对象来进行操作。
默认情况下事务是自动提交的，相当于有以下语句执行。
connection.setAutoCommit(true);
若要启动手动事务提交则设置为 false 即可
connection.setAutoCommit(false);
启动后我们可以对以下代码进行测试：
...
Connection connection = DriverManager.getConnection(URL, "root", "1244875112");
// 设置为手动提交事务
connection.setAutoCommit(false);
Statement stmt = null;
// 第一段执行的 SQL
String sql1 = "delete from user where id = 1";
stmt = connection.createStatement();
stmt.executeUpdate(sql1);
// 第二段执行的 SQL
String sql2 = "insert into user(id, name, age, address) values(10, '黄七', 20, '武汉')";
stmt = connection.createStatement();
stmt.executeUpdate(sql2);
// 手动提交事务
connection.commit();
// 关闭资源
stmt.close();
connection.close();
运行结果就是两段 SQL 顺利执行。注意，如果开启了手动提交事务，那么就一定要在执行完的 SQL 最后执行 Connection 对象的 commit 方法，这样修改才会持久化。
如果中间程序出现错误，事务会自动回滚，调用 Connection 的 rollback 方法。
例如下列程序，在中间计算 1/0 产生错误，事务被自动回滚，此时第一段和第二段 SQL 都不会持久化到数据库，即便出现错误的位置在第一段 SQL 执行之后。
...
Connection connection = DriverManager.getConnection(URL, "root", "1244875112");
// 设置为手动提交事务
connection.setAutoCommit(false);
Statement stmt = null;
// 第一段执行的 SQL
String sql1 = "delete from user where id = 1";
stmt = connection.createStatement();
stmt.executeUpdate(sql1);
int i = 1/0;
// 第二段执行的 SQL
String sql2 = "insert into user(id, name, age, address) values(10, '黄七', 20, '武汉')";
stmt = connection.createStatement();
stmt.executeUpdate(sql2);
// 手动提交事务
connection.commit();
// 关闭资源
stmt.close();
connection.close();
当然，必要的时候你也可以通过调用 rollback 方法手动滚回事务。
2. 设置事务保存点与回滚到保存点
在事务中设置保存点，使得我们滚回时可以滚回到特定的位置，而不是全部滚回。
接下来演示在 JDBC 中使用保存点与保存点回滚。
...
Connection connection = DriverManager.getConnection(URL, "root", "1244875112");
connection.setAutoCommit(false);
Statement stmt = null;
// 第一段 SQL 执行
String sql1 = "delete from user where id = 4";
stmt = connection.createStatement();
stmt.executeUpdate(sql1);
// 创建保存点 savepoint1
Savepoint savepoint1 = connection.setSavepoint("sp1");
// 第二段 SQL 执行
String sql2 = "delete from user where id = 5";
stmt = connection.createStatement();
stmt.executeUpdate(sql2);
// 创建保存点 savepoint2
Savepoint savepoint2 = connection.setSavepoint("sp2");
// 第三段 SQL 执行
String sql3 = "delete from user where id = 6";
stmt = connection.createStatement();
stmt.executeUpdate(sql3);
// 滚回 savepointer1 保存点
connection.rollback(savepoint1);
//提交事务
connection.commit();
// 释放资源
stmt.close();
connection.close();
执行结果显示，只有第一段 SQL 持久化了，后面两段 SQL 都未持久化。
注意
滚回保存点后的事务并未结束，只有遇到
commite()或者全滚回rollback()事务才算结束。
1084
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
