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

Question

I use Java, Spring (NamedParameterJdbcTemplate) and MySQL. My statement is as follows:

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

But this causes the following error:

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

Nested Exception:

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 happens when merged using the Spring template.

Thank!

Answer 1

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 2

For those who want to execute several statements from jdbcTemplate with MySQL without using a 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>

jdbcTemplate.execute, Spring 3.0.2 MySQL v5.1.9

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

Answer 3

JDBC MySQL , ';', MySQL. , Java.

100K, , Java ? , MySQL ETL , .

, Spring? - ?

Answer 4

. 1, 2

Answer 5

You missed the semicolon; in the second statement. I think this is a syntax error than anything else.

tries to run two commands there on the mysql and se console if the syntax is correct.

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