# jdbc_learning

**Repository Path**: sun81911/jdbc_learning

## Basic Information

- **Project Name**: jdbc_learning
- **Description**: JDBC知识点复习
- **Primary Language**: Java
- **License**: Not specified
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 1
- **Created**: 2020-06-12
- **Last Updated**: 2020-12-19

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

## 获取数据库连接

### 要素

#### 要素一 Driver接口实现类

##### Driver接口介绍

`java.sql.Driver` 接口是所有 JDBC 驱动程序需要实现的接口。这个接口是提供给数据库厂商使用的，不同数据库厂商提供不同的实现

在程序中不需要直接去访问实现了 Driver 接口的类，而是由驱动程序管理器类`java.sql.DriverManager`去调用这些Driver实现

MySQL驱动：`com.mysql.jdbc.Driver`

Oracle驱动：`oracle.jdbc.driver.OracleDriver`

##### 加载与注册JDBC驱动

**加载驱动**：加载 JDBC 驱动需调用`Class` 类的静态方法 `forName`，向其传递要加载的 JDBC 驱动的类名

```java
Class.forName("com.mysql.jdbc.Driver");
```

**注册驱动**：`DriverManager` 类是驱动程序管理器类，负责管理驱动程序

```java
DriverManager.registerDriver(new com.mysql.jdbc.Driver());
```

通常**不显式调用** DriverManager 类的 `registerDriver` 方法来注册驱动程序类的实例，因为 Driver 接口的驱动程序类都包含了静态代码块，在这个静态代码块中，会调用 `DriverManager.registerDriver()` 方法来注册自身的一个实例

```java
static {
    try {
        DriverManager.registerDriver(new Driver());
    } catch (SQLException var1) {
        throw new RuntimeException("Can't register driver!");
    }
}
```

#### 要素二 URL

JDBC URL 用于标识一个被注册的驱动程序，驱动程序管理器通过这个 URL 选择正确的驱动程序，从而建立到数据库的连接

JDBC URL的标准由三部分组成，各部分间用冒号分隔

```url
jdbc:mysql://localhost:3306/jdbc
```

1. `jdbc:mysql` ：jdbc及其子协议，这里为mysql
2. `localhost` ：数据库主机名
3. `3306` ：端口号，mysql默认端口号为3306
4. `jdbc` ：要连接的数据库名称

#### 要素三 用户名和密码

### 连接数据库示例

#### 连接方式一

```java
// 1 连接信息
String url = "jdbc:mysql://localhost:3306/jdbc";
String user = "root";
String password = "123456";
// 2 加载驱动
Class.forName("com.mysql.jdbc.Driver");
/*
当com.mysql.jdbc.Driver被加载到内存时，会执行静态代码块
static {
    try {
        DriverManager.registerDriver(new Driver());
    } catch (SQLException var1) {
        throw new RuntimeException("Can't register driver!");
    }
}
此时已经对驱动进行了注册，不必再手动注册
 */
// Driver driver = (Driver) clazz.newInstance();
// DriverManager.registerDriver(driver);
// 3 获取连接
Connection conn = DriverManager.getConnection(url, user, password);
System.out.println(conn);
```

#### 连接方式二

```java
// 1 读取配置文件信息
ClassLoader classLoader = ClassLoader.getSystemClassLoader();
InputStream is = classLoader.getResourceAsStream("jdbc.properties");
Properties info = new Properties();
info.load(is);
String driverClass = info.getProperty("driverClass");
String url = info.getProperty("url");
String user = info.getProperty("user");
String password = info.getProperty("password");
// 2 加载驱动
Class.forName(driverClass);
// 3 获取连接
Connection conn = DriverManager.getConnection(url, user, password);
System.out.println(conn);
```

其中`jdbc.properties`文件位于资源文件夹`resources`中，数据内容为

```properties
driverClass=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbc
user=root
password=123456
```

使用配置文件的方式保存配置信息，在代码中加载配置文件

##### 优势

1. 实现了代码和数据的分离，如果需要修改配置信息，直接在配置文件中修改
2. 若需要修改配置信息，可避免重新打包

## PreparedStatement

### 操作和访问数据库

数据库连接被用于向数据库服务器发送命令和 SQL 语句，并接受数据库服务器返回的结果。其实一个数据库连接就是一个Socket连接

在 `java.sql` 包中有 3 个接口分别定义了对数据库的调用的不同方式

1. `Statement`：用于执行静态 SQL 语句并返回它所生成结果的对象
2. `PrepatedStatement`：SQL 语句被**预编译**并存储在此对象中，可以使用此对象多次高效地执行该语句
3. `CallableStatement`：用于执行 SQL 存储过程

### Statement的弊端

通过调用 Connection 对象的 `createStatement` 方法创建该对象。该对象用于执行静态的 SQL 语句，并且返回执行结果

Statement 接口中定义了下列方法用于执行 SQL 语句：

```java
int excuteUpdate(String sql) // 执行更新操作INSERT、UPDATE、DELETE 
ResultSet executeQuery(String sql) // 执行查询操作SELECT
```

**问题**

问题一：存在拼串操作，繁琐

问题二：存在SQL注入问题

SQL 注入是利用某些系统没有对用户输入的数据进行充分的检查，而在用户输入数据中注入非法的 SQL 语句段或命令(如：`SELECT user, password FROM user_table WHERE user='a' OR 1 = ' AND password = ' OR '1' ='1'`) ，从而利用系统的 SQL 引擎完成恶意行为的做法

###  PreparedStatement的使用

#### 介绍

可以通过调用 Connection 对象的 `preparedStatement(String sql)` 方法获取 PreparedStatement 对象

PreparedStatement 接口是 `Statement` 的**子接口**，它表示一条**预编译**过的 SQL 语句

PreparedStatement 对象所代表的 SQL 语句中的参数用**问号(?)**来表示，调用 PreparedStatement 对象的setXxx() 方法来设置这些参数，setXxx() 方法有2个参数

- 第1个参数是要设置的 SQL 语句中的参数的**索引**(从 `1` 开始)

- 第2个是设置的 SQL 语句中的参数的**值**

#### 对比Statement

DBServer会对预编译语句提供性能优化。因为预编译语句有可能被重复调用，所以语句在被DBServer的编译器编译后的执行代码被**缓存**下来，那么下次调用时只要是相同的预编译语句就不需要编译，只要将参数直接传入编译过的语句执行代码中就会得到执行

在Statement语句中，即使是相同操作但因为数据内容不一样，所以整个语句本身不能匹配，没有缓存语句的意义，事实是没有数据库会对普通语句编译后的执行代码缓存，因此**每执行一次都要对传入的语句编译一次**

PreparedStatement 可以**防止 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                |

### 通用操作

#### 增删改

```java
// 通用的增删改操作
public int update(String sql, Object... args) {
    Connection conn = null;
    PreparedStatement ps = null;
    int rows = 0;
    try {
        // 1 获取数据库连接
        conn = JDBCUtils.getConnection();
        // 2 预编译sql
        ps = conn.prepareStatement(sql);
        // 3 填充占位符，args的参数个数应与占位符个数相同
        for (int i = 0; i < args.length; i++) {
            ps.setObject(i + 1, args[i]);
        }
        // 4 执行语句
        rows = ps.executeUpdate();
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        // 5 关闭资源
        JDBCUtils.closeResources(conn, ps, null);
    }
    // 6 返回受影响的记录数
    return rows;
}
```

#### 查询

##### 单条记录

```java
// 通用的单个数据查询
public <T> T getInstance(Class<T> clazz, String sql, Object... args) {
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    T t = null;
    try {
        // 1 获取数据库连接
        conn = JDBCUtils.getConnection();
        // 2 预编译sql语句
        ps = conn.prepareStatement(sql);
        // 3 填充占位符
        for (int i = 0; i < args.length; i++) {
            ps.setObject(i + 1, args[i]);
        }
        // 4 执行查询语句
        rs = ps.executeQuery();
        // 5 获取结果集的元数据
        ResultSetMetaData rsmd = rs.getMetaData();
        // 6 获取返回结果集的列数
        int count = rsmd.getColumnCount();
        // 7 判断是否存在下一条记录，若存在则返回true并下移指针，否则false
        if (rs.next()) {
            // 8 通过反射得到该运行时类的一个对象
            t = clazz.newInstance();
            // 9 循环装配对象t
            for (int i = 0; i < count; i++) {
                // 10 获取结果集第i+1个字段的值
                Object obj = rs.getObject(i + 1);
                // 11 获取结果集第i+1个字段的别名，若无别名则返回列名
                String name = rsmd.getColumnLabel(i + 1);
                // 12 通过反射获取名为name的属性
                Field f = clazz.getDeclaredField(name);
                // 13 设置属性访问权限
                f.setAccessible(true);
                // 14 将t对象的f属性的值置为obj
                f.set(t, obj);
            }
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        // 15 关闭资源
        JDBCUtils.closeResources(conn, ps, rs);
    }
    // 16 返回t对象
    return t;
}
```

##### 多条记录

```java
// 通用的多个数据查询
public <T> List<T> getList(Class<T> clazz, String sql, Object... args) {
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    ArrayList<T> list = new ArrayList<T>();
    try {
        // 1 获取数据库连接
        conn = JDBCUtils.getConnection();
        // 2 预编译sql语句
        ps = conn.prepareStatement(sql);
        // 3 填充占位符
        for (int i = 0; i < args.length; i++) {
            ps.setObject(i + 1, args[i]);
        }
        // 4 执行查询语句
        rs = ps.executeQuery();
        // 5 获取结果集的元数据
        ResultSetMetaData rsmd = rs.getMetaData();
        // 6 获取返回结果集的列数
        int count = rsmd.getColumnCount();
        // 7 判断是否存在下一条记录，若存在则返回true并下移指针，否则false
        while (rs.next()) {
            // 8 通过反射得到该运行时类的一个对象
            T t = clazz.newInstance();
            // 9 循环装配对象t
            for (int i = 0; i < count; i++) {
                // 10 获取结果集第i+1个字段的值
                Object obj = rs.getObject(i + 1);
                // 11 获取结果集第i+1个字段的别名，若无别名则返回列名
                String name = rsmd.getColumnLabel(i + 1);
                // 12 通过反射获取名为name的属性
                Field f = clazz.getDeclaredField(name);
                // 13 设置属性访问权限
                f.setAccessible(true);
                // 14 将t对象的f属性的值置为obj
                f.set(t, obj);
            }
            // 15 将t对象加入到列表list中
            list.add(t);
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        // 16 关闭资源
        JDBCUtils.closeResources(conn, ps, rs);
    }
    // 17 返回t对象
    return list;
}
```

### ResultSet与ResultSetMetaData

#### ResultSet

查询需要调用PreparedStatement 的 `executeQuery()` 方法，查询结果是一个ResultSet 对象

ResultSet 对象以逻辑表格的形式封装了执行数据库操作的结果集，ResultSet 接口由数据库厂商提供实现

ResultSet 返回的实际上就是一张数据表，有一个**指针**指向数据表的第一条记录的前面

ResultSet 对象维护了一个指向当前数据行的**游标**，初始的时候，游标在**第一行之前**，可以通过 ResultSet 对象的 `next()` 方法移动到下一行。调用 `next()`方法检测下一行是否有效。若**有效**，该方法返回 `true`，且指针**下移**，否则返回`false`

当指针指向一行时，可以通过调用 getXxx(int index) 或 getXxx(int columnName) 获取每一列的值

注意：Java与数据库交互涉及到的相关Java API中的索引都从`1`开始

#### ResultSetMetaData

可用于获取关于 ResultSet 对象中列的类型和属性信息的对象

通过ResultSet的`getMetaData()`方法可以的到该结果集的元数据ResultSetMetaData

##### 常用方法

| 方法                             | 作用                                             |
| -------------------------------- | ------------------------------------------------ |
| getColumnName(int column)        | 获取指定列的名称                                 |
| getColumnLabel(int column)       | 获取指定列的别名，若不指定别名则返回列名         |
| getColumnCount()                 | 返回当前 ResultSet 对象中的列数                  |
| getColumnTypeName(int column)    | 检索指定列的数据库特定的类型名称                 |
| getColumnDisplaySize(int column) | 返回指定列的最大标准宽度，以字符为单位           |
| isNullable(int column)           | 判断指定列中的值是否可以为 `null`                |
| isAutoIncrement(int column)      | 判断指定列是否为自动编号，这样这些列仍然是只读的 |

### 释放资源

释放ResultSet、Statement和Connection

数据库连接（Connection）是非常稀有的资源，用完后必须马上释放，如果Connection不能及时正确的关闭将导致系统宕机。Connection的使用原则是**尽量晚创建，尽量早释放**

可以在`finally`中关闭，保证及时其他代码出现异常，资源也一定能被关闭

```java
// 关闭资源
public static void closeResources(Connection conn, Statement stat, ResultSet rs) {
	// 关闭Connection
    try {
        if (conn != null)
            conn.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }
    // 关闭Statement
    try {
        if (stat != null)
            stat.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }
    // 关闭ResultSet
    try {
        if (rs != null)
            rs.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}
```

## BLOB类型字段

### MySQL BLOB类型

MySQL中，BLOB是一个二进制大型对象，是一个可以存储大量数据的容器，它能容纳不同大小的数据

插入`BLOB`类型的数据必须使用`PreparedStatement`，因为BLOB类型的数据无法使用字符串拼接写的

MySQL的四种BLOB类型(除了在存储的最大信息量上不同外，他们是等同的)

| 类型       | 大小（字节） |
| ---------- | ------------ |
| Tinyblob   | 最大 255     |
| Blob       | 最大 65K     |
| Mediumblob | 最大 16M     |
| Longblob   | 最大 4G      |

实际使用中根据需要存入的数据大小定义不同的BLOB类型，如果存储的文件过大，数据库的性能会下降

**注意**：如果在指定了相关的Blob类型以后，还报错：`xxx too large`，那么需要修改**全局变量**`max_allowed_packet`

在MySQL中查看`max_allowed_packet`的值，默认为`4M`即`4194304`

```mysql
show global variables like 'max_allowed_packet';
```

修改`max_allowed_packet`的值为`16M`即`16777216`

```mysql
set global max_allowed_packet = 16777216;
```

当服务器重新启动时，会将所有全局变量赋初始值

### 更新Blob类型数据

```java
Connection conn = null;
PreparedStatement ps = null;
FileInputStream is = null;
try {
    // 1 获取数据库连接
    conn = JDBCUtils.getConnection();
    String sql = "insert into customers(name, email, birth, photo) values(?,?,?,?)";
    // 2 预编译sql语句
    ps = conn.prepareStatement(sql);
    // 3 填充占位符
    ps.setObject(1, "张三");
    ps.setObject(2, "zhang@qq.com");
    ps.setObject(3, "2019-12-12");
    // 4 获取需要插入的文件的输入流
    is = new FileInputStream("photo.jpg");
    // 5 将文件输入流字节插入到数据库中
    ps.setBlob(4, is);
    // 6 执行语句
    ps.execute();
} catch (Exception e) {
    e.printStackTrace();
} finally {
    // 7 关闭资源
    if (is != null) {
        try {
            is.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    JDBCUtils.closeResources(conn, ps, null);
}
```

**修改**Blob数据同理插入，将`insert into`插入语句换成`update set`

### 查询Blob类型数据

```java
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
FileOutputStream fos = null;
InputStream is = null;
try {
    // 1 获取数据库连接
    conn = JDBCUtils.getConnection();
    String sql = "select * from customers where id = ?";
    // 2 预编译sql语句
    ps = conn.prepareStatement(sql);
    // 3 填充占位符
    ps.setObject(1, 20);
    // 4 执行查询语句返回结果集
    rs = ps.executeQuery();
    // 5 判断结果集是否存在下一条数据，若存在则返回true并下移指针，否则返回false
    if (rs.next()) {
        // 6 获取结果集中指定字段的数据
        int id = rs.getInt("id");
        String name = rs.getString("name");
        String email = rs.getString("email");
        Date birth = rs.getDate("birth");
        // 7 封装customer对象
        Customer c = new Customer(id, name, email, birth);
        System.out.println(c);
        // 8 获取blob类型数据
        Blob photo = rs.getBlob("photo");
        // 9 从blob对象中获取文件的输入流
        is = photo.getBinaryStream();
        // 10 实例化文件输出流
        fos = new FileOutputStream("photo_download.jpg");
        // 11 缓冲区
        byte[] buffer = new byte[1024];
        int len;
        // 12 将blob输入流中的字节通过缓冲区循环写到文件输出流中直至结束
        while ((len = is.read(buffer)) != -1) {
            fos.write(buffer, 0, len);
        }
    }
} catch (Exception e) {
    e.printStackTrace();
} finally {
    // 13 关闭资源
    if (fos != null) {
        try {
            fos.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    if (is != null) {
        try {
            is.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    JDBCUtils.closeResources(conn, ps, rs);
}
```

## 批量插入

### 批量执行SQL语句

当需要成批插入或者更新记录时，可以采用Java的批量更新机制，这一机制允许多条语句一次性提交给数据库批量处理，通常情况下比单独提交处理更有效率

JDBC的批量处理语句包括下面三个方法

1. `addBatch()`：添加需要批量处理的SQL语句或是参数
2. `executeBatch()`：执行批量处理语句
3. `clearBatch()`：清空缓存的数据

### 高效的批量插入

向数据库中插入20000条数据

#### 实现方式一

直接使用PreparedStatement直接执行20000次与数据库交互

```java
long start = System.currentTimeMillis();
Connection conn = null;
PreparedStatement ps = null;
try {
    conn = JDBCUtils.getConnection();
    String sql = "insert into goods(name) values(?)";
    ps = conn.prepareStatement(sql);
    for (int i = 1; i <= 20000; i++) {
        ps.setObject(1, "good_" + i);
        ps.execute();
    }
} catch (SQLException e) {
    e.printStackTrace();
} finally {
    JDBCUtils.closeResources(conn, ps, null);
}
long end = System.currentTimeMillis();
System.out.println("end - start: " + (end - start)); // end - start: 14463
```

操作执行时间为`14463`毫秒

#### 实现方式二

使用批处理的方式对数据库进行20000条数据的批量插入

首先将参数`rewriteBatchedStatements=true`追加到连接数据库的URL后面

因为MySQL服务器默认是**关闭**批处理的，需要通过设置参数，让MySQL开启批处理的支持

```java
long start = System.currentTimeMillis();
Connection conn = null;
PreparedStatement ps = null;
try {
    conn = JDBCUtils.getConnection();
    String sql = "insert into goods(name) values(?)";
    ps = conn.prepareStatement(sql);
    for (int i = 1; i <= 20000; i++) {
        ps.setObject(1, "good_" + i);
        // 不要直接执行
//                ps.execute();
        // 1 将sql语句保存起来
        ps.addBatch();
        // 2 每隔一定次数写入一次
        if (i % 1000 == 0) {
            // 3 执行批量操作
            ps.executeBatch();
            // 4 清空存储sql
            ps.clearBatch();
        }
    }
} catch (SQLException e) {
    e.printStackTrace();
} finally {
    JDBCUtils.closeResources(conn, ps, null);
}
long end = System.currentTimeMillis();
System.out.println("end - start: " + (end - start)); // end - start: 1873
```

操作执行时间为`1873`毫秒，在数据较多的情况下，操作时长明显小于直接插入时的`14463`毫秒

#### 实现方式三

此时在方式二的基础上，不允许数据库进行自动提交，开启事务，执行完所有操作之后再进行事务的提交

```java
long start = System.currentTimeMillis();
Connection conn = null;
PreparedStatement ps = null;
try {
    conn = JDBCUtils.getConnection();
    // 1 设置不允许自动提交
    conn.setAutoCommit(false);
    String sql = "insert into goods(name) values(?)";
    ps = conn.prepareStatement(sql);
    for (int i = 1; i <= 20000; i++) {
        ps.setObject(1, "good_" + i);
        // 2 将sql语句保存起来
        ps.addBatch();
        // 3 每隔一定次数写入一次
        if (i % 1000 == 0) {
            // 4 执行批量操作
            ps.executeBatch();
            // 5 清空存储sql
            ps.clearBatch();
        }
    }
    // 6 执行完所有sql之后再一次提交事务
    conn.commit();
} catch (SQLException e) {
    e.printStackTrace();
} finally {
    JDBCUtils.closeResources(conn, ps, null);
}
long end = System.currentTimeMillis();
System.out.println("end - start: " + (end - start)); // end - start: 1419
```

操作执行时间为`1419`毫秒

## 数据库事务

### 数据库事务介绍

**事务**：一组逻辑操作单元，使数据从一种状态变换到另一种状态

事务处理（事务操作）：保证所有事务都作为一个工作单元来执行，即使出现了故障，都不能改变这种执行方式。当在一个事务中执行多个操作时，要么所有的事务都被**提交(commit)**，那么这些修改就永久地保存下来；要么数据库管理系统将放弃所作的所有修改，整个事务**回滚(rollback)**到最初状态

为确保数据库中数据的一致性，数据的操纵应当是离散的成组的逻辑单元：当它全部完成时，数据的一致性可以保持，而当这个单元中的一部分操作失败，整个事务应全部视为错误，所有从起始点以后的操作应**全部回退**到开始状态

### JDBC事务处理

数据一旦提交，就不可回滚

#### 提交事务

当一个连接对象被创建时，**默认情况下是自动提交事务**：每次执行一个 SQL 语句时，如果执行成功，就会向数据库自动提交，而不能回滚

**关闭数据库连接**，数据就会自动的提交。如果多个操作，每个操作使用的是自己单独的连接，则无法保证事务。即**同一个事务的多个操作必须在同一个连接下**

#### 事务流程

1. 调用 Connection 对象的 `setAutoCommit(false);` 以**取消自动提交事务**
2. 在所有的 SQL 语句都**成功执行**后，调用 `commit(); `方法提交事务
3. 在出现**异常**时，调用 `rollback();` 方法回滚事务

若此时 Connection 没有被关闭，还可能被重复使用，则需要恢复其自动提交状态`setAutoCommit(true)`。尤其是在使用数据库连接池技术时，执行close()方法前，**恢复自动提交状态**

### 通用操作 - 支持事务操作

#### 增删改

```java
// 通用的增删改操作，支持事务操作
public int update(Connection conn, String sql, Object... args) {
    PreparedStatement ps = null;
    int rows = 0;
    try {
        // 1 通过传进来的连接进行预编译sql
        ps = conn.prepareStatement(sql);
        // 2 填充占位符，args的参数个数应与占位符个数相同
        for (int i = 0; i < args.length; i++) {
            ps.setObject(i + 1, args[i]);
        }
        // 3 执行语句
        rows = ps.executeUpdate();
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        // 4 关闭资源，注意此时不要关闭数据库连接
        JDBCUtils.closeResources(null, ps, null);
    }
    // 5 返回受影响的记录数
    return rows;
}
```

#### 查询

##### 单条记录

```java
// 通用的单个数据查询，支持事务操作
public <T> T getInstance(Connection conn, Class<T> clazz, String sql, Object... args) {
    PreparedStatement ps = null;
    ResultSet rs = null;
    T t = null;
    try {
        // 1 预编译sql语句
        ps = conn.prepareStatement(sql);
        // 2 填充占位符
        for (int i = 0; i < args.length; i++) {
            ps.setObject(i + 1, args[i]);
        }
        // 3 执行查询语句
        rs = ps.executeQuery();
        // 4 获取结果集的元数据
        ResultSetMetaData rsmd = rs.getMetaData();
        // 5 获取返回结果集的列数
        int count = rsmd.getColumnCount();
        // 6 判断是否存在下一条记录，若存在则返回true并下移指针，否则false
        if (rs.next()) {
            // 7 通过反射得到该运行时类的一个对象
            t = clazz.newInstance();
            // 8 循环装配对象t
            for (int i = 0; i < count; i++) {
                // 9 获取结果集第i+1个字段的值
                Object obj = rs.getObject(i + 1);
                // 10 获取结果集第i+1个字段的别名，若无别名则返回列名
                String name = rsmd.getColumnLabel(i + 1);
                // 11 通过反射获取名为name的属性
                Field f = clazz.getDeclaredField(name);
                // 12 设置属性访问权限
                f.setAccessible(true);
                // 13 将t对象的f属性的值置为obj
                f.set(t, obj);
            }
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        // 14 关闭资源
        JDBCUtils.closeResources(null, ps, rs);
    }
    // 15 返回t对象
    return t;
}
```

##### 多条记录

```java
// 通用的多个数据查询
public <T> List<T> getList(Connection conn, Class<T> clazz, String sql, Object... args) {
    PreparedStatement ps = null;
    ResultSet rs = null;
    ArrayList<T> list = new ArrayList<T>();
    try {
        // 1 预编译sql语句
        ps = conn.prepareStatement(sql);
        // 2 填充占位符
        for (int i = 0; i < args.length; i++) {
            ps.setObject(i + 1, args[i]);
        }
        // 3 执行查询语句
        rs = ps.executeQuery();
        // 4 获取结果集的元数据
        ResultSetMetaData rsmd = rs.getMetaData();
        // 5 获取返回结果集的列数
        int count = rsmd.getColumnCount();
        // 6 判断是否存在下一条记录，若存在则返回true并下移指针，否则false
        while (rs.next()) {
            // 7 通过反射得到该运行时类的一个对象
            T t = clazz.newInstance();
            // 8 循环装配对象t
            for (int i = 0; i < count; i++) {
                // 9 获取结果集第i+1个字段的值
                Object obj = rs.getObject(i + 1);
                // 10 获取结果集第i+1个字段的别名，若无别名则返回列名
                String name = rsmd.getColumnLabel(i + 1);
                // 11 通过反射获取名为name的属性
                Field f = clazz.getDeclaredField(name);
                // 12 设置属性访问权限
                f.setAccessible(true);
                // 13 将t对象的f属性的值置为obj
                f.set(t, obj);
            }
            // 14 将t对象加入到列表list中
            list.add(t);
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        // 15 关闭资源
        JDBCUtils.closeResources(null, ps, rs);
    }
    // 16 返回t对象
    return list;
}
```

### 模拟转账案例

```java
Connection conn = null;
try {
    // 1 获取数据库连接
    conn = JDBCUtils.getConnection();
    // 2 取消dml语句的自动提交并开启事务
    conn.setAutoCommit(false);
    // 3 模拟转账的扣钱
    String sql = "update user_table set balance = balance - 100 where user = ?";
    update(conn, sql, "AA");
    // 4 模拟异常中断情况
    System.out.println(1 / 0);
    // 5 模拟转账的收钱
    String sql2 = "update user_table set balance = balance + 100 where user = ?";
    update(conn, sql2, "BB");
    // 6 提交事务
    conn.commit();
    System.out.println("转账成功");
} catch (Exception e) {
    e.printStackTrace();
    // 7 若出现异常则回滚事务
    try {
        conn.rollback();
    } catch (SQLException ex) {
        ex.printStackTrace();
    }
} finally {
    // 8 将数据库的自动提交恢复默认状态
    try {
        conn.setAutoCommit(true);
    } catch (SQLException e) {
        e.printStackTrace();
    }
    // 9 关闭资源
    JDBCUtils.closeResources(conn, null, null);
}
```

### 设置

每启动一个 mysql 程序，就会获得一个单独的数据库连接，每个数据库连接都有一个全局变量`@@tx_isolation`，表示当前的事务隔离级别

#### 隔离级别

##### 在MySQL中

查看当前的隔离级别

```mysql
select @@tx_isolation;
```

设置当前 MySQL 连接的隔离级别为`READ_COMMITED`

```mysql
set transaction isolation level read committed;
```

设置数据库系统的全局的隔离级别为`READ_COMMITED`

```mysql
set global transaction isolation level read committed;
```

##### 在Java中

在Connection类中可以调用相应方法查看并设置数据库的隔离级别

查看数据库隔离级别

```java
int getTransactionIsolation()
```

设置数据库隔离级别

```java
void setTransactionIsolation(int level)
```

其中在Connection接口中定义的隔离级别常量有

| 常量                         | 值   |
| ---------------------------- | ---- |
| TRANSACTION_NONE             | 0    |
| TRANSACTION_READ_UNCOMMITTED | 1    |
| TRANSACTION_READ_COMMITTED   | 2    |
| TRANSACTION_REPEATABLE_READ  | 4    |
| TRANSACTION_SERIALIZABLE     | 8    |

#### 用户设置

##### 创建用户

创建mysql数据库用户并指定密码

```mysql
create user username identified by 'password';
```

- `username`：用户名

- `password`：密码

##### 授予权限

**语法**

```mysql
grant privileges on databasename.tablename to username@host;
```

`privileges`：权限，表示要授予的权限，例如有` select` , `insert` , `delete`, `update`等，若要授予全部权限，则填 `all privileges`

`databasename`：数据库名，可以使用通配符`*`代表所有

`tablename`：表名，可以使用通配符`*`代表所有

`username`：用户名

`host`：主机地址，可以使用`'%'`代表所有主机地址

**示例**

授予从任何地址登录的tom用户，对所有库所有表的全部权限，并将其密码设为abc123

```mysql
grant all privileges on *.* to tom@'%' identified by 'abc123';
```

授予从主机地址登录的tom用户，对goods这个库下的所有表的插删改查的权限

```mysql
grant select,insert,delete,update on goods.* to tom@localhost identified by 'abc123';
```

然后使用

```mysql
flush privileges;
```

刷新权限

## DAO及相关实现类

DAO：Data Access Object访问数据信息的类和接口，包括了对数据的CRUD（Create、Retrival、Update、Delete），而不包含任何业务相关的信息

以数据库中的`customer`表为例

### BaseDao

抽象基类，不可实例化，用于派生出不同表的Dao实现类

```java
public abstract class BaseDao<T> {
	// 当前泛型的运行时类
    private Class<T> clazz = null;
	// 初始化运行时类clazz
    {
        // 1 获取当前类的父类的泛型，用于在BaseDao的子类xxxDao中获取到继承父类时父类所指定的泛型类型
        Type t = this.getClass().getGenericSuperclass();
        ParameterizedType pt = (ParameterizedType) t;
        Type[] args = pt.getActualTypeArguments();
        // 2 将父类的泛型类型赋予clazz对象，进行反射操作
        clazz = (Class<T>) args[0]; // 父类只有一个泛型，直接取第一个
    }
    // 通用的单个特殊值的查询，支持事务操作
    public <E> E getValue(Connection conn, String sql, Object... args) {
        Object obj = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            // 1 预编译sql语句
            ps = conn.prepareStatement(sql);
            // 2 填充占位符
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            // 3 执行查询语句，返回结果集
            rs = ps.executeQuery();
            // 4 判断是否有下一条记录，若有则返回true并下移指针，否则返回false
            if (rs.next()) {
                // 5 返回该单个值
                obj = rs.getObject(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 6 关闭资源
            JDBCUtils.closeResources(null, ps, rs);
        }
        // 7 返回该单个结果
        return (E) obj;
    }
    // 通用的多条记录查询，支持事务操作
    public List<T> getList(Connection conn, String sql, Object... args) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        ArrayList<T> list = new ArrayList<T>();
        try {
            // 1 预编译sql语句
            ps = conn.prepareStatement(sql);
            // 2 填充占位符
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            // 3 执行查询语句
            rs = ps.executeQuery();
            // 4 获取结果集的元数据
            ResultSetMetaData rsmd = rs.getMetaData();
            // 5 获取返回结果集的列数
            int count = rsmd.getColumnCount();
            // 6 判断是否存在下一条记录，若存在则返回true并下移指针，否则false
            while (rs.next()) {
                // 7 通过反射得到该运行时类的一个对象
                T t = clazz.newInstance();
                // 8 循环装配对象t
                for (int i = 0; i < count; i++) {
                    // 9 获取结果集第i+1个字段的值
                    Object obj = rs.getObject(i + 1);
                    // 10 获取结果集第i+1个字段的别名，若无别名则返回列名
                    String name = rsmd.getColumnLabel(i + 1);
                    // 11 通过反射获取名为name的属性
                    Field f = clazz.getDeclaredField(name);
                    // 12 设置属性访问权限
                    f.setAccessible(true);
                    // 13 将t对象的f属性的值置为obj
                    f.set(t, obj);
                }
                // 14 将t对象加入到列表list中
                list.add(t);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 15 关闭资源
            JDBCUtils.closeResources(null, ps, rs);
        }
        // 16 返回t对象
        return list;
    }
    // 通用的单条记录查询，支持事务操作
    public T getInstance(Connection conn, String sql, Object... args) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        T t = null;
        try {
            // 1 预编译sql语句
            ps = conn.prepareStatement(sql);
            // 2 填充占位符
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            // 3 执行查询语句
            rs = ps.executeQuery();
            // 4 获取结果集的元数据
            ResultSetMetaData rsmd = rs.getMetaData();
            // 5 获取返回结果集的列数
            int count = rsmd.getColumnCount();
            // 6 判断是否存在下一条记录，若存在则返回true并下移指针，否则false
            if (rs.next()) {
                // 7 通过反射得到该运行时类的一个对象
                t = clazz.newInstance();
                // 8 循环装配对象t
                for (int i = 0; i < count; i++) {
                    // 9 获取结果集第i+1个字段的值
                    Object obj = rs.getObject(i + 1);
                    // 10 获取结果集第i+1个字段的别名，若无别名则返回列名
                    String name = rsmd.getColumnLabel(i + 1);
                    // 11 通过反射获取名为name的属性
                    Field f = clazz.getDeclaredField(name);
                    // 12 设置属性访问权限
                    f.setAccessible(true);
                    // 13 将t对象的f属性的值置为obj
                    f.set(t, obj);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 14 关闭资源
            JDBCUtils.closeResources(null, ps, rs);
        }
        // 15 返回t对象
        return t;
    }
    // 通用的增删改操作，支持事务操作
    public int update(Connection conn, String sql, Object... args) {
        PreparedStatement ps = null;
        int rows = 0;
        try {
            // 1 通过传进来的连接进行预编译sql
            ps = conn.prepareStatement(sql);
            // 2 填充占位符，args的参数个数应与占位符个数相同
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            // 3 执行语句
            rows = ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 4 关闭资源，注意此时不要关闭数据库连接
            JDBCUtils.closeResources(null, ps, null);
        }
        // 5 返回受影响的记录数
        return rows;
    }
}
```

其中包括

- `public int update(Connection conn, String sql, Object... args)`
- `public T getInstance(Connection conn, String sql, Object... args)`
- `public List<T> getList(Connection conn, String sql, Object... args)`
- `public <E> E getValue(Connection conn, String sql, Object... args)`

| 方法        | 作用                             |
| ----------- | -------------------------------- |
| update      | 通用的增删改操作                 |
| getInstance | 通用的单条记录查询               |
| getList     | 通用的多条记录查询               |
| getValue    | 泛型方法，通用的单个特殊值的查询 |

### CustomerDao

customer表的dao接口，规范操作方法

```java
public interface CustomerDao {
    /**
     * 将customer对象添加到数据库中
     *
     * @param conn
     * @param customer
     */
    void insert(Connection conn, Customer customer);
    /**
     * 根据指定id删除记录
     *
     * @param conn
     * @param id
     */
    void deleteById(Connection conn, int id);
    /**
     * 根据customer对象中的id修改数据库中指定id的记录
     *
     * @param conn
     * @param customer
     */
    void updateById(Connection conn, Customer customer);
    /**
     * 根据指定id获取customer对象
     *
     * @param conn
     * @param id
     * @return
     */
    Customer getById(Connection conn, int id);
    /**
     * 查询表中的所有记录
     *
     * @param conn
     * @return
     */
    List<Customer> getAll(Connection conn);

    /**
     * 返回数据表记录总数
     * @param conn
     * @return
     */
    long getCount(Connection conn);
    /**
     * 返回最大的生日
     * @param conn
     * @return
     */
    Date getMaxBirth(Connection conn);
}
```

### CustomerDaoImpl

CustomerDao接口的实现类，同时继承BaseDao，用以操作数据库中customer表的数据

```java
// 继承于抽象基类BaseDao，实现类CustomerDao接口
public class CustomerDaoImpl extends BaseDao<Customer> implements CustomerDao {
    @Override
    public void insert(Connection conn, Customer customer) {
        String sql = "insert into customers(name, email, birth) values(?,?,?)";
        update(conn, sql, customer.getName(), customer.getEmail(), customer.getBirth());
    }
    @Override
    public void deleteById(Connection conn, int id) {
        String sql = "delete from customers where id = ?";
        update(conn, sql, id);
    }
    @Override
    public void updateById(Connection conn, Customer customer) {
        String sql = "update customers set name = ?, email = ?, birth = ? where id = ?";
        update(conn, sql, customer.getName(), customer.getEmail(), customer.getBirth(), customer.getId());
    }
    @Override
    public Customer getById(Connection conn, int id) {
        String sql = "select id, name, email, birth from customers where id = ?";
        return getInstance(conn, sql, id);
    }
    @Override
    public List<Customer> getAll(Connection conn) {
        String sql = "select id, name, email, birth from customers";
        return getList(conn, sql);
    }
    @Override
    public long getCount(Connection conn) {
        String sql = "select count(*) from customers";
        return getValue(conn, sql);
    }
    @Override
    public Date getMaxBirth(Connection conn) {
        String sql = "select max(birth) from customers";
        return getValue(conn, sql);
    }
}
```

### CustomerDaoImplTest

对CustomerDaoImpl实现类进行单元测试，确保每个方法可以正常运行

```java
public class CustomerDaoImplTest {
    CustomerDaoImpl dao = new CustomerDaoImpl();
    @Test
    public void insert() {
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection();
            Customer cus = new Customer(1, "李四", "li@qq.com", DateUtils.getSqlDate("2019-04-14"));
            dao.insert(conn, cus);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResources(conn, null, null);
        }
    }
    @Test
    public void deleteById() {
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection();
            dao.deleteById(conn, 20);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResources(conn, null, null);
        }
    }
    @Test
    public void updateById() {
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection();
            Customer cus = new Customer(21, "李四", "lisi@qq.com", DateUtils.getSqlDate("2019-12-14"));
            dao.updateById(conn, cus);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResources(conn, null, null);
        }
    }
    @Test
    public void getById() {
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection();
            Customer customer = dao.getById(conn, 21);
            System.out.println(customer);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResources(conn, null, null);
        }
    }
    @Test
    public void getAll() {
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection();
            List<Customer> list = dao.getAll(conn);
            list.forEach(System.out::println);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResources(conn, null, null);
        }
    }
    @Test
    public void getCount() {
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection();
            long count = dao.getCount(conn);
            System.out.println(count);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResources(conn, null, null);
        }
    }
    @Test
    public void getMaxBirth() {
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection();
            Date date = dao.getMaxBirth(conn);
            System.out.println(date);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResources(conn, null, null);
        }
    }
}
```

## 数据库连接池

### 数据库连接池的必要性

#### 传统模式

在使用开发基于数据库的web程序时，传统的模式基本是按以下步骤

1. 在主程序（如servlet、beans）中建立数据库连接
2. 进行sql操作
3. 断开数据库连接

#### 存在的问题

普通的JDBC数据库连接使用 DriverManager 来获取，每次向数据库建立连接的时候都要将 Connection加载到内存中，再验证用户名和密码(得花费0.05s～1s的时间)。需要数据库连接的时候，就向数据库要求一个，执行完成后再断开连接。这样的方式将会消耗大量的资源和时间。数据库的连接资源并**没有得到很好的重复利用**。若同时有几百人甚至几千人在线，**频繁的进行数据库连接操作**将占用很多的系统资源，严重的甚至会造成服务器的崩溃

对于每一次数据库连接，使用完后都得断开。否则，如果程序出现异常而未能关闭，将会导致数据库系统中的**内存泄漏**，最终将导致重启数据库

这种开发不能控制被创建的连接对象数，系统资源会被毫无顾及的分配出去，如连接过多，也可能导致内存泄漏，服务器崩溃

### 数据库连接池技术

#### 基本思想

就是为数据库连接建立一个“缓冲池”。预先在缓冲池中放入一定数量的连接，当需要建立数据库连接时，只需从“缓冲池”中取出一个，使用完毕之后再放回去

数据库连接池负责分配、管理和释放数据库连接，它允许应用程序**重复使用一个现有的数据库连接**，而不是重新建立一个

数据库连接池在**初始化时将创建一定数量的数据库连接放到连接池中**，这些数据库连接的数量是由最小数据库连接数来设定的。无论这些数据库连接是否被使用，连接池都将一直保证至少拥有这么多的连接数量。连接池的最大数据库连接数量限定了这个连接池能占有的最大连接数，**当应用程序向连接池请求的连接数超过最大连接数量时，这些请求将被加入到等待队列中**

#### 优点

1. **资源重用**

   由于数据库连接得以重用，**避免了频繁创建，释放连接引起的大量性能开销**。在减少系统消耗的基础上，另一方面也增加了系统运行环境的平稳性

2. **更快的系统反应速度**

   数据库连接池在初始化过程中，往往已经创建了若干数据库连接置于连接池中备用。此时连接的初始化工作均已完成。对于业务请求处理而言，直接利用现有可用连接，避免了数据库连接初始化和释放过程的时间开销，从而减少了系统的响应时

3. **新的资源分配手段**

   对于多应用共享同一数据库的系统而言，可在应用层通过数据库连接池的配置，实现某一应用最大可用数据库连接数的限制，避免某一应用独占所有的数据库资源

4. **统一的连接管理，避免数据库连接泄漏**

   在较为完善的数据库连接池实现中，可根据预先的占用超时设定，强制回收被占用连接，从而避免了常规数据库连接操作中可能出现的资源泄露

### 多种开源的数据库连接池

JDBC 的数据库连接池使用 `javax.sql.DataSource` 来表示，DataSource 只是一个接口，该接口通常由服务器(Weblogic, WebSphere, Tomcat)提供实现，也有一些开源组织提供实现

#### 列举

1. **DBCP**：是Apache提供的数据库连接池。tomcat 服务器自带dbcp数据库连接池。速度相对c3p0较快，但因自身原因，Hibernate3已不再提供支持
2. **C3P0**：是一个开源组织提供的一个数据库连接池，速度相对较慢，稳定性还可以，Hibernate3推荐使用
3. **Proxool**：是sourceforge下的一个开源项目数据库连接池，有监控连接池状态的功能，稳定性较c3p0差一点
4. **BoneCP**：是一个开源组织提供的数据库连接池，速度快
5. **Druid**：是阿里提供的数据库连接池，据说是集DBCP 、C3P0 、Proxool 优点于一身的数据库连接池，但是速度不确定是否有BoneCP快

DataSource 通常被称为数据源，它包含**连接池**和**连接池管理**两个部分，习惯上也经常把 DataSource 称为连接池

DataSource用来取代DriverManager来获取Connection，获取速度快，同时可以大幅度提高数据库访问速度

#### 注意

数据源和数据库连接不同，数据源无需创建多个，它是产生数据库连接的工厂，因此整个应用只需要一个数据源即可

当数据库访问结束后，程序还是像以前一样关闭数据库连接`conn.close();` ，但`conn.close();`并没有关闭数据库的物理连接，它仅仅把数据库连接释放，归还给了数据库连接池，销毁数据库连接池使用`DataSource`类下的静态方法

```java
public static void destroy(DataSource pooledDataSource)
```

#### C3P0数据库连接池

##### 配置文件

放在`resources`目录下，命名为`c3p0-config.xml`

```xml
<?xml version="1.0" encoding="utf-8" ?>
<c3p0-config>
    <!-- 将此配置命名为hello_c3p0 -->
    <named-config name="hello_c3p0">
        <!-- 提供获取连接的4个基本信息 -->
        <property name="driverClass">com.mysql.jdbc.Driver</property>
<!--        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbc</property>-->
        <property name="jdbcUrl">jdbc:mysql:///jdbc</property> <!-- 若是本地3306端口，则可以省略 -->
        <property name="user">root</property>
        <property name="password">123456</property>
        <!-- 数据库连接池管理基本信息 -->
        <!-- 当连接池连接数不够时，一次性向服务器申请的连接数 -->
        <property name="acquireIncrement">50</property>
        <!-- 连接池中初始化的连接数 -->
        <property name="initialPoolSize">10</property>
        <!-- 连接池中最少的连接数 -->
        <property name="minPoolSize">10</property>
        <!-- 连接池中最多的连接数 -->
        <property name="maxPoolSize">100</property>
        <!-- 连接池中Statement的最多个数 -->
        <property name="maxStatements">50</property>
        <!-- 每个连接可使用的最多Statement数 -->
        <property name="maxStatementsPerConnection">5</property>
    </named-config>
</c3p0-config>
```

##### 获取连接

编写连接获取方法

```java
// 1 默认加载resources目录下的c3p0-config.xml文件，获取hello_c3p0配置
private static ComboPooledDataSource cpds = new ComboPooledDataSource("hello_c3p0");
// 通过c3p0连接池获取数据库连接
public static Connection getConnectionC3P0() {
    Connection conn = null;
    try {
        // 2 通过连接池得到数据库连接
        conn = cpds.getConnection();
    } catch (SQLException e) {
        e.printStackTrace();
    }
    // 3 返回连接
    return conn;
}
```

#### Druid数据库连接池

##### 配置文件

放在`resources`目录下，命名为`druid.properties`

```properties
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql:///jdbc
username=root
password=123456
initialSize=10
maxActive=100
```

##### 获取连接

```java
// 1 声明DataSource对象
private static DataSource source = null;
// 使用静态代码块初始化DataSource对象
static {
    try {
        // 2 获取系统类加载器
        ClassLoader loader = ClassLoader.getSystemClassLoader();
        // 3 获取druid配置文件输入流
        InputStream is = loader.getResourceAsStream("druid.properties");
        // 4 加载druid配置文件
        Properties pros = new Properties();
        pros.load(is);
        // 5 通过工厂类创建数据库连接池并初始化DataSource静态对象
        source = DruidDataSourceFactory.createDataSource(pros);
    } catch (Exception e) {
        e.printStackTrace();
    }
}
public static Connection getConnectionDruid() {
    Connection conn = null;
    try {
        // 6 通过连接池获取数据库连接
        conn = source.getConnection();
    } catch (SQLException e) {
        e.printStackTrace();
    }
    // 7 返回数据库连接
    return conn;
}
```

## Commons Dbutils

### 介绍

commons-dbutils 是 Apache 组织提供的一个开源 JDBC工具类库，它是对JDBC的简单封装，使用dbutils能极大简化jdbc编码的工作量，同时也不会影响程序的性能

### DbUtils

提供如关闭连接、装载JDBC驱动程序等常规工作的工具类，里面的所有方法都是静态的

### QueryRunner

#### 更新

```java
public int update(Connection conn, String sql, Object... params)
```

用来执行一个更新（插入、更新或删除）操作，返回受影响的记录条数

**示例**

```java
Connection conn = null;
try {
    // 1 实例化queryrunner对象
    QueryRunner runner = new QueryRunner();
    // 2 从druid连接池获取数据库连接
    conn = JDBCUtils.getConnectionDruid();
    // 3 编写sql语句，insert、update和delete同理使用
    String sql = "insert into customers(name, email, birth) values(?,?,?)";
    // 4 执行sql语句，返回受影响的记录条目数
    int count = runner.update(conn, sql, "王五", "wang@qq.com", "2019-04-03");
    System.out.println("添加了" + count + "条记录");
} catch (SQLException e) {
    e.printStackTrace();
} finally {
    // 5 关闭资源
    JDBCUtils.closeResources(conn);
}
```

#### 插入

```java
public <T> T insert(Connection conn, String sql, ResultSetHandler<T> rsh, Object... params)
```

只支持insert语句，其中 `rsh`为数据库自增键的值类型，返回值为该自增键的值

**示例**

```java
Connection conn = null;
try {
    // 1 实例化queryrunner对象
    QueryRunner runner = new QueryRunner();
    // 2 从druid连接池获取数据库连接
    conn = JDBCUtils.getConnectionDruid();
    // 3 编写sql语句，只能编写insert语句
    String sql = "insert into customers(name, email, birth) values(?,?,?)";
    // 4 定义自增键的返回值类型
    ScalarHandler<Long> handler = new ScalarHandler<>();
    // 5 执行插入操作，返回插入之后自增键的值
    Long index = runner.insert(conn, sql, handler, "王尼玛", "waddng@qq.com", "2019-08-03");
    System.out.println("此时自增id至" + index);
} catch (SQLException e) {
    e.printStackTrace();
} finally {
    // 6 关闭资源
    JDBCUtils.closeResources(conn);
}
```

#### 批处理

```java
public int[] batch(Connection conn, String sql, Object[][] params)
```

支持插入、更新、删除操作

```java
public <T> T insertBatch(Connection conn, String sql, ResultSetHandler<T> rsh, Object[][] params)
```

只支持插入操作

**示例**

```java
long start = System.currentTimeMillis();
Connection conn = null;
try {
    // 1 从数据库连接池获取数据库连接
    conn = JDBCUtils.getConnectionDruid();
    // 2 编写sql语句，支持insert、update和delete操作
    String sql = "insert into goods(name) values(?)";
    // 3 实例化queryrunner对象
    QueryRunner runner = new QueryRunner();
    // 4 准备插入数据库的值，
    String[][] data = new String[20000][1];
    for (int i = 0; i < 20000; i++) {
        data[i][0] = "name_" + (i + 1);
    }
    // 5 批量插入数据
    runner.batch(conn, sql, data);
} catch (SQLException e) {
    e.printStackTrace();
} finally {
    JDBCUtils.closeResources(conn);
}
long end = System.currentTimeMillis();
System.out.println("end - start: " + (end - start)); // end - start: 1218
```

#### 查询

```java
public <T> T query(Connection conn, String sql, ResultSetHandler<T> rsh, Object... params)
```

执行一个查询操作，在这个查询中，可变数组中的每个元素值被用来作为查询语句的置换参数。该方法会自行处理 PreparedStatement 和 ResultSet 的创建和关闭

##### BeanHandler

返回一条记录，以`Bean`的形式返回

```java
Connection conn = null;
try {
    // 1 获取数据库连接
    conn = JDBCUtils.getConnectionDruid();
    // 2 实例化queryrunner对象
    QueryRunner runner = new QueryRunner();
    // 3 编写查询语句
    String sql = "select id, name, email, birth from customers where id=?";
    // 4 定义返回值的bean对象类型
    BeanHandler<Customer> handler = new BeanHandler<Customer>(Customer.class);
    // 5 执行查询语句返回一个封装了对应信息的bean对象
    // 注意：当属性名与字段名不能一一对应时，需要使用别名
    Customer cus = runner.query(conn, sql, handler, 16);
    System.out.println(cus);
} catch (SQLException e) {
    e.printStackTrace();
} finally {
    // 6 关闭资源
    JDBCUtils.closeResources(conn);
}
```

##### BeanListHandler

返回多条记录，以`List<Bean>`的形式返回

```java
Connection conn = null;
try {
    // 1 获取数据库连接
    conn = JDBCUtils.getConnectionDruid();
    // 2 实例化queryrunner对象
    QueryRunner runner = new QueryRunner();
    // 3 编写查询语句
    String sql = "select id, name, email, birth from customers";
    // 4 定义返回值的bean对象类型，返回的多个bean对象装在list集合中
    BeanListHandler<Customer> handler = new BeanListHandler<>(Customer.class);
    // 5 执行查询语句，返回list对象
    List<Customer> list = runner.query(conn, sql, handler);
    list.forEach(System.out::println);
} catch (SQLException e) {
    e.printStackTrace();
} finally {
    // 6 关闭资源
    JDBCUtils.closeResources(conn);
}
```

##### MapHandler

返回一条记录，以`Map`的形式返回

```java
Connection conn = null;
try {
    conn = JDBCUtils.getConnectionDruid();
    QueryRunner runner = new QueryRunner();
    String sql = "select id, name, email, birth from customers where id=?";
    // 将结果集的数据以map的形式封装
    MapHandler handler = new MapHandler();
    // 返回结果集的map对象
    Map<String, Object> map = runner.query(conn, sql, handler, 16);
    System.out.println(map);
} catch (SQLException e) {
    e.printStackTrace();
} finally {
    JDBCUtils.closeResources(conn);
}
```

##### MapListHandler

返回多条记录，以`List<Map<String,Object>>`的形式返回

```java
Connection conn = null;
try {
    conn = JDBCUtils.getConnectionDruid();
    QueryRunner runner = new QueryRunner();
    String sql = "select id, name, email, birth from customers";
    // 将结果集中的单条数据封装到一个map对象中，再将多个map对象装载到list对象中
    MapListHandler handler = new MapListHandler();
    // 返回结果集的list对象
    List<Map<String, Object>> list = runner.query(conn, sql, handler);
    list.forEach(System.out::println);
} catch (SQLException e) {
    e.printStackTrace();
} finally {
    JDBCUtils.closeResources(conn);
}
```

##### ScalarHandler

用以返回特定的数值，例如函数结果

```java
Connection conn = null;
try {
    conn = JDBCUtils.getConnectionDruid();
    QueryRunner runner = new QueryRunner();
    String sql = "select count(*) from customers";
    // 定义所返回的特定值的类型
    ScalarHandler<Long> handler = new ScalarHandler<>();
    // 执行查询语句返回相应类型的值
    Long count = runner.query(conn, sql, handler);
    System.out.println(count);
} catch (SQLException e) {
    e.printStackTrace();
} finally {
    JDBCUtils.closeResources(conn);
}
```

##### ResultSetHandler

自定义结果集类型

```java
Connection conn = null;
try {
    conn = JDBCUtils.getConnectionDruid();
    QueryRunner runner = new QueryRunner();
    String sql = "select id, name, email, birth from customers where id=?";
    // 创建一个ResultSetHandler的实现类对象，并实现handle方法
    ResultSetHandler<Customer> handler = new ResultSetHandler<Customer>() {
        @Override
        public Customer handle(ResultSet resultSet) throws SQLException {
            Customer cus = null;
            // resultSet为查询返回的结果集，此时可以根据结果集自定义需要返回的数据
            if (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                String email = resultSet.getString("email");
                Date birth = resultSet.getDate("birth");
                cus = new Customer(id, name, email, birth);
            }
            // 此时返回的值即为query方法执行查询语句所返回的值
            return cus;
        }
    };
    // 获得ResultSetHandler的实现方法handle中所返回的值
    Customer cus = runner.query(conn, sql, handler, 16);
    System.out.println(cus);
} catch (SQLException e) {
    e.printStackTrace();
} finally {
    JDBCUtils.closeResources(conn);
}
```

#### 改写BaseDao类的通用方法

利用QueryRunner改写BaseDao中的通用方法，精简语句

```java
public abstract class BaseDao<T> {
    private Class<T> clazz = null;
    {
        // 1 获取当前类的父类的泛型，用于在BaseDao的子类xxxDao中获取到继承父类时父类所指定的泛型类型
        Type t = this.getClass().getGenericSuperclass();
        ParameterizedType pt = (ParameterizedType) t;
        Type[] args = pt.getActualTypeArguments();
        // 2 将父类的泛型类型赋予clazz对象，进行反射操作
        clazz = (Class<T>) args[0]; // 父类只有一个泛型，直接取第一个
    }
    private QueryRunner runner = new QueryRunner();
    // 查询单个特殊值的通用方法
    public <E> E getValue(Connection conn, String sql, Object... args) {
        E value = null;
        try {
            ScalarHandler<E> handler = new ScalarHandler<>();
            value = runner.query(conn, sql, handler, args);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return value;
    }
    // 通用的多个数据查询，支持事务操作
    public List<T> getList(Connection conn, String sql, Object... args){
        List<T> list = null;
        try {
            BeanListHandler<T> handler = new BeanListHandler<T>(clazz);
            list = runner.query(conn, sql, handler, args);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }
    // 通用的单个数据查询，支持事务操作
    public T getInstance(Connection conn, String sql, Object... args) {
        T bean = null;
        try {
            BeanHandler<T> handler = new BeanHandler<>(clazz);
            bean = runner.query(conn, sql, handler, args);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return bean;
    }
    // 通用的增删改操作，支持事务操作
    public int update(Connection conn, String sql, Object... args) {
        int rows = 0;
        try {
            rows = runner.update(conn, sql, args);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return rows;
    }
}
```