【JDBC下篇】PreparedStatement类 && JDBC事务

偏执的太偏执、 2023-10-08 17:20 33阅读 0赞

#### 文章目录 ####

*  1、PowerDesigner设计表
 *  2、模拟用户登录功能
 *  3、SQL注入
 *  4、解决SQL注入
 *  5、PreparedStatement和Statement的比较
 *  6、使用PreparedStatement完成增删改
 *  7、JDBC的事务自动提交
 *  8、JDBC工具类的封装
 *  9、悲观锁和乐观锁

## 1、PowerDesigner设计表 ##

首先使用PowerDesigner进行数据库表的模型设计。安装完成后，打开PowerDesigner

*  点击创建模型  
    ![在这里插入图片描述][344ca4aafd874540a9b65cda8eca8d49.png]
 *  选择 Physical Data Moudle，选择数据库类型，填写项目名  
    ![在这里插入图片描述][a2d791a583f142cabb8ce858a295e595.png]
 *  在网格中新加入一张表  
    ![在这里插入图片描述][86811279d71c4a059a3d14f45a697346.png]
 *  双击新加入的表开始设计表  
    ![在这里插入图片描述][dec852a0503c4a58aa7f427c05471ee0.png]  
    ![在这里插入图片描述][4e5dbf60fd544866b65c18513d69b94f.png]
 *  表生成成功过，双击打开可找到SQL脚本![在这里插入图片描述][7bd8a09d40fc47f6ba2f7819a9c6df8f.png]  
    ![在这里插入图片描述][94ae7cd156fa4394b930f1aa3f2a8398.png]
 *  保存pdm文件和sql文件  
    ![在这里插入图片描述][228b09ce8b53494bb612fa831249c68a.png]  
    ![在这里插入图片描述][aa314cbed9b444108173280d2a83c281.png]

## 2、模拟用户登录功能 ##

import java.sql.*;
    import java.util.HashMap;
    import java.util.Map;
    import java.util.Scanner;
    
    /**
     * 需求：模拟用户登录功能的实现
     * <p>
     * 业务描述：
     * 提供一个入口给用户输入用户名和密码，用户提交信息后，连接数据库验证用户名和密码是否合法
     * 合法则登录成功，不合法则提示登录失败
     */
    public class UserLogin {
        
        public static void main(String[] args) {
        
            Map<String, String> userInfo = initUI();
            boolean result = login(userInfo);
            System.out.println(result == true ? "登录成功" : "登录失败");
        }
    
        /**
         * 初始化用户登录UI界面
         * 返回用户名和密码
         *
         * @return
         */
        private static Map<String, String> initUI() {
        
            Scanner scanner = new Scanner(System.in);
            System.out.println("请输入用户名：");
            String loginName = scanner.nextLine();
            System.out.println("请输入密码：");
            String loginPwd = scanner.nextLine();
            Map<String,String> userInfo = new HashMap<>();
            userInfo.put("loginName",loginName);
            userInfo.put("loginPwd",loginPwd);
            return userInfo;
        }
    
        /**
         * 判断用户名和密码是否正确
         * @param userInfo
         * @return
         */
        private static boolean login(Map<String, String> userInfo) {
        
            boolean loginResult = false; //打标记，后面有用
            Connection conn = null;
            Statement statement = null;
            ResultSet resultSet = null;
            try {
        
                Class.forName("com.mysql.cj.jdbc.Driver");
                conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/testDB", "root", "root123");
                statement = conn.createStatement();
                String sql = "SELECT * FROM t_user WHERE loginName= '" + userInfo.get("loginName") + "' AND loginPwd='" + userInfo.get("loginPwd") + "'";
                resultSet = statement.executeQuery(sql);
                if (resultSet.next()) {
        
                    loginResult = true;
                }
            } catch (Exception e) {
        
                e.printStackTrace();
            } finally {
        
                if (resultSet != null) {
        
                    try {
        
                        resultSet.close();
                    } catch (SQLException e) {
        
                        e.printStackTrace();
                    }
                }
                if (statement != null) {
        
                    try {
        
                        statement.close();
                    } catch (SQLException e) {
        
                        e.printStackTrace();
                    }
                }
                if (conn != null) {
        
                    try {
        
                        conn.close();
                    } catch (SQLException e) {
        
                        e.printStackTrace();
                    }
                }
            }
            return loginResult;
        }
    
    }

以上重点细品点：

*  拿Map存用户名和密码并return
 *  打标记loginResult，最后if某条件满足，则赋予true

## 3、SQL注入 ##

接上面的程序：  
![SQL注入][SQL]  
Debug可以看到：  
![在这里插入图片描述][340b907977804a8d9525ced31425dc52.png]  
输入这样一个密码后，此时的SQL因为后面的`or '1'='1'`而彻底废掉了，恒成立！

![在这里插入图片描述][272988227aa447138faf42501933a416.png]  
到此，可以总结SQL注入的根本原因是：

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

之前有的网站，当输入上面这种非法密码的时候，会直接锁定这个用户账户。但这样带来的问题是：如果我知道某人的账户名，再输入非法密码，岂不是可以锁别人的账户。因此，这个做法不妥。

## 4、解决SQL注入 ##

**只要用户提供的信息不参与SQL语句的编译**，哪怕它含有关键字，也不起作用，这个隐患自然而然就解决了。想让用户提供的信息不参与编译，则需要用到**java.sql.PreparedStatement**

*  java.sql.PreparedStatement接口继承了java.sql.Statement
 *  PreparedStatement是属于预编译的数据库操作对象
 *  PreparedStatement的原理是预先对SQL语句的框架进行编译，然后再给SQL语句传"值"

对2中代码的login方法做出修改：

private static boolean login(Map<String, String> userInfo) {
        
    
            boolean loginResult = false;
            Connection conn = null;
            PreparedStatement preparedStatement = null;  //预编译的数据库操作对象
            ResultSet resultSet = null;
            
            try {
        
                Class.forName("com.mysql.cj.jdbc.Driver");
                conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/testDB", "root", "root123");
                /**
                 * 获取预编译的数据库操作对象
                 */
                //这个SQL是一个SQL语句的框子，其中一个？代表一个占位符，一个占位符将来接收一个"值"
                //注意占位符？别加引号，否则就是一个普通的字符串
                String sql = "SELECT * FROM t_user WHERE loginName = ? AND loginPwd = ? ";
                //程序执行到此处，会发送SQL语句框子给DBMS，然后DBMS进行SQL语句的预先编译
                preparedStatement = conn.prepareStatement(sql);
                /**
                 * 执行SQL
                 * 给占位符？传值，第一个？的下标是1.JDBC中的所有下标从1开始
                 * 这里调用的是setString方法，则？被替换后会自带''把传的值括起来
                 * 如果是setInt方法，则？仅仅被替换
                 * 到这儿的传值随便传，因为SQL上一步已经编译完了
                 */
                preparedStatement.setString(1,userInfo.get("loginName"));
                preparedStatement.setString(2,userInfo.get("loginPwd"));
                resultSet = preparedStatement.executeQuery();
                /**
                 * 处理结果集
                 */
                if (resultSet.next()) {
        
                    loginResult = true;
                }
            } catch (Exception e) {
        
                e.printStackTrace();
            } finally {
        
                if (resultSet != null) {
        
                    ...
            }
            return loginResult;
        }

执行结果：  
![run][]

**注意点：**

*  SQL语句的框子中：一个？代表一个占位符，一个占位符将来接收一个"值"
 *  占位符？别加引号，否则就是一个普通的字符串
 *  给占位符？传值，第一个？的下标是1.JDBC中的所有下标从1开始
 *  调用的是setString方法，则？被替换后会自带’'把传的值括起来，若是setInt方法，则？仅仅被替换。具体选择看你的数据类型

> 为了方便记忆：和之前的Statement比较，可以把这里动态看成：
> 
> --------------------
> 
> Statement中的String sql =… 被分成了两块，一块放在获取（预编译的）数据库操作对象前，一部分放在了执行SQL前，即setString传值。

## 5、PreparedStatement和Statement的比较 ##

*  Statement存在sql注入问题，PreparedStatement解决了sql注入问题
 *  Statement是编译一次执行一次，PreparedStatement是执行一次可执行N次，PreparedStatement效率更高
 *  PreparedStatement会在编译阶段做类型的安全检查。Statement的sql是拼接的，参数类型错误也不会提示，而PreparedStatement的setString、setInt会卡死数据类型，保证正确

因此，Statement极少用，只有当业务方面要求支持SQL注入，或者要进行SQL拼接的时候，才用Statement

> **须使用Statement的场景演示–升序降序**

SELECT * FROM .... order by xx ?

这里的升降序，给占位符传desc，desc会带上一个单引号，导致语法error这里就得用Statement：

import java.sql.*;
    import java.util.Scanner;
    
    public class StatementTest {
        
        public static void main(String[] args) {
        
            Scanner scanner  = new Scanner(System.in);
            System.out.println("请输入排序方式：升序asc,降序desc");
            String keyword = scanner.nextLine();
    
            Connection conn = null;
            Statement statement = null;
            ResultSet resultSet = null;
            try{
        
                Class.forName("com.mysql.cj.jdbc.Driver");
                conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/testDB","root","root123");
                statement = conn.createStatement();
                String sql = "SELECT loginName FROM t_user order by loginName " + keyword;
                resultSet = statement.executeQuery(sql);
                while(resultSet.next()){
        
                    System.out.println(resultSet.getString("loginName"));
                }
    
            }catch(Exception e){
        
                e.printStackTrace();
            }finally{
        
            ...
            }
    
        }
    }

这个时候使用PreparedStatement反而打不到效果：

![error][]

## 6、使用PreparedStatement完成增删改 ##

运行结果：

![run][run 1]

## 7、JDBC的事务自动提交 ##

JDBC的事务是自动提交的，即**只要执行任意一条DML语句，则自动提交一次**，这是JDBC默认的事务行为。

> **实验验证：**

![在这里插入图片描述][dbd372af3efd4b7d91d9f5a7bad55c75.png]  
![在这里插入图片描述][ffb215085e81467ebca7ea0f95811876.png]

但实际的业务中，通常都是N条DML语句共同联合才能完成一个业务，因此，必须保证这些DML语句在同一个事务中同时成功或者同时失败。比如账户转账事务：

> **JDBC事务之账户转账–错误示范**

![在这里插入图片描述][d9ea8d534b3046e8b070e381f5f7a555.png]  
这里主要贴核心代码：

Class.forName("com.mysql.cj.jdbc.Driver");
       conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/testDB","root","root123");
       String sql = "UPDATE t_act SET balance = ? WHERE actno = ?";
       preparedStatement = conn.prepareStatement(sql);
       /**
        * 第一次传值，
        * 更新1号账户
        */
       preparedStatement.setDouble(1,10000);
       preparedStatement.setInt(2,1);
       int count = preparedStatement.executeUpdate();
    
       /**
        * 若更新完转账者的账户后出现了异常
        * 导致接收者的账户没更新，则丢钱了
        * 这里就用空指针模拟异常情况
        */
       String e = null;
       e.toString();
    
       /**
        * 第二次传值
        * 更新2号账户
        */
       preparedStatement.setDouble(1,10000);
       preparedStatement.setInt(2,2);
       count += preparedStatement.executeUpdate();
       System.out.println(count == 2 ? "转账成功" : "转账失败");

![在这里插入图片描述][4e46a4eab95347a3be193f27e57b33c8.png]

使用JDBC的默认事务提交，则：

![在这里插入图片描述][723039a1240b43e39357ddad1a87566f.png]

以上虽然是错误示范，但根据`count += preparedStatement.executeUpdate();`是否为2来判断转账是否成功的思路值得细品！

☀☀☀

> **JDBC事务之账户转账–正确示范**

因此，修改自动提交为手动提交：  
![在这里插入图片描述][c8fed53acd2043da800e89e0841d5a89.png]

import java.sql.*;
    
    public class PreparedStatementTest {
        
        public static void main(String[] args) {
        
            Connection conn = null;
            PreparedStatement preparedStatement = null;
            try {
        
                Class.forName("com.mysql.cj.jdbc.Driver");
                conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/testDB","root","root123");
                /**
                 * 将事务提交机制改为手动提交
                 */
                conn.setAutoCommit(false);
                String sql = "UPDATE t_act SET balance = ? WHERE actno = ?";
                preparedStatement = conn.prepareStatement(sql);
                /**
                 * 第一次传值，
                 * 更新1号账户
                 */
                preparedStatement.setDouble(1,10000);
                preparedStatement.setInt(2,1);
                int count = preparedStatement.executeUpdate();
    
                /**
                 * 若更新完转账者的账户后出现了异常
                 * 导致接收者的账户没更新，则丢钱了
                 * 这里就用空指针模拟异常情况
                 */
                String e = null;
                e.toString();
    
                /**
                 * 第二次传值
                 * 更新2号账户
                 */
                preparedStatement.setDouble(1,10000);
                preparedStatement.setInt(2,2);
                count += preparedStatement.executeUpdate();
                System.out.println(count == 2 ? "转账成功" : "转账失败");
                /**
                 * 程序能执行到这儿，则说明前面的程序没有异常
                 * 在这里事务结束，手动提交数据
                 */
                conn.commit();
            } catch (Exception e) {
        
                e.printStackTrace();
                /**
                 * 出现异常后，除了打印异常信息
                 * 还要回滚事务
                 */
                if(conn != null){
        
                    try {
        
                        conn.rollback();
                    } catch (SQLException e1) {
        
                        e1.printStackTrace();
                    }
                }
            }
        }
    }

此时，转账中间发生异常后，数据回滚，不会丢钱：  
![在这里插入图片描述][f1ab8052c4a24614bc420697455a17fb.png]  
![在这里插入图片描述][3fbf30bb576145dbb71833cd284f09a7.png]  
JDBC单机事务中重点的三句代码：

*  `conn.setAutoCommit(false);`
 *  `conn.commit();`
 *  `conn.rollback();`

## 8、JDBC工具类的封装 ##

建立一个util工具包：  
![在这里插入图片描述][86b73e18c32f42c6b39cef02a8bd17ec.png]

package com.myjdbc.util;
    
    import java.sql.*;
    
    public class DBUtil {
        
        /**
         * 工具类中的构造方法一般都是私有的，因为工具类一般不用造对象
         * 不用new对象是因为工具类中的方法一般都是私有的，直接类名.
         */
        private DBUtil(){
        
    
        }
        /**
         * 如果把注册驱动写到某方法中，多次调用某方法会导致驱动重复注册
         * 因此放在静态代码块中
         * 只要调用某方法，则类加载。类加载，则静态代码块执行，进而驱动注册
         */
        static{
        
            try {
        
                Class.forName("com.mysql.cj.jdbc.Driver");
            } catch (ClassNotFoundException e) {
        
                e.printStackTrace();
            }
        }
    
        /**
         * 获取数据库连接对象
         * @return 数据库连接对象
         * @throws SQLException
         */
        public static Connection getConnection() throws SQLException {
        
            return DriverManager.getConnection("jdbc:mysql://localhost:3306/testDB","root","root123");
        }
    
        /**
         * 关闭资源
         * @param conn 数据库连接对象
         * @param statement 数据库操作对象
         * @param resultSet 查询结果集对象
         */
        public static void closeSource(Connection conn, Statement statement, ResultSet resultSet){
        
            if(resultSet != null){
        
                try {
        
                    resultSet.close();
                } catch (SQLException e) {
        
                    e.printStackTrace();
                }
            }
            if(statement != null){
        
                try {
        
                    statement.close();
                } catch (SQLException e) {
        
                    e.printStackTrace();
                }
            }
            if(conn != null){
        
                try {
        
                    conn.close();
                } catch (SQLException e) {
        
                    e.printStackTrace();
                }
            }
        }
    }

**使用上面自己封装的工具包来进行JDBC的模糊查询：**

import com.myjdbc.util.DBUtil;
    
    import java.sql.*;
    public class LikeQuery {
        
        public static void main(String[] args) {
        
            Connection conn = null;
            PreparedStatement preparedStatement = null;
            ResultSet resultSet = null;
            try {
        
                conn = DBUtil.getConnection();
                String sql = "SELECT loginName,loginPwd from t_user where loginPwd like ?";
                preparedStatement = conn.prepareStatement(sql);
                preparedStatement.setString(1,"__ot%");
                resultSet = preparedStatement.executeQuery();
                while(resultSet.next()){
        
                    System.out.println(resultSet.getString("loginName") + " " + resultSet.getString("loginPwd"));
                }
            } catch (SQLException e) {
        
                e.printStackTrace();
            }finally{
        
                DBUtil.closeSource(conn,preparedStatement,resultSet);
            }
        }
    }

![在这里插入图片描述][ebdc2e097c0e4b378101941df9c44ed2.png]

## 9、悲观锁和乐观锁 ##

*  悲观锁：事务必须排队执行。数据锁住了，不允许并发（行级锁：select语句末尾添加for update)
 *  乐观锁：支持并发，事务也不需要排队，只不过需要一个版本号  
    ![在这里插入图片描述][f7050b098f5249d4960705d992310efe.png]

> 行级锁（悲观锁）实验

JDBC1代码块：  
![在这里插入图片描述][47bf7a770dfc412a8875a77227763381.png]  
JDBC2代码块：  
![在这里插入图片描述][2cfd7a6e1b8840748238a7a84329a40d.png]

--------------------

展示行级锁效果：

![在这里插入图片描述][149e45de2156466e8b84bdbc192700d2.png]  
运行JDBC2，更改被行级锁锁住的几行数据  
![在这里插入图片描述][856e6568817c44c29faefa4d4d9e641c.png]

此时，停止JDBC1程序的debug，让事务结束，则可更新成功：  
![在这里插入图片描述][d5fe42dbb26e4d47a529caf53cba97fd.png]

![在这里插入图片描述][69a0da734e774a00be46e0a3b2f02c29.png]

[344ca4aafd874540a9b65cda8eca8d49.png]: https://img-blog.csdnimg.cn/344ca4aafd874540a9b65cda8eca8d49.png
[a2d791a583f142cabb8ce858a295e595.png]: https://img-blog.csdnimg.cn/a2d791a583f142cabb8ce858a295e595.png
[86811279d71c4a059a3d14f45a697346.png]: https://img-blog.csdnimg.cn/86811279d71c4a059a3d14f45a697346.png
[dec852a0503c4a58aa7f427c05471ee0.png]: https://img-blog.csdnimg.cn/dec852a0503c4a58aa7f427c05471ee0.png
[4e5dbf60fd544866b65c18513d69b94f.png]: https://img-blog.csdnimg.cn/4e5dbf60fd544866b65c18513d69b94f.png
[7bd8a09d40fc47f6ba2f7819a9c6df8f.png]: https://img-blog.csdnimg.cn/7bd8a09d40fc47f6ba2f7819a9c6df8f.png
[94ae7cd156fa4394b930f1aa3f2a8398.png]: https://img-blog.csdnimg.cn/94ae7cd156fa4394b930f1aa3f2a8398.png
[228b09ce8b53494bb612fa831249c68a.png]: https://img-blog.csdnimg.cn/228b09ce8b53494bb612fa831249c68a.png
[aa314cbed9b444108173280d2a83c281.png]: https://img-blog.csdnimg.cn/aa314cbed9b444108173280d2a83c281.png
[SQL]: https://img-blog.csdnimg.cn/a2c41f636f3e49328af2de5f777f21d6.png
[340b907977804a8d9525ced31425dc52.png]: https://img-blog.csdnimg.cn/340b907977804a8d9525ced31425dc52.png
[272988227aa447138faf42501933a416.png]: https://img-blog.csdnimg.cn/272988227aa447138faf42501933a416.png
[run]: https://img-blog.csdnimg.cn/70d0043f6ff34b2f9e74ef098aa5f75a.png
[error]: https://img-blog.csdnimg.cn/c805a471404245199430baec384cac57.png
[run 1]: https://img-blog.csdnimg.cn/7871c5061f6647fc90577498afda935a.png
[dbd372af3efd4b7d91d9f5a7bad55c75.png]: https://img-blog.csdnimg.cn/dbd372af3efd4b7d91d9f5a7bad55c75.png
[ffb215085e81467ebca7ea0f95811876.png]: https://img-blog.csdnimg.cn/ffb215085e81467ebca7ea0f95811876.png
[d9ea8d534b3046e8b070e381f5f7a555.png]: https://img-blog.csdnimg.cn/d9ea8d534b3046e8b070e381f5f7a555.png
[4e46a4eab95347a3be193f27e57b33c8.png]: https://img-blog.csdnimg.cn/4e46a4eab95347a3be193f27e57b33c8.png
[723039a1240b43e39357ddad1a87566f.png]: https://img-blog.csdnimg.cn/723039a1240b43e39357ddad1a87566f.png
[c8fed53acd2043da800e89e0841d5a89.png]: https://img-blog.csdnimg.cn/c8fed53acd2043da800e89e0841d5a89.png
[f1ab8052c4a24614bc420697455a17fb.png]: https://img-blog.csdnimg.cn/f1ab8052c4a24614bc420697455a17fb.png
[3fbf30bb576145dbb71833cd284f09a7.png]: https://img-blog.csdnimg.cn/3fbf30bb576145dbb71833cd284f09a7.png
[86b73e18c32f42c6b39cef02a8bd17ec.png]: https://img-blog.csdnimg.cn/86b73e18c32f42c6b39cef02a8bd17ec.png
[ebdc2e097c0e4b378101941df9c44ed2.png]: https://img-blog.csdnimg.cn/ebdc2e097c0e4b378101941df9c44ed2.png
[f7050b098f5249d4960705d992310efe.png]: https://img-blog.csdnimg.cn/f7050b098f5249d4960705d992310efe.png
[47bf7a770dfc412a8875a77227763381.png]: https://img-blog.csdnimg.cn/47bf7a770dfc412a8875a77227763381.png
[2cfd7a6e1b8840748238a7a84329a40d.png]: https://img-blog.csdnimg.cn/2cfd7a6e1b8840748238a7a84329a40d.png
[149e45de2156466e8b84bdbc192700d2.png]: https://img-blog.csdnimg.cn/149e45de2156466e8b84bdbc192700d2.png
[856e6568817c44c29faefa4d4d9e641c.png]: https://img-blog.csdnimg.cn/856e6568817c44c29faefa4d4d9e641c.png
[d5fe42dbb26e4d47a529caf53cba97fd.png]: https://img-blog.csdnimg.cn/d5fe42dbb26e4d47a529caf53cba97fd.png
[69a0da734e774a00be46e0a3b2f02c29.png]: https://img-blog.csdnimg.cn/69a0da734e774a00be46e0a3b2f02c29.png