Spring Data JDBC 使用 Boot 2.3.0 生成错误的 HSQLDB 查询 [英] Spring Data JDBC Generating Bad HSQLDB Query with Boot 2.3.0

问题描述

我有一个使用 Spring Data JDBC 的 Spring Boot 项目.测试使用 HSQLDB.当我尝试升级到 Spring Boot 2.3.0 时，我的存储库测试开始失败.

I have a Spring Boot project that uses Spring Data JDBC. The tests use HSQLDB. My repository tests started failing when I attempted an upgrade to Spring Boot 2.3.0.

Spring Data JDBC 现在似乎引用了表名和列名.Spring Boot 2.2.7 中包含的 Spring Data JDBC 版本没有.

Spring Data JDBC appears to now quote table and column names. The version of Spring Data JDBC included with Spring Boot 2.2.7 did not.

https://github.com/mrgrew/boot230bug 上的项目展示了差异.Spring Boot 2.3.0 生成 INSERT INTO "stats.counter" ("COUNTER_NAME") VALUES (?) 失败.Spring Boot 2.2.7 生成 INSERT INTO stats.counter (counter_name) VALUES (?) 成功.

The project at https://github.com/mrgrew/boot230bug demonstrates the difference. Spring Boot 2.3.0 generates INSERT INTO "stats.counter" ("COUNTER_NAME") VALUES (?) which fails. Spring Boot 2.2.7 generates INSERT INTO stats.counter (counter_name) VALUES (?) which succeeds.

我猜 Spring Data JDBC 没有正确识别方言.我的测试属性指定了 spring.datasource.platform=hsqldb，我认为这可以避免歧义.

I'm guessing Spring Data JDBC isn't properly identifying the dialect. My test properties specify spring.datasource.platform=hsqldb which I thought would avoid ambiguity.

这看起来像是 Spring Boot 2.3.0 中包含的 Spring Data JDBC 版本的一个错误.任何人都可以确认这是一个错误或建议对使用 Boot 2.3.0 的演示项目进行更改吗?

This seems like a bug with the version of Spring Data JDBC included with Spring Boot 2.3.0. Can anyone confirm this is a bug or suggest changes to my demo project that work with Boot 2.3.0?

预先感谢您的任何建议或讨论！

Thanks in advance for any advice or discussion!

推荐答案

看起来我问得太快了...在我问这个问题的同一天发布了迁移指南！https://spring.io/blog/2020/05/20/migrating-to-spring-data-jdbc-2-0

Looks like I was too quick to ask... A migration guide was published the same day I asked this question! https://spring.io/blog/2020/05/20/migrating-to-spring-data-jdbc-2-0

迁移指南解释了我所观察到的内容:

The migration guide explains what I was observing:

Spring Data JDBC 1.x 主要使用表名和列名而不更改它们.当您使用 SQL 关键字作为属性或实体名称或尝试在列名称中使用某些特殊字符时，这会导致问题.

Quoting of Identifiers

Spring Data JDBC 1.x uses table and column names mostly without changing them. This causes problems when you use an SQL key word as a property or entity name or when you tried to use some special character in a column name.

出于这个原因，Spring Data JDBC 2.0 默认引用所有标识符.这使得名称区分大小写，至少对于大多数数据库而言.由于我们还在默认情况下将生成的名称转换为数据库使用的默认字母大小写，因此这不会导致任何问题，假设您在 CREATE TABLE 语句中没有使用引号，就像大多数人一样.

For this reason Spring Data JDBC 2.0 quotes all identifiers by default. This makes the names case-sensitive, at least for most databases. Since we also, by default, convert the names generated into the default letter casing used by the database, this should not cause any problems, assuming you used no quotes in the CREATE TABLE statements, as most people do.

从 Liquibase 更改日志中删除 @Table 注释和 schemaName 删除了我无意中使用的引用并解决了问题.作为奖励，我不再需要创建架构，因此我可以删除 spring.datasource.platform=hsqldb 和 schema-hsqldb.sql 文件.请参阅工作版本的 fixed 分支.

Removing the @Table annotation and schemaName from the Liquibase change log removed my unintended use of quoting and solved the issue. As a bonus, I didn't need to create a schema anymore so I could remove spring.datasource.platform=hsqldb and the schema-hsqldb.sql file. See the fixed branch for the working version.

感谢您的推动 Jens Schauder！

这篇关于Spring Data JDBC 使用 Boot 2.3.0 生成错误的 HSQLDB 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！