JDBC（二）

2020-01-25 16:00:29来源：博客园阅读 ()

JDBC（二）

1. PreparedStatement预编译的语句对象

预编译语句PreparedStatement 是java.sql中的一个接口，它是Statement的子接口。通过Statement对象执行SQL语句时，需要将SQL语句发送给DBMS，由 DBMS首先进行编译后再执行。预编译语句和Statement不同，在创建PreparedStatement 对象时就指定了SQL语句，该语句立即发送给DBMS进行编译。当该编译语句被执行时，DBMS直接运行编译后的SQL语句，而不需要像其他SQL语句那样首先将其编译,在执行。

2.PreparedStatement和Statement的区别

在用法上不一样：

① Statement是普通的语句对象，PreparedStatement是预编译的语句对象；

Statement创建对象时不传入SQL，PreparedStatement创建对象是需要传入SQL；

Statement在执行时需传入SQL，PreparedStatement执行时不需传入SQL。

② Statement是拼接字符串效果，PreparedStatement不用拼接字符串；

③ PreparedStatement执行效率高于Statement；

④ PreparedStatement不是拼接字符串可以防止SQL注入问题；

具体实现代码如下：

  1 package cn.yif.dao.impl;
  2 
  3 import cn.yif.domain.Student;
  4 import cn.yif.dao.IStudentDao;
  5 import cn.yif.domain.User;
  6 import cn.yif.utils.DBCPUtil;
  7 import cn.yif.utils.JDBCUtil;
  8 
  9 import java.sql.*;
 10 import java.util.ArrayList;
 11 import java.util.List;
 12 
 13 /**
 14  * @author Administrator
 15  * @create 2019-09-13-20:33
 16  */
 17 public class StudentDaoImpl implements IStudentDao {
 18     @Override
 19     public void insert(Student student) {
 20         Connection conn = null;
 21         PreparedStatement preStatement = null;
 22         try {
 23             //①加载（注册）驱动，这部分硬编码可以写也可以不写，jdbc4.0版本自带了
 24             //Class.forName("com.mysql.jdbc.Driver");
 25             //②获取连接
 26             //conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctest01", "root", "admin");
 27             conn = JDBCUtil.getInstance().getConnection();
 28             //③预编译的语句对象：提前编译好SQL语句
 29             String insertSql = "insert into jdbcdemo02 (name, age) values (?, ?)";
 30             preStatement = conn.prepareStatement(insertSql);
 31             preStatement.setString(1, student.getName());
 32             preStatement.setInt(2, student.getAge());
 33             //④执行SQL语句：执行时无需代入SQL
 34             preStatement.execute();
 35         }catch (Exception ex){
 36             ex.printStackTrace();
 37         }finally {
 38             //⑤释放资源
 39             JDBCUtil.getInstance().close(null, preStatement, conn);
 40         }
 41     }
 42 
 43     @Override
 44     public void update(Student student) {
 45         Connection conn = null;
 46         PreparedStatement preStatement = null;
 47         try {
 48             //①加载（注册）驱动
 49             //Class.forName("com.mysql.jdbc.Driver");
 50             //②获取连接
 51             //conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctest01", "root", "admin");
 52             conn = JDBCUtil.getInstance().getConnection();
 53             //③预编译的语句对象：提前编译好SQL语句
 54             String updateSql = "update jdbcdemo02 set name = ?, age = ? where id = ?";
 55             preStatement = conn.prepareStatement(updateSql);
 56             preStatement.setString(1, student.getName());
 57             preStatement.setInt(2, student.getAge());
 58             preStatement.setInt(3, student.getId());
 59             //④执行SQL语句：执行时无需代入SQL
 60             preStatement.execute();
 61         }catch (Exception ex){
 62             ex.printStackTrace();
 63         }finally {
 64             //⑤释放资源
 65             JDBCUtil.getInstance().close(null, preStatement, conn);
 66         }
 67     }
 68 
 69     @Override
 70     public void delete(Integer id) {
 71         Connection conn = null;
 72         PreparedStatement preStatement = null;
 73         try {
 74             //①加载（注册）驱动
 75             //Class.forName("com.mysql.jdbc.Driver");
 76             //②获取连接
 77             //conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctest01", "root", "admin");
 78             conn = JDBCUtil.getInstance().getConnection();
 79             //③预编译的语句对象：提前编译好SQL语句
 80             String deleteSql = "delete from jdbcdemo02 where id = ?";
 81             preStatement = conn.prepareStatement(deleteSql);
 82             preStatement.setInt(1, id);
 83             //④执行SQL语句：执行时无需代入SQL
 84             preStatement.execute();
 85         }catch (Exception ex){
 86             ex.printStackTrace();
 87         }finally {
 88             //⑤释放资源
 89             JDBCUtil.getInstance().close(null, preStatement, conn);
 90         }
 91     }
 92 
 93     @Override
 94     public Student queryOne(Integer id) {
 95         Connection conn = null;
 96         PreparedStatement preStatement = null;
 97         ResultSet resultSet = null;
 98         Student student = null;
 99         try {
100             //①加载（注册）驱动
101             //Class.forName("com.mysql.jdbc.Driver");
102             //②获取连接
103             //conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctest01", "root", "admin");
104             conn = JDBCUtil.getInstance().getConnection();
105             //③预编译的语句对象：提前编译好SQL语句
106             String queryOneSql = "select * from jdbcdemo02 where id = ?";
107             preStatement = conn.prepareStatement(queryOneSql);
108             preStatement.setInt(1, id);
109             //④执行SQL语句：执行时无需代入SQL
110             resultSet = preStatement.executeQuery();
111             if(resultSet.next())
112             {
113                 student = new Student();
114                 student.setId(resultSet.getInt("id"));
115                 student.setName(resultSet.getString("name"));
116                 student.setAge(resultSet.getInt("age"));
117             }
118         }catch (Exception ex){
119 
120         }finally {
121             //⑤释放资源
122             JDBCUtil.getInstance().close(resultSet, preStatement, conn);
123         }
124         return student;
125     }
126 
127     @Override
128     public List<Student> queryAll() {
129         Connection conn = null;
130         PreparedStatement preStatement = null;
131         ResultSet resultSet = null;
132         Student student = null;
133         List<Student> list = new ArrayList<Student>();
134         try {
135             //①加载（注册）驱动
136             //Class.forName("com.mysql.jdbc.Driver");
137             //②获取连接
138             //conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctest01", "root", "admin");
139             conn = JDBCUtil.getInstance().getConnection();
140             //③获取语句对象：用来执行SQL语句
141             String queryAllSql = "select * from jdbcdemo02";
142             preStatement = conn.prepareStatement(queryAllSql);
143             //④执行SQL语句
144             resultSet = preStatement.executeQuery(queryAllSql);
145             while (resultSet.next())
146             {
147                 student = new Student();
148                 student.setId(resultSet.getInt("id"));
149                 student.setName(resultSet.getString("name"));
150                 student.setAge(resultSet.getInt("age"));
151                 list.add(student);
152             }
153         }catch (Exception ex){
154             ex.printStackTrace();
155         }finally {
156             //⑤释放资源
157             JDBCUtil.getInstance().close(resultSet, preStatement, conn);
158         }
159         return list;
160     }

3.登录方式实现

这里采用简单模拟的方式，比较Statement与PreparedStatement的不同，以及SQL注入问题带来的影响：

登录实现代码：

 1  @Override
 2     public User Login1_PreparedSt(String userName, String passWord) {
 3         Connection conn = null;
 4         PreparedStatement preStatement = null;
 5         ResultSet resultSet = null;
 6         User user = null;
 7         try {
 8             conn = JDBCUtil.getInstance().getConnection();
 9             String queryOneSql = "select * from jdbcdemo02_user where username = ? and password = ?";
10             preStatement = conn.prepareStatement(queryOneSql);
11             preStatement.setString(1, userName);
12             preStatement.setString(2, passWord);
13             resultSet = preStatement.executeQuery();
14             if(resultSet.next())
15             {
16                 user = new User();
17                 user.setId(resultSet.getInt("id"));
18                 user.setUsername(resultSet.getString("username"));
19                 user.setPassword(resultSet.getString("password"));
20                 user.setAge(resultSet.getInt("age"));
21                 user.setSex(resultSet.getInt("sex"));
22             }
23         }catch (Exception ex){
24 
25         }finally {
26             //⑤释放资源
27             JDBCUtil.getInstance().close(resultSet, preStatement, conn);
28         }
29         return user;
30     }
31 
32     @Override
33     public User Login2_PreparedSt(String userName) {
34         Connection conn = null;
35         PreparedStatement preStatement = null;
36         ResultSet resultSet = null;
37         User user = null;
38         try {
39             conn = JDBCUtil.getInstance().getConnection();
40             String queryOneSql = "select * from jdbcdemo02_user where username = ?";
41             preStatement = conn.prepareStatement(queryOneSql);
42             preStatement.setString(1, userName);
43             resultSet = preStatement.executeQuery();
44             if(resultSet.next())
45             {
46                 user = new User();
47                 user.setId(resultSet.getInt("id"));
48                 user.setUsername(resultSet.getString("username"));
49                 user.setPassword(resultSet.getString("password"));
50                 user.setAge(resultSet.getInt("age"));
51                 user.setSex(resultSet.getInt("sex"));
52             }
53         }catch (Exception ex){
54 
55         }finally {
56             //⑤释放资源
57             JDBCUtil.getInstance().close(resultSet, preStatement, conn);
58         }
59         return user;
60     }
61 
62     @Override
63     public User Login1_St(String userName, String passWord) {
64         Connection conn = null;
65         Statement statement = null;
66         ResultSet resultSet = null;
67         User user = null;
68         try {
69             conn = JDBCUtil.getInstance().getConnection();
70             statement = conn.createStatement();
71             String queryOneSql = "select * from jdbcdemo02_user where username = '"+userName+"' and password = '"+passWord+"'";
72             resultSet = statement.executeQuery(queryOneSql);
73             if(resultSet.next())
74             {
75                 user = new User();
76                 user.setId(resultSet.getInt("id"));
77                 user.setUsername(resultSet.getString("username"));
78                 user.setPassword(resultSet.getString("password"));
79                 user.setAge(resultSet.getInt("age"));
80                 user.setSex(resultSet.getInt("sex"));
81             }
82         }catch (Exception ex){
83 
84         }finally {
85             //⑤释放资源
86             JDBCUtil.getInstance().close(resultSet, statement, conn);
87         }
88         return user;
89     }

后台使用SQL注入测试代码：

 1 @org.junit.Test
 2     public void Login1_PreparedSt() {
 3         User test1 = new User();
 4         //正常情况：未有SQL注入
 5         test1.setUsername("李华");
 6         test1.setPassword("123456");
 7         test1.setSex(2);
 8         //异常情况：有SQL注入
 9 //        test1.setUsername(" 'or 1=1 or' ");
10 //        test1.setPassword("456");
11 //        test1.setSex(2);
12         IStudentDao userDao = new StudentDaoImpl();
13         User user = userDao.Login1_PreparedSt(test1.getUsername(), test1.getPassword());
14         if(user != null){
15             System.out.println(String.format("恭喜%s登录成功！", user.getUsername()));
16         }
17         else {
18             System.out.println("用户名或密码错误，登录失败！");
19         }
20     }
21 
22     @org.junit.Test
23     public void Login2_PreparedSt() {
24         User test1 = new User();
25         test1.setUsername("李华");
26         test1.setPassword("123456");
27         test1.setSex(2);
28         IStudentDao userDao = new StudentDaoImpl();
29         User user = userDao.Login2_PreparedSt(test1.getUsername());
30         if(user != null){
31             if(user.getPassword().equals(test1.getPassword())){
32                 System.out.println(String.format("恭喜%s登录成功！", user.getUsername()));
33             }
34             else {
35                 System.out.println("密码错误，登录失败！");
36             }
37         }
38         else {
39             System.out.println("用户名错误，登录失败！");
40         }
41     }
42 
43     @org.junit.Test
44     public void Login1_St() {
45         User test1 = new User();
46         //正常情况：未有SQL注入
47         //test1.setUsername("李华");
48         //test1.setPassword("123456");
49         //test1.setSex(2);
50         //异常情况：有SQL注入
51         test1.setUsername(" 'or 1=1 or' ");
52         //这里就是一条拼接的SQL -- select * from jdbcdemo02_user where username = ''or 1=1 or'' and password = 12344
53         test1.setPassword("145");
54         test1.setSex(2);
55         IStudentDao userDao = new StudentDaoImpl();
56         User user = userDao.Login1_St(test1.getUsername(), test1.getPassword());
57         if(user != null){
58             System.out.println(String.format("恭喜%s登录成功！", user.getUsername()));
59         }
60         else {
61             System.out.println("用户名或密码错误，登录失败！");
62         }
63     }

4.连接池

4.1.连接池介绍

连接池：简单地说，就是用来装连接对象的容器。

背景：在高并发访问的网页上，每次请求都会创建一个connection，因此会非常浪费资源（内存），当同时1000人访问的时候，那就会占用很多资源，因此浪费很多时间并且导致容器操作系统崩溃。

而连接池里面取connection则只需要从连接池里面拿到，不需要用户名和密码，用完之后，还回到连接池。

在Java中，在Java中,连接池使用javax.sql.DataSource接口来表示连接池. 这里的DataSource就是连接池。连接池就是DataSource。DataSource是接口，和JDBC一样，是Sun公司开发的一套接口，需要各大厂商去实现：导入各大厂商对应的jar包；

常用的DataSource的实现有下面两种方式:

DBCP: Spring推荐的（Spring框架已经集成DBCP）

C3P0: Hibernate推荐的(早期)（Hibernate框架已经集成C3P0）

DBCP连接池配置文件

dbcp.properties

#连接字符串

url=jdbc:mysql://localhost:3306/jdbcdemo

#用户名

username=root

#密码

password=admin

#驱动的类路径

driverClassName=com.mysql.jdbc.Driver

#连接池启动时的初始值

initialSize=1

#连接池的最大值

maxActive=50

#连接池的最大空闲数

maxIdle=20

具体代码实现如下：

 1  @Override
 2     public Student queryOneByDBCP(Integer id) {
 3         Connection conn = null;
 4         PreparedStatement preStatement = null;
 5         ResultSet resultSet = null;
 6         Student student = null;
 7         try {
 8             //①加载（注册）驱动
 9             //Class.forName("com.mysql.jdbc.Driver");
10             //②获取连接
11             //conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctest01", "root", "admin");
12             conn = DBCPUtil.getInstance().getConnection();
13             //③预编译的语句对象：提前编译好SQL语句
14             String queryOneSql = "select * from jdbcdemo02 where id = ?";
15             preStatement = conn.prepareStatement(queryOneSql);
16             preStatement.setInt(1, id);
17             //④执行SQL语句：执行时无需代入SQL
18             resultSet = preStatement.executeQuery();
19             if(resultSet.next())
20             {
21                 student = new Student();
22                 student.setId(resultSet.getInt("id"));
23                 student.setName(resultSet.getString("name"));
24                 student.setAge(resultSet.getInt("age"));
25             }
26         }catch (Exception ex){
27 
28         }finally {
29             //⑤释放资源
30             DBCPUtil.getInstance().close(resultSet, preStatement, conn);
31         }
32         return student;
33     }

DBCPUtil.java文件：

 1 package cn.yif.utils;
 2 
 3 import org.apache.commons.dbcp.BasicDataSource;
 4 import org.apache.commons.dbcp.BasicDataSourceFactory;
 5 
 6 import javax.sql.DataSource;
 7 import java.io.IOException;
 8 import java.sql.*;
 9 import java.util.Properties;
10 
11 public class DBCPUtil {
12     //使用静态static类构造单例模式
13     private DBCPUtil(){ }
14     //在静态代码块中创建instance的实例，这里使用static静态变量来保存
15     private static DBCPUtil instance = null;
16     //每次都要加载驱动，这里定义一个Properties，把资源文件里面的内容读到Properties里面
17     private static Properties properties = null;
18     //定义一个连接池对象
19     //private static BasicDataSource ds = null;
20     private static DataSource ds = null;
21     //JDBCUtil类加载的时候，就加载注册驱动
22     static {
23         properties = new Properties();
24         try {
25             //当前线程类加载器加载获取properties文件
26             properties.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("jdbc.properties"));
27             //方式一：创建一个BasicDataSource对象来读取配置文件
28 //            ds = new BasicDataSource();
29 //            ds.setDriverClassName(properties.getProperty("driverClassName"));
30 //            ds.setUrl(properties.getProperty("url"));
31 //            ds.setUsername(properties.getProperty("username"));
32 //            ds.setPassword(properties.getProperty("password"));
33             //方式二：使用工厂DataSourceFactory来加载配置文件
34             try {
35                 ds = BasicDataSourceFactory.createDataSource(properties);
36             } catch (Exception e) {
37                 e.printStackTrace();
38             }
39         } catch (IOException e) {
40             e.printStackTrace();
41         }
42         instance = new DBCPUtil();
43     }
44 
45     public static DBCPUtil getInstance(){
46         return instance;
47     }
48 
49     //抽取获取连接Connection的方法
50     public Connection getConnection() throws SQLException {
51         //这里不再使用DriverManager，而应该使用BasicDataSource，从连接池中拿到这个连接
52         return ds.getConnection();
53     }
54 
55     //抽取JDBC关闭资源的close方法
56     public void close(ResultSet resultSet, Statement statement, Connection conn){
57         try {
58             if(resultSet != null){
59                 resultSet.close();
60             }
61         } catch (SQLException e) {
62             e.printStackTrace();
63         }
64         try {
65             if(statement != null){
66                 statement.close();
67             }
68         } catch (SQLException e) {
69             e.printStackTrace();
70         }
71         try {
72             if(conn != null){
73                 conn.close();
74             }
75         } catch (SQLException e) {
76             e.printStackTrace();
77         }
78     }
79 }