- Related Questions & Answers
- Mysqli-prepare returns a so-called statement object which is used for subsequent operations eg execute, bindparam, storeresult, bindresult, fetch, etc. The statement object has private properties which update as each statement operation is carried out.
- In this example, while creating an instance of PreparedStatement, we have passed 2 arguments. 1st is the query itself and 2 nd is “Statement.RETURNGENERATEDKEYS“, which will help us to get the primary key value of the new row. The below code is used to provide parameters for Insert Query.
- Selected Reading
What are the types of JDBC Statements available? Write an example code for JDBC prepared statement. 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. SQL syntax for prepared statements does not support multi-statements (that is, multiple statements within a single string separated by; characters). Prepared statements use the query cache under the conditions described in Section 8.10.3.1, “How the Query Cache Operates”. 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.
JDBCObject Oriented ProgrammingProgramming
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).
Retrieving the auto-incremented values
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.
Example
https://powerfulwhich.weebly.com/generate-a-jwt-secret-key.html. 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 −
- 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.
- Create a Connection object by passing the URL of the database, user-name and password of a user in the database (in string format) as parameters to the getConnection() method of the DriverManager class.
- Create a PreparedStatement object using the prepareStatement() method of the connection interface.
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 −
- Set values of each record to the bind variables using the setXXX() methods and, add it to batch.
Powershell generate machine key iis. After adding values of all the records to the batch, execute the batch using the executeBatch() method.
- Finally, get the auto-incremented keys generated by this PreparedStatement object using the getGeneratedKeys() 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.
Example
Output
Here is a small write-up which should help those who still write plain Java JDBC code. https://powerfulwhich.weebly.com/zbrush-4r8-list-of-serial-keys-online-generator.html. 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.
![Prepared Statement Get Generated Keys Select Prepared Statement Get Generated Keys Select](/uploads/1/2/6/0/126094386/380693197.jpg)
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.
Prepared Statement Get Generated Keys Select 2
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.
![Prepared statement get generated keys select 1 Prepared statement get generated keys select 1](/uploads/1/2/6/0/126094386/171391252.jpg)
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 2016
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. https://powerfulwhich.weebly.com/dell-windows-vista-home-premium-product-key-generator.html. In our case as we have only one auto generated value (for student_id column) we get only single record in this result set.