Sample Illustrating Savepoint Support in JDBC

Table Of Contents 

• Overview of the Sample Application
• Notations used
• Required Software
• Application Set-up and Configuration
• Running the Application 
• Using Oracle9i JDeveloper 
• From command line
• Description of Sample Files

This sample illustrates the usage of the JDBC 3.0 feature, Savepoint. Savepoints provide fine-grained control of transactions by marking intermediate points within a transaction. Once a savepoint has been set, the transaction can be rolled back to that savepoint without affecting preceding work. The DatabaseMetaData.supportsSavepoints method can be used to determine whether a JDBC API implementation supports savepoints.

The Savepoint interface allows you to partition a transaction into logical breakpoints, providing control over how much of the transaction gets rolled back. With the help of a small bank application which displays the details of all the accounts in the bank and how to manage it, this sample illustrates

• How to create a savepoint in the transaction
• How to rollback to a particular savepoint in the transaction
• How to rollback the complete transaction
• How transaction can be handled explicitly while transferring of accounts

Using this application, user can carry out following operations :

• Update their account balance and thus create a transaction
• As a banker he can save the changes he made to the accounts in the bank
• Rollback the changes be made upto a point selected
• Commit the changes he made to the accounts and store the changes in database when required
• Transfer amount between two accounts

The account number, customer name and balance present in the account is stored in a table. As and when required the transaction can be started by updating an account and removed by committing or doing a complete rollback. The unchanged changes made at the time of exit are rolled back.

Here is the code explaining retrieving PL/SQL Index By table OUT parameter. You can find more details of the code in SavepointSample.java file under src\oracle\otnsamples\oracle9ijdbc\savepoint folder. Look into Description of Sample Files section for folder and file details.

.....    
      // This method helps to creates an unnamed savepoint in the
      // current transaction and returns the new Savepoint object that
      // represents it. The syntax of this method is
      // Savepoint setSavepoint()
      // This method raises SQLException if the Connection is in auto commit mode.
      Savepoint savepoint = conn.setSavepoint();
......

.....    
     // Get the savepoint id from it.
      int selectedSavepointId = Integer.parseInt(selectedLogDetails.get(0).toString());

      // Using the savepoint id selected above get the savepoint object
      // which was got when the Savepoint was created from the hash table.
      Savepoint savepoint = (Savepoint)savepointsCreated.get(new Integer(
                                                    selectedSavepointId));

      // This method undoes all changes after the given Savepoint object was set
      // in the current transaction and releases any database locks currently
      // held by this Connection object. It is similar to
      // the Rollback command in SQL. The syntax of this method is

      // void rollback(Savepoint savepointName)
      //          where savepointName specifies the point up to which the
      // transaction has to be rolled back.
      conn.rollback(savepoint);
....

This following notations are used through out this document  

Notation	Description
<SAMPLE_HOME>	Folder where the SavepointSample will be unzipped. For example, C:\OTNSamples
<JAVA_HOME>	Folder where JAVA is installed. For example, C:\jdk1.2
<ORACLE_HOME>	Folder where Oracle Database or the Client is installed. For example, C:\ora9i
<JDBC_HOME>	Folder where the Oracle JDBC driver is installed. For example, C:\ora9i\jdbc\lib

• Oracle9i JDeveloper ( Note: Oracle9i JDeveloper is Oracle's Visual Java Development Tool and can be downloaded from here )
  or JDK1.4.x or above This can be downloaded from here .
• Oracle9i Database or higher running SQL*Net TCP/IP listener. This can be downloaded from here .
• Oracle9i JDBC Driver for use with JDK 1.4. Note that the ojdbc14.jar can be downloaded from here. The JDBC driver is also available at <ORACLE_HOME>/jdbc/lib.

• Unjar the provided SavepointSample.jar using the following command 

> jar xvf SavepointSample.jar

Note: You will find jar.exe in <JAVA_HOME>\bin. Ensure <JAVA_HOME>\bin is present in your system path.  For setting up environment variables in different platforms, please refer environment set up readme document

This creates a folder SavepointSample with all the source files.

• Edit Connection.properties file in <SAMPLE_HOME>\SavepointSample directory
  

  Change the following lines:
  

  # Your Database Connection details 
    HostName  = insn104a.idc.oracle.com
    Database  = ora9idb
    Port      = 1522
    UserName  = scott
    Password  = tiger

Database Setup

 

This sample application requires that the following table exists in the database.

Table Name : Account_Master

Column Name                   Column Type
-------------------------------------------------------
Account_Number            NUMBER (8)
Customer_Name            VARCHAR2(50)
Balance                            NUMBER
      This table holds the details of all the accounts present in the bank.

To create these, run SQL*Plus , connect to your test database and execute the PlsqlTablesSample.sql script as shown below.

SQL>@<SAMPLE_HOME>\SavepointSample\database\SavepointSample.sql

Specify the same username/password as connection params in the Connection.properties file.

 

Running the application using Oracle9i JDeveloper

Back To Top

This section describes the steps required to run this application using Oracle9i JDeveloper .

• Open Oracle9i JDeveloper and use File/Open option to select the SavepointSample.jws from the SavepointSample directory.
• Next, select Project/Make SavepointSample.jpr from main menu.
• Select the SavepointSample.java file and select Run/Run SavepointSample.java from JDeveloper main menu to run the application.
  Note : JDK1.4 must be set as the JDK version before running the sample.