JDBC

一：概述

JDBC (Java Data Base Connectivity) 是一种用于执行 SQL 语句的 Java API。JDBC 是 Java 访问数据库的标准规范，可以为不同的关系型数据库提供统一访问。它由一组用 Java 语言编写的接口和类组成，位于 java.sql 和 javax.sql 包下面。

JDBC 需要连接驱动。客户端要和 DBMS 进行通信，需要满足一定的通信数据格式 (协议)，协议是由数据库厂商制定的，不同的DBMS有不同的协议。因此数据库厂商必须为客户端提供驱动软件，这样客户端才能连接到 DBMS。就像硬件设备厂家为了自家的设备能跑在操作系统上，必须提供驱动程序一样。

Sun 公司指定了 Java 访问数据库的规范，这些规范称为 JDBC。而各个数据库厂商提供了各自的实现，这些实现我们称为驱动。

二：入门案例

回想以下之前我们是怎么操作数据库的？大概可以分为以下步骤：

建立连接
执行 SQL 语句
获取返回结果
退出 (exit, 关闭连接，释放资源)

用 Java 程序操作数据库的过程和之前的操作很类似，分为以下几个步骤：

注册驱动
建立连接
获取 SQL 语句执行平台
执行 SQL 语句
处理结果
退出 (关闭连接，释放资源)

下面我们以 MySQL 为例 (驱动可以从这里下载)，演示下 java 是如何连接 MySQL 并执行 SQL 语句的。

演示之前，我们对 MySQL 的配置文件 my.ini 做下修改：将 [mysqld] 下的 general-log 的值修改为 1。

[mysqld]
general-log=1

然后重启 mysql 服务。在 windows 系统上是执行下面的命令：

net stop mysql
net start mysql

这样，我们就可以通过 log 知道我们的代码在 MySQL 服务器上是怎么执行的了。

    public void testGetConnection1() throws SQLException {
        // 注册驱动
        Driver driver = new com.mysql.jdbc.Driver();
        // 建立连接
        String url = "jdbc:mysql://localhost:3306/test";
        Properties info = new Properties();
        info.setProperty("user", "root");
        info.setProperty("password", "r00tme");
        Connection con = driver.connect(url, info);
        // 获取SQL语句执行平台
        Statement stmt = con.createStatement();
        String sql = "select * from t where id = 1";
        // 执行SQL语句
        ResultSet resultSet = stmt.executeQuery(sql);
        // 处理结果
        if (resultSet.next()) {
            int id = resultSet.getInt(1);
            int a = resultSet.getInt(2);
            int b = resultSet.getInt(3);
            int c = resultSet.getInt(4);
            System.out.printf("%d, %d, %d, %d", id, a, b, c);
        }
        // 关闭连接，释放资源
        con.close();
    }

三：API 详解

3.1 Driver & DriverManager

版本一

Driver driver = new com.mysql.jdbc.Driver();
String url = "jdbc:mysql://localhost:3306/jdbc_db";
Properties info = new Properties();
info.setProperty("user", "root");
info.setProperty("password", "r00tme");
Connection conn = driver.connect(url, info);
Assert.assertNotNull(conn);

版本二

Driver driver = new com.mysql.jdbc.Driver();
DriverManager.registerDriver(driver);
String url = "jdbc:mysql://localhost:3306/jdbc_db";
String user = "root";
String password = "r00tme";
Connection conn = DriverManager.getConnection(url, user, password);
Assert.assertNotNull(conn);

版本三

String driverClass = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://localhost:3306/test";
String user = "root";
String password = "r00tme";
Class<?> clazz = Class.forName(driverClass);
Driver driver = (Driver) clazz.newInstance();
DriverManager.registerDriver(driver);
Connection con = DriverManager.getConnection(url, user, password);
Assert.assertNotNull(con);

版本四

查看源码，我们发现 com.mysql.jdbc.Driver 在类加载的时候，就会自动注册。

String driverClass = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://localhost:3306/test";
String user = "root";
String password = "r00tme";
Class<?> clazz = Class.forName(driverClass);
Connection con = DriverManager.getConnection(url, user, password);
Assert.assertNotNull(con);

版本五

JDBC4 规范：要求驱动能够自动加载 (类似设备连接 PC 时，驱动能够自动加载运行一样)。因此，我们只要导入 jar 包就可以了。

String url = "jdbc:mysql://localhost:3306/test";
String user = "root";
String password = "r00tme";
Connection con = DriverManager.getConnection(url, user, password);
Assert.assertNotNull(con);

3.2 Statement

我们可以通过 Statement 执行各种 SQL 语句，包括 DDL、DCL、DML、DQL等。

通过 Statement 创建数据库、创建表、插入数据、更新数据、查找数据、删除表、删除数据库。

创建数据库

Connection conn = connect();
Statement stmt = conn.createStatement();
String sql = "create database if not exists db1";
stmt.execute(sql);
// boolean execute(String sql) 可以执行任何SQL语句, 如果有结果集返回true, 否则返回false.

创建表

Connection conn = connect();
Statement stmt = conn.createStatement();
String sql = "create table if not exists tb1(id int primary key, a int)";
stmt.execute(sql);

插入数据

Connection conn = connect();
Statement stmt = conn.createStatement();
String sql = "insert into tb1 values (1, 1), (2, 2), (3, 3)";
int rows = stmt.executeUpdate(sql);
Assert.assertEquals(3, rows);
// int executeUpdate(String) 可以执行DDL和DML, 返回受影响的行数

查询数据

Connection conn = connect();
Statement stmt = conn.createStatement();
String sql = "select * from tb1 where id = 0";
ResultSet rs = stmt.executeQuery(sql);
Assert.assertFalse(rs.next());
// ResultSet executeQuery(String sql) 执行DQL,返回结果集。不管有没有满足查询条件的记录，ResultSet都不为null.我们可以通过ResultSet的next()方法判断是否有满足查询条件的记录。

Statement 会引入 SQL 注入问题。

String name = "aaa' or 1=";
String password = " or 1='1";
name = "'" + name + "'";
password = "'" + password + "'";
String sql = String.format("select * from user where name=%s and password=%s", name, password);

3.3 PreparedStatement

通常我们执行一条 SQL 语句，得经过下面三个过程。

词法和语义解析
优化 SQL 语句，制定执行计划
执行并返回结果

我们把这种普通 SQL 语句称作 Immediate Statements。

但是很多情况下，一条 SQL 语句可能会被反复执行。或者是反复执行的 SQL 语句，它们结构相似，只是参数不同而已。如果每次执行的时候都要经过上面的词法语义解析、优化 SQL、制定执行计划等，那效率就明显不行了。

MySQL 提供了预编译语句，可以帮我们解决这个问题。在预编译语句中，参数我们用占位符 ? 替代，这样我们就可以为多条不同的 SQL 语句，提供统一的模板。我们把这种 SQL 语句称作 Prepared Statements 或者Parameterized Statements。

预编译语句的优势有以下两点：

一次解析优化，多次执行。
可以防止 SQL 注入问题。

3.3.1 语法

编译

PREPARE stmt_name FROM prepare_stmt;

执行

EXECUTE stmt_name [USING @var_name [, @var_name] ...];

删除

{DEALLOCATE | DROP} PREPARE stmt_name

3.3.2 Java 程序调用

在 Java 中我们提供了 PreparedStatement 这个接口来调用数据库的预编译功能。

Connection conn = connect();
PreparedStatement pstmt = conn.prepareStatement("select * from t_user where username=? and password=?");
String username = "aaa or 1='";
String password = " or 1='1";
username = "'" + username + "'";
password = "'" + password + "'";
pstmt.setString(1, username);
pstmt.setString(2, password);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
	int id = rs.getInt(1);
    String name = rs.getString(2);
   	String pwd = rs.getString(3);
  	System.out.printf("id=%d, name=%s, password=%s\n", id, name, pwd);
}

虽然这样我们就能避免 SQL 注入攻击了，但是测试性能时，我们会发现并没有什么提高。原因在于，这样我们只是开启了客户端预编译，实际还是用 Query 执行的，而不是 Execute。

为了提高性能，我们需要开启服务端预编译，在 url 后面添加一些参数。

url=jdbc:mysql://localhost:3306/jdbc_db?useServerPrepStmts=true&cachePrepStmts=true
useServerPrepStmts: 是否启用服务端预编译, true表示启用。
cachePrepStmts: 是否缓存预编译语句, true表示缓存。

3.4 Batch

不管是 Statement 还是 PreparedStatement，执行一条 SQL 语句，客户端都要和服务器进行一次通信，这样效率就比较低下。因此，MySQL 提供了批处理功能。

Connection conn = connect();
String sql = "insert into t_user (username, password) values (?, ?)";
PreparedStatement pstmt = conn.prepareStatement(sql);
long start = System.currentTimeMillis();
for (int i = 0; i < 10000; i++) {
  	pstmt.setString(1, "user" + i);
    pstmt.setString(2, "pwd" + i);
    pstmt.addBatch();
    if (i % 1000 == 0) {
        	pstmt.executeBatch();
            pstmt.clearBatch();
    }
}
pstmt.executeBatch();
long end = System.currentTimeMillis();
System.out.println("Batch: " + (end - start) + " millis");

默认情况下，MySQL 没有开启批处理模式。需要在 url 后面添加 rewriteBatchedStatements=true 参数。

3.4 ResultSet

Java 中用 ResultSet 接口代表数据库返回的结果集。

ResultSet 封装执行结果时，采用的类似于表格的方式。ResultSet 对象维护了一个指向表格数据行的游标，初始的时候，游标在第一行之前。ResultSet 的 next() 方法，可以判断是否有还有下一行数据。如果有，返回 true, 将游标越过该行，并把越过这行记录，放入 ResultSet 对象中。如果没有，返回 false。

3.4.1 ResultSet API

getXXX(int index)
getXXX(String columnName)
next()
previous()
absolute(int row)
beforeFirst()
afterLast()

3.4.2 常用数据类型对应表

MySQL 数据类型	JDBC 对应的 getter/setter	Java 数据类型
tinyInt	getByte()/setByte()	byte
smallint	getShort()/setShort()	short
int	getInt()/setInt()	int
bigint	getLong()/setLong()	long
char, varchar	getString()/setString()	String
Text	getClob()/setClob()	Clob
Blob	getBlob()/setBlob()	Blob
Date	getDate()/setDate()	java.sql.Date
Time	getTime()/setTime()	java.sql.Time
TimeStamp	getTimeStamp()/setTimeStamp()	java.sql.TimeStamp

3.5 管理连接、语句和结果集

每个 Connection 对象都可以创建一个或多个 Statement 对象。同一个 Statement 对象可以用于多个不相关的命令和查询。但是，一个 Statement 对象最多只能有一个打开的结果集。如果需要执行多个查询操作，且需要同时分析查询结果，那么必须创建多个 Statement 对象。

使用完 ResultSet、Statement 或 Connection 对象后，应立即调用 close() 方法。这些对象都使用了规模较大的数据结构，它们会占用数据库服务器有限的资源。

如果 Statement 对象上有一个打开的结果集，那么调用 close() 方法将自动关闭该结果集。同样地，调用 Connection 类的 close() 方法将关闭该连接上所有的 Statement。

3.6 JDBC 事务相关 API

Connection

void setAutoCommit(boolean autoCommit)
void commit()
void rollback()
void rollback(Savepoint savepoint)
Savepoint setSavepoint()
void releaseSavepoint(Savepoint savepoint)
void setTransactionIsolation(int level)
static int TRANSATION_NONE, TRANSACTION_READ_UNCOMMITTED, TRANSACTION_READ_COMMITTED, TRANSACTION_REPEATABLE_READ, TRANSACTION_SERIALIZABLE

参考资料

1.《Java 核心技术卷Ⅱ》第 10 版，Cay S. Horstmann

JDBC基础