[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2004-11-01
6
Medium Priority
?
199 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

In this post we will learn different types of Android Layout and some basics of an Android App.
Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:
Suggested Courses

656 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