How to do SQL Insert combined with auto-generated keys
retrieval using the execute() method?

With a DB connection, you must write the SQL Insert statement
and let this statement
be the first parameter to one of the below specified prepareStatement() methods.

If you have any parameter in your SQL Insert statement, then it is time to
set these using the PreparedStatment
setXXX() methods.

If you are using the first method,
the second parameter should be Statement.RETURN_GENERATED_KEYS.

If you use one of the last two methods,
the second parameter must be either an integer
array or a string array that in both cases must
represent the generated key-columns in the database table.

Now you can create a PreparedStatment object with one of the methods:

Methods in Connection interface

Description

PreparedStatement prepareStatement(String sql, int autoGeneratedKeys)

Creates a default PreparedStatement object that
has the capability to retrieve auto-generated keys.
This parameter is ignored if the SQL statement is not an INSERT statement,
or an SQL statement able to return auto-generated keys

PreparedStatement prepareStatement(String sql, int[] columnIndexes)

Creates a default PreparedStatement object
capable of returning the auto-generated keys designated by the given array.
The array contains the indexes (starting with 1 for the first) of the columns.
The driver will ignore the array if the SQL statement is not an
INSERT statement, or an SQL statement able to return auto-generated keys.

PreparedStatement prepareStatement(String sql, String[] columnNames)

Creates a default PreparedStatement object capable
of returning the auto-generated keys designated by the given array.
The array contains the names of the columns.
The driver will ignore the array if the SQL statement is not an
INSERT statement, or an SQL statement able to return auto-generated keys.

With the returned PreparedStatement object, you can execute the following
method to perform the SQL Insert statement:

Method in PreparedStatement interface

Description

boolean execute()

Executes the SQL statement in this
PreparedStatement object, which may be any kind of SQL statement.

After executed SQL Insert, use the getGeneratedKeys()
method in the PreparedStatement object to retrieve the db-table
key, which will be one or more columns of key data into a ResultSet.

You should use the close() method in the Statement object when you do not need it anymore.

You should also use the close() method in the Connection object when you do not need it anymore.

You can download this example here(needed tools can be found in the right menu on this page).

If we run this application the result should be:

As pure output

Translated by a Browser

<table border='1' ><tr><th colspan='5'>
The new car rental ID is:
108<br/> which is for the car:
</th></tr><tr><th>regNo</th><th>cartype</th><th>model</th><th>day<br/>Price</th><th>days</th></tr><tr><td>DE34562</td><td>OPEL</td><td>2001</td><td>340.0</td><td>10.0</td></tr></table>