众所周知,JDBC 是 MyBatis 等技术的基础。为了确保操作 MyBatis 时能更加轻车熟路,有必要将 JDBC 彻底弄熟。
因此,本文整理了 JDBC 相关内容,帮助进一步理解 JDBC 的概念和技术运用。
1. JDBC 相关概念
数据持久化 data persistence:
- 将内存数据存储到硬盘中。
- 主要手段包括:存储到 SQL、RDB、AOF、XML 等内。
Java 数据存储技术:
- JDBC 直接访问数据库。
- JDO (Java Data Object)。
- 第三方 O/R 工具,如 Hibernate, MyBatis 等。
JDBC:Java Database Connectivity:
-
JDBC 是独立于特定数据库管理系统的通用 SQL 存取操作的公共接口,是一组 API。
-
JDBC 定义了访问数据库的标准类库,提供标准方法访问数据库。
-
JDBC 使程序员无需了解数据库系统即可完成数据存取,提高了可移植性和易开发性。
JDBC 体系结构:
-
API 体系包含两种类型。
-
面向应用的 API:Java API。供开发人员使用,包括连接数据库,执行 SQL 语句,返回结果。
-
面向数据库的 API:Java Driver API。供开发商开发数据库驱动使用,普通程序员不用了解。
JDBC 程序编写流程:
2. JDBC 获取数据库连接
JDBC 实现数据库连接需要三个要素:
- Driver 接口实现类。
- URL。
- 用户名和密码。
2.1 Driver 接口实现类
Driver 接口导入:
- java.sql.Driver 接口是所有 JDBC 驱动程序都要实现的接口,不同数据库系统及厂商提供不同的实现类。
- 需要将 Driver 接口实现类的 jar 包拷贝到 Java 工程目录中。
- 静态项目,一般新建一个 lib 文件夹,拷到其中后,Add to Build Path。
- 动态项目,一般拷到 WebContent 目录的 WEB-INF 目录中的 lib 目录下。
加载与注册 Driver:
- 加载 Driver:需要调用 Class 类的静态方法 forName(),传递要加载的 JDBC Driver 类名。
- Class.forName("com.mysql.jdbc.Driver");
- 注册 Driver:需要通过 DriverManager 类管理驱动,实现注册。
- DriverManager.registerDriver(com.mysql.jdbc.Driver)。
- 一般不用调用这个方法,因为 Driver 一般会自己调用。
2.2 URL
JDBC URL:
-
JDBC URL 用于标识一个被注册的驱动程序。
-
DriverManager 通过这个 URL 获取驱动程序,建立到数据库的连接。
-
JDBC URL 组成包括:
- jdbc : 子协议 : 子名称。
- 协议:JDBC URL 的第一个字段总是 jdbc 协议。
- 子协议:用于标识一个数据库驱动程序。
- 子名称:用于标识数据库。包括:主机名 : 端口号 / 数据库名。
-
例如:jdbc:mysql://localhost:3306/test。
几种常见的 JDBC URL:
- MySQL:jdbc:mysql://主机名称:mysql服务端口号/数据库名称?参数=值&参数=值。
- Oracle:jdbc:oracle:thin:@主机名称:oracle服务端口号:数据库名称。
- SQLServer:jdbc:sqlserver://主机名称:sqlserver服务端口号:DatabaseName=数据库名称。
2.3 用户名和密码
用户名和密码可以通过属性赋值的方法提供。
2.4 数据库连接实例
1. 数据库连接代码:
public void testConnection5() throws Exception {
//1. 加载配置文件
InputStream is = ConnectionTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
Properties pros = new Properties();
pros.load(is);
//2. 读取配置信息
String user = pros.getProperty("user");
String password = pros.getProperty("password");
String url = pros.getProperty("url");
String driverClass = pros.getProperty("driverClass");
//3. 加载驱动
Class.forName(driverClass);
//4. 获取连接
Connection conn = DriverManager.getConnection(url, user, password);
System.out.println(conn);
}
- 配置代码:(位于 jdbc.properties)
user = root
password = abc123
url = jdbc:mysql://localhost:3306/test
driverClass = com.mysql.jdbc.Driver
3. JDBC PreparedStatement
3.1 JDBC 三种 CRUD 方式
JDBC Driver Manager Connection 三种方式,即 java.sql 提供了三种接口,实现对数据库的三种不同调用,分别是:
- Statement:执行静态 SQL 语句并返回结果对象。
- PreparedStatement:执行预编译 SQL 语句并返回结果对象,可以用该对象多次高效率调用预编译 SQL 语句。
- CallableStatement:执行 SQL 存储过程。
3.2 JDBC Statement 弊端
创建 Statement 对象:
- 调用 Connection 对象的 createStatement() 方法创建 Statement 对象。
执行 SQL 语句:
-
//1. 执行更新操作:INSERT, UPDATE, DELETE int executeUpdate(String sql); //2. 执行查询操作:SELECT ResultSet executeQuery(String sql);
Statement 弊端:
- 拼串操作,繁琐复杂。
- SQL 注入问题。即利用 SQL 引擎完成恶意行为。
3.3 JDBC PreparedStatement 使用
JDBC PreparedStatement 可以完全替代 Statement,实现更好的效果。
- PreparedStatement 具有更好的可读性和可维护性。
- PreparedStatement 通过预编译,可以实现性能优化。
- PreparedStatement 可以防止 SQL 注入。
创建 PreparedStatement 对象:
- 调用 Connection 对象的 preparedStatement(String sql) 方法获取对象。
- PreparedStatement 接口是 Statement 的子接口,是编译过的 SQL 语句。
- 通过对象的 setXXX() 方法设置参数,包括两个参数,是 KV 对。
Java 与 SQL 类型转换:
- 增删改操作:
public void update(String sql, Object ... args) {
Connection conn = null;
PreparedStatement ps = null;
try {
//1. 获取数据库连接
conn = JDBCUtils.getConnection();
//2. 获取 PreparedStatement 实例(或预编译语句)
ps = conn.preparedStatement(sql);
//3. 填充占位符
for (int i = 0; i < args.length; ++i) {
ps.setObject(i + 1, args[i]);
}
//4. 执行 SQL 语句
ps.execute();
} catch (Exception e) {
e.printStackTrace();
} finally {
//5. 关闭资源
JDBCUtils.closeResource(conn, ps);
}
}
- 查询操作:
public <T> T getInstance(Class<T> c, String sql, Object ... args) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
//1. 获取数据库连接
conn = JDBCUtils.getConnection();
//2. 获取 PreparedStatement 实例(或预编译语句)
ps = conn.preparedStatement(sql);
//3. 填充占位符
for (int i = 0; i < args.length; ++i) {
ps.setObject(i + 1, args[i]);
}
//4. 执行 SQL 语句
ps.execute();
//5. 得到结果集元数据
ResultSetMetaData rsmd = rs.getMetaData();
//6. 通过 rsmd 获取 columnCount, columnLabel,通过 ResultSet 获得列值
int columnCount = rsmd.getColumnCount();
if (rs.next()) {
T t = c.newInstance();
for (int i = 0; i < columnCount; ++i) {
// 7. 获取列值
Object columnVal = rs.getObject(i + 1);
// 8. 获取列的属性名
Sting columnLabel = rsmd.getColumnLabel(i + 1);
// 9. 通过反射,给对象相应属性赋值
Field field = c.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t, columnVal);
}
return t;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
//5. 关闭资源
JDBCUtils.closeResource(conn, ps, rs);
}
return null;
}
3.4 ResultSet 和 ResultSetMetaData
ResultSet:
- 查询需要调用 PreparedStatement 的 executeQuery() 方法,查询结果是 ResultSet 对象。
- ResultSet 对象以逻辑表格的形式封装了执行数据库操作的结果集。
- ResultSet 接口由数据库厂商提供。
- ResultSet 是一张数据表,有指针指向第一条记录的前面。可以调用 next() 指向下一条,返回 true 即为成功。指向当前行时,可以调用 getXXX(int index) 或 getXXX(String columnName) 获取每一列的值。
- 例如:getInt(1), getString("name")。注意 Java 与数据库交互都是 1 开始。
ResultSetMetaData:
- 可以用于获取 ResultSet 对象中列的类型和属性信息的对象。
- ResultSetMetaData meta = rs.getMetaData();
- getColumnName(int column):获取指定列名称。
- getColumnLabel(int column):获取指定列别名。
- getColumnCount():返回 ResultSet 列数。
- isNullable(int column):指定列可否为 null。
- getColumnTypeName(int column):检查指定列的数据库类型名。
- getColumnDisplaySize(int column):检查指定列的最大宽度,以字符为单位。
- isAutoIncrement(int column):是否自动编号。
ResultSet 和 ResultSetMetaData 关系:
3.5 资源释放
资源释放:
- 包括 ResultSet, Statement 和 Connection。
- 数据库连接 Connection 非常稀缺,用完要马上释放,否则可能会使服务器宕机。
- 晚创建,早释放。
- 建议在 finally 中释放,保证释放成功。
3.6 JDBC PreparedStatement API 小结
采用两种思想:
- 面向接口编程的思想。
- ORM:Object Relational Mapping 思想。
- 一个数据表对应一个 Java 类,表中一条记录对应一个 Java 对象,表中一个字段对应一个 Java 属性。
包含两种技术:
- JDBC 元数据技术:ResultSetMetaData。常用方法 getColumnCount() 和 getColumnLabel()。
- 通过反射,获取指定类对象,获取指定属性并赋值。
4. JDBC PreparedStatement 其他操作
JDBC PreparedStatement 其他操作包括:
- BLOB 类型数据操作。
- 批量数据插入操作。
4.1 BLOB 类型数据操作
BLOB 类型:
- BLOB 类型是一个二进制大型对象,可以存储大量不同大小数据。
- 插入 BLOB 类型数据必须使用 PreparedStatement。
- BLOB 类型:包括四种,根据需要选择。
向数据表中插入 BLOB:
//获取连接
Connection conn = JDBCUtils.getConnection();
String sql = "insert into customers(name,email,birth,photo)values(?,?,?,?)";
PreparedStatement ps = conn.prepareStatement(sql);
// 填充占位符
ps.setString(1, "徐海强");
ps.setString(2, "xhq@126.com");
ps.setDate(3, new Date(new java.util.Date().getTime()));
// 操作Blob类型的变量
FileInputStream fis = new FileInputStream("xhq.png");
ps.setBlob(4, fis);
//执行
ps.execute();
fis.close();
JDBCUtils.closeResource(conn, ps);
修改数据表中 BLOB:
Connection conn = JDBCUtils.getConnection();
String sql = "update customers set photo = ? where id = ?"; PreparedStatement ps = conn.prepareStatement(sql);
// 填充占位符
// 操作Blob类型的变量
FileInputStream fis = new FileInputStream("coffee.png");
ps.setBlob(1, fis); ps.setInt(2, 25);
ps.execute();
fis.close(); JDBCUtils.closeResource(conn, ps);
读取数据表中 BLOB:
String sql = "SELECT id, name, email, birth, photo FROM customer WHERE id = ?";
conn = getConnection();
ps = conn.prepareStatement(sql);
ps.setInt(1, 8);
rs = ps.executeQuery();
if (rs.next()) {
Integer id = rs.getInt(1);
String name = rs.getString(2);
String email = rs.getString(3);
Date birth = rs.getDate(4);
Customer cust = new Customer(id, name, email, birth);
System.out.println(cust);
//读取Blob类型的字段
Blob photo = rs.getBlob(5);
InputStream is = photo.getBinaryStream();
OutputStream os = new FileOutputStream("c.jpg");
byte [] buffer = new byte[1024];
int len = 0;
while((len = is.read(buffer)) != -1){
os.write(buffer, 0, len);
}
JDBCUtils.closeResource(conn, ps, rs);
if (is != null) {
is.close();
}
if (os != null) {
os.close();
}
}
4.2 批量数据插入操作
批量数据插入:
- 可以实现更高效率。
- 常用方法包括:
- addBatch(String):添加批量处理数据及参数。
- executeBatch():执行批量处理。
- clearBatch():清空缓存数据。
- 相关场景:
- 多条 SQL 语句批量处理。
- 一条 SQL 语句批量传参。
批量插入实例:
-
要求:向数据表中插入 20000 条数据。
-
CREATE TABLE goods( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20) );
-
public void testInsert2() throws Exception{ long start = System.currentTimeMillis(); Connection conn = JDBCUtils.getConnection(); //1.设置为不自动提交数据 conn.setAutoCommit(false); String sql = "insert into goods(name)values(?)"; PreparedStatement ps = conn.prepareStatement(sql); for (int i = 1;i <= 1000000;i++) { ps.setString(1, "name_" + i); //1.“攒”sql ps.addBatch(); if (i % 500 == 0) { //2.执行 ps.executeBatch(); //3.清空 ps.clearBatch(); } } //2.提交数据 conn.commit(); long end = System.currentTimeMillis(); System.out.println("花费的时间为:" + (end - start)); //1000000条:4978 JDBCUtils.closeResource(conn, ps); }
5. JDBC 数据库事务
5.1 JDBC 事务操作
JDBC 事务处理:
-
Connection 对象提供事务功能。
- setAutoCommit(false):取消自动提交。
- commit():提交事务。
- rollback():事务回滚。
-
要求:用户 AA 向用户 BB 转账 100 元。
-
public void testJDBCTransaction() { Connection conn = null; try { // 1.获取数据库连接 conn = JDBCUtils.getConnection(); // 2.开启事务 conn.setAutoCommit(false); // 3.进行数据库操作 String sql1 = "update user_table set balance = balance - 100 where user = ?"; update(conn, sql1, "AA"); // 模拟网络异常 //System.out.println(10 / 0); String sql2 = "update user_table set balance = balance + 100 where user = ?"; update(conn, sql2, "BB"); // 4.若没有异常,则提交事务 conn.commit(); } catch (Exception e) { e.printStackTrace(); // 5.若有异常,则回滚事务 try { conn.rollback(); } catch (SQLException e1) { e1.printStackTrace(); } } finally { try { // 6.恢复每次DML操作的自动提交功能 conn.setAutoCommit(true); } catch (SQLException e) { e.printStackTrace(); } // 7.关闭连接 JDBCUtils.closeResource(conn, null, null); } } //使用事务以后的通用的增删改操作(version 2.0) public void update(Connection conn ,String sql, Object... args) { PreparedStatement ps = null; try { // 1.获取PreparedStatement的实例 (或:预编译sql语句) ps = conn.prepareStatement(sql); // 2.填充占位符 for (int i = 0; i < args.length; i++) { ps.setObject(i + 1, args[i]); } // 3.执行sql语句 ps.execute(); } catch (Exception e) { e.printStackTrace(); } finally { // 4.关闭资源 JDBCUtils.closeResource(null, ps); } }
5.2 JDBC 事务隔离级别设置
事务隔离级别:
- 事务 ACID 性质,需要隔离以提供并发时的数据一致性保证。
- 隔离级别越高,数据一致性越好,并发性越差。
- 数据库提供四种隔离级别:Oracle 支持 read commited 和 serializable。MySQL 支持四种。
JDBC 事务隔离命令:
- SELECT @@tx_isolation:查看当前隔离级别。
- set transaction isolation level read committed:设置当前隔离级别。
- set global transaction isolation level read committed:设置全局隔离级别。
6. JDBC DAO 及相关实现类
6.1 DAO 概念
DAO:Data Access Object:访问信息数据类和接口
- DAO 包括了对数据的 CRUD:Create Retrival Update Delete 操作,而不包含任何业务信息,也称作 BaseDAO。
- 作用是实现功能的模块化,有利于代码维护和升级。
6.2 DAO 样例
7. JDBC 数据库连接池
7.1 数据库连接池概念
传统数据库连接:
- 基本步骤是主程序建立连接,SQL 操作,断开连接。
- 连接耗费时间较大,无法控制连接个数,用完必须断开连接。
数据库连接池:
- 让应用使用现有数据库连接,而不是重新建立连接。
- 优点:资源重用,快速,避免独占,连接管理。
常用数据库连接池开源实现:
- DBCP
- C3P0
- Proxool
- BoneCP
- Druid
7.2 数据库连接池常用命令
8. JDBC Apache-DBUtils CRUD 操作
8.1 Apache-DBUtils 概念
Apache-DBUtils:
- commons-dbutils 是 Apache 提供的一个开源 JDBC 工具类库。
- 它对 JDBC 进行简单封装,学习简单易操作。
API 包:
- org.apache.commons.dbutils.QueryRunner:提供数据库操作的一系列重载 update() 和 query() 操作。
- org.apache.commons.dbutils.ResultSetHandler:处理数据库查询结果集。
- org.apache.commons.dbutils.DbUtils:
8.2 Apache-DBUtils 常用命令
8.2.1 DbUtils
DbUtils:
- 提供一系列静态方法,包括关闭连接、装载驱动程序等常规操作的工具类。
- public static void close() throws java.sql.SQLException
- public static void closeQuery()
- public static void commitAndClose(Connection conn) throws SQLException
- public static void commitAndCloseQuietly(Connection conn)
- public static void rollback(Connection conn) throws SQLException
- public static void rollbackAndClose(Connection conn) throws SQLException
- public static void rollbackAndCloseQuietly(Connection)
- public static boolean loadDriver(java.lang.String driverClassName):装载并注册 JDBC 驱动。
8.2.2 QueryRunner
QueryRunner:
- 与 ResultSetHandler 结合,完成大部分数据库操作,大大减少编码量。
- 提供两种构造器:默认构造器,需要 javax.sql.DataSource 做参数的构造器。
- public int update(Connection conn, String sql, Object ... params) throws SQLException:执行更新操作。
- public T insert(Connection conn, String sql, ResultSetHandler rsh, Object ... params) throws SQLException:仅支持 INSERT 插入语句的操作。
- public int[] batch(Connection conn, String sql, Object ... params) throws SQLException:批量进行 INSERT, UPDATE, DELETE。
- public T insertBatch(Connection conn, String sql, ResultSetHandler rsh, Object ... params):批量进行 INSERT。
- public Object query(Connection conn, String sql, ResultSetHandler rsh, Object ... params) throws SQLException:执行查询操作。
添加操作:
public void testInsert() throws Exception {
QueryRunner runner = new QueryRunner();
Connection conn = JDBCUtils.getConnection3();
String sql = "INSERT INTO customers(name, email, birth) VALUES (?, ?, ?)";
int count = runner.update(conn, sql, "成龙", "abc@qq.com", "1990-1-1" );
System.out.println("添加了" + count + "条记录");
JDBCUtils.closeResource(conn, null);
}
删除操作:
public void testDelete() throws Exception {
QueryRunner runner = new QueryRunner();
Connection conn = JDBCUtils.getConnection3();
String sql = "DELETE FROM customers WHERE id < ?";
int count = runner.remove(conn, sql, 3);
System.out.println("删除了" + count + "条记录");
JDBCUtils.closeResource(conn, null);
}
8.2.3 ResultSetHandler
ResultSetHandler:
- 用于处理 java.sql.ResultSet,转换数据格式。
- Object handler(java.sql.ResultSet.rs)
- ArrayHandler:将结果第一行数据转换为对象数组。
- ArrayListHandler:将结果每一行数据都转换为数组,存放在 List 中。
- BeanHandler:将结果第一行数据封装到 JavaBean 实例中。
- BeanListHandler:将结果每一行数据都封装到 JavaBean 实例中,存放在 List 中。
- ColumnListHandler:存放列到 List 中。
- KeyedHandler:将结果每一行存放在 Map 中,再存放在 Map 中,并指定对应 Key。
- MapHandler:将结果第一行数据封装在 Map 中,指定 KV。
- MapListHandler:将结果每一行数据都封装到 Map 中,存放在 List 中。
- ScalarHandler:查询单个值对象。
通过 ResultSetHandler 的实现类 BeanHandler 实现查询:
public void testQueryInstance() throws Exception{
QueryRunner qr = new QueryRunner();
Connection conn = JDBCUtils.getConnection5();
String sql = "SELECT id,name,email,birth FROM customers WHERE id = ?";
BeanHandler<customer> handler = new BeanHandler<>(Customer.class);
Customer customer = qr.query(conn, sql, handler, 23);
system.out.println(customer);
JDBCUtils.closeResource(conn, null);
}
通过 ResultSetHandler 的实现类 BeanListHandler 实现多条记录查询:
public void testQueryInstance() throws Exception{
QueryRunner qr = new QueryRunner();
Connection conn = JDBCUtils.getConnection5();
String sql = "SELECT id,name,email,birth FROM customers WHERE id < ?";
BeanListHandler<customer> handler = new BeanListHandler<>(Customer.class);
List<Customer> list = qr.query(conn, sql, handler, 23);
list.forEach(System.out::println);
JDBCUtils.closeResource(conn, null);
}
自定义 ResultSetHanlder 实现类:
public void testQueryInstance() throws Exception{
QueryRunner qr = new QueryRunner();
Connection conn = JDBCUtils.getConnection5();
String sql = "SELECT id,name,email,birth FROM customers WHERE id = ?";
BeanListHandler<customer> handler = new ResultSetHandler<Customer>() {
@Override
public Customer handle(ResultSet rs) throws SQLException {
System.out.println("handle");
if (rs.next()) {
int id = rs.getInt("id");
String name = rs.getString("name");
String email = rs.getString("email");
Date birth = rs.getDate("birth");
return new Customer(id, name, email, birth);
}
return null;
}
};
Customer customer = qr.query(conn, sql, handler, 23);
System.out.println(customer);
JDBCUtils.closeResource(conn, null);
}
通过 ResultSetHandler 的实现类 ScalarHandler 实现最大、最小、平均、总和的查询。
public void testQueryInstance() throws Exception{
QueryRunner qr = new QueryRunner();
Connection conn = JDBCUtils.getConnection5();
String sql = "SELECT max(birth) FROM customers";
ScalarHandler handler = new ScalarHandler();
Date birth = (Date) qr.query(conn, sql, handler);
System.out.println(birth);
JDBCUtils.closeResource(conn, null);
}
9. JDBC 小结
JDBC 常规操作包括:
public void testUpdateWithTx() {
Connection conn = null;
try {
// 获取连接操作:
// 1. 手写连接:JDBCUtils.getConnection();
// 2. 基于连接池:C3P0, DBCP, Druid
// 对数据表进行一系列 CRUD 操作:
// 1. 使用 PreparedStatement 提供的增删改查
// 2. 使用 DBUtils 提供的 jar 包中的 QueryRunner 的增删改查
// 提交数据
conn.commit();
} catch (Exception e) {
e.printStackTrace();
try {
// 回滚数据
conn.rollback();
} catch (SQLExcpetion e1) {
e1.printStackTrace();
}
} finally {
// 关闭连接等操作
// 1. 使用 JDBCUtils.closeResource();
// 2. 使用 DBUtils 提供的 jar 包中的 DbUtils 类提供的关闭操作
}
}
10. 相关单词
JDBC 相关的单词包括:
- JDBC:Java DataBase Connectivity:Java 数据库连接。
- DriverManager:驱动管理器。
- statement:语句。
- execute:执行。
- autocommit:自动提交。
- metadata:元数据。
- idle:闲置的。
- acquire:获取。
- scalar:标量的,标量。
Comments | NOTHING