java09_main.java

Transaction abort. Insert and commit some rows. Insert more rows, but abort the transaction. Select all rows and see only the first set of rows.

import java.sql.*;
 
public class java09_main {
 
/*
                                                    
     * EXAMPLE - java09 for JDBC
                                                    
     * This example shows how to use transactions with SQL commands.  A database
                                                    
     * is created and then data is inserted within a transaction, which is
                                                    
     * committed.  Another transaction is also started and more data is inserted
                                                    
     * but this time the transaction is aborted.  The data is then read out and
                                                    
     * displayed showing that only the data in the first transaction is in the
                                                    
     * database
                                                    
     * 
                                                    
     * Full documentation for RDM is available at http://docs.raima.com/rdm/
                                                    
     * 
                                                    
     * For simplicity this example utilizes basic exception handling
                                                    
     * but good programming practices would dictate that this occur in 
                                                    
     * a full application.
                                                    
     */
                                                    
public static void main(String[] args) throws SQLException  {
 
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

                                                    
try
                                                    
        {
/* Create the connection with a simple connection string */
                                                    
            conn = DriverManager.getConnection("jdbc:raima:rdm://local");

                                                    
/* disable AutoCommit to allow multiple SQL statements to be grouped into a transaction */
                                                    
            conn.setAutoCommit(false);

                                                    
            CreateDatabases(conn);

                                                    
/* Open the connection to the database */
                                                    
            stmt = conn.createStatement();

                                                    
/* Insert three rows into the table */
                                                    
for (int ii = 1; ii < 4; ii++) 
            {
                stmt.executeUpdate("INSERT INTO Table1 VALUES " + ii);
            }
/* Commit the transaction */
                                                    
            conn.commit();

                                                    
/* Start a new transaction */
                                                    
/* Insert three rows into the table */
                                                    
for (int ii = 4; ii < 7; ii++) 
            {
                stmt.executeUpdate("INSERT INTO Table1 VALUES " + ii);
            }

                                                    
/* Rollback the transaction */
                                                    
            conn.rollback();

                                                    
/* Scan through the data, generating the result */
                                                    
            rs = stmt.executeQuery("SELECT id FROM Table1");
while (rs.next() != false)
            {
                System.out.println(rs.getInt(1));
            }
        }
catch (SQLException sqle)
        {
            sqle.printStackTrace();
        }
finally
                                                    
        {
if(rs != null)
                rs.close();
 
if(stmt != null)
                stmt.close();

                                                    
if(conn != null)
                conn.close();           
        }
    }

                                                    
private static void CreateDatabases(Connection conn)
    {
/* Create the example databases */
                                                    
        String query = "CREATE TABLE Table1 (id INTEGER PRIMARY KEY)";

                                                    
/* Drop any existing copies of the database */
                                                    
        DropDatabase(conn, "java09");

                                                    
/* Attempt to create the databases */
                                                    
        CreateThisDatabase(conn, "java09", query);
    }

                                                    
private static void CreateThisDatabase(Connection conn, String dbName, String query)
    {
        Statement stmt = null;
/* Create the specified database.  Make sure the database is
                                                    
         * closed when we are done so other databases can be created
                                                    
         * after this if necessary. */
                                                    
try
                                                    
        {
            stmt = conn.createStatement();
            stmt.execute("CREATE DATABASE " + dbName);
            stmt.execute(query);
            conn.commit();
        }
catch(SQLException sqle) {}
    }

                                                    
private static void DropDatabase(Connection conn, String dbName)
    {
        Statement stmt = null;
/* Attempt to drop this database.  If the database does not
                                                    
         * already exist this will fail and we simply ignore the
                                                    
         * failure for this sample. */
                                                    
try
                                                    
        {
/* Create the example database */
                                                    
            stmt = conn.createStatement();
            stmt.executeUpdate("DROP DATABASE " + dbName);
        }
catch (SQLException sqle) {}
    }
}