Java Review（三十四、JDBC）

@

目录


JDBC指Java 数据库连接，是一种标准Java应用编程接口（ JAVA API），用来连接 Java 编程语言和广泛的数据库。

1、JDBC典型用法

1.1、JDBC 4.2 常用接口和类简介

Java 8 支持 JDBC 4.2 标准， JDBC 4.2 在原有 JDBC 标准上增加了一些新特性。

  • DriverManager： 用于管理 JDBC 驱动的服务类。 程序中使用该类的主要功能是获取 Connection对象， 该类包含如下方法:
    • public static synchronized Connection getConnection(String url,String user,String pass) throwsSQLException：该方法获得 url 对应数据库的连接。
  • Connection： 代表数据库连接对象， 每个 Connection 代表一个物理连接会话。 要想访问数据库，必须先获得数据库连接。 该接口的常用方法如下:
    • Statement createStatement()throws SQLExcetpion： 该方法返回一个 Statement 对象。
    • PreparedStatement prepareStatement(String sql) throws SQLExcetpion： 该方法返回预编译的Statement 对象， 即将 SQL 语句提交到数据库进行预编译。
    • CallableStatement prepareCall(String sql) throws SQLExcetpion： 该方$去返回 CallableStatement对象， 该对象用于调用存储过程。

上面三个方法都返回用于执行 SQL 语句的 Statement 对象，PreparedStatement、 CallableStatement是 Statement 的子类， 只有获得了 Statement 之后才可执行 SQL 语句。

除此之外， Connection 还有如下几个用于控制事务的方法:

方法 描述
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 查询。 当执行 SQL 查询时， 返回查询到的结果集。 它的常用方法如下：
    • ResultSet executeQuery(String sql)throws SQLException： 该方法用于执行查询语句， 并返回查询结果对应的 ResultSet 对象。 该方法只能用于执行查询语句。
    • int executeUpdate(String sql)throws SQLExcetion： 该方法用于执行 DML 语句， 并返回受影响的行数； 该方法也可用于执行 DDL 语句， 执行 DDL 语句将返回 0。
    • boolean execute(String sql)throws SQLException： 该方法可执行任何 SQL 语句。 如果执行后第一个结果为 ResultSet 对象， 则返回 true； 如果执行后第一个结果为受影响的行数或没有任何结果， 则返回 false。

Java 7 为 Statement 新增了 closeOnCompletion()方法， 如果 Statement 执行了该方法， 则当所有依赖于该 Statement 的 ResultSet 关闭时， 该 Statement 会自动关闭。 Java 7 还为 Statement 提供了一个isCloseOnCompletion()方法， 该方法用于判断该 Statement 是否打幵了 “ closeOnCompletion”。

Java 8 为 Statement 新增了多个重载的 executeLargeUpdate()方法， 这些方法相当于增强版的executeUpdate() 方 法 ， 返 回 值 类 型 为 long。

  • PreparedStatement: 预 编 译 的 Statement 对象。 PreparedStatement 是 Statement 的子接口， 它允许数据库预编译 SQL 语句（ 这些 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 beforeHrstO: 将 ResultSet 的记录指针定位到首行之前， 这是 ResultSet 结果集记录指针的初始状态一一记录指针的起始位置位于第一行之前。
    • boolean first(): 将 ResultSet 的记录指针定位到首行。 如果移动后的记录指针指向一条有效记录， 则该方法返回 true。
    • boolean previous(): 将 ResultSet 的记录指针定位到上一行。 如果移动后的记录指针指向一条有效记录， 则该方法返回 true。
    • boolean next()： 将 ResultSet 的记录指针定位到下一行， 如果移动后的记录指针指向一条有效记录， 则该方法返回 true。
    • boolean last()： 将 ResultSet 的记录指针定位到最后一行， 如果移动后的记录指针指向一条有效记录， 则该方法返回 true。
    • void afterLast(): 将 ResultSet 的记录指针定位到最后一行之后。


API:java.sql.DriverManager

API:java.sql.Connection

API:java.sql.PreparedStatement

API:java.sql.ResultSet


1.2、JDBC 编程步骤

构建一个 JDBC 应用程序包括以下六个步骤-

（1）、加载数据库驱动。 通常使用 Class 类的 forName()静态方法来加载驱动。 例如如下代码：

// 加载驱动
Class.forName(driverClass)

（2）、通过 DriverManager 获取数据库连接。 DriverManager 提供了如下方法:

// 获取数据库连接
DriverManager.getConnection(String url,String user,String password);

（3）、通过 Connection 对象创建 Statement 对象。 Connection 创建Statement 的方法有如下三个：

  • createStatement()： 创建基本的 Statement 对象。
  • prepareStatement(String sql)： 根据传入的 SQL 语句创建预编译的 Statement 对象。
  • prepareCall(String sql)： 根据传入的 SQL 语句创建 CallableStatement 对象。

（4）、使用 Statement 执行 SQL 语句。 所有的 Statement 都有如下三个方法来执行 SQL 语句：

  • execute()： 可以执行任何 SQL 语句， 但比较麻烦。
  • executeUpdate()： 主要用于执行 DML 和 DDL 语句。 执行 DML 语句返回受 SQL 语句影响的行数， 执行 DDL 语句返回 0。
  • executeQuery()： 只能执行查询语句， 执行后返回代表查询结果的 ResultSet 对象。

（5）、操作结果集。如果执行的SQL语句是查询语句，则执行结果将返回一个ResultSet对象，该对象里保存了SQL语句查询的结果。可以通过操作该对象取得查询结果。

ReusltSet对象主要提供一下两类方法：

  • next()、 previous()、 first()、 last() 、 beforeFirst()、 afterLast() 、absolute()等移动记录指针的方法 。
  • getXxx()方法获取记录指针指向行、 特定列的值。 该方法既可使用列索引作为参数， 也可使用列名作为参数。 使用列索引作为参数性能更好， 使用列名作为参数可读性更好。

（6）、回收数据库资源， 包括关闭 ResultSet、 Statement 和 Connection 等资源。

连接数据库实例：ConnMySql.java
import java.sql.*;

public class ConnMySql
{
	public static void main(String[] args) throws Exception
	{
		// 1.加载驱动
		Class.forName("com.mysql.jdbc.Driver");
		try(
			// 2.使用DriverManager获取数据库连接,
			// 其中返回的Connection就代表了Java程序和数据库的连接
			// 不同数据库的URL写法需要查驱动文档知道，用户名、密码由DBA分配
			Connection conn = DriverManager.getConnection(
				"jdbc:mysql://127.0.0.1:3306/select_test?useSSL=true"
				, "root" , "32147");
			// 3.使用Connection来创建一个Statment对象
			Statement stmt = conn.createStatement();
			// 4.执行SQL语句
			/*
			Statement有三种执行sql语句的方法：
			1 execute 可执行任何SQL语句。- 返回一个boolean值，
			  如果执行后第一个结果是ResultSet，则返回true，否则返回false
			2 executeQuery 执行Select语句 － 返回查询到的结果集
			3 executeUpdate 用于执行DML语句。－ 返回一个整数，
			  代表被SQL语句影响的记录条数
			*/
			ResultSet rs = stmt.executeQuery("select s.* , teacher_name"
				+ " from student_table s , teacher_table t"
				+ " where t.teacher_id = s.java_teacher"))
		{
			// ResultSet有系列的getXxx(列索引 | 列名)，用于获取记录指针
			// 指向行、特定列的值，不断地使用next()将记录指针下移一行，
			// 如果移动之后记录指针依然指向有效行，则next()方法返回true。
			while(rs.next())
			{
				System.out.println(rs.getInt(1) + "\t"
					+ rs.getString(2) + "\t"
					+ rs.getString(3) + "\t"
					+ rs.getString(4));
			}
		}
	}
}

4、执行SQL语句的方式

JDBC 不仅可以执行查询， 也可以执行 DDL、DML 等 SQL 语句， 从而允许通过 JDBC 最大限度地控制数据库。

4.1、 executeUpdate 方法执行 DDL和DML语句

Statement使用executeLargeUpdate() (或 executeUpdate()) 来执行 DDL和 DML 语句。

使用Statement执行 DDL 和 DML 语句的步骤与执行普通查询语句的步骤基本相似， 区别在于执行了 DDL 语句后返回值为0, 执 行 了 DML 语句后返回值为受影响的记录条数。

下面程序示使用 executeUpdate()方法（ 此处暂未使用 executeLargeUpdate()方法是因为 MySQL驱动暂不支持） 创建数据表。 该示例并没有直接把数据库连接信息写在程序里， 而是使用一个 mysqLini文件（ 就是一个 properties 文件） 来保存数据库连接信息， 这是比较成熟的做法一一当需要把应用程序从开发环境移植到生产环境时， 无须修改源代码， 只需要修改 mysql.ini 配置文件即可。

ExecuteDDL.java

import java.util.*;
import java.io.*;
import java.sql.*;

public class ExecuteDDL
{
	private String driver;
	private String url;
	private String user;
	private String pass;
	public void initParam(String paramFile)
		throws Exception
	{
		// 使用Properties类来加载属性文件
		Properties props = new Properties();
		props.load(new FileInputStream(paramFile));
		driver = props.getProperty("driver");
		url = props.getProperty("url");
		user = props.getProperty("user");
		pass = props.getProperty("pass");
	}
	public void createTable(String sql)throws Exception
	{
		// 加载驱动
		Class.forName(driver);
		try(
		// 获取数据库连接
		Connection conn = DriverManager.getConnection(url , user , pass);
		// 使用Connection来创建一个Statment对象
		Statement stmt = conn.createStatement())
		{
			// 执行DDL,创建数据表
			stmt.executeUpdate(sql);
		}
	}
	public static void main(String[] args) throws Exception
	{
		ExecuteDDL ed = new ExecuteDDL();
		ed.initParam("mysql.ini");
		ed.createTable("create table jdbc_test "
			+ "( jdbc_id int auto_increment primary key, "
			+ "jdbc_name varchar(255), "
			+ "jdbc_desc text);");
		System.out.println("-----建表成功-----");
	}
}

使用 executeUpdate()执行 DML 语句与执行 DDL 语句基本相似， 区别是 executeUpdate()执行 DDL语句后返回 0, 而执行 DML 语句后返回受影响的记录条数。
ExecuteDML.java
import java.util.*;
import java.io.*;
import java.sql.*;

public class ExecuteDML
{
	private String driver;
	private String url;
	private String user;
	private String pass;

	public void initParam(String paramFile)
		throws Exception
	{
		// 使用Properties类来加载属性文件
		Properties props = new Properties();
		props.load(new FileInputStream(paramFile));
		driver = props.getProperty("driver");
		url = props.getProperty("url");
		user = props.getProperty("user");
		pass = props.getProperty("pass");
	}
	public int insertData(String sql)throws Exception
	{
		// 加载驱动
		Class.forName(driver);
		try(
			// 获取数据库连接
			Connection conn = DriverManager.getConnection(url
				, user , pass);
			// 使用Connection来创建一个Statment对象
			Statement stmt = conn.createStatement())
		{
			// 执行DML,返回受影响的记录条数
			return stmt.executeUpdate(sql);
		}
	}
	public static void main(String[] args)throws Exception
	{
		ExecuteDML ed = new ExecuteDML();
		ed.initParam("mysql.ini");
		int result = ed.insertData("insert into jdbc_test(jdbc_name,jdbc_desc)"
			+ "select s.student_name , t.teacher_name "
			+ "from student_table s , teacher_table t "
			+ "where s.java_teacher = t.teacher_id;");
		System.out.println("--系统中共有" + result + "条记录受影响--");
	}
}

4.2、使用 execute 方法执行 SQL 语句

Statement 的 execute()方法几乎可以执行任何 SQL 语句， 通常没有必要使用 execute()方法来执行 SQL 语句， 使用 executeQuery()或 executeUpdate()方法更简单。 但如果不清楚 SQL 语句的类型， 则只能使用 executeO方法来执行该 SQL 语句。

ExecuteSQL.java

import java.util.*;
import java.io.*;
import java.sql.*;

public class ExecuteSQL
{
	private String driver;
	private String url;
	private String user;
	private String pass;
	public void initParam(String paramFile)throws Exception
	{
		// 使用Properties类来加载属性文件
		Properties props = new Properties();
		props.load(new FileInputStream(paramFile));
		driver = props.getProperty("driver");
		url = props.getProperty("url");
		user = props.getProperty("user");
		pass = props.getProperty("pass");
	}
	public void executeSql(String sql)throws Exception
	{
		// 加载驱动
		Class.forName(driver);
		try(
			// 获取数据库连接
			Connection conn = DriverManager.getConnection(url
				, user , pass);
			// 使用Connection来创建一个Statement对象
			Statement stmt = conn.createStatement())
		{
			// 执行SQL,返回boolean值表示是否包含ResultSet
			boolean hasResultSet = stmt.execute(sql);
			// 如果执行后有ResultSet结果集
			if (hasResultSet)
			{
				try(
					// 获取结果集
					ResultSet rs = stmt.getResultSet())
				{
					// ResultSetMetaData是用于分析结果集的元数据接口
					ResultSetMetaData rsmd = rs.getMetaData();
					int columnCount = rsmd.getColumnCount();
					// 迭代输出ResultSet对象
					while (rs.next())
					{
						// 依次输出每列的值
						for (int i = 0 ; i < columnCount ; i++ )
						{
							System.out.print(rs.getString(i + 1) + "\t");
						}
						System.out.print("\n");
					}
				}
			}
			else
			{
				System.out.println("该SQL语句影响的记录有"
					+ stmt.getUpdateCount() + "条");
			}
		}
	}
	public static void main(String[] args) throws Exception
	{
		ExecuteSQL es = new ExecuteSQL();
		es.initParam("mysql.ini");
		System.out.println("------执行删除表的DDL语句-----");
		es.executeSql("drop table if exists my_test");
		System.out.println("------执行建表的DDL语句-----");
		es.executeSql("create table my_test"
			+ "(test_id int auto_increment primary key, "
			+ "test_name varchar(255))");
		System.out.println("------执行插入数据的DML语句-----");
		es.executeSql("insert into my_test(test_name) "
			+ "select student_name from student_table");
		System.out.println("------执行查询数据的查询语句-----");
		es.executeSql("select * from my_test");
	}
}

4.3、使用 PreparedStatement 执行 SQL 语句

如果经常需要反复执行一条结构相似的 SQL 语句， 例如如下两条 SQL 语句:

insert into student_table values(null,'张三、 1);
insert into student_table values(null,'李四、 2);

对于这两条 SQL 语句而言， 它们的结构基本相似， 只是执行插入时插入的值不同而己。 对于这种情况， 可以使用带占位符（？） 参数的 SQL 语句来代替它：

insert into student table values(null,?,?);

但 Statement 执行 SQL 语句时不允许使用问号占位符参数， 而且这个问号占位符参数必须获得值后才可以执行。 为了满足这种功能， JDBC 提供PreparedStatement 接口， 它是 Statement 接口的子接口，它可以预编译 SQL 语句， 预编译后的 SQL 语句被存储在 PreparedStatement 对象中， 然后可以使用该对象多次高效地执行该语句。 简而言之， 使用 PreparedStatement 比使用 Statement 的效率要高。
创建 PreparedStatement 对象使用 Connection 的 prepareStatement()方法， 该方法需要传入一个 SQL字符串， 该 SQL 字符串可以包含占位符参数。 如下代码所示：

/ / 仓ij建一个 PreparedStatement 对象
pstmt = conn.prepareStatement("insert into student_table values(null,?,1)");

PreparedStatement 也提供了 execute()、 executeUpdate()、 executeQuery()三个方法来执行 SQL 语句，不过这三个方法无须参数， 因为 PreparedStatement 己存储了预编译的 SQL 语句。

使用 PreparedStatement 预编译 SQL 语句时， 该 SQL 语句可以带占位符参数， 因此在执行 SQL 语句之前必须为这些参数传入参数值，PreparedStatement 提供了一系列的 setXxx(int index ， Xxx value)方法来传入参数值。

如果程序很清楚 PreparedStatement 预编译 SQL 语句中各参数的类型， 则使用相应的setXxx()方法来传入参数即可； 如果程序不清楚预编译 SQL 语句中各参数的类型 ， 则可以使用 setObject()方法来传入参数， 由 PreparedStatement 来负责类型转换。

下面程序示范了使用 Statement 和 PreparedStatement 分别插入 100 条记录的对比。 使用 Statement需要传入 100 条 SQL 语句， 但使用 PreparedStatement 则只需要传入 1 条预编译的 SQL 语句， 然后 100次为该 PreparedStatement 的参数设值即可。

PreparedStatementTest.java

import java.util.*;
import java.io.*;
import java.sql.*;

public class PreparedStatementTest
{
	private String driver;
	private String url;
	private String user;
	private String pass;
	public void initParam(String paramFile)throws Exception
	{
		// 使用Properties类来加载属性文件
		Properties props = new Properties();
		props.load(new FileInputStream(paramFile));
		driver = props.getProperty("driver");
		url = props.getProperty("url");
		user = props.getProperty("user");
		pass = props.getProperty("pass");
		// 加载驱动
		Class.forName(driver);
	}
	public void insertUseStatement()throws Exception
	{
		long start = System.currentTimeMillis();
		try(
			// 获取数据库连接
			Connection conn = DriverManager.getConnection(url
				, user , pass);
			// 使用Connection来创建一个Statment对象
			Statement stmt = conn.createStatement())
		{
			// 需要使用100条SQL语句来插入100条记录
			for (int i = 0; i < 100 ; i++ )
			{
				stmt.executeUpdate("insert into student_table values("
					+ " null ,'姓名" + i + "' , 1)");
			}
			System.out.println("使用Statement费时:"
				+ (System.currentTimeMillis() - start));
		}
	}
	public void insertUsePrepare()throws Exception
	{
		long start = System.currentTimeMillis();
		try(
			// 获取数据库连接
			Connection conn = DriverManager.getConnection(url
				, user , pass);
			// 使用Connection来创建一个PreparedStatement对象
			PreparedStatement pstmt = conn.prepareStatement(
				"insert into student_table values(null,?,1)"))

		{
			// 100次为PreparedStatement的参数设值，就可以插入100条记录
			for (int i = 0; i < 100 ; i++ )
			{
				pstmt.setString(1 , "姓名" + i);
				pstmt.executeUpdate();
			}
			System.out.println("使用PreparedStatement费时:"
				+ (System.currentTimeMillis() - start));
		}
	}
	public static void main(String[] args) throws Exception
	{
		PreparedStatementTest pt = new PreparedStatementTest();
		pt.initParam("mysql.ini");
		pt.insertUseStatement();
		pt.insertUsePrepare();
	}
}

总体来看， 使用 PreparedStatement 比使用 Statement 多 / 如下三个好处。

  • PreparedStatement 预编译 SQL 语句， 性能更好。
  • PreparedStatement 无须“拼接” SQL 语句， 编程更简单。
  • PreparedStatement 可以防止 SQL 注入， 安全性更好。

基于以上三点， 应尽量使用 Statement 来执行 SQL 语句， 改为使用 PreparedStatement 执行SQL 语句。

API:java.sql.PreparedStatement


4.4、使用 CallableStatement 调用存储过程

下面的 SQL 语句可以在 MySQL 数据库中创建一个简单的存储过程：

delimiter / /
create procedure add_pro(a int , b int, out sum int)
begin
set sum = a + b;
end;
//

上面程序创建了名为 add_pm 的存储过程， 该存储过程包含三个参数： a、 b 是传入参数， 而 sum 使用 out 修饰， 是传出参数。

调 用 存 储 过 程 使 用 CallableStatement , 可 以 通 过 Connection 的 prepareCall()方 法 来 创 建CallableStatement 对象， 创建该对象时需要传入调用存储过程的 SQL 语句。 调用存储过程的 SQL 语句总是这种格式： {call 过程名(?,?，?...)}, 其中的问号作为存储过程参数的占位符。 例如， 如下代码就创
建了调用上面存储过程的 CallableStatement 对象：

// 使用Connection创建一个 CallableStatement 对象
cstmt = conn.prepareCall("{call add_pro(?,?,?)}");

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

// 注册 CallableStatement 的第三个参数是 int 类型
cstmt.registerOutParameter(3, Types.INTEGER);

经过上面步骤之后， 就可以调用 CallableStatement 的 execute()方法来执行存储过程了， 执行结束后通过 CallableStatement 对象的 getXxx(int index)方法来获取指定传出参数的值。 下面程序示范了如何来调用该存储过程：

CallableStatementTest.java

import java.awt.*;
import java.awt.event.*;
import javax.swing.*;
import java.util.*;
import java.io.*;
import java.sql.*;

public class CallableStatementTest
{
	private String driver;
	private String url;
	private String user;
	private String pass;
	public void initParam(String paramFile)throws Exception
	{
		// 使用Properties类来加载属性文件
		Properties props = new Properties();
		props.load(new FileInputStream(paramFile));
		driver = props.getProperty("driver");
		url = props.getProperty("url");
		user = props.getProperty("user");
		pass = props.getProperty("pass");
	}
	public void callProcedure()throws Exception
	{
		// 加载驱动
		Class.forName(driver);
		try(
			// 获取数据库连接
			Connection conn = DriverManager.getConnection(url
				, user , pass);
			// 使用Connection来创建一个CallableStatment对象
			CallableStatement cstmt = conn.prepareCall(
				"{call add_pro(?,?,?)}"))
		{
			cstmt.setInt(1, 4);
			cstmt.setInt(2, 5);
			// 注册CallableStatement的第三个参数是int类型
			cstmt.registerOutParameter(3, Types.INTEGER);
			// 执行存储过程
			cstmt.execute();
			// 获取，并输出存储过程传出参数的值。
			System.out.println("执行结果是: " + cstmt.getInt(3));
		}
	}
	public static void main(String[] args) throws Exception
	{
		CallableStatementTest ct = new CallableStatementTest();
		ct.initParam("mysql.ini");
		ct.callProcedure();
	}
}

API:java.sql.CallableStatement


5、管理结果集

JDBC 使用 ResultSet 来封装执行查询得到的查询结果， 然后通过移动ResultSet 的记录指针来取出结果集的内容。


5.1、可滚动、 可更新的结果集

可以使用 absolute()、 previous()、 afterLast()等方法自由移动记录指针的 ResultSet 被称为可滚动的结果集。

以默认方式打开的 ResultSet 是不可更新的， 如果希望创建可更新的 ResultSet, 则 必 须 在 创 建Statement 或 PreparedStatement 时传入额外的参数。

Connection 在创建 Statement 或 PreparedStatement时还可额外传入如下两个参数。

  • resultSetType： 控制 ResultSet 的类型， 该参数可以取如下三个值。
    • ResultSet.TYPE_FORWARD_ONLY: 该常量控制记录指针只能向前移动。 这是 JDK 1.4 以前的默认值。
    • ResultSet.TYPE_SCROLL_INSENSITIVE： 该常量控制记录指针可以自由移动（ 可滚动结果集）， 但底层数据的改变不会影响 ResultSet 的内容。
    • ResultSet.TYPE_SCROLL_SENSITIVE： 该常量控制记录指针可以自由移动（ 可滚动结果集），而且底层数据的改变会影响 ResultSet 的内容。
  • resultSetConcurrency： 控制 ResultSet 的并发类型， 该参数可以接收如下两个值。
    • ResultSet.CONCUR_READ_ONLY： 该常量指示 ResultSet 是只读的并发模式（ 默认）。
    • ResultSet.CONCUR_UPDATABLE: 该常量指示 ResultSet 是可更新的并发模式。

下面代码通过这两个参数创建了一个 PreparedStatement 对象， 由该对象生成的 ResultSet 对象将是可滚动、 可更新的结果集。

// 使用 Connection 创建一个 PreparedStatement 对象
// 传入控制结果集可滚动、 可更新的参数
pstmt = conn.prepareStatement(sql , ResultSet•TYPE_SCROLL_INSENSITIVE
, ResultSet.CONCUR_

需要指出的是， 可更新的结果集还需要满足如下两个条件。

  • 所有数据都应该来自一个表。
  • 选出的数据集必须包含主键列。

Java 8 为 ResultSet 添加了 updateObject(String columnLabel, Object x, SQLType targetSqlType)和updateObject(int columnlndex, Object x, SQLType targetSqlType)两个默认方法， 这两个方法可以直接用Object 来修改记录指针所指记录、 特定列的值， 其中 SQLType 用于指定该数据列的类型。 但目前最新
的 MySQL 驱动暂不支持该方法。

下面程序示范了这种创建可滚动、 可更新的结果集的方法：

ResultSetTest.java

import java.util.*;
import java.io.*;
import java.sql.*;

public class ResultSetTest
{
	private String driver;
	private String url;
	private String user;
	private String pass;
	public void initParam(String paramFile)throws Exception
	{
		// 使用Properties类来加载属性文件
		Properties props = new Properties();
		props.load(new FileInputStream(paramFile));
		driver = props.getProperty("driver");
		url = props.getProperty("url");
		user = props.getProperty("user");
		pass = props.getProperty("pass");
	}
	public void query(String sql)throws Exception
	{
		// 加载驱动
		Class.forName(driver);
		try(
			// 获取数据库连接
			Connection conn = DriverManager.getConnection(url , user , pass);
			// 使用Connection来创建一个PreparedStatement对象
			// 传入控制结果集可滚动，可更新的参数。
			PreparedStatement pstmt = conn.prepareStatement(sql
				, ResultSet.TYPE_SCROLL_INSENSITIVE
				, ResultSet.CONCUR_UPDATABLE);
			ResultSet rs = pstmt.executeQuery())
		{
			rs.last();
			int rowCount = rs.getRow();
			for (int i = rowCount; i > 0 ; i-- )
			{
				rs.absolute(i);
				System.out.println(rs.getString(1) + "\t"
					+ rs.getString(2) + "\t" + rs.getString(3));
				// 修改记录指针所有记录、第2列的值
				rs.updateString(2 , "学生名" + i);
				// 提交修改
				rs.updateRow();
			}
		}
	}
	public static void main(String[] args) throws Exception
	{
		ResultSetTest rt = new ResultSetTest();
		rt.initParam("mysql.ini");
		rt.query("select * from student_table");
	}
}

5.2、处理 Blob 类型数据

Blob ( Binary Long Object ) 是二进制长对象的意思， Blob 列通常用于存储大文件， 典型的 Blob 内容是一张图片或一个声音文件， 由于它们的特殊性， 必须使用特殊的方式来存储。 使用 Blob 列可以把图片、 声音等文件的二进制数据保存在数据库里， 并可以从数据库里恢复指定文件。

如果需要将图片插入数据库， 显然不能直接通过普通的 SQL 语句来完成， 因为有一个关键的问题Blob 常量无法表示。 所以将 Blob 数据插入数据库需要使用 PreparedStatement,该对象有一个方法：
setBinaryStream(int parameterlndex,InputStream x), 该方法可以为指定参数传入二进制输入流， 从而可以实现将 Blob 数据保存到数据库的功能。

当需要从 ResultSet 里取出 Blob 数据时， 可以调用 ResultSet 的 getBlob(int columnlndex)方法， 该方法将返回一个 Blob 对象， Blob 对象提供了getBinaryStream()方法来获取该 Blob 数据的输入流， 也可以使用 Blob 对象提供的 getBytes()方法直接取出该 Blob 对象封装的二进制数据。

创建一个表：

create table userblob
(
    id int(4) primary key not null auto_increment,
    name varchar(30),
    photo longblob
);

向数据库中插入blob数据：

BlobDemo01.java

import java.sql.Connection ;
import java.sql.DriverManager ;
import java.sql.SQLException ;
import java.sql.PreparedStatement ;
import java.io.File ;
import java.io.FileInputStream ;
import java.io.InputStream ;
public class BlobDemo01{
    // 定义MySQL的数据库驱动程序
    public static final String DBDRIVER = "org.gjt.mm.mysql.Driver" ;
    // 定义MySQL数据库的连接地址
    public static final String DBURL = "jdbc:mysql://localhost:3306/sys" ;
    // MySQL数据库的连接用户名
    public static final String DBUSER = "root" ;
    // MySQL数据库的连接密码
    public static final String DBPASS = "aaaaaa" ;
    public static void main(String args[]) throws Exception{    // 所有异常抛出
        Connection conn = null ;        // 数据库连接
        PreparedStatement pstmt = null ;
        String name = "小华" ;
        String sql = "INSERT INTO userblob(name,photo) VALUES (?,?) " ;
        Class.forName(DBDRIVER) ;    // 加载驱动程序
        conn = DriverManager.getConnection(DBURL,DBUSER,DBPASS) ;
        pstmt = conn.prepareStatement(sql) ;
        File f = new File("d:" + File.separator + "图片1.JPG") ;    // 图片文件
        InputStream input = null ;
        input = new FileInputStream(f) ;
        pstmt.setString(1,name) ;    // 设置第一个“?”的内容
        pstmt.setBinaryStream(2,input,(int)f.length()) ;    // 设置输入流
        pstmt.executeUpdate() ;    // 更新数据库
        pstmt.close() ;
        conn.close() ;            // 数据库关闭
    }
};

从数据库中查询blob数据：

import java.sql.Connection ;
import java.sql.DriverManager ;
import java.sql.PreparedStatement ;
import java.io.File ;
import java.io.FileOutputStream ;
import java.sql.ResultSet ;
import java.io.InputStream ;
import java.io.OutputStream ;
public class BlobDemo01{
    // 定义MySQL的数据库驱动程序
    public static final String DBDRIVER = "org.gjt.mm.mysql.Driver" ;
    // 定义MySQL数据库的连接地址
    public static final String DBURL = "jdbc:mysql://localhost:3306/sys" ;
    // MySQL数据库的连接用户名
    public static final String DBUSER = "root" ;
    // MySQL数据库的连接密码
    public static final String DBPASS = "aaaaaa" ;
    
    public static void main(String args[]) throws Exception{    // 所有异常抛出
        Connection conn = null ;        // 数据库连接
        PreparedStatement pstmt = null ;
        ResultSet rs = null ;
        int id = 1 ;
        String sql = "SELECT name,photo FROM userblob WHERE id=?" ;
        Class.forName(DBDRIVER) ;    // 加载驱动程序
        conn = DriverManager.getConnection(DBURL,DBUSER,DBPASS) ;
        pstmt = conn.prepareStatement(sql) ;
        pstmt.setInt(1,id) ;
        rs = pstmt.executeQuery() ;    // 执行查询
        if(rs.next()){
            String name = rs.getString(1) ;
            System.out.println("姓名：" + name) ;
            InputStream input = rs.getBinaryStream(2) ;
            File f = new File("d:" + File.separator + "load图片1.gif") ;    // 图片文件
            OutputStream out = null ;
 out = new FileOutputStream(f) ;
int temp = 0 ;
            while((temp=input.read())!=-1){    // 边读边写
                out.write(temp) ;
            }
            input.close() ;
            out.close() ;
        }
        pstmt.close() ;
        conn.close() ;            // 数据库关闭
    }
}

为了方便读取，JAVA专门的提供了Blob接口进行二进制文件的读取操作：


BlobDemo01.java

import java.sql.Connection ;
import java.sql.DriverManager ;
import java.sql.SQLException ;
import java.sql.PreparedStatement ;
import java.sql.Blob ;
import java.sql.ResultSet ;
import java.io.File ;
import java.io.FileOutputStream ;
import java.io.InputStream ;
import java.io.OutputStream ;

public class BlobDemo01{
    // 定义MySQL的数据库驱动程序
    public static final String DBDRIVER = "org.gjt.mm.mysql.Driver" ;
    // 定义MySQL数据库的连接地址
    public static final String DBURL = "jdbc:mysql://localhost:3306/sys" ;
    // MySQL数据库的连接用户名
    public static final String DBUSER = "root" ;
    // MySQL数据库的连接密码
    public static final String DBPASS = "aaaaaa" ;
    public static void main(String args[]) throws Exception{    // 所有异常抛出
        Connection conn = null ;        // 数据库连接
        PreparedStatement pstmt = null ;
        ResultSet rs = null ;
        int id = 1 ;
        String sql = "SELECT name,photo FROM userblob WHERE id=?" ;
        Class.forName(DBDRIVER) ;    // 加载驱动程序
        conn = DriverManager.getConnection(DBURL,DBUSER,DBPASS) ;
        pstmt = conn.prepareStatement(sql) ;
        pstmt.setInt(1,id) ;
        rs = pstmt.executeQuery() ;    // 执行查询
        if(rs.next()){
            String name = rs.getString(1) ;
            System.out.println("姓名：" + name) ;
            Blob b = rs.getBlob(2) ;
          File f = new File("d:" + File.separator + "load图片2.gif") ;    // 图片文件
 OutputStream out = null ;
 out = new FileOutputStream(f) ;
           out.write(b.getBytes(1,(int)b.length())) ;
           out.close() ;
        }
        pstmt.close() ;
        conn.close() ;            // 数据库关闭
    }
}

从实际角度，把大文件存在MySQL数据库中是很不明智行为。往往采用映射路径方式完成。

API:java.sql.Blob


5.3、使用 ResultSetMetaData 分析结果集

当执行 SQL 查询后可以通过移动记录指针来遍历 ResultSet 的每条记录， 但程序可能不清楚该ResultSet 里包含哪些数据列， 以及每个数据列的数据类型， 那么可以通过 ResultSetMetaData 来获取关于 ResultSet 的描述信息。

ResultSet 里包含一个 getMetaData()方法， 该方法返回该 ResultSet 对应的 ResultSetMetaData 对象。一旦获得了 ResultSetMetaData 对象， 就可通过 ResultSetMetaData 提供的大量方法来返回 ResultSet 的描述信息。 常用的方法有如下三个。

  • int getColuninCount()： 返回该 ResultSet 的列数量。
  • String getColumnName(int column)： 返回指定索引的列名。
  • int getColumnType(int column)： 返回指定索引的列类型。

API:java.sql.ResultSetMetaData


6、Java的 RowSet

RowSet 接口继承了 ResultSet 接口， RowSet 接口下包含 JdbcRowSet、CachedRowSet 、 FilteredRowSet 、JoinRowSet 和 WebRowSet 常用子接口。 除 JdbcRowSet 需要保持与数据库的连接之外， 其余 4 个子接口都是离线的 RowSet， 无须保持与数据库的连接。

与 ResultSet 相比， RowSet 默认是可滚动、 可更新、 可序列化的结果集， 而且作为 JavaBean 使用，因此能方便地在网络上传输， 用于同步两端的数据。 对于离线 RowSet 而言， 程序在创建 RowSet 时己把数据从底层数据库读取到了内存， 因此可以充分利用计算机的内存， 从而降低数据库服务器的负载,提高程序性能。

RowSet 规范的接口类图

在这里插入图片描述

API:javax.sql.RowSet


6.1、Java 7 新增的 RowSetFactory 与 RowSet

Java 7 新增了 RowSetProvider 类和 RowSetFactory 接 口 ， 其 中 RowSetProvider 负 责 创 建RowSetFactory， 而 RowSetFactory 则提供了如下方法来创建 RowSet 实例。

  • CachedRowSet createCachedRowSet(): 创建一个默认的 CachedRowSet 0
  • FilteredRowSet createFilteredRowSet()： 创建一个默认的 FilteredRowSet。
  • JdbcRowSet createJdbcRowSet()： 创建一个默认的 JdbcRowSet。
  • JoinRowSet createJoinRowSet()： 创建一个默认的 JoinRowSet。
  • WebRowSet createWebRowSet()： 创建一个默认的 WebRowSet。

通过使用 RowSetFactory, 就可以把应用程序与 RowSet 实现类分离开， 避免直接使用 JdbcRow Setlmpl等非公开的 API, 也更有利于后期的升级、 扩展。

通过 RowSetFactory 的几个工厂方法不难看出， 使用 RowSetFactory 创建的 RowSet 其实并没有装填数据。

为了让 RowSet 能抓取到数据库的数据， 需要为 RowSet 设置数据库的 URL、 用户名、 密码等连接信息。 因此， RowSet 接口中定义了如下常用方法。

  • setUrl(String url)： 设置该 RowSet 要访问的数据库的 URL。
  • setUsemame(String name)： 设置该 RowSet 要访问的数据库的用户名。
  • setPassword(String password)： 设置该 RowSet 要访问的数据库的密码。
  • setCommand(String sql)： 设置使用该 sql 语句的查询结果来装填该 RowSet。
  • execute()： 执行查询。

下面程序通过 RowSetFactory 示范了使用 JdbcRowSet 的可滚动、 可修改特性:

RowSetFactoryTest.java

import java.util.*;
import java.io.*;
import java.sql.*;
import javax.sql.rowset.*;

public class RowSetFactoryTest
{
    private String driver;
    private String url;
    private String user;
    private String pass;
    public void initParam(String paramFile)throws Exception
    {
		// 使用Properties类来加载属性文件
		Properties props = new Properties();
		props.load(new FileInputStream(paramFile));
		driver = props.getProperty("driver");
		url = props.getProperty("url");
		user = props.getProperty("user");
		pass = props.getProperty("pass");
    }
    public void update(String sql)throws Exception
    {
        // 加载驱动
        Class.forName(driver);
        // 使用RowSetProvider创建RowSetFactory
        RowSetFactory factory = RowSetProvider.newFactory();
        try(
            // 使用RowSetFactory创建默认的JdbcRowSet实例
            JdbcRowSet jdbcRs = factory.createJdbcRowSet())
        {
            // 设置SQL查询语句
            jdbcRs.setCommand(sql);
            // 设置必要的连接信息
            jdbcRs.setUrl(url);
            jdbcRs.setUsername(user);
            jdbcRs.setPassword(pass);

            // 执行查询
            jdbcRs.execute();
            jdbcRs.afterLast();
            // 向前滚动结果集
            while (jdbcRs.previous())
            {
                System.out.println(jdbcRs.getString(1)
                    + "\t" + jdbcRs.getString(2)
                    + "\t" + jdbcRs.getString(3));
                if (jdbcRs.getInt("student_id") == 3)
                {
                    // 修改指定记录行
                    jdbcRs.updateString("student_name", "孙悟空");
                    jdbcRs.updateRow();
                }
            }
        }
    }

API:javax.sql.rowset.RowSetFactory

API:javax.sql.rowset.RowSetProvider


6.2、离线 RowSet

在使用 ResultSet 的时代， 程序查询得到 ResultSet 之后必须立即读取或处理它对应的记录， 否则一旦 Connection 关闭， 再去通过 ResultSet 读取记录就会引发异常。 在这种模式下， JDBC 编程十分痛苦——假设应用程序架构被分为两层： 数据访问层和视图显示层， 当应用程序在数据访问层查询得到ResultSet 之后， 对 ResultSet 的处理有如下两种常见方式。

  • 使用迭代访问 ResultSet 里的记录， 并将这些记录转换成 Java Bean， 再将多个 Java Bean 封装成等资源 个 List ， 然后将 集合， 也就是完成 Java Bean“集合传到视图显示层 ResultSet—Java Bean， 集合 视图显示层可以显示查询得到的数据 ”的转换。 转换完成后可以关闭 Connection 。
  • 直 接 将 ResultSet 传到视图显示层一一这要求当视图显示层显示数据时， 底层 Connection 必须一直处于打开状态， 否则 ResultSet 无法读取记录。

第一种方式比较安全， 但编程十分烦琐； 第二种方式则需要 Connection 一直处于打开状态， 这不仅不安全， 而且对程序性能也有较大的影响。

通过使用离线 RowSet 可以十分“ 优雅” 地处理上面的问题， 离线 RowSet 会直接将底层数据读入内存中， 封装成 RowSet 对象， 而 RowSet 对象则完全可以当成 Java Bean 来使用。 因此不仅安全， 而且编程十分简单。CachedRowSet 是所有离线 RowSet 的父接口， 因此下面以 CachedRowSet 为例进行介绍。
看下面程序:

CachedRowSetTest.java

import java.util.*;
import java.io.*;
import java.sql.*;
import javax.sql.*;
import javax.sql.rowset.*;

public class CachedRowSetTest
{
	private static String driver;
	private static String url;
	private static String user;
	private static String pass;
	public void initParam(String paramFile)throws Exception
	{
		// 使用Properties类来加载属性文件
		Properties props = new Properties();
		props.load(new FileInputStream(paramFile));
		driver = props.getProperty("driver");
		url = props.getProperty("url");
		user = props.getProperty("user");
		pass = props.getProperty("pass");
	}

	public CachedRowSet query(String sql)throws Exception
	{
		// 加载驱动
		Class.forName(driver);
		// 获取数据库连接
		Connection conn = DriverManager.getConnection(url , user , pass);
		Statement stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery(sql);
		// 使用RowSetProvider创建RowSetFactory
		RowSetFactory factory = RowSetProvider.newFactory();
		// 创建默认的CachedRowSet实例
		CachedRowSet cachedRs = factory.createCachedRowSet();
		// 使用ResultSet装填RowSet
		cachedRs.populate(rs);    // ①
		// 关闭资源
		rs.close();
		stmt.close();
		conn.close();
		return cachedRs;
	}
	public static void main(String[] args)throws Exception
	{
		CachedRowSetTest ct = new CachedRowSetTest();
		ct.initParam("mysql.ini");
		CachedRowSet rs = ct.query("select * from student_table");
		rs.afterLast();
		// 向前滚动结果集
		while (rs.previous())
		{
			System.out.println(rs.getString(1)
				+ "\t" + rs.getString(2)
				+ "\t" + rs.getString(3));
			if (rs.getInt("student_id") == 3)
			{
				// 修改指定记录行
				rs.updateString("student_name", "孙悟空");
				rs.updateRow();
			}
		}
		// 重新获取数据库连接
		Connection conn = DriverManager.getConnection(url
			, user , pass);
		conn.setAutoCommit(false);
		// 把对RowSet所做的修改同步到底层数据库
		rs.acceptChanges(conn);
	}
}

运行该程序， 可以看到在 Connection 关闭的情况下， 程序依然可以读取、 修改 RowSet 里的记录。为了将程序对离线 RowSet 所做的修改同步到底层数据库， 程序在调用 RowSet 的 acceptChanges()方法时必须传入 Connection。

API:javax.sql.rowset.CachedRowSet


6.3、离线 RowSet 的查询分页

由于 CachedRowSet 会将数据记录直接装载到内存中， 因此如果 SQL 查询返回的记录过大，CachedRowSet 将会占用大量的内存， 在某些极端的情况下， 它甚至会直接导致内存溢出。为了解决该问题， CachedRowSet 提供了分页功能。 所谓分页功能就是一次只装载 ResultSet 里的某几条记录， 这样就可以避免 CachedRowSet 占用内存过大的问题。

CachedRowSet 提供了如下方法来控制分页：

  • populate(ResultSet rs, int startRow)： 使用给定的 ResultSet 装填 RowSet， 从 ResultSet 的第 startRow条记录开始装填。
  • setPageSize(int pageSize)： 设置 CachedRowSet 每次返回多少条记录。
  • previousPage()： 在底层 ResultSet 可用的情况下， 让 CachedRowSet 读取上一页记录。
  • nextPage()： 在底层 ResultSet 可用的情况下， 让 CachedRowSet 读取下一页记录。

下面程序示范了 CachedRowSet 的分页支持：

import java.util.*;
import java.io.*;
import java.sql.*;
import javax.sql.*;
import javax.sql.rowset.*;

public class CachedRowSetPage
{
	private String driver;
	private String url;
	private String user;
	private String pass;
	public void initParam(String paramFile)throws Exception
	{
		// 使用Properties类来加载属性文件
		Properties props = new Properties();
		props.load(new FileInputStream(paramFile));
		driver = props.getProperty("driver");
		url = props.getProperty("url");
		user = props.getProperty("user");
		pass = props.getProperty("pass");
	}

	public CachedRowSet query(String sql , int pageSize
		, int page)throws Exception
	{
		// 加载驱动
		Class.forName(driver);
		try(
			// 获取数据库连接
			Connection conn = DriverManager.getConnection(url , user , pass);
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql))
		{
			// 使用RowSetProvider创建RowSetFactory
			RowSetFactory factory = RowSetProvider.newFactory();
			// 创建默认的CachedRowSet实例
			CachedRowSet cachedRs = factory.createCachedRowSet();
			// 设置每页显示pageSize条记录
			cachedRs.setPageSize(pageSize);
			// 使用ResultSet装填RowSet，设置从第几条记录开始
			cachedRs.populate(rs , (page - 1) * pageSize + 1);
			return cachedRs;
		}
	}
	public static void main(String[] args)throws Exception
	{
		CachedRowSetPage cp = new CachedRowSetPage();
		cp.initParam("mysql.ini");
		CachedRowSet rs = cp.query("select * from student_table" , 3 , 2);   // ①
		// 向后滚动结果集
		while (rs.next())
		{
			System.out.println(rs.getString(1)
				+ "\t" + rs.getString(2)
				+ "\t" + rs.getString(3));
		}
	}
}

7、事务处理

对于任何数据库应用而言， 事务都是非常重要的， 事务是保证底层数据完整的重要手段， 没有事务支持的数据库应用， 那将非常脆弱。

7.1、事务的概念和 MySQL 事务支持

事务是由一步或几步数据库操作序列组成的逻辑执行单元， 这系列操作要么全部执行， 要么全部放弃执行。 程序和事务是两个不同的概念。 一般而言， 一段程序中可能包含多个事务。

事务具备 4 个特性： 原子性（ Atomicity)、 一致性（ Consistency)、 隔离性 (Isolation) 和持续性( Durability)。 这 4 个特性也简称为 ACID 性。

  • 原 子 性（Atomicity): 事务是应用中最小的执行单位， 就如原子是自然界的最小颗粒， 具有不可再分的特征一样， 事务是应用中不可再分的最小逻辑执行体。
  • 一致性（Consistency): 事务执行的结果， 必须使数据库从一个一致性状态， 变到另一个一致性状态。 当数据库只包含事务成功提交的结果时， 数据库处于一致性状态。 如果系统运行发生中断， 某个事务尚未完成而被迫中断， 而该未完成的事务对数据库所做的修改已被写入数据库，此时， 数据库就处于一种不正确的状态。 比如银行在两个账户之间转账： 从 A 账户向 B 账户转入 1000 元， 系统先减少 A 账户的 1000 元， 然后再为 B 账户增加 1000 元。 如果全部执行成功，数据库处于于一致性状态； 如果仅执行完 A 账户金额的修改， 而没有增加 B 账户的金额， 则数据库就处于不一致性状态； 因此， 一致性是通过原子性来保证的。
  • 隔 离 性 (Isolation)： 各个事务的执行互不干扰， 任意一个事务的内部操作对其他并发的事务都是隔离的。 也就是说， 并发执行的事务之间不能看到对方的中间状态， 并发执行的事务之间不能互相影响。
  • 持续性（ Durability): 持续性也称为持久性（Persistence), 指事务一旦提交， 对数据所做的任何改变都要记录到永久存储器中， 通常就是保存进物理数据库。

数据库的事务由下列语句组成：

  • 一组 DML 语句， 经过这组 DML 语句修改后的数据将保持较好的一致性。
  • 一条 DDL 语句。
  • 一条 DCL 语句。

DDL 和 DCL 语句最多只能有一条， 因为 DDL 和 DCL 语句都会导致事务立即提交。
当事务所包含的全部数据库操作都成功执行后， 应该提交（ commit) 事务， 使这些修改永久生效。

事务提交有两种方式： 显式提交和自动提交。

  • 显式提交： 使用 commit()
  • 自动提交： 执行 DDL 或 DCL 语句， 或者程序正常退出。

当事务所包含的任意一个数据库操作执行失败后， 应该回滚 （ rollback) 事务， 使该事务中所做的修改全部失效。 事务回滚有两种方式： 显式回滚和自动回滚。

  • 显式回滚： 使用 rollbacko
  • 自动回滚： 系统错误或者强行退出。

MySQL 默认关闭事务（ 即打开自动提交)， 在默认情况下， 用户在 MySQL 控制台输入一条 DML 语句， 这条 DML 语句将会立即保存到数据库里。 为了幵启 MySQL 的事务支持， 可以显式调用如下命令:

SET AUTOCOMMIT = {0 1 1} o 为关闭自动提交， 即开启事务

一旦在 MySQL 的命令行窗口中输入 set autocommit=0 开启了事务， 该命令行窗口里的所有 DML语句都不会立即生效， 上一个事务结束后第一条 DML 语句将开始一个新的事务， 而后续执行的所有 SQL语句都处于该事务中， 除非显式使用 commit 来提交事务， 或者正常退出， 或者运行 DDL、 DCL 语句导致事务隐式提交。 当然， 也可以使用 rollback 回滚来结束事务， 使用 rollback 结束事务将导致本次事务中 DML 语句所做的修改全部失效。

除此之外， 如果不想关闭整个命令行窗口的自动提交， 而只是想临时性地幵始事务， 则可以使用MySQL 提供的 start transaction 或 begin 两个命令， 它们都表示临时性地开始一次事务， 处于 start transaction 或 begin 后的 DML 语句不会立即生效， 除非使用 commit 显式提交事务， 或者执行 DDL、DCL 语者来隐式提交事务。 如下 SQL 代码将不会对数据库有任何影响。

# 临时开始事务
begin;
# 向 student_table 表中插入 3 条记录
insert into student_table
values(null , 'xx' , 1);
insert into student_table
values(null ,'yy' , 1);
insert into student table
values(null ,'zz',1)
# 查询 student_table 表的记录
select * from student table;
# ①
# 回滚事务
rollback;
# 再次查询
select from student table; # ②

执行上面 SQL 语句中的第①条查询语句将会看到刚刚插入的 3 条记录， 如果打开 MySQL 的其他命令行窗口将看不到这 3 条记录一一这正体现了事务的隔离性。 接着程序 rollback 了事务中的全部修改，执行第②条查询语句时将看到数据库又恢复到事务开始前的状态。

提交， 不管是显式提交还是隐式提交， 都会结束当前事务； 回滚， 不管是显式回滚还是隐式回滚,都会结束当前事务。

除此之外， MySQL 还提供了 savepoint 来设置事务的中间点， 通过使用savepoint 设置事务的中间点可以让事务回滚到指定中间点， 而不是回滚全部事务。 如下 SQL 语句设置了一个中间点：

savepoint a;

一旦设置了中间点后， 就可以使用 rollback 回滚到指定中间点， 回滚到指定中间点的代码如下:

rollback to a;

7.1、JDBC 的事务支持

JDBC 连接也提供了事务支持， JDBC 连接的事务支持由 Connection 提供， Connection 默认打开自动提交， 即关闭事务， 在这种情况下， 每条 SQL 语句一旦执行， 便会立即提交到数据库， 永久生效，无法对其进行回滚操作。

可以调用 Connection 的 setAutoCommit()方法来关闭自动提交， 开启事务， 如下代码所示：

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

程序中还可调用 Connection 提供的 getAutoCommit()方法来返回该连接的自动提交模式。一旦事务开始之后， 程序可以像平常一样创建 Statement 对象， 创建了 Statement 对象之后， 可以执行任意多条 DML 语句， 如下代码所示：

stmt.executeUpdate( ...)；
stmt.executeUpdate(...);
stmt.executeUpdate(...);

上面这些 SQL 语句虽然被执行了， 但这些 SQL 语句所做的修改不会生效， 因为事务还没有结束。 如果所有的 SQL 语句都执行成功， 程序可以调用Connection 的 commit()方法来提交事务， 如下代码所示:

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

如果任意一条 SQL 语句执行失败， 则应该用 Connection 的 rollback()方法来回滚事务,如下代码所示:

// 回 滚 事 务
conn.rollback();

下面程序示范了当程序出现未处理的 SQLException 异常时， 系统将自动回滚事务:

import java.sql.*;
import java.io.*;
import java.util.*;

public class TransactionTest
{
	private String driver;
	private String url;
	private String user;
	private String pass;
	public void initParam(String paramFile)throws Exception
	{
		// 使用Properties类来加载属性文件
		Properties props = new Properties();
		props.load(new FileInputStream(paramFile));
		driver = props.getProperty("driver");
		url = props.getProperty("url");
		user = props.getProperty("user");
		pass = props.getProperty("pass");
	}
	public void insertInTransaction(String[] sqls) throws Exception
	{
		// 加载驱动
		Class.forName(driver);
		try(
			Connection conn = DriverManager.getConnection(url , user , pass))
		{
			// 关闭自动提交，开启事务
			conn.setAutoCommit(false);
			try(
				// 使用Connection来创建一个Statment对象
				Statement stmt = conn.createStatement())
			{
				// 循环多次执行SQL语句
				for (String sql : sqls)
				{
					stmt.executeUpdate(sql);
				}
			}
			// 提交事务
			conn.commit();
		}
	}
	public static void main(String[] args) throws Exception
	{
		TransactionTest tt = new TransactionTest();
		tt.initParam("mysql.ini");
		String[] sqls = new String[]{
			"insert into student_table values(null , 'aaa' ,1)",
			"insert into student_table values(null , 'bbb' ,1)",
			"insert into student_table values(null , 'ccc' ,1)",
			// 下面这条SQL语句将会违反外键约束，
			// 因为teacher_table中没有ID为5的记录。
			"insert into student_table values(null , 'ccc' ,5)" //①
		};
		tt.insertInTransaction(sqls);
	}
}

上面程序中的粗体字代码只是开启事务、 提交事务的代码， 并没有回滚事务的代码。 但当程序执行到 第 4 条 SQL 语句（ ①处代码） 时， 这条语句将会引起外键约束异常， 该异常没有得到处理， 引起程序非正常结束， 所以事务自动回滚。

Connection 也提供了设置中间点的方法:setSavepoint(),Connection 提供了两个方法来设置中间点。

  • Savepoint setSavepoint(): 在当前事务中创建一个未命名的中间点， 并返回代表该中间点的Savepoint 对象。
  • Savepoint setSavepoint(String name)： 在当前事务中创建一个具有指定名称的中间点， 并返回代表该中间点的 Savepoint 对象。

通常来说， 设置中间点时没有太大的必要指定名称， 因为 Connection 回滚到指定中间点时， 并不是根据名字回滚的， 而是根据中间点对象回滚的，Connection 提供了 rollback(Savepoint savepoint)方法回滚到指定中间点。


7.3、Java 8 增强的批量更新

JDBC 还提供了一个批量更新的功能， 使用批量更新时， 多条 SQL 语句将被作为一批操作被同时收集， 并同时提交。

使用批量更新也需要先创建一个 Statement 对象， 然后利用该对象的addBatch()方法将多条 SQL 语句同时收集起来， 最后调用 Java 8 为 Statement 对象新增的 executeLargeBatch()( 或原有的 executeBatch())方法同时执行这些 SQL 语句。 只要批量操作中任何一条 SQL 语句影响的记录条数可能超过Integer.MAX VALUE, 就应该使用 executeLargeBatch()方法， 而不是 executeBatch()方法。

如下代码片段示范了如何执行批量更新：

Statement stmt = conn.createStatement();
// 使用 Statement 同时收集多条 SQL 语句
stmt.addBatch(sqll);
stmt.addBatch(sql2);
stmt.addBatch(sql3);
……
// 同时执行所有的 SQL 语句
stmt.executeLargeBatch();

执行 executeLargeBatch()方法将返回一个 long[]数组， 因为使用 Statement 执行 DDL、 DML 语句都将返回一个 long 值， 而执行多条 DDL、 DML 语句将会返回多个 long 值， 多个 long 值就组成了这个 long[]数组。 如果在批量更新的 addBatchO方法中添加了 select 查询语句， 程序将直接出现错误。

为了让批量操作可以正确地处理错误， 必须把批量执行的操作视为单个事务， 如果批量更新在执行过程中失败， 则让事务回滚到批量操作开始之前的状态。 为了达到这种效果， 程序应该在开始批量操作之前先关闭自动提交， 然后开始收集更新语句， 当批量操作执行结束后， 提交事务， 并恢复之前的自动
提交模式。

如下代码示范了如何使用 JDBC 的批量更新：

import java.sql.*;
import java.io.*;
import java.util.*;

public class BatchTest
{
	private String driver;
	private String url;
	private String user;
	private String pass;
	public void initParam(String paramFile)throws Exception
	{
		// 使用Properties类来加载属性文件
		Properties props = new Properties();
		props.load(new FileInputStream(paramFile));
		driver = props.getProperty("driver");
		url = props.getProperty("url");
		user = props.getProperty("user");
		pass = props.getProperty("pass");
	}
	public void insertBatch(String[] sqls) throws Exception
	{
		// 加载驱动
		Class.forName(driver);
		try(
			Connection conn = DriverManager.getConnection(url , user , pass))
		{
			// 关闭自动提交，开启事务
			conn.setAutoCommit(false);
			// 保存当前的自动的提交模式
			boolean autoCommit = conn.getAutoCommit();
			// 关闭自动提交
			conn.setAutoCommit(false);
			try(
				// 使用Connection来创建一个Statement对象
				Statement stmt = conn.createStatement())
			{
				// 循环多次执行SQL语句
				for (String sql : sqls)
				{
					stmt.addBatch(sql);
				}
				// 同时提交所有的SQL语句
				stmt.executeLargeBatch();
				// 提交修改
				conn.commit();
				// 恢复原有的自动提交模式
				conn.setAutoCommit(autoCommit);
			}
			// 提交事务
			conn.commit();
		}
	}
	public static void main(String[] args) throws Exception
	{
		TransactionTest tt = new TransactionTest();
		tt.initParam("mysql.ini");
		String[] sqls = new String[]{
			"insert into student_table values(null , 'aaa' ,1)",
			"insert into student_table values(null , 'bbb' ,1)",
			"insert into student_table values(null , 'ccc' ,1)",
		};
		tt.insertInTransaction(sqls);
	}
}

8、分析数据库信息

大部分时候， 只需要对指定数据表进行插入（ C)、 查询（ R )、 修改（ U)、 删除（ D) 等 CRUD 操作；但在某些时候， 程序需要动态地获取数据库的相关信息， 例如数据库里的数据表信息、 列信息。 除此之外， 如果希望在程序中动态地利用底层数据库所提供的特殊功能， 则都需要动态分析数据库相关信息。

8.1、使用 DatabaseMetaData 分析数据库信息

JDBC 提供了 DatabaseMetaData 来封装数据库连接对应数据库的信息， 通过 Connection 提供的getMetaData()方法就可以获取数据库对应DatabaseMetaData 对象。

DatabaseMetaData 接口通常由驱动程序供应商提供实现， 其目的是让用户了解底层数据库的相关信息。 使用该接口的目的是发现如何处理底层数据库， 尤其是对于试图与多个数据库一起使用的应用程序—因为应用程序需要在多个数据库之间切换， 所以必须利用该接口来找出底层数据库的功能， 例如，调用 supportsCorrelatedSubqueries()方法查看是否可以使用关联子查询， 或者调用 supportsBatchUpdates()方法查看是否可以使用批量更新。

许多DatabaseMetaData 方法以 ResultSet 对象的形式返回查询信息， 然后使用 ResultSet 的常规方法（ 如 getString()和 getlnt()) 即可从这些 ResultSet 对象中获取数据。 如果查询的信息不可用， 则将返回一个空 ResultSet 对象。

DatabaseMetaData 的很多方法都需要传入一个 xxxPattem 模式字符串， 这里的 xxxPattem 不是正则表达式， 而是 SQL 里的模式字符串， 即用百分号（％ ) 代表任意多个字符， 使用下画线（_) 代表一个字符。 在通常情况下， 如果把该模式字符串的参数值设置为 mill， 即表明该参数不作为过滤条件。

下面程序通过 DatabaseMetaData 分析了当前 Connection 连接对应数据库的一些基本信息， 包括当前数据库包含多少数据表， 存储过程， student table 表的数据列、 主键、 外键等信息。

import java.sql.*;
import java.util.*;
import java.io.*;

public class DatabaseMetaDataTest
{
	private String driver;
	private String url;
	private String user;
	private String pass;
	public void initParam(String paramFile)throws Exception
	{
		// 使用Properties类来加载属性文件
		Properties props = new Properties();
		props.load(new FileInputStream(paramFile));
		driver = props.getProperty("driver");
		url = props.getProperty("url");
		user = props.getProperty("user");
		pass = props.getProperty("pass");
	}
	public void info() throws Exception
	{
		// 加载驱动
		Class.forName(driver);
		try(
			// 获取数据库连接
			Connection conn = DriverManager.getConnection(url
				, user , pass))
		{
			// 获取的DatabaseMetaData对象
			DatabaseMetaData dbmd = conn.getMetaData();
			// 获取MySQL支持的所有表类型
			ResultSet rs = dbmd.getTableTypes();
			System.out.println("--MySQL支持的表类型信息--");
			printResultSet(rs);
			// 获取当前数据库的全部数据表
			rs = dbmd.getTables(null,null, "%" , new String[]{"TABLE"});
			System.out.println("--当前数据库里的数据表信息--");
			printResultSet(rs);
			// 获取student_table表的主键
			rs = dbmd.getPrimaryKeys(null , null, "student_table");
			System.out.println("--student_table表的主键信息--");
			printResultSet(rs);
			// 获取当前数据库的全部存储过程
			rs = dbmd.getProcedures(null , null, "%");
			System.out.println("--当前数据库里的存储过程信息--");
			printResultSet(rs);
			// 获取teacher_table表和student_table之间的外键约束
			rs = dbmd.getCrossReference(null,null, "teacher_table"
				, null, null, "student_table");
			System.out.println("--teacher_table表和student_table之间"
				+ "的外键约束--");
			printResultSet(rs);
			// 获取student_table表的全部数据列
			rs = dbmd.getColumns(null, null, "student_table", "%");
			System.out.println("--student_table表的全部数据列--");
			printResultSet(rs);
		}
	}
	public void printResultSet(ResultSet rs)throws SQLException
	{
		ResultSetMetaData rsmd = rs.getMetaData();
		// 打印ResultSet的所有列标题
		for (int i = 0 ; i < rsmd.getColumnCount() ; i++ )
		{
			System.out.print(rsmd.getColumnName(i + 1) + "\t");
		}
		System.out.print("\n");
		// 打印ResultSet里的全部数据
		while (rs.next())
		{
			for (int i = 0; i < rsmd.getColumnCount() ; i++ )
			{
				System.out.print(rs.getString(i + 1) + "\t");
			}
			System.out.print("\n");
		}
		rs.close();
	}
	public static void main(String[] args)
		throws Exception
	{
		DatabaseMetaDataTest dt = new DatabaseMetaDataTest();
		dt.initParam("mysql.ini");
		dt.info();
	}
}

API:java.sql.DatabaseMetaData


8.1、使用系统表分析数据库信息

除可以使用 DatabaseMetaData 来分析底层数据库信息之外， 如果已经确定应用程序所使用的数据库系统， 则可以通过数据库的系统表来分析数据库信息。 系统表又称为数据字典， 数据字典的数据通常由数据库系统负责维护， 用户通常只能查询数据字典， 而不能修改数据字典的内容。

MySQL使用information_schema数据库来保存系统表 ：

  • tables:存放数据库里所有数据表的信息 。
  • schemata：存放数据库里所有数据库（与MySQL 的 Schema对应）的信息。
  • views: 存放数据库里所有视图的信息。
  • columns： 存放数据库里所有列的信息。
  • triggers： 存放数据库里所有触发器的信息。
  • routines： 存放数据库里所有存储过程和函数的信息。
  • key column usage: 存放数据库里所有具有约束的键信息。
  • tahle constraints： 存放数据库里全部约束的表信息。
  • statistics： 存放数据库里全部索引的信息。

通常来说， 如果需要获得数据库信息， 包括该数据库驱动提供了哪些功能， 则应该利用 Database MetaData 来了解该数据库支持哪些功能。 完全可能出现这样一种情况： 对于底层数据库支持的功能，但数据库驱动没有提供该功能， 程序还是不能使用该功能。 使用 DatabaseMetaData 则不会出现这种问题。
如果需要纯粹地分析数据库的静态对象， 例如分析数据库系统里包含多少数据库、 数据表、 视图、索引等信息， 则利用系统表会更加合适。


9、使用连接池管理连接

数据库连接的建立及关闭是极耗费系统资源的操作， 在多层结构的应用环境中， 这种资源的耗费对系统性能影响尤为明显。 通过 DriverManager 获取连接 获得的数据库连接， 一个数据库连接对象均对应一个物理数据库连接， 每次操作都打开一个物理连接， 使用完后立即关闭连接。频繁地打开、 关闭连接将造成系统性能低下。

数据库连接池的解决方案是： 当应用程序启动时， 系统主动建立足够的数据库连接， 并将这些连接组成一个连接池。 每次应用程序请求数据库连接时， 无须重新打开连接， 而是从连接池中取出己有的连接使用， 使用完后不再关闭数据库连接， 而是直接将连接归还给连接池。 通过使用连接池， 将大大提高程序的运行效率。

对于共享资源的情况， 有一个通用的设计模式： 资源池（ Resource Pool), 用于解决资源的频繁请求 、 释放所造成的性能下降。 为了解决数据库连接的频繁请求、 释放， JDBC 2.0 规范引入了数据库连接池技术。 数据库连接池是 Connection 对象的工厂。

数据库连接池的常用参数如下:

  • 数据库的初始连接数。
  • 连接池的最大连接数。
  • 连接池的最小连接数。
  • 连接池每次增加的容量。

JDBC 的数据库连接池使用 javax.sql.DataSource 来表示， DataSource 只是一个接口， 该接口通常由商用服务器（ 如 WebLogic、 WebSphere)等提供实现， 也有一些幵源组织提供实现（ 如 DBCP 和 C3P0 等）。

9.1、DBCP数据源

DBCP 是 Apache 软件基金组织下的开源连接池实现， 该连接池依赖该组织下的另一个开源系统：common-pool。 如果需要使用该连接池实现， 则应在系统中增加如下两个 jar 文件。

  • commons-dbcp.jar： 连接池的实现。
  • commons-pool.jar： 连接池实现的依赖库。

登录 http://commons.apache.org/站点即可下载 commons-pool.zip 和 commons-dbcp.zip 两个压缩文件，

Tomcat 的连接池正是采用该连接池实现的。 数据库连接池既可以与应用服务器整合使用， 也可以由应用程序独立使用。 下面的代码片段示范了使用 DBCP 来获得数据库连接的方式：

// 创建数据源对象
BasicDataSource ds =new BasicDataSource();
// 设置连接池所需的驱动
ds.setDriverClassName("com.mysql.jdbc.Driver");
// 设置连接数据库的 URL
ds.setUrl("jdbc:mysql://localhost:3306/javaee");
// 设置连接数据库的用户名
ds.setUsername("root");
// 设置连接数据库的密码
ds.setPassword("pass");
// 设置连接池的初始连接数
ds.setlnitialSize(5);
// 设置连接池最多可有多少个活动连接数
ds.setMaxActive(20);
// 设置连接池中最少有 2 个空闲的连接
ds.setMinldle(2)

数据源和数据库连接不同， 数据源无须创建多个， 它是产生数据库连接的工厂， 因此整个应用只需要一个数据源即可。 也就是说， 对于一个应用， 上面代码只要执行一次即可。 建议把上面程序中的 ds设置成 static 成员变量， 并且在应用开始时立即初始化数据源对象， 程序中所有需要获取数据库连接的地方直接访问该 ds 对象， 并获取数据库连接即可。 通过 DataSource 获取数据库连接的代码示例如下：

// 通过数据源获取数据库连接
Connection conn = ds.getConnection();

当数据库访问结束后， 程序还是像以前一样关闭数据库连接， 如下代码所示:

// 释放数据库连接
conn.close();

但上面代码并没有关闭数据库的物理连接， 它仅仅把数据库连接释放， 归还给连接池， 让其他客户端可以使用该连接。

9.2、C3P0 数据源

相比之下， C3P0 数据源性能更胜一筹， Hibernate 就推荐使用该连接池。

C3P0 连接池不仅可以自动清理不再使用的 Connection, 还可以自动清理Statement 和 ResultSet。 C3P0 连接池需要版本为 1.3 以上的 JRE, 推荐使用 1.4 以上的 JRE。 如果需要使用 C3P0 连接池， 则应在系统中增加如下 JAR 文件。

  • c3p0-0.9.1.2.jar： C3P0 连接池的实现。

登录 http://sourceforge.net/projects/c3p0/站点即可下载 C3P0 数据源的最新版本。

下面代码通过 C3P0 连接池获得数据库连接：

// 创建连接池实例
ComboPooledDataSource ds = new ComboPooledDataSource();
// 设置连接池连接数据库所需的驱动
ds.setDriverClass("com.mysql.jdbc.Driver");
// 设置连接数据库的 URL
ds.setJdbcUrl("jdbc:mysql://localhost:3306/javaee");
// 设置连接数据库的用户名
ds.setUser("root");
// 设置连接数据库的密码
ds.setPassword("32147");
// 设置连接池的最大连接数
ds.setMaxPoolSize(40);
// 设置连接池的最小连接数
ds.setMinPoolSize(2);
// 设置连接池的初始连接数
ds. setlnitialPoolSize(10);
// 设置连接池的缓存 Statement 的最大数
ds.setMaxStatements(180);

在程序中创建 C3P0 连接池的方法与创建 DBCP 连接池的方法基本类似。

一旦获取了 C3P0 连接池之后， 程序同样可以通过如下代码来获取数据库连接：

// 获得数据库连接
Connection conn = ds.getConnection();

参考：
【1】：《疯狂Java讲义》
【2】：JDBC 简介_w3cschool
【3】：JDBC 驱动类型_w3cschool
【4】：JDBC 连接数据库_w3cschool
【5】：JDBC 结果集_w3cschool
【6】：JDBC Statement 对象_w3cschool
【7】：JDBC:数据库操作：BLOB数据处理
【8】：JDBC 存储过程_w3cschool
【9】：JDBC 事务_w3cschool
posted @ 2020-06-06 00:11  三分恶  阅读(245)  评论(0)  编辑  收藏  举报