Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to create and release a save point in JDBC?
When you set a save point you define a logical rollback point within a transaction. If an error occurs past a save point, you can use the rollback method to undo either all the changes or only the changes made after the save point.
Savepoint interface gives you the additional transactional control. Most modern DBMS, support save points within their environments such as Oracle's PL/SQL.
Setting a save point
You can set a save point in a database using the setSavepoint(String savepointName) method of the Connection interface, this method accepts a string value representing the name of the save point and returns a Savepoint object.
Releasing a save point
You can release a save point using the releaseSavepoint(Savepoint savepointName) method of the Connection interface, this method accepts the name of the savepoint and releases/deletes the specified save point.
Example
Assume we have a table named customers in the database with 5 records as shown below:
+----+-----------+------+---------+----------------+ | 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 | +----+-----------+------+---------+----------------+
Following JDBC program Inserts 7 more records to it, sets a save point, removes some records and roll backs to the save point.
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://localhost/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();
}
}
}Output
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
Updated on: 30-Jul-2019
656 Views
Advertisements