Java漫游笔记-12-数据库编程

JDBC 架构

数据库是独立于我们开发的应用程序的一个软件，应用程序必须通过特定的通信协议才能与数据库进行沟通。
由于数据库产品众多，并且它们所使用的协议也各不相同。想要通过扩展 Java 语言，去适配每一个数据库，显然是不可能完成的任务。即使可以做得到，这种做法也是异常脆弱的，因为一旦数据库厂商对协议进行修改，那么 Java 语言也将面临修改。
为了解决这个问题，需要对应用程序和数据库软件进行解耦。而解耦最简单的方法，当然就是在这二者之间再加一层。这一层就是 JDBC 。
JDBC 的设计主要基于这样的思想：首先，Java 提供一套标准的 API 和一个驱动管理器，并允许数据库厂商向驱动管理器注册（按照规范实现的）数据库驱动程序。然后，Java 开发者就可以根据 API 编写应用程序，与驱动管理器进行通信，而驱动管理器再通过驱动程序与实际的数据库进行通信，从而最终实现操作数据库的目的。
这样的好处是：对于开发者而言，不再需要了解特定数据库的 API 。并且，如果需要更换数据库产品，只需要更换相应的数据库驱动即可（当然，这只是理想的情况，实际上，如果在程序中使用了数据库相关的特性，代码还是要做相应的修改）。

JDBC 驱动类型

JDBC 规范将数据库驱动划分为 4 种类型：

• Type 1 driver – JDBC-ODBC bridge ，将 JDBC 翻译成 ODBC ，然后通过 ODBC 驱动与数据库通信。首先需要在客户端安装配置好 ODBC 驱动，另外，因为需要把 JDBC 调用传递给 ODBC 并进行转换，因此性能会受限。
• Type 2 driver – Native-API driver ，使用本地代码调用数据库原生 API （例如：C/C++ 链接库）。执行效率高，但是平台相关。Oracle 提供的 OCI 驱动就是这种类型，使用的时候需要按照 Oracle Client。
• Type 3 driver – Network-Protocol driver (middleware driver) ，将 JDBC 的方法调用转换成特定的网络协议调用，与数据库的中间件服务器或组件通信，中间件服务器再与数据库通信。可以跨平台，如果 JDBC 与中间件服务器的协议是固定的，那么更换数据库对应用程序而言也是透明的。但由于加了一个中介，因此性能也会受限。
• Type 4 driver – Database-Protocol driver (Pure Java driver) ，由数据库厂商直接提供的纯 Java 类库，会把 JDBC 调用直接转换为特定的数据库访问协议。跨平台，效率高。

第 1 种驱动，需要额外配置 ODBC 驱动，并且性能不高，很少使用。
第 2 种驱动，需要安装平台相关的本地代码，也很少使用。
第 3 种驱动，如果一个客户端需要同时连接多个不同类型的数据库，基于架构的弹性，可以考虑使用。
第 4 种驱动，跨平台，性能也不错，最常用。

JDBC 组件

Connection

Connection 是我们操作数据库的基本对象，是与特定数据库进程的一个连接（会话）。
Connection 对象还能够通过 getMetaData() 方法，获取所连接的数据库的元数据，包括关于数据库的表、受支持的 SQL 语法、存储过程等等的信息。

Statement

Statement 对象是 SQL 语句的 Java 表示，简单来说，就是把 SQL 语句发送到数据库并执行。

ResultSet

ResultSet 代表了 SQL 查询结果集中的一行。
在默认情况下，同一时间每个 Statement 对象在只能打开一个 ResultSet 对象。

JDBC 编程步骤

1. 注册驱动
   在 JDBC 4.0 之前，需要手动注册数据库厂商提供的驱动类。
   通常使用 Class.forName() 来显式地加载。
   其原理也比较简单，调用 Class.forName() 会将对应的驱动类加载到内存中，然后执行该类的 static 静态代码块，例如：

   static {
        try {
            // 向 DriverManager 注册给定驱动程序
            DriverManager.registerDriver(new Driver());
        } catch (SQLException E) {
            throw new RuntimeException("Can't register driver!");
        }
   }

   JDBC 4.0 (JDK 1.6 之后) 提供了自动加载驱动的新特性。

   public class DriverManager {
       static {
           // 通过 System.getProperty("jdbc.drivers") 和 ServiceLoader.load(Driver.class) 加载驱动
           loadInitialDrivers();
           println("JDBC DriverManager initialized");
       }
       // some code
   }

   在数据库厂商提供的驱动 jar 包中，我们可以看到它包括了：META-INF/services/java.sql.Driver 文件。此文件指定了 java.sql.Driver 的实现类。

2. 建立连接
   调用 DriverManager.getConnection() 方法可以建立一个数据库连接。
   驱动管理器会遍历所有已注册的驱动，找到一个与给定 URL 相匹配的驱动，然后建立连接，并返回一个 Connection 对象。
   URL 须遵循一定规则，例如，MySQL 数据的 URL 格式为：

   jdbc:mysql://[host][,failoverhost...][:port]/[database][?propertyName1][=propertyValue1][&propertyName2][=propertyValue2]... （有一个细节需要注意：如果 URL 配置在 xml 文件中，& 需要替换成 &）

   这是很原始的方法，实际上，现在已经很少使用 DriverManager 来建立数据库连接。因为建立数据库连接操作是比较昂贵的，如果每次使用连接，都需要重新建立，显然不划算。因此，现在的应用，一般都会维护一个数据库连接池，在应用启动的时候，先建立好若干数据库连接，然后缓存起来，在需要的时候，就拿出来用，用完了，再放回池中，这样来提高连接的使用率，减少开销。具体可以参考 javax.sql.DataSource 的 getConnection() 方法。

3. 构建 Statement 对象
   在获得一个连接后，接下来就是要构建一个 Statement 对象。
   JDBC 提供了三种不同类型的 Statement 对象：
   • Statement ：用于执行不带参数的简单 SQL 。
   • PreparedStatement ：用于执行预编译的 SQL 语句，在创建时就需要指定 SQL ，可以带参数，可以不带参数。由于 PreparedStatement 对象存储的 SQL 语句已经编译，这意味着数据库直接运行它即可，因此 PreparedStatement 的执行效率要高于 Statement 。
     另外，使用预编译的语句，不用担心转义的问题，还可以避免 SQL 注入，既方便又安全。
   • CallableStatement ：用于执行 SQL 存储过程的接口，它是 PreparedStatement 的子类。
4. 执行 SQL
   调用 Statement 对象的相关方法，可以执行 SQL 语句。
   • execute() ：执行任何 SQL 语句。
   • executeQuery() ： 执行 SELECT 语句。
   • executeUpdate() ：执行 DDL、INSERT、UPDATE 或 DELETE 语句。
   • addBatch() & executeBatch() ：批量执行。

5. 结果处理
   有一些 SQL 语句会返回结果，这时候就需要对结果集进行处理。
   JDBC 采用 ResultSet 对象表示查询结果。
   默认的 ResultSet 对象不可更新，仅有一个向前移动的光标，只能从第一行到最后一行迭代一次。
   最初，光标被置于第一行之前。每一次调用 next() 方法会将光标移动到下一行，直至没有下一行时返回 false （这里在使用方法上，和集合框架中的迭代器略不同，它不需要先调用 hasNext() 再调用 next() ），因此，我们通常使用 while 循环来迭代结果集。
   但是，我们也可以生成可滚动和/或可更新的 ResultSet 对象。

   Statement stmt = con.createStatement(
                                      ResultSet.TYPE_SCROLL_INSENSITIVE,
                                      ResultSet.CONCUR_UPDATABLE);

   需要注意的是，并非所有的结果集都是可更新的。在使用 ResultSet 进行数据修改时，必须选取单表和主键，并且，每完成一行的修改，都要马上调用 updateRow() 来完成对数据库的写入。

6. 释放资源
   在操作完数据库之后，应显式地关闭 ResultSet 、Statement 和 Connection 对象，以释放占用的资源。

参考代码：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

public class JDBCTest {

    public static void main(String[] args) {
        final String DRIVER_CLASS_NAME = "com.mysql.jdbc.Driver";
        final String URL = "jdbc:mysql://localhost:3306/codinglike?useUnicode=true&characterEncoding=UTF-8";
        final String USERNAME = "user";
        final String PASSWORD = "pass";
        final String SQL = "SELECT id, username, password, email, create_time FROM user WHERE username = ?";
        Connection connection = null;
        PreparedStatement prepareStatement = null;
        ResultSet resultSet = null;
        try {
            // 加载驱动
            Class.forName(DRIVER_CLASS_NAME);
            // 建立连接
            connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
            // 构建 Statement 对象
            prepareStatement = connection.prepareStatement(SQL);
            // 设置参数
            prepareStatement.setString(1, "franky");
            // 执行 SQL
            resultSet = prepareStatement.executeQuery();
            // 结果处理
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String username = resultSet.getString("username");
                String password = resultSet.getString("password");
                String email = resultSet.getString("email");
                Date createTime = resultSet.getDate("create_time");
                System.out.println("User [id=" + id + ", username=" + username
                        + ", password=" + password + ", email=" + email
                        + ", createTime=" + createTime + "]");
            }
            // 释放资源
            resultSet.close();
            prepareStatement.close();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            // 遍历异常链
            for (Throwable throwable : e) {
                throwable.printStackTrace();
            }
        } finally {
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

JDBC 异常

在前面的代码中，可以看到，很多方法的调用都会抛出 SQLException ，它包含了提供关于数据库操作的错误信息。
我们可以通过调用 getErrorCode() 和 getSQLState() 方法，来进一步分析它们。

• getErrorCode() 提供了特定于供应商的异常代码。

• getSQLState() 提供了符合 XOPEN SQLstate 或 SQL:2003 标准的字符串。
  每个 SQLException 对象通常都有一个由多个 SQLException 对象构成的异常链。JDBC 4.0 的 SQLException 已经实现了 Iterable<Throwable> 接口，因此可以通过 for-each 来遍历异常链。

  for (Throwable throwable : sqlException) {
      // some code
  }

元数据

JDBC 除了对数据库的读写操作提供支持以外，我们还可以通过它来获取数据库的元数据。
这在编写 ORM 框架或者是编写数据库管理工具的时候非常有用。
主要涉及到：DatabaseMetaData 、 ParameterMetaData 和 ResultSetMetaData 接口。

DatabaseMetaData

通过 DatabaseMetaData 可以获取数据库的整体综合信息，例如：数据库名称、版本、表信息、列信息、索引信息、主外键信息，以及事务支持情况等等。

DatabaseMetaData dbmd = connection.getMetaData();

System.out.println("数据库产品的名称：" + dbmd.getDatabaseProductName());
System.out.println("数据库产品的版本号：" + dbmd.getDatabaseProductVersion());

System.out.println("驱动程序版本号：" + dbmd.getDriverVersion());
System.out.println("JDBC 版本号：" + dbmd.getJDBCMajorVersion() + "."
    + dbmd.getJDBCMinorVersion());

System.out.println("最大并发连接数（0意味着没有限制或是未知的）："
    + dbmd.getMaxConnections());
System.out.println("最大并发语句数（0意味着没有限制或是未知的）："
    + dbmd.getMaxStatements());

System.out.println("数据库是否支持事务：" + dbmd.supportsTransactions());
System.out.println("数据库是否支持保存点：" + dbmd.supportsSavepoints());
System.out.println("支持的事务隔离级别：");
System.out
    .println("TRANSACTION_READ_COMMITTED\t"
        + dbmd.supportsTransactionIsolationLevel(Connection.TRANSACTION_READ_COMMITTED));
System.out
    .println("TRANSACTION_READ_UNCOMMITTED\t"
        + dbmd.supportsTransactionIsolationLevel(Connection.TRANSACTION_READ_UNCOMMITTED));
System.out
    .println("TRANSACTION_REPEATABLE_READ\t"
        + dbmd.supportsTransactionIsolationLevel(Connection.TRANSACTION_REPEATABLE_READ));
System.out
    .println("TRANSACTION_SERIALIZABLE\t"
        + dbmd.supportsTransactionIsolationLevel(Connection.TRANSACTION_SERIALIZABLE));

System.out.println("数据库支持的所有数据类型：");
ResultSet typeInfo = dbmd.getTypeInfo();
while (typeInfo.next()) {
  System.out.println(typeInfo.getString("TYPE_NAME"));
}

System.out.println("数据库中的表（视图、别名等）信息：");
String dbName = "codinglike";
ResultSet tables = dbmd.getTables(null, dbName, null, null);
while (tables.next()) {
  System.out.println(tables.getString("TABLE_NAME") + "\t"
      + tables.getString("TABLE_TYPE"));
}

System.out.println("数据库中某个表的列信息：");
String tableName = "user";
ResultSet columns = dbmd.getColumns(null, null, tableName, null);
while (columns.next()) {
  System.out.println(columns.getString("COLUMN_NAME") + "\t"
      + columns.getString("TYPE_NAME"));
}

System.out.println("某个表的主键信息：");
ResultSet primaryKeys = dbmd.getPrimaryKeys(null, null, tableName);
while (primaryKeys.next()) {
  System.out.println(primaryKeys.getString("COLUMN_NAME") + "\t"
      + primaryKeys.getString("PK_NAME"));
}

ParameterMetaData

ParameterMetaData 可用于获取关于 PreparedStatement 对象中每个参数的类型信息。
如果使用 MySQL 数据库，需要在在 URL 中加上参数： generateSimpleParameterMetadata=true ，否则会抛出 java.sql.SQLException: Parameter metadata not available for the given statem 异常信息。

ParameterMetaData pmd = prepareStatement.getParameterMetaData();

// 获得参数的数量
int count = pmd.getParameterCount();
for (int i = 1; i <= count; i++) {
  // 获取参数的 Java 类型和 SQL 类型
  System.out.println(pmd.getParameterClassName(i) + "\t"
      + pmd.getParameterType(i) + "\t"
      + pmd.getParameterTypeName(i));
}

ResultSetMetaData

ResultSetMetaData 可用于获取关于 ResultSet 对象中每一列的信息。
有一定容易搞混淆的是：getColumnName() 获取的是列的原始名称。而 getColumnLabel() 返回的是别名（也就是 AS 后面的名称）。

ResultSetMetaData rsmd = resultSet.getMetaData();
int columnCount = rsmd.getColumnCount();
for (int i = 1; i <= columnCount; i++) {
    // 获取列信息
    System.out.println(rsmd.getColumnName(i) + "\t"
        + rsmd.getColumnLabel(i) + "\t"
        + rsmd.getColumnClassName(i) + "\t"
        + rsmd.getColumnType(i) + "\t"
        + rsmd.getColumnTypeName(i));
}

事务

有时候我们的业务很简单，只需要执行一个 SQL 语句就可以搞定。
但有的时候，用户的一个操作可能会涉及多个 SQL 。
例如，我们有以下两个表：

user 表

id	username	password
1	Franky	12345678
2	Nami	87654321

userinfo 表

id	user_id	gender	address
11	1	male	Shenzhen
12	2	female	Beijing

这两张表存在主从关系，当我们删除一个用户时，为了保证数据库的一致性，需要把两张表的关联数据都删除掉。并且，如果其中一张表的数据删除失败，那么另一张的删除操作也不应该提交。要实现这样的需求，我们就需要用到事务。
简单来说，一组语句可以构成一个事务，事务中的语句“同生共死”——要么成功执行所有语句并提交，要么在中间的某个位置失败并回滚到初始状态。

默认情况下，Connection 处于自动提交模式，也就是说，它的所有 SQL 语句都会被当成单个事务提交。
我们可以通过 setAutoCommit(false) 来禁用自动提交模式。如果这样，我们就需要通过 commit() 和 rollback() 来手动控制事务的提交和回滚。

try {
    // 禁用自动提交模式
    connection.setAutoCommit(false);

    // some code

    // 提交事务
    connection.commit();
} catch (SQLException e) {
    // 回滚
    connection.rollback();
}

JDBC 3.0 之后，如果数据库驱动支持保存点（Savepoint），我们还可以通过 setSavepoint() 更精细地控制回滚操作，以便回滚到事务中间的某一个状态，而不是事务的开头。

Savepoint savepoint = null;
try {
    // 禁用自动提交模式
    connection.setAutoCommit(false);

    // some code

    savepoint = connection.setSavepoint();

    // some code

    // 提交事务
    connection.commit();
} catch (SQLException e) {
    // 回滚
    if(savepoint == null) {
      connection.rollback();
    } else {
      connection.rollback(savepoint);
      connection.commit();
    }
}

另外，当我们批量执行 SQL 的时候，也需要暂时禁用自动提交模式。

// 获取当前的提交模式
boolean autoCommit = connection.getAutoCommit();
// 禁用自动提交模式
connection.setAutoCommit(false);

// some code
prepareStatement.addBatch();
// some code

prepareStatement.executeBatch();

// 提交事务
connection.commit();
// 恢复为初始状态
connection.setAutoCommit(autoCommit);

关于，事务的 ACID 属性、事务隔离级别及其影响等内容，后续再展开。

最佳实践

JDBC 有相对固定的编程模型，易于理解，使用起来也很简单，我们只需要找到相应数据库的驱动，把它们加入 CLASSPATH ，就可以开始参照 API 去操作数据库了。
然而，实际上，应用程序的持久层又很容易成为性能瓶颈，因此，在实现这部分代码的时候，我们应该仔细权衡，在可能的情况下尽量提升性能。
可以从不同方面来达到优化的目的，以下是一些供参考选项：

• 使用对象池技术
  • Connection 池
    在高并发的应用中使用连接池已经是事实上的标准。然而，JDK 本身和数据库厂商的驱动程序并没有提供数据库连接池服务。因此，我们要么自己实现，要么使用第三方提供的成熟组件。
  • Statement 池
    JDBC 3.0 之后，我们还可以为每个连接创建一个预处理语句缓冲池。这样能够让驱动可以重复使用 PreparedStatement 对象。可以通过 MaxPooledStatements 进行配置，还可以通过 DatabaseMetaData 的 getMaxStatements() 方法来查询最大并发语句数。
• 减少交互次数
  • 慎重选择事务提交模式，使用 executeBatch() 批量执行 SQL ，可以减少数据库数据传输的往返次数，节省流量，从而提高性能。
  • 设置合理的提取规模（Fetch Size）。Fetch Size 决定了数据库每批次传输的记录条数。默认的 Fetch Size 取决于具体的数据库驱动，我们可以通过 Statement 的 getFetchSize() 和 setFetchSize() 方法来查询和设置。Fetch Size 并不存在一个最优的固定值，与查询结果的记录集大小以及可用内存大小相关。
  • 优化 SQL 语句，在需要进行多次数据库交互时，可以考虑使用存储过程是否恰当，或者能否通过表连接（join）减少数据抓取次数。
  • 缓存热点数据，减少对数据库的访问。
• 返回更少的数据
  • 采用分页技术。
  • 只获取必要的字段，不要使用 select * from table 。
  • 在获取元数据信息时，设置限定条件，缩小搜索范围。
• 减少数据库服务器的 CPU 运算
  • 使用存储预编译 SQL 语句的 PreparedStatement 对象。
  • 合理使用涉及排序的查询语句。