JDBC02 利用JDBC連接資料庫

1/2/3 Statement 和 Preparedstatement 的區別

4 讀取properties設定檔

5 資料庫連接池

6 利用資料庫連接池連接資料庫

1 使用Statement執行含有動態資訊的SQL語句時有幾個不足: 1.1 由於需要將動態資料拼接到SQL語句中,這導致程式複雜度高,容易出錯

1.2 拼接的資料若含有SQL語法內容就會導致拼接後的SQL語法含義改變而出現SQL注入攻擊

1.3 當大批量執行語義相同,但是含有動態資料的SQL時效率很差

2 使用Statement執行SQL語句不好的原因 2.1 當執行一條SQL語句發送到資料庫時,資料庫先將該SQL解析並生成一個執行計畫(這個過程會消耗資源和性能)，如果多次執行一樣的SQL語句,資料庫會重用執行計畫,但是若多次執行語義相同但是含有動態資料的SQL時,資料庫會生成不同的執行計畫,嚴重影響資料庫的開銷

2.2 例如

執行 SELECT * FROM userifo_fury 生成一個執行計畫再次執行SELECT * FROM userifo_fury 就會重用上面的執行計畫(因為這是靜態的SQL語句

但是，執行INSERT INTO userifo VALUES(1, 'JACK','122314','141234@QQ.COM','FURY',15600) ) 生成一個執行計畫，再執行執行INSERT INTO userifo VALUES(2, 'rose','122314','141234@QQ.COM','FURY',15600)由於內容不同,會再次生成另外一個執行計畫，若執行1000次上述情況的INSERT,資料庫會產生1000個執行計畫，這樣就嚴重影響了資料庫的效率因此， Statement只適合執行靜態的SQL語句，不適合執行動態的SQL語句

3 利用PreparedStatement代替Statement 編寫簡單沒有SQL注入問題批量執行語義相同的SQL語句會重用執行計畫 1 package cn.xiangxu.entity; 2 3 import java.io.Serializable; 4 5 public class User implements Serializable { 6 7 private static final long serialVersionUID = -5109978284633713580L; 8 9 private Integer id; 10 private String name; 11 private String pwd; 12 public User { 13 super; 14 // TODO Auto-generated constructor stub 15 } 16 public User(Integer id, String name, String pwd) { 17 super; 18 this.id = id; 19 this.name = name; 20 this.pwd = pwd; 21 } 22 @Override 23 public int hashCode { 24 final int prime = 31; 25 int result = 1; 26 result = prime * result + ((id == null) ? 0 : id.hashCode); 27 return result; 28 } 29 @Override 30 public boolean equals(Object obj) { 31 if (this == obj) 32 return true; 33 if (obj == null) 34 return false; 35 if (getClass != obj.getClass) 36 return false; 37 User other = (User) obj; 38 if (id == null) { 39 if (other.id != null) 40 return false; 41 } else if (!id.equals(other.id)) 42 return false; 43 return true; 44 } 45 public Integer getId { 46 return id; 47 } 48 public void setId(Integer id) { 49 this.id = id; 50 } 51 public String getName { 52 return name; 53 } 54 public void setName(String name) { 55 this.name = name; 56 } 57 public String getPwd { 58 return pwd; 59 } 60 public void setPwd(String pwd) { 61 this.pwd = pwd; 62 } 63 @Override 64 public String toString { 65 return "User [id=" + id + ", name=" + name + ", pwd=" + pwd + "]"; 66 } 67 68 69 70 }

user表對應的實體類

1 package testJDBC; 2 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; 8 import java.util.ArrayList; 9 import java.util.List; 10 11 import org.junit.Test; 12 13 import cn.xiangxu.entity.User; 14 15 public class TestCase { 16 @Test 17 public void test01 { 18 Connection conn = null; 19 PreparedStatement ps = null; 20 ResultSet rs = null; 21 try { 22 Class.forName("com.mysql.jdbc.Driver"); // 載入資料庫驅動 23 24 conn = DriverManager.getConnection( // 初始化連線物件 25 "jdbc:mysql://localhost:3306/test", "root", "182838"); 26 27 28 String sql = "SELECT * FROM user WHERE pwd = ? "; // 拼接SQL語句，位置參數用？代替 29 30 ps = conn.prepareStatement(sql); // 初始化預編譯執行物件 31 32 ps.setString(1, "182838"); // 設置SQL語句中的位置位置參數（注意：是從1開始數不是從0開始數） 33 34 rs = ps.executeQuery; // 執行SQL語句 35 36 List users = new ArrayList; // 創建一個集合來存放記錄物件 37 while(rs.next) { // 遍歷結果集 38 // System.out.println("===================="); 39 // System.out.println(rs.getInt("id")); 40 // System.out.println(rs.getString("name")); 41 // System.out.println(rs.getString("pwd")); 42 User user = new User; 43 user.setId(rs.getInt("id")); 44 user.setName(rs.getString("name")); 45 user.setPwd(rs.getString("pwd")); 46 users.add(user); // 向集合中添加元素 47 } 48 49 System.out.println(users); // 列印輸出集合 50 for(User user : users) { 51 System.out.println(user); 52 } 53 54 // 釋放資源 55 rs.close; 56 ps.close; 57 conn.close; 58 59 } catch (Exception e) { 60 // TODO Auto-generated catch block 61 e.printStackTrace; 62 } finally { 63 if(rs != null) { 64 try { 65 rs.close; 66 } catch (SQLException e) { 67 // TODO Auto-generated catch block 68 e.printStackTrace; 69 } 70 } 71 if(ps != null) { 72 try { 73 ps.close; 74 } catch (SQLException e) { 75 // TODO Auto-generated catch block 76 e.printStackTrace; 77 } 78 } 79 if(conn != null) { 80 try { 81 conn.close; 82 } catch (SQLException e) { 83 // TODO Auto-generated catch block 84 e.printStackTrace; 85 } 86 } 87 } 88 89 } 90 91 }

使用預編譯Statement的實例

4 利用Properties物件讀取properties設定檔中的資訊 4.1 Properties繼承了Hashtable類， Properties物件也是使用鍵值對的方式來保存資料，但是Properties物件的鍵和值都是字串類型

class Properties extends Hashtable

4.2 Properties 類中的主要方法 4.2.1 public synchronized void load(InputStream inStream) throws IOException

將properties屬性檔的檔輸入流載入到Properties物件

4.2.2 public void store(OutputStream out, String comments) throws IOException

將Properties物件中的屬性清單保存到輸出流檔中

注意：第二個參數表示注釋資訊（注意：properties檔中不能用中文），在注釋資訊後面會自動添加一個時間資訊

注意：新創建的檔在專案的根目錄下面（問題：為什麼在eclipse中沒有，但是到資料夾中卻能找到？？？）

4.2.3 public String getProperty(String key)

獲取屬性值，參數是屬性的鍵

4.2.4 public synchronized Object setProperty(String key, String value)

修改屬性值，參數1是屬性的鍵，參數2是屬性的新值

4.3 案例

要求：讀取properties設定檔總的屬性值，將讀取到的屬性值進行修改後保存到另外一個properties設定檔中

1 package cn.xiangxu.entity; 2 3 import java.io.FileInputStream; 4 import java.io.FileOutputStream; 5 import java.io.InputStream; 6 import java.util.Iterator; 7 import java.util.Properties; 8 9 public class Test { 10 public static void main(String[] args) { 11 try { 12 Properties prop = new Properties; // 創建Properties對象 13 14 // prop.load(new FileInputStream("config.properties")); // 使用這種方式時，設定檔必須放在專案的根目錄下 15 InputStream is = Test.class.getClassLoader.getResourceAsStream("config/config.properties"); // 讀取屬性檔 16 17 prop.load(is); // 載入屬性清單 18 19 Iterator it=prop.stringPropertyNames.iterator; // 將設定檔中的所有key放到一個可反覆運算物件中 20 while(it.hasNext){ // 利用反覆運算器模式進行反覆運算 21 String key=it.next; // 讀取下一個反覆運算物件的下一個元素 22 System.out.println(key+":"+prop.getProperty(key)); // 根據key值獲取value值（獲取屬性資訊） 23 } 24 25 is.close; // 關閉輸入流，釋放資源 26 27 FileOutputStream oFile = new FileOutputStream("b.properties", true);//創建一個輸出流檔， true表示追加打開 28 prop.setProperty("maxactive", "33"); // 修改屬性資訊 29 prop.store(oFile, "zhe shi yi ge xin de shu xing pei zhi wen jian."); // 將Properties物件中的內容放到剛剛創建的檔中去 30 oFile.close; // 關閉輸出流，釋放資源 31 32 } catch (Exception e) { 33 // TODO Auto-generated catch block 34 e.printStackTrace; 35 } 36 } 37 }

讀取屬性設定檔資訊

等待讀取的properties設定檔的位置如下圖所示

5 資料庫連接池 5.1 什麼是資料庫連接池

程式啟動時就創建足夠多的資料庫連接，並將這些連接組成一個連接池，由程式自動地對池中的連接進行申請、使用、釋放

5.2 資料庫連接池的運行機制

》程式初始化時創建連接池

》需要操作資料庫時向資料庫連接池申請一個可用的資料庫連接

》使用完畢後就將資料庫連接還給資料庫連接池（注意：不是關閉連接，而是交給連接池）

》整個程式退出時，斷開所有連接，釋放資源（即：管理資料庫連接池的那個執行緒被殺死後才關閉所有的連接）

5.3 資料庫連接池的程式設計步驟 5.3.1 導包 5.3.2 聲明ThreadLocal、BasicDataSource成員變數（注意：這兩個成員變數是靜態的） 5.3.3 在靜態代碼塊中產生實體那兩個成員變數，並通過Properties物件讀取設定檔資訊，利用這些設定檔資訊給BasicDataSource物件進行初始化處理

5.3.4 編寫創建連接靜態方法

利用BasicDataSource物件產生實體一個連線物件

將這個連線物件放到ThreadLocal物件中

5.3.5 編寫釋放連接靜態方法

從ThreadLocal物件中獲取連線物件

清空ThreadLocal物件

判斷連線物件是否釋放

6 利用資料庫連接池操作資料庫

專案結構圖

1 # zhe shi zhu shi , yi ban bu yong zhong wen 2 # deng hao liang bian mei you kong ge, mo wei mei you fen hao 3 # hou mian bu neng you kong ge 4 driverClassName=com.mysql.jdbc.Driver 5 url=jdbc:mysql://localhost:3306/test 6 username=root 7 password=182838 8 maxActive=100 9 maxWait=3000

properties設定檔

1 2 4.0.0 3 cn.xiangxu 4 testJDBC 5 0.0.1-SNAPSHOT 6 7 8 mysql 9 mysql-connector-java 10 5.1.37 11 12 13 junit 14 junit 15 4.12 16 17 18 commons-dbcp 19 commons-dbcp 20 1.4 21 22 23

maven依賴檔

1 package cn.xiangxu.tools; 2 3 import java.io.IOException; 4 import java.io.InputStream; 5 import java.sql.Connection; 6 import java.sql.SQLException; 7 import java.util.Properties; 8 9 import org.apache.commons.dbcp.BasicDataSource; 10 11 public class DBUtil { 12 /* 13 * ThreadLocal用於執行緒跨方法共用資料使用 14 * ThreadLocal內部有一個Map, key為需要共用資料的執行緒本身,value就是其需要共用的資料 15 */ 16 private static ThreadLocal tl; // 聲明一個類似於倉庫的東西 17 private static BasicDataSource dataSource; // 聲明一個資料庫連接池物件 18 19 // 靜態代碼塊，在類載入的時候執行，而且只執行一次 20 static { 21 tl = new ThreadLocal; // 產生實體倉庫對象 22 dataSource = new BasicDataSource; // 實例資料庫連接池物件 23 24 Properties prop = new Properties; // 創建一個Properties物件用（該物件可以用來載入設定檔中的屬性清單） 25 InputStream is = DBUtil.class.getClassLoader.getResourceAsStream("config/mysql.properties"); // 讀取設定檔信息 26 try { 27 prop.load(is); // 載入設定檔中的屬性清單 28 29 String driverClassName = prop.getProperty("driverClassName"); // 獲取屬性資訊 30 String url = prop.getProperty("url"); 31 String username = prop.getProperty("username"); 32 String password = prop.getProperty("password"); 33 Integer maxActive = Integer.parseInt(prop.getProperty("maxActive")); 34 Integer maxWait = Integer.parseInt(prop.getProperty("maxWait")); 35 36 dataSource.setDriverClassName(driverClassName); // 初始化資料庫連接池（即：配置資料庫連接池的先關參數） 37 dataSource.setUrl(url); 38 dataSource.setUsername(username); 39 dataSource.setPassword(password); 40 dataSource.setMaxActive(maxActive); 41 dataSource.setMaxWait(maxWait); 42 43 is.close; // 關閉輸入流，釋放資源 44 } catch (IOException e) { 45 // TODO Auto-generated catch block 46 e.printStackTrace; 47 } 48 49 } 50 51 /** 52 * 創建連線物件（注意：靜態方法可以直接通過類名來調用） 53 * @return 連線物件 54 * @throws Exception 55 */ 56 public static Connection getConnection throws Exception { 57 try { 58 Connection conn = dataSource.getConnection; // 創建連線物件（利用資料庫連接池進行創建） 59 tl.set(conn); // 將連線物件放到倉庫中 60 return conn; 61 } catch (Exception e) { 62 // TODO Auto-generated catch block 63 e.printStackTrace; 64 throw e; 65 } 66 } 67 68 /** 69 * 關閉連線物件（注意：靜態方法可以通過類名直接調用） 70 * @throws Exception 71 */ 72 public static void closeConnection throws Exception { 73 Connection conn = tl.get; // 從倉庫中取出連線物件 74 tl.remove; // 清空倉庫 75 if(conn != null) { // 判斷連線物件是否釋放資源 76 try { 77 conn.close; 78 } catch (Exception e) { 79 // TODO Auto-generated catch block 80 e.printStackTrace; 81 throw e; 82 } 83 } 84 } 85 86 }

資料庫連接池類

1 package testJDBC; 2 3 import java.sql.Connection; 4 import java.sql.PreparedStatement; 5 import java.sql.ResultSet; 6 7 import org.junit.Test; 8 9 import cn.xiangxu.tools.DBUtil; 10 11 public class TestDBUtil { 12 @Test 13 public void test01 { 14 try { 15 Connection conn = DBUtil.getConnection; // 創建連線物件 16 String sql = "SELECT * FROM user "; // 拼接SQL語句 17 PreparedStatement ps = conn.prepareStatement(sql); // 創建執行物件 18 ResultSet rs = ps.executeQuery(sql); // 執行SQL語句 19 while(rs.next) { // 遍歷結果集 20 System.out.println(rs.getString("name")); 21 } 22 } catch (Exception e) { 23 e.printStackTrace; 24 } finally { // 關閉連接，釋放資源 25 try { 26 DBUtil.closeConnection; 27 } catch (Exception e) { 28 e.printStackTrace; 29 } 30 } 31 } 32 }

資料庫連接池的應用

並通過Properties物件讀取設定檔資訊，利用這些設定檔資訊給BasicDataSource物件進行初始化處理