如何使用Java和MySQL在一个语句中插入到两个不同的表中? [英] How to insert into two different tables in one statement with Java and MySQL?

问题描述

我正在使用Java，Spring(NamedParameterJdbcTemplate)和MySQL.我的声明如下:

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())]`

嵌套异常为:

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

语法在MySQL中工作正常，但通过Spring模板合并时出现了问题.

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

谢谢！

推荐答案

使用addBatch方法运行多个语句

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();

源

这篇关于如何使用Java和MySQL在一个语句中插入到两个不同的表中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！