MySQL数据库学习笔记（九）----JDBC的ResultSet接口（查询操作）、PreparedStatement接口重构增删改查（含SQL注入的解释）

【正文】

首先需要回顾一下上一篇文章中的内容：MySQL数据库学习笔记（八）----JDBC入门及简单增删改数据库的操作

一、ResultSet接口的介绍：

对数据库的查询操作，一般需要返回查询结果，在程序中，JDBC为我们提供了ResultSet接口来专门处理查询结果集。

Statement通过以下方法执行一个查询操作：

ResultSet executeQuery(String sql) throws SQLException 

单词Query就是查询的意思。函数的返回类型是ResultSet，实际上查询的数据并不在ResultSet里面，依然是在数据库里，ResultSet中的next()方法类似于一个指针，指向查询的结果，然后不断遍历。所以这就要求连接不能断开。

ResultSet接口常用方法：

boolean next()     遍历时，判断是否有下一个结果 int getInt(String columnLabel) int getInt(int columnIndex) Date getDate(String columnLabel) Date getDate(int columnIndex) String getString(String columnLabel) String getString(int columnIndex)

 

二、ResultSet接口实现查询操作：

步骤如下：（和上一篇博文中的增删改的步骤类似哦）

1、加载数据库驱动程序：Class.forName(驱动程序类) 2、通过用户名密码和连接地址获取数据库连接对象：DriverManager.getConnection(连接地址,用户名,密码) 3、构造查询SQL语句 4、创建Statement实例：Statement stmt = conn.createStatement() 5、执行查询SQL语句，并返回结果：ResultSet rs = stmt.executeQuery(sql) 6、处理结果 7、关闭连接：rs.close()、stmt.close()、conn.close()

我们来举个例子吧，来查询下面的这个表：

新建工程JDBC02，依旧先导入jar包。然后新建类，完整版代码如下：

 1 package com.vae.jdbc;

 3 import java.sql.Connection;

 4 import java.sql.DriverManager;

 5 import java.sql.ResultSet;

 6 import java.sql.SQLException;

 7 import java.sql.Statement;

 9 public class JdbcQuey {

12 //数据库连接地址

13 private final static String URL = "jdbc:mysql://localhost:3306/JDBCdb";

14 //用户名

15 public final static String USERNAME = "root";

16 //密码

17 public final static String PASSWORD = "smyh";

18 //加载的驱动程序类（这个类就在我们导入的jar包中）

19 public final static String DRIVER = "com.mysql.jdbc.Driver";

21 public static void main(String[] args) {

22 // TODO Auto-generated method stub

23 query();

28 //方法：查询操作

29 public static void query(){

30 try {

31 Class.forName(DRIVER);

32 Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);

33 String sql = "select id,name,age,description from person";

34 Statement state = conn.createStatement();

35 //执行查询并返回结果集

36 ResultSet rs = state.executeQuery(sql);

37 while(rs.next()){ //通过next来索引：判断是否有下一个记录

38 //rs.getInt("id"); //方法：int java.sql.ResultSet.getInt(String columnLabel) throws SQLException

39 int id = rs.getInt(1); //方法：int java.sql.ResultSet.getInt(int columnIndex) throws SQLException

41 String name = rs.getString(2);

42 int age = rs.getInt(3);

43 String description = rs.getString(4);

44 System.out.println("id="+id+",name="+name+",age="+age+",description="+description);

46 rs.close();

47 state.close();

48 conn.close(); 

50 } catch (ClassNotFoundException e) {

51 e.printStackTrace();

52 } catch (SQLException e) {

53 e.printStackTrace();

56 }

关于代码的解释，可以看上一篇博客。上方代码的核心部分是37至45行。

37行：next()函数：通过next来索引，判断是否有下一个记录。一开始就指向内存的首地址，即第一条记录，如果返回值为true，指针会自动指向下一条记录。

38、39行：getInt(String columnLabel)或者getInt(int columnIndex)代表的是列的索引，参数可以是列的名字，也可以用编号来表示，我们一般采用后者。编号的顺序是按照33行sql语句中列的顺序来定的。

程序运行后，后台输出如下：

上一篇博客+以上部分，实现了对数据库的简单增删改查的操作。其实这种拼接的方式很不好：既麻烦又不安全。我们接下来进行改进。

 

三、使用PreparedStatement重构增删改查（推荐）

概念：表示预编译的SQL语句的对象。SQL语句被预编译并存储在PreparedStatement对象中。然后可以使用此对象多次高效地执行该语句。PreparedStatement是Statement的一个接口。

作用：灵活处理sql语句中的变量。

举例：

以下面的这张数据库表为例：

新建Java工程文件JDBC3。新建一个Person类，方便在主方法里进行操作。Person类的代码如下：

package com.vae.jdbc;

public class Person {

 private int id;

 private String name;

 private int age;

 private String description;

 public int getId() {

 return id;

 public void setId(int id) {

 this.id = id;

 public String getName() {

 return name;

 public void setName(String name) {

 this.name = name;

 public int getAge() {

 return age;

 public void setAge(int age) {

 this.age = age;

 public String getDescription() {

 return description;

 public void setDescription(String description) {

 this.description = description;

 public Person(int id, String name, int age, String description) {

 super();

 this.id = id;

 this.name = name;

 this.age = age;

 this.description = description;

 public Person(String name, int age, String description) {

 super();

 this.name = name;

 this.age = age;

 this.description = description;

 public Person() {

 super();

 @Override

 public String toString() {

 return "Person [id=" + id + ", name=" + name + ", age=" + age

 + ", description=" + description + "]";


上方是一个简单的Person类，并添加set和get方法以及构造方法，无需多解释。

插入操作：

现在在主类JDBCtest中实现插入操作，完整代码如下：



 1 package com.vae.jdbc;

 3 import java.sql.Connection;

 4 import java.sql.DriverManager;

 5 import java.sql.PreparedStatement;

 6 import java.sql.SQLException;

 8 public class JDBCtest {

11 //数据库连接地址

12 public final static String URL = "jdbc:mysql://localhost:3306/JDBCdb";

13 //用户名

14 public final static String USERNAME = "root";

15 //密码

16 public final static String PASSWORD = "smyh";

17 //驱动类

18 public final static String DRIVER = "com.mysql.jdbc.Driver";

21 public static void main(String[] args) {

22 // TODO Auto-generated method stub

23 Person p = new Person("smyhvae",22,"我是在Java代码中插入的数据");

24 insert(p);

29 //方法：使用PreparedStatement插入数据

30 public static void insert(Person p){

32 try {

33 Class.forName(DRIVER);

34 Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);

35 String sql = "insert into person(name,age,description)values(?,?,?)";

36 PreparedStatement ps = conn.prepareStatement(sql);

37 //设置占位符对应的值

38 ps.setString(1, p.getName());

39 ps.setInt(2, p.getAge());

40 ps.setString(3, p.getDescription());

42 ps.executeUpdate();

44 ps.close();

45 conn.close();

48 } catch (ClassNotFoundException e) {

49 e.printStackTrace();

50 } catch (SQLException e) {

51 e.printStackTrace();

52 } 

54 }



我们来看一下上面的代码是怎么实现代码的优化的：

30行：将整个person对象进去，代表的是数据库中的一条记录。

35行：问号可以理解为占位符，有几个问号就代表要插入几个列，这样看来sql代码就比较简洁。

38至40行：给35行的问号设值，参数1代表第一个问号的位置，以此类推。

然后我们在main主方法中给Person设具体的值（23行），通过insert()方法就插入到数据库中去了。数据库中就多了一条记录：



更新操作：

代码和上方类似，修改操作的方法如下：



 1 //方法：使用PreparedStatement更新数据

 2 public static void update(Person p){

 3 try {

 4 Class.forName(DRIVER);

 5 Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);

 6 String sql = "update person set name=?,age=?,description=? where id=?";

 7 PreparedStatement ps = conn.prepareStatement(sql);

 8 //设置占位符对应的值

 9 ps.setString(1, p.getName());

10 ps.setInt(2, p.getAge());

11 ps.setString(3, p.getDescription());

12 ps.setInt(4, p.getId());

14 ps.executeUpdate();

16 ps.close();

17 conn.close();

20 } catch (ClassNotFoundException e) {

21 e.printStackTrace();

22 } catch (SQLException e) {

23 e.printStackTrace();

25 }



因为在这里有四个问号的占位符，所以稍后再main方法中记得使用四个参数的Person构造方法，传递四个参数。

删除操作：

代码和上方类似，方法如下：



 1 //方法：使用PreparedStatement删除数据

 2 public static void delete(int id){

 3 try {

 4 Class.forName(DRIVER);

 5 Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);

 6 String sql = "delete from person where id=?";

 7 PreparedStatement ps = conn.prepareStatement(sql);

 8 //设置占位符对应的值

 9 ps.setInt(1, id);

11 ps.executeUpdate();

13 ps.close();

14 conn.close();

17 } catch (ClassNotFoundException e) {

18 e.printStackTrace();

19 } catch (SQLException e) {

20 e.printStackTrace();

22 }



这里的方法中，传入的参数是是一个id。

查询操作：



 1 // 使用PreparedStatement查询数据

 2 public static Person findById(int id){

 3 Person p = null;

 4 try {

 5 Class.forName(DRIVER);

 6 Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);

 7 String sql = "select name,age,description from person where id=?";

 8 PreparedStatement ps = conn.prepareStatement(sql);

 9 //设置占位符对应的值

10 ps.setInt(1, id);

12 ResultSet rs = ps.executeQuery();

13 if(rs.next()){

14 p = new Person();

15 p.setId(id);

16 p.setName(rs.getString(1));

17 p.setAge(rs.getInt(2));

18 p.setDescription(rs.getString(3));

19 //把 java.sql.Date 与 java.util.Date之间的转换

20 // java.util.Date date = rs.getDate(4);

21 // ps.setDate(4, new java.sql.Date(date.getTime()));

24 rs.close();

25 ps.close();

26 conn.close();

29 } catch (ClassNotFoundException e) {

30 e.printStackTrace();

31 } catch (SQLException e) {

32 e.printStackTrace();

34 return p;

35 }



查询操作稍微麻烦一点，在方法中传入的参数是id，方法的返回值是查询的结果，即Person类。

 

四、PreparedStatement小结：

在JDBC应用中，如果你已经是稍有水平开发者，你就应该始终以PreparedStatement代替Statement。也就是说，在任何时候都不要使用Statement。

基于以下的原因：

一、代码的可读性和可维护性

二、PreparedStatement可以尽最大可能提高性能

三、最重要的一点是极大地提高了安全性


如果使用Statement而不使用PreparedStatement，则会造成一个安全性问题：SQL注入

来看一下SQL注入是怎么回事。现在有如下的一张用户名密码表user：



我们在执行如下sql语句进行查询：

select id,name,pwd from user where name=xxx and pwd = x or 1=1

 

竟能出奇地查到所有的用户名、密码信息：



因为1=1永远是成立的，所以这句话永远都成立。所以在Java代码中，可以利用这个漏洞，将上方的蓝框部分内容当做pwd的变量的内容。来举个反例：使用Statement写一个登陆的操作：



 1 //登 录(Statement：会造成SQL注入的安全性问题)

 2 public static void login(String name,String pwd){

 3 Person p = null;

 4 try {

 5 Class.forName(DRIVER);

 6 Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);

 7 // String sql = "select id,name,pwd from user where name= and pwd=";

 9 StringBuffer sql = new StringBuffer("select id,name,pwd from user where name=");

10 sql.append(name).append(" and pwd=").append(pwd).append("");

11 Statement ps = conn.createStatement();

13 ResultSet rs = ps.executeQuery(sql.toString());

14 if(rs.next()){

16 rs.close();

17 ps.close();

18 conn.close();

21 } catch (ClassNotFoundException e) {

22 e.printStackTrace();

23 } catch (SQLException e) {

24 e.printStackTrace();

26 }



上方代码中的第10行就是采用字符串拼接的方式，就会造成SQL注入的安全性问题。

而如果使用PreparedStatement中包含问号的sql语句，程序就会先对这句sql语句进行判断，就不会出现字符串拼接的现象了。

 

五、完整版代码：

最后附上本文中，PreparedStatement接口重构增删改查的完整版代码：



 1 package com.vae.jdbc;

 3 import java.sql.Connection;

 4 import java.sql.DriverManager;

 5 import java.sql.PreparedStatement;

 6 import java.sql.ResultSet;

 7 import java.sql.SQLException;

 9 public class JDBCtest {

 12 //数据库连接地址

 13 public final static String URL = "jdbc:mysql://localhost:3306/JDBCdb";

 14 //用户名

 15 public final static String USERNAME = "root";

 16 //密码

 17 public final static String PASSWORD = "smyh";

 18 //驱动类

 19 public final static String DRIVER = "com.mysql.jdbc.Driver";

 22 public static void main(String[] args) {

 23 // TODO Auto-generated method stub

 24 Person p = new Person();

 25 //insert(p);

 26 //update(p);

 27 //delete(3);

 28 p = findById(2);

 29 System.out.println(p);

 30 }

 33 //方法：使用PreparedStatement插入数据

 34 public static void insert(Person p){

 36 try {

 37 Class.forName(DRIVER);

 38 Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);

 39 String sql = "insert into person(name,age,description)values(?,?,?)";

 40 PreparedStatement ps = conn.prepareStatement(sql);

 41 //设置占位符对应的值

 42 ps.setString(1, p.getName());

 43 ps.setInt(2, p.getAge());

 44 ps.setString(3, p.getDescription());

 46 ps.executeUpdate();

 48 ps.close();

 49 conn.close();

 52 } catch (ClassNotFoundException e) {

 53 e.printStackTrace();

 54 } catch (SQLException e) {

 55 e.printStackTrace();

 56 } 

 57 }

 60 //方法：使用PreparedStatement更新数据

 61 public static void update(Person p){

 62 try {

 63 Class.forName(DRIVER);

 64 Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);

 65 String sql = "update person set name=?,age=?,description=? where id=?";

 66 PreparedStatement ps = conn.prepareStatement(sql);

 67 //设置占位符对应的值

 68 ps.setString(1, p.getName());

 69 ps.setInt(2, p.getAge());

 70 ps.setString(3, p.getDescription());

 71 ps.setInt(4, p.getId());

 73 ps.executeUpdate();

 75 ps.close();

 76 conn.close();

 79 } catch (ClassNotFoundException e) {

 80 e.printStackTrace();

 81 } catch (SQLException e) {

 82 e.printStackTrace();

 83 }

 84 }

 87 //方法：使用PreparedStatement删除数据

 88 public static void delete(int id){

 89 try {

 90 Class.forName(DRIVER);

 91 Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);

 92 String sql = "delete from person where id=?";

 93 PreparedStatement ps = conn.prepareStatement(sql);

 94 //设置占位符对应的值

 95 ps.setInt(1, id);

 97 ps.executeUpdate();

 99 ps.close();

100 conn.close();

103 } catch (ClassNotFoundException e) {

104 e.printStackTrace();

105 } catch (SQLException e) {

106 e.printStackTrace();

107 }

108 }

111 // 使用PreparedStatement查询数据

112 public static Person findById(int id){

113 Person p = null;

114 try {

115 Class.forName(DRIVER);

116 Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);

117 String sql = "select name,age,description from person where id=?";

118 PreparedStatement ps = conn.prepareStatement(sql);

119 //设置占位符对应的值

120 ps.setInt(1, id);

122 ResultSet rs = ps.executeQuery();

123 if(rs.next()){

124 p = new Person();

125 p.setId(id);

126 p.setName(rs.getString(1));

127 p.setAge(rs.getInt(2));

128 p.setDescription(rs.getString(3));

129 //把 java.sql.Date 与 java.util.Date之间的转换

130 // java.util.Date date = rs.getDate(4);

131 // ps.setDate(4, new java.sql.Date(date.getTime()));

133 }

134 rs.close();

135 ps.close();

136 conn.close();

139 } catch (ClassNotFoundException e) {

140 e.printStackTrace();

141 } catch (SQLException e) {

142 e.printStackTrace();

143 }

144 return p;

145 }

148 }



 


  【SQL开发实战技巧】系列（二十一）：数据仓库中时间类型操作（进阶）识别重叠的日期范围，按指定10分钟时间间隔汇总数据 

 如何识别重叠的日期范围、日期出现次数、确定当前记录和下一条记录之间相差的天数【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作，毕竟SQL开发在数据分析场景非常重要且基础，面试也会经常问SQL开发和调优经验，相信当我写完这一系列文章，也能再有所收获，未来面对SQL面试也能游刃有余~。例如：以上就是今天要讲的内容，本文仅仅简单介绍了pandas的使用，而pandas提供了大量能使我们快速便捷地处理数据的函数和方法。


  【SQL开发实战技巧】系列（二十）：数据仓库中时间类型操作（进阶）获取季度开始结束时间以及如何统计非连续性时间的数据 

 本篇文章讲解的主要内容是：***汇总报表时常要求按季度分类汇总这就需要通过给定年份获取对应的季度开始结束时间、业务数据不连续的情况下如何统计所有年份数据、如何统计相同月份与周内日期聘用的员工、如何返回2月或12月聘用的所有员工以及周二聘用的所有员工***


  【SQL开发实战技巧】系列（十九）：数据仓库中时间类型操作（进阶）如何一个SQL打印当月或一年的日历？如何确定某月内第一个和最后—个周内某天的日期？ 

 如何一个SQL打印出当月日历或当年日历？？？如何统计一年内属于周内某一天的所有日期???如何确定某月内第一个和最后—个周内某天的日期？？？【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作，毕竟SQL开发在数据分析场景非常重要且基础，面试也会经常问SQL开发和调优经验，相信当我写完这一系列文章，也能再有所收获，未来面对SQL面试也能游刃有余~。本例要求返回当月内第一个星期一与最后一个星期一,我们分别找上月末及当月末之前七天的下一周周一即可。


  【SQL开发实战技巧】系列（十八）：数据仓库中时间类型操作（进阶）INTERVAL、EXTRACT以及如何确定一年是否为闰年及周的计算 

 日期操作函数(INTERVAL、EXTRACT)的使用以及如何确定一年是否为闰年及周的计算两个小案例【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作，毕竟SQL开发在数据分析场景非常重要且基础，面试也会经常问SQL开发和调优经验，相信当我写完这一系列文章，也能再有所收获，未来面对SQL面试也能游刃有余~。本章主要介绍的是关于时间类型的一些常规操作。


  【SQL开发实战技巧】系列（十七）：数据仓库中时间类型操作（初级）确定两个日期之间的工作天数、计算—年中周内各日期出现次数、确定当前记录和下一条记录之间相差的天数 

 如何确定两个日期之间的工作日有多少天、计算—年中每周内各日期出现次数、确定当前记录和下一条记录之间相差的天数【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作，毕竟SQL开发在数据分析场景非常重要且基础，面试也会经常问SQL开发和调优经验，相信当我写完这一系列文章，也能再有所收获，未来面对SQL面试也能游刃有余~。本章节的三个需求：确定两个日期之间的工作天数、计算—年中周内各日期出现次数、确定当前记录和下一条记录之间相差的天数有些许难度，不过建议还是学会比较好。


  【SQL开发实战技巧】系列（十六）：数据仓库中时间类型操作（初级）日、月、年、时、分、秒之差及时间间隔计算 

 日、月、年、时、分、秒之差及时间间隔计算。【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作，毕竟SQL开发在数据分析场景非常重要且基础，面试也会经常问SQL开发和调优经验，相信当我写完这一系列文章，也能再有所收获，未来面对SQL面试也能游刃有余~。本章介绍的关于时间的计算比较简单，主要是为了后面时间计算文章做铺垫！


 生命壹号

 个人网站：smyhvae.com。博客园：cnblogs.com/smyhvae。微信公众号：生命团队 | vitateam