Version
Spring Boot: 3.0.1
JDK: 17
MYSQL: 8.0.27
QueryDsl: 5.0.0
Hibernate: 6.1.6
Example code: https://github.com/KevinBlandy/springboo3-querydsl
Query
@Test
@Transactional(readOnly = true)
public void test () {
var tag = QTag.tag;
var postTag = QPostTag.postTag;
var post = QPost.post;
var query = new JPAQueryFactory(this.entityManager);
SimpleExpression<Long> postCount = Expressions.as(JPAExpressions
.select(Expressions.ONE.count())
.from(postTag)
.innerJoin(post).on(post.id.eq(postTag.postId))
.where(postTag.tagId.eq(tag.id)
.and(post.enabled.eq(true)
.and(post.deleted.eq(0L)))
)
, "post_count");
var tuple = query.select(tag.id, tag.title, tag.icon, tag.instruction, tag.enabled, tag.path, postCount)
.from(tag)
.where(tag.id.eq(10000L))
.fetchOne();
log.info("tuple:{}", tuple);
}
Generated SQL
2022-12-23T17:36:58.570+08:00 INFO 580 --- [ main] example.text.ExampleApplicationTest : Started ExampleApplicationTest in 6.0 seconds (process running for 7.253)
Hibernate:
select
t1_0.id,
t1_0.title,
t1_0.icon,
t1_0.instruction,
t1_0.enabled,
t1_0.path,
(select
count(1)
from
post_tag p1_0
join
post p2_0
on p2_0.id=p1_0.post_id
where
p1_0.tag_id=t1_0.id
and (
p2_0.enabled=abs(sign(?))
and p2_0.deleted=cast(? as BIGINT UNSIGNED COMMENT '是否删除。0:未删除,非0:删除时间戳')
))
from
tag t1_0
where
t1_0.id=?
2022-12-23T17:36:59.843+08:00 TRACE 580 --- [ main] org.hibernate.orm.jdbc.bind : binding parameter [1] as [BOOLEAN] - [true]
2022-12-23T17:36:59.845+08:00 TRACE 580 --- [ main] org.hibernate.orm.jdbc.bind : binding parameter [2] as [BIGINT] - [0]
2022-12-23T17:36:59.845+08:00 TRACE 580 --- [ main] org.hibernate.orm.jdbc.bind : binding parameter [3] as [BIGINT] - [10000]
2022-12-23T17:36:59.865+08:00 WARN 580 --- [ main] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 1064, SQLState: 42000
2022-12-23T17:36:59.865+08:00 ERROR 580 --- [ main] o.h.engine.jdbc.spi.SqlExceptionHelper : 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 'BIGINT UNSIGNED COMMENT '是否删除。0:未删除,非0:删除时间戳')' at line 1
I used a subquery in my query, but the generated SQL used a function that didn't look like a mysql dialect, which caused an exception.
And the column comments defined in the @Column
annotation are parsed into the SQL.
Stack
jakarta.persistence.PersistenceException: Converting `org.hibernate.exception.SQLGrammarException` to JPA `PersistenceException` : JDBC exception executing SQL [select t1_0.id,t1_0.title,t1_0.icon,t1_0.instruction,t1_0.enabled,t1_0.path,(select count(1) from post_tag p1_0 join post p2_0 on p2_0.id=p1_0.post_id where p1_0.tag_id=t1_0.id and (p2_0.enabled=abs(sign(?)) and p2_0.deleted=cast(? as BIGINT UNSIGNED COMMENT '是否删除。0:未删除,非0:删除时间戳'))) from tag t1_0 where t1_0.id=?]
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:165)
at org.hibernate.query.spi.AbstractSelectionQuery.list(AbstractSelectionQuery.java:374)
at org.hibernate.query.sqm.internal.QuerySqmImpl.list(QuerySqmImpl.java:1073)
at org.hibernate.query.spi.AbstractSelectionQuery.getSingleResult(AbstractSelectionQuery.java:457)
at org.hibernate.query.sqm.internal.QuerySqmImpl.getSingleResult(QuerySqmImpl.java:1103)
at com.querydsl.jpa.impl.AbstractJPAQuery.getSingleResult(AbstractJPAQuery.java:214)
at com.querydsl.jpa.impl.AbstractJPAQuery.fetchOne(AbstractJPAQuery.java:326)
at example.text.ExampleApplicationTest.test(ExampleApplicationTest.java:56)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:568)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:59)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:56)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at org.springframework.test.context.junit4.statements.RunBeforeTestExecutionCallbacks.evaluate(RunBeforeTestExecutionCallbacks.java:76)
at org.springframework.test.context.junit4.statements.RunAfterTestExecutionCallbacks.evaluate(RunAfterTestExecutionCallbacks.java:84)
at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:75)
at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86)
at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:366)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:252)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:97)
at org.junit.runners.ParentRunner$4.run(ParentRunner.java:331)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:79)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:329)
at org.junit.runners.ParentRunner.access$100(ParentRunner.java:66)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:293)
at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)
at org.junit.runners.ParentRunner$3.evaluate(ParentRunner.java:306)
at org.junit.runners.ParentRunner.run(ParentRunner.java:413)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:191)
at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
at org.junit.runner.JUnitCore.run(JUnitCore.java:115)
at org.junit.vintage.engine.execution.RunnerExecutor.execute(RunnerExecutor.java:42)
at org.junit.vintage.engine.VintageTestEngine.executeAllChildren(VintageTestEngine.java:80)
at org.junit.vintage.engine.VintageTestEngine.execute(VintageTestEngine.java:72)
at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:147)
at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:127)
at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:90)
at org.junit.platform.launcher.core.EngineExecutionOrchestrator.lambda$execute$0(EngineExecutionOrchestrator.java:55)
at org.junit.platform.launcher.core.EngineExecutionOrchestrator.withInterceptedStreams(EngineExecutionOrchestrator.java:102)
at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:54)
at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:114)
at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:95)
at org.junit.platform.launcher.core.DefaultLauncherSession$DelegatingLauncher.execute(DefaultLauncherSession.java:91)
at org.junit.platform.launcher.core.SessionPerRequestLauncher.execute(SessionPerRequestLauncher.java:60)
at org.eclipse.jdt.internal.junit5.runner.JUnit5TestReference.run(JUnit5TestReference.java:98)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:40)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:529)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:756)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:452)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:210)
Caused by: org.hibernate.exception.SQLGrammarException: JDBC exception executing SQL [select t1_0.id,t1_0.title,t1_0.icon,t1_0.instruction,t1_0.enabled,t1_0.path,(select count(1) from post_tag p1_0 join post p2_0 on p2_0.id=p1_0.post_id where p1_0.tag_id=t1_0.id and (p2_0.enabled=abs(sign(?)) and p2_0.deleted=cast(? as BIGINT UNSIGNED COMMENT '是否删除。0:未删除,非0:删除时间戳'))) from tag t1_0 where t1_0.id=?]
at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:64)
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:56)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:109)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:95)
at org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.executeQuery(DeferredResultSetAccess.java:253)
at org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.getResultSet(DeferredResultSetAccess.java:146)
at org.hibernate.sql.results.jdbc.internal.JdbcValuesResultSetImpl.advanceNext(JdbcValuesResultSetImpl.java:205)
at org.hibernate.sql.results.jdbc.internal.JdbcValuesResultSetImpl.processNext(JdbcValuesResultSetImpl.java:85)
at org.hibernate.sql.results.jdbc.internal.AbstractJdbcValues.next(AbstractJdbcValues.java:29)
at org.hibernate.sql.results.internal.RowProcessingStateStandardImpl.next(RowProcessingStateStandardImpl.java:88)
at org.hibernate.sql.results.spi.ListResultsConsumer.consume(ListResultsConsumer.java:197)
at org.hibernate.sql.results.spi.ListResultsConsumer.consume(ListResultsConsumer.java:33)
at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.doExecuteQuery(JdbcSelectExecutorStandardImpl.java:443)
at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.executeQuery(JdbcSelectExecutorStandardImpl.java:166)
at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.list(JdbcSelectExecutorStandardImpl.java:91)
at org.hibernate.sql.exec.spi.JdbcSelectExecutor.list(JdbcSelectExecutor.java:31)
at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.lambda$new$0(ConcreteSqmSelectQueryPlan.java:113)
at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.withCacheableSqmInterpretation(ConcreteSqmSelectQueryPlan.java:335)
at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.performList(ConcreteSqmSelectQueryPlan.java:276)
at org.hibernate.query.sqm.internal.QuerySqmImpl.doList(QuerySqmImpl.java:571)
at org.hibernate.query.spi.AbstractSelectionQuery.list(AbstractSelectionQuery.java:363)
... 53 more
Caused by: 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 'BIGINT UNSIGNED COMMENT '是否删除。0:未删除,非0:删除时间戳')' at line 1
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:916)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:972)
at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
at org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.executeQuery(DeferredResultSetAccess.java:217)
... 69 more
application.yml
server:
port: 8088
logging:
level:
"org.hibernate.orm.jdbc.bind": TRACE
spring:
datasource:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/demo?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowMultiQueries=true
username: root
password: root
jpa:
database-platform: org.hibernate.dialect.MySQLDialect
open-in-view: false
hibernate:
ddl-auto: none
show-sql: true
properties:
"hibernate.format_sql": true
This query works fine on SpringBoot 2.7.6.
2022-12-23 17:59:46.295 INFO 13964 --- [ main] o.s.t.c.transaction.TransactionContext : Began transaction (1) for test context [DefaultTestContext@765f05af testClass = ExampleApplicationTest, testInstance = example.text.ExampleApplicationTest@5bd82fed, testMethod = test@ExampleApplicationTest, testException = [null], mergedContextConfiguration = [WebMergedContextConfiguration@62f68dff testClass = ExampleApplicationTest, locations = '{}', classes = '{class example.ExampleApplication}', contextInitializerClasses = '[]', activeProfiles = '{}', propertySourceLocations = '{}', propertySourceProperties = '{org.springframework.boot.test.context.SpringBootTestContextBootstrapper=true, server.port=0}', contextCustomizers = set[org.springframework.boot.test.context.filter.ExcludeFilterContextCustomizer@2a693f59, org.springframework.boot.test.json.DuplicateJsonObjectContextCustomizerFactory$DuplicateJsonObjectContextCustomizer@723ca036, org.springframework.boot.test.mock.mockito.MockitoContextCustomizer@0, org.springframework.boot.test.web.client.TestRestTemplateContextCustomizer@7a5ceedd, org.springframework.boot.test.autoconfigure.actuate.metrics.MetricsExportContextCustomizerFactory$DisableMetricExportContextCustomizer@43f02ef2, org.springframework.boot.test.autoconfigure.properties.PropertyMappingContextCustomizer@0, org.springframework.boot.test.autoconfigure.web.servlet.WebDriverContextCustomizerFactory$Customizer@33ecda92, org.springframework.boot.test.context.SpringBootTestArgs@1, org.springframework.boot.test.context.SpringBootTestWebEnvironment@2dc54ad4], resourceBasePath = 'src/main/webapp', contextLoader = 'org.springframework.boot.test.context.SpringBootContextLoader', parent = [null]], attributes = map['org.springframework.test.context.web.ServletTestExecutionListener.activateListener' -> false, 'org.springframework.test.context.event.ApplicationEventsTestExecutionListener.recordApplicationEvents' -> false]]; transaction manager [org.springframework.orm.jpa.JpaTransactionManager@2ea0161f]; rollback [true]
Hibernate:
select
tag0_.id as col_0_0_,
tag0_.title as col_1_0_,
tag0_.icon as col_2_0_,
tag0_.instruction as col_3_0_,
tag0_.enabled as col_4_0_,
tag0_.path as col_5_0_,
(select
count(1)
from
post_tag posttag1_
inner join
post post2_
on (
post2_.id=posttag1_.post_id
)
where
posttag1_.tag_id=tag0_.id
and post2_.enabled=?
and post2_.deleted=?) as col_6_0_
from
tag tag0_
where
tag0_.id=?
2022-12-23 17:59:46.772 INFO 13964 --- [ main] example.text.ExampleApplicationTest : tuple:null
Thanks.
bug