3

I am trying to use JDBC to insert into multiple tables. Because it has to be fast I want to use PreparedStatement and the executeBatch method. Tables are combinded by a foreign key relationship.

  • First idea was to use getGeneratedKeys() but this fails with some JDBC drivers. E.g. PostgreSQL.

  • Second idea was to use the SQL-currval(...)-function. But having to call execute batch for one statement and the for the other makes all keys the same value. So this method also fails.

  • JDBC doesn't accept semicolon separeted inserts.

How can I achieve this?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
hans.g
  • 149
  • 3
  • 7

3 Answers3

1

You seem to be mainly using PostgreSQL. It may be good to know that since PostgreSQL JDBC driver version 8.4-701 the PreparedStatement#getGeneratedKeys() is fully functional.

You only need to prepare the statement as follows to make it to return the keys:

statement = connection.prepareStatement(SQL, Statement.RETURN_GENERATED_KEYS);

So, upgrade if necessary your driver and fix the way how you prepare the statement. This applies by the way also on many other JDBC drivers.

A completely different alternative is to drop plain JDBC altogether and go one abstraction layer up and dive in "good 'ol" Hibernate or the modern JPA. They offers a pure object oriented approach of database entity handling in Java without the need to fiddle with generated keys and like. It's all handled under the covers transparently and they supports a very wide range of DB dialects.

BalusC
  • 1,082,665
  • 372
  • 3,610
  • 3,555
  • Thanks for the reply. Are you saying that fuctionality in 8.4-701 was removed in 9.0-801? Using Statement.RETURN_GENERATED_KEYS throws an Exeption when calling executeBatch() saying it isn't exspecting results but is getting some... Does this work in 8.4-701? – hans.g Mar 03 '11 at 07:43
0

If the code has to run fast then you should try writing a stored procedure/function which will accept arrays of values which will be inserted in the tables.

Here's an example.

Boris Pavlović
  • 63,078
  • 28
  • 122
  • 148
  • thanks. My problem is, that the system is vey dynamic, e.g. new tables and structures can be added. Having to then define an extra fuction in the postgres-server (it should work on any JDBC database, not only postgres) is to much work. Is there a way to generate procedures via JDBC? The Java-App has to be the center of the system – hans.g Mar 02 '11 at 16:55
0

Okay here's my own answer.

So long the vendors can't implement the getGeneratedKeys() function correctly you need and database specific fallback option.

Makes the code very ugly, if you need fallbacks for unsported databases.

Here my workaround for Postgres 9.0:

  • First generate a big load of keys with "SELECT nextval('public.\"table_column_seq\"') FROM generate_series(1,"+pollsize+")");
  • Then hand out these keys in all your prepared batch statements.

Wasted a whole bloody day on that, and then all I get is an ugly solution. JDBC should check on app-startup if the driver really holds up to its expectation and otherwise throw some unrecoverable wicked YouVeGotaF**ckedUpDriverException.

hans.g
  • 149
  • 3
  • 7