java - 尝试更新 SQL 数据库条目时收到 SQL 语法错误。我如何弄清楚这里出了什么问题？

Question

下面是我连接到一个简单服务器和更新信息等的代码。

package mysqltest;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class MySQLTest {
    static Connection con = null;
    static Statement stmt = null;
    static ResultSet result = null;

    static String url = "jdbc:mysql://localhost:3306/coffeeshop";
    static String user = "root";
    static String password = "";

    public static void main(String[] args) {                 
            // SQL query string          
            //update specifics
            String push1 = "INSERT INTO Coffees"
                + "(CoffeeName, SupplierID, Price, Sales, Total)"
                + "values"
                + "('Colombian', 95, 5.95, 0, 0)";

            String push2 = "INSERT INTO Coffees"
                + "(CoffeeName, SupplierID, Price, Sales, Total)"
                + "values"
                + "('French Roast', 27, 6.95, 0, 0),"
                + "('Espresso', 104, 7.95, 0, 0),"
                + "('Colombian Decaf', 95, 16.45, 0, 0),"
                + "('French Roast Decaf', 27, 8.45, 0, 0)";

            String push3 = "UPDATE Coffees"
                + "SET SupplierId=12, Total=2"
                + "WHERE CoffeeName='Colombian'";


            String push4 = "DELETE FROM Coffees WHERE CoffeeName='Colombian'";
            String query = "SELECT * FROM Coffees";
            //clear everything first

            //push updates
            connect();
            pushUpdate(push1);
            //need to reconnect after update as update closes connection after updates.
            connect();
            queries(query); 
            pushUpdate(push2);
            connect();
            queries(query);
            pushUpdate(push3);
            connect();
            queries(query);
            pushUpdate(push4);
            connect();
            queries(query);
}
    public static boolean connect(){
        boolean connect;
        try{
            con = DriverManager.getConnection(url, user, password);
            stmt = con.createStatement();
            connect = true;
        }catch(SQLException ex){
            System.out.println("SQLException caught: " + ex.getMessage());
            connect = false;
        }
        return connect;
    }

    public static void pushUpdate(String push){
        try{
            stmt.executeUpdate(push);
        }catch(SQLException ex){
            System.out.println("SQLException caught: " + ex.getMessage());
        }

        try{
            con.close();
            stmt.close();
        }catch(SQLException ex){
            System.out.println("SQLException caught: " + ex.getMessage());   
        }   
    }

    public static void queries(String query){
        try{
            result = stmt.executeQuery(query);
            System.out.printf("%-10s%-35s%-12s  %-9s%-7s%-7s\n", 
                "CoffeeID", "CoffeeName", "SupplierID", "Price", "Sales", "Total");             

            while (result.next()) { // loop until the end of the results                 
                    int coffeeID = result.getInt("CoffeeID");
                    String coffeeName = result.getString("CoffeeName");
                    int supplierID = result.getInt("SupplierID");
                    double price = result.getDouble("Price");
                    int sales = result.getInt("Sales");
                    int total = result.getInt("Total");
                    System.out.printf("%8d  %-35s%10d  %7.2f  %7d%7d\n", coffeeID, coffeeName, supplierID, price, sales, total);
                }

        }catch(SQLException ex){
            System.out.println("Exception caught: " + ex.getMessage());
        }finally {
            try {
                if (result != null) {
                    result.close();
                }
            }catch (SQLException ex){
                System.out.println("SQLException caught: " + ex.getMessage());
            }
        }
    }
}

基本上我已经创建了一个表，我根据“推送”更新了该表。推送 1、2 和 4 工作正常，但是 3 抛出此错误...

“捕获到 SQLException:您的 SQL 语法有错误；请查看与您的 MariaDB 服务器版本对应的手册，了解在第 1 行的‘=12，Total=2WHERE CoffeeName='Colombian''附近使用的正确语法”

好的，所以基本上我已经测试了 push3，因为它是在我的程序中编写的，写入到 cmd 提示符中并且工作正常。显然没有使用“+”符号。

cmd prompt snippet

Answer 1

空格很重要。这:

"UPDATE Coffees"
+ "SET SupplierId=12, Total=2"
+ "WHERE CoffeeName='Colombian'"

变成这样:

"UPDATE CoffeesSET SupplierId=12, Total=2WHERE CoffeeName='Colombian'"

CoffeesSET 无效，2WHERE 无效，导致整个语句无法解析。在需要的地方添加空格:

"UPDATE Coffees "
+ "SET SupplierId=12, Total=2 "
+ "WHERE CoffeeName='Colombian'"