java - 在 MySQL 中有效但在 Java 中无效的 SQL 查询

Question

我有多个 INSERT INTO 查询

Connection con = null;

String sql = "START TRANSACTION;"
               + "Set autocommit=0;"
               + " INSERT INTO CARS (CARNR) VALUES (" + (getLetzteFahrzeugNr() +1)+ ");"
               + " INSERT INTO KAUFINFO (CARNR) VALUES (" + (getLetzteFahrzeugNr() +1)+ ");"
               + " INSERT INTO POSITIONS (CARNR) VALUES (" + (getLetzteFahrzeugNr() +1)+ ");"
               + " COMMIT;";

System.out.println(sql);           
Statement stmt = null;
ResultSet rs = null;


Integer carnr = null;
stmt = con.createStatement();
stmt.executeUpdate(sql);

虽然查询直接在 MySQL 上运行完美，但是当我从 Java 代码调用它时它不起作用，而且我一直收到错误。

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 'Set autocommit=0; INSERT INTO CARS (CARNR) VALUES (108); INSERT INTO KAUFINFO (C' at line 1
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.Util.getInstance(Util.java:386)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4098)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4030)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2490)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2651)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2671)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1749)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1666)
at util.DbUtilities.getNewFahrzeugNr(DbUtilities.java:131)
at util.DbUtilities.main(DbUtilities.java:231)

根据我的研究，在我使用 executequery 之前，INSERT INTO 必须使用 executeUpdate。

Answer 1

; 用于分隔多个语句。使用 JDBC 时，需要分别执行它们:

Connection con = ...;
con.setAutoCommit(false);

Statement stmt = con.createStatement();
stmt.executeUpdate("INSERT INTO CARS (CARNR) VALUES (" + (getLetzteFahrzeugNr() +1)+ ")");
stmt.executeUpdate("INSERT INTO KAUFINFO (CARNR) VALUES (" + (getLetzteFahrzeugNr() +1)+ ")");
stmt.executeUpdate("INSERT INTO POSITIONS (CARNR) VALUES (" + (getLetzteFahrzeugNr() +1)+ ")");

con.commit();

强制注释:
使用 PreparedStatement可能会更整洁，更安全。