- Details
- Written by Nam Ha Minh
- Last Updated on 05 June 2019 | Print Email
- Prepared Statement Get Generated Keys Select 2016
- Prepared Statement Get Generated Keys Select In Word
![Prepared Statement Get Generated Keys Select Prepared Statement Get Generated Keys Select](/uploads/1/2/5/8/125872516/612834837.jpg)
Write an example for JDBC prepared statement with ResultSet. How to get primary key value (auto-generated keys) from inserted queries using JDBC? Write a simple program for CallableStatement statement to execute stored procedure. Write a program for CallableStatement statement with stored procedure returns OUT parameters. The only way that some JDBC drivers to return Statement.RETURNGENERATEDKEYS is to do something of the following: long key = -1L; Statement statement = connection.createStatement; statement.executeUpdate(YOURSQLHERE, Statement.RETURNGENERATEDKEYS). Its value will be set by calling the setter methods of PreparedStatement. Why use PreparedStatement? Improves performance: The performance of the application will be faster if you use PreparedStatement interface because query is compiled only once. How to get the instance of PreparedStatement?
Write an example for JDBC prepared statement with ResultSet. How to get primary key value (auto-generated keys) from inserted queries using JDBC? Write a simple program for CallableStatement statement to execute stored procedure. Write a program for CallableStatement statement with stored procedure returns OUT parameters.
This post provides code example that gets the value of the newly inserted record in database using JDBC.
Some database operations, especially those involving parent-child relationships (one-to-many), you may want to insert a record into the parent table first, then into the child table. But the records in the child table must have a foreign key that refers to the parent table’s primary key. In such case, you need a technique that retrieves the value of the primary key of the last record inserted (usually auto-incremented by the database engine). JDBC provides such mechanism but implementation is slightly different for different database systems.
For MySQL and Java Derby database, use the following code:
For MySQL and Java Derby database, use the following code:
For Oracle database, use the following code:
Note: for Oracle database, you should create a sequence for the table’s primary key.
Prepared Statement Get Generated Keys Select 2016
Other JDBC Tutorials:
Prepared Statement Get Generated Keys Select In Word
About the Author:
Nam Ha Minh is certified Java programmer (SCJP and SCWCD). He started programming with Java in the time of Java 1.4 and has been falling in love with Java since then. Make friend with him on Facebook.Here is a small write-up which should help those who still write plain Java JDBC code. I know we have some wonderful persistence frameworks like Hibernate that make ones life comfortable but the reality is we still have to deal with plain old JDBC apis. If you are poor chap like me, below code should make your life easy.
Problem statement:
I just inserted a record in Oracle database using Java JDBC. The primary key column was auto populated by a sequence value. How should I get the last inserted records auto generated primary key?
Solution:
The solution should be getGeneratedKeys(). This method was added in JDBC 3.0 and it should be used to get last auto generated key value.
See code snippet below:
The above code should give us auto generated primary key value. The one thing to note here is method prepareStatement(). We passed two arguments first the insert query string and second an array of column name. The column name should be the primary key column name of table where you inserting the record.
Check below source code to see complete solution.
Full solution
We have a database table called
STUDENTS
. We also have an oracle sequence called STUDENT_SEQ
that we uses to generate primary key for STUDENTS table.![Prepared Statement Get Generated Keys Select Prepared Statement Get Generated Keys Select](/uploads/1/2/5/8/125872516/632538379.gif)
In Java, we use plain JDBC calls to insert a record in
STUDENTS
table. We uses sequence STUDENT_SEQ
to generate primary key. Once the record is inserted, we want the last inserted primary value.The above code is filled with comments and is pretty self explanatory. Finally we have last inserted value in
studentId
variable.The
getGeneratedKeys()
method is key here. It gives us the result set of all auto generated key values. In our case as we have only one auto generated value (for student_id column) we get only single record in this result set.