Mar 21, 2007 no matter how I tried to use java.sql.Statement.getGeneratedKeys I could not get the information I needed (last inserted id). Maybe it is a problem of versions (mysql-4.1 / mysql connector 3.1.10) but getGeneratedKeys always returns an empty ResultSet. So if I have to go to a platform dependant solution, here's a way to do it. Returning generated keys in MySql with JDBC PreparedStatement duplicate This question already has an answer here: I'm programming with plain JDBC a DAO layer because I only have 61.38 MB on Java Memory in my Tomcat (service hosting). Connection.prepareStatement(sql-statement, Statement.RETURNGENERATEDKEYS);The following forms are valid only if the data source supports SELECT FROM INSERT statements. Sql-statement can be a single-row INSERT statement or a multiple-row INSERT statement. With the first form, you specify the names of the columns for which you want automatically generated keys. Query the code lastinsertid/code. This returns the first id generated by your batch of rows. Then assume the next N consecutive id values are used by your batch of rows. This is a reliable method. If you read the source code for MySQL’s JD.
While creating a table, in certain scenarios, we need values to column such as ID, to be generated/incremented automatically. Various databases support this feature in different ways.
In MySQL database you can declare a column auto increment using the following syntax.
While inserting records in a table there is no need to insert value under the auto-incremented column. These will be generated automatically.
For example, in a table if we have a column with name ID and data type INT, which is auto-incremented and, if we already have 6 records in that table. When you insert the next record using the INSERT statement the ID value of the new record will be 7 and the ID value of its next record will be 8.
(You can specify the initial value and interval for these auto-incremented columns).
If you insert records into a table which contains auto-incremented column, using a PreparedStatement object.
You can retrieve the values of that particular column, generated by the current PreparedStatement object using the getGeneratedKeys() method.
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 −
Now, to insert records into this table using PreparedStatement object and, to retrieve the auto-incremented values generated by it −
To this method pass the INSERT statement with bind variables in string format as one parameter and, Statement.RETURN_GENERATED_KEYS as other parameter as −
After adding values of all the records to the batch, execute the batch using the executeBatch() method.
Following JDBC program inserts 5 records into the Sales table (created above) using PreparedStatement, retrieves and displays the auto-incremented values generated by it.
In this tutorial, you will learn how to use PreparedStatement object to insert data into MySQL table.
In the previous tutorial, we have shown you how to use the PreparedStatement object to update data. When you call the executeUpdate()
method, you get the number of rows affected. When you insert a record into a table, you may want to get the inserted ID back to the program for further processing. Let’s see how we can do it.
First, as always, you open a new connection to MySQL. You can utilized the utility class MySQLJDBCUtil
that we developed in the previous tutorial.
Then, you construct an INSERT
statement with placeholders and create a new PreparedStatement
object by calling the prepareStatement()
method of the Connection
object. You pass the INSERT statement as the first argument and an integer with value Statement.RETURN_GENERATED_KEYS
as the the second argument to the method. The second argument instructs JDBC to give the inserted ID back.
Next, you supply values for placeholders by calling setYYY()
method of the PreparedStatement
object.
After that, you call the executeUpdate()
method to execute the INSERT
statement. This method returns the number of rows affected. We check the return value to see if the record has been inserted successfully.
Finally, to get the inserted id, you call the getGeneratedKeys()
method of the PreparedStatement
object. The method returns a ResultSet
. You just need to get data out of this ResultSet
as follows:
The following is the complete example of inserting data into the candidates
table and get the inserted ID back.
Let’s run the program.
IDENTITY: Hibernate relies on an auto-incremented database column to generate the primary key,. Serial key generator. TABLE: Hibernate uses a database table to simulate a sequence.I prefer to use the GenerationType.SEQUENCE because it is very efficient and allows Hibernate to decide when to perform the insert statement. SEQUENCE: Hibernate requests the primary key value from a database sequence,. This provides the required flexibility to use other performance optimization techniques like JDBC batching.When you like to learn more about performance tuning and how Hibernate can optimize the GenerationType.SEQUENCE, have a look at my. With Hibernate 5.x, GenerationType.AUTO leads to the TABLE generator being used on MySQL DBs.
It shows that you have successfully inserted a new candidate into the candidates
table with id 134.
In this tutorial, we have shown you how to use PreparedStatement object to insert a new record into a MySQL table and get the inserted ID back for further processing.