0

I am working with mysql in Java.

Basically, I have multiple queries that each create a table in the database, along with a single ALTER statement that adjusts the auto-increment initial value for one of my attributes. I am executing those queries as a transaction--namely either all are committed to the database or none are. But to do so I have create a separate Statement for each query - 8 in total - and execute each. After, I commit all the results. And then I have to close each Statement.

But this seems inefficient. To many Statements. So I wonder whether batch methods would work. My concern is that batch methods execute all the queries simultaneously, and since I have Referential Integrity Constraints and the ALTER query there is a dependancy between the tables - and thus the order in which they are created matters. Is this not correct ? Am I misunderstanding how batch statements work ?

If my logic above is correct, then should I possibly group a few queries together (that are not related) and use batch methods to execute them. This will then reduce the number of Statements I have.

Aerole
  • 105
  • 2
  • 10
  • In MySQL, you can probably just [use a single `Statement`](http://stackoverflow.com/questions/10797794/multiple-queries-executed-in-java-in-single-statement) and pass it a string that includes all the SQL statements, separated with a semicolon (`;`). Have you tried this? Also, all DDL statements [cause an implicit commit](http://stackoverflow.com/questions/28197013/transactional-ddl-workflow-for-mysql), so executing them within a separate transaction doesn't really matter. – Mick Mnemonic Aug 03 '15 at 00:22
  • Issue with the single Statement is readability since I'm creating about 7 tables each contain about 7-12 attributes, in addition to checks, foreign keys, and primary keys. – Aerole Aug 03 '15 at 00:35

1 Answers1

0

I don't think you can batch DDL (i.e. create, drop, alter). Also, it's not a great idea, performance wise, to require dynamic DDL.

You can batch DML statements using Statement.addBatch(String) (i.e. select, insert, update and delete statements) and then call Statement.executeBatch().

Elliott Frisch
  • 198,278
  • 20
  • 158
  • 249