setAutoCommit(false) Hello World example!

Hello!

I have the following table in the Database:
SHOW CREATE TABLE user_group;
 
CREATE TABLE `user_group` (
 `user_id` BIGINT(20) NOT NULL,
 `group_id` BIGINT(20) NOT NULL,
 
 PRIMARY KEY (`user_id`,`group_id`),
 KEY `FK72A9010BEF694ECE` (`user_id`),
 KEY `FK72A9010B2B0696E6` (`group_id`),
 
 CONSTRAINT `FK72A9010B2B0696E6` FOREIGN KEY (`group_id`) REFERENCES `app_group` (`id`),
 CONSTRAINT `FK72A9010BEF694ECE` FOREIGN KEY (`user_id`) REFERENCES `app_user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

2 sample rows from the database:
SELECT * FROM user_group WHERE user_id = 241;
+---------+----------+
| user_id | group_id |
+---------+----------+
|     241 |        2 |
|     241 |       13 |
+---------+----------+

and let 's try inserting a valid data with autoCommit = false!
import java.sql.*;
 
public class HelloWorld {
 
    private static final String DATB_URL = "jdbc:mysql://localhost:3306/test";
    private static final String USERNAME = "test";
    private static final String PASSWORD = "test";
 
    public static void main(String[] args) throws SQLException {
        // Prepare..
        final Connection connection = DriverManager.getConnection(DATB_URL, USERNAME, PASSWORD);
        connection.setAutoCommit(false);
 
        final String query = "INSERT INTO user_group VALUES (241, 3)";
        final PreparedStatement preparedStatement = connection.prepareStatement(query);
 
        // Execute..
        preparedStatement.execute();
 
        // Close..
        preparedStatement.close();
        connection.close();
    }
}

And execute our SELECT query again:
SELECT * FROM user_group WHERE user_id = 241;
+---------+----------+
| user_id | group_id |
+---------+----------+
|     241 |        2 |
|     241 |       13 |
+---------+----------+

Ah, ok very nice! So let 's commit after execution..
// Execute.. 
preparedStatement.execute();
connection.commit();

And..
mysql> SELECT * FROM user_group WHERE user_id = 241;
+---------+----------+
| user_id | group_id |
+---------+----------+
|     241 |        2 |
|     241 |        3 |
|     241 |       13 |
+---------+----------+

This is nice.. Let 's add some notes..

Transactions in SQL..

A transaction is a sequence of one or more SQL statements that together form a logical unit of work. The SQL statements that form the transaction are typically closely related and perform interdependent actions. Each statement in the transaction performs some part of a task, but all of them are required to complete the task.

The transaction concept is critical for programs that update a database, because it ensures the integrity of the database.

The statements in a transaction will be executed as an atomic unit of work in the database. Either the results of all of the statements will be applied to the database, or none of the statements will have results posted to the database.

The ANSI/ISO SQL standard defines a SQL transaction model.
  • START TRANSACTION Sets the properties of a new transaction and starts that transaction.
  • COMMIT Terminates a successful transaction and commits all changes to the database.
  • ROLLBACK When used without a savepoint, terminates an unsuccessful transaction and rolls back any changes to the beginning of a transaction, essentially restoring the database to its consistent state before the transaction (as if the transaction had never executed). When used with a savepoint, rolls back the transaction to the named savepoint, but allows it to continue.

Transactions in JDBC..

All JDBC compliant drivers are required to provide transaction support.

When to start a new transaction is a decision made implicitly by either the JDBC driver or the underlying data source. Although some data sources implement an explicit "begin transaction" statement, there is no JDBC API to do so. Typically, a new transaction is started when the current SQL statement requires one and there is no transaction already in place. Whether or not a given SQL statement requires a transaction is specified by SQL:2003.

The Connection attribute auto-commit specifies when to end transactions. Enabling auto-commit causes a transaction commit after each individual SQL statement as soon as that statement is complete. The point at which a statement is considered to be “complete” depends on the type of SQL statement as well as what the application does after executing it:
  • For Data Manipulation Language (DML) statements such as Insert, Update, Delete, and DDL statements, the statement is complete as soon as it has finished executing.
  • For Select statements, the statement is complete when the associated result set is closed.
  • For CallableStatement objects or for statements that return multiple results, the statement is complete when all of the associated result sets have been closed, and all update counts and output parameters have been retrieved.
Here is a related question from Stackoverflow!
Here is a link to using Transactions in JDBC.