MySQL_06_JDBC

MySQL
发布日期:   2021-11-17
更新日期:   2021-12-16
文章字数:   7.1k
阅读时长:   29 分
阅读次数:  

前面介绍了数据库的相关操作，但那仅仅是在数据库客户端的操作。而程序本质上是对数据的处理，那么程序怎么操作数据库呢？这就需要数据库的接口了，对于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站《动力节点》。

文章作者: 浮云
文章链接: https://ihianian.github.io/2021/11/17/MySQL-06-JDBC/
版权声明: 本博客所有文章除特別声明外，均采用 CC BY 4.0 许可协议。转载请注明来源 浮云 !
MySQL
赏
  目录