JDBC代码实现之第五版:利用PreparedStatement替代Statement执行SQL
前言
由JDBC代码实现之第四版:利用连接池重构DBTool工具类 从服务器创建与数据库的连接方面,对代码进行了优化,从而得到了DBUtil工具类。下面再从执行SQL语句方面,利用PreparedStatement替代Statement对象,更进一步的提高代码的执行效率。
1.利用Statement和PreparedStatement执行SQL的原理
- Statement和PreparedStatement(PS)是JDBC这项技术的一部分。它们都是接口对象,且PS继承于Statement,我们只需使用它们内部的方法,而无需去关心它们底层的具体实现。连接的管理者会帮我们实例化连接,连接再实例化其他对象,这些都是由工具来做,因为不同的数据库,具体的实现类就不同,我们无需去管,也管不起,只要知道接口有什么方法怎么用就行。
- 它们底层的工作原理都和数据库有些关系,因为它们都是要执行SQL的,而执行SQL就必须要访问数据库。二者之间的联系在于它们的继承关系,且都是用来执行SQL的。然后其区别为,Statement适合执行静态SQL,PS适合执行动态SQL。没有条件的SQL即为静态SQL,相反,有条件的SQL为动态SQL(比如select * from emp;没有where条件可视为静态的,但不一定必须带有where语句,只要SQL中有可变参数即可视为动态的)。
- 首先创建连接,实例化Connection对象,然后利用它与数据库建立连接;建立连接以后,第二步,利用这个连接对象再创建一个新的对象Statement或者PS对象,它们的作用都是用来执行SQL;第三步,通过Statement或者PS对象向数据库发送SQL;第四步,数据库接收到SQL以后,首先要检查SQL有没有语法问题;如果没有问题,第五步,数据库将SQL编译成计划;第六步,执行这个计划。
- 数据库执行SQL的过程:数据库接收到Statement传过来的数据以后
- 检查sql:它先检查sql,检查sql的主要的目的,是看一下这个sql写的对不对,如果语法都不对,就不用执行,直接报错了,先检查语法。
- 编译成计划:检查完sql以后,如果没问题,也不是立刻执行,而是对sql编译,其实sql是需要编译的(类似Java代码编译成的是class文件),sql编译出来的叫做计划,那么数据库会把语法对的sql编译成计划。
- 执行计划:编译完之后,数据库它运行的是这个计划。然后,由这个计划给jdbc返回数据,而这个数据,还是由Statement接收。
- 在整个编译的过程中,编译成计划,它是相对比较耗时的。就是说,编译比较耗时,这是一个前提,再一个,如果jdbc给数据库,发送的两个sql是一样的,这个计划是可以复用的,这个计划编译好之后,会一直存到数据库上,然后相同sql,可以复用计划,关于计划,复用计划的效率会高,要尽量复用计划。这么3点原则。数据库的设计者是这样设计的。那介于这样的一个前提:
- 如果用Statement执行这样的一个SQL:比如先执行sql1是
select * from emps where empno=1;
,再执行sql2是,select * from emps where empno=2
,先后执行两个sql,它们的逻辑是一样的,但条件不同, 此时的数据库会创建两个计划,因为毕竟还有不同的地方。即当逻辑相同,仅仅是条件参数不同的sql,这样使用Statement去执行,数据库会创建多个计划,无法复用计划,效率偏低。所以,Statement不适合执行有条件的sql,它只适合执行没有条件的静态的sql。 - 如果用PreparedStatement来执行sql的话,这个SQL这样这样写,select * from emps where empno=?,不写死这个条件值,而是写个问号占位,将来再赋值,不管条件是几,但这个SQL被固定下来了,所以这个第3步,PreparedStatement传给传数据库的是一个带问号的sql,这个问号也编译到计划里面去,它是没有具体的条件值的,这个条件会在当计划编译好以后,想执行计划的时候,需要由PreparedStatement,再次访问数据库,再单传一遍这个条件,所以,PreparedStatemet执行的这个sql和条件是分开传的,分两次发送数据库,在第一次传SQL的条件中,用问号占位,第二次单传一遍条件,这个条件传给数据库这个计划,不用再重新编译了,它只编译一次计划,无论将来传什么条件,那么当前的SQL是固定的,它可以复用一个计划,最后执行计划,然后由计划给JDBC返回结果。所以PS适合执行有条件的SQL,因为它可以让计划复用,能够提高效率。而Statement这个对象是没有能力执行带有问号的SQL的,所以在执行动态SQL时,就必须把条件写上去。
- 如果用Statement执行这样的一个SQL:比如先执行sql1是
2.利用PreparedStatement执行SQL语句
通过对Statement与PS对象的分析,它们之间唯一不同的地方就是,在执行动态语句时,PS需要将SQL与条件分两次传给数据库。其中SQL部分的逻辑与代码和使用Statement基本相同,只不过将Statement对象换成PS对象而已。首先需要创建并获取连接,由连接创建PS对象,编写SQL,并利用PS对象向将SQL语句发送给数据库,然后执行SQL,最后在finally中将连接归还给连接池。对于Statement对象而言,以上步骤便完成了JDBC连接输出库的整个过程,但是对于PS对象来说,传入的SQL语句的条件并未写死,而由问号占位,在执行SQL之前,还需要利用PS对象中相应的set方法对SQL中的条件进行逐一设置。
执行DML语句:
修改员工表emps,根据员工ID修改表中员工的名字,这个sql中就应该有两个条件,一个是修改后的员工名字为 张三丰,一个是员工id为1,假设修改后的员工名是用户需要在页面上输入的一个条件,而员工ID由服务器从用户的页面主动获取,在正式项目中这两个参数一定通过页面传过来的。服务器接收参数,再将得到的参数发送给数据库,拼到所执行的DML语句在数据库中所生成的编译计划里,最后执行计划。假设从页面获取的条件为:int empno = 1; String ename = "张三丰";
,那么这个sql语句应该写成:String sql = "update emps set ename=? where empno=?";
, 写完sql以后,将这个sql传给数据库生成编译计划(当我们创建ps对象时,是直接就发送了sql,发完sql以后,数据库就会编译这个sql):PreparedStatement ps = conn.prepareStatement(sql);
,在执行这个sql前要先发送条件,给sql中声明的问号赋值(此时只是把两个值,暂时存到了PS对象上,并没有立刻发送):ps.setString(1, ename); ps.setInt(2, empno);
,最后通过ps.executeUpdate();
方法向数据库发送条件参数,并让数据库执行sql,执行这个计划,另外,这个方法也会返回一个整数,这个数是执行此sql语句影响的行数,不过多数时候用不上这个行数,只要修改成功不报错就可以,无需接收这个返回值。完整代码如下:
/**
* 如何使用PS执行DML
*/
@Test
public void test() {
//假设页面传入的修改参数是:
int empno = 1;
String ename = "张三丰";
Connection conn = null;
try {
conn = DBUtil.getConnection();
String sql = "update emps set ename=? where empno=?";
//创建ps对象,并让它立刻发送SQL
PreparedStatement ps = conn.prepareStatement(sql);
//给参数?赋值
//ps.set类型(?的序号,?的值)
ps.setString(1, ename);
ps.setInt(2, empno);
//向数据库发送参数,并让数据库执行SQL
ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.close(conn);
}
}
执行DQL语句:
查询员工表emps:select * from emps;,带有一个条件,比如说查询公子高于6000的所有员工,在正式项目里,工资这个条件肯定也是页面传过来的,这里假设页面出入的查询条件为:double sal = 6000.0;
,那么这个sql语句应该写成:String sql = "select * from emps where sal>?";
,写完sql以后,将这个sql发送给数据库编译成计划:PreparedStatement ps = conn.prepareStatement(sql);
,在执行这个计划前要先给条件赋值,把条件参数暂存到PS对象里:ps.setDouble(1, sal);
,然后通过executeQuery发送PS对象中的参数,并执行执行计划,这个方法会返回一个结果集,最后遍历结果集得到数据:ResultSet rs = ps.executeQuery();
。完整代码示例:
/**
* 如何使用PS执行SQL
*/
@Test
public void test() {
//假设页面传入查询条件是
double sal = 6000.0;
Connection conn = null;
try {
conn = DBUtil.getConnection();
String sql = "select * from emps where sal>?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setDouble(1, sal);
ResultSet rs = ps.executeQuery();
while(rs.next()) {
System.out.println(rs.getInt("empno"));
System.out.println(rs.getString("ename"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.close(conn);
}
}
注:
- 在为带有问号的sql设置条件参数时,它的语法结构是这样的:
ps.setXxx(int n, xxx);
,set后面写类型,即set类型,括号里边第一个参数是整数,如果数字1,就代表第一个问号,如果数字2,就代表第二个问号,即问号的号;第二个参数是问号的值,一般是一个变量,通过变量对问号赋值。 - 当我们执行完sql把连接close关闭了,这里因为使用了连接池,实际上是把连接归还给连接池,连接池会把连接中的一切数据清空,无论是由连接创建出来的PS对象也好,还是ResultSet结果集也好,凡是由连接创建而来的内容都会被自动清空。
- Statement和PS对象的区别在于,前者适合执行没有条件的静态SQL,而后者适合执行动态的SQL,PS执行动态SQL的效率会有所提高,因为它能够让计划复用,但如果SQL是静态的,没有任何条件,没有任何参数,还是建议用Statement对象执行。如果SQL有任何一个条件或参数,建议用PS执行。
3.SQL Injection
PreparedStatement对象除了可以执行动态SQL时复用计划,还有一个优点,它能够避免被注入攻击。
准备工作:
首先建立一个users用户表,给它插入一条默认数据,然后commit提交。
create table users(
id number(8),
username varchar2(30),
password varchar2(30)
);
insert into users values(1,'tarena','123');
commit;
把用户表也建好以后,要通过模拟登录功能,演示SQL的注入攻击,登录功能主要是根据账号密码访问数据库查询数据是否存在于user用户表中。
登录功能
一般的登录功能,都是在网页上输入账号密码,还得有一个登录按钮用来提交数据,用户输入完账号密码点击登录时,就把数据提交给了服务器, 服务器把数据作为SQL的查询条件,查询数据库表是否有与之匹配的数据存在,因为服务器是用java做的,java访问数据库,就需要用JDBC技术来实现,查询时需要写一个类似这样的sql语句:
select * from users where username='zhangsan' and password ='123'
其中,username='zhangsan’和password='123’的值会被当作用户传入的两个查询条件拼入这个SQL当中。
PreparedStatement对象执行SQL
- 如果是用PreparedStatement执行SQL,这个sql中的条件是可以声明为问号的:
select * from users where username=? and password =?
把带有问号的sql发送给数据,先编译成计划,这个计划一旦被编译了,后面无论传入什么条件,条件中有没有or,数据库都不允许改变这个sql的本意。数据库会认为那个or,就是个普通的字符,它的底层会进行转义,从而不会影响sql原有的逻辑结构。 - 对JDBC而言,SQL注入攻击只对Statement有效,对PreparedStatement无效,因为PreparedStatement不允许在插入参数时改变SQL语句的逻辑结构使用与编译的语句对象,用户传入的任何数据不会和原SQL语句发生匹配关系,无需对输入的数据做过滤如果用户将" or1 = 1" 传入赋值给占位符,SQL语句将无法执行
select * from users where username = ? and password = ?;
Statement执行SQL
如果是用Statement执行sql,首先得先写出sql的结构,因为查询的条件都是字符串,条件外面要写俩个单引号:
select * from users where username='' and password =''
然后通过字符串拼接,把页面传入的条件账号密码,直接拼到sql里面去,比如账号等于zhangsan,密码为123,硬拼成如下的sql语句:
select * from users where username='zhangsan' and password ='123'
其中账号密码源于浏览器页面,而外面的单引号是拼接sql时,为了sql的语法结构,自己写上去的,最后再把用户输入的账号密码,硬拼到这个单引号儿里。当账号密码正确时是一定可以查出数据的,但这样的拼接方式是有漏洞的,因为用Statement对象执行SQL时,不允许写问号,如果一个用户懂技术,他传入一个特殊密码,比如这样的是,a' or 'b'='b
,这是万能密码,很厉害的:
select * from users where username='tarena' and password ='a or 'b'=b'
如果拼进来这个特殊密码,这就很巧妙地把这个sql原有的逻辑结构破坏了,本来没有or,结果却硬拼出来一个or,而or的右侧是一个恒等式,导致无论账号对不对,密码对不对,因为,'b'='b'
,恒等式(当然也可以是'c'='c'
),所以就一定能查的到数据,得到数据,影响查询结果。这就是SQL的注入攻击。当然想要解决这个问题,也不是说非得用ps对象,
就用Statement也能解决,只要判断一下传入的密码里不允许有or,强制不让用户写or,比如利用正则表达式判断下语法,也可以解决,但毕竟还是麻烦了一些。
验证PreparedStatement避免注入攻击
模拟登录案例,演示PS防止注入攻击:假设页面传入的登录条件账号和万能密码。利用PS对象将条件赋值给带有问号占位符的DQL的SQL语句,并执行,执行查询以后无需遍历,判断一下即可。如果查到数据,输出登录成功,认为账号密码对了,反之,登录失败,认为账号密码不对,执行测试,输出登录失败,说明万能密码不好使,就证明了用PS对象来执行SQL是靠谱的,不会受到攻击。
/**
* 使用PS执行查询语句,看它能否避免注入攻击。
*/
@Test
public void test() {
//假设页面传入的登录条件是:
String username = "zhangsan";
String password = "a' or 'b'='b";
Connection conn = null;
try {
conn = DBUtil.getConnection();
String sql = "select * from users where username=? and password=?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, username);
ps.setString(2, password);
ResultSet rs = ps.executeQuery();
if(rs.next()) {
System.out.println("登录成功");
} else {
System.out.println("登录失败");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.close(conn);
}
}
4.ResultSet结果集的深入理解
结果集指针
结果集ResultSet是用来封装查询到的结果数据的一个集合。我们利用JDBC查询到的数据会被封装到这个结果集里,它封装的方式就类似一个多行多列表格形式。这个结果集内部是有指针的,指针实际上就是个变量,我们用这个变量指向结果集中数据的某一行,所以通常习惯于管它叫做指针。这个指针默认指向的位置是表格数据的第一行之上,它指向的是一个空行,所以直接从结果集中取数,是取不到任何数据的。那如果想取到某一行数据,就必须让指针的位置指向这一行,而结果集中的next()方法的作用就是让指针指向下一行,每次调用next()方法,指针就向下移动一行,它就指向了那一行数据,当我们通过while语句遍历结果集时,每次通过rs.next()顺序获取下一行数据,直到指针指向了最后一行,再next(),指针将超出范围,指向最后一行之下,此时指针又指向了一个空行,也取不到任何数据,读取数据为空,循环结束。结果集由指针来管理数据的读取。而当指针指向某一行时,通过相应的get类型方法获取这一行数据某一列的值,get时候会指定那个列名,或者是这一列的序号进去取值。
结果集元数据
ResultSetMetaData,翻译过来叫结果集元数据,其中meta是元的意思,有元数据这个概念在其他语言当中,包括java语言中的其他的地方,有的时候也会遇到元素据这个词,工作时也经常会看到某个地方说元数据,而理解什么是元数据并不是那么特别容易的,因为元这个词,它挺抽象的,这个汉语是博大精深的,你像这个元,在很多语境当中不好解释,但是反正能理解,但是说不出来,只可意会,不可言传。比如说元气元神,其实他里面这个元和我们这个差不多,还有这个元始天尊啥的,元旦应该跟这个没啥关系,如果非要说他做一个解释的话,反正强解释也行,未必很准确,就是体会一下这个Meta元的理解是,一个事物的根本,一个事物的本质,或者你有这么的感觉,元气元神,给人感觉是最重要的一个东西,是一个根本。然后,MetaData元数据指的是数据的根本,也就是数据的概述信息,数据的概述,对数据的描述。比如一幅画,这个画作者是谁,尺幅多大,多宽,多长,然后它是什么类型的画,油画,国画,还是什么什么东西,我们把墙上的这幅画看成是一份数据,那么作者,画的大小,画的类型是对这个数据的描述,那这样的数据,就是数据的概述信息,我们就可以称之为元数据,所以元数据就是数据的描述信息,数据的概述信息。比如说,教室的元数据,是对教室的描述,这个结果集的元数据,就是对结果集的描述,概述信息。那么网页的元数据是对网页的描述,以此类推。这个是可以泛指的,总之,这个对象是对结果集的描述,通过结果集能得到它。
代码示例
演示ResultSetMetaData对象,首先利用JDBC技术,由Statement对象访问数据库,查询员工表中的所有数据,并根据员工的ID进行排序,获得一个结果集,通过结果集得到结果集中的元数据,看看里面有什么。那么结果集中有一个方法叫getMetaData()可以得到结果集的元数据,这个方法的返回值类型为ResultSetMetaData,它里面封装了对结果集的描述信息,这个元数据里有很多的内容,如果想用的话,可以查一下它的API文档有介绍,比如getColumnCount()这个方法可以得到结果集中一共有多少列数据,getColumnTypeName(int n)方法可以得到结果集中第n列数据的类型。
/**
* ResultSetMetaData
*/
@Test
public void test5() {
Connection conn = null;
try {
conn = DBUtil.getConnection();
String sql = "select * from emps order by empno";
Statement smt = conn.createStatement();
ResultSet rs = smt.executeQuery(sql);
//获取结果集元数据
ResultSetMetaData md = rs.getMetaData();
//元数据中封装了结果集的描述信息
System.out.println(md.getColumnCount());
System.out.println(md.getColumnTypeName(2));
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.close(conn);
}
}
可滚动结果集
关于结果集还有一种,叫做可滚动结果集,更准确来说,应该叫可跳动结果集,结果集是有指针的,并且指针默认指向第一行之上,每次next(),向下一行,是连续的,而可滚动结果集,这个可以让这个指针不连续,可以直接从第2行跳到200行,从100行跳到1000行,随便跳跃,得到这样的结果集,在生命Statement或PS语句对象时有所区别,但由于这种可滚动结果集的执行效率极低,几乎从来都不会被使用。这是因为它跟数据库查询数据的原则有关系,比如说访问数据库,查询表中的全部数据,假设表里有1w条数据,数据库其实很聪明,它并不会一下把1w条数据都给我,如果它直接给我1w条数据的话,我把这1w条数据得到,存到我的内存里,很耗内存。如果觉得1w条不过瘾的话,那就100w条给你,你把100w条数据存到你的内存里,接收到的对象里,那是很耗内存的,搞不好,一下你的服务器就崩溃了,所以数据库有这么一个机制,如果它发现数据太多,它会先给你一部分,比如1000条,当你next()处理到900条的时候,它发现快不够用了,在给你1000条,这样,我们是一批一批处理的,得到的数据,有一个缓冲。每次调用next()方法,按照次序滚动,这样数据库有个预判,发现1000条快用完了,再申请,再来另外1000条。反过来,如果是可跳跃的,我有可能从此一条,直接跳到最后一行,数据库是没法预判的,它不知道你要去哪一行,所以,它只能一下子把所有数据都给你,即便是数据,这个表里有100条,也得都给你,所以我们从来都不会这样用,它可跳动,但不好用。
5.Tips
结果集元数据当中封装的不是具体数据,不是那个什么员工张三李四,它封装的是对这份数据的描述,对这份数据的概括,一般工作时,用它的可能性不是很大,可能大部分人不会用到,因为我们常规的业务,什么增删改查等等用不上,一些比较高级的业务中才能用上。高级到什么程度,我给你举个例子,以前我在xxx的时候,我们公司开发用的不是那些开源的框架,它也不用spring,不用什么mybatis,框架自己写,写的比那个开源的框架还好,写那框架花了好几个亿,那程序员成本很高的,写到什么程度啊,就是我们在eclipse当中,我们自己也做了些插件,你只要点那个插件,通过插件界面选择一张表或几张表,然后,点下一步,下一步,下一步,当然每一步都有一些配置,最后配完之后,确定,这个插件就会根据这张表,生成和这个表相关的一切代码,包括服务器端,访问数据库的jdbc相关的代码,也包括一些处理请求的代码,什么叫处理请求的代码,包括网页,全都生成了,你直接一启动就能用了,增删改查就好使了,不过呢,他里面业务没有处理,因为他不知道是什么业务,我们去填业务,那做这个插件,做这个工具,它是根据表生成的这套代码,那他就得分析,它这个表儿有几个字段,每个字段是什么类型,这个字段有多长,这个字段,是不是必输的,它分析这些内容,生成的这套代码,那么那个时候,他就必须得读结果集元数据,那个时候需要用到结果集元数据,说白了,那如果我们想写一个类似于这样的框架,或者是插件的话,你可能会用到这个东西,否则,基本上你用不上,所以这是个高级货,那高级货的话,虽然用的少,但是你也要了解,因为这个面试官,他可能愿意问这些高级货,因为面试官,面试你的人,他都不是初级程序员,他都是一个高级程序员,至少是个高级程序员,或者是设计,或者是项目经理什么的,他问的问题,不可能那么小白,都得有点儿深度,有点难度明白吧,尤其是上来几个问题,看看你水到底多深,一下就砸到底儿问,问的很深的一套问题,你答不上来再浅点,再答不上来再浅点,太浅了就不要你了,都这样的,所以说这个你得懂点,面试的时候,你多多少少知道点儿,跟他聊一聊,起码咱们有个话儿说,你别说问你啥,你一句话都没有,这个就比较尴尬,了解一下没有坏处。
参考文献(References)
文中如有侵权行为,请联系me。。。。。。。。。。。。。
文中的错误,理解不到位的地方在所难免,也请指教!在成长过程中,也将继续不断完善,不作为专业文章。不喜勿喷。