mysql - JavaFX SQL 语法错误

Question

我最近在 SQL 语法中遇到错误，但我无法找出问题所在。

String table = "rfc";
            String update = "UPDATE "+table+" set Type = '" + AWBTypeBox.getValue() + "' set Aantal = " + AWBAantal.getText() + " set Gewicht = " + AWBGewicht.getText() + " set Volume = " + AWBVolume.getText() + " set Beschadiging = '" + AWBBeschadiging.getText() + "' where idReservering = " + account_id + "";
            Statement statement = dbCon.conDatabase().createStatement();

            int resultSet = statement.executeUpdate(update);

它给出的错误是:

SEVERE: null
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 Aantal = 2 set Gewicht = 20 set Volume = 15 set Beschadiging = 'ja' where id' at line 1
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:422)

我尝试了 1 小时尝试更改语法中的内容，但没有修复错误。

Answer 1

set 仅在更新中出现一次:

UPDATE "+table+"
    set Type = '" + AWBTypeBox.getValue() + "', 
        Aantal = " + AWBAantal.getText() + ",
        Gewicht = " + AWBGewicht.getText() + ",
        Volume = " + AWBVolume.getText() + ",
        Beschadiging = '" + AWBBeschadiging.getText() + "'
    where idReservering = " + account_id + "";

您应该用逗号替换除第一个 set 之外的所有内容。

为了清晰起见，这些行被分成多行。您应该根据您的应用程序的需要来格式化它们。

注意:在查询中使用参数也更安全。但是，您无法参数化表名称，因此您仍然需要将其直接放入字符串中。