[疯狂Java]JDBC:PreparedStatement预编译执行SQL语句

1. SQL语句的执行过程——Statement直接执行的弊病:

    1) SQL语句和编程语言一样,仅仅就会普通的文本字符串,首先数据库引擎无法识别这种文本字符串,而底层的CPU更不理解这些文本字符串(只懂二进制机器指令),因此SQL语句在执行之前肯定需要编译的;

    2) SQL语句的执行过程:提交SQL语句 -> 数据库引擎对SQL语句进行编译得到数据库可执行的代码 -> 执行SQL代码;

    3) 现在再来看Statement的执行机制:

         i. Statement的execute系列方法直接将SQL语句作为参数传入并提交给数据库执行;

         ii. 也就是说每提交一次都需要先经过编译然后再执行;

         iii. 那么有一个最大的问题就是如果一条SQL语句需要再短时间内被反复执行,那么每次都需要经过编译这样不是效率非常非常低吗??

!!可能你会问哪有需要反复大量执行的相同语句呢?仔细一想可能是的,因此上面说的并不完全精确,精确地讲应该是反复执行一系列模型相似的语句,比如:

[sql] view plain copy

  1. insert into table1 values(1, "Peter");  

!你每次执行时只是values中的值不同,但是总体的语句还是insert into语句,那么你每次提交都需要编译岂不是会把大把时间浪费在编译上面了,非常不值;

2. PreparedStatement的预编译机制——类似于Properties配置文件:

    1) 通过Connection(conn)还可以得到另一种SQL语句对象,即PreparedStatement,该方法就是:PreparedStatement Connection.prepareStatement(String sql);

    2) 注意细节:这里就不是create了,而是准备一个SQL语句句柄,精确地讲是一个PreparedStatement语句句柄,并且创建该句柄时直接传入了SQL语句;

    3) 预编译机制:

         i. 调用prepareStatement时会直接将该SQL语句提交给数据库进行编译,得到的PreparedStatement句柄其实是一个预编译好的SQL语句;

         ii. 之后调用PreparedStatement的execute方法(其execute系列方法都是无参的),就直接将该预编译的语句提交给数据库直接运行而不需要再编译一次了;

         iii. 因此这种方法只需要编译一次就够了,后面就是直接提交执行无需再编译,因此效率最高;

    4) 而预编译语句最大的特点就是支持占位符(支持的占位符就是?,代表任意长度的字符串),比如:insert into table1 values(null, ?, ?);

!!也就是说可以用带占位符的SQL语句来创建预编译SQL句柄:PreparedStatement pstmt = conn.prepareStatement("insert into table1 values(null, ?, ?)");

!!这样的语句也能通过,也可以成功编译,并且可以再后期决定这些占位符具体的值,即使改变这些值后依然不需要编译而直接提交运行;

    5) 设定占位符具体的值:

        i. 可以使用PreparedStatement的setXxx方法设定预编译语句中占位符的值;

        ii. 其原型是这个模式的:void PreparedStatement setXxx(int parameterIndex, Xxx x);

        iii. Xxx几乎涵盖了所有Java基础类型(String、int、double、Date等等);

        iv. parameterIndex代表语句中第几个占位符(从1开始),而x就是具体设定的值;

    6) 设置好占位符的值之后无需编译可以直接提交执行;

!!这种机制其实是跟Properties配置文件完全一样,修改值后无需编译即可运行!

    7) 直接提交执行:

         i. 使用PreparedStatement的execute系列方法即可,和Statement的execute系列方法相对应,只不过无需SQL语句参数了,因为已经存在预编译的SQL语句了,因此都是无参的,就表示直接提交执行;

         ii. 方法:

             a. ResultSet PreparedStatement.executeQuery();

             b. int PreparedStatement.executeUpdate();

             c. boolean PreparedStatement.execute();

!!返回值的意义和Statement的完全相同;

3. 比较直接提交和预编译运行的执行效率(各执行100次):

[java] view plain copy

  1. public class Test {  
  2. private String driver;  
  3. private String url;  
  4. private String user;  
  5. private String pass;  
  6. private void insertUseStatement() throws Exception {  
  7. long start = System.currentTimeMillis();  
  8. try ( // 过了try块会直接释放连接资源
  9.             Connection conn = DriverManager.getConnection(url, user, pass);  
  10.             Statement stmt = conn.createStatement()  
  11.         ) {  
  12. for (int i = 0; i < 100; i++) {  
  13.                 stmt.executeUpdate("insert into student_table values(" + "null, '姓名" + i + "', 1)");  
  14.             }  
  15.             System.out.println("使用Statment耗时:" + (System.currentTimeMillis() - start));  
  16.         }  
  17.     }  
  18. private void insertUsePreparedStatement() throws Exception {  
  19. long start = System.currentTimeMillis();  
  20. try ( // 因此需要在另一个方法中重新连接
  21.             Connection conn = DriverManager.getConnection(url, user, pass);  
  22.             PreparedStatement pstmt = conn.prepareStatement("insert into student_table values(null, ?, 1)")  
  23.         ) {  
  24. for (int i = 0; i < 100; i++) {  
  25.                 pstmt.setString(1,  "姓名" + i);  
  26.                 pstmt.executeUpdate();  
  27.             }  
  28.             System.out.println("使用PreparedStatement耗时:" + (System.currentTimeMillis() - start));  
  29.         }  
  30.     }  
  31. public void init() throws Exception {  
  32.         Properties props = new Properties();  
  33.         props.load(new FileInputStream("mysql.ini"));  
  34.         driver = props.getProperty("driver");  
  35.         url = props.getProperty("url");  
  36.         user = props.getProperty("user");  
  37.         pass = props.getProperty("pass");  
  38.         insertUseStatement();  
  39.         insertUsePreparedStatement();         
  40.     }  
  41. public static void main(String[] args) throws Exception {  
  42. new Test().init();  
  43.     }  
  44. }  

!!可以看到预编译比直接提交少用很多时间;

4. 预编译SQL的安全性能:

    1) 首先最明显的一点就是Statement不支持占位符,因此SQL语句中包含可变内容时必须要进行字符串拼接,而字符串拼接不仅加大了编程的难度,降低了代码的可读性,而且非常容易发生因拼接错误而导致地极难发现的bug,因此从这点来看PreparedStatement更加安全;

    2) 其次是字符串拼接容易埋下SQL注入的漏洞:

         i. SQL注入是指黑客在应用程序端恶意地往查询信息中填写SQL语句实现入侵(因为客户端输入的要查询的信息往往都是一些正常信息,例如姓名、电话、学号等,没人会无聊地往里面输入代码之类的东西);

         ii. 一个典型的例子:比如SQL语句的目的是select * from member_table where name = input_name and pass = input_pass; input_name和input_pass是用户在客户端输入框中输入的账号名和登陆密码,如果该查询语句能查询到该用户(即返回记录不为空)就表示该用户登陆成功;

如果用预编译占位符来表示该语句就是:select * from member_table where name = ? and pass = ?;   // 然后后期用input_name和input_pass来填补占位符,这没什么问题

但如果用Statement拼接的方式来写该语句就是:"select * from member_table where name = '" + input_name + "' and pass = '" + input_pass +"'";,而此时如果黑客在任意一个输入框(账户名或者密码)中填入'or true or'(就比如账户名输入框吧),那么得到的结果就是:

select * from member_table where name = '' or true or '' and pass = '';

!!也就是说最后的逻辑表达式变成了name = ''、true、'' and pass = ''三者通过or连接在了一起,因为or了一个true因此整个where表达式的结果都是true,因此必然会select处记录,因此即使这样也可以正常登陆!!这就被成功入侵了

         iii. 这最主要是由于不带占位符的拼接必须要用单引号'来包裹SQL字符串,而占位符的填写无需单引号,JDBC会自动将Java变量转换成纯字符串然后再自动加上SQL单引号填入占位符中,即使填入的变量是String str = "'Lala'",那么JDBC也会将其中的单引号' '转化成纯字符单引号处理,而不会被当做SQL的特殊字符单引号'来处理,因为在SQL中单引号'是字符串常量符号!

5. 占位符使用问题注意:

    1) 占位符只能占位SQL语句中的普通值,决不能占位表名、列名、SQL关键字(select、insert等);

    2) 原因很简单,以为PreparedStatement的SQL语句是要预编译的,如果关键字、列名、表名等被占位那就直接代表该SQL语句语法错误而无法编译,会直接抛出异常,因此只有不影响编译的部分可用占位符占位!!

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏机器学习从入门到成神

Java知识点总结

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/sinat_35512245/articl...

1801
来自专栏Java帮帮-微信公众号-技术文章全总结

第二十九天-加强1-Junit&类加载&反射&Properties&BeanUtils&xml&动态代理&数据库【悟空教程】

第二十九天-加强1-Junit&类加载&反射&Properties&BeanUtils&xml&动态代理&数据库【悟空教程】

2127
来自专栏程序员叨叨叨

【PHP】Propel的使用,看这一篇就够了

本文为学习Propel框架使用的笔记,默认已经安装好Propel环境,若有读者不知如何安装Propel,可参考《听说你PHP配置Composer遇到了一些困境》...

3935
来自专栏IT笔记

Hibernate合并查询结果集为实体类

用过mybatis的小伙伴可能都知道,我们可以查询两个表的部分字段合并为一个实体。然而用了Hibernate这么久了,居然还不知道也有此神器。 ? hibern...

4246
来自专栏Ryan Miao

mysql插入日期 vs oracle插入日期

今天做oracle日期插入的时候突然开始疑惑日期是如何插入的。 用框架久了,反而不自己做简单的工作了。比如插入。 通常,新建一个表对象,然后绑定数据,前端for...

3119
来自专栏数据结构与算法

SPOJ1043 GSS1(线段树)

注意查询的时候不能按照以前的方式写,因为不知道变量的下界,最稳妥的办法就是判三种情况

1031
来自专栏chenssy

【死磕Sharding-jdbc】---结果合并

接下来以执行 SELECT o.*FROM t_order o whereo.user_id=10order byo.order_id desc limit 2...

1143
来自专栏PHP在线

mysql学习笔记

InnoDB引擎与MyISAM引擎 mysql是关系型数据库。其中的存储引擎可以show engines来查看。我的版本是5.6.26的,查看版本用selec...

2855
来自专栏xingoo, 一个梦想做发明家的程序员

AngularJS API之toJson 对象转为JSON

toJson()能把对象序列化为json 方法讲解 这个方法最多支持2个参数: angular.toJson(obj, pretty); obj 是想要转换的...

2485
来自专栏编程

Web工程下读取文件的几种方法

1、读取文件系统路径文件 * 一定要使用绝对路径 String fileName = getServletContext().getRealPath("/in...

2086

扫码关注云+社区

领取腾讯云代金券