Multiple inserts into database

The jdbc.core class PreparedStatementFactory seems to be coded to handle multiple inserts.

However, the higher level of abstraction jdbc.object package has classes that *have* to make one database trip for each insert/update. So, they have open a connection, do their thing and then close the connection for each operation. The object classes do not seem to able to leverage the multiple statements capability in the core classes.

True?

If not, can someone show me code that will post two (2) or more inserts, of DIFFERENT KINDS, into to a database before doing JDBC style clean-up (closing connections etc).

In other words, write a reusable Spring Framework based JDBC object that would implement the following back-to-back in a single database trip:

INSERT INTO PRODUCT VALUES (X, Y, Z);
INSERT INTO ORDER VALUES (A, B, C);

-- possibly more DML ---

One solution is to write a PL/SQL procedure INSERT_PROD_ORDER (A, B, C, X, Y, Z) that implements the above inserts within its body and then to use the Spring Framework stored procedure invocation instead of SQL.

But I would rather do this all in threadsafe java, if I could... :twisted: i.e. have none of my code within the database.

Keep in mind that when you use the Spring transaction handling classes (DataSourceTransactionManager, or the other variants like HibernateTransactionManager and JTATransactionmanager) the connection actually gets bound to the current transaction and thread, and only gets closed at the end of the transaction. So any number of contained JDBC operations will happen on the same connection...

Hi Colin: So there is a way to contrive thread-bound transactions. Looks tricky though, since we seem to be using Spring Framework to work against itself.

I do have a related question on this. I was trying to create a setup class that would initalize the database schema. i was having it read the setup script from a file and run it against the database using the jdbctemplate.execute() method.

But however, it seems to fail, if I try to have it run more than one query from a file. i.e.

INSERT ... INTO ... ;
INSERT ... INTO ... ;

Is there a way to do it? Or do I need to do something like execute the queries one at a time?

Yes, you would have to execute the statements one by one. You can't execute a script in a JDBC statement.

Ok, thanks for clearing that up. Is there a best practice you (or any of you gurus) use when programatically creating a database via JDBC? I was hoping to load the script and execute it via JDBC, but if that's not possible, what do you guys do in this case?

If you just want to pass an API a file full of db script type stuff to execute (i.e. create db, insert sample data, etc.) you can use the iBatis ScriptRunner class. See the actual iBatis docs (not Spring docs about Spring iBatis integration) for more info...