jdbc executeUpdate question

[hilz]

Hi all:
My question is about the method
int executeUpdate(String sql, String[] columnNames)

the following is the description of this method in the JDBC Javadoc :

Executes the given SQL statement and signals the driver that the
auto-generated keys indicated in the given array should be made available
for retrieval.


My question is, how to retrieve the auto-generated key(s) after this
statement has prepared them for retrieval?



thanks
hilz

 

[Murray]

Hi all:
My question is about the method
int executeUpdate(String sql, String[] columnNames)

the following is the description of this method in the JDBC Javadoc :

Executes the given SQL statement and signals the driver that the
auto-generated keys indicated in the given array should be made available
for retrieval.


My question is, how to retrieve the auto-generated key(s) after this
statement has prepared them for retrieval?



thanks
hilz

@see Statement#getGeneratedKeys()

 

[hilz]

@see Statement#getGeneratedKeys()

Murray,
Than you for the answer.
it seems this functionality is not supported by the driver i am using.(not
even the method i asked about in my OP)

assuming the auto-generated key in my database is MY_ID,
is there anything wrong with doing the following:

stmt.executeUpdate("INSERT INTO MY_TABLE ( COL1, COL2, COL3, .....)
VALUES ( 1 , 2 , 3 , .....);

ResultSet rs = stmt.executeQuery("SELECT MY_ID FROM MY_TABLE");
rs.last();
long myNewId = rs.getLong(1);

 

[Murray]

Murray,
Than you for the answer.
it seems this functionality is not supported by the driver i am using.(not
even the method i asked about in my OP)

assuming the auto-generated key in my database is MY_ID,
is there anything wrong with doing the following:

stmt.executeUpdate("INSERT INTO MY_TABLE ( COL1, COL2, COL3, .....)
VALUES ( 1 , 2 , 3 , .....);

ResultSet rs = stmt.executeQuery("SELECT MY_ID FROM MY_TABLE");
rs.last();
long myNewId = rs.getLong(1);

Unless it's in some kind of transaction, that would probably be a bad idea.
e.g. what happens if another thread/program/whatever creates a new entry at
the same time? You might get the id of THAT insert instead of the one you
just created.

Depending on which database you're using, there's usually a way to get the
last generated ID *for a particular connection* which would be safer. e.g.
in MySql you can do

select LAST_INSERT_ID() as new_id

and in SQL Server it's

select @@identity as new_id

 

[Chris Smith]

Unless it's in some kind of transaction, that would probably be a bad idea.
e.g. what happens if another thread/program/whatever creates a new entry at
the same time? You might get the id of THAT insert instead of the one you
just created.

Even in a transaction, there's no guarantee (or even a good reason to
believe) that this will work. Relations are unordered, and the "last"
one is nothing special, unless there's an ORDER BY in the query. In
practice, records are likely to stay in order if the database decides to
do a sequential scan, but not if you add a few indexes to improve
performance; then the order of results can be pretty much anything.
Most databases also have a way of compacting and cleaning up the disk
storage of data, and that will likely change some orders of records on
disk, which in turn can change the order in qhich query results are
reported.

--
www.designacourse.com
The Easiest Way to Train Anyone... Anywhere.

Chris Smith - Lead Software Developer/Technical Trainer
MindIQ Corporation