JDBC学习实战_yukiyama

本文总结自: 尚硅谷JDBC核心技术视频教程（康师傅带你一站式搞定jdbc）。

JDBC概述

JDBC (Java Database Connectivity): 独立于特定数据库管理系统的通用的数据库存取和操作的公共接口（一组API）。JDBC 定义了用来访问数据库的标准 Java 类库（java.sql, javax.sql），使用这些类库可以以一种标准的方法、方便地访问数据库资源。

JDBC接口包括两个层次：

面向应用的API：Java API，抽象接口，供应用程序开发人员使用（连接数据库，执行SQL语句，获得结果）。
面向数据库的API：Java Driver API，供开发商开发数据库驱动程序用。

使用JDBC的步骤

通过以下步骤使用 JDBC 连接并操作数据库。

添加依赖，例如 mysql-connector-java-8.0.11.jar 。
使用 Driver 或 DriverManage 来获取连接实例 Connection 。
通过连接实例获取 Statement 实例 (使用 PreparedStatement) 。
通过 Statement 实例执行 sql 语句。若是查询则可获取结果集。
关闭连接和资源。

获取数据库连接

必要信息

JDBC 连接数据库需要如下信息。

Driver 接口实现类
- java.sql.Driver 接口是所有 JDBC 驱动程序需要实现的接口，不同的数据库厂商实现类不同。
- 连接时可通过 Driver 实现类的 connect() 方法获取连接实例，但通常采用将 Driver 实现类注册到驱动程序管理器类 java.sql.DriverManager 中，再由 DriverManager 对象的静态方法 getConnection() 来取得连接实例。
URL：用于标识一个被注册的驱动程序，驱动程序管理器通过这个 URL 选择正确的驱动程序，从而建立到数据库的连接。
用户名和密码

不同 DBMS 以及同一 DBMS 的不同版本，所需的信息不同，连接 MySQL 8.0.x 所需信息如下。

连接所需信息	描述
Driver 接口实现类	`com.mysql.cj.jdbc.Driver`
URL	jdbc:mysql://localhost:3306/test?characterEncoding=utf8&useSSL=false
用户名	连接 MySQL 的用户名
密码	连接 MySQL 的用户密码

URL 中各参数意义可参考官方文档 6.3 Configuration Properties 。

使用 DriverManager 来获取连接时，要完成 Driver 对象的加载，并将其注册到 DriverManager 中。

加载驱动：加载 JDBC 驱动需调用 Class 类的静态方法 forName()，向其传递要加载的 JDBC 驱动的类名
- Class.forName(“com.mysql.cj.jdbc.Driver”);
注册驱动：DriverManager 类是驱动程序管理器类，负责管理驱动程序
- 使用 DriverManager.registerDriver(driver) 来注册驱动，driver 是具体驱动对象。
- 通常不必显式调用 DriverManager 类的 registerDriver() 方法来注册驱动程序类的实例，因为 Driver 接口的驱动程序类 都包含了静态代码块，在这个静态代码块中，会调用 DriverManager.registerDriver() 方法来注册自身的实例。例如下面的 MySQL Driver 实现类的源码的静态代码块。
  1
  2
  3
  4
  5
  6
  7
  static {
  try {
  DriverManager.registerDriver(new Driver());
  } catch (SQLException var1) {
  throw new RuntimeException("Can't register driver!");
  }
  }

连接实现举例

方式一

此方式显式调用了第三方数据库的API。

@Test
public void test1(){
    try {
        // 1. 提供 java.sql.Driver 接口实现类的对象
        Driver driver = new com.mysql.cj.jdbc.Driver();
        // 2. 提供 url
        String url = "jdbc:mysql://localhost:3306/test?characterEncoding=utf8&useSSL=false";
        // 3. 提供 properties (用户名密码)
        Properties info = new Properties();
        info.setProperty("user", "root");
        info.setProperty("password", "xxxxxxxx");
        // 4. 调用 driver 的 connect() 方法获取连接实例
        Connection connect = driver.connect(url, info);
        System.out.println("方式一：" + connect);
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }
}

方式二

相较于方式一，这里使用反射实例化 Driver，不在代码中体现第三方数据库的API，体现了面向接口编程思想。

@Test
public void test2(){
    try {
        // 1. 利用反射得到 Driver 实例
        String className = "com.mysql.cj.jdbc.Driver";
        Class clazz = Class.forName(className);
        Driver driver = (Driver) clazz.newInstance();
        // 2. 提供 url
        String url = "jdbc:mysql://localhost:3306/test?characterEncoding=utf8&useSSL=false";
        // 3. 提供 properties (用户名密码)
        Properties info = new Properties();
        info.setProperty("user", "root");
        info.setProperty("password", "xxxxxxxx");
        // 4. 调用 driver 的 connect() 方法获取连接实例
        Connection connect = driver.connect(url, info);
        System.out.println("方式二：" + connect);
    } catch (Exception e) {
        throw new RuntimeException(e);
    }
}

方式三

使用 DriverManager 获取连接。

@Test
public void test3(){
    try {
        // 1. 提供连接数据库需要的信息
        String className = "com.mysql.cj.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/test?characterEncoding=utf8&useSSL=false";
        String user = "root", pw = "xxxxxxxx";
        // 2. 通过反射提供 Driver 实现类对象
        Class clazz = Class.forName(className);
        Driver driver = (Driver) clazz.newInstance();
        // 3. 注册 driver
        DriverManager.registerDriver(driver);
        Connection connect = DriverManager.getConnection(url, user, pw);
        System.out.println("方式三：" + connect);
    } catch (Exception e) {
        throw new RuntimeException(e);
    }
}

方式四

不必显式注册驱动，因为在 DriverManager 的源码中的静态代码块中已实现了驱动的注册。

@Test
public void test4(){
    try {
        // 1. 提供连接数据库需要的信息
        String driverName = "com.mysql.cj.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/test?characterEncoding=utf8&useSSL=false";
        String user = "root", pw = "lxs911227";
        // 2. 通过反射提供 Driver 实现类对象，在加载该对象类的时候，静态代码块完成注册
        Class.forName(driverName);
        // 3. 获取连接
        Connection connection = DriverManager.getConnection(url, user, pw);
        System.out.println("方式四：" + connection);
    } catch (Exception e) {
        throw new RuntimeException(e);
    }
}

方式五(最终版)

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

@Test
public void test5(){
    try {
        //1.加载配置文件
        InputStream is = ConnectionTest.class.getClassLoader().
          getResourceAsStream("jdbc.properties");
        Properties pros = new Properties();
        pros.load(is);
        // 2. 读取配置信息
        String driverName = pros.getProperty("driver");
        String url = pros.getProperty("url");
        String user = pros.getProperty("user"), pw = pros.getProperty("password");
        // 3. 通过反射提供 Driver 实现类对象，在加载该对象类的时候，静态代码块完成注册
        Class.forName(driverName);
        // 4. 获取连接
        Connection connection = DriverManager.getConnection(url, user, pw);
        System.out.println("方式五：" + connection);
    } catch (Exception e) {
        throw new RuntimeException(e);
    }
}

其中，配置文件声明在工程的src目录下：jdbc.properties

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/test?characterEncoding=utf8&useSSL=false
user=root
password=xxxx

使用配置文件的好处：

实现了代码和数据的分离，如果需要修改配置信息，直接在配置文件中修改，不需要修改源码。
由于配置信息不是写在源码中的，配置修改无需重新编译源码。

操作数据库

JDBC 获取数据库连接实例后，由该实例返回 Statement 对象，再由该对象解析 sql 语句，完成增删改查操作。 java.sql 包中有 3 个接口分别定义了对数据库的调用的不同方式。

Statement	描述
Statement	执行静态 SQL 语句并返回它所生成结果的对象。
PrepatedStatement	继承了 Statement ，预编译 SQL 语句并存储在对象中，使用此对象可多次高效地执行 SQL 语句。
CallableStatement	继承了 Statement ，用于执行 SQL 存储过程。

Java与SQL数据类型

如下是 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

ORM

ORM (Object-relational mapping) ，对象关系映射。即将数据库中的表映射成一个类，简单来说，有如下关系。

一个数据表对应一个 java 类。
表中的一条记录对应 java 类的一个对象实例。
表中的一个字段 (列) 对应 java 类的一个字段。

如下是一张表 user_table (表名) 与其对应的 ORM 类 User，后续演示会使用。

user	password	balance
AA	123456	1000
BB	654321	1000
CC	abcd	2000
DD	abcder	3000

package com.yukiyama.jdbc.bean;

public class User {

    private String user;
    private String password;
    private int balance;

    public User() {}

    public User(String user, String password) {
        super();
        this.user = user;
        this.password = password;
    }

    @Override
    public String toString() {
        return "User{" +
                "user='" + user + '\'' +
                ", password='" + password + '\'' +
                ", balance=" + balance +
                '}';
    }

    public String getUser() {
        return user;
    }

    public void setUser(String user) {
        this.user = user;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public int getBalance() {
        return balance;
    }

    public void setBalance(int balance) {
        this.balance = balance;
    }
}

Statement

通过调用 Connection 对象的 createStatement() 方法创建该对象。该对象用于执行静态的 SQL 语句，并且返回执行结果。Statement 接口中定义了下列方法用于执行 SQL 语句。

// 执行增删改操作 INSERT、UPDATE、DELETE
// 返回值表示受影响的行数
int excuteUpdate(String sql)
// 执行查询操作 SELECT，返回结果集
ResultSet executeQuery(String sql)

使用 Statement 操作数据表存在弊端：

问题一：繁琐的拼串操作
问题二：存在SQL注入问题

SQL 注入：在用户输入数据中注入非法的 SQL 语句段或命令，若系统未对用户输入的数据进行检查，则该注入可能导致意料外的结果。

-- 原 SQL
SELECT user, password FROM user_table WHERE user = '' AND password = ''
-- 被恶意注入的 SQL
SELECT user, password FROM user_table WHERE user='a' OR 1 = ' AND password = ' OR '1' = '1')

解决 SQL 注入问题：使用 PreparedStatement 代替从 Statement。

Statement 完成 SQL 操作示例。

package com.yukiyama.jdbc.statement;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.Scanner;

import com.yukiyama.jdbc.bean.User;
import com.yukiyama.jdbc.utils.JDBCUtils;

public class StatementTest {

    // 使用Statement的弊端：需要拼写sql语句，并且存在SQL注入的问题
    public static void main(String[] args) {
        Scanner scanner = new Scanner(System.in);
        System.out.print("please input username: ");
        String user = scanner.next();
        System.out.print("please input password: ");
        String pw = scanner.next();
        String sql = "SELECT user, password FROM user_table WHERE user = '" + user + "' AND password = '" + pw + "'";
        User returnUser = get(sql, User.class);
        if(returnUser != null) {
            System.out.println("Login OK!");
        } else {
            System.out.println("Login Failed!");
        }
    }

    // 使用Statement实现对数据表的查询操作
    public static <T> T get(String sql, Class<T> clazz) {
        T t = null;
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            // 1. 获取连接
            conn = JDBCUtils.getConnection();
            // 2. 获取 statement
            st = conn.createStatement();
            // 3. statement 执行 sql 语句
            rs = st.executeQuery(sql);
            // 4. 获取结果集的元数据
            ResultSetMetaData rsmd = rs.getMetaData();
            // 5. 获取结果集的列数
            int columnCount = rsmd.getColumnCount();
            if (rs.next()) {
                t = clazz.newInstance();
                for (int i = 0; i < columnCount; i++) {
                    // 获取列的名称
                    // String columnName = rsmd.getColumnName(i+1);
                    // 获取列的别名
                    String columnName = rsmd.getColumnLabel(i + 1);
                    // 根据列名获取对应数据表中的数据
                    Object columnVal = rs.getObject(columnName);
                    // 将数据表中得到的数据，封装进对象
                    Field field = clazz.getDeclaredField(columnName);
                    field.setAccessible(true);
                    field.set(t, columnVal);
                }
                return t;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally { // 关闭资源
            JDBCUtils.closeResource(conn, st, rs);
        }
        return null;
    }

}

执行结果

1
2
3

please input username: AA
please input password: 123456
Login OK!

将连接过程和关闭过程抽取为 JDBCUtils 类的静态方法，如下。

package com.yukiyama.utils;

import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JDBCUtils {

    public static Connection getConnection() throws Exception {
        //1.加载配置文件
        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
        Properties pros = new Properties();
        pros.load(is);
        // 2. 读取配置信息
        String driverName = pros.getProperty("driver");
        String url = pros.getProperty("url");
        String user = pros.getProperty("user"), pw = pros.getProperty("password");
        // 3. 通过反射提供 Driver 实现类对象，在加载该对象类的时候，静态代码块完成注册
        Class.forName(driverName);
        // 4. 获取连接
        Connection connection = DriverManager.getConnection(url, user, pw);
        return connection;
    }
    // 关闭statement和连接
    public static void closeResource(Connection connection, Statement ps){
        try {
            if(ps != null) ps.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        try {
            if(connection != null) connection.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    // 关闭连接，statement和结果集
    public static void closeResource(Connection connection, Statement st, ResultSet rs){
        try {
            if(st != null) st.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        try {
            if(connection != null) connection.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        try {
            if(rs != null) rs.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}

PreparedStatement

调用 Connection 对象的 preparedStatement(String sql) 方法获取 PreparedStatement 对象。PreparedStatement 本身是接口，继承了 Statement ，它相比 Statement 有如下几个优点。

预编译 SQL 语句，防止 SQL 注入。
可以通过占位符传入流，实现对大文件的操作。
预编译 SQL 语句，实现高效的批量操作。

占位符：PreparedStatement 对象所代表的 SQL 语句中的参数用问号 ? 来表示，调用其实例方法 setXxx() 。setXxx() 方法有两个参数，第一个参数是要设置的 SQL 语句中的参数的索引 (从 1 开始)，第二个是设置的 SQL 语句中的参数的值。

预编译：DBServer 会对预编译语句提供性能优化。因为预编译语句有可能被重复调用，所以语句在被 DBServer 的编译器编译后的执行代码被缓存下来，那么下次调用时只要是相同的预编译语句就不需要编译，只要将参数直接传入编译过的语句执行代码中就会得到执行。而使用 Statement 时，即使是相同操作但因为数据内容不一样，整个语句本身不能匹配，没有缓存语句的意义。事实是没有数据库会对普通语句编译后的执行代码缓存。这样每执行一次都要对传入的语句编译一次。

后续代码实例将展示：

从非通用的「增删改」改进为通用的「增删改」。
对同一张表通用的「查」改进为对任意表通用的「查」。

增删改操作

非通用「增」

@Test
public void testInsert() {
    Connection conn = null;
    PreparedStatement ps = null;
    try {
        // 1 获取数据库连接
        conn = JDBCUtils.getConnection();
        // 2 预编译 sql 语句，获取 ps 实例
        String sql = "insert into customers(name, email, birth) values(?,?,?)";
        ps = conn.prepareStatement(sql);
        // 3 填充占位符
        ps.setObject(1, "yukiyama");
        ps.setObject(2, "yukiyama@gmail.com");
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        java.util.Date date = sdf.parse("1111-11-11");
        ps.setObject(3, new Date(date.getTime()));
        // 4 执行
        ps.execute();
    } catch (Exception e) {
        throw new RuntimeException(e);
    } finally {
        // 5 关闭资源
        JDBCUtils.closeResource(conn, ps);
    }
}

非通用「改」

@Test
public void testUpdate() {
    Connection conn = null;
    PreparedStatement ps = null;
    try {
        // 1 获取数据库连接
        conn = JDBCUtils.getConnection();
        // 2 预编译 sql 语句，获取 ps 实例
        String sql = "update customers set name = ? where id = ?";
        ps = conn.prepareStatement(sql);
        // 3 填充占位符
        ps.setObject(1, "莫扎特");
        ps.setObject(2, 18);
        // 4 执行
        ps.execute();
    } catch (Exception e) {
        throw new RuntimeException(e);
    } finally {
        // 5 关闭资源
        JDBCUtils.closeResource(conn, ps);
    }
}

上述两例的非通用性体现在于 SQL 语句在方法之内，不同的操作需对应不同的 SQL。

通用增删改

@Test
public void testCommonUpdate(){
    String sql = "delete from customers where id = ?";
    update(sql, 21);
}

public void update(String sql, Object ... args){
    Connection conn = null;
    PreparedStatement ps = null;
    try {
        conn = JDBCUtils.getConnection();
        ps = conn.prepareStatement(sql);
        for (int i = 0; i < args.length; i++) {
            ps.setObject(i + 1, args[i]);
        }
        ps.execute();
    } catch (Exception e) {
        throw new RuntimeException(e);
    } finally {
        JDBCUtils.closeResource(conn, ps);
    }
}

查询操作

查询操作与增删改操作的不同之处在于前者有查询结果而后者没有，因此查询操作将返回「结果集」，即 ResultSet 类的对象。

单表通用「查」

如下是对同一张表通用的查询操作。

@Test
public void test(){
    String sql = "select id, name, birth, email from customers where id = ?";
    Customer customer = queryForCustomerTest(sql, 18);
    System.out.println(customer);
}
public Customer queryForCustomerTest(String sql, Object...args){
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
        conn = JDBCUtils.getConnection();
        ps = conn.prepareStatement(sql);
        for (int i = 0; i < args.length; i++) {
            ps.setObject(i + 1, args[i]);
        }
        rs = ps.executeQuery();
        if(rs.next()){
            int id = rs.getInt(1);
            String name = rs.getString(2);
            Date birth = rs.getDate(3);
            String email = rs.getString(4);
            Customer customer = new Customer(id, name, email, birth);
            return customer;
        }
    } catch (Exception e) {
        throw new RuntimeException(e);
    } finally {
        JDBCUtils.closeResource(conn, ps, rs);
    }
    return null;
}

该上述代码实例中，在 if(rs.next()) 中将获取的结果集 (一条) 组装为 ORM 对象并返回。该方式需要事先知道表的列数并依次获取各字段。更好的办法是通过「结果集元数据」ResultMetaData 获取列数信息，再通过反射确定结果集对应的 ORM 对象各字段的值，得到最终的对象。

利用反射

对同一张表通用的查询 –– 利用反射返回行数据的 ORM 对象。

@Test
public void test(){
    String sql = "select id, name, birth, email from customers where id = ?";
    Customer customer = queryForCustomer(sql, 18);
    System.out.println(customer);
}
public Customer queryForCustomer(String sql, Object...args){
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
        conn = JDBCUtils.getConnection();
        ps = conn.prepareStatement(sql);
        for (int i = 0; i < args.length; i++) {
            ps.setObject(i + 1, args[i]);
        }
        rs = ps.executeQuery();
        if(rs.next()){
            ResultSetMetaData rsmd = rs.getMetaData();
            Customer customer = new Customer();
            int colCount = rsmd.getColumnCount();
            for (int i = 0; i < colCount; i++) {
                Object colValue = rs.getObject(i + 1);
                String colName = rsmd.getColumnName(i + 1);
                Field field = Customer.class.getDeclaredField(colName);
                field.setAccessible(true);
                field.set(customer, colValue);
            }
            return customer;
        }
    } catch (Exception e) {
        throw new RuntimeException(e);
    } finally {
        JDBCUtils.closeResource(conn, ps, rs);
    }
    return null;
}

上述代码需要根据列名反射得到行数据，采用结果集元数据 ResultSetMetaData 的实例方法 getColumnName() 时，得到的是真实的列名，而 ORM 对象中的字段名不一定与数据库中的列名相同。为解决这个问题，可在 SQL 语句中列名之后写上别名，该别名与 ORM 对象中的字段名相同，并采用 getColumnLabel() 方法取代 getColumnName() 来获取列的别名，即可反射到 ORM 对象中的字段。

上述代码的 ORM 对象 Customer 如下。

package com.yukiyama.jdbc.bean;

import java.sql.Date;

public class Customer {
    private int id;
    private String name;
    private String email;
    private Date birth;

    public Customer() {
        super();
    }

    public Customer(int id, String name, String email, Date birth) {
        super();
        this.id = id;
        this.name = name;
        this.email = email;
        this.birth = birth;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public Date getBirth() {
        return birth;
    }

    public void setBirth(Date birth) {
        this.birth = birth;
    }

    @Override
    public String toString() {
        return "Customer [id=" + id + ", name=" + name + ", email=" + email + ", birth=" + birth + "]";
    }

}

使用列的别名

对同一张表通用的查询 –– 使用列的别名而非列名。

package com.yukiyama.jdbc.preparedstatement;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;

import com.yukiyama.jdbc.bean.Order;
import com.yukiyama.jdbc.utils.JDBCUtils;
import org.junit.Test;

public class OrderForQuery {

    @Test
    public void testOrderForQuery() {
        String sql = "select order_id orderId,order_name orderName,order_date orderDate from `order` where order_id = ?";
        Order order = orderForQuery(sql, 1);
        System.out.println(order);
    }

    public Order orderForQuery(String sql, Object...args) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getConnection();
            ps = conn.prepareStatement(sql);
            for(int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            rs = ps.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            int columnCount = rsmd.getColumnCount();
            if(rs.next()) {
                Order order = new Order();
                for(int i = 0; i < columnCount; i++) {
                    Object columnValue = rs.getObject(i + 1);
//					String columnName = rsmd.getColumnName(i + 1); // 不推荐使用列名
                    String columnLabel = rsmd.getColumnLabel(i + 1); // 推荐使用别名
                    Field field = Order.class.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(order, columnValue);
                }
                return order;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, ps, rs);
        }

        return null;
    }

    @Test
    public void testQuery1()  {

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getConnection();
            String sql = "select order_id, order_name, order_date from `order` where order_id = ?";
            ps = conn.prepareStatement(sql);
            ps.setObject(1, 1);
            rs = ps.executeQuery();
            if(rs.next()) {
                int id = (int) rs.getObject(1);
                String name = (String) rs.getObject(2);
                Date date = (Date) rs.getObject(3);

                Order order = new Order(id, name, date);
                System.out.println(order);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, ps, rs);
        }
    }
}

上述代码的 ORM 对象 Order 如下。

package com.yukiyama.jdbc.bean;

import java.sql.Date;

public class Order {

    private int orderId;
    private String orderName;
    private Date orderDate;

    public Order() {
        super();
    }

    public Order(int orderId, String orderName, Date orderDate) {
        super();
        this.orderId = orderId;
        this.orderName = orderName;
        this.orderDate = orderDate;
    }

    public int getOrderId() {
        return orderId;
    }

    public void setOrderId(int orderId) {
        this.orderId = orderId;
    }

    public String getOrderName() {
        return orderName;
    }

    public void setOrderName(String orderName) {
        this.orderName = orderName;
    }

    public Date getOrderDate() {
        return orderDate;
    }

    public void setOrderDate(Date orderDate) {
        this.orderDate = orderDate;
    }

    @Override
    public String toString() {
        return "Order [orderId=" + orderId + ", orderName=" + orderName + ", orderDate=" + orderDate + "]";
    }

}

多表通用「查」

在「单表通用查」中，返回值类型对应一张具体的表，可以使用泛型将其改造为对不同表通用的查找方法。只需在方法中传入具体的类的 class 实例，并返回泛型即可。

泛型方法的定义参考。

如下代码给出返回一条 (一个 ORM 对象实例) 和返回多条结果 (多个 ORM 对象实例) 的方法。

package com.yukiyama.jdbc.preparedstatement;

import com.yukiyama.jdbc.bean.Customer;
import com.yukiyama.jdbc.bean.Order;
import com.yukiyama.jdbc.utils.JDBCUtils;
import org.junit.Test;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.List;

public class PreparedStatementQueryTest {

    @Test
    public void test() {
        // 查询单行
        String sql1 = "select order_id orderId, order_name orderName from `order` where order_id = ?";
        Order order = getResult(Order.class, sql1, 1);
        System.out.println(order);

        // 查询多行
        String sql2 = "select id, name, email from customers where id < ?";
        List<Customer> list = getResultList(Customer.class, sql2, 12);
        list.forEach(System.out::println);
    }
    // 针对不同表的通用查询操作，返回表中的多条记录
    public <T> List<T> getResultList(Class<T> clazz, String sql, Object...args) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getConnection();
            ps = conn.prepareStatement(sql);
            for(int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            rs = ps.executeQuery();
            // 获取结果集中的元数据
            ResultSetMetaData rsmd = rs.getMetaData();
            int colCount = rsmd.getColumnCount();
            // 创建集合对象
            List<T> list = new ArrayList<>();
            while(rs.next()) {
                T t = clazz.newInstance();
                for(int i = 0; i < colCount; i++) {
                    Object value = rs.getObject(i + 1);
                    // 获取列名
                    // String colName = rsmd.getColumnName(i + 1);
                    String colName = rsmd.getColumnLabel(i + 1);
                    // 给 t 对象指定的某个属性赋值
                    Field field = clazz.getDeclaredField(colName);
                    field.setAccessible(true);
                    field.set(t, value);
                }
                list.add(t);
            }

            return list;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, ps, rs);
        }

        return null;
    }
    // 针对不同表的通用查询操作，返回表中的一条记录
    public <T> T getResult(Class<T> clazz, String sql, Object...args) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getConnection();
            ps = conn.prepareStatement(sql);
            for(int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            rs = ps.executeQuery();
            // 获取结果集中的元数据
            ResultSetMetaData rsmd = rs.getMetaData();
            int colCount = rsmd.getColumnCount();
            if(rs.next()) {
                T t = clazz.newInstance();
                for(int i = 0; i < colCount; i++) {
                    Object value = rs.getObject(i + 1);
                    String colName = rsmd.getColumnLabel(i + 1);
                    // 给 t 对象指定的某个属性赋值
                    Field field = clazz.getDeclaredField(colName);
                    field.setAccessible(true);
                    field.set(t, value);
                }
                return t;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, ps, rs);
        }

        return null;
    }

}

主要类及方法

要点	描述
prepareStatement()	`Connection` 的实例方法，传入 String 类型的 SQL语句，得到预编译的 `PreparedStatement` 对象。
executeQuery()	`PreparedStatement` 的实例方法，执行查询，返回结果集 `ResultSet` 。
ResultSet	结果集类，持有查询结果。
next()	`ResultSet` 实例方法，返回值为 boolean 类型判断结果集当前行是否存在。每次调用，若存在，则当前行移动到下一行。
getXxx()	`ResultSet` 实例方法，当前行存在时，`getXxx(int index)` 或 `getXxx(String columnName)` 获取列的值。`index` 表示第 `index` 列，从 1 开始。
getMetaData()	`ResultSet` 的实例方法，返回结果集元数据类对象 `ResultSetMetaData` 。
ResultSetMetaData	结果集元数据类，持有结果集的元数据，如列数等。
getColumnCount()	`ResultSetMetaData` 实例方法，返回列数。
getColumnName()	`ResultSetMetaData` 实例方法，返回指定列的 `String` 类型列名。
getColumnLabel()	`ResultSetMetaData` 实例方法，返回指定列的 `String` 类型列的别名。

小结

两种思想

面向接口编程的思想
ORM思想

两种技术

JDBC结果集的元数据：ResultSetMetaData
- 获取列数：getColumnCount()
- 获取列的别名：getColumnLabel()
通过反射，创建指定类的对象，获取指定的属性并赋值

操作BLOB

BLOB (Binary Large OBject)，二进制大型对象，用来存储存储照片视频等数据量较大的文件对象。

插入BLOB 类型的数据必须使用 PreparedStatement ，因为 BLOB 类型的数据无法使用字符串拼接。
MySQL 提供四种 BLOB 类型，仅表现为支持的数据大小上限不同。

BLOB 四种类型及大小限制。

BLOB类型	大小上限
TinyBlob	255B
Blob	65KB
MediumBlob	16MB
LongBlob	4GB

如下代码演示如何插入和查询 BLOB 文件。

package com.yukiyama.jdbc.blob;

import com.yukiyama.jdbc.bean.Customer;
import com.yukiyama.jdbc.utils.JDBCUtils;
import org.junit.Test;

import java.io.*;
import java.sql.*;

public class BlobTest {

    // 插入 blog
    @Test
    public void testInsert() throws Exception {
        Connection conn = JDBCUtils.getConnection();
        String sql = "insert into customers (name, email, birth, photo) values (?, ?, ?, ?)";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setObject(1, "猫猫");
        ps.setObject(2, "cat@gmail.com");
        ps.setObject(3, "2022-01-01");
        FileInputStream is = new FileInputStream(new File("src/img.png"));
        ps.setObject(4, is); // 也可以用 setBlob 方法
        ps.execute();
        JDBCUtils.closeResource(conn, ps);
    }

    // 查询 blob
    @Test
    public void testQuery() {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        InputStream is = null;
        FileOutputStream fos = null;
        try {
            conn = JDBCUtils.getConnection();
            String sql = "select id, name, email, birth, photo from customers where id = ?";
            ps = conn.prepareStatement(sql);
            ps.setObject(1, 22);
            rs = ps.executeQuery();

            if(rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String email = rs.getString("email");
                Date birth = rs.getDate("birth");

                Customer customer = new Customer(id, name, email, birth);
                System.out.println(customer);
                // 以文件方式保存 blob 对象
                Blob photo = rs.getBlob("photo");
                is = photo.getBinaryStream();
                fos = new FileOutputStream("output.png");
                byte[] buffer = new byte[1024];
                int len = 0;
                while((len = is.read(buffer)) != -1) {
                    fos.write(buffer, 0, len);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if(is != null) is.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
            try {
                if(fos != null) fos.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
            JDBCUtils.closeResource(conn, ps, rs);
        }

    }
}

批量插入

Statement

Statement 无预编译功能，每一次插入的 SQL 都作为一条新的 SQL 语句来处理，因此不使用 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);
}

PreparedStatement

基本方式

使用 PreparedStatement 预编译 SQL 语句后，简单地循环插入，每一次插入后执行 execute() 方法。

@Test
public void testInsert1() {
    Connection conn = null;
    PreparedStatement ps = null;
    try {
        long start = System.currentTimeMillis();
        conn = JDBCUtils.getConnection();
        String sql = "insert into goods (name) values (?)";
        ps = conn.prepareStatement(sql);
        for(int i = 1; i <= 20000; i++) {
            ps.setObject(1, "name_" + i);
            ps.execute();
        }
        long end = System.currentTimeMillis();
        System.out.println("time cost: " + (end - start));
    } catch (Exception e){
        e.printStackTrace();
    } finally {
        JDBCUtils.closeResource(conn, ps);
    }
}

批量方法

预编译 SQL 语句后，每次填充占位符后不直接执行 execute() 方法，而是执行 addBatch() 方法累计 SQL 语句，之后可以设置一个较大的次数间隔，每隔一定次数后再执行 executeBatch() 方法批量执行，批量执行后及时执行 clearBatch() 方法清空 batch 。

MySQL 服务器默认关闭批处理，可在 URL 中传入 ?rewriteBatchedStatements=true 参数开启批处理功能，如下。

1	jdbc:mysql://localhost:3306/test?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true

@Test
public void testInsert2() {
    Connection conn = null;
    PreparedStatement ps = null;
    try {
        long start = System.currentTimeMillis();
        conn = JDBCUtils.getConnection();
        String sql = "insert into goods (name) values (?)";
        ps = conn.prepareStatement(sql);
        for(int i = 1; i <= 1000000; i++) {
            ps.setObject(1, "name_" + i);
            // 1. 累计 sql
            ps.addBatch();
            if(i % 500 == 0) {
                // 2. 每500次执行一次
                ps.executeBatch();
                // 3. 清空 batch
                ps.clearBatch();
            }
        }
        long end = System.currentTimeMillis();
        System.out.println("time cost: " + (end - start));
    } catch (Exception e){
        e.printStackTrace();
    } finally {
        JDBCUtils.closeResource(conn, ps);
    }
}

非自动提交

通过 conn.setAutoCommit(false); 设置为非自动提交，在循环结束后再通过 conn.commit(); 统一提交。

@Test
public void testInsert3() {
    Connection conn = null;
    PreparedStatement ps = null;
    try {
        long start = System.currentTimeMillis();
        conn = JDBCUtils.getConnection();
        // 设置不自动提交
        conn.setAutoCommit(false);
        String sql = "insert into goods (name) values (?)";
        ps = conn.prepareStatement(sql);
        for(int i = 1; i <= 1000000; i++) {
            ps.setObject(1, "name_" + i);
            // 1. 累计 sql
            ps.addBatch();
            if(i % 500 == 0) {
                // 2. 执行
                ps.executeBatch();
                // 3. 清空 batch
                ps.clearBatch();
            }
        }
        // 提交数据
        conn.commit();
        long end = System.currentTimeMillis();
        System.out.println("time cost: " + (end - start));
    } catch (Exception e){
        e.printStackTrace();
    } finally {
        JDBCUtils.closeResource(conn, ps);
    }
}

上述三种方式耗时比较。

方式	插入数据量	耗时 (ms)
基本方式	20000	54805
批量方法	20000	1629
批量方法	1000000	38669
非自动提交	1000000	40908 (似乎不起作用，原因排查中)

事务

事务及事务处理

事务: 一组逻辑操作，具体指一组 SQL 语句。

事务处理: 处理一个事务时保证该事务所包含的 SQL 语句要么完全执行，要么完全不执行。完全执行时，称该事务被 提交 (commit) ，执行过程中出错时，要 回滚 (rollback) 到最初状态。

MySQL 中数据自动提交的情形。

情形	描述
DDL 操作	`set autocommit = false` 对 DDL 操作不起作用。
DML 操作	`set autocommit = false` 关闭 DML 操作的自动提交。
关闭连接时	关闭连接时会自动提交数据。

SQL 语句一旦提交就不可回滚。通过如下方式实现事务处理。

调用 Connection 对象的 setAutoCommit(false); 以取消自动提交事务。
在所有的 SQL 语句都成功执行后，调用 commit(); 方法提交事务。
在出现异常时，调用 rollback(); 方法回滚事务。
若此时 Connection 没有被关闭，还可能被重复使用，则需要恢复其自动提交状态 setAutoCommit(true)。尤其是在使用数据库连接池技术时，执行 close() 方法前，建议恢复自动提交状态。

如下代码演示「转账操作」，展示如何将非事务处理改为事务处理。

非事务处理方式

// 不考虑事务时，若中断，则数据不一致
@Test
public void testUpdate(){
    CommonDML dml = new CommonDML();
    String sql1 = "update user_table set balance = balance - 100 where user = ?";
    dml.update(sql1, "AA");
    // System.out.println(10 / 0); // 模拟异常
    String sql2 = "update user_table set balance = balance + 100 where user = ?";
    dml.update(sql2, "BB");
}

update() 方法

// 通用增删改操作
public int update(String sql, Object ... args){
    Connection conn = null;
    PreparedStatement ps = null;
    try {
        conn = JDBCUtils.getConnection();
        ps = conn.prepareStatement(sql);
        for (int i = 0; i < args.length; i++) {
            ps.setObject(i + 1, args[i]);
        }
        return ps.executeUpdate();
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        JDBCUtils.closeResource(conn, ps);
    }
    return 0;
}

事务处理方式

@Test
public void testUpdateTX(){
    Connection conn = null;
    try {
        conn = JDBCUtils.getConnection();
        // 关闭自动提交
        conn.setAutoCommit(false); // 可通过 conn.getAutoCommit() 查看当前设置
        CommonDML dml = new CommonDML();
        String sql1 = "update user_table set balance = balance - 100 where user = ?";
        dml.updateTX(conn, sql1, "AA");
        // System.out.println(10 / 0); // 模拟异常
        String sql2 = "update user_table set balance = balance + 100 where user = ?";
        dml.updateTX(conn, sql2, "BB");
        // 最后提交数据
        conn.commit();
    } catch (Exception e) {
        e.printStackTrace();
        try { // 回滚
            conn.rollback();
        } catch (SQLException ex) {
            e.printStackTrace();
        }
    } finally {
        try { // 恢复自动提交默认设置
            conn.setAutoCommit(true);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        JDBCUtils.closeResource(conn, null);
    }
}

updateTX() 方法

// 考虑事务的通用增删改操作
public int updateTX(Connection conn, String sql, Object ... args){
    PreparedStatement ps = null;
    try {
        ps = conn.prepareStatement(sql);
        for (int i = 0; i < args.length; i++) {
            ps.setObject(i + 1, args[i]);
        }
        return ps.executeUpdate();
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        JDBCUtils.closeResource(null, ps);
    }
    return 0;
}

ACID

事务具有以下性质。

ACID	描述
原子性 (Atomicity)	将单个事务中的所有 SQL 作为原子工作单元执行，要么全部执行，要么全部不执行。
一致性 (Consistency)	事务完成后，所有数据的状态都是一致的。例如 A 账户转账 100 元到 B 账户上，则 A 账户减少 100 元， B 账户必然多出 100元。
隔离性 (Isolation)	指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
持久性 (Durability)	事务完成后，对数据库数据的修改被持久化存储。

并发问题及隔离级别

并发问题: 多个并发事务访问数据库中相同的数据时, 可能导致如下问题 (现象)。

并发问题	描述
脏读 (Dirty Read)	有事务 T1、T2。首先，T1 更新 `A` 为 `A'`，在提交前 T2 读取到 `A'`，接着 T1 回滚，数据库中的 `A` 未改变，因此 T2 读取的 `A'` 是错误的。
不可重复读 (Non Repeatable Read)	有事务 T1、T2。首先，T2 读取到数据 `A` ，接着 T1 更新 `A` 为 `A'` 并提交，数据库中的 `A` 更新为 `A'`。T2 还在执行中，此时再次读取，得到 `A'` 。同一个事务 T2 两次读取的数据不一致，即为不可重复读。
幻读 (Phantom Read)	例子1: 有事务 T1、T2。首先，T2 读取到数据 `A` ，接着 T1 插入若干行数据并提交。T2 还在执行中，此时再次读取，由于新数据的插入，得到的结果相比 `A` 要多。例子2: 有事务 T1、T2。首先，T2 更新数据 `B` ，`B` 不存在，无法更新。接着 T1 插入数据`B` 。T2 还在执行中，此时再次更新 `B`，可以更新成功，读取 `B` 也能成功。

隔离级别: 产生上述并发问题的原因在于未保证事务的隔离性，即事务未完成时被其他事务介入。如下四种不同级别的隔离能力，隔离级别越高，数据一致性就越好，并发性越弱。

Oracle 支持：READ COMMITED (默认)，SERIALIZABLE。
Mysql 支持：4 种事务隔离级别，默认为 REPEATABLE READ。

下表是四种隔离级别及对应的并发问题 (Y表示存在该问题，N表示不存在该问题)。

隔离级别	脏读	不可重复读	幻读
Read Uncommitted (读未提交)	Y	Y	Y
Read Committed (读已提交)	N	Y	Y
Repeatable Read (可重复读)	N	N	Y
Serializable (串行化)	N	N	N

隔离级别效果演示

设置

为演示不同隔离级别的效果，需要一些设置。

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

查看当前事务隔离级别

1	select @@transaction_isolation;

创建新用户

1 2	-- 请把 "xxxxxx" 换成实际使用的密码 create user yukiyama identified by 'xxxxxx';

通过 root 用户给新用户赋予权限

1	grant all on test.* to yukiyama@localhost;

查看用户权限

1	show grants for yukiyama@localhost;

设置当前 mySQL 连接的隔离级别:

1	set transaction isolation level read uncommitted;

设置数据库系统的全局的隔离级别:

1 2	-- 重连后再查看 set global transaction isolation level read uncommitted;

取消自动提交

1 2	-- 当前连接内有效 set autocommit = false;

手动提交

commit;

演示

演示 read uncommitted

设置全局隔离级别为 read uncommitted，退出重连后再查看。
在两个连接下，关闭自动提交。
两个连接开始查询同一数据，是一致的，在提交前为两个事务 T1和T2。
T1修改该数据，此时T2再次查看同一数据，看到 修改后 的数据。
T1回滚数据，此时T2再次查看同一数据，看到修改前的数据。

现象出现在第4步，T2查到了T1修改，但未提交的临时数据 (脏数据)，即「脏读」。

演示 read committed

设置全局隔离级别为 read committed，退出重连后再查看。
在两个连接下，关闭自动提交。
两个连接开始查询同一数据，是一致的，在提交前为两个事务 T1和T2。
T1修改该数据，此时T2再次查看同一数据，看到 修改前 的数据。
T1提交数据，此时T2再次查看同一数据，看到 修改后 的数据。

相比 read uncommitted ，在第4步中，T2不会查到「脏数据」。但在第5步中，由于 T1 提交了数据，T2再次查询得到的是T1修改后的数据，同一个事务中两次查询结果不同，即所谓「不可重复读」现象。

演示 repeatable read

设置全局隔离级别为 repeatable read，退出重连后再查看。
在两个连接下，关闭自动提交。
两个连接开始查询同一数据，是一致的，在提交前为两个事务 T1和T2。
T1修改该数据，此时T2再次查看同一数据，看到 修改前 的数据。
T1提交数据，此时T2再次查看同一数据，看到 修改前 的数据。

相比 read committed，第4, 5 步查到的都是修改前的数据，即保证了在同一事务中相同的查询总能查询到相同的结果。

演示 serializable

TODO

DAO

DAO (Data Access Object): 访问数据信息的类和接口，包括对数据的 CRUD (Create、Retrival、Update、Delete) 操作。

BaseDAO

用于表的 DML 操作的 DAO 类，后续操作具体表的 DAO 类继承该类。

方法	描述
getValue	泛型方法，获取单个值
update	增删改通用方法，传入连接
getInstanceList	泛型方法，以 `List<T>` 返回多行记录对应的对象列表
getInstance	泛型方法，返回一行记录对应的对象

package com.yukiyama.jdbc.dao;

import com.yukiyama.jdbc.utils.JDBCUtils;

import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class BaseDAO {
    // 获取单个值，例如 count(*)
    public <T> T getValue(Connection conn, String sql, Object...args) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            rs = ps.executeQuery();
            if(rs.next()){
                return (T) rs.getObject(1);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.closeResource(null, ps, rs);
        }
        return null;
    }
    // 考虑事务的通用增删改操作
    public int update(Connection conn, String sql, Object ... args){
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            return ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(null, ps);
        }
        return 0;
    }
    // 通用增删改操作
    public int updateBasic(String sql, Object ... args){
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JDBCUtils.getConnection();
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            return ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, ps);
        }
        return 0;
    }
    // 考虑事务的针对不同表的通用查询操作，返回表中的多条记录
    public <T> List<T> getInstanceList(Connection conn, Class<T> clazz, String sql, Object...args) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement(sql);
            for(int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            rs = ps.executeQuery();
            // 获取结果集中的元数据
            ResultSetMetaData rsmd = rs.getMetaData();
            int colCount = rsmd.getColumnCount();
            // 创建集合对象
            List<T> list = new ArrayList<>();
            while(rs.next()) {
                T t = clazz.newInstance();
                for(int i = 0; i < colCount; i++) {
                    Object value = rs.getObject(i + 1);
                    // 获取列名
                    // String colName = rsmd.getColumnName(i + 1);
                    String colName = rsmd.getColumnLabel(i + 1);
                    // 给 t 对象指定的某个属性赋值
                    Field field = clazz.getDeclaredField(colName);
                    field.setAccessible(true);
                    field.set(t, value);
                }
                list.add(t);
            }

            return list;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(null, ps, rs);
        }

        return null;
    }
    // 针对不同表的通用查询操作，返回表中的多条记录
    public <T> List<T> getInstanceListBasic(Class<T> clazz, String sql, Object...args) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getConnection();
            ps = conn.prepareStatement(sql);
            for(int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            rs = ps.executeQuery();
            // 获取结果集中的元数据
            ResultSetMetaData rsmd = rs.getMetaData();
            int colCount = rsmd.getColumnCount();
            // 创建集合对象
            List<T> list = new ArrayList<>();
            while(rs.next()) {
                T t = clazz.newInstance();
                for(int i = 0; i < colCount; i++) {
                    Object value = rs.getObject(i + 1);
                    // 获取列名
                    // String colName = rsmd.getColumnName(i + 1);
                    String colName = rsmd.getColumnLabel(i + 1);
                    // 给 t 对象指定的某个属性赋值
                    Field field = clazz.getDeclaredField(colName);
                    field.setAccessible(true);
                    field.set(t, value);
                }
                list.add(t);
            }

            return list;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, ps, rs);
        }

        return null;
    }
    // 考虑事务的针对不同表的通用查询操作，返回表中的一条记录
    public <T> T getInstance(Connection conn, Class<T> clazz, String sql, Object...args) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement(sql);
            for(int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            rs = ps.executeQuery();
            // 获取结果集中的元数据
            ResultSetMetaData rsmd = rs.getMetaData();
            int colCount = rsmd.getColumnCount();
            if(rs.next()) {
                T t = clazz.newInstance();
                for(int i = 0; i < colCount; i++) {
                    Object value = rs.getObject(i + 1);
                    String colName = rsmd.getColumnLabel(i + 1);
                    // 给 t 对象指定的某个属性赋值
                    Field field = clazz.getDeclaredField(colName);
                    field.setAccessible(true);
                    field.set(t, value);
                }
                return t;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(null, ps, rs);
        }

        return null;
    }
    // 针对不同表的通用查询操作，返回表中的一条记录
    public <T> T getInstanceBasic(Class<T> clazz, String sql, Object...args) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getConnection();
            ps = conn.prepareStatement(sql);
            for(int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            rs = ps.executeQuery();
            // 获取结果集中的元数据
            ResultSetMetaData rsmd = rs.getMetaData();
            int colCount = rsmd.getColumnCount();
            if(rs.next()) {
                T t = clazz.newInstance();
                for(int i = 0; i < colCount; i++) {
                    Object value = rs.getObject(i + 1);
                    String colName = rsmd.getColumnLabel(i + 1);
                    // 给 t 对象指定的某个属性赋值
                    Field field = clazz.getDeclaredField(colName);
                    field.setAccessible(true);
                    field.set(t, value);
                }
                return t;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, ps, rs);
        }

        return null;
    }
}

改进BaseDAO

前面实现的 BaseDAO 的 getInstance 和 getInstanceList 方法中，传入了 Class<T> clazz，但在使用继承 BaseDAO 的具体表的 DAO 类 (如 CustomerDAO) 时，泛型 T 是确定的，因此可不必传入，可以在代码块中利用反射获取泛型类，如下。

package com.yukiyama.jdbc.dao2;

import com.yukiyama.jdbc.utils.JDBCUtils;

import java.lang.reflect.Field;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class BaseDAO<T> {
    private Class<T> clazz = null;
    {
        // 获取当前BaseDAO的子类继承的父类的泛型
        Type genericSuperclass = this.getClass().getGenericSuperclass();
        ParameterizedType paramType = (ParameterizedType) genericSuperclass;
        Type[] typeArguments = paramType.getActualTypeArguments();
        clazz = (Class<T>) typeArguments[0];
    }
    // 获取单个值，例如 count(*)
    public <T> T getValue(Connection conn, String sql, Object...args) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            rs = ps.executeQuery();
            if(rs.next()){
                return (T) rs.getObject(1);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.closeResource(null, ps, rs);
        }
        return null;
    }
    // 考虑事务的通用增删改操作
    public int update(Connection conn, String sql, Object ... args){
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            return ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(null, ps);
        }
        return 0;
    }
    // 通用增删改操作
    public int updateBasic(String sql, Object ... args){
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JDBCUtils.getConnection();
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            return ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, ps);
        }
        return 0;
    }
    // 考虑事务的针对不同表的通用查询操作，返回表中的多条记录
    public List<T> getInstanceList(Connection conn, String sql, Object...args) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement(sql);
            for(int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            rs = ps.executeQuery();
            // 获取结果集中的元数据
            ResultSetMetaData rsmd = rs.getMetaData();
            int colCount = rsmd.getColumnCount();
            // 创建集合对象
            List<T> list = new ArrayList<>();
            while(rs.next()) {
                T t = clazz.newInstance();
                for(int i = 0; i < colCount; i++) {
                    Object value = rs.getObject(i + 1);
                    // 获取列名
                    // String colName = rsmd.getColumnName(i + 1);
                    String colName = rsmd.getColumnLabel(i + 1);
                    // 给 t 对象指定的某个属性赋值
                    Field field = clazz.getDeclaredField(colName);
                    field.setAccessible(true);
                    field.set(t, value);
                }
                list.add(t);
            }

            return list;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(null, ps, rs);
        }

        return null;
    }
    // 针对不同表的通用查询操作，返回表中的多条记录
    public <T> List<T> getInstanceListBasic(Class<T> clazz, String sql, Object...args) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getConnection();
            ps = conn.prepareStatement(sql);
            for(int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            rs = ps.executeQuery();
            // 获取结果集中的元数据
            ResultSetMetaData rsmd = rs.getMetaData();
            int colCount = rsmd.getColumnCount();
            // 创建集合对象
            List<T> list = new ArrayList<>();
            while(rs.next()) {
                T t = clazz.newInstance();
                for(int i = 0; i < colCount; i++) {
                    Object value = rs.getObject(i + 1);
                    // 获取列名
                    // String colName = rsmd.getColumnName(i + 1);
                    String colName = rsmd.getColumnLabel(i + 1);
                    // 给 t 对象指定的某个属性赋值
                    Field field = clazz.getDeclaredField(colName);
                    field.setAccessible(true);
                    field.set(t, value);
                }
                list.add(t);
            }

            return list;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, ps, rs);
        }

        return null;
    }
    // 考虑事务的针对不同表的通用查询操作，返回表中的一条记录
    public T getInstance(Connection conn, String sql, Object...args) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement(sql);
            for(int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            rs = ps.executeQuery();
            // 获取结果集中的元数据
            ResultSetMetaData rsmd = rs.getMetaData();
            int colCount = rsmd.getColumnCount();
            if(rs.next()) {
                T t = clazz.newInstance();
                for(int i = 0; i < colCount; i++) {
                    Object value = rs.getObject(i + 1);
                    String colName = rsmd.getColumnLabel(i + 1);
                    // 给 t 对象指定的某个属性赋值
                    Field field = clazz.getDeclaredField(colName);
                    field.setAccessible(true);
                    field.set(t, value);
                }
                return t;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(null, ps, rs);
        }

        return null;
    }
    // 针对不同表的通用查询操作，返回表中的一条记录
    public <T> T getInstanceBasic(Class<T> clazz, String sql, Object...args) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getConnection();
            ps = conn.prepareStatement(sql);
            for(int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            rs = ps.executeQuery();
            // 获取结果集中的元数据
            ResultSetMetaData rsmd = rs.getMetaData();
            int colCount = rsmd.getColumnCount();
            if(rs.next()) {
                T t = clazz.newInstance();
                for(int i = 0; i < colCount; i++) {
                    Object value = rs.getObject(i + 1);
                    String colName = rsmd.getColumnLabel(i + 1);
                    // 给 t 对象指定的某个属性赋值
                    Field field = clazz.getDeclaredField(colName);
                    field.setAccessible(true);
                    field.set(t, value);
                }
                return t;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, ps, rs);
        }

        return null;
    }
}

CustomerDAO

package com.yukiyama.jdbc.dao2;

import com.yukiyama.jdbc.bean.Customer;

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

public interface CustomerDAO {
    // 根据指定的cust插入一行
    void insert(Connection conn, Customer cust);
    // 根据ID删除一行
    void deleteById(Connection conn, int id);
    // 根据ID修改一行
    void updateById(Connection conn, Customer cust);
    // 根据id查询一行记录
    Customer getCustomerById(Connection conn, int id);
    // 查询表中的所有记录
    List<Customer> getAll(Connection conn);
    // 返回 count(*)
    Long getCount(Connection conn);
    // 返回最大生日
    Date getMaxBirth(Connection conn);
}

CustomerDAOImpl

package com.yukiyama.jdbc.dao2;

import com.yukiyama.jdbc.bean.Customer;

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

public class CustomerDaoImpl extends BaseDAO<Customer> implements CustomerDAO {
    @Override
    public void insert(Connection conn, Customer cust) {
        String sql = "insert into customers (name, email, birth) values(?, ?, ?)";
        update(conn, sql, cust.getName(), cust.getEmail(), cust.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 cust) {
        String sql = "update customers set name = ?, email = ?, birth = ? where id = ?";
        update(conn, sql, cust.getName(), cust.getEmail(), cust.getBirth(), cust.getId());
    }
    @Override
    public Customer getCustomerById(Connection conn, int id) {
        String sql = "select id, name, email, birth from customers where id = ?";
        Customer cust = getInstance(conn, sql, id);
        return cust;
    }
    @Override
    public List<Customer> getAll(Connection conn) {
        String sql = "select id, name, email, birth from customers";
        List<Customer> customers = getInstanceList(conn, sql);
        return customers;
    }
    @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);
    }
}

数据库连接池

基本概念

在开发基于数据库的 web 程序时，传统模式如下：

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

这种模式存在的问题如下。

问题	描述
连接资源重用率差	需要连接时创建新的连接实例，使用后再关闭，频繁的连接与断开占用大量系统资源。
获取连接速度慢	需要连接时通过 DriverManager 获取连接实例 (需验证用户名和密码)，耗时较久。
内存泄漏	若未能正常关闭连接，可能导致内存泄漏。
无法控制连接数	每次需要连接时都会新建一个连接实例。

类似线程池的思想，为解决上述问题，可采用 数据库连接池技术。

缓冲池: 与线程池思想类似，为数据库连接建立一个「缓冲池」。预先在缓冲池中放入一定数量的连接实例，需要连接时，从中取出一个，使用完毕之后再放回。
连接管理: 连接池负责管理连接，例如分配和释放。它允许重复利用现有连接而不必总是新建。

数据库连接池较重要的两个参数是 「最小连接数」 和 「最大连接数」 。连接池初始化时会创建最小连接数个连接，无论这些连接是否被使用，连接池都将一直保证至少拥有该数量的连接实例。最大连接数限制了连接池拥有的最大连接数，当应用程序向连接池请求的连接数超过该值时，请求将被加入到等待队列中。

通过连接池即可解决前述问题，也即数据库连接池的优点如下。

优点	描述
连接资源重用	避免了频繁创建，释放连接引起的大量性能开销。
提高获取连接的速度	连接池初始化时已经创建了若干连接实例，需要时可立即使用。
连接资源可分配	通过连接池的配置，可控制资源的分配，例如限制最大连接数等。
避免连接资源泄漏	根据预先的占用超时设定，强制回收被占用连接，避免内存泄漏。

DataSource

JDBC 的数据库连接池对应 javax.sql.DataSource 称为 「数据源」，包含 「连接池」和「连接池管理」 。DataSource 只是一个接口，该接口通常由服务器 (Weblogic, WebSphere, Tomcat) 提供实现，也有一些数据库连接池开源项目实现，例如 DBCP、C3P0 和 Druid。使用连接池时注意如下两点。

数据库连接池 (DataSource) 和数据库连接不同，DataSource 无需创建多个，它是产生数据库连接的工厂，因此整个应用只需要一个 DataSource 即可。
使用连接池来获取连接，使用完毕后执行 conn.close(); 时，实际上并未关闭该连接，而是将该连接释放回连接池中。

C3P0

C3P0 是一个开源组织提供的一个数据库连接池，hibernate 官方推荐使用。

连接参数硬编码方式

@Test
public void test() throws PropertyVetoException, SQLException {
    ComboPooledDataSource cpds = new ComboPooledDataSource("c3p0_hello");
    cpds.setDriverClass( "com.mysql.cj.jdbc.Driver" ); //loads the jdbc driver
    cpds.setJdbcUrl( "jdbc:mysql://localhost:3306/test" );
    cpds.setUser("root");
    cpds.setPassword("xxxxxxxx");
    // 设置初始连接数
    cpds.setInitialPoolSize(5);
    Connection conn = cpds.getConnection();
    System.out.println(conn);
    // 销毁 cpds 连接池
    // DataSources.destroy(cpds);
}

配置文件方式

// 从配置文件中读取配置参数
@Test
public void test1() throws SQLException {
    ComboPooledDataSource cpds = new ComboPooledDataSource("c3p0Hello");
    Connection conn = cpds.getConnection();
    System.out.println(conn);
    // 销毁 cpds 连接池
    // DataSources.destroy(cpds);
}

src下的配置文件为：c3p0-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
    <named-config name="c3p0Hello">
        <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/test</property>
        <property name="user">root</property>
        <property name="password">xxxxxxx</property>
        <!-- 连接数不够时，一次性向连接池申请的连接数 -->
        <property name="acquireIncrement">5</property>
        <!-- 初始连接数 -->
        <property name="initialPoolSize">10</property>
        <!-- 最小连接数 -->
        <property name="minPoolSize">10</property>
        <!-- 最大连接数 -->
        <property name="maxPoolSize">100</property>
        <!-- 最大 statement 数 -->
        <property name="maxStatements">50</property>
        <!-- 每个连接的最大连接数 -->
        <property name="maxStatementsPerConnection">2</property>
    </named-config>
</c3p0-config>

DBCP

DBCP 是 Apache 软件基金组织下的开源连接池实现，该连接池依赖该组织下的另一个开源系统：Common-pool。如需使用该连接池实现，应在系统中增加如下两个 jar 文件：

Commons-dbcp.jar：连接池的实现
Commons-pool.jar：连接池实现的依赖库

Tomcat 的连接池正是采用该连接池来实现的。 该数据库连接池既可以与应用服务器整合使用，也可由应用程序独立使用。

属性	默认值	说明
initialSize	0	连接池启动时创建的初始化连接数量
maxActive	8	连接池中可同时连接的最大的连接数
maxIdle	8	连接池中最大的空闲的连接数，超过的空闲连接将被释放，如果设置为负数表示不限制
minIdle	0	连接池中最小的空闲的连接数，低于这个数量会创建新的连接。该参数越接近maxIdle，性能越好，因为连接的创建和销毁，都是需要消耗资源的；但是不能太大。
maxWait	无限制	最大等待时间，当没有可用连接时，连接池等待连接释放的最大时间，超过该时间限制会抛出异常，如果设置-1表示无限等待
poolPreparedStatements	false	开启池的Statement是否prepared
maxOpenPreparedStatements	无限制	开启池的prepared 后的同时最大连接数
minEvictableIdleTimeMillis		连接池中连接，在时间段内一直空闲，被逐出连接池的时间
removeAbandonedTimeout	300	超过时间限制，回收没有用(废弃)的连接
removeAbandoned	false	超过removeAbandonedTimeout时间后，是否进行没用连接（废弃）的回收

连接参数硬编码方式

private static BasicDataSource ds = new BasicDataSource();
@Test
public void test() throws SQLException {
    ds.setDriverClassName("com.mysql.cj.jdbc.Driver");
    ds.setUrl("jdbc:mysql://localhost:3306/test");
    ds.setUsername("root");
    ds.setPassword("xxxxxxxx");

    ds.setInitialSize(10);
    ds.setMaxActive(10);
    Connection conn = ds.getConnection();
    System.out.println(conn);
}

配置文件方式

private static DataSource ds1;
static {
    try {
        Properties pros = new Properties();
        InputStream is = new FileInputStream(new File("src/dbcp.properties"));
        pros.load(is);
        ds1 = BasicDataSourceFactory.createDataSource(pros);
    } catch (Exception e) {
        throw new RuntimeException(e);
    }
}
@Test
public void test1() throws IOException, SQLException {
    Connection conn = ds1.getConnection();
    System.out.println(conn);
}

src下的配置文件为：dbcp.properties

driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/test
username=root
password=xxxxxxx

Druid

Druid 是阿里巴巴开源的数据库连接池实现，它结合了 C3P0、DBCP、Proxool 等的优点，同时加入了日志监控，可以很好的监控连接池和 SQL 的执行情况，可以说是针对监控而生的数据库连接池。

配置文件方式

private static DataSource ds;
static {
    try {
        Properties pros = new Properties();
        // InputStream is = new FileInputStream(new File("src/druid.properties"));
        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");
        pros.load(is);
        ds = DruidDataSourceFactory.createDataSource(pros);
    } catch (Exception e) {
        throw new RuntimeException(e);
    }
}
@Test
public void test() throws IOException, SQLException {
    Connection conn = ds.getConnection();
    System.out.println(conn);
}

src下的配置文件为：druid.propertis

driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/test
username=root
password=xxxxxxx
initialSize=5
maxActive=10

详细配置参数

Apache-DBUtils

commons-dbutils 是 Apache 组织提供的一个开源 JDBC工具类库，它封装了 JDBC 的操作，极大简化了操作数据库的编码工作。主要有如下三个类 (均在 org.apache.commons.dbutils 下)。

dbutils主要类	描述
`QueryRunner`	封装DML操作
`ResultSetHandler`	支持结果集的转换
`DbUtils`	工具类

DbUtils

DbUtils ：提供如关闭连接、装载 JDBC 驱动程序等常规工作的工具类，所有方法均是静态的。主要方法如下。


`close`	检查所提供的参数是不是 NULL (Connection, Statement, ResultSet)，不是则关闭。
`closeQuietly`	完成 `close` 的功能，并且在关闭连接时不抛出 SQL 异常。
`commitAndClose`	提交连接的事务，然后关闭连接。
`commitAndCloseQuietly`	完成 `commitAndClose` 的功能，并且在关闭连接时不抛出 SQL 异常
`rollback`
`rollbackAndClose`
`rollbackAndCloseQuietly`
`loadDriver`	装载并注册 JDB C驱动程序，如果成功就返回 true 。

QueryRunner

该类封装了 DML 操作，极大简化了操作数据库的编码工作。

主要方法
`QueryRunner()`	默认构造器
`QueryRunner(DataSource ds)`	传入 DataSource 的构造器
`update`	执行更新操作（插入、修改、删除)
`insert`	执行插入操作
`batch`	批处理
`insertBatch`	插入操作批处理
`query`	执行一个查询操作，在这个查询中，对象数组中的每个元素值被用来作为查询语句的置换参数。该方法会自行处理 PreparedStatement 和 ResultSet 的创建和关闭。

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	查询单个值对象。

代码示例

package com.yukiyama.jdbc.dbutils;

import com.yukiyama.jdbc.bean.Customer;
import com.yukiyama.jdbc.utils.JDBCUtils2;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.*;
import org.junit.Test;

import java.io.IOException;
import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

public class QueryRunnerTest {
    // 插入一条数据
    @Test
    public void testInsert(){
        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();
            conn = JDBCUtils2.getConnectionByDruid();
            String sql = "insert into customers (name, email, birth) values (?, ?, ?)";
            int insertCount = runner.update(conn, sql, "duoduo", "duo@gmail.com", "1998-09-09");
            System.out.println("添加了" + insertCount + "条记录。");
        } catch (IOException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils2.closeResource(conn, null);
        }
    }
    // 查询一条记录 BeanHandler<>
    @Test
    public void testQuery1() {
        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();
            conn = JDBCUtils2.getConnectionByDruid();
            String sql = "select id, name, email, birth from customers where id = ?";
            BeanHandler<Customer> handler = new BeanHandler<>(Customer.class);
            Customer cust = runner.query(conn, sql, handler, 18);
            System.out.println(cust);
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils2.closeResource(conn, null);
        }
    }
    // 查询多条记录 BeanListHandler<>
    @Test
    public void testQuery2() {
        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();
            conn = JDBCUtils2.getConnectionByDruid();
            String sql = "select id, name, email, birth from customers";
            BeanListHandler<Customer> handler = new BeanListHandler<>(Customer.class);
            List<Customer> list = runner.query(conn, sql, handler);
            list.forEach(System.out::println);
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils2.closeResource(conn, null);
        }
    }
    // 查询一条记录，以map形式封装 MapHandler
    @Test
    public void testQuery3() {
        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();
            conn = JDBCUtils2.getConnectionByDruid();
            String sql = "select id, name, email, birth from customers where id = ?";
            MapHandler handler = new MapHandler();
            Map<String, Object> map = runner.query(conn, sql, handler, 18);
            System.out.println(map);
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils2.closeResource(conn, null);
        }
    }
    // 查询多条记录，以map形式封装 MapListHandler
    @Test
    public void testQuery4() {
        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();
            conn = JDBCUtils2.getConnectionByDruid();
            String sql = "select id, name, email, birth from customers";
            MapListHandler handler = new MapListHandler();
            List<Map<String, Object>> list = runner.query(conn, sql, handler);
            list.forEach(System.out::println);
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils2.closeResource(conn, null);
        }
    }
    // 查询单个值 ScalarHandler
    @Test
    public void testQuery5() {
        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();
            conn = JDBCUtils2.getConnectionByDruid();
            String sql = "select count(*) from customers";
            ScalarHandler handler = new ScalarHandler();
            Long count = (Long) runner.query(conn, sql, handler);
            System.out.println(count);
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils2.closeResource(conn, null);
        }
    }
    // 查询单个值
    @Test
    public void testQuery6() {
        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();
            conn = JDBCUtils2.getConnectionByDruid();
            String sql = "select max(birth) from customers";
            ScalarHandler handler = new ScalarHandler();
            Date date = (Date) runner.query(conn, sql, handler);
            System.out.println(date);
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils2.closeResource(conn, null);
        }
    }
    // 自定义 ResultSetHandler
    @Test
    public void testQuery7() {
        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();
            conn = JDBCUtils2.getConnectionByDruid();
            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 {
                    if(rs.next()){
                        int id = rs.getInt("id");
                        String name = rs.getString("name");
                        String email = rs.getString("email");
                        Date birth = rs.getDate("birth");
                        Customer customer = new Customer(id, name, email, birth);
                        return customer;
                    }
                    return null;
                }
            };
            Customer cust = runner.query(conn, sql, handler, 18);
            System.out.println(cust);
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            DbUtils.closeQuietly(conn);
            // JDBCUtils2.closeResource(conn, null);
        }
    }
}

总结

本文内容总结如下。

要点	备注
描述JDBC	公共接口 / 标准 Java 库类 / 面向接口
JDBC API 体系	面向应用 (使用 JDBC 连接和操作不同的 DBMS) 面向具体数据库管理系统 (各DBMS需适配)
使用 JDBC 连接数据库的步骤	1. 加入依赖 `mysql-connector-java-8.0.11.jar` 2. 使用 `Driver` 或 `DriverManage` 来获取连接实例 `Connection`
使用 JDBC 连接数据库的代码实现	三要素: Driver接口实现类 / url / 用户名&密码
使用反射实例化 Driver
使用 DriverManager 代替 Driver	在 DriverManager 中注册具体的 Driver 实例 `DriverManager.registerDriver(new Driver());`
Driver 实现类中的静态代码块	静态代码块中完成注册
将必要信息写到配置文件中	代码与配置解耦
操作数据库	Statement PreparedStatement CallableStatement
Statement的弊端	需要拼串 SQL注入
SQL 注入的例子	`select user, password from user_table where user = '' and password = ''` `select user, password from user_table where user = '1' or ' and password = '=1 or '1' = '1'`
避免 SQL 注入的方法	使用 PreparedStatement 预编译后，通过占位符确定了 sql 语句逻辑关系，避免拼串导致逻辑关系被修改
PreparedStatement 的优点	1. 预编译，防止 SQL 注入 2. 可以通过占位符传入流，实现对大文件的操作 3. 可以实现更高效的批量操作 (原因是预编译)
使用 PreparedStatement 操作数据库	1. 获取数据库连接 2. 写出预编译 sql 语句，返回 PreparedStatement 实例 3. 填充占位符 4. 执行 5. 关闭资源
ORM	Object Relational Model 对象关系模型 Object Relational Mapping 对象关系映射 - 一个数据表对应一个 java 类 - 表中的一条记录对应 java 类的一个对象实例 - 标中的一个字段对应 java 类的一个属性
通用增删改方法	基于 PreparedStatement
通用查询方法
结果集	`ResultSet rs = ps.executeQuery()`
结果集元数据	`ResultSetMetaData rsmd = rs.getMetaData();`
获取列数	`int colCount = rsmd.getColumnCount();`
将结果中的一行输出为对象实例	利用反射 `Object colValue = rs.getObject(i + 1);` `String colName = rsmd.getColumnLabel(i + 1);` `Field field = Customer.class.getDeclaredField(colName);` `field.setAccessible(true);` `field.set(customer, colValue);`
获取列别名而不是列名	`getColumnLabel` 若无别名则返回列名
对不同表的通用查询方法	泛型
两种思想	1. 面向接口 2. ORM
两种技术	1. 结果集元数据 2. 反射