JDBC-API详解-PreparedStatement接口

PreparedStatement接口

• PreparedStatement作用：
  预编译SQL语句并执行：预防SQL注入问题

• 重点：
  useServerPrepStmts=true

下文详细介绍

---

• PreparedStatement 是 Statement 接口的子接口，继承于父接口中所有的方法。它是一个预编译的 SQL 语句
  

---

SQL注入

SQL注入是通过操作输入来修改事先定义好的SQL语句，用以达 到执行代码对服务器进行攻击的方法。

• 首先

SQL注入是通过操作输入来修改事先定义好的SQL语句，用以达到执行代码对服务器进行攻击的方法。

spring.datasource.driver-class-

name=com.mysql.cj.jdbc.Driver

spring.datasource.url=jdbc:mysql://localhost:3306/te
st?
useSSL=false&useUnicode=true&characterEncoding=UTF-8
spring.datasource.username=root
spring.datasource.password=1234

• 其次

1. 在MySQL中创建名为 test 的数据库

create database test;

2. 在命令提示符中运行今天资料下的 day03-JDBC\资料\2. sql注入演示\sql.jar 这个jar包
   链接:https://pan.baidu.com/s/1byhp3-q1dLFrQ3QggCdlpA
   提取码：SGS1
   
3. 此时我们就能在数据库中看到user表

• 最后

分析1图文介绍

1. 接下来在浏览器的地址栏输入 localhost:8080/login.html 就能看到如下页面
   
2. 我们就可以在如上图中输入用户名和密码进行登陆。用户名和密码输入正确就登陆成功，跳转到首页。用户名和密码输入错误则给出错误提示，如下图
   
   重点理解这里:
   但是我可以通过输入一些特殊的字符登陆到首页。用户名随意写，密码写成 ’ or ‘1’ =‘1’
   

分析2代码介绍

1. 代码模拟SQL注入问题

@Test
public void testLogin() throws  Exception {

  //2. 获取连接：如果连接的是本机mysql并且端口是默认的3306 可以简化书写
  String url = "jdbc:mysql:///db1?useSSL=false";
  String username = "root";
  String password = "1234";
  Connection conn =
DriverManager.getConnection(url, username,password);

  // 接收用户输入 用户名和密码
  String name = "sjdljfld";
  String pwd = "' or '1' = '1";
  String sql = "select * from tb_user where username = '"+name+"' and password = '"+pwd+"'";

  // 获取stmt对象
  Statement stmt = conn.createStatement();
  
  // 执行sql
  ResultSet rs = stmt.executeQuery(sql);
  
  // 判断登录是否成功
  if(rs.next()){
    System.out.println("登录成功~");
 }else{
    System.out.println("登录失败~");
 }
 
  //7. 释放资源
  rs.close();
  stmt.close();
  conn.close();
}

本文核心重点

2. 上面代码是将用户名和密码拼接到sql语句中，拼接后的sql语句如下

select * from tb_user where username = 'sjdljfld'and password = ''or '1' = '1'

从上面语句可以看出条件 username = 'sjdljfld' andpassword = '' 不管是否满足，而 or 后面的 '1' = '1' 是始终满足的，最终条件是成立的，就可以正常的进行登陆了。

结果危害:
这就是SQL注入漏洞，也是很危险的。当然现在市面上的系统都不会存在这种问题了，所以大家也不要尝试用这种方式去试其他的系统。

如何解决？

这里就可以将SQL执行对象 Statement 换成PreparedStatement 对象

一、PreparedStatement的原理

1. 因为有预先编译的功能，提高 SQL 的执行效率。
2. 可以有效的防止 SQL 注入的问题，安全性更高。

二、PreparedStatement概述

1.获取对象

PreparedStatement作用：

• 预编译SQL语句并执行：预防SQL注入问题

• 获取 PreparedStatement 对象 ( Connection 创建 PreparedStatement 对象)

Connection 接口中的方法	描述
PreparedStatement prepareStatement(String sql)	指定预编译的 SQL 语句，SQL 语句中使用占位符?创建一个语句对象

//连接驱动
Connection conn =DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/db1useSSL=false","root","o676448";

// SQL语句中的参数值，使用？占位符替代
String sql = "select * from user where username =? and password = ?";

// 通过Connection对象获取，并传入对应的sql语句
PreparedStatement pstmt =conn.prepareStatement(sql);

---

• PreparedStatement 接口中的方法

PreparedStatement 接口中的方法	描述
int executeUpdate()	执行 DML，增删改的操作，返回影响的行数。
ResultSet executeQuery()	执行 DQL，查询的操作，返回结果集

1. 设置参数值

上面的sql语句中参数使用 ? 进行占位，在之前之前肯定要设置这些 ? 的值。

• PreparedStatement对象：setXxx(参数1，参数2)：给 ? 赋值
  • Xxx：数据类型 ； 如 setInt (参数1，参数2)
  • 参数：
    • 参数1： ？的位置编号，从1 开始
    • 参数2： ？的值

2. 执行SQL语句

executeUpdate(); 执行DDL语句和DML语句
executeQuery(); 执行DQL语句
注意：

• 调用这两个方法时不需要传递SQL语句，因为获取SQL语
  句执行对象时已经对SQL语句进行预编译了。

3. PreparedSatement 的好处

1. prepareStatement()会先将 SQL 语句发送给数据库预编译。PreparedStatement 会引用着预编译后的结果。
2. 可以多次传入不同的参数给 PreparedStatement 对象并执行。减少 SQL 编译次数，提高效率。
3. 安全性更高，没有 SQL 注入的隐患。
4. 提高了程序的可读性

• 使用 PreparedStatement 的步骤

1. 编写 SQL 语句，未知内容使用?占位：“SELECT * FROM user WHERE name=? AND password=?”;
2. 获得 PreparedStatement 对象
3. 设置实际参数：setXxx(占位符的位置, 真实的值)
4. 执行参数化 SQL 语句
5. 关闭资源

PreparedStatement接口中设置参数的方法	描述
void setDouble(int parameterIndex, double x)	将指定参数设置为给定 Java double 值。
void setFloat(int parameterIndex, float x)	将指定参数设置为给定 Java REAL 值。
void setInt(int parameterIndex, int x)	将指定参数设置为给定 Java int 值。
void setLong(int parameterIndex, long x)	将指定参数设置为给定 Java long 值。
void setObject(int parameterIndex, Object x)	使用给定对象设置指定参数的值。
void setString(int parameterIndex, String x)	将指定参数设置为给定 Java String 值。

2.使用PreparedStatement改进

@Test
public void testPreparedStatement() throws
 Exception {


  //2. 获取连接：如果连接的是本机mysql并且端口是默认的3306 可以简化书写
  String url = "jdbc:mysql:///db1?useSSL=false";
  String username = "root";
  String password = "1234";
  Connection conn =DriverManager.getConnection(url, username,password);


  // 接收用户输入 用户名和密码
  String name = "zhangsan";
  
  String pwd = "' or '1' = '1";
  
  // 定义sql
  String sql = "select * from tb_user where username = ? and password = ?";
  
  // 获取pstmt对象
  PreparedStatement pstmt =conn.prepareStatement(sql);
  
  // 设置？的值
  pstmt.setString(1,name);
  pstmt.setString(2,pwd);
  
  // 执行sql
  ResultSet rs = pstmt.executeQuery();


  // 判断登录是否成功
  if(rs.next()){
    System.out.println("登录成功~");
 }else{
    System.out.println("登录失败~");
 }
 
  //7. 释放资源
  rs.close();
  pstmt.close();
  conn.close();
}

执行上面语句就可以发现不会出现SQL注入漏洞问题了。
那么PreparedStatement又是如何解决的呢？它是将特殊字符进行了转义，转义的SQL如下：

select * from tb_user where username = 'sjdljfld' and password = '\'or \'1\' = \'1'

代码如下（示例）：

data = pd.read_csv(
    'https://labfile.oss.aliyuncs.com/courses/1283/adult.data.csv')
print(data.head())

该处使用的url网络请求的数据。

---

二、PreparedStatement原理

1.PreparedStatement 好处：

• 预编译SQL，性能更高
• 防止SQL注入：将敏感字符进行转义
  
  Java代码操作数据库流程如图所示：
• 将sql语句发送到MySQL服务器端
• MySQL服务端会对sql语句进行如下操作
  • 检查SQL语句，检查SQL语句是否正确
  • 编译SQL语句。将SQL语句编译成可执行的函数。
    检查SQL和编译SQL花费的时间比执行SQL的时间还要长。如果我们只是重新设置参数，那么检查SQL语句和编译SQL语句将不需要重复执行。这样就提高了性能
  • 执行SQL语句

接下来我们通过查询日志来看一下原理。

• 开启预编译功能
  在代码中编写url时需要加上以下参数。而我们之前根本就没有开启预编译功能，只是解决了SQL注入漏洞。

useServerPrepStmts=true

• 配置MySQL执行日志（重启mysql服务后生效）在mysql配置文件（my.ini）中添加如下配置

log-output=FILE
general-log=1
general_log_file="D:\mysql.log"
slow-query-log=1
slow_query_log_file="D:\mysql_slow.log"
long_query_time=2

• java测试代码如下：

/**
 * PreparedStatement原理
 * @throws Exception
 */
@Test
public void testPreparedStatement2() throws Exception {

//2. 获取连接：如果连接的是本机mysql并且端口是默认的
3306 可以简化书写
  
  // useServerPrepStmts=true 参数开启预编译功能
  String url = "jdbc:mysql:///db1?
useSSL=false&useServerPrepStmts=true";
  String username = "root";
  String password = "1234";
  Connection conn =
DriverManager.getConnection(url, username,password);
  
  // 接收用户输入 用户名和密码
  String name = "zhangsan";
  String pwd = "' or '1' = '1";
  
  // 定义sql
  String sql = "select * from tb_user where username = ? and password = ?";
  
  // 获取pstmt对象
  PreparedStatement pstmt =conn.prepareStatement(sql);
  Thread.sleep(10000);
  
  // 设置？的值
  pstmt.setString(1,name);
  pstmt.setString(2,pwd);
  ResultSet rs = null;

  // 执行sql
  rs = pstmt.executeQuery();

  // 设置？的值
  pstmt.setString(1,"aaa");
  pstmt.setString(2,"bbb");

  // 执行sql
  rs = pstmt.executeQuery();

  // 判断登录是否成功
  if(rs.next()){
    System.out.println("登录成功~");
 }else{
    System.out.println("登录失败~");
 }
  //7. 释放资源
  rs.close();
  pstmt.close();
  conn.close();
}

• 执行SQL语句，查看 D:\mysql.log 日志如下:
  
  上图中第三行中的 Prepare 是对SQL语句进行预编译。第四行和第五行是执行了两次SQL语句，而第二次执行前并没有对SQL进行预编译。

• 小结：
  在获取PreparedStatement对象时，将sql语句发送给mysql
  服务器进行检查，编译（这些步骤很耗时）
执行时就不用再进行这些步骤了，速度更快
  如果sql模板一样，则只需要进行一次检查、编译

三. 案例

• 案例1：使用 PreparedStatement 查询一条数据，封装成一个学生 Student 对象

package com.itheima;
import com.itheima.entity.Student;
import com.itheima.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class Demo9Student {
public static void main(String[] args) throws SQLException {
//创建学生对象
Student student = new Student();
Connection connection = JdbcUtils.getConnection();
PreparedStatement ps = connection.prepareStatement("select * from student where id=?");
//设置参数
ps.setInt(1,2);
ResultSet resultSet = ps.executeQuery();
if (resultSet.next()) {
//封装成一个学生对象
student.setId(resultSet.getInt("id"));
student.setName(resultSet.getString("name"));
student.setGender(resultSet.getBoolean("gender"));
student.setBirthday(resultSet.getDate("birthday"));
}
//释放资源
JdbcUtils.close(connection,ps,resultSet);
//可以数据
System.out.println(student);
  }
}

• 案例2：将多条记录封装成集合 List，集合中每个元素是一个 JavaBean 实体类
  • 需求： 查询所有的学生类，封装成 List返回
  • 代码：
    

package com.itheima;
import com.itheima.entity.Student;
import com.itheima.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class Demo10List {
public static void main(String[] args) throws SQLException {
//创建一个集合
List<Student> students = new ArrayList<>();
Connection connection = JdbcUtils.getConnection();
PreparedStatement ps = connection.prepareStatement("select * from student");
//没有参数替换
ResultSet resultSet = ps.executeQuery();
while(resultSet.next()) {
//每次循环是一个学生对象
Student student = new Student();
//封装成一个学生对象
student.setId(resultSet.getInt("id"));
student.setName(resultSet.getString("name"));
student.setGender(resultSet.getBoolean("gender"));
student.setBirthday(resultSet.getDate("birthday"));
//把数据放到集合中
students.add(student);
}
//关闭连接
JdbcUtils.close(connection,ps,resultSet);
//使用数据
for (Student stu: students) {
System.out.println(stu);
       }
   }
}

PreparedStatement 执行 DML 操作

package com.itheima;
import com.itheima.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class Demo11DML {
public static void main(String[] args) throws SQLException {
//insert();
//update();
delete();
}

//插入记录
private static void insert() throws SQLException {
Connection connection = JdbcUtils.getConnection();
PreparedStatement ps = connection.prepareStatement("insert into student
values(null,?,?,?)");
ps.setString(1,"小白龙");
ps.setBoolean(2, true);
ps.setDate(3,java.sql.Date.valueOf("1999-11-11"));
int row = ps.executeUpdate();
System.out.println("插入了" + row + "条记录");
JdbcUtils.close(connection,ps);
}

//更新记录: 换名字和生日
private static void update() throws SQLException {
Connection connection = JdbcUtils.getConnection();
PreparedStatement ps = connection.prepareStatement("update student set name=?, birthday=?
where id=?");
ps.setString(1,"黑熊怪");
ps.setDate(2,java.sql.Date.valueOf("1999-03-23"));
ps.setInt(3,5);
int row = ps.executeUpdate();
System.out.println("更新" + row + "条记录");
JdbcUtils.close(connection,ps);
}

//删除记录: 删除第 5 条记录
private static void delete() throws SQLException {
Connection connection = JdbcUtils.getConnection();
PreparedStatement ps = connection.prepareStatement("delete from student where id=?");
ps.setInt(1,5);
int row = ps.executeUpdate();
System.out.println("删除了" + row + "条记录");
JdbcUtils.close(connection,ps);
}
}

• 案例2：JDBC 事务的处理
  之前我们是使用 MySQL 的命令来操作事务。接下来我们使用 JDBC 来操作银行转账的事务。

1. 添加account表信息

CREATE TABLE account (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
balance DOUBLE
);
-- 添加数据
INSERT INTO account (NAME, balance) VALUES ('Jack', 1000), ('Rose', 1000);

2. API介绍

Connection接口中的方法	说明
void setAutoCommit(boolean autoCommit)	参数时true或者false,如果设置false则关闭自动提交，变为手动提交事务
void Commit()	提交事务
void rollback()	回滚事务

3. 开发步骤

   • 获取连接
   • 开启事务
   • 获取到PreparedStatement
   • 使用PreparedStatement执行两次更新操作
   • 正常情况下提交事务
   • 出现异常回滚事务
   • 最后关闭资源

4. 案例代码：

package com.itheima;

import com.itheima.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class Demo12Transaction {
    //没有异常，提交事务，出现异常回滚事务
    public static void main(String[] args) {
//1) 注册驱动
        Connection connection = null;
        PreparedStatement ps = null;
        try {
//2) 获取连接
            connection = JdbcUtils.getConnection();
//3) 开启事务
            connection.setAutoCommit(false);
//4) 获取到 PreparedStatement
//从 jack 扣钱
            ps = connection.prepareStatement("update account set balance = balance - ? where
                    name = ? ");
            ps.setInt(1, 500);
            ps.setString(2, "Jack");
            ps.executeUpdate();
//出现异常
            System.out.println(100 / 0);
//给 rose 加钱
            ps = connection.prepareStatement("update account set balance = balance + ? where
                    name = ? ");
            ps.setInt(1, 500);
            ps.setString(2, "Rose");
            ps.executeUpdate();
//提交事务
            connection.commit();
            System.out.println("转账成功");
        } catch (Exception e) {
            e.printStackTrace();
            try {
//事务的回滚
                connection.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            System.out.println("转账失败");
        } finally {
//7) 关闭资源
            JdbcUtils.close(connection, ps);
        }
    }
}