?
Solved

How to create a rollback with MySql and Java? (test source valid)

Posted on 2004-11-01
6
Medium Priority
?
201 Views
Last Modified: 2010-03-31
/*
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)
);

To connect to this database I use a properties file thats looks like:
dbDriver                  com.mysql.jdbc.Driver
dbConnectionString      jdbc:mysql://localhost/my_test_database?user=root&password=my_pass

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

BTW I just got 25 points, sorry!


*/


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();
                  }
            }
      }

}
0
Comment
Question by:fredand44
5 Comments
 
LVL 9

Expert Comment

by:DrWarezz
ID: 12465542
What do you mean by "it doesn't work"?
Does an error occur when you run it....???

gL,
[r.D]
0
 

Author Comment

by:fredand44
ID: 12465671
Hello DrWarezz!

The first insert will always be committed even if I do a rollback. That is what I mean it doesn't work.

Best regards
Fredrik
0
 
LVL 4

Expert Comment

by:reginab
ID: 12467467
yep because you commit it before you check for an exception.  check the exception and then only commit. else roll back. you cannot roll back beyond the last commit.
0
 

Author Comment

by:fredand44
ID: 12476023
/*
Hello!
The error was the type of DB. At first I did not used InnoDB but after I have created a my.cnf file and restarted the MySQl database with --defaults-extra-file=C:/mysql/data/my.cnf
I could create my tables like:

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

CREATE TABLE test2
(
id INT NOT NULL AUTO_INCREMENT,
test1_id INT NOT NULL,
PRIMARY KEY(id)
)
TYPE = INNODB;

After that everything worked fine if I got a exception and needed to do a rollback.

Best regards
Fredrik

*/
0
 

Accepted Solution

by:
modulo earned 0 total points
ID: 12828011
PAQed with points refunded (25)

modulo
Community Support Moderator
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After being asked a question last year, I went into one of my moods where I did some research and code just for the fun and learning of it all.  Subsequently, from this journey, I put together this article on "Range Searching Using Visual Basic.NET …
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
Video by: Michael
Viewers learn about how to reduce the potential repetitiveness of coding in main by developing methods to perform specific tasks for their program. Additionally, objects are introduced for the purpose of learning how to call methods in Java. Define …
Viewers learn how to read error messages and identify possible mistakes that could cause hours of frustration. Coding is as much about debugging your code as it is about writing it. Define Error Message: Line Numbers: Type of Error: Break Down…
Suggested Courses
Course of the Month8 days, 7 hours left to enroll

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question