java jdbc事务_Java JDBC事务管理和保存点

最新推荐文章于 2021-08-20 17:00:00 发布

cunchi4221

最新推荐文章于 2021-08-20 17:00:00 发布

阅读量307

点赞数

文章标签：数据库 mysql java python jdbc

原文链接：https://www.journaldev.com/2483/java-jdbc-transaction-management-savepoint

版权

java jdbc事务

Transaction Management in java is required when we are dealing with relational databases. We use JDBC API for database operations and today we will learn how to use JDBC transaction management. In the JDBC Tutorial we learned how we can use JDBC API for database connectivity and execute SQL queries. We also looked at the different kind of drivers and how we can write loosely couple JDBC programs that helps us in switching from one database server to another easily.

当我们处理关系数据库时，需要使用Java中的事务管理。我们使用JDBC API进行数据库操作，今天我们将学习如何使用JDBC事务管理。在《 JDBC教程》中，我们学习了如何使用JDBC API进行数据库连接并执行SQL查询。我们还研究了不同类型的驱动程序，以及如何编写松散耦合的JDBC程序，这些程序可以帮助我们轻松地从一台数据库服务器切换到另一台数据库服务器。

Java JDBC中的事务管理 (Transaction Management in Java JDBC)

This tutorial is aimed to provide details about JDBC Transaction Management and using JDBC Savepoint for partial rollback.

本教程旨在提供有关JDBC事务管理以及将JDBC Savepoint用于部分回滚的详细信息。

By default when we create a database connection, it runs in auto-commit mode. It means that whenever we execute a query and it’s completed, the commit is fired automatically. So every SQL query we fire is a transaction and if we are running some DML or DDL queries, the changes are getting saved into database after every SQL statement finishes.

默认情况下，当我们创建数据库连接时，它以自动提交模式运行。这意味着无论何时执行查询并完成查询，都会自动触发提交。因此，我们触发的每个SQL查询都是一个事务，如果我们正在运行一些DML或DDL查询，则在每个SQL语句完成后，所做的更改都将保存到数据库中。

Sometimes we want a group of SQL queries to be part of a transaction so that we can commit them when all the queries runs fine. If we get any exception, we have a choice of rollback all the queries executed as part of the transaction.

有时我们希望一组SQL查询成为事务的一部分，以便在所有查询运行良好时可以提交它们。如果遇到任何异常，我们可以选择回滚作为事务一部分执行的所有查询。

Let’s understand with a simple example where we want to utilize JDBC transaction management support for data integrity. Let’s say we have UserDB database and Employee information is saved into two tables. For my example, I am using MySQL database but it will run fine on other relational databases as well such as Oracle and PostgreSQL.

让我们通过一个简单的示例来理解，我们希望利用JDBC事务管理支持来确保数据完整性。假设我们有UserDB数据库，并将Employee信息保存到两个表中。对于我的示例，我正在使用MySQL数据库，但是它将在其他关系数据库以及Oracle和PostgreSQL上正常运行。

The tables store employee information with address details in tables, DDL scripts of these tables are like below.

这些表将员工信息和地址详细信息存储在表中，这些表的DDL脚本如下所示。

CREATE TABLE `Employee` (
  `empId` int(11) unsigned NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`empId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `Address` (
  `empId` int(11) unsigned NOT NULL,
  `address` varchar(20) DEFAULT NULL,
  `city` varchar(5) DEFAULT NULL,
  `country` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`empId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Our final project looks like below image, we will look into each of the classes one by one.

我们的最终项目如下图所示，我们将逐一研究每个类。

As you can see that I have MySQL JDBC jar in the project build path, so that we can connect to the MySQL database.

如您所见，在项目构建路径中有MySQL JDBC jar，因此我们可以连接到MySQL数据库。

DBConnection.java

package com.journaldev.jdbc.transaction;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DBConnection {

	public final static String DB_DRIVER_CLASS = "com.mysql.jdbc.Driver";
	public final static String DB_URL = "jdbc:mysql://localhost:3306/UserDB";
	public final static String DB_USERNAME = "pankaj";
	public final static String DB_PASSWORD = "pankaj123";

	public static Connection getConnection() throws ClassNotFoundException, SQLException {

		Connection con = null;

		// load the Driver Class
		Class.forName(DB_DRIVER_CLASS);

		// create the connection now
		con = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);

		System.out.println("DB Connection created successfully");
		return con;
	}
}

DBConnection is the class where we are creating MySQL database connection to be used by other classes.

DBConnection是我们要创建供其他类使用MySQL数据库连接的类。

EmployeeJDBCInsertExample.java

package com.journaldev.jdbc.transaction;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class EmployeeJDBCInsertExample {

	public static final String INSERT_EMPLOYEE_QUERY = "insert into Employee (empId, name) values (?,?)";

	public static final String INSERT_ADDRESS_QUERY = "insert into Address (empId, address, city, country) values (?,?,?,?)";

	public static void main(String[] args) {

		Connection con = null;
		try {
			con = DBConnection.getConnection();
			
			insertEmployeeData(con, 1, "Pankaj");

			insertAddressData(con, 1, "Albany Dr", "San Jose", "USA");
		} catch (SQLException | ClassNotFoundException e) {
			e.printStackTrace();
		} finally {

			try {
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public static void insertAddressData(Connection con, int id,
			String address, String city, String country) throws SQLException {
		PreparedStatement stmt = con.prepareStatement(INSERT_ADDRESS_QUERY);
		stmt.setInt(1, id);
		stmt.setString(2, address);
		stmt.setString(3, city);
		stmt.setString(4, country);

		stmt.executeUpdate();

		System.out.println("Address Data inserted successfully for ID=" + id);
		stmt.close();
	}

	public static void insertEmployeeData(Connection con, int id, String name)
			throws SQLException {
		PreparedStatement stmt = con.prepareStatement(INSERT_EMPLOYEE_QUERY);
		stmt.setInt(1, id);
		stmt.setString(2, name);

		stmt.executeUpdate();

		System.out.println("Employee Data inserted successfully for ID=" + id);
		stmt.close();
	}

}

This is a simple JDBC program where we are inserting user provided values in both Employee and Address tables created above. Now when we will run this program, we will get following output.

这是一个简单的JDBC程序，我们将在上面创建的Employee和Address表中插入用户提供的值。现在，当我们运行该程序时，将得到以下输出。

DB Connection created successfully
Employee Data inserted successfully for ID=1
com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'city' at row 1
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2939)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1623)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1715)
	at com.mysql.jdbc.Connection.execSQL(Connection.java:3249)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1268)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1541)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1455)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1440)
	at com.journaldev.jdbc.transaction.EmployeeJDBCInsertExample.insertAddressData(EmployeeJDBCInsertExample.java:45)
	at com.journaldev.jdbc.transaction.EmployeeJDBCInsertExample.main(EmployeeJDBCInsertExample.java:23)

As you can see that SQLException is raised when we are trying to insert data into Address table because the value is bigger than the size of the column.

如您所见，当我们尝试将数据插入到地址表中时，由于该值大于列的大小，因此引发了SQLException。

If you will look at the content of the Employee and Address tables, you will notice that data is present in Employee table but not in Address table. This becomes a serious problem because only part of the data is inserted properly and if we run the program again, it will try to insert into Employee table again and throw below exception.

如果查看Employee和Address表的内容，您会注意到Employee表中有数据，而Address表中没有数据。这将成为一个严重的问题，因为只有部分数据被正确插入，并且如果我们再次运行该程序，它将尝试再次插入Employee表并抛出以下异常。

com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException: Duplicate entry '1' for key 'PRIMARY'
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:931)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2941)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1623)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1715)
	at com.mysql.jdbc.Connection.execSQL(Connection.java:3249)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1268)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1541)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1455)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1440)
	at com.journaldev.jdbc.transaction.EmployeeJDBCInsertExample.insertEmployeeData(EmployeeJDBCInsertExample.java:57)
	at com.journaldev.jdbc.transaction.EmployeeJDBCInsertExample.main(EmployeeJDBCInsertExample.java:21)

So there is no way we can save the data into Address table now for the Employee. So this program leads to data integrity issues and that’s why we need transaction management to insert into both the tables successfully or rollback everything if any exception arises.

因此，我们现在无法将员工的数据保存到“地址”表中。因此，该程序会导致数据完整性问题，这就是为什么我们需要事务管理将事务成功插入两个表中，或者在出现任何异常时回滚所有内容。

JDBC事务管理 (JDBC Transaction Management)

JDBC API provide method setAutoCommit() through which we can disable the auto commit feature of the connection. We should disable auto commit only when it’s required because the transaction will not be committed unless we call the commit() method on connection. Database servers uses table locks to achieve transaction management and it’s resource intensive process. So we should commit the transaction as soon as we are done with it. Let’s write another program where we will use JDBC transaction management feature to make sure data integrity is not violated.

JDBC API提供了setAutoCommit（）方法，通过它可以禁用连接的自动提交功能。我们应该仅在需要时禁用自动提交，因为除非我们在连接上调用commit（）方法，否则不会提交事务。数据库服务器使用表锁来实现事务管理及其资源密集型过程。因此，我们应该在完成交易后立即提交交易。让我们编写另一个程序，在该程序中将使用JDBC事务管理功能来确保不违反数据完整性。

EmployeeJDBCTransactionExample.java

package com.journaldev.jdbc.transaction;

import java.sql.Connection;
import java.sql.SQLException;

public class EmployeeJDBCTransactionExample {

	public static void main(String[] args) {
		
		Connection con = null;
		try {
			con = DBConnection.getConnection();
			
			//set auto commit to false
			con.setAutoCommit(false);

			EmployeeJDBCInsertExample.insertEmployeeData(con, 1, "Pankaj");

			EmployeeJDBCInsertExample.insertAddressData(con, 1, "Albany Dr", "San Jose", "USA");
			
			//now commit transaction
			con.commit();
			
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				con.rollback();
				System.out.println("JDBC Transaction rolled back successfully");
			} catch (SQLException e1) {
				System.out.println("SQLException in rollback"+e.getMessage());
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

}

Please make sure you remove the earlier inserted data before running this program. When you will run this program, you will get following output.

在运行该程序之前，请确保删除先前插入的数据。当您运行该程序时，将得到以下输出。

DB Connection created successfully
Employee Data inserted successfully for ID=1
com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'city' at row 1
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2939)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1623)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1715)
	at com.mysql.jdbc.Connection.execSQL(Connection.java:3249)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1268)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1541)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1455)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1440)
	at com.journaldev.jdbc.transaction.EmployeeJDBCInsertExample.insertAddressData(EmployeeJDBCInsertExample.java:45)
	at com.journaldev.jdbc.transaction.EmployeeJDBCTransactionExample.main(EmployeeJDBCTransactionExample.java:19)
JDBC Transaction rolled back successfully

The output is similar to previous program but if you will look into the database tables, you will notice that data is not inserted into Employee table. Now we can change the city value so that it can fit in the column and rerun the program to insert data into both the tables. Notice that connection is committed only when both the inserts executed fine and if any of them throws exception, we are rolling back complete transaction.

输出类似于先前的程序，但是如果您查看数据库表，则会注意到数据没有插入到Employee表中。现在，我们可以更改城市值，使其适合列，然后重新运行程序以将数据插入两个表中。请注意，只有在两个插入都执行良好并且其中任何一个抛出异常时，才提交连接，我们将回滚完整事务。

JDBC保存点 (JDBC Savepoint)

Sometimes a transaction can be group of multiple statements and we would like to rollback to a particular point in the transaction. JDBC Savepoint helps us in creating checkpoints in a transaction and we can rollback to that particular checkpoint. Any savepoint created for a transaction is automatically released and become invalid when the transaction is committed, or when the entire transaction is rolled back. Rolling a transaction back to a savepoint automatically releases and makes invalid any other savepoints that were created after the savepoint in question.

有时一个事务可以是多个语句组成的组，我们想回滚到事务中的特定点。 JDBC Savepoint帮助我们在事务中创建检查点，并且我们可以回滚到该特定检查点。为事务创建的任何保存点将自动释放，并在提交事务或整个事务回滚时变为无效。将事务回滚到保存点会自动释放，并使在该保存点之后创建的任何其他保存点无效。

Let’s say we have a Logs table where we want to log the messages that employee information is saved successfully. But since it’s just for logging, if there are any exceptions while inserting into Logs table, we don’t want to rollback the entire transaction. Let’s see how we can achieve this with JDBC savepoint.

假设我们有一个Logs表，我们要在其中记录成功保存员工信息的消息。但是由于它仅用于记录日志，因此如果在插入Logs表时存在任何异常，我们就不希望回滚整个事务。让我们看看如何使用JDBC保存点来实现这一点。

CREATE TABLE `Logs` (
  `id` int(3) unsigned NOT NULL AUTO_INCREMENT,
  `message` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

EmployeeJDBCSavePointExample.java

package com.journaldev.jdbc.transaction;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Savepoint;

public class EmployeeJDBCSavePointExample {

	public static final String INSERT_LOGS_QUERY = "insert into Logs (message) values (?)";

	public static void main(String[] args) {

		Connection con = null;
		Savepoint savepoint = null;
		try {
			con = DBConnection.getConnection();

			// set auto commit to false
			con.setAutoCommit(false);

			EmployeeJDBCInsertExample.insertEmployeeData(con, 2, "Pankaj");

			EmployeeJDBCInsertExample.insertAddressData(con, 2, "Albany Dr",
					"SFO", "USA");

			// if code reached here, means main work is done successfully
			savepoint = con.setSavepoint("EmployeeSavePoint");

			insertLogData(con, 2);

			// now commit transaction
			con.commit();

		} catch (SQLException e) {
			e.printStackTrace();
			try {
				if (savepoint == null) {
					// SQLException occurred in saving into Employee or Address tables
					con.rollback();
					System.out
							.println("JDBC Transaction rolled back successfully");
				} else {
					// exception occurred in inserting into Logs table
					// we can ignore it by rollback to the savepoint
					con.rollback(savepoint); 
					
					//lets commit now
					con.commit();
				}
			} catch (SQLException e1) {
				System.out.println("SQLException in rollback" + e.getMessage());
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	private static void insertLogData(Connection con, int i)
			throws SQLException {
		PreparedStatement stmt = con.prepareStatement(INSERT_LOGS_QUERY);
		
		//message is very long, will throw SQLException
		stmt.setString(1, "Employee information saved successfully for ID" + i);

		stmt.executeUpdate();
		System.out.println("Logs Data inserted successfully for ID=" + i);

		stmt.close();
	}

}

The program is very simple to understand. As you can see that I am creating the savepoint after data is inserted successfully into Employee and Address tables. If SQLException arises and savepoint is null, it means that exception is raised while executing insert queries for either Employee or Address table and hence I am rolling back complete transaction.

该程序非常简单易懂。如您所见，在将数据成功插入Employee和Address表之后，我正在创建保存点。如果出现SQLException且savepoint为null，则意味着对Employee或Address表执行插入查询时会引发异常，因此我回滚了完整的事务。

If savepoint is not null, it means that SQLException is coming in inserting data into Logs table, so I am rolling back transaction only to the savepoint and committing it.

如果savepoint不为null，则意味着将SQLException插入到Logs表中，因此我仅将事务回滚到savepoint并提交。

If you will run above program, you will see below output.

如果您将运行以上程序，则会看到以下输出。

DB Connection created successfully
Employee Data inserted successfully for ID=2
Address Data inserted successfully for ID=2
com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'message' at row 1
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2939)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1623)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1715)
	at com.mysql.jdbc.Connection.execSQL(Connection.java:3249)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1268)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1541)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1455)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1440)
	at com.journaldev.jdbc.transaction.EmployeeJDBCSavePointExample.insertLogData(EmployeeJDBCSavePointExample.java:73)
	at com.journaldev.jdbc.transaction.EmployeeJDBCSavePointExample.main(EmployeeJDBCSavePointExample.java:30)

If you will check database tables, you will notice that the data is inserted successfully in Employee and Address tables. Note that we could have achieved this easily by committing the transaction when data is inserted successfully in Employee and Address tables and used another transaction for inserting into logs table. This is just an example to show the usage of JDBC savepoint in java programs.

如果您要检查数据库表，您会注意到该数据已成功插入Employee和Address表中。请注意，当数据成功插入Employee和Address表并使用另一个事务插入日志表时，通过提交事务，我们可以轻松实现此目的。这只是一个示例，显示了Java程序中JDBC保存点的用法。

Download JDBC Transaction Management Example Project 下载JDBC事务管理示例项目

Download project from above link and play around with it, try to use multiple savepoints and JDBC transactions API to learn more about it.

从上面的链接下载项目并试用它，尝试使用多个保存点和JDBC事务API来了解更多信息。