In this tutorial, you will learn how to use commit() and rollback() methods of the Connection object to control transaction.

1. Mysql Jdbc Url
2. Java Prepared Statement Return Generated Keys
3. Mysql Jdbc Return_generated_keys Download
4. Mysql Jdbc Maven

Using auto generated keys.; 2 minutes to read +2; In this article. Download JDBC Driver. The Microsoft JDBC Driver for SQL Server supports the optional JDBC 3.0 APIs to retrieve automatically generated row identifiers. In this tutorial, we use the MySQL Connector/J driver. It is the official JDBC driver for MySQL. The examples were created and tested on Ubuntu Linux. You might also want to check Java tutorial, PostgreSQL Java tutorial, Apache Derby tutorial, MySQL tutorial,. The existence of a WfbUtilities class makes it possible that your ptmt2 variable refers to some subclass of PreparedStatement which is a wrapper for whatever MySQL returns. Perhaps the implementer of that subclass was lazy and didn't get around to implementing the getGeneratedKeys method.

Setting auto-commit mode

When you connect to MySQL databases, the auto-commit mode is set to true by default. It means that the changes will be applied to the database once the statement successfully executed. In case you want to control when to commit the transaction, you call the setAutoCommit() method of the Connection object as follows:

Once you have set auto-commit mode to false , you can call commit() or rollback() methods of the Connection object to commit or rollback the transaction.

Notice that you should always call setAutoCommit() method right after you open a connection to the database.

Committing and rolling back a transaction

Once the auto-commit mode is set to false , you can commit or rollback the transaction. The flow of using those methods is as follows:

MySQL JDBC transaction example

In this example, we will insert a new record into the candidates table and also assign some of skills to the newly inserted candidate.

We will perform both inserting a candidate and assigning skills within one transaction. The steps will be as follows:

1. Insert a record into the candidates table and get the inserted ID back.
2. Insert a set of candidate ID and Skill ID into the candidate_skills table.
3. If all above operations are successfully, commit the transaction, otherwise roll it back.

The following is the complete example of using JDBC transaction.

Let’s check the table candidates table before we run the program.

Now, we run the program.

Mysql Jdbc Url

and check the candidates table again:

and check also the candidate_skills table to see if the assignments are there.

As you see we have successfully inserted data into both candidates and candidate_skills table within the same transaction.

• Related Questions & Answers

• Selected Reading

Java Prepared Statement Return Generated Keys

If you insert records into a table which contains auto-incremented column, using a Statement or, PreparedStatement objects.

You can retrieve the values of that particular column, generated by them object using the getGeneratedKeys() method.

Example

download a clone terminal mac Let us create a table with name sales in MySQL database, with one of the columns as auto-incremented, using CREATE statement as shown below −

Retrieving auto-generated values (PreparedStatement object)

Following JDBC program inserts 3 records into the Sales table (created above) using PreparedStatement, retrieves and displays the auto-incremented values generated by it.

Example

Mysql Jdbc Return_generated_keys Download

Output

Retrieving auto-generated values (Statement object)

Following JDBC program inserts 3 records into the Sales table (created above) using Statement, retrieves and displays the auto-incremented values generated by it.

Mysql Jdbc Maven

Output