java中使用savepoint,Java Connection releaseSavepoint()方法与示例

最新推荐文章于 2022-04-07 09:01:48 发布

往事锦年

最新推荐文章于 2022-04-07 09:01:48 发布

阅读量521

点赞数 1

文章标签： java中使用savepoint

保存点是事务中的逻辑回滚点。设置保存点时，只要在保存点之后发生错误，就可以使用rollback()方法撤消在创建的保存点之前所做的事件。

您可以使用Connection接口的setSavepoint()方法在数据库中设置保存点。

并且，您可以使用releaseSavepoint()方法删除/释放保存点。

此方法接受一个Savepoint对象作为参数，并删除指定的Savepoint。

释放保存点-

使用registerDriver()DriverManager类的方法将驱动程序注册为-//注册驱动程序

DriverManager.registerDriver(new com.mysql.jdbc.Driver());

使用getConnection()DriverManager类的方法获取连接，如下所示：//获得连接

String url = "jdbc:mysql://localhost/mydatabase";

Connection con = DriverManager.getConnection(url, "root", "password");

使用setSavepoint()方法将保存点设置为所需值，使用setHoldability()Connection接口的方法将其设置为-Savepoint savePoint = con.setSavepoint("MysavePoint");

如果不需要，请使用releaseSavepoint()方法释放Savepoint 。con.releaseSavepoint("MysavePoint");

让我们使用CREATE语句在MySQL数据库中创建一个名为MyPlayers的表，如下所示-CREATE TABLE MyPlayers(

ID INT,

First_Name VARCHAR(255),

Last_Name VARCHAR(255),

Date_Of_Birth date,

Place_Of_Birth VARCHAR(255),

Country VARCHAR(255),

PRIMARY KEY (ID)

);

现在，我们将使用INSERT语句在MyPlayers表中插入7条记录-insert into MyPlayers values(1, 'Shikhar', 'Dhawan', DATE('1981-12-05'), 'Delhi', 'India');

insert into MyPlayers values(2, 'Jonathan', 'Trott', DATE('1981-04-22'), 'CapeTown', 'SouthAfrica');

insert into MyPlayers values(3, 'Kumara', 'Sangakkara', DATE('1977-10-27'), 'Matale', 'Srilanka');

insert into MyPlayers values(4, 'Virat', 'Kohli', DATE('1988-11-05'), 'Delhi', 'India');

insert into MyPlayers values(5, 'Rohit', 'Sharma', DATE('1987-04-30'), 'Nagpur', 'India');

insert into MyPlayers values(6, 'Ravindra', 'Jadeja', DATE('1988-12-06'), 'Nagpur', 'India');

insert into MyPlayers values(7, 'James', 'Anderson', DATE('1982-06-30'), 'Burnley', 'England');

下面的JDBC程序演示releaseSavepoint()了Connection接口的方法。在这里，我们在Myplayers表中插入了一条新记录。设置保存点，删除先前插入的记录，然后回滚到创建的保存点，并显示表的内容，最后使用该releaseSavepoint()方法释放创建的保存点。

示例import java.sql.Connection;

import java.sql.Date;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Savepoint;

import java.sql.Statement;

public class Connection_releaseSavepoint {

public static void main(String args[]) throws SQLException {

//注册驱动程序

DriverManager.registerDriver(new com.mysql.jdbc.Driver());

//获得连接

String url = "jdbc:mysql://localhost/mydatabase";

Connection con = DriverManager.getConnection(url, "root", "password");

System.out.println("Connection established......");

//将自动提交设置为false-

con.setAutoCommit(false);

//创建一个Statement对象

Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

//检索数据

ResultSet rs = stmt.executeQuery("select * from MyPlayers");

System.out.println("Contents of the table initially");

while(rs.next()) {

System.out.print("ID: "+rs.getString("ID")+", ");

System.out.print("First_Name: "+rs.getString("First_Name")+", ");

System.out.print("Last_Name: "+rs.getString("Last_Name")+", ");

System.out.print("Date_Of_Birth: "+rs.getString("Date_Of_Birth")+", ");

System.out.print("Place_Of_Birth: "+rs.getString("Place_Of_Birth")+", ");

System.out.print("Country: "+rs.getString("Country"));

System.out.println("");

}

PreparedStatement pstmt = con.prepareStatement("INSERT INTO MyPlayers VALUES (?, ?, ?, ?, ?, ?)");

pstmt.setInt(1, 8);

pstmt.setString(2, "Virat");

pstmt.setString(3, "Kohli");

pstmt.setDate(4, new Date(594733933000L));

pstmt.setString(5, "Delhi");

pstmt.setString(6, "India");

pstmt.executeUpdate();

//设置保存点

Savepoint savePoint = con.setSavepoint("MysavePoint");

//删除记录

stmt.execute("Delete from MyPlayers where id = 8");

//回滚到保存点

con.rollback(savePoint);

//回滚后表的内容

System.out.println("Contents of the table");

rs = stmt.executeQuery("select * from MyPlayers");

while(rs.next()) {

System.out.print("ID: "+rs.getString("ID")+", ");

System.out.print("First_Name: "+rs.getString("First_Name")+", ");

System.out.print("Last_Name: "+rs.getString("Last_Name")+", ");

System.out.print("Date_Of_Birth: "+rs.getString("Date_Of_Birth")+", ");

System.out.print("Place_Of_Birth: "+rs.getString("Place_Of_Birth")+", ");

System.out.print("Country: "+rs.getString("Country"));

System.out.println("");

}

con.releaseSavepoint(savePoint);

System.out.println("Save point released");

}

现在，您可以观察结果中保存点之前创建的新记录。由于我们在设置保存点后删除了该记录，因此在回滚时将还原该记录。

输出结果Connection established......

Contents of the table initially

ID: 1, First_Name: Shikhar, Last_Name: Dhawan, Date_Of_Birth: 1981-12-05, Place_Of_Birth: Delhi, Country: India

ID: 2, First_Name: Jonathan, Last_Name: Trott, Date_Of_Birth: 1981-04-22, Place_Of_Birth: CapeTown, Country: SouthAfrica

ID: 3, First_Name: Kumara, Last_Name: Sangakkara, Date_Of_Birth: 1977-10-27, Place_Of_Birth: Matale, Country: Srilanka

ID: 4, First_Name: Virat, Last_Name: Kohli, Date_Of_Birth: 1988-11-05, Place_Of_Birth: Mumbai, Country: India

ID: 5, First_Name: Rohit, Last_Name: Sharma, Date_Of_Birth: 1987-04-30, Place_Of_Birth: Nagpur, Country: India

ID: 6, First_Name: Ravindra, Last_Name: Jadeja, Date_Of_Birth: 1988-12-06, Place_Of_Birth: Nagpur, Country: India

ID: 7, First_Name: James, Last_Name: Anderson, Date_Of_Birth: 1982-06-30, Place_Of_Birth: Burnley , Country: England

Contents of the table after rollback