JDBC學習筆記-有解無憂

JDBC核心技術

講師：宋紅康

微博：尚硅谷-宋紅康

第1章：JDBC概述

1.1 資料的持久化

持久化(persistence)：把資料保存到可掉電式存盤設備中以供之后使用，大多數情況下，特別是企業級應用，資料持久化意味著將記憶體中的資料保存到硬碟上加以”固化”，而持久化的實作程序大多通過各種關系資料庫來完成，
持久化的主要應用是將記憶體中的資料存盤在關系型資料庫中，當然也可以存盤在磁盤檔案、XML資料檔案中，

1.2 Java中的資料存盤技術

在Java中，資料庫存取技術可分為如下幾類：
- JDBC直接訪問資料庫
- JDO (Java Data Object )技術
- 第三方O/R工具，如Hibernate, Mybatis 等
JDBC是java訪問資料庫的基石，JDO、Hibernate、MyBatis等只是更好的封裝了JDBC，

1.3 JDBC介紹

JDBC(Java Database Connectivity)是一個獨立于特定資料庫管理系統、通用的SQL資料庫存取和操作的公共介面（一組API），定義了用來訪問資料庫的標準Java類別庫，（java.sql,javax.sql）使用這些類別庫可以以一種標準的方法、方便地訪問資料庫資源，
JDBC為訪問不同的資料庫提供了一種統一的途徑，為開發者屏蔽了一些細節問題，
JDBC的目標是使Java程式員使用JDBC可以連接任何提供了JDBC驅動程式的資料庫系統，這樣就使得程式員無需對特定的資料庫系統的特點有過多的了解，從而大大簡化和加快了開發程序，
如果沒有JDBC，那么Java程式訪問資料庫時是這樣的：

有了JDBC，Java程式訪問資料庫時是這樣的：

總結如下：

1.4 JDBC體系結構

JDBC介面（API）包括兩個層次：
- 面向應用的API：Java API，抽象介面，供應用程式開發人員使用（連接資料庫，執行SQL陳述句，獲得結果），
- 面向資料庫的API：Java Driver API，供開發商開發資料庫驅動程式用，

JDBC是sun公司提供一套用于資料庫操作的介面，java程式員只需要面向這套介面編程即可，

不同的資料庫廠商，需要針對這套介面，提供不同實作，不同的實作的集合，即為不同資料庫的驅動， ————面向介面編程

1.5 JDBC程式撰寫步驟

補充：ODBC(Open Database Connectivity，開放式資料庫連接)，是微軟在Windows平臺下推出的，使用者在程式中只需要呼叫ODBC API，由 ODBC 驅動程式將呼叫轉換成為對特定的資料庫的呼叫請求，

第2章：獲取資料庫連接

2.1 要素一：Driver介面實作類

2.1.1 Driver介面介紹

java.sql.Driver 介面是所有 JDBC 驅動程式需要實作的介面，這個介面是提供給資料庫廠商使用的，不同資料庫廠商提供不同的實作，
在程式中不需要直接去訪問實作了 Driver 介面的類，而是由驅動程式管理器類(java.sql.DriverManager)去呼叫這些Driver實作，
- Oracle的驅動：oracle.jdbc.driver.OracleDriver
- mySql的驅動： com.mysql.jdbc.Driver

將上述jar包拷貝到Java工程的一個目錄中，習慣上新建一個lib檔案夾，

在驅動jar上右鍵-->Build Path-->Add to Build Path

注意：如果是Dynamic Web Project（動態的web專案）話，則是把驅動jar放到WebContent（有的開發工具叫WebRoot）目錄中的WEB-INF目錄中的lib目錄下即可

2.1.2 加載與注冊JDBC驅動

加載驅動：加載 JDBC 驅動需呼叫 Class 類的靜態方法 forName()，向其傳遞要加載的 JDBC 驅動的類名
- Class.forName(“com.mysql.jdbc.Driver”);
注冊驅動：DriverManager 類是驅動程式管理器類，負責管理驅動程式
- 使用DriverManager.registerDriver(com.mysql.jdbc.Driver)來注冊驅動
- 通常不用顯式呼叫 DriverManager 類的 registerDriver() 方法來注冊驅動程式類的實體，因為 Driver 介面的驅動程式類都包含了靜態代碼塊，在這個靜態代碼塊中，會呼叫 DriverManager.registerDriver() 方法來注冊自身的一個實體，下圖是MySQL的Driver實作類的原始碼：

2.2 要素二：URL

JDBC URL 用于標識一個被注冊的驅動程式，驅動程式管理器通過這個 URL 選擇正確的驅動程式，從而建立到資料庫的連接，
JDBC URL的標準由三部分組成，各部分間用冒號分隔，
- jdbc:子協議:子名稱
- 協議：JDBC URL中的協議總是jdbc
- 子協議：子協議用于標識一個資料庫驅動程式
- 子名稱：一種標識資料庫的方法，子名稱可以依不同的子協議而變化，用子名稱的目的是為了定位資料庫提供足夠的資訊，包含主機名(對應服務端的ip地址)，埠號，資料庫名
舉例：
幾種常用資料庫的 JDBC URL
- MySQL的連接URL撰寫方式：
  - jdbc:mysql://主機名稱:mysql服務埠號/資料庫名稱?引數=值&引數=值
  - jdbc:mysql://localhost:3306/atguigu
  - jdbc:mysql://localhost:3306/atguigu?useUnicode=true&characterEncoding=utf8（如果JDBC程式與服務器端的字符集不一致，會導致亂碼，那么可以通過引數指定服務器端的字符集）
  - jdbc:mysql://localhost:3306/atguigu?user=root&password=123456
- Oracle 9i的連接URL撰寫方式：
  - jdbc:oracle:thin:@主機名稱:oracle服務埠號:資料庫名稱
  - jdbc:oracle:thin:@localhost:1521:atguigu
- SQLServer的連接URL撰寫方式：
  - jdbc:sqlserver://主機名稱:sqlserver服務埠號:DatabaseName=資料庫名稱
  - jdbc:sqlserver://localhost:1433:DatabaseName=atguigu

2.3 要素三：用戶名和密碼

user,password可以用“屬性名=屬性值”方式告訴資料庫
可以呼叫 DriverManager 類的 getConnection() 方法建立到資料庫的連接

2.4 資料庫連接方式舉例

2.4.1 連接方式一

	@Test
    public void testConnection1() {
        try {
            //1.提供java.sql.Driver介面實作類的物件
            Driver driver = null;
            driver = new com.mysql.jdbc.Driver();

            //2.提供url，指明具體操作的資料
            String url = "jdbc:mysql://localhost:3306/test";

            //3.提供Properties的物件，指明用戶名和密碼
            Properties info = new Properties();
            info.setProperty("user", "root");
            info.setProperty("password", "abc123");

            //4.呼叫driver的connect()，獲取連接
            Connection conn = driver.connect(url, info);
            System.out.println(conn);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

說明：上述代碼中顯式出現了第三方資料庫的API

2.4.2 連接方式二

	@Test
    public void testConnection2() {
        try {
            //1.實體化Driver
            String className = "com.mysql.jdbc.Driver";
            Class clazz = Class.forName(className);
            Driver driver = (Driver) clazz.newInstance();

            //2.提供url，指明具體操作的資料
            String url = "jdbc:mysql://localhost:3306/test";

            //3.提供Properties的物件，指明用戶名和密碼
            Properties info = new Properties();
            info.setProperty("user", "root");
            info.setProperty("password", "abc123");

            //4.呼叫driver的connect()，獲取連接
            Connection conn = driver.connect(url, info);
            System.out.println(conn);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

說明：相較于方式一，這里使用反射實體化Driver，不在代碼中體現第三方資料庫的API，體現了面向介面編程思想，

2.4.3 連接方式三

	@Test
    public void testConnection3() {
        try {
            //1.資料庫連接的4個基本要素：
            String url = "jdbc:mysql://localhost:3306/test";
            String user = "root";
            String password = "abc123";
            String driverName = "com.mysql.jdbc.Driver";

            //2.實體化Driver
            Class clazz = Class.forName(driverName);
            Driver driver = (Driver) clazz.newInstance();
            //3.注冊驅動
            DriverManager.registerDriver(driver);
            //4.獲取連接
            Connection conn = DriverManager.getConnection(url, user, password);
            System.out.println(conn);
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

說明：使用DriverManager實作資料庫的連接，體會獲取連接必要的4個基本要素，

2.4.4 連接方式四

	@Test
    public void testConnection4() {
        try {
            //1.資料庫連接的4個基本要素：
            String url = "jdbc:mysql://localhost:3306/test";
            String user = "root";
            String password = "abc123";
            String driverName = "com.mysql.jdbc.Driver";

            //2.加載驅動 （①實體化Driver ②注冊驅動）
            Class.forName(driverName);


            //Driver driver = (Driver) clazz.newInstance();
            //3.注冊驅動
            //DriverManager.registerDriver(driver);
            /*
            可以注釋掉上述代碼的原因，是因為在mysql的Driver類中宣告有：
            static {
                try {
                    DriverManager.registerDriver(new Driver());
                } catch (SQLException var1) {
                    throw new RuntimeException("Can't register driver!");
                }
            }

             */


            //3.獲取連接
            Connection conn = DriverManager.getConnection(url, user, password);
            System.out.println(conn);
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

說明：不必顯式的注冊驅動了，因為在DriverManager的原始碼中已經存在靜態代碼塊，實作了驅動的注冊，

2.4.5 連接方式五(最終版)

	@Test
    public  void testConnection5() throws Exception {
    	//1.加載組態檔
        InputStream is = ConnectionTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
        Properties pros = new Properties();
        pros.load(is);
        
        //2.讀取配置資訊
        String user = pros.getProperty("user");
        String password = pros.getProperty("password");
        String url = pros.getProperty("url");
        String driverClass = pros.getProperty("driverClass");

        //3.加載驅動
        Class.forName(driverClass);

        //4.獲取連接
        Connection conn = DriverManager.getConnection(url,user,password);
        System.out.println(conn);

    }

其中，組態檔宣告在工程的src目錄下：【jdbc.properties】

user=root
password=abc123
url=jdbc:mysql://localhost:3306/test
driverClass=com.mysql.jdbc.Driver

說明：使用組態檔的方式保存配置資訊，在代碼中加載組態檔

使用組態檔的好處：

①實作了代碼和資料的分離，如果需要修改配置資訊，直接在組態檔中修改，不需要深入代碼
②如果修改了配置資訊，省去重新編譯的程序，

第3章：使用PreparedStatement實作CRUD操作

3.1 操作和訪問資料庫

資料庫連接被用于向資料庫服務器發送命令和 SQL 陳述句，并接受資料庫服務器回傳的結果，其實一個資料庫連接就是一個Socket連接，
在 java.sql 包中有 3 個介面分別定義了對資料庫的呼叫的不同方式：
- Statement：用于執行靜態 SQL 陳述句并回傳它所生成結果的物件，
- PrepatedStatement：SQL 陳述句被預編譯并存盤在此物件中，可以使用此物件多次高效地執行該陳述句，
- CallableStatement：用于執行 SQL 存盤程序

3.2 使用Statement操作資料表的弊端

通過呼叫 Connection 物件的 createStatement() 方法創建該物件，該物件用于執行靜態的 SQL 陳述句，并且回傳執行結果，

Statement 介面中定義了下列方法用于執行 SQL 陳述句：

int excuteUpdate(String sql)：執行更新操作INSERT、UPDATE、DELETE
ResultSet executeQuery(String sql)：執行查詢操作SELECT

但是使用Statement操作資料表存在弊端：
- 問題一：存在拼串操作，繁瑣
- 問題二：存在SQL注入問題
SQL 注入是利用某些系統沒有對用戶輸入的資料進行充分的檢查，而在用戶輸入資料中注入非法的 SQL 陳述句段或命令(如：SELECT user, password FROM user_table WHERE user='a' OR 1 = ' AND password = ' OR '1' = '1') ，從而利用系統的 SQL 引擎完成惡意行為的做法，
對于 Java 而言，要防范 SQL 注入，只要用 PreparedStatement(從Statement擴展而來) 取代 Statement 就可以了，
代碼演示：

public class StatementTest {

	// 使用Statement的弊端：需要拼寫sql陳述句，并且存在SQL注入的問題
	@Test
	public void testLogin() {
		Scanner scan = new Scanner(System.in);

		System.out.print("用戶名：");
		String userName = scan.nextLine();
		System.out.print("密   碼：");
		String password = scan.nextLine();

		// SELECT user,password FROM user_table WHERE USER = '1' or ' AND PASSWORD = '='1' or '1' = '1';
		String sql = "SELECT user,password FROM user_table WHERE USER = '" + userName + "' AND PASSWORD = '" + password
				+ "'";
		User user = get(sql, User.class);
		if (user != null) {
			System.out.println("登陸成功!");
		} else {
			System.out.println("用戶名或密碼錯誤！");
		}
	}

	// 使用Statement實作對資料表的查詢操作
	public <T> T get(String sql, Class<T> clazz) {
		T t = null;

		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		try {
			// 1.加載組態檔
			InputStream is = StatementTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
			Properties pros = new Properties();
			pros.load(is);

			// 2.讀取配置資訊
			String user = pros.getProperty("user");
			String password = pros.getProperty("password");
			String url = pros.getProperty("url");
			String driverClass = pros.getProperty("driverClass");

			// 3.加載驅動
			Class.forName(driverClass);

			// 4.獲取連接
			conn = DriverManager.getConnection(url, user, password);

			st = conn.createStatement();

			rs = st.executeQuery(sql);

			// 獲取結果集的元資料
			ResultSetMetaData rsmd = rs.getMetaData();

			// 獲取結果集的列數
			int columnCount = rsmd.getColumnCount();

			if (rs.next()) {

				t = clazz.newInstance();

				for (int i = 0; i < columnCount; i++) {
					// //1. 獲取列的名稱
					// String columnName = rsmd.getColumnName(i+1);

					// 1. 獲取列的別名
					String columnName = rsmd.getColumnLabel(i + 1);

					// 2. 根據列名獲取對應資料表中的資料
					Object columnVal = rs.getObject(columnName);

					// 3. 將資料表中得到的資料，封裝進物件
					Field field = clazz.getDeclaredField(columnName);
					field.setAccessible(true);
					field.set(t, columnVal);
				}
				return t;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			// 關閉資源
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (st != null) {
				try {
					st.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}

			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

		return null;
	}
}

綜上：

3.3 PreparedStatement的使用

3.3.1 PreparedStatement介紹

可以通過呼叫 Connection 物件的 preparedStatement(String sql) 方法獲取 PreparedStatement 物件
PreparedStatement 介面是 Statement 的子介面，它表示一條預編譯過的 SQL 陳述句
PreparedStatement 物件所代表的 SQL 陳述句中的引數用問號(?)來表示，呼叫 PreparedStatement 物件的 setXxx() 方法來設定這些引數. setXxx() 方法有兩個引數，第一個引數是要設定的 SQL 陳述句中的引數的索引(從 1 開始)，第二個是設定的 SQL 陳述句中的引數的值

3.3.2 PreparedStatement vs Statement

代碼的可讀性和可維護性，
PreparedStatement 能最大可能提高性能：
- DBServer會對預編譯陳述句提供性能優化，因為預編譯陳述句有可能被重復呼叫，所以陳述句在被DBServer的編譯器編譯后的執行代碼被快取下來，那么下次呼叫時只要是相同的預編譯陳述句就不需要編譯，只要將引數直接傳入編譯過的陳述句執行代碼中就會得到執行，
- 在statement陳述句中,即使是相同操作但因為資料內容不一樣,所以整個陳述句本身不能匹配,沒有快取陳述句的意義.事實是沒有資料庫會對普通陳述句編譯后的執行代碼快取，這樣每執行一次都要對傳入的陳述句編譯一次，
- (語法檢查，語意檢查，翻譯成二進制命令，快取)
PreparedStatement 可以防止 SQL 注入

3.3.3 Java與SQL對應資料型別轉換表

Java型別	SQL型別
boolean	BIT
byte	TINYINT
short	SMALLINT
int	INTEGER
long	BIGINT
String	CHAR,VARCHAR,LONGVARCHAR
byte array	BINARY , VAR BINARY
java.sql.Date	DATE
java.sql.Time	TIME
java.sql.Timestamp	TIMESTAMP

3.3.4 使用PreparedStatement實作增、刪、改操作

	//通用的增、刪、改操作（體現一：增、刪、改 ； 體現二：針對于不同的表）
	public void update(String sql,Object ... args){
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			//1.獲取資料庫的連接
			conn = JDBCUtils.getConnection();
			
			//2.獲取PreparedStatement的實體 (或：預編譯sql陳述句)
			ps = conn.prepareStatement(sql);
			//3.填充占位符
			for(int i = 0;i < args.length;i++){
				ps.setObject(i + 1, args[i]);
			}
			
			//4.執行sql陳述句
			ps.execute();
		} catch (Exception e) {
			
			e.printStackTrace();
		}finally{
			//5.關閉資源
			JDBCUtils.closeResource(conn, ps);
			
		}
	}

3.3.5 使用PreparedStatement實作查詢操作

	// 通用的針對于不同表的查詢:回傳一個物件 (version 1.0)
	public <T> T getInstance(Class<T> clazz, String sql, Object... args) {

		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			// 1.獲取資料庫連接
			conn = JDBCUtils.getConnection();

			// 2.預編譯sql陳述句，得到PreparedStatement物件
			ps = conn.prepareStatement(sql);

			// 3.填充占位符
			for (int i = 0; i < args.length; i++) {
				ps.setObject(i + 1, args[i]);
			}

			// 4.執行executeQuery(),得到結果集：ResultSet
			rs = ps.executeQuery();

			// 5.得到結果集的元資料：ResultSetMetaData
			ResultSetMetaData rsmd = rs.getMetaData();

			// 6.1通過ResultSetMetaData得到columnCount,columnLabel；通過ResultSet得到列值
			int columnCount = rsmd.getColumnCount();
			if (rs.next()) {
				T t = clazz.newInstance();
				for (int i = 0; i < columnCount; i++) {// 遍歷每一個列

					// 獲取列值
					Object columnVal = rs.getObject(i + 1);
					// 獲取列的別名:列的別名，使用類的屬性名充當
					String columnLabel = rsmd.getColumnLabel(i + 1);
					// 6.2使用反射，給物件的相應屬性賦值
					Field field = clazz.getDeclaredField(columnLabel);
					field.setAccessible(true);
					field.set(t, columnVal);

				}

				return t;

			}
		} catch (Exception e) {

			e.printStackTrace();
		} finally {
			// 7.關閉資源
			JDBCUtils.closeResource(conn, ps, rs);
		}

		return null;

	}

說明：使用PreparedStatement實作的查詢操作可以替換Statement實作的查詢操作，解決Statement拼串和SQL注入問題，

3.4 ResultSet與ResultSetMetaData

3.4.1 ResultSet

查詢需要呼叫PreparedStatement 的 executeQuery() 方法，查詢結果是一個ResultSet 物件
ResultSet 物件以邏輯表格的形式封裝了執行資料庫操作的結果集，ResultSet 介面由資料庫廠商提供實作
ResultSet 回傳的實際上就是一張資料表，有一個指標指向資料表的第一條記錄的前面，
ResultSet 物件維護了一個指向當前資料行的游標，初始的時候，游標在第一行之前，可以通過 ResultSet 物件的 next() 方法移動到下一行，呼叫 next()方法檢測下一行是否有效，若有效，該方法回傳 true，且指標下移，相當于Iterator物件的 hasNext() 和 next() 方法的結合體，
當指標指向一行時, 可以通過呼叫 getXxx(int index) 或 getXxx(int columnName) 獲取每一列的值，
- 例如: getInt(1), getString("name")
- 注意：Java與資料庫互動涉及到的相關Java API中的索引都從1開始，
ResultSet 介面的常用方法：
- boolean next()
- getString()
- …

3.4.2 ResultSetMetaData

可用于獲取關于 ResultSet 物件中列的型別和屬性資訊的物件
ResultSetMetaData meta = rs.getMetaData();
- getColumnName(int column)：獲取指定列的名稱
- getColumnLabel(int column)：獲取指定列的別名
- getColumnCount()：回傳當前 ResultSet 物件中的列數，
- getColumnTypeName(int column)：檢索指定列的資料庫特定的型別名稱，
- getColumnDisplaySize(int column)：指示指定列的最大標準寬度，以字符為單位，
- isNullable(int column)：指示指定列中的值是否可以為 null，
- isAutoIncrement(int column)：指示是否自動為指定列進行編號，這樣這些列仍然是只讀的，

問題1：得到結果集后, 如何知道該結果集中有哪些列？列名是什么？

? 需要使用一個描述 ResultSet 的物件，即 ResultSetMetaData

問題2：關于ResultSetMetaData

如何獲取 ResultSetMetaData：呼叫 ResultSet 的 getMetaData() 方法即可
獲取 ResultSet 中有多少列：呼叫 ResultSetMetaData 的 getColumnCount() 方法
獲取 ResultSet 每一列的列的別名是什么：呼叫 ResultSetMetaData 的getColumnLabel() 方法

3.5 資源的釋放

釋放ResultSet, Statement,Connection，
資料庫連接（Connection）是非常稀有的資源，用完后必須馬上釋放，如果Connection不能及時正確的關閉將導致系統宕機，Connection的使用原則是盡量晚創建，盡量早的釋放，
可以在finally中關閉，保證及時其他代碼出現例外，資源也一定能被關閉，

3.6 JDBC API小結

兩種思想
- 面向介面編程的思想
- ORM思想(object relational mapping)
  - 一個資料表對應一個java類
  - 表中的一條記錄對應java類的一個物件
  - 表中的一個欄位對應java類的一個屬性
sql是需要結合列名和表的屬性名來寫，注意起別名，
兩種技術
- JDBC結果集的元資料：ResultSetMetaData
  - 獲取列數：getColumnCount()
  - 獲取列的別名：getColumnLabel()
- 通過反射，創建指定類的物件，獲取指定的屬性并賦值

章節練習

練習題1：從控制臺向資料庫的表customers中插入一條資料，表結構如下：

練習題2：創立資料庫表 examstudent，表結構如下：

向資料表中添加如下資料：

代碼實作1：插入一個新的student 資訊

請輸入考生的詳細資訊

Type:
IDCard:
ExamCard:
StudentName:
Location:
Grade:

資訊錄入成功!

代碼實作2：在 eclipse中建立 java 程式：輸入身份證號或準考證號可以查詢到學生的基本資訊，結果如下：

代碼實作3：完成學生資訊的洗掉功能

第4章操作BLOB型別欄位

4.1 MySQL BLOB型別

MySQL中，BLOB是一個二進制大型物件，是一個可以存盤大量資料的容器，它能容納不同大小的資料，
插入BLOB型別的資料必須使用PreparedStatement，因為BLOB型別的資料無法使用字串拼接寫的，
MySQL的四種BLOB型別(除了在存盤的最大資訊量上不同外，他們是等同的)

實際使用中根據需要存入的資料大小定義不同的BLOB型別，
需要注意的是：如果存盤的檔案過大，資料庫的性能會下降，
如果在指定了相關的Blob型別以后，還報錯：xxx too large，那么在mysql的安裝目錄下，找my.ini檔案加上如下的配置引數： max_allowed_packet=16M，同時注意：修改了my.ini檔案之后，需要重新啟動mysql服務，

4.2 向資料表中插入大資料型別

//獲取連接
Connection conn = JDBCUtils.getConnection();
		
String sql = "insert into customers(name,email,birth,photo)values(?,?,?,?)";
PreparedStatement ps = conn.prepareStatement(sql);

// 填充占位符
ps.setString(1, "徐海強");
ps.setString(2, "[email protected]");
ps.setDate(3, new Date(new java.util.Date().getTime()));
// 操作Blob型別的變數
FileInputStream fis = new FileInputStream("xhq.png");
ps.setBlob(4, fis);
//執行
ps.execute();
		
fis.close();
JDBCUtils.closeResource(conn, ps);

4.3 修改資料表中的Blob型別欄位

Connection conn = JDBCUtils.getConnection();
String sql = "update customers set photo = ? where id = ?";
PreparedStatement ps = conn.prepareStatement(sql);

// 填充占位符
// 操作Blob型別的變數
FileInputStream fis = new FileInputStream("coffee.png");
ps.setBlob(1, fis);
ps.setInt(2, 25);

ps.execute();

fis.close();
JDBCUtils.closeResource(conn, ps);

4.4 從資料表中讀取大資料型別

String sql = "SELECT id, name, email, birth, photo FROM customer WHERE id = ?";
conn = getConnection();
ps = conn.prepareStatement(sql);
ps.setInt(1, 8);
rs = ps.executeQuery();
if(rs.next()){
	Integer id = rs.getInt(1);
    String name = rs.getString(2);
	String email = rs.getString(3);
    Date birth = rs.getDate(4);
	Customer cust = new Customer(id, name, email, birth);
    System.out.println(cust); 
    //讀取Blob型別的欄位
	Blob photo = rs.getBlob(5);
	InputStream is = photo.getBinaryStream();
	OutputStream os = new FileOutputStream("c.jpg");
	byte [] buffer = new byte[1024];
	int len = 0;
	while((len = is.read(buffer)) != -1){
		os.write(buffer, 0, len);
	}
    JDBCUtils.closeResource(conn, ps, rs);
		
	if(is != null){
		is.close();
	}
		
	if(os !=  null){
		os.close();
	}
    
}

第5章批量插入

5.1 批量執行SQL陳述句

當需要成批插入或者更新記錄時，可以采用Java的批量更新機制，這一機制允許多條陳述句一次性提交給資料庫批量處理，通常情況下比單獨提交處理更有效率

JDBC的批量處理陳述句包括下面三個方法：

addBatch(String)：添加需要批量處理的SQL陳述句或是引數；
executeBatch()：執行批量處理陳述句；
clearBatch():清空快取的資料

通常我們會遇到兩種批量執行SQL陳述句的情況：

多條SQL陳述句的批量處理；
一個SQL陳述句的批量傳參；

5.2 高效的批量插入

舉例：向資料表中插入20000條資料

資料庫中提供一個goods表，創建如下：

CREATE TABLE goods(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
);

5.2.1 實作層次一：使用Statement

Connection conn = JDBCUtils.getConnection();
Statement st = conn.createStatement();
for(int i = 1;i <= 20000;i++){
	String sql = "insert into goods(name) values('name_' + "+ i +")";
	st.executeUpdate(sql);
}

5.2.2 實作層次二：使用PreparedStatement

long start = System.currentTimeMillis();
		
Connection conn = JDBCUtils.getConnection();
		
String sql = "insert into goods(name)values(?)";
PreparedStatement ps = conn.prepareStatement(sql);
for(int i = 1;i <= 20000;i++){
	ps.setString(1, "name_" + i);
	ps.executeUpdate();
}
		
long end = System.currentTimeMillis();
System.out.println("花費的時間為：" + (end - start));//82340
		
		
JDBCUtils.closeResource(conn, ps);

5.2.3 實作層次三

/*
 * 修改1： 使用 addBatch() / executeBatch() / clearBatch()
 * 修改2：mysql服務器默認是關閉批處理的，我們需要通過一個引數，讓mysql開啟批處理的支持，
 * 		 ?rewriteBatchedStatements=true 寫在組態檔的url后面
 * 修改3：使用更新的mysql 驅動：mysql-connector-java-5.1.37-bin.jar
 * 
 */
@Test
public void testInsert1() throws Exception{
	long start = System.currentTimeMillis();
		
	Connection conn = JDBCUtils.getConnection();
		
	String sql = "insert into goods(name)values(?)";
	PreparedStatement ps = conn.prepareStatement(sql);
		
	for(int i = 1;i <= 1000000;i++){
		ps.setString(1, "name_" + i);
			
		//1.“攢”sql
		ps.addBatch();
		if(i % 500 == 0){
			//2.執行
			ps.executeBatch();
			//3.清空
			ps.clearBatch();
		}
	}
		
	long end = System.currentTimeMillis();
	System.out.println("花費的時間為：" + (end - start));//20000條：625                                                                         //1000000條:14733  
		
	JDBCUtils.closeResource(conn, ps);
}

5.2.4 實作層次四

/*
* 層次四：在層次三的基礎上操作
* 使用Connection 的 setAutoCommit(false)  /  commit()
*/
@Test
public void testInsert2() throws Exception{
	long start = System.currentTimeMillis();
		
	Connection conn = JDBCUtils.getConnection();
		
	//1.設定為不自動提交資料
	conn.setAutoCommit(false);
		
	String sql = "insert into goods(name)values(?)";
	PreparedStatement ps = conn.prepareStatement(sql);
		
	for(int i = 1;i <= 1000000;i++){
		ps.setString(1, "name_" + i);
			
		//1.“攢”sql
		ps.addBatch();
			
		if(i % 500 == 0){
			//2.執行
			ps.executeBatch();
			//3.清空
			ps.clearBatch();
		}
	}
		
	//2.提交資料
	conn.commit();
		
	long end = System.currentTimeMillis();
	System.out.println("花費的時間為：" + (end - start));//1000000條:4978 
		
	JDBCUtils.closeResource(conn, ps);
}

第6章：資料庫事務

6.1 資料庫事務介紹

事務：一組邏輯操作單元,使資料從一種狀態變換到另一種狀態，
事務處理（事務操作）：保證所有事務都作為一個作業單元來執行，即使出現了故障，都不能改變這種執行方式，當在一個事務中執行多個操作時，要么所有的事務都被提交(commit)，那么這些修改就永久地保存下來；要么資料庫管理系統將放棄所作的所有修改，整個事務回滾(rollback)到最初狀態，
為確保資料庫中資料的一致性，資料的操縱應當是離散的成組的邏輯單元：當它全部完成時，資料的一致性可以保持，而當這個單元中的一部分操作失敗，整個事務應全部視為錯誤，所有從起始點以后的操作應全部回退到開始狀態，

6.2 JDBC事務處理

資料一旦提交，就不可回滾，
資料什么時候意味著提交？
- 當一個連接物件被創建時，默認情況下是自動提交事務：每次執行一個 SQL 陳述句時，如果執行成功，就會向資料庫自動提交，而不能回滾，
- 關閉資料庫連接，資料就會自動的提交，如果多個操作，每個操作使用的是自己單獨的連接，則無法保證事務，即同一個事務的多個操作必須在同一個連接下，
JDBC程式中為了讓多個 SQL 陳述句作為一個事務執行：
- 呼叫 Connection 物件的 setAutoCommit(false); 以取消自動提交事務
- 在所有的 SQL 陳述句都成功執行后，呼叫 commit(); 方法提交事務
- 在出現例外時，呼叫 rollback(); 方法回滾事務
若此時 Connection 沒有被關閉，還可能被重復使用，則需要恢復其自動提交狀態 setAutoCommit(true)，尤其是在使用資料庫連接池技術時，執行close()方法前，建議恢復自動提交狀態，

【案例：用戶AA向用戶BB轉賬100】

public void testJDBCTransaction() {
	Connection conn = null;
	try {
		// 1.獲取資料庫連接
		conn = JDBCUtils.getConnection();
		// 2.開啟事務
		conn.setAutoCommit(false);
		// 3.進行資料庫操作
		String sql1 = "update user_table set balance = balance - 100 where user = ?";
		update(conn, sql1, "AA");

		// 模擬網路例外
		//System.out.println(10 / 0);

		String sql2 = "update user_table set balance = balance + 100 where user = ?";
		update(conn, sql2, "BB");
		// 4.若沒有例外，則提交事務
		conn.commit();
	} catch (Exception e) {
		e.printStackTrace();
		// 5.若有例外，則回滾事務
		try {
			conn.rollback();
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
    } finally {
        try {
			//6.恢復每次DML操作的自動提交功能
			conn.setAutoCommit(true);
		} catch (SQLException e) {
			e.printStackTrace();
		}
        //7.關閉連接
		JDBCUtils.closeResource(conn, null, null); 
    }  
}

其中，對資料庫操作的方法為：

//使用事務以后的通用的增刪改操作（version 2.0）
public void update(Connection conn ,String sql, Object... args) {
	PreparedStatement ps = null;
	try {
		// 1.獲取PreparedStatement的實體 (或：預編譯sql陳述句)
		ps = conn.prepareStatement(sql);
		// 2.填充占位符
		for (int i = 0; i < args.length; i++) {
			ps.setObject(i + 1, args[i]);
		}
		// 3.執行sql陳述句
		ps.execute();
	} catch (Exception e) {
		e.printStackTrace();
	} finally {
		// 4.關閉資源
		JDBCUtils.closeResource(null, ps);

	}
}

6.3 事務的ACID屬性

原子性（Atomicity）
原子性是指事務是一個不可分割的作業單位，事務中的操作要么都發生，要么都不發生，
一致性（Consistency）
事務必須使資料庫從一個一致性狀態變換到另外一個一致性狀態，
隔離性（Isolation）
事務的隔離性是指一個事務的執行不能被其他事務干擾，即一個事務內部的操作及使用的資料對并發的其他事務是隔離的，并發執行的各個事務之間不能互相干擾，
持久性（Durability）
持久性是指一個事務一旦被提交，它對資料庫中資料的改變就是永久性的，接下來的其他操作和資料庫故障不應該對其有任何影響，

6.3.1 資料庫的并發問題

對于同時運行的多個事務, 當這些事務訪問資料庫中相同的資料時, 如果沒有采取必要的隔離機制, 就會導致各種并發問題:
- 臟讀: 對于兩個事務 T1, T2, T1 讀取了已經被 T2 更新但還沒有被提交的欄位，之后, 若 T2 回滾, T1讀取的內容就是臨時且無效的，
- 不可重復讀: 對于兩個事務T1, T2, T1 讀取了一個欄位, 然后 T2 更新了該欄位，之后, T1再次讀取同一個欄位, 值就不同了，
- 幻讀: 對于兩個事務T1, T2, T1 從一個表中讀取了一個欄位, 然后 T2 在該表中插入了一些新的行，之后, 如果 T1 再次讀取同一個表, 就會多出幾行，
資料庫事務的隔離性: 資料庫系統必須具有隔離并發運行各個事務的能力, 使它們不會相互影響, 避免各種并發問題，
一個事務與其他事務隔離的程度稱為隔離級別，資料庫規定了多種事務隔離級別, 不同隔離級別對應不同的干擾程度, 隔離級別越高, 資料一致性就越好, 但并發性越弱，

6.3.2 四種隔離級別

資料庫提供的4種事務隔離級別：
Oracle 支持的 2 種事務隔離級別：READ COMMITED, SERIALIZABLE， Oracle 默認的事務隔離級別為: READ COMMITED ，
Mysql 支持 4 種事務隔離級別，Mysql 默認的事務隔離級別為: REPEATABLE READ，

6.3.3 在MySql中設定隔離級別

每啟動一個 mysql 程式, 就會獲得一個單獨的資料庫連接. 每個資料庫連接都有一個全域變數 @@tx_isolation, 表示當前的事務隔離級別，
查看當前的隔離級別:
```
SELECT @@tx_isolation;
```

設定當前 mySQL 連接的隔離級別:

set  transaction isolation level read committed;

設定資料庫系統的全域的隔離級別:

set global transaction isolation level read committed;

補充操作：

創建mysql資料庫用戶：

create user tom identified by 'abc123';

授予權限

#授予通過網路方式登錄的tom用戶，對所有庫所有表的全部權限，密碼設為abc123.
grant all privileges on *.* to tom@'%'  identified by 'abc123'; 

 #給tom用戶使用本地命令列方式，授予atguigudb這個庫下的所有表的插刪改查的權限，
grant select,insert,delete,update on atguigudb.* to tom@localhost identified by 'abc123';

第7章：DAO及相關實作類

DAO：Data Access Object訪問資料資訊的類和介面，包括了對資料的CRUD（Create、Retrival、Update、Delete），而不包含任何業務相關的資訊，有時也稱作：BaseDAO
作用：為了實作功能的模塊化，更有利于代碼的維護和升級，
下面是尚硅谷JavaWeb階段書城專案中DAO使用的體現：

層次結構：

【BaseDAO.java】

package com.atguigu.bookstore.dao;

import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;


/**
 * 定義一個用來被繼承的對資料庫進行基本操作的Dao
 * 
 * @author HanYanBing
 *
 * @param <T>
 */
public abstract class BaseDao<T> {
	private QueryRunner queryRunner = new QueryRunner();
	// 定義一個變數來接收泛型的型別
	private Class<T> type;

	// 獲取T的Class物件，獲取泛型的型別，泛型是在被子類繼承時才確定
	public BaseDao() {
		// 獲取子類的型別
		Class clazz = this.getClass();
		// 獲取父類的型別
		// getGenericSuperclass()用來獲取當前類的父類的型別
		// ParameterizedType表示的是帶泛型的型別
		ParameterizedType parameterizedType = (ParameterizedType) clazz.getGenericSuperclass();
		// 獲取具體的泛型型別 getActualTypeArguments獲取具體的泛型的型別
		// 這個方法會回傳一個Type的陣列
		Type[] types = parameterizedType.getActualTypeArguments();
		// 獲取具體的泛型的型別·
		this.type = (Class<T>) types[0];
	}

	/**
	 * 通用的增刪改操作
	 * 
	 * @param sql
	 * @param params
	 * @return
	 */
	public int update(Connection conn,String sql, Object... params) {
		int count = 0;
		try {
			count = queryRunner.update(conn, sql, params);
		} catch (SQLException e) {
			e.printStackTrace();
		} 
		return count;
	}

	/**
	 * 獲取一個物件
	 * 
	 * @param sql
	 * @param params
	 * @return
	 */
	public T getBean(Connection conn,String sql, Object... params) {
		T t = null;
		try {
			t = queryRunner.query(conn, sql, new BeanHandler<T>(type), params);
		} catch (SQLException e) {
			e.printStackTrace();
		} 
		return t;
	}

	/**
	 * 獲取所有物件
	 * 
	 * @param sql
	 * @param params
	 * @return
	 */
	public List<T> getBeanList(Connection conn,String sql, Object... params) {
		List<T> list = null;
		try {
			list = queryRunner.query(conn, sql, new BeanListHandler<T>(type), params);
		} catch (SQLException e) {
			e.printStackTrace();
		} 
		return list;
	}

	/**
	 * 獲取一個但一值得方法，專門用來執行像 select count(*)...這樣的sql陳述句
	 * 
	 * @param sql
	 * @param params
	 * @return
	 */
	public Object getValue(Connection conn,String sql, Object... params) {
		Object count = null;
		try {
			// 呼叫queryRunner的query方法獲取一個單一的值
			count = queryRunner.query(conn, sql, new ScalarHandler<>(), params);
		} catch (SQLException e) {
			e.printStackTrace();
		} 
		return count;
	}
}

【BookDAO.java】

package com.atguigu.bookstore.dao;

import java.sql.Connection;
import java.util.List;

import com.atguigu.bookstore.beans.Book;
import com.atguigu.bookstore.beans.Page;

public interface BookDao {

	/**
	 * 從資料庫中查詢出所有的記錄
	 * 
	 * @return
	 */
	List<Book> getBooks(Connection conn);

	/**
	 * 向資料庫中插入一條記錄
	 * 
	 * @param book
	 */
	void saveBook(Connection conn,Book book);

	/**
	 * 從資料庫中根據圖書的id洗掉一條記錄
	 * 
	 * @param bookId
	 */
	void deleteBookById(Connection conn,String bookId);

	/**
	 * 根據圖書的id從資料庫中查詢出一條記錄
	 * 
	 * @param bookId
	 * @return
	 */
	Book getBookById(Connection conn,String bookId);

	/**
	 * 根據圖書的id從資料庫中更新一條記錄
	 * 
	 * @param book
	 */
	void updateBook(Connection conn,Book book);

	/**
	 * 獲取帶分頁的圖書資訊
	 * 
	 * @param page：是只包含了用戶輸入的pageNo屬性的page物件
	 * @return 回傳的Page物件是包含了所有屬性的Page物件
	 */
	Page<Book> getPageBooks(Connection conn,Page<Book> page);

	/**
	 * 獲取帶分頁和價格范圍的圖書資訊
	 * 
	 * @param page：是只包含了用戶輸入的pageNo屬性的page物件
	 * @return 回傳的Page物件是包含了所有屬性的Page物件
	 */
	Page<Book> getPageBooksByPrice(Connection conn,Page<Book> page, double minPrice, double maxPrice);

}

【UserDAO.java】

package com.atguigu.bookstore.dao;

import java.sql.Connection;

import com.atguigu.bookstore.beans.User;

public interface UserDao {

	/**
	 * 根據User物件中的用戶名和密碼從資料庫中獲取一條記錄
	 * 
	 * @param user
	 * @return User 資料庫中有記錄 null 資料庫中無此記錄
	 */
	User getUser(Connection conn,User user);

	/**
	 * 根據User物件中的用戶名從資料庫中獲取一條記錄
	 * 
	 * @param user
	 * @return true 資料庫中有記錄 false 資料庫中無此記錄
	 */
	boolean checkUsername(Connection conn,User user);

	/**
	 * 向資料庫中插入User物件
	 * 
	 * @param user
	 */
	void saveUser(Connection conn,User user);
}

【BookDaoImpl.java】

package com.atguigu.bookstore.dao.impl;

import java.sql.Connection;
import java.util.List;

import com.atguigu.bookstore.beans.Book;
import com.atguigu.bookstore.beans.Page;
import com.atguigu.bookstore.dao.BaseDao;
import com.atguigu.bookstore.dao.BookDao;

public class BookDaoImpl extends BaseDao<Book> implements BookDao {

	@Override
	public List<Book> getBooks(Connection conn) {
		// 呼叫BaseDao中得到一個List的方法
		List<Book> beanList = null;
		// 寫sql陳述句
		String sql = "select id,title,author,price,sales,stock,img_path imgPath from books";
		beanList = getBeanList(conn,sql);
		return beanList;
	}

	@Override
	public void saveBook(Connection conn,Book book) {
		// 寫sql陳述句
		String sql = "insert into books(title,author,price,sales,stock,img_path) values(?,?,?,?,?,?)";
		// 呼叫BaseDao中通用的增刪改的方法
		update(conn,sql, book.getTitle(), book.getAuthor(), book.getPrice(), book.getSales(), book.getStock(),book.getImgPath());
	}

	@Override
	public void deleteBookById(Connection conn,String bookId) {
		// 寫sql陳述句
		String sql = "DELETE FROM books WHERE id = ?";
		// 呼叫BaseDao中通用增刪改的方法
		update(conn,sql, bookId);
			
	}

	@Override
	public Book getBookById(Connection conn,String bookId) {
		// 呼叫BaseDao中獲取一個物件的方法
		Book book = null;
		// 寫sql陳述句
		String sql = "select id,title,author,price,sales,stock,img_path imgPath from books where id = ?";
		book = getBean(conn,sql, bookId);
		return book;
	}

	@Override
	public void updateBook(Connection conn,Book book) {
		// 寫sql陳述句
		String sql = "update books set title = ? , author = ? , price = ? , sales = ? , stock = ? where id = ?";
		// 呼叫BaseDao中通用的增刪改的方法
		update(conn,sql, book.getTitle(), book.getAuthor(), book.getPrice(), book.getSales(), book.getStock(), book.getId());
	}

	@Override
	public Page<Book> getPageBooks(Connection conn,Page<Book> page) {
		// 獲取資料庫中圖書的總記錄數
		String sql = "select count(*) from books";
		// 呼叫BaseDao中獲取一個單一值的方法
		long totalRecord = (long) getValue(conn,sql);
		// 將總記錄數設定都page物件中
		page.setTotalRecord((int) totalRecord);

		// 獲取當前頁中的記錄存放的List
		String sql2 = "select id,title,author,price,sales,stock,img_path imgPath from books limit ?,?";
		// 呼叫BaseDao中獲取一個集合的方法
		List<Book> beanList = getBeanList(conn,sql2, (page.getPageNo() - 1) * Page.PAGE_SIZE, Page.PAGE_SIZE);
		// 將這個List設定到page物件中
		page.setList(beanList);
		return page;
	}

	@Override
	public Page<Book> getPageBooksByPrice(Connection conn,Page<Book> page, double minPrice, double maxPrice) {
		// 獲取資料庫中圖書的總記錄數
		String sql = "select count(*) from books where price between ? and ?";
		// 呼叫BaseDao中獲取一個單一值的方法
		long totalRecord = (long) getValue(conn,sql,minPrice,maxPrice);
		// 將總記錄數設定都page物件中
		page.setTotalRecord((int) totalRecord);

		// 獲取當前頁中的記錄存放的List
		String sql2 = "select id,title,author,price,sales,stock,img_path imgPath from books where price between ? and ? limit ?,?";
		// 呼叫BaseDao中獲取一個集合的方法
		List<Book> beanList = getBeanList(conn,sql2, minPrice , maxPrice , (page.getPageNo() - 1) * Page.PAGE_SIZE, Page.PAGE_SIZE);
		// 將這個List設定到page物件中
		page.setList(beanList);
		
		return page;
	}

}

【UserDaoImpl.java】

package com.atguigu.bookstore.dao.impl;

import java.sql.Connection;

import com.atguigu.bookstore.beans.User;
import com.atguigu.bookstore.dao.BaseDao;
import com.atguigu.bookstore.dao.UserDao;

public class UserDaoImpl extends BaseDao<User> implements UserDao {

	@Override
	public User getUser(Connection conn,User user) {
		// 呼叫BaseDao中獲取一個物件的方法
		User bean = null;
		// 寫sql陳述句
		String sql = "select id,username,password,email from users where username = ? and password = ?";
		bean = getBean(conn,sql, user.getUsername(), user.getPassword());
		return bean;
	}

	@Override
	public boolean checkUsername(Connection conn,User user) {
		// 呼叫BaseDao中獲取一個物件的方法
		User bean = null;
		// 寫sql陳述句
		String sql = "select id,username,password,email from users where username = ?";
		bean = getBean(conn,sql, user.getUsername());
		return bean != null;
	}

	@Override
	public void saveUser(Connection conn,User user) {
		//寫sql陳述句
		String sql = "insert into users(username,password,email) values(?,?,?)";
		//呼叫BaseDao中通用的增刪改的方法
		update(conn,sql, user.getUsername(),user.getPassword(),user.getEmail());
	}

}

【Book.java】

package com.atguigu.bookstore.beans;
/**
 * 圖書類
 * @author songhongkang
 *
 */
public class Book {

	private Integer id;
	private String title; // 書名
	private String author; // 作者
	private double price; // 價格
	private Integer sales; // 銷量
	private Integer stock; // 庫存
	private String imgPath = "static/img/default.jpg"; // 封面圖片的路徑
	//構造器，get()，set()，toString()方法略
}

【Page.java】

package com.atguigu.bookstore.beans;

import java.util.List;
/**
 * 頁碼類
 * @author songhongkang
 *
 */
public class Page<T> {

	private List<T> list; // 每頁查到的記錄存放的集合
	public static final int PAGE_SIZE = 4; // 每頁顯示的記錄數
	private int pageNo; // 當前頁
//	private int totalPageNo; // 總頁數，通過計算得到
	private int totalRecord; // 總記錄數，通過查詢資料庫得到

【User.java】

package com.atguigu.bookstore.beans;
/**
 * 用戶類
 * @author songhongkang
 *
 */
public class User {

	private Integer id;
	private String username;
	private String password;
	private String email;

第8章：資料庫連接池

8.1 JDBC資料庫連接池的必要性

在使用開發基于資料庫的web程式時，傳統的模式基本是按以下步驟：　　
- 在主程式（如servlet、beans）中建立資料庫連接
- 進行sql操作
- 斷開資料庫連接
這種模式開發，存在的問題:
- 普通的JDBC資料庫連接使用 DriverManager 來獲取，每次向資料庫建立連接的時候都要將 Connection 加載到記憶體中，再驗證用戶名和密碼(得花費0.05s～1s的時間)，需要資料庫連接的時候，就向資料庫要求一個，執行完成后再斷開連接，這樣的方式將會消耗大量的資源和時間，資料庫的連接資源并沒有得到很好的重復利用，若同時有幾百人甚至幾千人在線，頻繁的進行資料庫連接操作將占用很多的系統資源，嚴重的甚至會造成服務器的崩潰，
- 對于每一次資料庫連接，使用完后都得斷開，否則，如果程式出現例外而未能關閉，將會導致資料庫系統中的記憶體泄漏，最終將導致重啟資料庫，（回憶：何為Java的記憶體泄漏？）
- 這種開發不能控制被創建的連接物件數，系統資源會被毫無顧及的分配出去，如連接過多，也可能導致記憶體泄漏，服務器崩潰，

8.2 資料庫連接池技術

為解決傳統開發中的資料庫連接問題，可以采用資料庫連接池技術，
資料庫連接池的基本思想：就是為資料庫連接建立一個“緩沖池”，預先在緩沖池中放入一定數量的連接，當需要建立資料庫連接時，只需從“緩沖池”中取出一個，使用完畢之后再放回去，
資料庫連接池負責分配、管理和釋放資料庫連接，它允許應用程式重復使用一個現有的資料庫連接，而不是重新建立一個，
資料庫連接池在初始化時將創建一定數量的資料庫連接放到連接池中，這些資料庫連接的數量是由最小資料庫連接數來設定的，無論這些資料庫連接是否被使用，連接池都將一直保證至少擁有這么多的連接數量，連接池的最大資料庫連接數量限定了這個連接池能占有的最大連接數，當應用程式向連接池請求的連接數超過最大連接數量時，這些請求將被加入到等待佇列中，

作業原理：

資料庫連接池技術的優點

1. 資源重用

由于資料庫連接得以重用，避免了頻繁創建，釋放連接引起的大量性能開銷，在減少系統消耗的基礎上，另一方面也增加了系統運行環境的平穩性，

2. 更快的系統反應速度

資料庫連接池在初始化程序中，往往已經創建了若干資料庫連接置于連接池中備用，此時連接的初始化作業均已完成，對于業務請求處理而言，直接利用現有可用連接，避免了資料庫連接初始化和釋放程序的時間開銷，從而減少了系統的回應時間

3. 新的資源分配手段

對于多應用共享同一資料庫的系統而言，可在應用層通過資料庫連接池的配置，實作某一應用最大可用資料庫連接數的限制，避免某一應用獨占所有的資料庫資源

4. 統一的連接管理，避免資料庫連接泄漏

在較為完善的資料庫連接池實作中，可根據預先的占用超時設定，強制回收被占用連接，從而避免了常規資料庫連接操作中可能出現的資源泄露

8.3 多種開源的資料庫連接池

JDBC 的資料庫連接池使用 javax.sql.DataSource 來表示，DataSource 只是一個介面，該介面通常由服務器(Weblogic, WebSphere, Tomcat)提供實作，也有一些開源組織提供實作：
- DBCP 是Apache提供的資料庫連接池，tomcat 服務器自帶dbcp資料庫連接池，速度相對c3p0較快，但因自身存在BUG，Hibernate3已不再提供支持，
- C3P0 是一個開源組織提供的一個資料庫連接池，速度相對較慢，穩定性還可以，hibernate官方推薦使用
- Proxool 是sourceforge下的一個開源專案資料庫連接池，有監控連接池狀態的功能，穩定性較c3p0差一點
- BoneCP 是一個開源組織提供的資料庫連接池，速度快
- Druid 是阿里提供的資料庫連接池，據說是集DBCP 、C3P0 、Proxool 優點于一身的資料庫連接池，但是速度不確定是否有BoneCP快
DataSource 通常被稱為資料源，它包含連接池和連接池管理兩個部分，習慣上也經常把 DataSource 稱為連接池
DataSource用來取代DriverManager來獲取Connection，獲取速度快，同時可以大幅度提高資料庫訪問速度，
特別注意：
- 資料源和資料庫連接不同，資料源無需創建多個，它是產生資料庫連接的工廠，因此整個應用只需要一個資料源即可，
- 當資料庫訪問結束后，程式還是像以前一樣關閉資料庫連接：conn.close(); 但conn.close()并沒有關閉資料庫的物理連接，它僅僅把資料庫連接釋放，歸還給了資料庫連接池，

8.3.1 C3P0資料庫連接池

獲取連接方式一

//使用C3P0資料庫連接池的方式，獲取資料庫的連接：不推薦
public static Connection getConnection1() throws Exception{
	ComboPooledDataSource cpds = new ComboPooledDataSource();
	cpds.setDriverClass("com.mysql.jdbc.Driver"); 
	cpds.setJdbcUrl("jdbc:mysql://localhost:3306/test");
	cpds.setUser("root");
	cpds.setPassword("abc123");
		
//	cpds.setMaxPoolSize(100);
	
	Connection conn = cpds.getConnection();
	return conn;
}

獲取連接方式二

//使用C3P0資料庫連接池的組態檔方式，獲取資料庫的連接：推薦
private static DataSource cpds = new ComboPooledDataSource("helloc3p0");
public static Connection getConnection2() throws SQLException{
	Connection conn = cpds.getConnection();
	return conn;
}

其中，src下的組態檔為：【c3p0-config.xml】

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
	<named-config name="helloc3p0">
		<!-- 獲取連接的4個基本資訊 -->
		<property name="user">root</property>
		<property name="password">abc123</property>
		<property name="jdbcUrl">jdbc:mysql:///test</property>
		<property name="driverClass">com.mysql.jdbc.Driver</property>
		
		<!-- 涉及到資料庫連接池的管理的相關屬性的設定 -->
		<!-- 若資料庫中連接數不足時, 一次向資料庫服務器申請多少個連接 -->
		<property name="acquireIncrement">5</property>
		<!-- 初始化資料庫連接池時連接的數量 -->
		<property name="initialPoolSize">5</property>
		<!-- 資料庫連接池中的最小的資料庫連接數 -->
		<property name="minPoolSize">5</property>
		<!-- 資料庫連接池中的最大的資料庫連接數 -->
		<property name="maxPoolSize">10</property>
		<!-- C3P0 資料庫連接池可以維護的 Statement 的個數 -->
		<property name="maxStatements">20</property>
		<!-- 每個連接同時可以使用的 Statement 物件的個數 -->
		<property name="maxStatementsPerConnection">5</property>

	</named-config>
</c3p0-config>

8.3.2 DBCP資料庫連接池

DBCP 是 Apache 軟體基金組織下的開源連接池實作，該連接池依賴該組織下的另一個開源系統：Common-pool，如需使用該連接池實作，應在系統中增加如下兩個 jar 檔案：
- Commons-dbcp.jar：連接池的實作
- Commons-pool.jar：連接池實作的依賴庫
Tomcat 的連接池正是采用該連接池來實作的，該資料庫連接池既可以與應用服務器整合使用，也可由應用程式獨立使用，
資料源和資料庫連接不同，資料源無需創建多個，它是產生資料庫連接的工廠，因此整個應用只需要一個資料源即可，
當資料庫訪問結束后，程式還是像以前一樣關閉資料庫連接：conn.close(); 但上面的代碼并沒有關閉資料庫的物理連接，它僅僅把資料庫連接釋放，歸還給了資料庫連接池，
配置屬性說明

屬性	默認值	說明
initialSize	0	連接池啟動時創建的初始化連接數量
maxActive	8	連接池中可同時連接的最大的連接數
maxIdle	8	連接池中最大的空閑的連接數，超過的空閑連接將被釋放，如果設定為負數表示不限制
minIdle	0	連接池中最小的空閑的連接數，低于這個數量會被創建新的連接，該引數越接近maxIdle，性能越好，因為連接的創建和銷毀，都是需要消耗資源的；但是不能太大，
maxWait	無限制	最大等待時間，當沒有可用連接時，連接池等待連接釋放的最大時間，超過該時間限制會拋出例外，如果設定-1表示無限等待
poolPreparedStatements	false	開啟池的Statement是否prepared
maxOpenPreparedStatements	無限制	開啟池的prepared 后的同時最大連接數
minEvictableIdleTimeMillis		連接池中連接，在時間段內一直空閑，被逐出連接池的時間
removeAbandonedTimeout	300	超過時間限制，回收沒有用(廢棄)的連接
removeAbandoned	false	超過removeAbandonedTimeout時間后，是否進行沒用連接（廢棄）的回收

獲取連接方式一：

public static Connection getConnection3() throws Exception {
	BasicDataSource source = new BasicDataSource();
		
	source.setDriverClassName("com.mysql.jdbc.Driver");
	source.setUrl("jdbc:mysql:///test");
	source.setUsername("root");
	source.setPassword("abc123");
		
	//
	source.setInitialSize(10);
		
	Connection conn = source.getConnection();
	return conn;
}

獲取連接方式二：

//使用dbcp資料庫連接池的組態檔方式，獲取資料庫的連接：推薦
private static DataSource source = null;
static{
	try {
		Properties pros = new Properties();
		
		InputStream is = DBCPTest.class.getClassLoader().getResourceAsStream("dbcp.properties");
			
		pros.load(is);
		//根據提供的BasicDataSourceFactory創建對應的DataSource物件
		source = BasicDataSourceFactory.createDataSource(pros);
	} catch (Exception e) {
		e.printStackTrace();
	}
		
}
public static Connection getConnection4() throws Exception {
		
	Connection conn = source.getConnection();
	
	return conn;
}

其中，src下的組態檔為：【dbcp.properties】

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true&useServerPrepStmts=false
username=root
password=abc123

initialSize=10
#...

8.3.3 Druid（德魯伊）資料庫連接池

Druid是阿里巴巴開源平臺上一個資料庫連接池實作，它結合了C3P0、DBCP、Proxool等DB池的優點，同時加入了日志監控，可以很好的監控DB池連接和SQL的執行情況，可以說是針對監控而生的DB連接池，可以說是目前最好的連接池之一，

package com.atguigu.druid;

import java.sql.Connection;
import java.util.Properties;

import javax.sql.DataSource;

import com.alibaba.druid.pool.DruidDataSourceFactory;

public class TestDruid {
	public static void main(String[] args) throws Exception {
		Properties pro = new Properties();		 pro.load(TestDruid.class.getClassLoader().getResourceAsStream("druid.properties"));
		DataSource ds = DruidDataSourceFactory.createDataSource(pro);
		Connection conn = ds.getConnection();
		System.out.println(conn);
	}
}

其中，src下的組態檔為：【druid.properties】

url=jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true
username=root
password=123456
driverClassName=com.mysql.jdbc.Driver

initialSize=10
maxActive=20
maxWait=1000
filters=wall

詳細配置引數：

配置	預設	說明
name		配置這個屬性的意義在于，如果存在多個資料源，監控的時候可以通過名字來區分開來，如果沒有配置，將會生成一個名字，格式是：”DataSource-” + System.identityHashCode(this)
url		連接資料庫的url，不同資料庫不一樣，例如：mysql : jdbc:mysql://10.20.153.104:3306/druid2 oracle : jdbc:oracle:thin:@10.20.149.85:1521:ocnauto
username		連接資料庫的用戶名
password		連接資料庫的密碼，如果你不希望密碼直接寫在組態檔中，可以使用ConfigFilter，詳細看這里：https://github.com/alibaba/druid/wiki/使用ConfigFilter
driverClassName		根據url自動識別這一項可配可不配，如果不配置druid會根據url自動識別dbType，然后選擇相應的driverClassName(建議配置下)
initialSize	0	初始化時建立物理連接的個數，初始化發生在顯示呼叫init方法，或者第一次getConnection時
maxActive	8	最大連接池數量
maxIdle	8	已經不再使用，配置了也沒效果
minIdle		最小連接池數量
maxWait		獲取連接時最大等待時間，單位毫秒，配置了maxWait之后，預設啟用公平鎖，并發效率會有所下降，如果需要可以通過配置useUnfairLock屬性為true使用非公平鎖，
poolPreparedStatements	false	是否快取preparedStatement，也就是PSCache，PSCache對支持游標的資料庫性能提升巨大，比如說oracle，在mysql下建議關閉，
maxOpenPreparedStatements	-1	要啟用PSCache，必須配置大于0，當大于0時，poolPreparedStatements自動觸發修改為true，在Druid中，不會存在Oracle下PSCache占用記憶體過多的問題，可以把這個數值配置大一些，比如說100
validationQuery		用來檢測連接是否有效的sql，要求是一個查詢陳述句，如果validationQuery為null，testOnBorrow、testOnReturn、testWhileIdle都不會其作用，
testOnBorrow	true	申請連接時執行validationQuery檢測連接是否有效，做了這個配置會降低性能，
testOnReturn	false	歸還連接時執行validationQuery檢測連接是否有效，做了這個配置會降低性能
testWhileIdle	false	建議配置為true，不影響性能，并且保證安全性，申請連接的時候檢測，如果空閑時間大于timeBetweenEvictionRunsMillis，執行validationQuery檢測連接是否有效，
timeBetweenEvictionRunsMillis		有兩個含義： 1)Destroy執行緒會檢測連接的間隔時間2)testWhileIdle的判斷依據，詳細看testWhileIdle屬性的說明
numTestsPerEvictionRun		不再使用，一個DruidDataSource只支持一個EvictionRun
minEvictableIdleTimeMillis
connectionInitSqls		物理連接初始化的時候執行的sql
exceptionSorter		根據dbType自動識別當資料庫拋出一些不可恢復的例外時，拋棄連接
filters		屬性型別是字串，通過別名的方式配置擴展插件，常用的插件有：監控統計用的filter:stat日志用的filter:log4j防御sql注入的filter:wall
proxyFilters		型別是List，如果同時配置了filters和proxyFilters，是組合關系，并非替換關系

第9章：Apache-DBUtils實作CRUD操作

9.1 Apache-DBUtils簡介

commons-dbutils 是 Apache 組織提供的一個開源 JDBC工具類別庫，它是對JDBC的簡單封裝，學習成本極低，并且使用dbutils能極大簡化jdbc編碼的作業量，同時也不會影響程式的性能，
API介紹：
- org.apache.commons.dbutils.QueryRunner
- org.apache.commons.dbutils.ResultSetHandler
- 工具類：org.apache.commons.dbutils.DbUtils
API包說明：

9.2 主要API的使用

9.2.1 DbUtils

DbUtils ：提供如關閉連接、裝載JDBC驅動程式等常規作業的工具類，里面的所有方法都是靜態的，主要方法如下：
- public static void close(…) throws java.sql.SQLException：　DbUtils類提供了三個多載的關閉方法，這些方法檢查所提供的引數是不是NULL，如果不是的話，它們就關閉Connection、Statement和ResultSet，
- public static void closeQuietly(…): 這一類方法不僅能在Connection、Statement和ResultSet為NULL情況下避免關閉，還能隱藏一些在程式中拋出的SQLEeception，
- public static void commitAndClose(Connection conn)throws SQLException：用來提交連接的事務，然后關閉連接
- public static void commitAndCloseQuietly(Connection conn)：用來提交連接，然后關閉連接，并且在關閉連接時不拋出SQL例外，
- public static void rollback(Connection conn)throws SQLException：允許conn為null，因為方法內部做了判斷
- public static void rollbackAndClose(Connection conn)throws SQLException
- rollbackAndCloseQuietly(Connection)
- public static boolean loadDriver(java.lang.String driverClassName)：這一方裝載并注冊JDBC驅動程式，如果成功就回傳true，使用該方法，你不需要捕捉這個例外ClassNotFoundException，

9.2.2 QueryRunner類

該類簡單化了SQL查詢，它與ResultSetHandler組合在一起使用可以完成大部分的資料庫操作，能夠大大減少編碼量，
QueryRunner類提供了兩個構造器：
- 默認的構造器
- 需要一個 javax.sql.DataSource 來作引數的構造器
QueryRunner類的主要方法：
- 更新
  - public int update(Connection conn, String sql, Object... params) throws SQLException:用來執行一個更新（插入、更新或洗掉）操作，
  - ......
- 插入
  - public T insert(Connection conn,String sql,ResultSetHandler rsh, Object... params) throws SQLException：只支持INSERT陳述句，其中 rsh - The handler used to create the result object from the ResultSet of auto-generated keys. 回傳值: An object generated by the handler.即自動生成的鍵值
  - ....
- 批處理
  - public int[] batch(Connection conn,String sql,Object[][] params)throws SQLException： INSERT, UPDATE, or DELETE陳述句
  - public T insertBatch(Connection conn,String sql,ResultSetHandler rsh,Object[][] params)throws SQLException：只支持INSERT陳述句
  - .....
- 查詢
  - public Object query(Connection conn, String sql, ResultSetHandler rsh,Object... params) throws SQLException：執行一個查詢操作，在這個查詢中，物件陣列中的每個元素值被用來作為查詢陳述句的置換引數，該方法會自行處理 PreparedStatement 和 ResultSet 的創建和關閉，
  - ......
測驗

// 測驗添加
@Test
public void testInsert() throws Exception {
	QueryRunner runner = new QueryRunner();
	Connection conn = JDBCUtils.getConnection3();
	String sql = "insert into customers(name,email,birth)values(?,?,?)";
	int count = runner.update(conn, sql, "何成飛", "[email protected]", "1992-09-08");

	System.out.println("添加了" + count + "條記錄");
		
	JDBCUtils.closeResource(conn, null);

}

// 測驗洗掉
@Test
public void testDelete() throws Exception {
	QueryRunner runner = new QueryRunner();
	Connection conn = JDBCUtils.getConnection3();
	String sql = "delete from customers where id < ?";
	int count = runner.update(conn, sql,3);

	System.out.println("洗掉了" + count + "條記錄");
		
	JDBCUtils.closeResource(conn, null);

}

9.2.3 ResultSetHandler介面及實作類

該介面用于處理 java.sql.ResultSet，將資料按要求轉換為另一種形式，
ResultSetHandler 介面提供了一個單獨的方法：Object handle (java.sql.ResultSet .rs)，
介面的主要實作類：
- ArrayHandler：把結果集中的第一行資料轉成物件陣列，
- ArrayListHandler：把結果集中的每一行資料都轉成一個陣列，再存放到List中，
- BeanHandler：將結果集中的第一行資料封裝到一個對應的JavaBean實體中，
- BeanListHandler：將結果集中的每一行資料都封裝到一個對應的JavaBean實體中，存放到List里，
- ColumnListHandler：將結果集中某一列的資料存放到List中，
- KeyedHandler(name)：將結果集中的每一行資料都封裝到一個Map里，再把這些map再存到一個map里，其key為指定的key，
- MapHandler：將結果集中的第一行資料封裝到一個Map里，key是列名，value就是對應的值，
- MapListHandler：將結果集中的每一行資料都封裝到一個Map里，然后再存放到List
- ScalarHandler：查詢單個值物件
測驗

/*
 * 測驗查詢:查詢一條記錄
 * 
 * 使用ResultSetHandler的實作類：BeanHandler
 */
@Test
public void testQueryInstance() throws Exception{
	QueryRunner runner = new QueryRunner();

	Connection conn = JDBCUtils.getConnection3();
		
	String sql = "select id,name,email,birth from customers where id = ?";
		
	//
	BeanHandler<Customer> handler = new BeanHandler<>(Customer.class);
	Customer customer = runner.query(conn, sql, handler, 23);
	System.out.println(customer);	
	JDBCUtils.closeResource(conn, null);
}

/*
 * 測驗查詢:查詢多條記錄構成的集合
 * 
 * 使用ResultSetHandler的實作類：BeanListHandler
 */
@Test
public void testQueryList() throws Exception{
	QueryRunner runner = new QueryRunner();

	Connection conn = JDBCUtils.getConnection3();
		
	String sql = "select id,name,email,birth from customers where id < ?";
		
	//
	BeanListHandler<Customer> handler = new BeanListHandler<>(Customer.class);
	List<Customer> list = runner.query(conn, sql, handler, 23);
	list.forEach(System.out::println);
		
	JDBCUtils.closeResource(conn, null);
}

/*
 * 自定義ResultSetHandler的實作類
 */
@Test
public void testQueryInstance1() throws Exception{
	QueryRunner runner = new QueryRunner();

	Connection conn = JDBCUtils.getConnection3();
		
	String sql = "select id,name,email,birth from customers where id = ?";
		
	ResultSetHandler<Customer> handler = new ResultSetHandler<Customer>() {

		@Override
		public Customer handle(ResultSet rs) throws SQLException {
			System.out.println("handle");
//			return new Customer(1,"Tom","[email protected]",new Date(123323432L));
				
			if(rs.next()){
				int id = rs.getInt("id");
				String name = rs.getString("name");
				String email = rs.getString("email");
				Date birth = rs.getDate("birth");
					
				return new Customer(id, name, email, birth);
			}
			return null;
				
		}
	};
		
	Customer customer = runner.query(conn, sql, handler, 23);
		
	System.out.println(customer);
		
	JDBCUtils.closeResource(conn, null);
}

/*
 * 如何查詢類似于最大的，最小的，平均的，總和，個數相關的資料，
 * 使用ScalarHandler
 * 
 */
@Test
public void testQueryValue() throws Exception{
	QueryRunner runner = new QueryRunner();

	Connection conn = JDBCUtils.getConnection3();
		
	//測驗一：
//	String sql = "select count(*) from customers where id < ?";
//	ScalarHandler handler = new ScalarHandler();
//	long count = (long) runner.query(conn, sql, handler, 20);
//	System.out.println(count);
		
	//測驗二：
	String sql = "select max(birth) from customers";
	ScalarHandler handler = new ScalarHandler();
	Date birth = (Date) runner.query(conn, sql, handler);
	System.out.println(birth);
		
	JDBCUtils.closeResource(conn, null);
}

JDBC總結

總結
@Test
public void testUpdateWithTx() {
		
	Connection conn = null;
	try {
		//1.獲取連接的操作（
		//① 手寫的連接：JDBCUtils.getConnection();
		//② 使用資料庫連接池：C3P0;DBCP;Druid
		//2.對資料表進行一系列CRUD操作
		//① 使用PreparedStatement實作通用的增刪改、查詢操作（version 1.0 \ version 2.0)
//version2.0的增刪改public void update(Connection conn,String sql,Object ... args){}
//version2.0的查詢 public <T> T getInstance(Connection conn,Class<T> clazz,String sql,Object ... args){}
		//② 使用dbutils提供的jar包中提供的QueryRunner類
			
		//提交資料
		conn.commit();
			
	
	} catch (Exception e) {
		e.printStackTrace();
			
			
		try {
			//回滾資料
			conn.rollback();
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
			
	}finally{
		//3.關閉連接等操作
		//① JDBCUtils.closeResource();
		//② 使用dbutils提供的jar包中提供的DbUtils類提供了關閉的相關操作
			
	}
}

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/92750.html

標籤：MySQL

上一篇：mysql中的表操作

下一篇：Mysql快速入門

JDBC學習筆記