java - Spring JDBC 中使用 BeanPropertySqlParameterSource 的 SQLSyntaxErrorException

Question

我在 SpringBoot 应用程序中使用 Spring JDBC。这是我的数据类之一:

@Getter
@Setter
@NoArgsConstructor
@Wither
@Builder
@AllArgsConstructor
public class MTAccountData {
    private int id;
    private boolean enable;
    private boolean readOnlyAccount;
    private String group;
    private int leverage;
    private int agentAccount;
    private double balance;
    private double prevMonthBalance;
    private double prevBalance;
    private double credit;
    private double interestRate;
    private double taxes;
    private double prevMonthEquity;
    private double prevEquity;
    private int registrationDate;
    private int lastConnectionDate;
}

MySQL表如下:

CREATE TABLE `accounts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `enable` bit(1) DEFAULT NULL,
  `readOnlyAccount` bit(1) DEFAULT NULL,
  `group` varchar(45) DEFAULT NULL,
  `leverage` int(11) DEFAULT NULL,
  `agentAccount` int(11) DEFAULT NULL,
  `balance` float DEFAULT NULL,
  `prevMonthBalance` float DEFAULT NULL,
  `prevBalance` float DEFAULT NULL,
  `credit` float DEFAULT NULL,
  `interestRate` float DEFAULT NULL,
  `taxes` float DEFAULT NULL,
  `prevMonthEquity` float DEFAULT NULL,
  `prevEquity` float DEFAULT NULL,
  `registrationDate` int(11) DEFAULT NULL,
  `lastConnectionDate` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

在我的服务类中，我有一个将新帐户保存到 MySQL 数据库的方法:

private void saveAccount(Integer companyId, MTAccountData account) {
        SqlParameterSource accountParameters = new BeanPropertySqlParameterSource(account);
        int generatedAccountId = new SimpleJdbcInsert(dataSource) //
                .usingGeneratedKeyColumns("ID") //
                .withTableName(tableName(companyId)) //
                .executeAndReturnKey(accountParameters) //
                .intValue();
        account.setId(generatedAccountId);
    }

我启用了 Spring JDBC 调试，当我尝试保存记录时，它会打印调试日志，但我收到了在帖子末尾看到的异常。我无法弄清楚原因...保存方法有问题吗？

2019-06-15 21:21:11.926  INFO 32737 --- [nio-9090-exec-1] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring DispatcherServlet 'dispatcherServlet'
2019-06-15 21:21:11.926  INFO 32737 --- [nio-9090-exec-1] o.s.web.servlet.DispatcherServlet        : Initializing Servlet 'dispatcherServlet'
2019-06-15 21:21:11.937  INFO 32737 --- [nio-9090-exec-1] o.s.web.servlet.DispatcherServlet        : Completed initialization in 11 ms
2019-06-15 21:21:12.073 DEBUG 32737 --- [nio-9090-exec-1] o.s.jdbc.core.simple.SimpleJdbcInsert    : JdbcInsert not compiled before execution - invoking compile
2019-06-15 21:21:12.099 DEBUG 32737 --- [nio-9090-exec-1] o.s.j.c.m.TableMetaDataProviderFactory   : Using GenericTableMetaDataProvider
2019-06-15 21:21:12.099 DEBUG 32737 --- [nio-9090-exec-1] o.s.j.c.metadata.TableMetaDataProvider   : GetGeneratedKeys is supported
2019-06-15 21:21:12.099 DEBUG 32737 --- [nio-9090-exec-1] o.s.j.c.metadata.TableMetaDataProvider   : GeneratedKeysColumnNameArray is supported for MySQL
2019-06-15 21:21:12.127 DEBUG 32737 --- [nio-9090-exec-1] o.s.j.c.metadata.TableMetaDataProvider   : Retrieving meta-data for mtsync_test/root@localhost/accounts
2019-06-15 21:21:12.140 DEBUG 32737 --- [nio-9090-exec-1] o.s.j.c.metadata.TableMetaDataProvider   : Retrieved meta-data: id 4 false
2019-06-15 21:21:12.140 DEBUG 32737 --- [nio-9090-exec-1] o.s.j.c.metadata.TableMetaDataProvider   : Retrieved meta-data: enable -7 true
2019-06-15 21:21:12.141 DEBUG 32737 --- [nio-9090-exec-1] o.s.j.c.metadata.TableMetaDataProvider   : Retrieved meta-data: readOnlyAccount -7 true
2019-06-15 21:21:12.141 DEBUG 32737 --- [nio-9090-exec-1] o.s.j.c.metadata.TableMetaDataProvider   : Retrieved meta-data: group 12 true
2019-06-15 21:21:12.141 DEBUG 32737 --- [nio-9090-exec-1] o.s.j.c.metadata.TableMetaDataProvider   : Retrieved meta-data: leverage 4 true
2019-06-15 21:21:12.142 DEBUG 32737 --- [nio-9090-exec-1] o.s.j.c.metadata.TableMetaDataProvider   : Retrieved meta-data: agentAccount 4 true
2019-06-15 21:21:12.142 DEBUG 32737 --- [nio-9090-exec-1] o.s.j.c.metadata.TableMetaDataProvider   : Retrieved meta-data: balance 7 true
2019-06-15 21:21:12.142 DEBUG 32737 --- [nio-9090-exec-1] o.s.j.c.metadata.TableMetaDataProvider   : Retrieved meta-data: prevMonthBalance 7 true
2019-06-15 21:21:12.142 DEBUG 32737 --- [nio-9090-exec-1] o.s.j.c.metadata.TableMetaDataProvider   : Retrieved meta-data: prevBalance 7 true
2019-06-15 21:21:12.142 DEBUG 32737 --- [nio-9090-exec-1] o.s.j.c.metadata.TableMetaDataProvider   : Retrieved meta-data: credit 7 true
2019-06-15 21:21:12.142 DEBUG 32737 --- [nio-9090-exec-1] o.s.j.c.metadata.TableMetaDataProvider   : Retrieved meta-data: interestRate 7 true
2019-06-15 21:21:12.142 DEBUG 32737 --- [nio-9090-exec-1] o.s.j.c.metadata.TableMetaDataProvider   : Retrieved meta-data: taxes 7 true
2019-06-15 21:21:12.143 DEBUG 32737 --- [nio-9090-exec-1] o.s.j.c.metadata.TableMetaDataProvider   : Retrieved meta-data: prevMonthEquity 7 true
2019-06-15 21:21:12.143 DEBUG 32737 --- [nio-9090-exec-1] o.s.j.c.metadata.TableMetaDataProvider   : Retrieved meta-data: prevEquity 7 true
2019-06-15 21:21:12.143 DEBUG 32737 --- [nio-9090-exec-1] o.s.j.c.metadata.TableMetaDataProvider   : Retrieved meta-data: registrationDate 4 true
2019-06-15 21:21:12.143 DEBUG 32737 --- [nio-9090-exec-1] o.s.j.c.metadata.TableMetaDataProvider   : Retrieved meta-data: lastConnectionDate 4 true
2019-06-15 21:21:12.143 DEBUG 32737 --- [nio-9090-exec-1] o.s.jdbc.core.simple.SimpleJdbcInsert    : Compiled insert object: insert string is [INSERT INTO accounts (enable, readOnlyAccount, group, leverage, agentAccount, balance, prevMonthBalance, prevBalance, credit, interestRate, taxes, prevMonthEquity, prevEquity, registrationDate, lastConnectionDate) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)]
2019-06-15 21:21:12.143 DEBUG 32737 --- [nio-9090-exec-1] o.s.jdbc.core.simple.SimpleJdbcInsert    : JdbcInsert for table [accounts] compiled
2019-06-15 21:21:12.157 DEBUG 32737 --- [nio-9090-exec-1] o.s.jdbc.core.simple.SimpleJdbcInsert    : The following parameters are used for call INSERT INTO accounts (enable, readOnlyAccount, group, leverage, agentAccount, balance, prevMonthBalance, prevBalance, credit, interestRate, taxes, prevMonthEquity, prevEquity, registrationDate, lastConnectionDate) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) with: [org.springframework.jdbc.core.SqlParameterValue@6b0bd4d, org.springframework.jdbc.core.SqlParameterValue@5c5caccd, org.springframework.jdbc.core.SqlParameterValue@53bfcc87, org.springframework.jdbc.core.SqlParameterValue@f3f8b3f, org.springframework.jdbc.core.SqlParameterValue@c661540, org.springframework.jdbc.core.SqlParameterValue@35e5fac5, org.springframework.jdbc.core.SqlParameterValue@1893d30a, org.springframework.jdbc.core.SqlParameterValue@6b86d554, org.springframework.jdbc.core.SqlParameterValue@57f61171, org.springframework.jdbc.core.SqlParameterValue@4ddffa98, org.springframework.jdbc.core.SqlParameterValue@4bd27baa, org.springframework.jdbc.core.SqlParameterValue@16232bf1, org.springframework.jdbc.core.SqlParameterValue@71b9dddd, org.springframework.jdbc.core.SqlParameterValue@4300e248, org.springframework.jdbc.core.SqlParameterValue@1bd7dea9]
2019-06-15 21:21:12.159 DEBUG 32737 --- [nio-9090-exec-1] o.s.jdbc.core.JdbcTemplate               : Executing SQL update and returning generated keys
2019-06-15 21:21:12.161 DEBUG 32737 --- [nio-9090-exec-1] o.s.jdbc.core.JdbcTemplate               : Executing prepared SQL statement
2019-06-15 21:21:12.162 DEBUG 32737 --- [nio-9090-exec-1] o.s.jdbc.core.simple.SimpleJdbcInsert    : Using generated keys support with array of column names.
2019-06-15 21:21:12.207 DEBUG 32737 --- [nio-9090-exec-1] o.s.jdbc.core.StatementCreatorUtils      : Overriding type info with runtime info from SqlParameterValue: column index 1, SQL type 16, type name null
2019-06-15 21:21:12.208 TRACE 32737 --- [nio-9090-exec-1] o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 1, parameter value [true], value class [java.lang.Boolean], SQL type 16
2019-06-15 21:21:12.218 DEBUG 32737 --- [nio-9090-exec-1] o.s.jdbc.core.StatementCreatorUtils      : Overriding type info with runtime info from SqlParameterValue: column index 2, SQL type 16, type name null
2019-06-15 21:21:12.219 TRACE 32737 --- [nio-9090-exec-1] o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 2, parameter value [false], value class [java.lang.Boolean], SQL type 16
2019-06-15 21:21:12.219 DEBUG 32737 --- [nio-9090-exec-1] o.s.jdbc.core.StatementCreatorUtils      : Overriding type info with runtime info from SqlParameterValue: column index 3, SQL type 12, type name null
2019-06-15 21:21:12.219 TRACE 32737 --- [nio-9090-exec-1] o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 3, parameter value [group-2], value class [java.lang.String], SQL type 12
2019-06-15 21:21:12.220 DEBUG 32737 --- [nio-9090-exec-1] o.s.jdbc.core.StatementCreatorUtils      : Overriding type info with runtime info from SqlParameterValue: column index 4, SQL type 4, type name null
2019-06-15 21:21:12.220 TRACE 32737 --- [nio-9090-exec-1] o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 4, parameter value [10], value class [java.lang.Integer], SQL type 4
2019-06-15 21:21:12.221 DEBUG 32737 --- [nio-9090-exec-1] o.s.jdbc.core.StatementCreatorUtils      : Overriding type info with runtime info from SqlParameterValue: column index 5, SQL type 4, type name null
2019-06-15 21:21:12.222 TRACE 32737 --- [nio-9090-exec-1] o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 5, parameter value [10043], value class [java.lang.Integer], SQL type 4
2019-06-15 21:21:12.222 DEBUG 32737 --- [nio-9090-exec-1] o.s.jdbc.core.StatementCreatorUtils      : Overriding type info with runtime info from SqlParameterValue: column index 6, SQL type 8, type name null
2019-06-15 21:21:12.222 TRACE 32737 --- [nio-9090-exec-1] o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 6, parameter value [1.0], value class [java.lang.Double], SQL type 8
2019-06-15 21:21:12.223 DEBUG 32737 --- [nio-9090-exec-1] o.s.jdbc.core.StatementCreatorUtils      : Overriding type info with runtime info from SqlParameterValue: column index 7, SQL type 8, type name null
2019-06-15 21:21:12.223 TRACE 32737 --- [nio-9090-exec-1] o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 7, parameter value [1.0], value class [java.lang.Double], SQL type 8
2019-06-15 21:21:12.223 DEBUG 32737 --- [nio-9090-exec-1] o.s.jdbc.core.StatementCreatorUtils      : Overriding type info with runtime info from SqlParameterValue: column index 8, SQL type 8, type name null
2019-06-15 21:21:12.223 TRACE 32737 --- [nio-9090-exec-1] o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 8, parameter value [1.0], value class [java.lang.Double], SQL type 8
2019-06-15 21:21:12.223 DEBUG 32737 --- [nio-9090-exec-1] o.s.jdbc.core.StatementCreatorUtils      : Overriding type info with runtime info from SqlParameterValue: column index 9, SQL type 8, type name null
2019-06-15 21:21:12.223 TRACE 32737 --- [nio-9090-exec-1] o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 9, parameter value [1.0], value class [java.lang.Double], SQL type 8
2019-06-15 21:21:12.223 DEBUG 32737 --- [nio-9090-exec-1] o.s.jdbc.core.StatementCreatorUtils      : Overriding type info with runtime info from SqlParameterValue: column index 10, SQL type 8, type name null
2019-06-15 21:21:12.223 TRACE 32737 --- [nio-9090-exec-1] o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 10, parameter value [1.0], value class [java.lang.Double], SQL type 8
2019-06-15 21:21:12.223 DEBUG 32737 --- [nio-9090-exec-1] o.s.jdbc.core.StatementCreatorUtils      : Overriding type info with runtime info from SqlParameterValue: column index 11, SQL type 8, type name null
2019-06-15 21:21:12.223 TRACE 32737 --- [nio-9090-exec-1] o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 11, parameter value [1.0], value class [java.lang.Double], SQL type 8
2019-06-15 21:21:12.223 DEBUG 32737 --- [nio-9090-exec-1] o.s.jdbc.core.StatementCreatorUtils      : Overriding type info with runtime info from SqlParameterValue: column index 12, SQL type 8, type name null
2019-06-15 21:21:12.224 TRACE 32737 --- [nio-9090-exec-1] o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 12, parameter value [1.0], value class [java.lang.Double], SQL type 8
2019-06-15 21:21:12.224 DEBUG 32737 --- [nio-9090-exec-1] o.s.jdbc.core.StatementCreatorUtils      : Overriding type info with runtime info from SqlParameterValue: column index 13, SQL type 8, type name null
2019-06-15 21:21:12.224 TRACE 32737 --- [nio-9090-exec-1] o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 13, parameter value [1.0], value class [java.lang.Double], SQL type 8
2019-06-15 21:21:12.224 DEBUG 32737 --- [nio-9090-exec-1] o.s.jdbc.core.StatementCreatorUtils      : Overriding type info with runtime info from SqlParameterValue: column index 14, SQL type 4, type name null
2019-06-15 21:21:12.224 TRACE 32737 --- [nio-9090-exec-1] o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 14, parameter value [1], value class [java.lang.Integer], SQL type 4
2019-06-15 21:21:12.224 DEBUG 32737 --- [nio-9090-exec-1] o.s.jdbc.core.StatementCreatorUtils      : Overriding type info with runtime info from SqlParameterValue: column index 15, SQL type 4, type name null
2019-06-15 21:21:12.228 TRACE 32737 --- [nio-9090-exec-1] o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 15, parameter value [1], value class [java.lang.Integer], SQL type 4
2019-06-15 21:21:12.365 ERROR 32737 --- [nio-9090-exec-1] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: 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 'group, leverage, agentAccount, balance, prevMonthBalance, prevBalance, credit, i' at line 1] with root cause

异常(exception):

java.sql.SQLSyntaxErrorException: 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 'group, leverage, agentAccount, balance, prevMonthBalance, prevBalance, credit, i' at line 1
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120) ~[mysql-connector-java-8.0.16.jar:8.0.16]
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) ~[mysql-connector-java-8.0.16.jar:8.0.16]
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-java-8.0.16.jar:8.0.16]
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:955) ~[mysql-connector-java-8.0.16.jar:8.0.16]
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1094) ~[mysql-connector-java-8.0.16.jar:8.0.16]
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1042) ~[mysql-connector-java-8.0.16.jar:8.0.16]
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1345) ~[mysql-connector-java-8.0.16.jar:8.0.16]
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:1027) ~[mysql-connector-java-8.0.16.jar:8.0.16]

Answer 1

group 是一个 SQL 关键字。为您的列选择另一个名称。