• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 205
  • Last Modified:

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

/*
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
fredand44
Asked:
fredand44
1 Solution
 
DrWarezzCommented:
What do you mean by "it doesn't work"?
Does an error occur when you run it....???

gL,
[r.D]
0
 
fredand44Author Commented:
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
 
reginabCommented:
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
 
fredand44Author Commented:
/*
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
 
moduloCommented:
PAQed with points refunded (25)

modulo
Community Support Moderator
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now