JDBC基础整理（快速入门、自定义连接池）

JDBC是什么

Java数据库连接（Java DataBase Connectivity）

是Java提供的一套用于连接、操作数据库的一套接口规范，即使用Java语言来操作数据库

为什么需要有JDBC

就像车子跑起来需要发动机的驱动，显卡等电脑硬件想运行起来需要显卡驱动，使用Java语言操作相应的数据库也需要相应的驱动

但存在一个问题，数据库的种类非常繁多，会导致相应的数据库驱动也很繁多，不同数据库的API又会存在很大的差别
为了操作不同数据库而去学习不同数据库的API，对于开发人员而言，无疑增加了很多不必要的学习成本

好在Java官方提供了JDBC接口规范，数据库厂商需要实现该接口来编写各自的数据库驱动
这样对于开发人员而言只需要熟悉JDBC API就能操作各类厂商的数据库了，即面向接口编程

图片来自于百度

JDBC的组成

JDBC主要由java.sql以及javax.sql两个包组成
可以先简单了解以下对象，更多详细的描述可以查看JDK API文档
java.sql.DriverManager 驱动的管理类，用于数据库驱动的注册
java.sql.Connection 数据库连接接口，处理客户端与数据库之间的所有交互
java.sql.Statement 语句接口，封装了要向数据库操作的SQL语句信息
java.sql.ResultSet 结果集接口，封装了查询语句返回的结果信息

JDBC快速入门

以操作MySQL数据库为例，先来看一段最简单的JDBC操作数据库的代码，然后再根据代码了解相应的步骤

// 注册驱动
Class.forName("com.mysql.cj.jdbc.Driver");
// 建立连接
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mysql?characterEncoding=UTF-8", "root", "123456");
// 创建语句
Statement statement = connection.createStatement();
// 执行查询
ResultSet resultSet = statement.executeQuery("select * from user");
// 遍历结果
while(resultSet.next()) {
    String host = resultSet.getString("Host");
    String user = resultSet.getString("User");
    System.out.println(host + ":" + user);
}
// 释放资源
resultSet.close();
statement.close();
connection.close();

控制台打印的查询结果

localhost:root
localhost:mysql.session
localhost:mysql.sys
%:root

JDBC操作数据库的步骤

1. 注册驱动

Maven引入驱动依赖

上述代码既然是以MySQL数据库为例，自然需要在项目中引入MySQL驱动的Maven构件，当前最新版本为8.0.12
为了方便写测试代码，引入Junit的Maven构件，当前最新版本为4.12

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>LATEST</version>
</dependency>
<dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>LATEST</version>
</dependency>

com.mysql.jdbc.Driver与com.mysql.cj.jdbc.Driver之间的区别

为了操作数据库，需要先获取到实现了java.sql.Driver接口的驱动
MySQL驱动包提供了com.mysql.jdbc.Driver、com.mysql.cj.jdbc.Driver两个数据库驱动类

com.mysql.jdbc.Driver是5.x版本的驱动中的实现类，已经过时
com.mysql.cj.jdbc.Driver是6.x及以上版本的驱动中的实现类

com.mysql.jdbc.Driver代码的静态代码块中描述了该驱动实现类已经过时

static {
    System.err.println("Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. "
            + "The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.");
}

DriverManager方法注册驱动

有了数据库厂商提供的驱动后，要先在程序中注册加载驱动
java.sql.DrvierManager提供了注册驱动的方法

public static void registerDriver(java.sql.Driver driver)
public static void registerDriver(java.sql.Driver driver, DriverAction da)

注册驱动需要传入java.sql.Driver接口的实现，传入com.mysql.jdbc.Driver驱动进行注册

DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver())

Class.forName反射注册驱动

但通过对com.mysql.cj.jdbc.Driver代码的查看，发现类中静态代码块已经对当前驱动进行了注册，会造成二次注册
所以开发人员不需要手动去注册MySQL驱动，只要让JVM加载com.mysql.cj.jdbc.Driver或其子类即可完成驱动的注册

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

那最常用的方式是通过反射直接加载目标Class

Class.forName("com.mysql.cj.jdbc.Driver")

且这种方式不同于传入对象的硬编码，字符串的方式在更换数据库的时候也更方便

2. 建立连接

加载并注册完驱动后就可以建立起连接来实现对数据库的访问了
java.sql.DriverManager提供了三种方法获取Connection对象，客户端与数据库的所有交互都通过该对象完成

public static Connection getConnection(String url, java.util.Properties info)
public static Connection getConnection(String url, String user, String password)
public static Connection getConnection(String url)

最常用的方式是通过URL以及数据库账号密码来获取连接，现在连接本地MySQL下名为mysql的数据库并设置编码属性为UTF-8

DriverManager.getConnection("jdbc:mysql://localhost:3306/mysql?characterEncoding=UTF-8", "root", "123456")

URL连接的组成与写法

MySQL写法：jdbc:mysql://localhost:3306/databaseName
MySQL本地默认端口号简写：jdbc:mysql:///databaseName
Oracle写法：jdbc:oracle:thin:@localhost:1521:databaseName

主要由jdbc协议、相应数据库的子协议以及主机名、端口号和数据库名称构成
其中thin为oracle数据库的一种驱动方式，对应的还有oci方式

3. 操作数据

连接到本地的MySQL数据库后开始对数据库进行操作
操作数据库要通过SQL语句来操作，首先要通过connection对象创建语句对象来实现对数据库的SQL操作

Statement statement = connection.createStatement();

Statement接口提供了execute、executeQuery、executeUpate、addBatch、executeBatch等方法来执行SQL

• execute：执行任意的SQL语句
• executeQuery：执行select语句
• executeUpdate：执行insert、update、delete或SQL DDL等语句
• addBatch：添加多条SQL语句到批处理中
• clearBatch：清除批量SQL语句
• executeBatch：批量执行SQL语句

查询的结果封装在ResultSet对象中，即存放了结果对象的一个容器
ResultSet对象提供了next方法来移动容器中的指针，类似于集合中迭代器的hasNext方法
通过循环判断就可以遍历拿到每个结果对象的值

ResultSet resultSet = statement.executeQuery("select * from user");
while(resultSet.next()) {
    String host = resultSet.getString("Host");
    String user = resultSet.getString("User");
    System.out.println(host + ":" + user);
}

数据库字段类型与JDBC方法对应表

数据库字段有不同的类型，ResultSet对象可以通过不同的方法获取不同类型的数据

MySQL字段类型	ResultSet对应方法	方法返回类型
BIT(1)	getBoolean(String)	boolean
BIT	getBytes(String)	byte[]
TINYINT	getByte(String)	byte
SMALLINT	getShort(String)	short
INT	getInt(String)	int
BIGINT	getLong(String)	long
CHAR、 VARCHAR	getString(String)	java.lang.String
TEXT、BLOB	getClob(String) getBlob(String)	java.sql.Clob java.sql.Blob
DATE	getDate(String)	java.sql.Date
TIME	getTime(String)	java.sql.Time
TIMESTAMP	getTimestamp(String)	java.sql.Timestamp

ResultSet还提供了其他很多方式来获取字段的值
例如getObject(int index)、getObject(String columnName)分别根据字段位置和字段名称来获取任意类型的数据
更多相关的方法可以查看JDK API找到相应的类了解

4. 释放资源

操作完数据库后需要释放资源，依次断开对数据库的操作和连接

传统close方法手动释放资源

传统的close方式必须要在finally中编写确保资源一定会被释放
但代码相对而言比较重复繁琐

@Test
public void closeResource() {
    Connection connection = null;
    Statement statement = null;
    ResultSet resultSet = null;
    try {
        // 省略部分代码
        // ......
    } catch (ClassNotFoundException | SQLException e) {
        e.printStackTrace();
    } finally {
        // 释放资源
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            resultSet = null;
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            statement = null;
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            connection = null;
        }
    }
}

AutoCloseable自动释放资源

JDK7开始提供了AutoCloseable接口，该接口的主要功能是帮助开发人员自动释放资源
ResultSet、Statement、Connection接口都继承了AutoCloseable接口
使用AutoCloseable接口管理资源需要使用JDK7的try-catch-resources语法
创建的资源在退出`try-block代码块时会自动调用该资源的close方法，释放的顺序为先创建后释放

@Test
public void autoCloseable() {
    // 省略部分代码
    // ......
    try {
        Class.forName(driverClass);
        // try-catch-resources语法的try-block代码块
        try (Connection connection = DriverManager.getConnection(url, username, password);
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery(sql)) {
            while(resultSet.next()) {
                String host = resultSet.getString("Host");
                String user = resultSet.getString("User");
                System.out.println(host + ":" + user);
            }
        }
    } catch (ClassNotFoundException | SQLException e) {
        e.printStackTrace();
    }
}

为什么要释放资源

数据库是部署在服务器上的，服务器有着相应的硬件配置
程序对数据库的操作与连接都会占用服务器的CPU、内存等硬件资源
当程序处于空闲状态，对数据库没有任何操作时，应及时释放资源好让数据库能分配其他程序资源
资源的过多占用可能会导致服务器宕机停止工作而导致严重的后果
说的直白一点就是不要上完了厕所还要一直霸占着坑位

JDBC工具类封装

通过上述代码的流程，可以发现每次使用JDBC操作数据库都要先注册驱动、建立连接然后再操作数据库、最后释放资源
其中注册驱动、建立连接以及释放资源都是重复的，可以封装一个工具类来消除这种重复的编码操作
使开发人员只需要关注SQL的编写以及对结果的处理

public class JDBCUtil {

    private static final String DRIVERCLASS;
    private static final String URL;
    private static final String USERNAME;
    private static final String PASSWORD;

    static{
        Properties pro = new Properties();
        // 通过ClassLoader类加载器从classpath路径下加载配置了数据库信息的属性文件
        InputStream in = JDBCUtil.class.getClassLoader().getResourceAsStream("db.properties");
        try {
            pro.load(in);
        } catch (IOException e) {
            e.printStackTrace();
        }
        DRIVERCLASS = pro.getProperty("driverClass");
        URL = pro.getProperty("url");
        USERNAME = pro.getProperty("username");
        PASSWORD = pro.getProperty("password");
    }
    
    // 注册驱动
    private static void loadDriver(){
        try {
            Class.forName(DRIVERCLASS);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    
    // 获取连接
    public static Connection getConnection(){
        // 加载驱动
        loadDriver();
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(URL, USERNAME , PASSWORD);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }
}

在代码中直接编写数据库配置信息的硬编码方式显然是不利于维护与修改的
所以将配置信息编写在classpath路径下的db.properties文件中
再通过类加载器读取文件获取文件中的键值对，完成驱动的注册与连接的建立，资源的释放则通过try-catch-resources实现

@Test
public void jdbcUtil() {
    String sql = "select * from user";
    try (Connection connection = JDBCUtil.getConnection();
         Statement statement = connection.createStatement();
         ResultSet resultSet = statement.executeQuery(sql)) {
        while(resultSet.next()) {
            String host = resultSet.getString("Host");
            String user = resultSet.getString("User");
            System.out.println(host + ":" + user);
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

结合封装的JDBC工具类之前的代码能得到这样进一步的精简

为什么需要数据库连接池

JDBCUtil存在的问题：
每次访问数据库都要重新建立连接，而建立连接是非常耗时的，当程序频繁访问数据库会造成程序性能的下降

解决的方案：
在一个容器中初始化一定数量的数据库连接，程序每次访问数据库直接从容器中获取到连接，执行完对数据库的操作后再把连接归还到容器中，这个容器便称之为数据库连接池

自定义一个最简单的连接池

Java官方提供了数据库连接池的接口规范，实现javax.sql.DataSource接口来编写连接池

public class DataSourceUtil implements DataSource {

    /**
     * 存放连接的容器
     */
    private List<Connection> connectionPool = new ArrayList<>();
    
    public DataSourceUtil() {
        addConnection();
    }

    /**
     * 初始化连接池
     */
    private void addConnection() {
        // 初始化10个连接
        for (int i = 0; i < 10; i++) {
            connectionPool.add(JDBCUtil.getConnection());
        }
    }

    /**
     * 从连接池中获取连接
     * @return 连接对象
     */
    @Override
    public Connection getConnection() {
        // 如果连接池空了，对连接池进行扩容
        if (connectionPool.isEmpty()) {
            addConnection();
        }
        return connectionPool.remove(0);
    }

    /**
     * 归还连接到连接池中
     * @param connection 连接对象
     */
    public void closeConnection(Connection connection) {
        connectionPool.add(connection);
    }
    
    // ......省略部分代码
}

使用该连接池进行数据库操作测试

@Test
public void dataSourceUtil() {
    String sql = "select * from user";
    DataSourceUtil dataSourceUtil = new DataSourceUtil();
    Connection connection = dataSourceUtil.getConnection();
    try (Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery(sql)) {
        while(resultSet.next()) {
            String host = resultSet.getString("Host");
            String user = resultSet.getString("User");
            System.out.println(host + ":" + user);
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        // 使用DataSourceUtil手动归还连接
        dataSourceUtil.closeConnection(connection);
    }
}

自己写的数据源毕竟是比较简单的，并不会涉及到方方面面的问题
好在有各种强大的开源的连接池可以供平时开发时使用，例如hikari、dbcp、c3p0等常用数据源

关于滚动结果集

使用JDBC查询数据库会返回ResultSet对象，默认通过Statement createStatement()方法创建执行返回的结果集是只能向下滚动且只读的
使用Statement createStatement(int resultSetType, int resultSetConcurrency)来指定结果集的类型以及策略

常用结果集类型
resultSetType
    TYPE_FORWARD_ONLY          结果集只能向下
    TYPE_SCROLL_INSENSITIVE    可以滚动，不能修改记录
    TYPE_SCROLL_SENSITIVE      可以滚动，可以修改记录
    
常用结果集并发策略
resultSetConcurrency
    CONCUR_READ_ONLY           只读，不能修改
    CONCUR_UPDATABLE           结果集可以修改

另外ResultSet还提供了很多的方法来对结果集内的指针进行操作

next()                         移动到下一行
previous()                     移动到前一行
absolute(int row)              移动到指定行
beforeFirst()                  移动到resultSet最前面
afterLast()                    移动到resultSet最后面
updateRow()                    更新行数据

编写测试例子查询结果集中第四行的数据

@Test
public void resultSet() {
    String sql = "select * from user";
    try (Connection connection = JDBCUtil.getConnection();
         Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
         ResultSet resultSet = statement.executeQuery(sql)) {
        resultSet.absolute(4);
        String host = resultSet.getString("Host");
        String user = resultSet.getString("User");
        System.out.println(host + ":" + user);
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

执行结果

%:root

关于SQL注入

使用Statement对象执行SQL语句是直接采用拼接字符串的方式，会导致SQL注入的危害
例如登录校验用户是否存在的简单SQL注入，SQL如下

String sql = "select * from user where username = ' + username + ' and password = ' + password + ' ";

如果用户传入的username 为xxx ' or ' 1 = 1，SQL将会变成

String sql = "select * from user where username = 'xxx' or '1 = 1' and password = ''";

这样导致表达式username = 'xxx' or '1 = 1'结果总是为true，所以不管密码填什么都无所谓了
执行这样的SQL将导致用户并没有输入正确的账号密码却通过了验证进入到了系统

使用PreparedStatement防止SQL注入

Statement会使数据库频繁编译SQL，可能造成数据库缓冲区溢出
PreparedStatement对象支持SQL预编译，还能通过占位符来管理变量从而防止SQL注入
此时username再传入xxx ' or ' 1 = 1，程序会将其当做整体，在数据库查找username为"xxx ' or ' 1 = 1"的用户

String sql = "select * from user where username = ? and password = ?";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
// 设置第一个、第二个占位符的值
preparedStatement.setString(1, user.getUsername());
preparedStatement.setString(2, user.getPassword());