JdbcTemplate throws BadSqlGrammarException on a MySQL query
org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammarShort answer
BadSqlGrammarException is Spring's wrapper for an invalid SQL statement, with the driver's SQLException inside. Read the root cause first: it holds MySQL's own error. The usual reasons are a reserved word used as a name without backticks, and a table name whose letter case differs from the real table on Linux.
Fix it fast
1Find out which cause you have
Print the SQL and the wrapped SQLException. getSql() is the statement, and the cause holds the SQLState and MySQL's own message.
catch (BadSqlGrammarException e) { log.error("SQL: {}", e.getSql()); log.error("Cause: {}", e.getSQLException() == null ? "none" : e.getSQLException().getMessage()); }2Match what you see, then jump to its fix
What does the error look like?
org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammarnested exception is java.sql.SQLSyntaxErrorException
Cause 1 of 2
A reserved word is used as a table or column name
1Check
Words like INTERVAL are reserved. Using one as an identifier without quoting causes a syntax error right at that word.
SELECT * FROM information_schema.KEYWORDS WHERE WORD = 'INTERVAL' AND RESERVED = 1;2Fix
Quote the identifier with backticks in the SQL string, or rename the column.
jdbcTemplate.query("SELECT `interval` FROM `schedule` WHERE id = ?", mapper, id);3Verify
The same query now runs in the mysql client and from Spring.
SELECT `interval` FROM `schedule` LIMIT 1;Confirmed by dev.mysql.com
Cause 2 of 2
The table name has a different letter case than the real table
1Check
On most Unix systems table names are case-sensitive; Windows and default macOS file systems are not. lower_case_table_names defaults to 0 on Unix.
SHOW TABLES;
SHOW VARIABLES LIKE 'lower_case_table_names';2Fix
Use the exact stored case in every query. The manual recommends one convention, such as always lowercase, for portability.
jdbcTemplate.query("SELECT id, name FROM users WHERE id = ?", mapper, id);3Verify
The query runs on the Linux server as it did on the laptop.
SHOW TABLES LIKE 'users';Confirmed by dev.mysql.com
How do I stop it coming back?
- Name tables and columns in lowercase with underscores and avoid words on the reserved list.
- Run integration tests against the same MySQL major version and OS family as production.
- Log getSql() with the exception so the failing statement is never a guess.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Projects of mine built on the same technologies:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Databases
EntityNotFoundException when loading an entity that has a missing related row
ConstraintViolationException on an orphaned foreign key
EntityNotFoundException means Hibernate followed an id and no row was there.
3 causes, each with a fixDatabases
Hibernate fails on a CREATE or ALTER TABLE statement at startup
Error executing DDL "alter table events drop foreign key FKg0mkvgsqn8584qoql6a2rxheq" via JDBC Statement
Hibernate generated a CREATE, ALTER or DROP statement and MySQL refused it.
3 causes, each with a fixDatabases
MySQL Connector/J: EOFException, expected to read 4 bytes, read 0 bytes
Communications link failure
The MySQL server or something between you and it closed the connection, and the driver found out only when it tried to use it.
3 causes, each with a fixDatabases
Java cannot log in to MySQL 8: authentication plugin caching_sha2_password not supported
ERROR 2061 (HY000): Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.
MySQL 8 accounts use the caching_sha2_password plugin by default, and Connector/J releases before 8.0.9 cannot use it.
3 causes, each with a fixDatabases
MySQL refuses to delete a row because other rows point to it
SQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails
Error 1451 means at least one child row still references the parent row you are deleting or changing.
3 causes, each with a fixDatabases
JDBC cannot talk to MySQL: Communications link failure
The last packet sent successfully to the server was 0 milliseconds ago.
Communications link failure means the TCP link to MySQL could not be opened or was closed.
3 causes, each with a fix