Java - JDBC

JDBC 全称 Java DataBase Connectivity，是一套接口，各个厂商会实现此接口以 .jar 包方式在官网提供，我们通过调用接口来操作数据库，完全不需要关注接口实现细节。

目录

• 目录
• 1 导入包
• 2 操作数据库六步骤
  • 2.1 注册驱动
  • 2.2 建立数据库连接
  • 2.3 创建数据库对象
  • 2.4 执行 SQL 语句
  • 2.5 处理结果集
    • 结果集元数据
    • 执行 INSERT 获取自增长主键的值
  • 2.6 释放资源
• 3 预编译
• 4 DAO
• 5 连接池

1 导入包

存文本编写时，需要配置 JVM 环境变量 CLASSPATH，告诉 JVM 需要的 CLASS 文件在哪里能找到。

CLASPATH=.;E:\mysql-connector-j-8.2.0.jar

不然就编译时用 -cp 指定位置。

javac -encoding UTF-8 -cp '.;E:\mysql-connector-j-8.2.0.jar' jdbc.java

JDBC 文档。包含在 JDK 当中，叫 java.sql

MySQL 的 JDBC 实现在官网上能够下载。

2 操作数据库六步骤

2.1 注册驱动

com.mysql.cj.jdbc.Driver 实现了 JDK 中的 java.sql.Driver 接口。

整个 JDBC 对象全由 DriverManager 管理，我们只需要调用里面的方法就可以完成 SQL 操作。

创建 MySQL JDBC 驱动对象也可以用反射的方式。因为通过反射写的是类权限定名，可以放到配置文件中，方便后续修改加载。符合 OCP 原则。

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

// Notice, do not import com.mysql.cj.jdbc.*
// or you will have problems!

public class LoadDriver {
    public static void main(String[] args) {
        try {
            // The newInstance() call is a work around for some
            // broken Java implementations

            Class.forName("com.mysql.cj.jdbc.Driver").newInstance();
        } catch (Exception ex) {
            // handle the error
        }
    }
}

MySQL :: MySQL Connector/J Developer Guide :: 7.1 Connecting to MySQL Using the JDBC DriverManager Interface

或者手动导包创建对象。创建 jdbc00.java。

import java.sql.SQLException;
import java.sql.DriverManager;
import java.sql.Driver;

public class jdbc00 {
    public static void main(String[] args) {
        try {
            // 1.注册驱动
            // 1.1 创建 MySQL 实例，而且官方不建议 import com.mysql.cj.jdbc.Driver 再去 new Driver()
            Driver driver = new com.mysql.cj.jdbc.Driver();
            // 1.2 注册驱动，交给 DriverManager 全程管理
            DriverManager.registerDriver(new Driver());
        } catch (SQLException ex) {
            // 必须要处理 SQLException 异常，因为 new com.mysql.cj.jdbc.Driver(); 对象和
            // 调 DriverManager.registerDriver 都往外抛。
            print("驱动注册失败：" + ex)
        }
    }
}

也看到 MySQL 源码（src\main\user-impl\java\com\mysql\cj\jdbc\Driver.java）中自己通过静态代码快注册了驱动，所以可以不注册？需要后续尝试下。

// Register ourselves with the DriverManager.
static {
    try {
        java.sql.DriverManager.registerDriver(new Driver());
    } catch (SQLException E) {
        throw new RuntimeException("Can't register driver!");
    }
}

Java 6（JDBC 4.0）开始可以不用注册驱动，由 Java 自动注册。但是为了防止失败还是我们手动注册比较稳妥。

https://community.progress.com/s/article/4800 https://docs.oracle.com/javadb/10.8.3.0/ref/rrefjdbc4_0summary.html

2.2 建立数据库连接

使用 DriverManager 类静态方法 getConnection 建立数据库连接。

• static Connection getConnection(String url) throws SQLException
• static Connection getConnection(String url, String user, String password) throws SQLException
• static Connection getConnection(String url, Properties info) throws SQLException

这有三种方式去连接数据库，但都要填一个叫 url 的参数。它有个固定格式 协议://主机:端口/数据库名/连接参数，像是协议是 jdbc:mysql// 是固定的，后面的主机、端口、数据库都按照实际的填即可。

jdbc:mysql://<host>:<port>/<database_name>?<connection_parameters>

只是连接参数不太好分辨是什么意思，连接参数一般都是连接数据库需要配置的内容，比如调优之类的，最常用到的就是用户名和密码，下面简单列了几个常见到的参数：

• user，数据库用户名
• password，数据库密码
• serverTimezone，设置时区，涉及到时间处理上，最好指定时区，不然默认 JDBC 会以客户端连接的时区查数据。
• useSSL，设置 true 或 false，用在数据库连接阶段
• useUnicode，设置传输过程中数据是不是使用 Unicode 编码，默认是 true
• characterEncoding，比如插入、创建数据库使用什么编码，默认 UTF-8

参数填写格式也很简单 p1=v1&p2=v2，跟 URL 参数一样的格式，下面例子中说明了整个连接过程启用 SSL 建立，使用账户 root，密码为空，连接 localhost:3306 的 mysql 数据库。

jdbc:mysql//localhost:3306/test?user=root&password=&useSSL=true

jdbc00.java。这里提供了三种建立连接的方式，第一种直接传 url，第二种账户单独写，第三种读配置文件。

import java.sql.SQLException;
import java.sql.DriverManager;
import java.sql.Driver;
import java.util.Properties;;
import java.util.FileReader;
import java.io.IOException;

public class jdbc00 {
    public static void main(String[] args) {
        Connection conn = null;

        try {
            // 1.注册驱动
            // 1.1 创建 MySQL 实例，而且官方不建议 import com.mysql.cj.jdbc.Driver 再去 new Driver()
            Driver driver = new com.mysql.cj.jdbc.Driver();
            // 1.2 注册驱动，交给 DriverManager 全程管理
            DriverManager.registerDriver(new Driver());


            // 2.建立连接数据库连接
            // 2.1 直接写完整 url
            conn = DriverManager.getConnection("jdbc:mysql://localhost/mysql?" +
                    "user=root&password=");
            // 2.2 先写 url，再填密码
            conn = DriverManager.getConnection("jdbc:mysql//localhost:3306/mysql", "root", "");
            // 2.3 通过传配置文件来建立连接
            Properties dbConf = new Properties();
            FileReader fileReader = new FileReader("../conf/db.properties");
            dbConf.setProperty("useUnicode", "true");
            dbConf.load(fileReader);
            conn = DriverManager.getConnection("jdbc:mysql://localhost/mysql", dbConf);
        } catch (SQLException ex) {
            // 必须要处理 SQLException 异常，因为 new com.mysql.cj.jdbc.Driver(); 对象和
            // 调 DriverManager.registerDriver 都往外抛。
            print("驱动注册失败：" + ex)
        } catch (IOException e) {
            throw new RuntimeException(e);
        } 
    }
}

2.3 创建数据库对象

getConnection 方法成功建立数据库连接会返回的对象 Connection，通过调用 Connection 对象的实例方法 createStatement() 方法获得数据库对象 Statement。

import java.sql.*;

public class jdbc04 {
    public static void main(String[] args) {
        Connection conn = null;
        Statement statement = null;
        
        try {
            // 1.注册驱动
            Driver driver = new com.mysql.cj.jdbc.Driver();
            DriverManager.registerDriver(driver);

            // 2.建立数据库连接
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mysql?user=root&password=");

            // 3.获取数据库对象
            statement = conn.createStatement();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}

2.4 执行 SQL 语句

通过数据库对象的 Statement 的实例方法执行 SQL：

• int executeUpdate(String sql) throws SQLException，DML 语句使用，执行完返回成功行数，返回值 0 可能是 DDL 语句没有结果。
• boolean execute(String sql) throws SQLException，DQL 语句使用，查询出结果集返回 True，没有结果 False。
• ResultSet executeQuery(String sql) throws SQLException，执行查询 SQL

2.5 处理结果集

如果是 DQL 语句，才需要处理，像是 DML 这种就没有返回的数据，无需处理结果集。

获取数据需要两步，第一步是移动到数据行上，第二步读取数据：

1. boolean next() throws SQLException，移动到数据上。第一次执行会移动到第一行上，如果有数据就返回 true，如果没有返回 false。
2. getter 方法读数据，比如 getString(String columnLabel) 就可以通过 SQL 结果集中列数得到 String 类型数据，getString(int columnIndex) 则可以通过结果集列名得到 String 数据，但前提是数据库种的数据也是相同类型的数据，要是不一致就会抛异常。其他方法 getInt、getTime、getShort...... 也是一样的都能获得对应数据。

get* 方法获取数据，数据填列数，jdbc 下标从 1 开始，列数都是从 1 开始。或者也可以填 SQL 执行完返回的列名。

这里假设用 executeQuery 方法执行 SQL resultSet = statement.executeQuery("SELECT Host, User, authentication_string auth FROM user"); 返回以下结果集。

由于数据都是字符，这里就统一用 getString 方法获取数据。第一行数据通过 resultSet.getString(1) 输入列的数字来获取，第一列肯定是 Host，第二列是 User，第三列是 auth。第二行到第四行，直接通过查询结果集种的列名来获取数据，但是要注意第三列是别名 auth，并不是数据库的列名 authentication_string。

// 5.处理结果集
if (resultSet.next()) {
    System.out.println(resultSet.getString(1));
    System.out.println(resultSet.getString(2));
    System.out.println(resultSet.getString(3));
}
System.out.println();

if (resultSet.next()) {
    System.out.println(resultSet.getString("Host"));
    System.out.println(resultSet.getString("User"));
    System.out.println(resultSet.getString("auth"));
}
System.out.println();

if (resultSet.next()) {
    System.out.println(resultSet.getString("Host"));
    System.out.println(resultSet.getString("User"));
    System.out.println(resultSet.getString("auth"));
}
System.out.println();

if (resultSet.next()) {
    System.out.println(resultSet.getString("Host"));
    System.out.println(resultSet.getString("User"));
    System.out.println(resultSet.getString("auth"));
}

获取数据返回。

localhost
mysql.infoschema
$A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED

localhost
mysql.session
$A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED

localhost
mysql.sys
$A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED

localhost
root

要想获取所有数据手动一直判断 next() 很麻烦，不够便捷，这里使用 while 循环能很方便的判断取出所有数据。

while(resultSet.next()) {
    System.out.println(resultSet.getString("Host"));
    System.out.println(resultSet.getString("User"));
    System.out.println(resultSet.getString("auth"));
    System.out.println();
}

结果集元数据

结果集元数据 ResultSetMetaData，主要是获取指定结果集中的列相关信息，比如列名、类型、列的数量以及列是不是可写，能不能为空。

还是用前面的 SQL 结果集表格中数据为例，通过 getMetaData 方法，获取元数据对象。

// 获取元数据对象
ResultSetMetaData metaData = resultSet.getMetaData();

使用 ResultSetMetaData 对象中的实例方法，得到列的基本信息。

System.out.println("结果集中一共有" + metaData.getColumnCount() + "列");
for (int i = 1; i <= metaData.getColumnCount(); i++) {
    System.out.println("第" + i + "列叫：" + metaData.getColumnName(i) +
            "，类型是：" + metaData.getColumnType(i) +
            "，类型值长：" + metaData.getColumnDisplaySize(i) +
            "，这个列归属" + metaData.getTableName(i) + "表" +
            "，这个表归属" + metaData.getCatalogName(i) + "数据库"
    );

}

打印结果。

结果集中一共有3列
第1列叫：Host，类型是：CHAR，类型值长：255，这个列归属user表，这个表归属mysql数据库
第2列叫：User，类型是：CHAR，类型值长：32，这个列归属user表，这个表归属mysql数据库
第3列叫：authentication_string，类型是：TEXT，类型值长：21845，这个列归属user表，这个表归属mysql数据库

执行 INSERT 获取自增长主键的值

这里临时创建个表做实例演示。

DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user`  (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '用户 ID',
  `name` varchar(255) NOT NULL COMMENT '用户名',
  `password` varchar(255) NOT NULL COMMENT '用户密码',
  `realname` varchar(255) COMMENT '真实姓名',
  `gender` varchar(1) COMMENT '性别',
  `tel` varchar (11) COMMENT '手机号码',
  PRIMARY KEY (`id`)
) COMMENT = '这是一张存储用户信息的表'

使用下面方法可以在插入数据后获取字段设置为 AUTO_INCREMENT 的值：

• int executeUpdate(String sql, int autoGeneratedKeys)
• boolean execute(String sql, int autoGeneratedKeys)

当然也不止这些方法能获取，只要有 int autoGeneratedKeys 参数的方法都可以。

这些方法使用也很简单，在执行 INSERT 语句时传递 autoGeneratedKeys 参数即可，这个参数只接受 1 才会生成，因此 Statement.RETURN_GENERATED_KEYS 常量就行，比 1 更有辨识度。

// 4.执行 DML 语句
String sql = "INSERT INTO `sys`.`t_user` (`name`, `password`) VALUES ('lisi', 'lisi1234')";
resultSet = statement.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
System.out.println("成功插入" + resultSet + "条数据");

执行完 SQL 后 通过 Statement 类实例方法 getGeneratedKeys() 得到 AUTO_INCREMENT 列的结果集，透过结果集就能获取对应的值。

// 5.获取主键
ResultSet generatedKeys = statement.getGeneratedKeys();
if (generatedKeys.next()) {
    System.out.println("结果集中一共有：" + generatedKeys.getMetaData().getColumnCount() + "列");
    System.out.println("这列名称叫：" + generatedKeys.getMetaData().getColumnName(1));
    System.out.println("通过列 id 查询值为：" + generatedKeys.getString(1));
    System.out.println("通过结果集中列名查询值为：" + generatedKeys.getString("GENERATED_KEY"));
}

运行打印

成功插入1条数据
结果集中一共有：1列
这列名称叫：GENERATED_KEY
通过列 id 查询值为：22
通过结果集中列名查询值为：22

2.6 释放资源

从 ResultSet -> Statment -> Connection 的顺序依次来关闭。

这三个接口都有 close() 方法，通过调用这个方法来释放资源。这里分别 try-catch 是怕放一起其中某个出异常，后面不再执行导致资源关闭不完全。

public static void main(String[] args) {
    Connection conn = null;
    Statement statement = null;
    ResultSet resultSet = null;
    try {
        // 1.注册驱动
        ......
        
        // 2.建立数据库连接
        conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mysql?user=root&password=");

        // 3.获取数据库对象
        statement = conn.createStatement();

        // 4.执行 DQL 语句
        resultSet = statement.executeQuery("SELECT Host, User, authentication_string auth FROM user");
    } catch (SQLException e) {
        throw new RuntimeException(e);
    } finally {
        // 6.关闭资源
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }
}

为了省事儿也可以尝试 try-with-resources 自动关闭资源，更便捷，更安全。在 try() 圆括号当中的内容会自动释放资源。

public static void main(String[] args) {
    try (
        // 2.建立数据库连接
        Connection conn = jdbcUtil.getConnection();
        // 3.获取数据库对象
        Statement statement = conn.createStatement();
        // 4.执行 DQL 语句，获取结果集
        ResultSet resultSet = statement.executeQuery("SELECT Host, User, authentication_string auth FROM user");
    ){
        // 5.处理结果集
        while(resultSet.next()) {
            System.out.println(resultSet.getString("Host"));
            System.out.println(resultSet.getString("User"));
            System.out.println(resultSet.getString("auth"));
            System.out.println();
        }
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }
}

其中的注册驱动、创建数据库连接、释放资源这几种重复使用到的方法封装到 ToolUtils.jdbcUtil，作为工具类使用。

package ToolUtils;

import java.lang.reflect.InvocationTargetException;
import java.sql.*;
import java.util.ResourceBundle;

public class jdbcUtil {
    private static String driver;
    private static String url;
    private static String user;
    private static String password;

    /**
     * 注册驱动
     */
    static {
        ResourceBundle db = ResourceBundle.getBundle("db");
        jdbcUtil.driver = db.getString("driver");
        jdbcUtil.url = db.getString("url");
        jdbcUtil.user = db.getString("user");
        jdbcUtil.password = db.getString("password");

        try {
            Driver driver = (Driver) Class.forName(jdbcUtil.driver).getDeclaredConstructor().newInstance();
            DriverManager.registerDriver(driver);
        } catch (ClassNotFoundException | NoSuchMethodException | InstantiationException | IllegalAccessException |
                 InvocationTargetException | SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 工具类不需要实例化
     */
    private jdbcUtil() {}


    /**
     * 获取数据库连接
     * @return 返回 Connection 连接对象
     * @throws SQLException
     */
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(jdbcUtil.url, jdbcUtil.user, jdbcUtil.password);
    }

    /**
     * 释放资源
     * @param resultSet 结果集
     * @param connection 连接
     * @throws SQLException
     */
    public static void releaseResource(ResultSet resultSet, Statement statement, Connection connection) throws SQLException{
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }
}

3 预编译

预编译最大的好处是能杜绝 SQL 注入，另一个好处是执行效率会高。再往后所有的 SQL 都会用预编译的方式写。

以 INSERT 举例。首先得先定义出要指定的 SQL，用户输入的值以问号（？）作为占位符后续再填充具体值。

// 3.要执行的 SQL
String sql = "INSERT INTO `sys`.`t_user` (`name`, `password`) VALUES (?, ?)";

Connection 的 prepareStatement 方法预编译 SQL。

// 3.获取数据库对象，对 SQL 进行预编译，通过指定返回主键常量，后续通过  getGeneratedKeys() 得到子增长列结果集
statement = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

得到编译的数据库 PreparedStatement 对象后，通过 PreparedStatement 对应 SetXXX 方法来把替换问号占位符的值，这里由于数据库是这两列是 varchar 所以就用 setString 设置值，如果两边类型不一致会抛异常。

// 4.给 SQL 占位符赋值
statement.setString(1, "lisi");
statement.setString(2, "lisi1234");

使用 PreparedStatement 的 executeXXX 实例方法来执行 SQL，其他执行 SQL 的方法和 Statement 一样。

// 5.执行 DML 语句
insertResult = statement.executeUpdate();

PreparedStatment 预编译效率高。只需要编译一次后续可以重复执行，比 Statment 效率高。

PreparedStatment 类型安全，强制类型检查。在设置占位符值的时候要求你主动思考对应值是什么类型，避免 SQL 查询错误。比如人家要求传递 Date 你却传递 Int/String。

4 DAO

DAO 全称 Data Access Object，是种 JavaEE 中的设计模式，每个 DAO 在实际开发中只是针对数据库这层操作，而 DAO 则是被业务层调用，业务层由视图层调用，视图层被客户端发生的请求调用，这样各层级分别负责自己的事，各司其职，这是种分层思想。

每个 DAO 都是用来操作数据库中的表，因此会创建一个 Java 类来操作，命名通常是 tablenameDAO，比如表名是 t_user 类名会写成为 UserDAO。

其实 DAO 有很多查询方面的操作，那么查询结果就需要一个对象来代表，这时候就需要按照表中字段建立一个类，里面的实例变量都是字段名。通常这个类叫做 JavaBean，POJO（Plain Old Java Object），Domain Model（领域模型），但是他们都是指同一个东西。

User.java。这里创建一个 User 表的 POJO 类，User 表的返回的一行数据都映射为一个 User 类的对象，里面的属性对应结果集中的列。设置属性需要注意使用包装类，比如 id 类型是基本数据类型 long，加入 id 返回 null 则会报错，使用 Long 包装类则不会出现此问题。

package DAO;

/**
 * User 表的 POJO 类。
 */
public class User {
    private long id;
    private String name;
    private String password;
    private String realname;
    private String gender;
    private String tel;

    public User() {
    }

    public long getId() {
        return id;
    }

    public String getName() {
        return name;
    }

    public String getPassword() {
        return password;
    }

    public String getRealname() {
        return realname;
    }

    public String getGender() {
        return gender;
    }

    public String getTel() {
        return tel;
    }

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

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

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

    public void setRealname(String realname) {
        this.realname = realname;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }

    public void setTel(String tel) {
        this.tel = tel;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", password='" + password + '\'' +
                ", realname='" + realname + '\'' +
                ", gender='" + gender + '\'' +
                ", tel='" + tel + '\'' +
                '}';
    }
}

UserDAO.java。来对 User 表进行 CRUD 操作。

package DAO;

import ToolUtils.jdbcUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 针对 User 表进行 CRUD
 */
public class UserDAO {
    /**
     * 向 user 表插入一个用户
     * @param user
     * @return 返回 1 代表插入成功
     */
    public int insert(User user) {
        int sqlExecuteResult;
        Connection connection = null;
        PreparedStatement ps = null;
        try {
            connection = jdbcUtil.getConnection();
            String sql = "INSERT INTO `sys`.`t_user` (`name`, `password`, `realname`, `gender`, `tel`) VALUES (?, ?, ?, ?, ?)";
            ps = connection.prepareStatement(sql);
            ps.setString(1, user.getName());
            ps.setString(2, user.getPassword());
            ps.setString(3, user.getRealname());
            ps.setString(4, user.getGender());
            ps.setString(5, user.getTel());
            sqlExecuteResult = ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            jdbcUtil.releaseResource(null, ps, connection);
        }
        return sqlExecuteResult;
    }

    /**
     * 对 user 数据进行修改
     * @param user
     * @return 返回修改成功的数据条
     */
    public int update(User user) {
        int sqlExecuteResult;
        Connection connection = null;
        PreparedStatement ps = null;
        try {
            connection = jdbcUtil.getConnection();
            String sql = "UPDATE `sys`.`t_user` SET `name` = ?, `password` = ?, `realname` = ?, `gender` = ?, `tel` = ? WHERE `id` = ?";
            ps = connection.prepareStatement(sql);
            ps.setString(1, user.getName());
            ps.setString(2, user.getPassword());
            ps.setString(3, user.getRealname());
            ps.setString(4, user.getGender());
            ps.setString(5, user.getTel());
            ps.setLong(6, user.getId());
            sqlExecuteResult = ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            jdbcUtil.releaseResource(null, ps, connection);
        }
        return sqlExecuteResult;
    }

    /**
     * 删除指定 user
     * @param user
     * @return 返回删除成功的数据条
     */
    public int delete(User user) {
        int sqlExecuteResult;
        Connection connection = null;
        PreparedStatement ps = null;
        try {
            connection = jdbcUtil.getConnection();
            String sql ="DELETE FROM `sys`.`t_user` WHERE `id` = ?";
            ps = connection.prepareStatement(sql);
            ps.setLong(1, user.getId());
            sqlExecuteResult = ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            jdbcUtil.releaseResource(null, ps, connection);
        }
        return sqlExecuteResult;
    }

    /**
     * 查询 user 表中所有数据
     * @return 返回 List
     */
    public List<User> selectAll() {
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        ArrayList<User> users = new ArrayList<>();
        try {
            connection = jdbcUtil.getConnection();
            String sql = "SELECT * FROM `sys`.`t_user`";
            ps = connection.prepareStatement(sql);
            resultSet = ps.executeQuery();
            while (resultSet.next()) {
                // 将每行结果集封装成 User 对象。
                User user = new User();
                user.setId(resultSet.getLong("id"));
                user.setName(resultSet.getString("name"));
                user.setPassword(resultSet.getString("password"));
                user.setRealname(resultSet.getString("realname"));
                user.setGender(resultSet.getString("gender"));
                user.setTel(resultSet.getString("tel"));
                users.add(user);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            jdbcUtil.releaseResource(resultSet, ps, connection);
        }
        return users;
    }
}

编写对应测试方法进行 CRUD 测试。

UserDAO userDAO = new UserDAO();

// SELECT
System.out.println("查询所有数据：");
for (User user1 : userDAO.selectAll()) {
    System.out.println(user1);
}
System.out.println();

// INSERT
User user = new User();
user.setGender("男");
user.setName("raingray");
user.setPassword("123456");
user.setTel("15612341234");
user.setRealname("李四");
System.out.println("要插入的数据：" + user);
System.out.println("插入了" + userDAO.insert(user) + "条数据，下面是所有数据：");
for (User user2 : userDAO.selectAll()) {
    System.out.println(user2);
}
System.out.println();

// UPDATE
User insertData = userDAO.selectAll().get(3);
System.out.println("要修改密码为 root 数据的用户：" + insertData);
insertData.setPassword("root");
System.out.println("修改了" + userDAO.update(insertData) + "条数据，下面是所有数据：");
for (User user3 : userDAO.selectAll()) {
    System.out.println(user3);
}
System.out.println();

// DELETE
System.out.println("删除用户：" + insertData);
System.out.println("修改了" + userDAO.delete(insertData) + "条数据，下面是所有数据：");
for (User user4 : userDAO.selectAll()) {
    System.out.println(user4);
}

运行输出。

查询所有数据：
User{id=31, name='Cheng Xiuying', password='YwdkRuGAP3', realname='Cheng Xiuying', gender='F', tel='zgiwptQoj7'}
User{id=32, name='Yuan Yuning', password='4qLhnGadxZ', realname='Yuan Yuning', gender='M', tel='AEPAQckx66'}
User{id=33, name='Ng Wing Sze', password='WaZDq0V506', realname='Ng Wing Sze', gender='F', tel='UqagFETdvC'}

要插入的数据：User{id=0, name='raingray', password='123456', realname='李四', gender='男', tel='15612341234'}
插入了1条数据，下面是所有数据：
User{id=31, name='Cheng Xiuying', password='YwdkRuGAP3', realname='Cheng Xiuying', gender='F', tel='zgiwptQoj7'}
User{id=32, name='Yuan Yuning', password='4qLhnGadxZ', realname='Yuan Yuning', gender='M', tel='AEPAQckx66'}
User{id=33, name='Ng Wing Sze', password='WaZDq0V506', realname='Ng Wing Sze', gender='F', tel='UqagFETdvC'}
User{id=44, name='raingray', password='123456', realname='李四', gender='男', tel='15612341234'}

要修改密码为 root 数据的用户：User{id=44, name='raingray', password='123456', realname='李四', gender='男', tel='15612341234'}
修改了1条数据，下面是所有数据：
User{id=31, name='Cheng Xiuying', password='YwdkRuGAP3', realname='Cheng Xiuying', gender='F', tel='zgiwptQoj7'}
User{id=32, name='Yuan Yuning', password='4qLhnGadxZ', realname='Yuan Yuning', gender='M', tel='AEPAQckx66'}
User{id=33, name='Ng Wing Sze', password='WaZDq0V506', realname='Ng Wing Sze', gender='F', tel='UqagFETdvC'}
User{id=44, name='raingray', password='root', realname='李四', gender='男', tel='15612341234'}

删除用户：User{id=44, name='raingray', password='root', realname='李四', gender='男', tel='15612341234'}
修改了1条数据，下面是所有数据：
User{id=31, name='Cheng Xiuying', password='YwdkRuGAP3', realname='Cheng Xiuying', gender='F', tel='zgiwptQoj7'}
User{id=32, name='Yuan Yuning', password='4qLhnGadxZ', realname='Yuan Yuning', gender='M', tel='AEPAQckx66'}
User{id=33, name='Ng Wing Sze', password='WaZDq0V506', realname='Ng Wing Sze', gender='F', tel='UqagFETdvC'}

由于 DAO 都需要建立数据库连接、释放资源和 CRUD 这种重复操作，通常会放到 BaseDAO 类里封装起来，其他的 tablenameDAO 继承去执行 SQL 调用，tablenameDAO 只需要写 SQL 往里传参就行。

BaseDAO.java。编写通用查询和更新的方法。

package DAO;

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

public class BaseDAO {
    /**
     *
     * @param clazz 用于创建对象的类
     * @param sql 要执行的 SQL
     * @param parameters 要绑定的 SQL 参数
     * @return 返回数组，里面放着对象
     * @param <T> 泛型用来限制参数和返回值类型
     */
    public static <T> List<T> executeQuery(Class<T> clazz, String sql, Object... parameters) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<T> dataList = new ArrayList<>();

        try {
            connection = jdbcUtil.getConnection();
            preparedStatement = connection.prepareStatement(sql);

            // 绑定参数
            if (parameters != null && parameters.length > 0) {
                for (int i = 0; i < parameters.length; i++) {
                    preparedStatement.setObject(i + 1, parameters[i]);
                }
            }

            //执行 SQL
            resultSet = preparedStatement.executeQuery();
            // 获取结果集元数据一会儿反射赋值用。
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnNumber = metaData.getColumnCount();

            while (resultSet.next()) {
                // 反射创建对应 POJO 对象
                T o = clazz.getDeclaredConstructor().newInstance();
                // 循环获取结果集字段名称，通过反射对私有成员变量赋值。这里一定要保证结果集中列名和 POJO 类中成员变量名一致，不然反射获取 Field 会因为名称不一样导致失败
                for (int i = 1; i <= columnNumber; i++) {
                    String columnName = metaData.getColumnName(i);
                    Field field = o.getClass().getDeclaredField(columnName);
                    field.setAccessible(true);
                    field.set(o, resultSet.getObject(i));
                }
                dataList.add(o);
            }
        } catch (SQLException | InstantiationException | InvocationTargetException | NoSuchMethodException |
                 NoSuchFieldException | IllegalAccessException e) {
            throw new RuntimeException(e);
        } finally {
            jdbcUtil.releaseResource(null, preparedStatement, connection);
        }
        return dataList;
    }

    /**
     * @param sql        要执行的 DML SQL。
     * @param parameters 要绑定的预编译参数。使用 Object 接收是因为参数可能是各种类型的，Object 比较通用。
     * @return 返回 SQL 执行完数据变动的行数。如果是更新语句，返回 3 就是更新 3 条，删除和新增也是同理。
     */
    public static int executeUpdate(String sql, Object... parameters) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        int count;
        try {
            connection = jdbcUtil.getConnection();
            preparedStatement = connection.prepareStatement(sql);

            if (parameters != null && parameters.length > 1) {
                for (int i = 0; i < parameters.length; i++) {
                    preparedStatement.setObject(i + 1, parameters[i]);
                }
            } else if (parameters != null && parameters.length == 1) {
                preparedStatement.setObject(1, parameters[0]);
            }
            count = preparedStatement.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            jdbcUtil.releaseResource(null, preparedStatement, connection);
        }
        return count;
    }
}

测试方法。

// SELECT
System.out.println("查询所有数据：");
for (User user : BaseDAO.executeQuery(User.class, "SELECT * FROM `sys`.`t_user`", "张三")) {
    System.out.println(user);
}
System.out.println();

// INSERT
System.out.println("插入用户张三：");
System.out.println("插入了" + BaseDAO.executeUpdate("INSERT INTO `sys`.`t_user` (`name`, `password`) VALUES (?, ?)",
        "张三", "root") + "条数据");
for (User user : BaseDAO.executeQuery(User.class, "SELECT * FROM `sys`.`t_user`")) {
    System.out.println(user);
}
System.out.println();

// UPDATE
System.out.println("修改张三的用户名为 raingray：");
System.out.println("更新了" +BaseDAO.executeUpdate("UPDATE `sys`.`t_user` SET `name` = 'raingray' WHERE `name` = ? and `password` = ?", "张三", "root") + "条数据");
for (User user : BaseDAO.executeQuery(User.class, "SELECT * FROM `sys`.`t_user`")) {
    System.out.println(user);
}
System.out.println();

// DELETE
System.out.println("删除 raingray 用户：");
System.out.println("删除了" + BaseDAO.executeUpdate("DELETE FROM `sys`.`t_user` WHERE `name` = ? and `password` = ?", "raingray", "root") + "条数据");
for (User user : BaseDAO.executeQuery(User.class, "SELECT * FROM `sys`.`t_user`")) {
    System.out.println(user);
}

运行输出。

查询所有数据：
User{id=31, name='Cheng Xiuying', password='YwdkRuGAP3', realname='Cheng Xiuying', gender='F', tel='zgiwptQoj7'}
User{id=32, name='Yuan Yuning', password='4qLhnGadxZ', realname='Yuan Yuning', gender='M', tel='AEPAQckx66'}
User{id=33, name='Ng Wing Sze', password='WaZDq0V506', realname='Ng Wing Sze', gender='F', tel='UqagFETdvC'}

插入用户张三：
插入了1条数据
User{id=31, name='Cheng Xiuying', password='YwdkRuGAP3', realname='Cheng Xiuying', gender='F', tel='zgiwptQoj7'}
User{id=32, name='Yuan Yuning', password='4qLhnGadxZ', realname='Yuan Yuning', gender='M', tel='AEPAQckx66'}
User{id=33, name='Ng Wing Sze', password='WaZDq0V506', realname='Ng Wing Sze', gender='F', tel='UqagFETdvC'}
User{id=49, name='张三', password='root', realname='null', gender='null', tel='null'}

修改张三的用户名为 raingray：
更新了1条数据
User{id=31, name='Cheng Xiuying', password='YwdkRuGAP3', realname='Cheng Xiuying', gender='F', tel='zgiwptQoj7'}
User{id=32, name='Yuan Yuning', password='4qLhnGadxZ', realname='Yuan Yuning', gender='M', tel='AEPAQckx66'}
User{id=33, name='Ng Wing Sze', password='WaZDq0V506', realname='Ng Wing Sze', gender='F', tel='UqagFETdvC'}
User{id=49, name='raingray', password='root', realname='null', gender='null', tel='null'}

删除 raingray 用户：
删除了1条数据
User{id=31, name='Cheng Xiuying', password='YwdkRuGAP3', realname='Cheng Xiuying', gender='F', tel='zgiwptQoj7'}
User{id=32, name='Yuan Yuning', password='4qLhnGadxZ', realname='Yuan Yuning', gender='M', tel='AEPAQckx66'}
User{id=33, name='Ng Wing Sze', password='WaZDq0V506', realname='Ng Wing Sze', gender='F', tel='UqagFETdvC'}

5 连接池

传统 JDBC 连接有两个缺点：

1. 连接过多，一段时间内连接建立过多，数据库也会崩。
2. 连接过慢，建立连接本质上就是 Socks 也是需要时间的。

通过连接池可以解决此问题，连接池会预先创建一堆数据库连接，要用就调，不用就归还。因为池里最大连接数量有限，所有连接已经被程序用完，还想获取那得排队等待其他程序归还才能获取。

连接池由 javax.sql.DataSource 接口定义好规范，具体实现有很多第三方依赖已经做好了，比如 Druid、HiKariCP，在 Spring 和 Hibernate 中使用的 c3p0。

在数据库连接池中 close() 方法不是关闭数据库连接，而是把连接池设置为空闲状态。

连接池必须要设置的几个参数：

• 初始化连接数量。默认有几个连接可供使用
• 连接最大数量。默认连接数量用完后，还有程序要取连接，连接池就会新建连接，但是不会超过指定最大数量
• 最长等待时间。所有连接都被占用，这时候程序再向连接池取连接就会让等待，这里设置等待时间怕死循环，超过时间就抛异常

这里已 druid 为例，先创建 druid.properties 一会儿用来连接。对应的配置选项含义在 Wiki 中有中文解释。

driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/mysql?characterEncoding=UTF-8&autoReconnect=true&allowMultiQueries=true&serverTimezone=GMT%2B8
username=root
password=
initialSize=50
minIdle=10
maxActive=100
maxWait=10000
timeBetweenEvictionRunsMillis=60000
minEvictableIdleTimeMillis=300000

直接改造 jdbcUtilWithDruid.java

package ToolUtils;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class jdbcUtilWithDruid  {
    private static final DataSource dataSource;

    /**
     * 通过配置文件创建连接池
     */
    static {
        Properties properties = new Properties();
        try {
            // 方式一：读取应用根目录下的 druid.properties 的 InputStream 流
            InputStream conf =Thread.currentThread().getContextClassLoader().getResourceAsStream("druid.properties");
            // 方式二：读取应用根目录下的 druid.properties
            // String confPath = Thread.currentThread().getContextClassLoader().getResource("druid.properties").getPath();
            // FileInputStream conf = new FileInputStream(confPath);
            // 方式三：写绝对路径
            // FileInputStream conf = new FileInputStream("D:\\raingray\\Learn\\JavaWebProject\\LearnJDBC\\src\\druid.properties");

            properties.load(conf);
            dataSource = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 工具类不需要实例化
     */
    private jdbcUtilWithDruid() {}

    /**
     * 获取连接池数据库连接
     * @return 返回 Connection 连接对象
     * @throws SQLException
     */
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }

    /**
     * 向连接池归还连接
     * @throws SQLException
     */
    public static void releaseResource() {
        try {
            getConnection().close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}

在使用上没有任何区别。

import ToolUtils.jdbcUtilWithDruid;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * 使用封装 Druid 的工具类执行 SQL
 */
public class jdbc08 {
    public static void main(String[] args) {
        try {
            // 2.建立数据库连接
            Connection conn = jdbcUtilWithDruid.getConnection();
            // 3.获取数据库对象
            Statement statement = conn.createStatement();
            // 4.执行 DQL 语句，获取结果集
            ResultSet resultSet = statement.executeQuery("SELECT Host, User, authentication_string auth FROM user");

            // 5.处理结果集
            while (resultSet.next()) {
                System.out.println(resultSet.getString("Host"));
                System.out.println(resultSet.getString("User"));
                System.out.println(resultSet.getString("auth"));
                System.out.println();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            jdbcUtilWithDruid.releaseResource();
        }
    }
}