使用 java 插入表时出现 SQl 错误

Question

我正在尝试向sql表插入新行 使用以下代码的客户：

String str = "register customer Mark Waid 1234567 389529548 Monthly LilacHaifa "
+ "1234567891234567 5/2025";
String[] tokens = str.trim().split("\s++");
sql = "INSERT INTO Customers " +
"(ID, Name, Password, Connected, SubscriptionType, Status, Store Name, CreditCardNumber, Balance, ExpirationDate) "
+ "VALUES (?,?,?,?,?,?,?,?,?,?)";
preparedStmt = conn.prepareStatement(sql);
preparedStmt.setInt(1, Integer.parseInt(tokens[5]));
preparedStmt.setString(2, tokens[2] + " " + tokens[3]);
preparedStmt.setString(3, tokens[4]);
preparedStmt.setInt(4, 1);
preparedStmt.setString(5, tokens[6]);
preparedStmt.setInt(6, 1);
preparedStmt.setString(7, tokens[7]);
preparedStmt.setString(8, tokens[8]);
preparedStmt.setDouble(9, 0);
preparedStmt.setString(10, tokens[9]);
preparedStmt.executeUpdate();

但我收到以下错误

com.mysql.jdbc.exceptions.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 'Name, CreditCardNumber, Balance, ExpirationDate) VALUES (389529548,'Mark Waid','' at line 1
SQLException: 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 'Name, CreditCardNumber, Balance, ExpirationDate) VALUES (389529548,'Mark Waid','' at line 1
SQLState: 42000
VendorError: 1064
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2985)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3283)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1332)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1604)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1519)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1504)
at ocsf.LilacServer.handleMessageFromClient(LilacServer.java:297)
at ocsf.AbstractServer.receiveMessageFromClient(AbstractServer.java:474)
at ocsf.ConnectionToClient.run(ConnectionToClient.java:219)

我的查询肯定有问题，除了我无法准确识别它是什么。 感谢您的任何帮助。

Answer 1

Store Name列中有一个空格，因此应正确引用，具体取决于您使用的数据库。

例如：

MS SQL Server：[Store Name]
Oracle："Store Name"
MySQL：`Store Name`
Postgres："Store Name"

Answer 2

尝试用下面的 sql 替换 ：-

sql = "INSERT INTO Customers " +
"(ID, Name, Password, Connected, SubscriptionType, Status, `Store Name`, CreditCardNumber, Balance, ExpirationDate) "
+ "VALUES (?,?,?,?,?,?,?,?,?,?)";

Answer 3

在您的插入SQL 查询中，存储名称似乎是不正确的列名。

请检查表的 DDL 客户 ，并检查其中的商店名称列。最好将客户表 DDL 粘贴到问题中。

如果正确，则必须将其与单引号一起使用作为"商店名称">，如下面的代码所示：

sql = "INSERT INTO Customers " +
"(ID, Name, Password, Connected, SubscriptionType, Status, 'Store Name', CreditCardNumber, Balance, ExpirationDate) "
+ "VALUES (?,?,?,?,?,?,?,?,?,?)";