- JDBC Tutorial
- JDBC - Home
- JDBC - Introduction
- JDBC - SQL Syntax
- JDBC - Environment
- JDBC - Sample Code
- JDBC - Driver Types
- JDBC - Connections
- JDBC - Statements
- JDBC - Result Sets
- JDBC - Data Types
- JDBC - Transactions
- JDBC - Exceptions
- JDBC - Batch Processing
- JDBC - Stored Procedure
- JDBC - Streaming Data
- JDBC Examples
- JDBC - Create Database
- JDBC - Select Database
- JDBC - Drop Database
- JDBC - Create Tables
- JDBC - Drop Tables
- JDBC - Insert Records
- JDBC - Select Records
- JDBC - Update Records
- JDBC - Delete Records
- JDBC - WHERE Clause
- JDBC - Like Clause
- JDBC - Sorting Data
- JDBC Useful Resources
- JDBC - Questions and Answers
- JDBC - Quick Guide
- JDBC - Useful Resources
- JDBC - Discussion
- Useful - Java Tutorials
JDBC - setSavepoint, releaseSavepoint Example
Following is the example, which makes use of setSavepoint and rollback described in the Transaction tutorial.
This sample code has been written based on the environment and database setup done in the previous chapters.
Copy and paste the following example in JDBCExample.java, compile and run as follows −
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Savepoint; import java.sql.Statement; public class JDBCExample { static final String DB_URL = "jdbc:mysql://localhost/TUTORIALSPOINT"; static final String USER = "guest"; static final String PASS = "guest123"; static final String QUERY = "SELECT id, first, last, age FROM Employees"; static final String DELETE_QUERY = "DELETE FROM Employees WHERE ID = 8"; static final String DELETE_QUERY_1 = "DELETE FROM Employees WHERE ID = 9"; public static void printResultSet(ResultSet rs) throws SQLException{ // Ensure we start with first row rs.beforeFirst(); while(rs.next()){ // Display values System.out.print("ID: " + rs.getInt("id")); System.out.print(", Age: " + rs.getInt("age")); System.out.print(", First: " + rs.getString("first")); System.out.println(", Last: " + rs.getString("last")); } System.out.println(); } public static void main(String[] args) { // Open a connection try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS); Statement stmt = conn.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); ) { conn.setAutoCommit(false); ResultSet rs = stmt.executeQuery(QUERY); System.out.println("List result set for reference...."); printResultSet(rs); // delete row having ID = 8 // But save point before doing so. Savepoint savepoint1 = conn.setSavepoint("ROWS_DELETED_1"); System.out.println("Deleting row...."); stmt.executeUpdate(DELETE_QUERY); // Rollback the changes after save point 1. conn.rollback(savepoint1); // delete rows having ID = 9 // But save point before doing so. conn.setSavepoint("ROWS_DELETED_2"); System.out.println("Deleting row...."); stmt.executeUpdate(DELETE_QUERY_1); rs = stmt.executeQuery(QUERY); System.out.println("List result set for reference...."); printResultSet(rs); // Clean-up environment rs.close(); } catch (SQLException e) { e.printStackTrace(); } } }
Now, let us compile the above example as follows −
C:\>javac JDBCExample.java C:\>
When you run JDBCExample, it produces the following result −
C:\>java JDBCExample List result set for reference.... ID: 1, Age: 23, First: Zara, Last: Ali ID: 2, Age: 30, First: Mahnaz, Last: Fatma ID: 3, Age: 35, First: Zaid, Last: Khan ID: 4, Age: 33, First: Sumit, Last: Mittal ID: 5, Age: 40, First: John, Last: Paul ID: 7, Age: 20, First: Sita, Last: Singh ID: 8, Age: 20, First: Rita, Last: Tez ID: 9, Age: 20, First: Sita, Last: Singh Deleting row.... Deleting row.... List result set for reference.... ID: 1, Age: 23, First: Zara, Last: Ali ID: 2, Age: 30, First: Mahnaz, Last: Fatma ID: 3, Age: 35, First: Zaid, Last: Khan ID: 4, Age: 33, First: Sumit, Last: Mittal ID: 5, Age: 40, First: John, Last: Paul ID: 7, Age: 20, First: Sita, Last: Singh ID: 8, Age: 20, First: Rita, Last: Tez C:\>
Advertisements