sql - 为什么我会收到 "INSERT INTO"的语法错误？

Question

String pName = getStrFromUser("Product name: ");
int price = getIntFromUser("Price: ", false);
String category = getStrFromUser("Category: ");
String description = getStrFromUser("Description: ");

PreparedStatement statement = connection.prepareStatement("INSERT INTO ws.products (name, price, cid, description) VALUES (?, ?, (SELECT ws.categories.cid FROM ws.categories WHERE ws.categories.name LIKE ?), ?)");

statement.setString(1, pName);
statement.setInt(2, price);
statement.setString(3, category);
statement.setString(4, description);
statement.executeUpdate();

我得到:

Error encountered: ERROR: syntax error at or near "INSERT INTO ws

可能是什么问题？

Answer 1

VALUES 子句中的子查询看起来很可疑。尝试改写为 INSERT INTO ... SELECT:

String sql = "INSERT INTO ws.products (name, price, cid, description) ";
sql += "SELECT ?, ?, cid, ? FROM ws.categories WHERE name LIKE ?";
PreparedStatement statement = connection.prepareStatement(sql);
statement.setString(1, pName);
statement.setInt(2, price);
statement.setString(3, description);
statement.setString(4, category);
statement.executeUpdate();