CODEWITHSUNDEEP
java
Ahsan Abid
Ahsan Abid

Reputation: 609

How to get auto generated keys of a table while using java prepared statement Batch?

How to get auto generated keys of a table while using java prepared statement Batch? One way to do is to iterate the resultset that is returned? Is there is any other efficient way of doing it?

Upvotes: 1

Views: 2324

Answers (4)

Ravinder Reddy
Ravinder Reddy

Reputation: 24002

Use getGeneratedKeys() method from your Statement or PreparedStatement object to identify the new auto generated values. Iterate the returned ResultSet object to get the newly generated key values in the order of batch statements.

Edit: This call may throw java.sql.SQLFeatureNotSupportedException if the JDBC driver, that you are using, does not support this method.

Upvotes: 1

Sergey Gazaryan
Sergey Gazaryan

Reputation: 1043

I think you need to use the OUTPUT clause,like this:

--====== make our test table to insert to
Create Table InsertIDTst(
        ID int identity primary key
        , ColName nvarchar(255)
        , object_id int);
GO

--====== make a table variable to hold the new ID's
Declare @IDList Table(ID int);

--====== insert a bunch of rows, 
--              and save the new IDs at the same time
INSERT into InsertIDTst(ColName, object_id)
 Output INSERTED.ID Into @IDList(ID)
 Select name, object_id
 From sys.columns

--====== show that we have the new IDs
SELECT * from @IDList

Upvotes: 0

simaremare
simaremare

Reputation: 417

i think that it is really dangerous to set a record's id (numeric-autonumber) outside the RDBMS, it may cause a conflict if there is another user doing the same execution as you do at the same time. if you want to know what is the id of the next persisting record, you have to re-query it just after persisting it. Unless you are going to use ORM to help you doing this. by using ORM, you won't need to re-query, just persist the record and you'll get the id assigned to it,.

hope this help,.

Upvotes: 0

Jwalin Shah
Jwalin Shah

Reputation: 2521

According to me, you need to retrieve result set by prepared statement and from you need to get that auto generated key fieldname.

Suppose auto generated field name is "ID" and tablename is student

then you need to retrieve resultset by query like select id from student.

then you will get object of resultset which contains Id.

To retrieve,

resultsetobj.getString(0);

OR

resultsetobj.getString("Id");

Upvotes: 0

Related Questions