JDBC代码实现之第五版

最新推荐文章于 2024-06-24 21:19:32 发布

Water Fan

最新推荐文章于 2024-06-24 21:19:32 发布

阅读量233

点赞数

分类专栏： JDBC

本文链接：https://blog.csdn.net/qq_41765518/article/details/104272856

版权

JDBC 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

JDBC代码实现之第五版：利用PreparedStatement替代Statement执行SQL

前言
1.利用Statement和PreparedStatement执行SQL的原理
2.利用PreparedStatement执行SQL语句
- 执行DML语句：
- 执行DQL语句：
3.SQL Injection
4.ResultSet结果集的深入理解
5.Tips
参考文献(References)

前言

由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时，就必须把条件写上去。

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。。。。。。。。。。。。。
~~文中的错误，理解不到位的地方在所难免，也请指教！在成长过程中，也将继续不断完善，不作为专业文章。不喜勿喷。~~

Water Fan

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
JDBC代码实现之第五版

JDBC代码实现之第五版：利用PreparedStatement替代Statement执行SQL前言1.利用Statement和PreparedStatement执行SQL的原理2.利用PreparedStatement执行SQL语句执行DML语句：执行DQL语句：5.6.8.总结参考文献(References)前言由JDBC代码实现之第四版：利用连接池重构DBTool工具类从服务器创建与数...
复制链接

扫一扫

专栏目录