投稿
  1. 极客之音首页
  2. 技术随笔

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

飞熊 • • 技术随笔 • 阅读 185

有时候，不是因为你没有能力，也不是因为你缺少勇气，只是因为你付出的努力还太少，所以，成功便不会走向你。而你所需要做的，就是坚定你的梦想，你的目标，你的未来，然后以不达目的誓不罢休的那股劲，去付出你的努力，成功就会慢慢向你靠近。

导读：本篇文章讲解 【JDBC下篇】PreparedStatement类 && JDBC事务，希望对大家有帮助，欢迎收藏，转发！站点地址：www.bmabk.com，来源：原文

文章目录

1、PowerDesigner设计表

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

  • 点击创建模型
    在这里插入图片描述
  • 选择 Physical Data Moudle，选择数据库类型，填写项目名
    在这里插入图片描述
  • 在网格中新加入一张表
    在这里插入图片描述
  • 双击新加入的表开始设计表
    在这里插入图片描述
    在这里插入图片描述
  • 表生成成功过，双击打开可找到SQL脚本在这里插入图片描述
    在这里插入图片描述
  • 保存pdm文件和sql文件
    在这里插入图片描述
    在这里插入图片描述

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注入
Debug可以看到：
在这里插入图片描述
输入这样一个密码后，此时的SQL因为后面的or '1'='1'而彻底废掉了，恒成立！

在这里插入图片描述
到此，可以总结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完成增删改

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");
            String sql = "INSERT INTO t_user (`id`,`loginName`,`loginPwd`,`realName`) VALUES (?,?,?,?)";
            preparedStatement = conn.prepareStatement(sql);
            preparedStatement.setInt(1,4);
            preparedStatement.setString(2,"user");
            preparedStatement.setString(3,"123qwe");
            preparedStatement.setString(4,"AAA");
            int count = preparedStatement.executeUpdate();
            System.out.println(count == 1 ? "数据库表更新成功" : "t_user表更新失败");
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
        ...
        }
    }
}

运行结果：

run

7、JDBC的事务自动提交

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

实验验证：

在这里插入图片描述
在这里插入图片描述

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

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

在这里插入图片描述
这里主要贴核心代码：

   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 ? "转账成功" : "转账失败");

在这里插入图片描述

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

在这里插入图片描述

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

☀☀☀

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

因此，修改自动提交为手动提交：
在这里插入图片描述

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();
                }
            }
        }
    }
}

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

  • conn.setAutoCommit(false);
  • conn.commit();
  • conn.rollback();

8、JDBC工具类的封装

建立一个util工具包：
在这里插入图片描述

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);
        }
    }
}

在这里插入图片描述

9、悲观锁和乐观锁

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

行级锁（悲观锁）实验

JDBC1代码块：
在这里插入图片描述
JDBC2代码块：
在这里插入图片描述

展示行级锁效果：

在这里插入图片描述
运行JDBC2，更改被行级锁锁住的几行数据
在这里插入图片描述

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

在这里插入图片描述

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。

文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/146088.html

赞 (0)
飞熊的头像飞熊bm
0 0

相关推荐

发表回复

登录后才能评论
极客之音——专业性很强的中文编程技术网站，欢迎收藏到浏览器，订阅我们！