Java JDBC使用详解

在 Java 各类 ORM 框架之中，JDBC 可谓万物之母，或许你无需自己手动编写 JDBC 相关代码，但学习它的工作方式一定能让对其它基于它之上的框架的有进一步的了解。

下面手把手带你摸透 JDBC 的前世今生

一、基础介绍

1. 连接创建

通过 JDBC 创建数据库连接比较简单，使用 DriverManager.getConnection() 方法获取即可。

其中通过 Class.forName() 用于指定驱动主类，在当下流行的 Maven 工程中这一步通常可省略。

public class ConnectionUtil {

    public static Connection getConnection() {
        try {
            String url = "jdbc:oracle:thin:@//192.168.0.20:1521/helowin";
            String username = "budai";
            String password = "123456";
            // 指定驱动主类
            Class.forName("oracle.jdbc.driver.OracleDriver")
            // 获取连接
            return DriverManager.getConnection(url, username, password);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}

这里介绍几类常用数据库的 JDBC 连接串以供参考：

• MySQL

  URL: jdbc:mysql://{host}:{port}/{database}。
  ClassName: com.mysql.jdbc.Driver。

• Oracle

  URL: jdbc:oracle:thin:@//{host}:{port}/{database}。
  ClassName: oracle.jdbc.OracleDriver。

• Hive

  URL: jdbc:hive2://{host}:{port}/{database}。
  ClassName: org.apache.hive.jdbc.HiveDriver。

• Postgres

  URL: jdbc:postgresql://{Endpoint}:{port}/{project_name}。
  ClassName: org.postgresql.Driver。

2. 动态驱动

上述的示例为通过 Maven 工程依赖从而实现自动化的驱动加载，而通过 DriverManager 与 ClassLoader 类加载器实现动态的驱动加载，若需要加载外部文件或网络资源驱动文件，可通 URLClassLoader 加载器实现。

当实现自定义动态驱动加载时需要注意一点，即同一个工程并不允许同时加载同一驱动的不同版本，当需要加载相应驱动的不同版本时需要先卸载掉之前加载的驱动，否则由于双亲委派的机制即便再次加载仍读取旧版本的驱动依赖。

public class ConnectionUtil {

     public Connection getConnection(DbEntity dbEntity) {
        String url = "jdbc:oracle:thin:@//192.168.0.20:1521/helowin";
        String userName = "budai";
        String password = "123456";
        String driverClass = "oracle.jdbc.driver.OracleDriver"
        try {
            // Load driver class and register
            // Normal is maven job, but we can use "URLClassLoader" to dynamic load
            ClassLoader classLoader = Thread.currentThread().getContextClassLoader();
            Class<?> aClass = classLoader.loadClass(driverClass);
            DriverManager.registerDriver((java.sql.Driver) aClass.newInstance());
            // Get connection
            return DriverManager.getConnection(url, userName, password);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
}

在实现类的卸载时， JDK 中并没有提供显式的类卸载方式，一个类被卸载只能通过 GC 回收的方式，而一个类想要被垃圾回收需要满足下述三个条件：

• 对象不存在任何实例对象（new 实例对象为空）；
• 对象不存在任何引用关系（GC root 引用为空）；
• 对象的加载器已经被销毁（ClassLoader 为空）；

因此在实现动态驱动加载的时一定要注意创建的对象资源是否正常的关闭，并通过 classLoader = null 或 URLClassLoader 中的 close() 方法销毁类加载器从而保证能够正常的卸载。

3. 语句执行

通过 Statement 对象用于 SQL 语句的执行，具体介绍信息如下。

除了基本的 Statement 之外， java.sql 包下扩展了一系列针对不同常见的语句执行类。

类名	描述
Statement	最基础的语句执行。
PreparedStatement	提供预编的方式语句执行，安全性相对更高。
CallableStatement	作用效果类似 PreparedStatement，但其可以调用存储过程。

Statement 的执行方式包含以下三种，不同方式的区别参考下表：

方法	描述
execute()	可用于执行任意 SQL ，如常见的增删改查，返回集为 ResultSet。
executeQuery()	仅用于执行查询操作，返回集为 ResultSet 。
executeUpdate()	仅用于执行修改操作，返回所影响的行数。

默认情况下 Statement 对象执行语句后自动立即提交 commit，但在某些业务场景下为了降低与数据库交互次数通常需要关闭自动提交，从而实现批次手动提交提高效率。

public void demo(){
    Connection conn = null;
    Statement stmt = null;
    try {
        conn = ConnectionUtil.getConnection();
        // 关闭自动提交
        conn.setAutoCommit(false);
        // 创建会话
        stmt = conn.createStatement();
        String sql = "insert into tb_user values(123, 'alex', 'male')";
        for(int i = 0; i < 5; i++) {
            stmt.execute(sql);
        }
        // 手动提交
        conn.commit()
    } catch(Exception e) {
        e.printStackTrace();
    } finally {
        // 略去资源关闭操作
    }
}

4. 结果查询

ResultSet 用于读取 Statement 执行之后的结果集，通过 next() 以游标的方式遍历结果集中的每一行记录。通过 getXXX() 方法读取当前游标行的记录列，如 getString("name") 即获取当前游标行记录中列为 name 的值并以 String 方式返回。当然也可以通过下标方式访问，如 getString(1) 即获取第一列的值并以 String 方式返回。

下面是一个虚拟表查询示例，其中将 Connection 等资源声明在 try() 中当代码执行完 try{} 语句时会自动进行资源关闭，省去手动添加 finally 关闭资源。

public void demo(){
    String sql = "select 'alex' as name from dual";
    try (
        // 获取连接
        Connection con = ConnectionUtil.getConnection();
        // 建立会话
        Statement stmt = con.createStatement();
        // 执行查询
        ResultSet rs = stmt.executeQuery(sql);
    ) {
        while(rs.next()) {
            System.out.println(rs.getString("name"));
        }
    } catch(Exception e) {
        e.printStackTrace();
    }
}

5. 语句预编

在上面的查询案例中我们并没有添加条件，但在更多的业务场景下是需要根据条件进行筛选。但如果采用字符串拼接的方式，极为可能遭到 SQL 注入攻击。

如下两种情况下，第一种情况下没有任何问题，根据获取的 ID 和密码查询用户信息。但在第二种情况下，获取的 ID 中恶意拼接了恒等导致密码验证无效，无论输入任何密码结果都为真。

// 情况 1
String id1 = "123";
String pwd1 = "123456";
String sql1 = "select * from tb_user where id = " + id1 + " and password = " + pwd1;

// 情况 2
String id2 = "123 and 1=1";
String pwd2 = "123456";
String sql2 = "select * from tb_user where id = " + id2 + " and password = " + pwd2;

为了防止上述情况， Java 通过 PreparedStatement 提供了预编机制，引入占位符 ? 防止恶意 SQL 注入攻击。

即将语句中待输入的字符串通过 ? 进行占位，后续再进行赋值，而后面无论用户输入什么值，都会将输入当成属性值而不会编译为 SQL 语句。

String sql3 = "select * from tb_user where id = ? and pwd = ?;

下面通过具体实现案例加深了解。

public void demo1(){
    // 占位符预编译，提升安全性
    String sql = "select * from tb_user where id = ? and pwd = ?";
    try (
            Connection con = DriverManager.getConnection();
            PreparedStatement ps = con.prepareStatement(sql); 
    ) {
        // 占位符赋值
        ps.setString(1, "123");
        ps.setString(2, "123456");
        try(ResultSet rs = ps.execute()) {
            while(rs.next()) {
                // 结果处理
            }
        }
    } catch(Exception ex) {
        ex.printStackTrace();
    }  
}

二、进阶操作

在之前介绍通过 JDBC 方式实现基本的数据查询，下面即更深入的介绍如何实现高效查询。

1. 查询参数

在 Statement 与 ResultSet 中都提供 fetchSize 参数用于指定单次查询的行数。

当每次通过 executeQuery() 执行查询都将返回一个结果集，若查询结果过大时，一次性加载内存中显然是十分不合理的，通过 fetchSize 即设置每次读取的记录数，然后分批进行读取从而降低内存负载。

简而言之，若 fetchSize 越小，加载进内存的数据则越少，但与数据库服务之间的通讯频次则越高，反之则内存压力大而服务器压力小，需要根据具体的业务场景而定。

在 MySQL 中默认是一次性将数据读入内存，而在 Oracle 中默认的 fetchSize 则为 10 。

(1) 区别

既然 Statement 与 ResultSet 都提供了 fetchSize 参数，那二者又有何区别？

因为 ResultSet 是由 Statement 执行而来，所以 ResultSet 将会继承 Statement 中配置的 fetchSize 。这样的设计的一大目的是为了通过 Statement 给所有结果集指定 fetchSize ，但是当存在不同的结果集可根据场景实现覆盖。

try (
        Connection con = DriverManager.getConnection();
        Statement stmt = con.createStatement(); 
) {
    stmt.setFetchSize(50);
    try(ResultSet rs1 = stmt.executeQuery(sql1)) {
        // 覆盖 fetchSize 为 40
        rs1.setFetchSize(40)
        while(rs1.next()) {
            // 结果处理
        }
    }
    try(ResultSet rs2 = stmt.executeQuery(sql2)) {
        // 覆盖 fetchSize 为 45
        rs2.setFetchSize(45)
        while(rs2.next()) {
            // 结果处理
        }
    }
} catch(Exception ex) {
    ex.printStackTrace();
}  

2. 结果缓存

通过 ResultSet 可以读取执行结果，但前提是会话必须一直处于连通状态，即当出现连接波动可能导致程序异常丢失结果。

对此 java.sql 包中提供 CachedRowSet 以缓存的方式的读取数据，即将数据先刷入本地缓存中再实现读取，此时即便连接关闭也可实现数据读取，配合 fetchSize 参数可实现全量数据的本地缓存读取，当然此种方式对内存压力相对较高。

CachedRowSet 的具体使用示例如下：

try (
        Connection con = dataSource.getConnection();
        Statement stmt = con.createStatement();
) {
        RowSetFactory factory = RowSetProvider.newFactory();
        try (
                ResultSet rs = stmt.executeQuery(sql);
                CachedRowSet rowSet = factory.createCachedRowSet();
        ) {
            // 结果集刷入缓存
            rowSet.populate(rs);
            // 即便提前关闭也不影响后续读取
            rowSet.close();
            while (rowSet.next()) {
                // 后续从 rowSet 读取即可，操作同 rs 读取
            }
        }
    } catch (Exception ex) {
        ex.printStackTrace();
}

三、连接池

在项目工程中，通常会频繁的与数据库建立连接，而如果每次都重复的进行创建关闭显然十分消耗服务资源。因此为了提供更高的性能 Java 引入了连接池功能，与线程池类似，都是通过池化技术实现更有效的性能管理。

下面就具体介绍如何自定义创建连接池，这里以 Apache 的 BasicDataSource 为例，当然还有其它如阿里巴巴的 DruidDataSource ，不同连接池实现流程类似，这里不一一介绍。

1. 依赖导入

使用 BasicDataSource 连接池需要导入相关的 Maven 依赖，依赖配置如下：

<dependency>
    <groupId>org.apache.commons</groupId>
    <artifactId>commons-dbcp2</artifactId>
    <version>2.9.0</version>
</dependency>

2. 连接池

新建 BasicPoolUtil 类用于配置连接池信息，连接池各项具体参数配置如下：

参数	描述
setUrl()	数据库 JDBC url 连接串。
setUsername()	数据库用户名。
setPassword()	数据库用户密码。
setClassName()	数据库 Class Name。
setClassLoad()	指定数据库 Class Name 的加载器。
InitialSize()	初始化连接池大小。
setMaxIdle()	最大空闲连接数，超过最大值时获取连接将根据 MaxWait 进行等待，若仍无空闲连接则抛出异常，设置为 -1 表示无限制。
setMaxTotal()	最大数据库连接数，即连接池保留的最少连接，设置为 -1 表示无限制。
setMaxWaitMillis()	最大建立连接等待时间，设置为 -1 表示无限制。
setMinEvictableIdleTimeMillis()	超过 MaxIdle 的连接最长存活时间，默认 30m。
setTimeBetweenEvictionRunsMillis()	空闲连接清理线程运行间隔周期，默认 60s。

public class BasicPoolUtil {

    public static Datasource buildDatasource() throws Exception {
        BasicDataSource dataSource = new BasicDataSource();
        // 连接 URL
        dataSource.setUrl("jdbc:mysql://192.168.0.20:3306/test_db");
        // 驱动类
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        // 用户名
        dataSource.setUsername("root");
        // 用户密码
        dataSource.setPassword("123456");
        dataSource.setInitialSize(4);
        dataSource.setMaxTotal(8);
        dataSource.setMaxIdle(4);
        dataSource.setMaxWaitMillis(10000);
        dataSource.setMinEvictableIdleTimeMillis(TimeUnit.SECONDS.toMillis(30L));
        dataSource.setTimeBetweenEvictionRunsMillis(TimeUnit.SECONDS.toMillis(60L));
        return dataSource;
    }
}

3. 类加载器

在上面的参数列表中提到了通过设置 setClassLoad() 参数可以指定数据库的主类加载方式，通过该方式即可实现将数据库的版本隔离管理，无需在工程 Maven 逐一引入各个数据的不同版本，而是通过外部驱动包实现动态管理。

如下示例即利用 URLClassLoader 实现驱动的主类加载，实现数据库的隔离管理。

public static ClassLoader getClassLoader(String driverPath) {
    // 获取当前线程上下文加载器
    ClassLoader parent = Thread.currentThread().getContextClassLoader();
    URL[] urls;
    try {
        File driver = new File(driverPath);
        // 驱动包不存在抛出异常
        if (!driver.exists()) {
            throw new FileNotFoundException();
        }
        // File 转 URL 资源格式
        list.add(driver.toURI().toURL());
        urls = list.toArray(new URL[0]);
    } catch (Exception e) {
        throw new RuntimeException(e);
    }
    return new URLClassLoader(urls, parent);
}

4. 基本使用

通过连接池的方式唯一的区别是获取连接时是通过 datasource.getConnection() 方式获取，当完成对应操作关闭时是将连接返回到连接池中进行统一管理，而不是真正意义上的连接关闭。

当连接池中活动的连接数低于 MaxIdle 时，即使我们关闭连接也只是将连接放回连接池中，连接真正意义上还是处于激活状态，待下次获取连接时则无需重新创建连接，通过池化技术从而省去大量的连接创建资源消耗。

连接池基本使用示例如下：

@Test
public void demo() {
    // 获取连接池
    Datasource datasource = BasicPoolUtil.buildDatasource(connInfo);
    try (
            Connection conn = datasource.getConnection();
            Statement stmt = conn.createStatement();
    ) {         
        String sql = "drop table tb_user";       
        stmt.execute(sql);
    } catch (Exception ex) {
        ex.printStackTrace();
    }
}

四、元数据信息

1. 表信息

通过 DatabaseMetaData 获取关系型数据库表结构信息，如 库名、 表名、 备注 等信息。

public void TableDemo() {
    String schema = "budai";
    // 读取表与视图
    String[] queryType = new String[]{"TABLE", "VIEW"};

    List<Map<String, String>> tableInfoMap = new ArrayList<>();
    try (Connection connection = ConnectionUtil.getConnection()) {
        DatabaseMetaData metaData = connection.getMetaData();
        ResultSet rs = metaData.getTables(null, schema, null, queryType);

        while (rs.next()) {
            Map<String, String> table = new HashMap<>();
            table.put("TableSchema", rs.getString("TABLE_SCHEM"));
            table.put("TableName", rs.getString("TABLE_NAME"));
            table.put("TableType", rs.getString("TABLE_TYPE"));
            table.put("TableCatalog", rs.getString("TABLE_CAT"));
            table.put("Remarks", rs.getString("REMARKS"));
            tableInfoMap.add(table);
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
    System.out.println(tableDTO);
}

2. 表结构

通过 DatabaseMetaData 获取关系型数据库表结构信息，如 字段名、 字段类型、 字段长度 等信息。

public void columnsInfo() throws SQLException {
    String schema = "BUDAI";
    String tableName = "TB_22091901";

    List<Map<String, String>> tableInfoMap = new ArrayList<>();
    try (Connection conn = ConnectionUtil.getConnection()) {
        try {
            // 获取主键列表
            List<String> primaryKeyList = new ArrayList<>();
            DatabaseMetaData metaData = conn.getMetaData();
            ResultSet primaryKeyRs = metaData.getPrimaryKeys(null, schema, tableName);
            while (primaryKeyRs.next()) {
                String name = primaryKeyRs.getString("COLUMN_NAME");
                if (!primaryKeyList.contains(name)) {
                    primaryKeyList.add(name);
                }
            }

            // 获取字段描述信息
            ResultSet columnRs = metaData.getColumns(null, schema, tableName, null);
            while (columnRs.next()) {
                Map<String, String> column = new LinkedHashMap<>();
                String columnName = columnRs.getString("COLUMN_NAME");
                columnVo.put("ColumnName", columnName);
                Integer isPrimaryKey = primaryKeyList.contains(columnName) ? 1 : 0;
                column.put("IsPrimaryKey", isPrimaryKey == 1 ? "yes" : "no");
                column.put("DataType", columnRs.getString("DATA_TYPE"));
                column.put("TypeName", columnRs.getString("TYPE_NAME"));
                column.put("ColumnSize", columnRs.getString("COLUMN_SIZE"));
                column.put("DecimalDigits", columnRs.getString("DECIMAL_DIGITS"));                    
                column.put("Nullable", columnRs.getString("NULLABLE"));
                column.put("IsNullable", columnRs.getString("IS_NULLABLE"));
                column.put("Remarks", columnRs.getString("REMARKS"));
                column.put("ColumnDef", columnRs.getString("COLUMN_DEF"));
                column.put("NumPrecRadix", columnRs.getString("NUM_PREC_RADIX"));
                column.put("CharOctetLength", columnRs.getString("CHAR_OCTET_LENGTH"));
                column.put("OrdinalPosition", String.valueOf(columnRs.getInt("ORDINAL_POSITION")));
                tableInfoMap.add(column);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    System.out.println(tableMap);
}