資料結構
網路
關係資料庫管理系統
作業系統
Java
MS Excel
iOS
HTML
CSS
Android
Python
C 程式設計
C++
C#
MongoDB
MySQL
Javascript
PHP
物理學
化學
生物學
數學
英語
經濟學
心理學
社會學
時尚研究
法律研究如何在 JDBC 中建立和釋放儲存點？
當您設定儲存點時，您就在事務中定義了一個邏輯回滾點。如果在儲存點之後發生錯誤，您可以使用回滾方法撤消所有更改或僅撤消儲存點之後所做的更改。
Savepoint 介面為您提供了額外的交易控制。大多數現代 DBMS 在其環境中支援儲存點，例如 Oracle 的 PL/SQL。
設定儲存點
您可以使用 Connection 介面的 **setSavepoint(String savepointName)** 方法在資料庫中設定儲存點，此方法接受表示儲存點名稱的字串值並返回一個 Savepoint 物件。
釋放儲存點
您可以使用 Connection 介面的 **releaseSavepoint(Savepoint savepointName)** 方法釋放儲存點，此方法接受儲存點的名稱並釋放/刪除指定的儲存點。
示例
假設我們有一個名為 customers 的資料庫表，其中包含 5 條記錄，如下所示
+----+-----------+------+---------+----------------+ | ID | NAME | AGE | SALARY | ADDRESS | +----+-----------+------+---------+----------------+ | 1 | Amit | 25 | 3000.00 | Hyderabad | | 2 | Kalyan | 27 | 4000.00 | Vishakhapatnam | | 3 | Renuka | 30 | 5000.00 | Delhi | | 4 | Archana | 24 | 1500.00 | Mumbai | | 5 | Koushik | 30 | 9000.00 | Kota | +----+-----------+------+---------+----------------+
以下 JDBC 程式向其中插入 7 條更多記錄，設定一個儲存點，刪除一些記錄並回滾到儲存點。
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Savepoint;
public class SavepointExample {
public static void main(String args[]) throws SQLException {
//Registering the Driver
DriverManager.registerDriver(new com.mysql.jdbc.Driver());
//Getting the connection
String mysqlUrl = "jdbc:mysql:///mydatabase";
Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");
System.out.println("Connection established......");
//Setting auto-commit false
con.setAutoCommit(false);
System.out.println(" ");
//Creating the Statement
PreparedStatement pstmt = con.prepareStatement("INSERT into customers VALUES (?, ?, ?, ?, ?) ");
pstmt.setInt(1, 6);
pstmt.setString(2, "Hardik");
pstmt.setInt(3, 45);
pstmt.setInt(4, 6400);
pstmt.setString(5, "Bhopal");
pstmt.executeUpdate();
pstmt.setInt(1, 7);
pstmt.setString(2, "Trupti");
pstmt.setInt(3, 33);
pstmt.setInt(4, 4360);
pstmt.setString(5, "Ahmedabad");
pstmt.executeUpdate();
pstmt.setInt(1, 8);
pstmt.setString(2, "Mithili");
pstmt.setInt(3, 26);
pstmt.setInt(4, 4100);
pstmt.setString(5, "Vijayawada");
pstmt.executeUpdate();
pstmt.setInt(1, 9);
pstmt.setString(2, "Maneesh");
pstmt.setInt(3, 39);
pstmt.setInt(4, 4000);
pstmt.setString(5, "Hyderabad");
pstmt.executeUpdate();
pstmt.setInt(1, 10);
pstmt.setString(2, "Rajaneesh");
pstmt.setInt(3, 30);
pstmt.setInt(4, 6400);
pstmt.setString(5, "Delhi");
pstmt.executeUpdate();
pstmt.setInt(1, 11);
pstmt.setString(2, "Komal");
pstmt.setInt(3, 29);
pstmt.setInt(4, 8000);
pstmt.setString(5, "Ahmedabad");
pstmt.executeUpdate();
pstmt.setInt(1, 12);
pstmt.setString(2, "Manyata");
pstmt.setInt(3, 25);
pstmt.setInt(4, 5000);
pstmt.setString(5, "Vijayawada");
pstmt.executeUpdate();
//Setting save point
Savepoint savePoint = con.setSavepoint("mysavepoint");
System.out.println(" ");
System.out.println("Contents of the customers table after inserting the records: ");
Statement stmt = con.createStatement();
//Retrieving data
ResultSet rs = stmt.executeQuery("Select * from customers");
while(rs.next()) {
System.out.print("ID: "+rs.getInt("ID")+", ");
System.out.print("Name: "+rs.getString("Name")+", ");
System.out.print("Age: "+rs.getInt("Age")+", ");
System.out.print("Salary: "+rs.getInt("Salary")+", ");
System.out.print("Address: "+rs.getString("Address"));
System.out.println();
}
//Deleting the records
stmt.execute("Delete from customers where id > 5");
System.out.println(" ");
System.out.println("Contents of the customers table after deleting the records: ");
//Retrieving data
rs = stmt.executeQuery("Select * from customers");
while(rs.next()) {
System.out.print("ID: "+rs.getInt("ID")+", ");
System.out.print("Name: "+rs.getString("Name")+", ");
System.out.print("Age: "+rs.getInt("Age")+", ");
System.out.print("Salary: "+rs.getInt("Salary")+", ");
System.out.print("Address: "+rs.getString("Address"));
System.out.println();
}
//Rolling back to the save point
con.rollback(savePoint);
System.out.println(" ");
System.out.println("Contents of the table at the save point: ");
//Retrieving data
rs = stmt.executeQuery("Select * from customers");
while(rs.next()) {
System.out.print("ID: "+rs.getInt("ID")+", ");
System.out.print("Name: "+rs.getString("Name")+", ");
System.out.print("Age: "+rs.getInt("Age")+", ");
System.out.print("Salary: "+rs.getInt("Salary")+", ");
System.out.print("Address: "+rs.getString("Address"));
System.out.println();
}
}
}輸出
Connection established...... Contents of the customers table after inserting the records: ID: 1, Name: Amit, Age: 25, Salary: 3000, Address: Hyderabad ID: 2, Name: Kalyan, Age: 27, Salary: 4000, Address: Vishakhapatnam ID: 3, Name: Renuka, Age: 30, Salary: 5000, Address: Delhi ID: 4, Name: Archana, Age: 24, Salary: 1500, Address: Mumbai ID: 5, Name: Koushik, Age: 30, Salary: 9000, Address: Kota ID: 6, Name: Hardik, Age: 45, Salary: 6400, Address: Bhopal ID: 7, Name: Trupti, Age: 33, Salary: 4360, Address: Ahmedabad ID: 8, Name: Mithili, Age: 26, Salary: 4100, Address: Vijayawada ID: 9, Name: Maneesh, Age: 39, Salary: 4000, Address: Hyderabad ID: 10, Name: Rajaneesh, Age: 30, Salary: 6400, Address: Delhi ID: 11, Name: Komal, Age: 29, Salary: 8000, Address: Ahmedabad ID: 12, Name: Manyata, Age: 25, Salary: 5000, Address: Vijayawada Contents of the customers table after deleting the records: ID: 1, Name: Amit, Age: 25, Salary: 3000, Address: Hyderabad ID: 2, Name: Kalyan, Age: 27, Salary: 4000, Address: Vishakhapatnam ID: 3, Name: Renuka, Age: 30, Salary: 5000, Address: Delhi ID: 4, Name: Archana, Age: 24, Salary: 1500, Address: Mumbai ID: 5, Name: Koushik, Age: 30, Salary: 9000, Address: Kota Contents of the table at the save point: ID: 1, Name: Amit, Age: 25, Salary: 3000, Address: Hyderabad ID: 2, Name: Kalyan, Age: 27, Salary: 4000, Address: Vishakhapatnam ID: 3, Name: Renuka, Age: 30, Salary: 5000, Address: Delhi ID: 4, Name: Archana, Age: 24, Salary: 1500, Address: Mumbai ID: 5, Name: Koushik, Age: 30, Salary: 9000, Address: Kota ID: 6, Name: Hardik, Age: 45, Salary: 6400, Address: Bhopal ID: 7, Name: Trupti, Age: 33, Salary: 4360, Address: Ahmedabad ID: 8, Name: Mithili, Age: 26, Salary: 4100, Address: Vijayawada ID: 9, Name: Maneesh, Age: 39, Salary: 4000, Address: Hyderabad ID: 10, Name: Rajaneesh, Age: 30, Salary: 6400, Address: Delhi ID: 11, Name: Komal, Age: 29, Salary: 8000, Address: Ahmedabad ID: 12, Name: Manyata, Age: 25, Salary: 5000, Address: Vijayawada
更新於: 2019-07-30
820 次瀏覽
廣告