写点什么

数据库 JDBC：PreparedStatement

用户头像
大规模数据处理学习者
关注
发布于: 2020 年 11 月 03 日
数据库JDBC：PreparedStatement

本文基于MySQL创建了一个shop数据库

CREATE DATABASE IF NOT EXISTS shop;

再创建一个Product表插入一些数据，为后面JDBC的执行创建数据：

-- 创建Product表	
CREATE TABLE IF NOT EXISTS Product(
	product_id		CHAR(4)			NOT NULL,
	product_name 	VARCHAR(100)	NOT NULL,
	product_type	VARCHAR(32)		NOT NULL,
	sale_price		INTEGER			DEFAULT 0,
	purchase_price	INTEGER,
	regist_date		DATE,
	PRIMARY KEY(product_id)
);
-- 插入数据
START TRANSACTION;
INSERT INTO Product VALUES ('0001', 'T恤衫', '衣服', 1000, 500, '2009-09-20');
INSERT INTO Product VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11');
INSERT INTO Product VALUES ('0003', '运动T恤', '衣服', 4000, 2800, NULL);
INSERT INTO Product VALUES ('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');
INSERT INTO Product VALUES ('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');
INSERT INTO Product VALUES ('0006', '叉子', '厨房用具', 500, NULL, '2009-09-20');
INSERT INTO Product VALUES ('0007', '擦菜板', '厨房用具', 880, 790, '2008-04-28');
INSERT INTO Product VALUES ('0008', '圆珠笔', '办公用品', 100, NULL,'2009-11-11');
COMMIT;

预处理语句介绍



SQL 预处理是一种特殊的 SQL 处理方式，它会预先根据 SQL 语句模板来生成对应的执行计划，而后只需携带 SQL 参数便能直接执行，提升了 SQL 执行的性能，是一种典型的空间换时间的算法优化。

由于28准则，绝大多数情况下，某需求某一条 SQL 语句可能会被反复调用执行，或者每次执行的时候只有个别的值不同（比如 select 的 where 子句值不同，update 的 set 子句值不同，insert 的 values 值不同）。如果每次都需要经过上面的词法语义解析、语句优化、制定执行计划等，则效率就会降低。所谓预编译语句就是将此类 SQL 语句中的值用占位符替代，视为将 SQL 语句模板化或者参数化，一般称Prepared Statements，是一种特殊的Statement，预编译语句的优势在于归纳为：一次编译、多次运行，省去了解析优化等过程；而且能防止 SQL 注入。

如果我们只是来查询或者更新数据的话，最好用PreparedStatement代替Statement，它具有如下优点：

  • 可以提高语句的执行性能

  • 安全性更好，可以防止SQL注入

  • 提高代码的可读性和可维护性



SQL预处理语句

MySQL将PREPARE、EXECUTE、DEALLOCATE统称为PREPARE STATEMENT，也称之为预处理语句。语法：

-- 定义预处理语句
PREPARE stmt_name FROM preparable_stmt;
-- 执行预处理语句
EXECUTE stmt_name [USING @var_name [, @var_name] ...];
-- 删除(释放)定义
{DEALLOCATE | DROP} PREPARE stmt_name;



假设，我们实现查询Product中purchase_price大于某个值的某个种类(product_type)商品信息

-- 预定义SQL模板
PREPARE stmt1 FROM 'SELECT * FROM Product WHERE purchase_price >= ? AND product_type = ?';
-- 设置SQL参数
SET @1 = 500;
SET @2 = '厨房用具';
-- 执行预处理语句
EXECUTE stmt1 USING @1, @2;
-- 删除Prepare定义
DROP PREPARE stmt1;

参数的占位符问题， MySQL 的占位符是 ?，而 PostgreSQL 的占位符则不同，它会根据参数的序列来依次定义，如第一个参数的占位符是$1，第二个参数的占位符则是$2。

JDBC执行Prepare

下面创建PreparedStatement，查询purchase_price大于某个值的某个种类(product_type)商品信息。

import java.sql.*;
public class Connection {
    public static void main(String[] args) {
        /* 1) PostgreSQL的连接信息 */
        String url = "jdbc:mysql://localhost:3306/shop?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&useSSL=false";
        String user = "root";
        String password = "123456a";
        try {
            /* 定义JDBC驱动 */
            Class.forName("com.mysql.cj.jdbc.Driver");
            /* 连接数据库 */
            java.sql.Connection con = DriverManager.getConnection(url, user, password);
            String sql = "SELECT * FROM Product WHERE purchase_price >= ? AND product_type = ?;";
            PreparedStatement prest = con.prepareStatement(sql);
            /* 向PreparedStatement写入参数 */
            prest.setInt(1, 500);
            prest.setString(2, "厨房用具");
						/* 执行查询 */
            ResultSet rs = prest.executeQuery();
            while (rs.next()) {
                String val = rs.getString("product_name");
                System.out.println("statement = " + prest.toString() + " product_name : " + val);
            }
  
						/* 切断与数据库的连接 */
            rs.close();
            prest.close();
            con.close();
        } catch(ClassNotFoundException e) {
            System.out.println("Got a ClassNotFoundException: " + e.getMessage());
            e.printStackTrace();
        } catch (SQLException e) {
            System.out.println("Got a SQLException: " + e.getMessage());
            e.printStackTrace();
        }
    }
}

运行结果：

statement = com.mysql.cj.jdbc.ClientPreparedStatement: SELECT * FROM Product WHERE purchase_price >= 500 AND product_type = '厨房用具'; product_name : 菜刀
statement = com.mysql.cj.jdbc.ClientPreparedStatement: SELECT * FROM Product WHERE purchase_price >= 500 AND product_type = '厨房用具'; product_name : 高压锅
statement = com.mysql.cj.jdbc.ClientPreparedStatement: SELECT * FROM Product WHERE purchase_price >= 500 AND product_type = '厨房用具'; product_name : 擦菜板



Prepare防止SQL注入

假设将PreparedStatement的第二个参数更改为厨房用品 OR 1 = 1，

prest.setString(2, "厨房用品 OR 1 = 1");

则，此时的执行语句将会被转化为：

SELECT * FROM Product WHERE purchase_price >= 500 AND product_type = '厨房用品 OR 1 = 1';

从而，导致查不到符合条件的数据。而假设是通过executeQuery来执行的查询

sql = "SELECT * FROM Product WHERE purchase_price >= 500 AND product_type =";
sql += "'厨房用具' OR 1 = 1"; //注意此处的厨房用具需要添加单引号，否则会报错
System.out.println("sql = " + sql);
Statement st = con.createStatement();
ResultSet st_rs = st.executeQuery(sql);
while (st_rs.next()) {
		String val = st_rs.getString("product_name");
		System.out.println("statement = " + sql + "product_name : " + val);
}
st_rs.close();
st.close();

SQL注入成功，可以查询到数据库中的信息：

statement = SELECT * FROM Product WHERE purchase_price >= 500 AND product_type ='厨房用具' OR 1 = 1product_name : T恤衫
statement = SELECT * FROM Product WHERE purchase_price >= 500 AND product_type ='厨房用具' OR 1 = 1 product_name : T恤衫
statement = SELECT * FROM Product WHERE purchase_price >= 500 AND product_type ='厨房用具' OR 1 = 1 product_name : 打孔器
statement = SELECT * FROM Product WHERE purchase_price >= 500 AND product_type ='厨房用具' OR 1 = 1 product_name : 运动T恤
statement = SELECT * FROM Product WHERE purchase_price >= 500 AND product_type ='厨房用具' OR 1 = 1 product_name : 菜刀
statement = SELECT * FROM Product WHERE purchase_price >= 500 AND product_type ='厨房用具' OR 1 = 1 product_name : 高压锅
statement = SELECT * FROM Product WHERE purchase_price >= 500 AND product_type ='厨房用具' OR 1 = 1 product_name : 叉子
statement = SELECT * FROM Product WHERE purchase_price >= 500 AND product_type ='厨房用具' OR 1 = 1 product_name : 擦菜板
statement = SELECT * FROM Product WHERE purchase_price >= 500 AND product_type ='厨房用具' OR 1 = 1 product_name : 圆珠笔


Prepare 虽然在每个数据库中的语法差异很大，但是一般情况下我们都不会手写 SQL，而是使用 ORM 框架来做。

-- 慕课课程:SQL Prepare



参考资料



划线
评论
复制
发布于: 2020 年 11 月 03 日阅读数: 24

版权声明: 本文为 InfoQ 作者【大规模数据处理学习者】的原创文章。

原文链接:【http://xie.infoq.cn/article/5b1ece04ca5dd91de10143618】。

本文遵守【CC-BY 4.0】协议，转载请保留原文出处及本版权声明。

JDBCSQL预处理PrepareStatement
用户头像

大规模数据处理学习者

关注

正向成长 2018.08.06 加入

想要坚定地做大规模数据处理(流数据方向)，希望结合结合批处理的传统处理方式，以及之后流批混合处理方向进行学习和记录。

评论

发布
暂无评论
Copyright © 2021, Geekbang Technology Ltd. All rights reserved. 极客邦控股（北京）有限公司 | 京 ICP 备 16027448 号 - 5京公网安备京公网安备 11010502039052号
数据库JDBC：PreparedStatement