Solved

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

Posted on 2004-11-01
183 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
Question by:fredand44
    5 Comments
     
    LVL 9

    Expert Comment

    by:DrWarezz
    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
    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
    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
    /*
    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:
    PAQed with points refunded (25)

    modulo
    Community Support Moderator
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Suggested Solutions

    For customizing the look of your lightweight component and making it look lucid like it was made of glass. Or: how to make your component more Apple-ish ;) This tip assumes your component to be of rectangular shape and completely opaque. (COD…
    Introduction This article is the second of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers the basic installation and configuration of the test automation tools used by…
    Viewers learn about the “for” loop and how it works in Java. By comparing it to the while loop learned before, viewers can make the transition easily. You will learn about the formatting of the for loop as we write a program that prints even numbers…
    Viewers will learn about the regular for loop in Java and how to use it. Definition: Break the for loop down into 3 parts: Syntax when using for loops: Example using a for loop:

    934 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

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now