MySQL_06_JDBC


前面介绍了数据库的相关操作,但那仅仅是在数据库客户端的操作。而程序本质上是对数据的处理,那么程序怎么操作数据库呢?这就需要数据库的接口了,对于Java语言来说,则需要用到JDBC。

1. 概述

JDBC是Java DataBase Connectivity的缩写,即Java语言连接数据库,本质上是SUN公司定制的一套接口(interface),在java.sql.*这个包下,有很多个关于数据库的接口。回顾Java中的接口,接口都有调用者和实现者,面向接口调用、面向接口写实现类,这都属于面向接口编程。

面向接口编程的作用就是解耦合,降低程度的耦合度,提高程序的扩展力。调用者只需要调用接口,使用接口提供的功能,不需要在意接口是如何实现的;而实现者则只需要实现这个接口即可。

mysql_34.png (384×839) (gitee.io)

那么为什么制定这个JDBC接口呢?

因为每一个数据库的底层实现原理都不一样,比如MySQL、Oracle、sqlserver等等,每一个数据库都有自己独特的实现原理。那么对于程序员来说,如果在程序中操作不同的数据库,就需要编写不同的程序(涉及到底层特点),这对程序员来说是非常冗杂的。

所以为了方便程序员操作数据库,SUN公司制定了JDBC接口,程序员只需要调用接口即可。至于接口的实现类,则由不同的数据库厂商来实现

这里面向接口编程即前面提到的多态,父类型引用指向子类型对象,程序员不需要管子类型到底是什么对象,只需要知道父类型提供的方法即可。

但是虽然程序员不需要管接口的实现类如何实现的,但是这个实现类的class文件必须有,必须能够被 JVM 调用到。

mysql_35.png (1436×632) (gitee.io)

如上图所示,右侧不同的厂商对接口进行了实现,即实现类。程序员在调用的时候,直接利用多态,对JDBC接口创建对应的父类型引用,指向右侧的子类型实现类对象即可。注意,这里的接口一定要回顾前面面向对象中的接口。

左侧红框中的程序是程序员编写的,中间黑框中的接口是SUN公司提供的,右侧红框中的实现类则是sql厂商实现的,那么如果想要完整的连接数据库,则需要在对应的官网上下载对应的实现类(.jar包,也被称为驱动)。

驱动:所有的数据库驱动都是以jar包的形式存在,jar包当中有很多.class文件,这些class文件就是对JDBC接口的实现。驱动不是SUN公司提供的,是各大数据库厂家提供的,下载驱动需要到官网下载。

那么数据库厂商为什么要实现这个接口呢?假如不实现的话,程序员就需要手动实现这个接口,这对程序员来说很庞杂,那么程序员就很有可能不采用这种数据库,对数据库来说是损失了客户。

2. 简单模拟JDBC

模拟这个需要三个角色,程序员的程序、SUN公司的接口和数据库厂商的实现类。

SUN公司定义的接口如下:

1
2
3
4
5
// SUN公司定义的JDBC接口
public interface jdbcIntergace {

public abstract void sqlConnection();
}

数据库厂商的实现类如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
// mysql
public class mysql implements jdbcIntergace {

public void sqlConnection(){
System.out.println("mysql 关于jdbc接口的实现类");
System.out.println("开始连接数据库,底层执行mysql数据库相关的函数");
}
}


// oracle
public class oracle implements jdbcIntergace {

public void sqlConnection(){
System.out.println("oracle 关于jdbc接口的实现类");
System.out.println("开始连接数据库,底层执行oracle数据库相关的函数");
}
}


// sqlserver
public class sqlserver implements jdbcIntergace {

public void sqlConnection(){
System.out.println("sqlserver 关于jdbc接口的实现类");
System.out.println("开始连接数据库,底层执行sqlserver数据库相关的函数");
}
}

程序员连接数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
public class javaProgramer {

public static void main(String[] args){

// 连接不同的数据库,只需要创建不同的子类型对象即可
// jdbcIntergace jdbci = new mysql();
// jdbcIntergace jdbci = new oracle();
jdbcIntergace jdbci = new sqlserver();

// 方法名永远不会变,SUN公司已经制定好了。
jdbci.sqlConnection();
}
}

为了更方便,可以采用反射机制中的资源绑定,只需要修改配置文件,而不需要重新编译,这里不再赘述。JDBC本质上就是一套接口

3. 准备工作

在JDBC开发前需要提前下载好对应数据库的 jar包,那么下载好后的jar包应该放到哪里呢?程序运行的时候怎么找到这些文件呢?

回顾 SUN 公司提供的一些工具类,这些也是class文件,系统是怎么找到这些文件的呢?因为设置好了环境变量,程序在运行的时候会在环境变量的路径中查找所需要的文件。所以,需要把jar包的绝对路径添加到环境变量ClassPath中。注意Path和ClassPath的区别,Path是系统级别的,而ClassPath则是Java级别的,Java找类都是在ClassPath中找。这样,程序在运行的时候会自动到环境变量的路径中查找所需的文件,即找到JDBC接口的实现类。

4. JDBC编程六步(重点)

逻辑上说,JDBC编程主要有以下几个步骤。

  1. 注册驱动

    作用是告诉Java程序,即将要连接的是哪个品牌的数据库

  2. 获取连接

    表示JVM的进程和数据库进程之间的通道打开了,这是属于进程之间的通信,是重量级的,使用完之后一定要关闭通道。

  3. 获取数据库操作对象

    用于执行SQL语句的对象

  4. 执行SQL语句

    执行SQL语句,主要是DQL和DML

  5. 处理查询结果集

    对DQL查询的结果进行处理,即处理数据,如果上述没有执行DQL,则不需要这一步。

  6. 释放资源

    使用完资源之后一定要关闭资源,Java和数据库属于进程间的通信,开启之后一定要关闭。

5. JDBC编程实践

JDNC相关的接口和类都在java.sql.*包下。

5.1 注册驱动

JDBC和驱动相关的类是java.sql.DriverManager类,接口是java.sql.Driver。前者是驱动管理器,用于管理各种驱动;后者是驱动接口,不同的数据库厂商实现了对应的驱动实现类。

注册驱动,需要用到DriverManager类中的registerDriver(Driver driver)静态方法,该方法需要传入一个驱动器参数。

1
static void registerDriver(Driver driver) throws SQLException{}

这个驱动器参数指的是各个数据库厂商对接口的实现类。比如MySQL的实现类,下载Jar包:mysql-connector-java.8.0.27.jar,将其绝对路径添加到ClassPath中。(将Jar包解压缩,如下图所示,该类的class文件在下面的包下,注意创建类对象的时候需要写类的全称;查看源码,该实现类抛出异常,所以调用的时候需要处理异常。)

mysql_36.png (839×194) (gitee.io)

mysql_37.png (843×347) (gitee.io)

简单例子如下所示:

1
2
3
4
5
6
7
8
9
10
11
12
import java.sql.*;

public class JDBCTest01 {

public static void main(String[] args) throws SQLException {

// 注册驱动(相当于设置一下环境)
Driver driver = new com.mysql.jdbc.Driver();
DriverManager.registerDriver(driver);

}
}

5.1.1 注册驱动第二种方法

查看源码就会发现,数据库厂商实现类中包含一段静态代码块,该代码块就是注册驱动。所以可以直接执行该静态代码块即可,而不需要上述创建对象的形式。执行静态代码块有一种形式,就是反射机制Class.forName("类名"),即类加载,执行静态代码块。(这种方法比较常用,因为参数是字符串,可以写到配置文件中,这样,更改参数的话,只需要重写修改配置文件即可,而不需要修改源码再编译)。

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

5.2 获取连接

获取连接指的是连接数据库,所以需要指明要连接数据库的IP地址、用户、密码以及具体的数据库。获取连接所用的方法也是在DriverManager类中。

1
2
3
4
5
6
7
8
public static Connection getConnection(String url, String user, String password) throws SQLException{}

// 其中
// url - a database url of the form jdbc:subprotocol:subname
// user - the database user on whose behalf the connection is beingmade
// password - the user's password

// url 类似这种:"jdbc:mysql://ip:port/数据库名";

简单例子如下所示:

1
2
3
4
5
6
7
8
9
10
11
String url = "jdbc:mysql://IP地址:端口号/数据库名称";
// jdbc:mysql 属于mysql的协议
// IP地址:端口号 属于IP地址和端口
// 数据库名称 属于要连接的数据库

String user = "用户名";
String password = "密码";

// 获得一个连接对象(相当于,在上面的环境下,连接某个地址上的数据库)
Connection conn = DriverManager.getConnection(url, user, password);
System.out.println("数据库连接对象:" + conn);

5.2.1 配置文件形式获取连接

使用资源绑定器绑定属性文件。(建议采用这种方法,实际开发中不建议把连接数据库的信息写死到Java程序中

1
2
3
4
5
6
7
8
9
10
import java.util.ResourceBundle;

ResourceBundle bundle = ResourceBundle.getBundle("jdbc");
String driver = ResourceBundle.getString("driver");
String url = ResourceBundle.getString("url");
String user = ResourceBundle.getString("user");
String password = ResourceBundle.getString("password");

Class.forName(driver);
Connection conn = DriverManager.getConnection(url, user, password);

资源绑定器文件内容如下所示:

mysql_38.png (368×129) (gitee.io)

5.3 获取数据库操作对象

经过上一步通道打开之后,本机和数据库所在主机之间的连接已经形成,接下来就是创建数据库操作对象了,用于后续的数据库操作,数据库操作对象是在通道的基础上创建的,所以需要用到上面的通道对象

1
Statement createStatement throws SQLException(){}

简单例子如下所示:

1
Statement stmt = conn.createStatement();

5.4 执行SQL语句

SQL语句其实分为两大类,一类是DQL(对数据库数据表本身没有改动),一类是DML(对数据库数据表有改动)。因此数据库操作对象主要提供了两种执行SQL语句的方法。

1
2
3
4
5
6
// 执行给定的SQL语句,该SQL语句可能为 insert、update和delete语句,或者不返回任何内容的SQL语句。
// 返回值的意义是:影响数据库中的记录条数。
int executeUpdate(String sql){};

// 执行给定的SQL语句,该语句返回单个ResultSet对象。
ResultSet executeQuery(String sql){};

其实SQL语句就是标准的SQL语句,只不过是以字符串的形式出现,并且不需要写分号。简单例子如下所示:

1
2
3
String sql = "insert into t_student values(7, \"ceshi\", 25 ,\"ceshi@mysql.com\")";
int count = stmt.executeUpdate(sql);
System.out.println(count == 1 ? "插入成功" : "插入失败");

注意,最终形成的SQL语句,其中的字符串必须用双引号或单引号,采用变量的形式如下所示:

1
2
3
4
5
6
7
8
9
10
11
String user1 = map.get("user");
String password1 = map.get("password");

String sql = "select password from t_user where login=" + "\"" + user1 + "\"";
// String sql = "select * from t_user where login=" + "\"" + user1 + "\"" + "and password=" + "\"" + password1 + "\"";

rs = stmt.executeQuery(sql);

if(rs.next()){
result = true;
}

上面的user1虽然是字符串,但是拼接后的sql语句,实际上没有双引号,所以,必须手动在user1两侧加上双引号。

5.5 处理查询结果集

上面DQL语句的执行结果,返回了ResultSet对象,该对象提供了一些方法,用于处理返回结果。ResultSet对象和集合类似但又不完全相同,具有自己的一套方法。常用方法如下所示:

方法名 描述
boolean next() 移动光标,类似迭代器。如果下一个位置有数据,返回true,否则返回false
String getString(int columnIndex) 将光标所在行,指定column列的数据取出(即当前行的指定列),以字符串形式取出。注意,下标从0开始。
String getString(String columnLabel) 同上,只不过参数不是列标,而是字段名称,注意,是查询结果的字段的名称,如果起了别名之后,字段必须是别名。(建议采用)
int getInt() 同上
double getDouble 同上

简单例子如下所示:

1
2
3
4
5
6
7
8
9
String sql = "select * from t_jiaoben";
rs = stmt.executeQuery(sql);

while(rs.next()){

System.out.println("no: " + rs.getString("no") + ";" +
"name: " + rs.getString("name") + ";" +
"create_time" + rs.getString("create_time"));
}

5.6 释放资源

注意,为了保证资源一定释放,要在finally语句块中关闭资源,并且要遵循从小到大一次关闭:结果集对象,数据库操作对象,连接通道对象依次关闭。

注意要分别处理异常,否则如果开始的对象关闭出现异常,那么后面的对象就不会关闭了。 方法如下所示:

1
2
3
4
5
6
7
8
// 数据库操作对象的释放资源方法
void close() throws SQLException(){}

// 连接通道的释放资源方法
void close() throws SQLException(){}

// 查询结果集的释放资源方法
void close() throws SQLException(){}

简单例子如下所示(注意处理异常):

1
2
3
4
5
6
7
8
9
10
11
if(rs != null){
rs.close();
}

if(stmt != null){
stmt.close();
}

if(conn != null){
conn.close();
}

简单例子如下所示:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
import java.sql.*;

public class TestJDBC01{

public static void main(String[] args){

Connection conn = null;
Statement stmt = null;

try{
Driver driver = new com.mysql.jdbc.Driver();

DriverManager.registerDriver(driver);

String url = "jdbc:mysql://IP地址:端口号/数据库名称";
String user = "用户名";
String password = "密码";

conn = DriverManager.getConnection(url, user, password);

stmt = conn.createStatement();

String sql = "insert into t_student values(8, \"yaoer\", 26, \"yaoer@mysql.com\")";

// String sql = "delete from t_student where no is NULL";

int count = stmt.executeUpdate(sql);

System.out.println(count);

}catch(SQLException e){
System.out.println(e);

}finally{

if(stmt != null){

try{

stmt.close();

}catch(SQLException e){
System.out.println(e);
}

}

if(conn != null){

try{

conn.close();

}catch(SQLException e){
System.out.println(e);
}

}

}

}
}

5.7 总结

可以看到,上面的六步可以总结成:设置好环境,在该环境下连接指定数据库,然后获取数据库的操作对象,之后进行数据库操作。

6. IDEA搭配JDBC

前面提到过,采用文本文件形式编译Java的话,需要将JDBC的Jar包放到ClassPath中,但是采用IDEA工具的配置不同,如下所示

首先对模块进行设置,将Jar包导入到模块中即可,点击应用,点击ok,可以看到左侧外部库中导入了Jar包。

mysql_39.png (563×852) (gitee.io)

mysql_40.png (997×845) (gitee.io)

mysql_41.png (1487×818) (gitee.io)

7. 实践:模拟用户登录

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
package jdbc;

import java.sql.*;
import java.util.HashMap;
import java.util.Map;
import java.util.Scanner;

/**
*
* 实现功能:
* 1. 需求:模拟用户登录功能的实现
* 2. 业务描述:
* 程序运行的时候,提供一个输入的入口,可以让用户输入用户名和密码;
* 用户输入用户名和密码之后,提交信息,Java程序收集到用户信息;
* 然后Java程序连接数据库验证用户名和密码是否合法。
* 合法:显示登陆成功!
* 不合法:显示登陆失败!
* 3. 数据的准备:
* 在实际开发中,表的设计会使用专业的建模工具,这里推荐使用:PowerDesigner,使用该工具来进行数据库表的设计。
* 也可以是使用Navicat,或者直接在命令行建表。
*/
public class JDBCTest03 {

public static void main(String[] args) {

Map<String, String> map = initUI();

System.out.println(login(map) ? "登陆成功" : "登陆失败");
}

/**
* 初始化用户界面
* @return 用户输入的用户名和密码等登陆信息。
*/
public static Map<String, String> initUI(){

Scanner scanner = new Scanner(System.in);
System.out.print("请输入登陆用户名:");
String user = scanner.nextLine();
System.out.print("请输入登陆密码:");
String password = scanner.nextLine();

Map<String, String> map = new HashMap<>();
map.put("user", user.trim());
map.put("password", password.trim());

return map;
}

/**
* 用户登录
* @param map 用户登录信息
* @return false表示登录失败,true表示登录成功
*/
public static boolean login(Map<String, String> map){

// JDBC代码六步
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
boolean result = false;

try{
DriverManager.registerDriver(new com.mysql.jdbc.Driver());

String url = "jdbc:mysql://IP地址:端口号/数据库名";
String user = "用户名";
String password = "密码";

conn = DriverManager.getConnection(url, user, password);
stmt = conn.createStatement();

String user1 = map.get("user");
String password1 = map.get("password");

// String sql = "select password from t_user where login=" + "\"" + user1 + "\"";
//
// rs = stmt.executeQuery(sql);
//
// while(rs.next()){
// String passwd = rs.getString("password");
// if(password1.equals(passwd)){
// result = true;
// break;
// }
// }

String sql = "select * from t_user where login=" + "\"" + user1 + "\"" + "and password=" + "\"" + password1 + "\"";
rs = stmt.executeQuery(sql);

if(rs.next()){
result = true;
}

}catch(SQLException sqle){
sqle.printStackTrace();
}finally{

if(rs != null){
try{
rs.close();
}catch(SQLException sqle){
sqle.printStackTrace();
}
}

if(stmt != null){
try{
stmt.close();
}catch(SQLException sqle){
sqle.printStackTrace();
}
}

if(conn != null){
try{
conn.close();
}catch(SQLException sqle){
sqle.printStackTrace();
}
}
}

return result;
}
}

8. SQL注入问题

在前面提到过,条件查询只要保证where条件为true即可,那么在上面的模拟登录中,验证账户密码是否正确主要是通过拼接SQL语句来条件查询。这其实存在一个比较严重的问题:SQL注入

例如,下面的输入也是可以模拟登录成功的:

1
2
3
请输入登陆用户名:asdf
请输入登陆密码:asdf" or "1" = "1
登陆成功

可以将变量的值拼接到SQL语句中,最终形成的SQL语句如下所示:

1
select * from t_user where login="asdf" and password="asdf" or "1" = "1"

那么上述的条件语句,因为or的优先级低于and,所以该条件语句恒为true。所以SQL语句在执行的时候,查询表中的全部内容。这样做不不合适的,一种解决办法是,即使有查询结果,也要将账号密码全部对比一遍;另一种解决办法是形成上述的SQL语句不参与编译,即不将包含关键字的SQL语句编译。

导致SQL注入的根本原因是什么?

用户输入的信息中含有SQL语句的关键字,并且这些关键字参与SQL语句的编译过程,导致SQL御酒的原意被扭曲,进而达到SQL注入。

9. 解决SQL注入问题

解决SQL注意,一般情况下采用上述的第二种方法。

只要用户提供的信息不参与SQL语句的编译过程,问题就解决了。即使用户提供的信息中含有SQL语句的关键字,但是没有参与编译,不起作用。

要想用户提供的信息不参与SQL语句的编译,那么必须使用java.sql.PreparedStatement接口,该接口继承了java.sql.Statement,属于预编译的数据库操作对象。其原理是预先对SQL语句的框架进行编译,然后再给SQL语句传“值”,然后执行语句,这样值就不会被编译,而是直接。

解决SQL注入的关键是什么?

用户提供的信息中即使含有SQL语句的关键字,但是这些关键字并没有参与编译,不起作用。

将数据库操作对象替换为数据库预操作对象,即PreparedStatement ps = conn.prepareStatement(sql);,自动编译。注意,传入的SQL语句是需要编译的框架,不是完整的SQL语句,不需要编译的内容不需要传入,用问号作为占位符代替。之后,再将值传入数据库预操作对象,ps.setString(index, 值),可根据值的数据类型调用对象的方法,此时,传入的值完全被当做数据来对来,没有被编译。最后,执行SQL语句的时候,和原来的方法一样,但是不需要传入SQL语句,因为创建对象的时候已经传入并编译好了。这样,最终编译执行的语句是SQL框架和未编译的值,具体的修改部分如下所示:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
public static boolean login(Map<String, String> map){

// JDBC代码六步
Connection conn = null;
// Statement stmt = null;
PreparedStatement ps = null; // 这里使用PreparedStatement(预编译的数据库操作对象)
ResultSet rs = null;
boolean result = false;

try{
DriverManager.registerDriver(new com.mysql.jdbc.Driver());

String url = "jdbc:mysql://IP地址:端口号/数据库名";
String user = "用户名";
String password = "密码";

conn = DriverManager.getConnection(url, user, password);
// stmt = conn.createStatement();

String user1 = map.get("user");
String password1 = map.get("password");

// SQL语句框架,用问号表示后期传入的值。其中问号表示占位符,一个问号将来接收一个值,注意,占位符不能用单引号括起来。
String sql = "select * from t_user where login= ? and password= ?";
ps = conn.prepareStatement(sql); // 获取预编译的数据库操作对象,因为是预编译,所以需提前将SQL框架传入。
// 程序执行到此处,会发送sql语句框架给DBMS,然后DBMS进行SQL语句的预先编译。

// 此时,已经将该编译的部分编译完了,那么就需要给问号传值了,
// 注意,给占位符传值,按照问号的下标来,起始下标为1.
// 因为传入的值是String类型,所以采用 setString()方法,第一个参数是占位符的下标,第二个参数是值。
// 此时传入的值,不会被编译,因为上面已经编译完了,ps对象保存的数据是:编译好的SQL框架+具体值。
// 所以,即使传入的值包含SQL关键字,但也仅被当成普通内容(传值的函数传入的内容是什么类型就被当做什么类型)来对待。
ps.setString(1, user1);
ps.setString(2, password1);

// 此处,不需要传入SQL语句,如果传入,则会再编译一次,用户输入的内容就会被编译,SQL注入解决失败。
rs = ps.executeQuery();

if(rs.next()){
// 查到结果不为空,则说明匹配正确。
result = true;
}

}catch(SQLException sqle){
sqle.printStackTrace();
}finally{

if(rs != null){
try{
rs.close();
}catch(SQLException sqle){
sqle.printStackTrace();
}
}

if(ps != null){
try{
ps.close();
}catch(SQLException sqle){
sqle.printStackTrace();
}
}

if(conn != null){
try{
conn.close();
}catch(SQLException sqle){
sqle.printStackTrace();
}
}
}

return result;
}

9.1 对比Statement和PreparedStatement

  • Statement存在SQL注入问题,PreparedStatement解决了SQL注入问题。

  • Statement值编译一次执行一次,PreparedStatement是编译一次,可执行N次。PreparedStatement效率较高一些。

    注意,在mysql中,如果前后两次输入的SQL语句完全一样,那么第二次执行的时候其实不会编译,直接返回上次的结果。

    所以,一般情况下,如果仅仅切换账号和密码,那么采用PreparedStatement是可以编译一次,执行N次的,数据值不需要编译。

  • PreparedStatement

    PreparedStatement会在编译阶段做类型的安全检查,因为Statement是SQL语句拼接,完全是字符串。而PreparedStatement存在后期类型传值,这样,传什么类型的值就调用对应的方法,不易出错。

总之,PreparedStatement使用情况较多,如果要求必须支持SQL注入,那么就需要采用Statement这种方法。

10 演示事务以及银行转账

JDBC中的事务和mysql中的一样,也是执行一条SQL语句,就相当于提交一次事务,不会等到程序执行完再统一提交事务,可以在程序中写两条SQL语句执行,在其中一条语句执行后加断点进行测试,查看数据库中的数据是否发生变化

简单例子如下所示:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
String sql = "update t_user set password = ? where login = ?";
ps = conn.prepareStatement(sql);

ps.setString(1, "wangwu123");
ps.setString(2, "wangwu");

int count = ps.executeUpdate();
System.out.println(count);

// 此处加断点

// 第二条SQL语句
sql = "update t_user set password = ? where login = ?";
ps = conn.prepareStatement(sql);

ps.setString(1, "lisi123");
ps.setString(2, "lisi");

count = ps.executeUpdate();
System.out.println(count);

实际检查数据库数据,发现确实发生了变化,即JDBC中单条语句就会提交事务。那么这样的话,类似银行转账,至少需要两条语句,一条对数据作加法,一条对数据作减法。加入程序出现了问题,第一条语句执行成功,第二条语句执行失败,那么总体上两个账户的金额是增加的,这对银行来说是亏损的,所以这两条语句应该组成一个事务,统一提交。

转账失败案例如下所示:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
public class JDBCTest05 {

public static void main(String[] args){

Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;

try {
DriverManager.registerDriver(new com.mysql.jdbc.Driver());
conn = DriverManager.getConnection("jdbc:mysql://IP地址:端口号/数据库名", "用户名", "密码");

// 转账,111账户向222账户转账1000元,111账户减掉1000,222账户增加1000.
// 查询111账户和222账户余额
String sql_query = "select balance from t_act where actno = ?";
ps = conn.prepareStatement(sql_query);
ps.setInt(1, 111);
rs = ps.executeQuery();

int balance_1 = 0;
if(rs.next()){
balance_1 = rs.getInt("balance");
}

ps.setInt(1, 222);
rs = ps.executeQuery();

int balance_2 = 0;
if(rs.next()){
balance_2 = rs.getInt("balance");
}

// 111账户余额减1000,222账户余额加1000
String sql_minus = "update t_act set balance = ? where actno = ?";
ps = conn.prepareStatement(sql_minus);
ps.setInt(1, balance_1 - 1000);
ps.setInt(2, 111);

int count = ps.executeUpdate();

// 手动产生异常,后续账户222的金额增加失败。但账户111却减少成功,导致二者的金额不对等。
int[] arr = new int[10];
System.out.println(arr[20]);

ps.setInt(1, balance_2 + 1000);
ps.setInt(2, 222);
count = ps.executeUpdate();

} catch (SQLException e) {
e.printStackTrace();
}finally {

if(rs != null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}

if(ps != null){
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}

if(conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}

因为JDBC中设置了事务自动提交,执行一条SQL语句就会自动提交一个事务,而对于转账来说,这些语句应该是统一的、原子的,要么全部执行成功,要么全部执行失败。

所以在转账这部分,应该取消事务的自动提交,改为手动提交,JDBC中的事务应该怎么设置呢?**数据库连接对象提供了设置方法void setAutoCommit(boolean autoCommit) throws SQLException**,传入布尔型值,如果是false,则取消了自动提交。之后,需要提交事务的时候,调用连接对象的提交方法即可void commit() throws SQLException。另外,如果出现了异常,需要将前面“已经执行的语句”回滚(为了避免前面的日志以及缓存对后续造成影响,参考mysql事务部分),即void rollback() throws SQLException

上述SQL语句部分修改代码如下所示:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
try {
DriverManager.registerDriver(new com.mysql.jdbc.Driver());
conn = DriverManager.getConnection("jdbc:mysql://IP地址:端口/数据库名", "用户名", "密码");

// 取消事务自动提交
conn.setAutoCommit(false);

// 转账,111账户向222账户转账1000元,111账户减掉1000,222账户增加1000.
// 查询111账户和222账户余额
String sql_query = "select balance from t_act where actno = ?";
ps = conn.prepareStatement(sql_query);
ps.setInt(1, 111);
rs = ps.executeQuery();

int balance_1 = 0;
if(rs.next()){
balance_1 = rs.getInt("balance");
}

ps.setInt(1, 222);
rs = ps.executeQuery();

int balance_2 = 0;
if(rs.next()){
balance_2 = rs.getInt("balance");
}

// 111账户余额减1000,222账户余额加1000
String sql_minus = "update t_act set balance = ? where actno = ?";
ps = conn.prepareStatement(sql_minus);
ps.setInt(1, balance_1 - 1000);
ps.setInt(2, 111);

int count = ps.executeUpdate();

// 手动产生异常,测试账户111和222的账户金额不对等。
int[] arr = new int[10];
System.out.println(arr[20]);

ps.setInt(1, balance_2 + 1000);
ps.setInt(2, 222);
count = ps.executeUpdate();

// 执行到此处,说明两条SQL都执行完成,也就是这个“组合事务”是执行成功了的,手动提交事务。否则不提交事务。
conn.commit();

} catch (Exception e) {
// 发生异常回滚事务
if(conn != null){
try {
conn.rollback();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
e.printStackTrace();
}

总结起来如下所示:

  • Connection.setAutoCommit(),取消事务自动提交。
  • Connection.commit(),后期主动提交事务。
  • Connection.rollback(),发生异常后回滚事务。

11. 备注

参考B站《动力节点》。


文章作者: 浮云
版权声明: 本博客所有文章除特別声明外,均采用 CC BY 4.0 许可协议。转载请注明来源 浮云 !
  目录