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 SQLExceptionStatementResultSet 关闭时 ,该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>
  1. 通过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
  1. 通过 Connection 对象创建Statement 对象

  2. 使用Statement 执行SQL 语句

  3. 操作结果集

代码示例

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 语句

Statementexecute() 方法几乎可以执行任何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;

调用存储过程通过 ConnectionprepareCall() 方法来创建CallableStatement 对象,创建该对象时需要传入调用存储过程 SQL 语句。

调用存储过程的SQL 语句格式:{call 过程名(?,?,?)},其中?为存储过程参数的占位符

conn.prepareCall("{call add_pro(?,?,?)}");

存储过程的参数既有传入参数,也有传出参数。 可以通过CallableStatementsetXxx() 方法为传入参数设置值;传出参数就是 Java 程序可以通过该参数获取存储过程里的值,需要调用CallableStatementregisterOutParameter() 方法来注册该参数

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,则必须在创建StatementPreparedStatement 时传入额外的参数。

Connection 在创建StatementPreparedStatement 时可以额外传入以下两个参数

  • 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 接口下包含JdbcRowSetCachedRowSetFilteredRowSetJoinRowSetWebRowSet 常用子接口,除了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 默认打开自动提交,即关闭事务

可以调用 ConnectionsetAutoCommit(boolean autoCommit) 方法来关闭自动提交

// 关闭自动提交,开启事务
conn.setAutoCommit(false);

当程序执行完 DML 语句后,需要调用Connectioncommit() 方法来提交事务

// 提交事务
conn.commit();

如果 SQL语句 执行失败,可以用 Connectionrollback() 方法来回滚事务

// 回滚事务
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");
            }
        }
    }
}

热门相关:学霸的黑科技系统   我在末世有套房   神武觉醒   女人的战争之奉天洞的混战   美味的交换