How to create a rollback? (test source valid)
Posted by: Fredrik Andersson
Date: November 01, 2004 12:39PM

/*
Hello!
I try to find out how to use real transactions with Java and a MySQL database.
With transaction I mean that if a insert or update is done and we got a exception occurs before an other
insert or update is done we will be able to undo the first insert or update (a rollback).

But I do not manage to create a rollback.
What I want to do is to run a rollback when a Exception occurs.

Below is my full test code ready to use, which I thought would do a rollback but it doesn't.

My tables in a database in MySQL looks like:

CREATE TABLE test1
(
id INT NOT NULL AUTO_INCREMENT,
date DATETIME NOT NULL,
PRIMARY KEY(id)
);

CREATE TABLE test2
(
id INT NOT NULL AUTO_INCREMENT,
test1_id INT NOT NULL,
PRIMARY KEY(id),
FOREIGN KEY (test1_id) REFERENCES test1(id)
);


So if any one could tell me how to create a rollback or tell me if there is a bug please let me know.

Unfortunally I do not use the latest MySQL database, but I use the latest jdbc-driver. And I also use Java 1.4.0.

Best regards
Fredrik


*/


import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;
import java.util.*;
import java.sql.*;

public class GuestTest2
{
public static void main(String[] args)
{
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;

/*
This block insert 1 row in table test1 and 1 row in table test2
If we get a Exception there should be a rollback so all changes is undone.

To create a exception we can comment the row
preparedStatement.setInt(1, id.intValue());
to
//preparedStatement.setInt(1, id.intValue());
*/
try
{
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection("jdbc:mysql://localhost/internetprogrammering_uppgift6?user=root&password=pass");
connection.setAutoCommit(false);
preparedStatement = connection.prepareStatement("INSERT INTO test1 (date) VALUES(?)");
preparedStatement.setTimestamp(1, new Timestamp(System.currentTimeMillis()) );
preparedStatement.execute();

preparedStatement = connection.prepareStatement("SELECT MAX(id) FROM test1");
preparedStatement.execute();
resultSet = preparedStatement.getResultSet();
resultSet.next();
Integer id = (Integer)resultSet.getObject(1);

preparedStatement = connection.prepareStatement("INSERT INTO test2 (test1_id) VALUES(?)");
//Comment row below to create a exception
preparedStatement.setInt(1, id.intValue());
preparedStatement.execute();

connection.commit();

resultSet.close();
preparedStatement.close();
connection.close();
}
catch(Exception e1)
{
e1.printStackTrace();
try
{
System.out.println("Rollback");
connection.rollback();
connection.commit();
}
catch(Exception e2)
{
e2.printStackTrace();
}
}
finally
{
try
{
if(resultSet != null)
{
resultSet.close();
}
if(preparedStatement != null)
{
preparedStatement.close();
}
if(connection != null)
{
connection.close();
}
}
catch(Exception e2)
{
e2.printStackTrace();
}
}


/*
This section shows whats in the database after insert with or with out rollbacks.
*/
try
{
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection("jdbc:mysql://localhost/internetprogrammering_uppgift5?user=root&password=snuffa");
preparedStatement = connection.prepareStatement("SELECT * FROM test1");
preparedStatement.execute();
resultSet = preparedStatement.getResultSet();
while( resultSet.next() )
{
StringBuffer stringBuffer = new StringBuffer();
stringBuffer.append(resultSet.getString(1));
stringBuffer.append(" ");
stringBuffer.append(resultSet.getString(2));
System.out.println( stringBuffer.toString() );
}

resultSet.close();
preparedStatement.close();
connection.close();
}
catch(Exception e)
{
e.printStackTrace();
}
finally
{
try
{
if(resultSet != null)
{
resultSet.close();
}
if(preparedStatement != null)
{
preparedStatement.close();
}
if(connection != null)
{
connection.close();
}
}
catch(Exception e2)
{
e2.printStackTrace();
}
}
}

}

Options: ReplyQuote


Subject
Written By
Posted
How to create a rollback? (test source valid)
November 01, 2004 12:39PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.