CODEWITHSUNDEEP
javaspring-bootspring-jdbc
xyzt
xyzt

Reputation: 1311

SQLSyntaxErrorException in Spring JDBC with BeanPropertySqlParameterSource

I use Spring JDBC in my SpringBoot application. This is one of my data classes:

@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;
}

And MySQL table is as follows:

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;

In my service class I have a method to save a new account to MySQL database:

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);
    }

I enabled Spring JDBC debugging and it prints debug logs when I try to save a record but I get the exception that is seen at the end of my post. I couldn't figure out the reason... Is there something wrong with the save method?

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]

Upvotes: 0

Views: 578

Answers (2)

SZ.
SZ.

Reputation: 37

As there are a lot of SQL keywords and it's impossible to avoid all of them so one possible solution is to pass column names and add escaping,

String[] columns = {"name", "desc",  "code", "`group`", "`order`"}

SimpleJdbcInsert insert = new SimpleJdbcInsert(getJdbcTemplate());
insert.withTableName(tableName);
insert.usingColumns(columns);
insert.usingGeneratedKeyColumns("id");

Number id = insert.executeAndReturnKey(parameterSource);

Note that in order to pass parameter for keyword as column name, you need to add escaping while setting params like:

params.put("`group`", "group_");
params.put("`order`", 3);
params.put("name", "name_");

Upvotes: 0

JB Nizet
JB Nizet

Reputation: 691635

group is a SQL keyword. Choose another name for your column.

Upvotes: 1

Related Questions