Java JDBC 入门

JDBC 全称 Java Database Connectivity,中文:Java 数据库连接。JDBC 是 Java 标准库提供的一套专门用于访问关系型数据库的 API,使用 JDBC API 访问数据库需要数据库厂商提供对应的 JDBC 驱动(大部分数据库厂商都会提供,比如 MySQL、MariaDB、Oracle、DB2、SQL Server),如果厂商未提供 JDBC 驱动,JDBC 也提供了相应的解决方案:JDBC-ODBC Bridge;也就是说只要数据库厂商提供了 ODBC(Open Database Connectivity,开放数据库互连)驱动我们也可以使用 JDBC API 来访问。

JDBC API 简介

自从数据需要被持久化存储,程序与数据库之间的交互就是不可避免的操作。而早期程序与不同的数据库的交互方式不同,意味着程序开发不得不面对数据库的具体实现,一旦切换数据库,又是新的学习过程,这样开发人员的效率始终被和数据库的交互所限制。

根据软件设计中的依赖倒置原则,要针对接口编程,不要针对实现编程,因而由于数据库的变化而引起实现的变化是违背软件设计的基本原则的。优化的方式是在数据库上提供一层抽象接口,不同数据库根据接口完成自己的实现,而用户使用时,只需面向接口编程,而不用关心内部的实现细节。如果需要更换数据库,只需要指定不同的数据库标识即可。

Java 的 JDBC(Java DataBase Connectivity)就是一组这样的抽象 API,通过执行 SQL 语句,为多种关系型数据库提供统一访问。下面以 MySQL 的一个简单查询为例,介绍 JDBC 的接口和基本构成。

在运行 SimpleDemo 之前,你需要先从 MySQL 官网下载对应的 JDBC 驱动(jar 文件),然后将这个 jar 文件放到 CLASSPATH 路径中。

JDBC API 位于 java.sql 包,扩展 API 位于 javax.sql 包。

  • java.sql.Driver:代表一个 JDBC 驱动,由驱动提供商实现。
  • java.sql.DriverManager:驱动管理器,主要用于获取数据库连接。
  • java.sql.Connection:代表一个数据库连接(一般为 Socket 套接字连接)。
  • java.sql.Statement:语句对象,用于执行 SQL 语句(如 SELECT、INSERT、UPDATE、DELETE)。
  • java.sql.ResultSet:结果对象,代表 SQL 执行结果(二维表,一行称为一条记录,一列称为一个字段)。
  • java.sql.SQLException:使用 JDBC 操作数据库期间,如果发生错误,SQLException 将被抛出(或者是它的子类)。

上面的程序可以分为 3 个部分:注册驱动、连接数据库、访问数据库。其中访问数据库基本相似,顶多就是 SQL 方言不同。

注册驱动
Class.forName("com.mysql.cj.jdbc.Driver"):从 CLASSPATH 中查找对应的类,然后载入方法区,执行该类的静态初始化块。
其中注册驱动的关键在于静态初始化块,我们来看看 mysql jdbc 驱动中的 static 初始化块的代码(com.mysql.cj.jdbc.Driver):

可以看到,java.sql.Driver 接口是由 JDBC 驱动提供者实现的,static 块的语句很简单,就是调用 DriverManager.registerDriver(new Driver()) 方法。那是不是意味着我们可以直接调用 java.sql.DriverManager 类的 registerDriver() 静态方法来注册驱动程序呢?如果是从可行性上看,那是可以的;但是从效率上看,不建议直接调用 DriverManager.registerDriver() 方法,因为这会创建两个 Driver 实例(静态初始化块中创建一次,registerDriver() 方法中 new 一次),导致资源的浪费(同时也会注册两次 JDBC 驱动,没必要),即使 JVM 都会对它们进行回收。

但是从 JDBC 4.0 开始,我们不再需要使用 Class.forName() 来显式注册驱动了(当然对应的 JDBC 驱动也必须符合 JDBC 4.0 规范),直接使用 DriverManager.getConnection() 获取数据库连接即可。JDBC 会自动查找 CLASSPATH 中 META-INF/services/java.sql.Driver 文件,这个文件一般只有一行,它的内容为 com.mysql.cj.jdbc.Driver,即对应的 JDBC Driver 类的全限定类名。JDBC 会尝试让这些 driver 解析给定的 url,如果能够解析则选定此驱动,进行注册,否则继续下一个尝试,如果都没有则抛出异常。一般情况下这没有什么问题,但是如果 CLASSPATH 中有多个 driver 都能解析此 url,JDBC 会选择哪个呢?我觉得应该是最前面的那个 driver(CLASSPATH 中先出现的那个)。比如 mysql driver 和 mariadb driver 都可以解析 jdbc:mysql://localhost/test,实际上使用哪个是无法确定的(虽然 mariadb 与 mysql 基本兼容,但难免会有细微的差异)。所以我觉得还是显式的使用 Class.forName() 来注册对应的驱动比较稳妥,即便是 JDBC 4.0 驱动。

连接数据库
JDBC 使用 database url 来定位一个数据库实例,这个 database url 始终以 jdbc: 开头,后面的一般由驱动提供者自己定义,比如 mysql 的 url 格式为:jdbc:mysql://host[:port]/dbname,mariadb 的 url 格式为 jdbc:mariadb://host[:port]/dbname,oracle 12c 的 url 格式为 jdbc:oracle:driver_type:[username/password]@database_specifier。一般来说 username、password 认证信息不包含在 database url 中,而是作为单独的参数提供给 DriverManager 的 getConnection() 方法,即 DriverManager.getConnection(url, user, pass)

对于 mysql、mariadb,url 实际上可以省略 dbname,如 jdbc:mysql://localhost,获取连接后可以使用 use dbname 切换数据库。

JDBC 数据类型

java.sql 包中有两个类定义了 JDBC 数据类型:java.sql.Typesjava.sql.JDBCType(枚举类,JDK1.8 新增)。

SQL 类型 Java 类型
BIT boolean
TINYINT byte
SMALLINT short
INTEGER int
BIGINT long
REAL float
FLOAT double
DOUBLE double
BINARY byte[]
VARBINARY byte[]
LONGVARBINARY byte[]
NUMERIC java.math.BigDecimal
DECIMAL java.math.BigDecimal
CHAR java.lang.String
VARCHAR java.lang.String
LONGVARCHAR java.lang.String
DATE java.sql.Date
TIME java.sql.Time
TIMESTAMP java.sql.Timestamp
BLOB java.sql.Blob
CLOB java.sql.Clob
Array java.sql.Array
REF java.sql.Ref
Struct java.sql.Struct

java.sql.Types 类的静态常量,定义 JDBC 中的数据类型:

JDBC 主要接口

DriverManager 类

管理 JDBC Driver 的静态工具类,例如:注册 Driver、取消注册 Driver、与指定数据库建立连接。
JDBC 2.0 新增的 DataSource 接口提供了另一种连接数据库的方法,是 DriverManager 的替代品。

Connection 接口

代表一个数据库连接(会话,Session)

Savepoint 接口

数据库事务中的保存点(savepoint),由 connection.setSavepoint() 方法创建,可用于 rollback() 等方法。

Statement 接口

静态 SQL 语句对象,默认情况下,每个 Statement 对象只能同时打开一个 ResultSet 对象。

PreparedStatement 接口

PreparedStatement 是 Statement 的子接口,预编译 SQL 语句对象,支持设置参数(使用 ? 进行占位)。

CallableStatement 接口

CallableStatement 是 PreparedStatement 的子接口,调用函数/存储过程的语句对象,也使用 ? 占位。有两种语法:

  • {? = call function_name[(arg1, arg2, ...)]}:调用函数,其中 argN 可以使用 ? 占位。
  • {call procedure_name[(arg1, arg2, ...)]}:调用存储过程,其中 argN 可以使用 ? 占位。
  • 值得说明的是,[] 表示里面的内容是可选的,不要将 [] 输入到实际的 SQL 语句中,否则会报错。

函数的参数只能是 IN 类型的,而存储过程的参数可以是 IN、OUT、INOUT 三种类型,OUT、INOUT 参数必须在 execute*() 前使用 registerOutParameter() 注册,然后在 execute*() 之后使用 getXxx() 来获取对应的值;当然,函数的返回值其实也算是 OUT 参数(等号前面的那个),因此也要使用 registerOutParameter() 注册,在 execute 之后使用 getXxx() 获取。

注意,不是所有 JDBC 驱动都支持通过 parameterName 设置/读取 参数的,通过 DatabaseMetaData.supportsNamedParameters() 方法可以判断当前 JDBC 驱动以及 RDBMS 是否支持命名参数(不过感觉这个方法不是很准,因为我使用它得到的是 false,但是实际上 mysql/mariadb jdbc 驱动支持命名参数,实际运行也没有报错),DatabaseMetaData 可以通过 Connection 对象的 getMetaData() 方法获取。

ResultSet 接口

ResultSet 是由 SQL 语句执行产生的结果表对象,默认是 TYPE_FORWARD_ONLY(只能向前移动)、CONCUR_READ_ONLY(只读)。

BigDecimal 类

在 JDBC API 中,经常会用到 java.math.BigDecimal,BigDecimal 是专门用于表示和处理高精度小数的类。Java 中的 float、double 由于存在精度丢失(扩展阅读:整数与小数的二进制处理)只能用在工程计算、科学计算等精度要求不高的场景(float、double 也称为 浮点数),而 BigDecimal 定点数 因为使用字符串存储小数,只要内存空间允许,可以实现任意精度数值的表示与运算,诸如银行账户、货币大小这些精度要求高的场景必须使用定点数来存储和处理,但也因为使用字符串进行存储和运算,所以需要的内存比浮点数更多,而且因为定点数的运算也比较复杂,所以效率也不如浮点数。

java.math.BigDecimal 的 API 简介:https://www.zybuluo.com/Otokaze/note/1223158#bigdecimal

Clob 接口

Clob 是 Character Large Object 的简称,即 字符大对象,在 MySQL 中就是 TEXT 数据类型。在 ResultSet、PreparedStatement、CallableStatement 接口中,可以使用 getClob()setClob() 方法对 Clob 数据进行操作,而 java.sql.Clob 接口本身可以对 Clob 数据进行更新。

Blob 接口

java.sql.Blobjava.sql.Clob 很相似,只不过 Blob(Binary Large Object) 是用来存储二进制数据的,不关心字符编码。

Date 类

JDBC 中的 Date、Time、Timestamp 均为 java.util.Date 的子类,这三个类都位于 java.sql 包,分别代表:日期时间时间戳

Time 类

Timestamp 类

Timestamp 是 java.util.Date + 内部的小数秒(以纳秒为单位)的组合实现,整数秒存储在 Date 对象,小数秒存储在 Timestamp 对象,也就是说这两个部分是分开存储的。此外,hashCode() 方法使用底层的 java.util.Date 实现,因此在其计算中不包含 nanos 部分。由于 Timestamp 类和上面提到的 java.util.Date 类之间存在差异,因此建议代码不要将 Timestamp 视为 java.util.Date 的实例。Timestamp 和 java.util.Date 之间的继承关系实际上表示实现继承,而不是类型继承

SQLException 类

使用 JDBC 操作数据库期间,发生任何有关 SQL 的异常都会导致 SQLException 对象被抛出。SQLException 实现了 Iterable 接口,可迭代。

JDBC 简单示例

创建数据库
创建 mydb 数据库,用于测试。

编译、执行、验证结果:

创建数据表
创建 website 表,用于测试。

编译、执行、验证结果:

插入数据
因为插入语句只是参数不同,所以可以使用 PreparedStatement。

编译、执行、验证结果:

查询数据
这里用到了 ResultSetMetaData,具体用法请参考 JDBC API。

编译、执行、验证结果:

过滤数据
假设存在表 mydb.webdata,表的内容为:

现在我想查找出 content 包含 %_data_% 的记录:

第一个很明显不行,因为 %_ 在 like 子句中表示 任意字符单个字符,必须使用反斜线对其转义,以取消它们的特殊含义。但如果要在 JDBC 中执行这条查询语句,还需要注意 \ 本身在 Java 字符串中也表示一个转义序列的开始,所以我们需要使用 \\ 来表示真正的 \ 字符(否则编译失败,因为 \%\_ 并不是有效的 Java 字符串转义序列,这和 regex 正则模式是一个道理)。

如果你不想使用这种丑陋的方法(但我自己已经习惯了),也可以使用 JDBC 提供的 SQL 转义语法,即 {escape '转义字符'},它的作用是定义 SQL 中的转义序列的开始字符,默认的 \ 与 Java 字符串转移序列有冲突,你可以将其改为一个普通字符,比如 /,所以上面的那条 executeQuery() 语句可以这样写:select id, title from webdata where content like '%/%/_data/_/%%' {escape '/'},JDBC 规定,escape 语法必须位于 SQL 语句的尾部,如果有多条语句,那么每条语句的尾部都要放置 escape 语法声明。

SQL 转义序列
其实除了 {escape '转义字符'} 外,还有几个类似的转义语法,我们将它们统称为 JDBC SQL 转义序列
JDBC SQL 转义序列必须位于一对花括号({})内,具体的语法为:{keyword parameters},常见的有:

  • {escape 'escape character'}:定义 SQL 转移序列的开始字符
  • {fn func([arg1[, arg2...]])}:调用数据库中的标量函数
  • {d 'yyyy-mm-dd'}:将当前表达式作为 Date 数据类型
  • {t 'hh:mm:ss[.f...]'}:将当前表达式作为 Time 数据类型
  • {ts 'yyyy-mm-dd hh:mm:ss[.f...]'}:将当前表达式作为 Timestamp 数据类型
  • {call proc_name(?, ...)}:调用存储过程(procedure)
  • {? = call func_name(?, ...)}:调用函数(function)
  • {oj outer-join-statement}:outer join(外部连接)表达式

JDBC 实用类库

Commons DbUtils 是 Apache 提供的一个开源 JDBC 轻量包装库,整个库的类很少,核心的两个类/接口是 QueryRunnerResultSetHandler,DbUtils 旨在简化 JDBC API 的使用,因为在 JDBC 中清理资源是很乏味且容易出错的工作(稍不注意就可能导致资源泄漏),所以 DbUtils 从代码中抽象出所有清理任务,让你了解你真正想用 JDBC 做的事情:查询更新数据。

DbUtils 优点

  • 没有资源泄漏的可能性。正确的 JDBC 编码并不困难,但它既费时又乏味。这通常会导致可能难以追踪的资源泄漏。
  • 更清晰的持久性代码。将数据保存在数据库中所需的代码量大大减少。剩余的代码清楚地表达了您的意图,而不会混淆资源清理。
  • 从 ResultSet 自动填充 JavaBean 属性。您无需通过手动调用 setter 方法将列值复制到 bean 实例中。ResultSet 的每一行都可以由一个完全填充的 bean 实例表示。

获取 DbUtils:http://www-eu.apache.org/dist/commons/dbutils/binaries/commons-dbutils-1.7-bin.tar.gz

DbUtils 简介
DbUtils 是一个很小的包装库,前面说了,DbUtils 中只有两个核心的类/接口:QueryRunnerResultSetHandler

  • QueryRunner:用来执行各种查询、更新操作,并获取结果。
  • ResultSetHandler:将 ResultSet 对象转换为想要的数据。

DbUtils 例子
查询操作:

更新操作:

【查询操作】例子中,创建了 ResultSetHandler<List<Object[]>> 匿名内部类,但因为这种转换操作很普遍(获取结果表的所有行),如果每次 query 都要自己手动创建一个匿名类,那估计比 JDBC API 还繁琐。DbUtils 也意识到了这一点,为我们实现了大部分常用的 ResultSetHandler 类,它们都位于 org.apache.commons.dbutils.handlers 包。现在我们可以利用 handlers 包的 ArrayListHandler 来替换【查询操作】中的匿名内部类:

【更新操作】例子中,除了可以多次调用 update() 方法来进行多次更新外,还可以使用 batch() 方法进行批量更新:

DbUtils API
org.apache.commons.dbutils.QueryRunner 类,执行 SQL 的对象。

org.apache.commons.dbutils.OutParameter<T> 类,用来表示对应索引处的 OUT、INOUT 参数:

存储过程的例子(貌似 DbUtils 不能用来创建函数/存储过程):

org.apache.commons.dbutils.ResultSetHandler<T> 接口,定义如何将 ResultSet 转换为你需要的数据(对象)。

org.apache.commons.dbutils.DbUtils 工具类,提供一些常用的 JDBC 实用方法。

org.apache.commons.dbutils.handlers 包中的一些实用 ResultSetHandler 类:

  • ArrayHandler:返回结果表的第 1 行(Object[]
  • ArrayListHandler:返回结果表的所有行(List<Object[]>
  • BeanHandler<T>:返回由结果表的第 1 行填充的 JavaBean 对象(T
  • BeanListHandler<T>:返回由结果表填充的 JavaBean 对象(List<T>
  • ScalarHandler<T>:返回结果表的第 1 行的第 X 列(T
  • ColumnListHandler<T>:返回由结果表的第 X 列的值组成的列表(List<T>
  • MapHandler:返回第 1 行的 columnName -> columnValue 组成的 Map 对象(Map<String, Object>
  • MapListHandler:返回所有行的 columnName -> columnValue 组成的 List 对象(List<Map<String, Object>>

ScalarHandler、ColumnListHandler 例子

MapHandler、MapListHandler 例子