Java JDBC

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语句将返回0
• boolean execute(String sql) throws SQLException 该方法可执行任何 SQL 语句。如果执行后的第一个结果为 ResultSet 对象，则返回true； 如果执行后第一个结果为受影响行数，或没有任何结果，则返回false
• void 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行。如果移动后的记录指针指向一条有效记录，则该方法返回true
• void beforeFirst() 将 ResultSet 的记录指针定位到首行之前，这是Result 结果集记录指针的初始状态（记录指针的的起始位置位于第一行之前）
• boolean first() 将ResultSet 的记录指针定位到首航。如果移动后的记录指针指向一条有效记录，则该方法返回true
• boolean previous() 将ResultSet 的记录指针定位到上一行。如果移动后的记录指针指向一条有效记录，则该方法返回true
• boolean next() 将 ResultSet 的记录指针定位到下一行。如果移动后的记录指针指向一条有效记录，则该方法返回true
• boolean last() 将 ResultSet 的记录指针定位到最后一行。如果移动后的记录指针指向一条有效记录，则该方法返回true
• boolean afterLast() 将 ResultSet 的记录指针定位到最后一行之后
• int getRow() 获取当前行号

当把记录指针移动到指定行之后，ResultSet 可通过getXxx(int columnIndex) 或 getXxx(String columnLable) 来获取当前行、指定列的值

JDBC 编程步骤

1. 引入MySQL的JDBC驱动依赖

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.28</version>
    <scope>runtime</scope>
</dependency>

2. 通过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

3. 通过 Connection 对象创建Statement 对象

4. 使用Statement 执行SQL 语句

5. 操作结果集

代码示例

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() 创建一个默认的 CachedRowSet
• FilteredRowSet createFilteredRowSet() 创建一个默认的FilteredRowSet
• JdbcRowSet createJdbcRowSet() 创建一个默认的 JdbcRowSet
• JoinRowSet createJoinRowSet() 创建一个默认的 JoinRowSet
• WebRowSet 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");
            }
        }
    }
}

本栏目推荐文章
• 深入探索JVM：理解Java程序在虚拟机中的存储和管理
• 用jacoco统计JAVA项目测试代码覆盖率
• Java实现在线编辑预览office文档
• Java小细节之缩进中if 和else的对应关系
• 春眠不觉晓，Java数据类型知多少？基础牢不牢看完本文就有数了
• mysql发生连接异常Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
• idea 中java代码修改后运行代码不生效
• Java日期时间处理详解
• java项目(jar包)配置为开机自启 jar包bat脚本启动和配置为开机自启
• Java中锁的解决方案