Insert into multiple table with jdbc

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?

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.

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.

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.