How to insert into two different tables in one statement with Java and MySQL?

Question

I am using Java, Spring (NamedParameterJdbcTemplate) and MySQL. My statement looks like this:

INSERT INTO Table1 (Name) VALUES (?);
INSERT INTO Table2 (Path, Table1Id) VALUES (?, LAST_INSERT_ID())

But it is throwing the following error:

PreparedStatementCallback; bad SQL grammar [INSERT INTO Table1 (Name) VALUES (?);
INSERT INTO Table2 (Path, Table1Id) VALUES (?, LAST_INSERT_ID())] `

Nested exception is:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO Table2 (Path, Table1Id' at line 1

The syntax works fine in MySQL but something is up when combining via the Spring template.

Thanks!

Answer 1

You need to execute each statement separately. First, insert into table1, then insert into table2

Answer 2

Use the addBatch method to run multiple statements

Statement stmt = con.createStatement();
   stmt.addBatch(
    "update registration set balance=balance-5.00
        where theuser="+theuser);
   stmt.addBatch(
    "insert into auctionitems(
                   description, startprice) 
        values("+description+","+startprice+")");

   int[] results = stmt.executeBatch();

source

Answer 3

The MySQL JDBC driver doesn't support multiple statements separated by ';', even if the MySQL client itself does. You can't depend on that if you must do this with Java.

If you have 100K records, are you sure that Java is the right tool for the job? I'd wonder if MySQL import tools or ETL would be better if this is a batch job.

Also, what about the Spring batch module? Does it have anything specially made for this problem?

Answer 4

did you miss the semicolon ; in the second statement. I think this is a syntax error than anything else.

trying running there two commands on the mysql console and se if the syntax is correct.

I always run the coomand on console to make sure the sytax.

Answer 5

For anyone who wants to execute multiple statements from jdbcTemplate with MySQL without using batch update:

Add "?allowMultiQueries=true" at the end of the url (see example below).

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="com.mysql.jdbc.Driver" />
    <property name="url" value="jdbc:mysql://localhost:3306/tt?allowMultiQueries=true" />
    <property name="username" value="root" />
    <property name="password" value="" />
</bean>

Tested with jdbcTemplate.execute method, Spring 3.0.2 and MySQL driver v5.1.9

    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.9</version>
    </dependency>