JDBC 基本用法
常用接口和类简介
DriverManager 类
用于管理 JDBC 驱动的服务类。程序中使用该类的主要功能是获取 Connection 对象
public static synchronized Connection getConnection(String url,String user,String pass) throws SQLException获取url 对应的数据库连接
Connection
代表数据库连接对象， 每个Connection 代表一个物理连接会话
该接口常用的方法如下
Statement createStatement() throws SQLException该方法返回一个Statement对象PreparedStatement prepareStatement(String sql) throws SQLException该方法返回预编译的Statement对象CallableStatement prepareCall(String sql) throws该方法返回CallableStatement对象，该对象用于调用存储过程void setNetworkTimeout(Executor executor, int milliseconds) throws SQLException设置数据库连接的超时时间int getNetworkTimeout() throws SQLException获取数据库连接的超时时间
控制事务的方法
Savepoint setSavepoint()创建一个保存点Savepoint setSavepoint(String name)以指定名字来创建一个保存点void setTransactionIsolation(int level)设置事务的隔离级别void rollback()回滚事务void rollback(Savepoint savepoint)将事务回归到指定的保存点void setAutoCommit(boolean autoCommit)关闭自动提交，打开事务void commit()提交事务
Statement
用于执行 SQL 语句的工具接口。可用于执行 DDL，DCL，DML 语句，也可用于执行SQL 查询。
常用方法如下
ResultSet executeQuery(String sql) throws SQLException该方法用于执行查询语句，并返回查询结果对应的ResultSet对象。int executeUpdate(String sql) throws SQLException该方法用于执行 DML 语句并返回受影响的行数。也可以执行DDL语句，执行DDL语句将返回0boolean execute(String sql) throws SQLException该方法可执行任何 SQL 语句。如果执行后的第一个结果为ResultSet对象，则返回true； 如果执行后第一个结果为受影响行数，或没有任何结果，则返回falsevoid closeOnCompletion() throws SQLException该Statement的ResultSet关闭时 ，该Statement会自动关闭(Java 7)boolean isCloseOnCompletion() throws SQLException判断Statement是否打开了closeOnCompletion(Java 7)long executeLargeUpdate(String sql) throws SQLException相当于增强版的executeUpdate,影响行数大于Integer.MAX_VALUE时，应该使用此方法getResultSet()获取该Statement 执行查询语句所返回的ResultSet对象getUpdateCount()获取该Statement执行 DML 语句所影响的记录行数
PreparedStatement
预编译的 Statement 对象。PreparedStatement 是 Statement 的子接口，它允许数据库预编译SQL 语句，以后每次只改变SQL 命令的参数，避免数据库每次都需要编译 SQL 语句，因此性能更好。
相对于 Statement 而言，使用 PreparedStatement 执行SQL 语句时，无需再传入 SQL 语句，只要为预编译的SQL 语句传入参数值即可。
所以它比 Statement 多了如下方法
void setXxx(int parameterIndex,Xxx value)该方法根据传入参数值的类型不同，需要使用不同的方法。传入的值根据索引传给SQL 语句中指定位置的参数
ResultSet
结果集对象。该对象包含访问查询结果的方法，ResultSet 可以通过列索引或列名获取列数据。
它包含了如下常用方法来移动记录指针
void close()释放ResultSet对象boolean absolute(int row)讲将结果集的记录指针移动到第row 行，如果row是负数，则移动到倒数第row行。如果移动后的记录指针指向一条有效记录，则该方法返回truevoid beforeFirst()将ResultSet的记录指针定位到首行之前，这是Result结果集记录指针的初始状态（记录指针的的起始位置位于第一行之前）boolean first()将ResultSet的记录指针定位到首航。如果移动后的记录指针指向一条有效记录，则该方法返回trueboolean previous()将ResultSet的记录指针定位到上一行。如果移动后的记录指针指向一条有效记录，则该方法返回trueboolean next()将ResultSet的记录指针定位到下一行。如果移动后的记录指针指向一条有效记录，则该方法返回trueboolean last()将ResultSet的记录指针定位到最后一行。如果移动后的记录指针指向一条有效记录，则该方法返回trueboolean afterLast()将ResultSet的记录指针定位到最后一行之后int getRow()获取当前行号
当把记录指针移动到指定行之后，ResultSet 可通过getXxx(int columnIndex) 或 getXxx(String columnLable) 来获取当前行、指定列的值
JDBC 编程步骤
- 引入MySQL的JDBC驱动依赖
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.28</version>
<scope>runtime</scope>
</dependency>
- 通过
DriverManager获取数据库连接
当使用DriverManger 获取数据库连接时，通常需要传入三个参数：数据库URL、用户名、密码
URL 通常遵循如下写法
jdbc:subprotocol:other stuff
上述写法中 jdbc 是固定的，而subprotocol 指定连接到特定数据库的驱动，后面other stuff则不是固定的，不同数据库的URL写法可能存在较大差异
Mysql的URL写法
jdbc:mysql://hostname:port/databasename
Oracle 数据库的URL写法
jdbc:oracle:thin:@hostname:port:databasename
-
通过
Connection对象创建Statement对象 -
使用
Statement执行SQL 语句 -
操作结果集
代码示例
public class Main {
public static void main(String[] args) throws Exception {
try (
// 使用DriverManager 获取数据库连接
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");
// 使用 Connection 来创建一个 Statement对象
Statement statement = conn.createStatement();
// 执行SQL 查询语句 拿到结果集
ResultSet rs = statement.executeQuery("select * from students");
) {
// 遍历结果集 输出打印结果
while (rs.next()) {
System.out.print(rs.getInt(1) + "\t");
System.out.print(rs.getString(2) + "\t");
System.out.print(rs.getInt(3) + "\t");
System.out.println(rs.getString(4) + "\t");
}
}
}
}
输出
1 小红 23 女
2 小兰 22 女
3 小鹏 20 男
4 小绿 21 男
5 小花 22 女
6 小强 24 男
7 小五 23 男
执行SQL 语句的方式
使用 executeUpdate 方法执行 DDL 和 DML 语句
简单封装一个DBUtil 类
public class DBUtil {
private String url;
private String username;
private String password;
public DBUtil(String url, String username, String password) {
this.url = url;
this.username = username;
this.password = password;
}
// 封装一个执行DDL和DML的方法
public int execute(String sql) throws SQLException {
try (
Connection conn = DriverManager.getConnection(this.url, this.username, this.password);
Statement stmt = conn.createStatement();
) {
// 返回修改行数，如果是DDL则返回0
return stmt.executeUpdate(sql);
}
}
public void printData(String sql) throws SQLException {
try (
Connection conn = DriverManager.getConnection(this.url, this.username, this.password);
Statement stmt = conn.createStatement();
) {
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
System.out.print(rs.getInt(1) + "\t");
System.out.println(rs.getString(2) + "\t");
}
}
}
public static void main(String[] args) throws SQLException {
DBUtil db = new DBUtil("jdbc:mysql://localhost:3306/test", "root", "123456");
// 创建表
db.execute("CREATE TABLE `books` (\n" +
" `id` int(0) NOT NULL AUTO_INCREMENT,\n" +
" `name` varchar(20) NULL,\n" +
" PRIMARY KEY (`id`)\n" +
");");
System.out.println("建表成功");
// 插入一条记录
int dmlRes = db.execute("INSERT into books (`name`) values(\"朝花夕拾\");");
System.out.println("修改行数：" + dmlRes);
// 打印结果
db.printData("select * from books");
}
}
输出
建表成功
修改行数：1
1 朝花夕拾
使用 execute 方法执行SQL 语句
Statement 的 execute() 方法几乎可以执行任何SQL 语句，根据返回判断是否返回了 ResultSet 对象，再通过getResultSet() 和 getUpdateCount() 获取查询语句返回的ResultSet 对象 或 执行DML 语句返回的影响记录行数
// 判断是否返回ResultSet对象，不是则输出影响记录行数
if (!statement.execute("INSERT into books (`name`) values(\"朝花夕拾\");")) {
System.out.println("影响记录行数" + statement.getUpdateCount());
}
// 判断是否返回ResultSet对象，是则输出结果
if (statement.execute("select * from students")) {
try (
// 获取该Statement 执行查询语句所返回的 ResultSet 对象
ResultSet rs = statement.getResultSet();
) {
// 遍历输出
while (rs.next()) {
System.out.print(rs.getInt(1) + "\t");
System.out.println(rs.getString(2) + "\t");
}
}
}
输出
影响记录行数1
1 小红
2 小兰
3 小鹏
4 小绿
5 小花
6 小强
7 小五
使用PreparedStatement 执行SQL 语句
PreparedStatement 是 Statement 接口的子接口，使用它可以预编译SQL语句，预编译后的SQL 语句被存储在PreparedStatement 对象中，然后可以高效的执行该语句，还能有效防止SQL 注入
PreparedStatement 使用的SQL字符串 可以包含占位符，例如
insert into students values(null, ?, 1)
使用占位符时，PreparedStatement 提供了一系列的 setXxx(int index,Xxx value) 方法来传入参数值。
PreparedStatement 也提供了execute()、executeUpdate()、executeQuery() 三个方法来执行SQL 语句，不过这三个方法无需参数，因为已存储了预编译的SQL 语句
public class PreparedDemo {
public static void main(String[] args) throws Exception {
try (
// 使用DriverManager 获取数据库连接
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");
// 使用 Connection 来创建一个 Statement对象
PreparedStatement pstmt = conn.prepareStatement("INSERT into books (`name`) values(?)");
) {
pstmt.setString(1, "西游记");
pstmt.execute();
}
}
}
使用 CallableStatement 调用存储过程
创建一个简单的存储过程
CREATE PROCEDURE add_pro(a int,b int, out sum int)
BEGIN
SET sum = a + b;
END;
调用存储过程通过 Connection 的 prepareCall() 方法来创建CallableStatement 对象，创建该对象时需要传入调用存储过程 SQL 语句。
调用存储过程的SQL 语句格式：{call 过程名(?,?,?)}，其中?为存储过程参数的占位符
conn.prepareCall("{call add_pro(?,?,?)}");
存储过程的参数既有传入参数，也有传出参数。 可以通过CallableStatement 的 setXxx() 方法为传入参数设置值；传出参数就是 Java 程序可以通过该参数获取存储过程里的值，需要调用CallableStatement 的registerOutParameter() 方法来注册该参数
cstmt.registerOutParameter(3, Types.INTEGER);
代码案例
public class CallableStatementTest {
public static void main(String[] args) throws Exception {
try (
// 使用DriverManager 获取数据库连接
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");
// 调用存储过程，通过占位符传递参数
CallableStatement cstmt = conn.prepareCall("{call add_pro(?,?,?)}");
) {
cstmt.setInt(1,12);
cstmt.setInt(2,22);
// 注册 out 参数
cstmt.registerOutParameter(3, Types.INTEGER);
cstmt.execute();
System.out.println("执行结果："+ cstmt.getInt(3));
}
}
}
输出
执行结果：34
管理结果集
可更新的结果集
以默认方式打开的 ResultSet 是不可更新的，如果希望创建可更新的 ResultSet，则必须在创建Statement 或PreparedStatement 时传入额外的参数。
Connection 在创建Statement 或 PreparedStatement 时可以额外传入以下两个参数
ResultSetType控制ResultSet的类型，该参数可以取如下三个值ResultSet.TYPE_FORWARD_ONLY该常量控制记录指针只能向前移动ResultSet.TYPE_SCROLL_INSENSITIVE该常量控制记录指针可以自由移动，但底层数据的改变不会影响ResultSet的内容ResultSet.TYPE_SCROLL_SENSITIVE该常量控制记录指针可以自由移动，而且底层数据的改变会影响ResultSet的内容
resultSetConcurrency控制ResultSet的并发类型，该参数可以接受如下两个值ResultSet.CONCUR_READ_ONLY该常量只是ResultSet是只读的并发模式（默认）ResultSet.CONCUR_UPDATABLE该常量只是ResultSet是可更新的并发模式
需要指出的是，可更新的结果集还需要满足如下两个条件
- 所有数据都应该来自一个表
- 选出的数据集必须包含主键列
ResultSet 提供了如下方法用来修改记录指针所指记录、特定列的值
updateXxx(int columnIndex,Xxx value)修改指定索引号的列的值updateXxx(String columnLabel,Xxx value)修改指定索列名的值
最后需要调用 updateRow() 方法来提交当前行的修改
public class ResultSetTest {
public static void main(String[] args) throws Exception {
try (
// 使用DriverManager 获取数据库连接
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");
// 使用 Connection 来创建一个 Statement对象，设置结果集可滚动，可更新
PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM `students`", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet rs = pstmt.executeQuery();
) {
while (rs.next()) {
// 修改第名称字段的值为 名称+行号
rs.updateString("name", rs.getString(2) + rs.getRow());
// 提交修改
rs.updateRow();
}
}
}
}
使用 ResultSetMetaData 分析结果集
ResultSet 里包含一个getMetaData() 方法，该方法返回该ResultSet 对应的 ResultSetMetaData 对象，我们可以通过 ResultSetMetaData 对象来获取 ResultSet 里包含了哪些数据列，以及每个数据列的数据类型
常用的方法有如下三个
int getColumnCount()返回该ResultSet的列数量String getColumnName(int column)返回指定索引的列名int getColumnType(int column)返回指定索引的列类型
public static void main(String[] args) throws Exception {
try (
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");
Statement statement = conn.createStatement();
ResultSet rs = statement.executeQuery("select * from students");
) {
ResultSetMetaData metaData = rs.getMetaData();
// 获取列数量
int columns_num = metaData.getColumnCount();
for (int i = 0; i < columns_num; i++) {
// 打印列名
System.out.print(metaData.getColumnName(i + 1) + "\t");
// 获取枚举类型 返回值参考 java.sql.Types 类
System.out.println(metaData.getColumnType(i + 1) + "\t");
}
}
}
输出
id 4
name 12
age 4
sex 12
使用 RowSet 包装结果集
RowSet 接口继承了ResultSet 接口， RowSet 接口下包含JdbcRowSet，CachedRowSet，FilteredRowSet、JoinRowSet 和 WebRowSet 常用子接口，除了JdbcRowSet 需要保持与数据库的连接之外，处于4个子接口都是离线的RowSet，无需保持与数据库的连接
RowSetFactory 与 RowSet
Java 7 新增了 RowSetProvider 类和 RowSetFactory 接口， 其中 RowSetProvider 负责创建 RowSetFactory，而 RowSetFactory 则提供了如下方法来创建 RowSet 实例
CachedRowSet createCachedRowSet()创建一个默认的CachedRowSetFilteredRowSet createFilteredRowSet()创建一个默认的FilteredRowSetJdbcRowSet createJdbcRowSet()创建一个默认的JdbcRowSetJoinRowSet createJoinRowSet()创建一个默认的JoinRowSetWebRowSet createWebRowSet()创建一个默认的WebRowSet
下面程序通过 RowSetFactory 示范了使用JdbcRowSet 的可滚动性、可修改特性
public class RowSetTest {
public static void main(String[] args) throws Exception {
RowSetFactory factory = RowSetProvider.newFactory();
try (
JdbcRowSet rowSet = factory.createJdbcRowSet();
) {
// 配置连接信息
rowSet.setUrl("jdbc:mysql://localhost:3306/test");
rowSet.setUsername("root");
rowSet.setPassword("123456");
// 设置 SQL 查询语句
rowSet.setCommand("select * from students");
// 执行查询
rowSet.execute();
while (rowSet.next()) {
System.out.print(rowSet.getInt(1) + "\t");
System.out.println(rowSet.getString(2) + "\t");
if (rowSet.getInt(1) == 1) {
// 修改指定记录行
rowSet.updateString(2, "孙悟空");
rowSet.updateRow();
}
}
}
}
}
离线RowSet
使用 离线 RowSet 可以避免Connection 一旦关闭，再通过 ResultSet 访问数据引发异常的情况，离线RowSet直接将底层数据读入内存中，封装成 RowSet 对象，可以当作 Java Bean 使用
CachedRowSet 是所有离线RowSet 的父接口，并且该CachedRowSet 还支持了如下方法来控制分页
populate(ResultSet rs,int startRow)使用给定的ResultSet装填RowSet，从ResultSet的第startRow条记录开始状态setPageSize(int pageSize)设置CachedRowSet每次返回多少条记录previousPaeg()在底层ResultSet可用的情况下，让CachedRowSet读取上一页记录nextPage()在底层ResultSet可用的情况下，读取下一页记录
public class CachedRowSetTest {
private String url = "jdbc:mysql://localhost:3306/test";
private String username = "root";
private String password = "123456";
public CachedRowSet query(String sql, int pageSize, int page) throws Exception {
try (
Connection conn = DriverManager.getConnection(url, username, password);
Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
ResultSet rs = stmt.executeQuery(sql);
) {
RowSetFactory factory = RowSetProvider.newFactory();
CachedRowSet cachedRowSet = factory.createCachedRowSet();
// 设置每页记录数量
cachedRowSet.setPageSize(pageSize);
// 根据页和每页记录数 计算出从第几条记录开始
cachedRowSet.populate(rs, (page - 1) * pageSize + 1);
return cachedRowSet;
}
}
public static void main(String[] args) throws Exception {
CachedRowSetTest test = new CachedRowSetTest();
CachedRowSet rs = test.query("select * from students",2,3);
while (rs.next()){
System.out.println(rs.getInt(1) + "\t" + rs.getString(2));
}
}
}
事务处理
JDBC 连接的事务支持由 Connection 提供， Connection 默认打开自动提交，即关闭事务
可以调用 Connection 的 setAutoCommit(boolean autoCommit) 方法来关闭自动提交
// 关闭自动提交，开启事务
conn.setAutoCommit(false);
当程序执行完 DML 语句后，需要调用Connection 的 commit() 方法来提交事务
// 提交事务
conn.commit();
如果 SQL语句 执行失败，可以用 Connection 的 rollback() 方法来回滚事务
// 回滚事务
conn.rollback();
实例
public class TransactionTest {
private static String url = "jdbc:mysql://localhost:3306/test";
private static String username = "root";
private static String password = "123456";
public static void main(String[] args) throws Exception {
try (
Connection conn = DriverManager.getConnection(url, username, password);
) {
// 关闭自动提交
conn.setAutoCommit(false);
try (Statement stmt = conn.createStatement()) {
// 下面模拟转账过程 张三向李四转账20元
stmt.execute("update account set money = money - 20 where `name` = '张三'");
// 报错
int i = 1 / 0;
stmt.execute("update account set money = money + 20 where `name` = '李四'");
} catch (Exception ex) {
ex.printStackTrace();
// 事务回滚
conn.rollback();
}
}
}
}
Connection 也提供了设置中间点的方法
Savepoint setSavepoint()在当前事务中创建一个未命名的中间点，并返回代表该中间点的 Savepoint 对象Savepoint setSavepoint(String name)在当前事务中创建一个具有指定名称的中间点，并返回代表该中间点的 Savepoint 对象rollback(Savepoint savepoint)回滚到指定中间点
批量更新
通过使用Statement 对象的addBatch() 方法将多条SQL语句收集起来，然后调用executeBatch()或 executeLargeBatch() 方法同时执行这些SQL 语句
Statement stmt = conn.createStatement();
// 添加多条SQL语句
stmt.addBatch(sql1);
stmt.addBatch(sql2);
stmt.addBatch(sql3);
// 批量更新
stmt.executeBatch();
使用连接池
数据库连接的建立及关闭是极耗费系统资源的操作，在多层架构的应用环境中，这种资源的耗费对系统性能影响尤为明显。
数据库连接池的解决方案是：当应用程序启动时，系统主动建立足够的数据库连接，并将这些连接组成一个连接池。每次请求数据库连接时，无需重新打开连接，而是从连接池中取走已有的连接使用，使用完不再关闭连接，而是归还给连接池。
引入C3P0 数据源
<dependency>
<groupId>com.mchange</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.5.5</version>
</dependency>
代码示例
public class C3P0Test {
public static void main(String[] args) throws Exception {
// 创建连接池实例
ComboPooledDataSource ds = new ComboPooledDataSource();
// 设置连接数据库的URL
ds.setJdbcUrl("jdbc:mysql://localhost:3306/test");
// 设置数据库用户名
ds.setUser("root");
// 设置用户名的密码
ds.setPassword("123456");
// 设置最大连接数
ds.setMaxPoolSize(40);
// 设置最小连接数
ds.setMinPoolSize(2);
// 设置初始化线程数
ds.setInitialPoolSize(10);
// 设置连接池的缓存Statement 的最大数
ds.setMaxStatements(180);
try (
// 获取连接
Connection conn = ds.getConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select * from students");
) {
while (rs.next()) {
System.out.print(rs.getInt(1) + "\t");
System.out.println(rs.getString(2) + "\t");
}
}
}
}
