JDBC（二）_ZenDei技術網路在線

1. PreparedStatement預編譯的語句對象預編譯語句PreparedStatement 是java.sql中的一個介面，它是Statement的子介面。通過Statement對象執行SQL語句時，需要將SQL語句發送給DBMS，由 DBMS首先進行編譯後再執行。預編譯語句和State ...

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 }