Spring Data JPA generates incorrect query when using pagination and semicolon in native query

spring-projects / spring-data-jpa

Simplifies the development of creating a JPA-based data access layer.

https://spring.io/projects/spring-data-jpa/

Apache License 2.0

2.93k stars 1.39k forks source link

Spring Data JPA generates incorrect query when using pagination and semicolon in native query #3455

Closed iceStarFox closed 1 month ago

iceStarFox commented 2 months ago

I have a native query using semicolon in between, query is like ...GROUP_CONCAT( a.reason SEPARATOR ';')... Using pagination and return with a Page object.

I noticed it would always generated query like ...GROUP_CONCAT( a.reason SEPARATOR ' limit ?;')...

This issue we encountered after we did SpringBoot upgrading from 2.7 to 3.2.3.

The same issue can also refer to stack overflow. https://stackoverflow.com/questions/77306698/springboot-3-using-semicolon-in-jpa-query

christophstrobl commented 2 months ago

@iceStarFox thank you for reporting - we'll look into it.

christophstrobl commented 2 months ago

Assuming you're using hibernate as a JPA provider this seems to be an issue with the hibernate implementation, that can be reproduced with plain JPA as outlined below.

Query nativeQuery = entityManager.createNativeQuery("SELECT * FROM SD_User WHERE lastname LIKE CONCAT(?1, '%;')");
nativeQuery.setParameter(1, "Matthews");
nativeQuery.setFirstResult(0);
nativeQuery.setMaxResults(10);

You may want to report the issue with the hibernate team. It would be great if you could leave a comment with the link to the new issue on this ticket.

quaff commented 2 months ago

see https://hibernate.atlassian.net/browse/HHH-18033