13-MySQL数据库与JDBC编程

发表于 | 更新于 | 分类于

SQL 语句基础

标准的 SQL 语句通常可以分为几种类型：

  • 查询语句：主要由 select 关键字完成。
  • DML(Data Manipulation Language，数据操作语言)：主要由 insert、update 和 delete 完成。
  • DDL(Data Definition Language，数据定义语言)：主要由 create、alter、drop 和 truncate 完成。
  • DCL(Data Control Language，数据控制语言)：主要由 grant 和 revoke 完成。
  • 事务控制语句：主要由 commit、rollback 和 savepoint 完成。

JDBC 常用接口和类简介

  • DriverManager：用于管理 JDBC 驱动的服务类。程序中使用该类的主要功能是获取 Connection 对象。
  • Connection：代表数据库连接对象，每一个 Connection 代表一个物理连接。该接口常用方法有：
    • Statement createStatement()：返回一个 Statement 对象。
    • PrepareStatement prepareStatement(String sql)：返回预编译的 Statement 对象。
    • CallableStatement prepareCall(String sql)：返回 CallableStatement 对象，该对象用于调用存储过程。
    • Connection 还有如下几个用于控制事务的方法
      • Savepoint setSavepoint()：创建一个保存点。
      • Savepoint setSavepoint(String name)：以指定名字来创建一个保存点。
      • void setTransactionIsolation(int level)：设置事务的隔离级别。
      • void rollback()：回滚事务。
      • void rollback(Savepoint savepoint)：将事务回滚到指定的保存点。
      • void setAutoCommit(boolean autoCommint)：关闭自动提交、打开事务。
      • void commit()：提交事务。
      • setSchema(String schema) 和 getSchema() 两个方法用于控制该 Connection 访问的数据库 Schema。
      • setNetworkTimeout(Executor execupor) 和 getNetworkTimeout 两个方法用于控制数据库连接的超时行为。
  • Statement：用于执行 SQL 语句的工具接口。当执行查询语句时，返回查询到的结果集。其常用方法如下：
    • ResultSet executeQuery(String sql)：用于执行查询语句，并返回查询结果对应的 ResultSet 对象。
    • int executeUpdate(String sql)：用于执行 DML 语句，并返回受影响的行数。
    • boolean execute(String sql)：执行任何 SQL 语句。如果执行后第一个结果为 ResultSet 对象，则返回 true；如果执行后第一个结果为受影响的行数或没有任何结果，则返回 false。
    • closeOnCompletion() ，该方法用于当所有依赖于该 Statement 的 ResultSet 关闭时，该 Statement 会自动关闭。
    • Java8 为 Statement 提供了多个重载的 executeLargeUpdate() 方法，这些方法相当于增强版的 executeUpdate() 返回值类型为 long。
  • PrepareStatement：预编译的 Statement 对象。允许数据库预编译 SQL 语句(带有参数的SQL语句)，以后每次只要改变参数的值，就可以达到重复使用SQL语句的目的。它比 Statement 多了如下方法：
    • void setXxx(int parameterIndex, Xxx value)：该方法根据传入参数值的类型不同，需要使用不同的方法。
  • ResultSet：结果集对象。该对象包含访问查询结果的方法，ResultSet 可以通过列索引或列名获取列数据。它包含了如下常用方法来移动记录指针：
    • void close()：释放 ResultSet 对象。
    • boolean absolute(int row)：将结果集的记录指针移动到第 row 行，如果 row 时负数，则移动到倒数第 row 行。如果移动后的记录指针指向第一条有效记录，则该方法返回 true。
    • void beforeFirst()：将 ResultSet 的记录指针定位到首行之前，这是 ResultSet 结果集记录指针的初始状态-记录指针的起始位置位于第一行之前。
    • boolean first()：将 ResultSet 的记录指针定位到首行。如果移动后的记录指针指向一条有效记录，则该方法返回 true。
    • boolean previous()：将 ResultSet 的记录指针定位到上一行。如果移动后的记录指针指向一条有效记录，在返回 true。
    • boolean next()：将 ResultSet 的记录指针定位到下一行。如果移动后的记录指针指向一条有效记录，在返回 true。
    • boolean last()：将 ResultSet 的记录指针定位到最后一行。如果移动后的记录指针指向一条有效记录，在返回 true。
    • void afterLast()：将 ResultSet 的记录指针定位到最后一行之后。

JDBC 编程步骤

  1. 加载数据库驱动

    1
    2
    String driverClass="com.mysql.jdbc.Driver";
    Class.forName(driverClass);

  2. 通过 DriverManager 获取数据库连接。

    1
    2
    3
    DriverManager.getConnection(String url,String user,String password);
    //数据URL通常遵循如下写法：
    jdbc:subprotocol:other stuff

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

1
createStatement()、prepareStatement(String sql)、prepareCall(String sql)
  1. 使用 Statement 执行 SQL 语句
1
execute()、executeUpdate()、executeQuerey()
  1. 操作结果集。
1
2
next()、previous()、first()、last()、beforeFirst()、afterLast()、absolute() 移动记录指针
getXxx() 获取记录指针指向行、特定列的值。既可以使用列索引作为参数，也可以使用列名作为参数。
  1. 回收数据库资源，包括关闭 ResultSet、Statement 和 Connection。

使用 CallableStatement 调用存储过程

首先创建一个简单的存储过程

1
2
3
4
5
6
delimiter //
create procedure add_pro(a int,b int,out sum int)
begin
set sum=a+b;
end;
//

创建调用存储过程的 CallableStatement

1
CallableStatement cstmt=connection.prepareCall("{call add_pro(?,?,?)}");

存储过程的参数既有传入参数，也有传出参数。所谓传入参数就是 Java 程序必须为这些参数传入的值，可以通过 CallableStatement 的 setXxx() 方法为传入参数设置值；传出参数就是 Java 程序可以通过该参数获取存储过程的值，CallableStatement 需要调用 registerOutParameter() 方法来注册该参数

1
2
//注册 CallableStatement 的第三个参数是 Integer 类型
cstmt.registerOutParameter(3,Types.INTEGER)；

经过以上步骤，就可以调用 CallableStatement 的 execute() 方法来执行存储过程，执行结束后通过 CallableStatement 对象的 getXxx(int index) 方法来获取指定传出参数的值。

管理结果集

事务管理

事务是保证底层数据完整的重要手段。

事务具备 4 个特性：

  • 原子性(Atomicity)：事务是应用程序最小的执行单位。
  • 一致性(Consistency)：
  • 隔离性(Isolation)：各个事务独立执行，互不干扰。任意一个事务的内部操作对其他事务都是不可见的。
  • 持续性(Durability)：指事务一旦提交，对数据所做的任何改变都会永久写入到物理设备。

当事务所包含的全部数据库操作完成后，应该提交事务，有两种方式：显示提交-使用 commit；自动提交-执行 DDL 或 DCL 语句，或者程序正常退出。

当事务所包含的任意一个数据库操作失败后，应该回滚事务，使该事务中所做的修改全部失效，事务回滚有两种方式：显式回滚-使用 rollback；自动回滚-系统错误或者强行退出。

MySQL 事务

MySQL 默认关闭事务(意思是自动提交事务)，如果需要开启 MySQL 的事务支持，可以显式调用如下命令：

1
set autocommit={0|1} 0 为关闭自动提交事务，即开启事务

临时开启事务

如果不想关闭整个命令行窗口的自动提交，而只是想临时性地开始事务，则可以使用 MySQL 提供的 start transaction 或 begin 两个命令，它们都表示临时性地开始一次事务，处于 start transaction 或 begin 后的 DML 语句不会立即生效，除非使用 commit 显式提交事务，或者执行 DDL、DCL 语句来隐式提交事务

设置事务中间点

MySQL 提供 savepoint 来设置事务的中间点，通过使用 savepoint 可以让事务回滚到指定中间点，而不是回滚全部事务：savepoint a;，rollback a;

JDBC 的事务支持

JDBC 连接的事务支持由 Connection 提供，Connection 默认打开自动提交事务，即关闭事务。可以调用 Connection 的 setAuto Commit() 方法来关闭自动提交：

1
connection.setAutoCommit(false);//关闭自动提交

当 Connection 遇到一个未处理的 SQLException 异常时，系统将会非正常退出，事务也会自动回滚，但如果程序捕获了该异常，则需要在 catch 块中显式回滚事务。

Connection 提供了设置事务中间点的方法：

  • Savepoint setSavepoint()：在当前事务中创建一个未命名的中间点
  • Savepoint setSavepoint(String name)：在当前事务中创建一个具有指定命名的中间点

然后当需要回滚操作时，调用 Connection 的 rollback(Savepoint savepoint) 方法回滚到指定中间点。