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. With older JDBC drivers for MySQL, you could always use a MySQL-specific method on the Statement interface, or issue the query SELECT LASTINSERTID after issuing an INSERT to a table that had an AUTOINCREMENT key.
- Java prepared Statement get auto increment value after insert in MySQL How to get auto generated keys from MySQL database? PreparedStatement stmt = conn.prepareStatement(sql, PreparedStatement.RETURNGENERATEDKEYS); //.
- Now, to insert multiple records into this table by executing the multiple-row INSERT statement using Statement object and, to retrieve the auto-incremented values generated by it − Register the Driver class of the desired database using the registerDriver method of the DriverManager class or, the forName method of the class named Class.
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:![Mysql Insert Statement Return Generated Keys Mysql Insert Statement Return Generated Keys](/uploads/1/2/5/8/125872699/677840608.png)
The following is the complete example of inserting data into the
candidates
table and get the inserted ID back.Mysql Insert Statement Return Generated Keys In Word
Let’s run the program.
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.