Re: JDBC: getMoreResults() versus rs.next() & autoGeneratedKeys
On 12/21/2013 5:46 AM, Robert Klemme wrote:
On 21.12.2013 01:14, Arne Vajh?j wrote:
On 12/20/2013 9:07 AM, Andreas Leitgeb wrote:
I have to write code to execute runtime-specified SQL-statements,
and there are two things that I wasn't able to understand from reading
the javadocs:
2.)
As I don't know the statement beforehand, I can't decide at coding
time, if it might be an "insert" eventually returning a serial key,
and thusly requiring the argument RETURN_GENERATED_KEYS), or a select
that will need cursor-like treatment such as passing resultSetType,
resultSetConcurrency and resultSetHoldability flags.
How would I tell the JDBC engine, that if it is a select then it
should pass certain flags, and if it is an insert, then I would be
interested in the generated keys? prepareStatement() doesn't seem to
have an overload to accept both variants.
True - you would need to know whether it is a SELECT or an INSERT.
An UPDATE also can generate keys. And a DELETE can also return data via
a RETURNING clause.
Just like you need to know whether to call executeQuery or
executeUpdate.
Finally, why isn't RETURN_GENERATED_KEYS the default, anyway?
In most implementations it result in another round trip to
the server with a SELECT.
I'm not sure about "most". The feature used for that would be the same
as that used for a RETURNING clause with INSERT, UPDATE and DELETE for
databases that have it:
http://www.postgresql.org/docs/9.3/static/sql-insert.html
http://docs.oracle.com/cd/E16655_01/server.121/e17209/statements_9014.htm#i2122356
Any reasonable implementation of a protocol would not make an additional
roundtrip to the SQL engine or even an additional SELECT call necessary.
Just checked.
Oracle JDBC does utilize RETURNING so no second roundtrip.
I believe efficiency was the primary reason for introduction of
RETURNING because then results could be sent back immediately. Also,
for a database without RETURNING in the worst case there would be no
reliable way to identify the generated keys (i.e. if there is no UNIQUE
INDEX or UNIQUE constraint on the table that is not including the column
with the auto generated key).
>
> The generated keys are only related to the particular statement
> execution. A separate SELECT would need to query some system tables and
> use a handle identifying the particular execution of that statement. Of
> course that would be possible for a JDBC driver to do if the database
> implementation would provide such a handle for every execution but I
> would assume that this is returned along with the other result data
> (e.g. number of inserted / updated rows).
The databases using auto increment instead of sequences typical store
the last generated key in the connection and limit auto increment to
only one column per table so it is easy to grab.
Arne