CODEWITHSUNDEEP
springspring-data-jpajava-17spring-boot-3
Hany Sakr
Hany Sakr

Reputation: 2939

SpringBoot 3 using semicolon in JPA query

I have a JPA query in Spring Boot 3 that search for a text with semicolon. The query is like this:

Query(value=".... LIKE CONCAT(?, '%;') ...")

It ends up generating the following query

LIKE CONCAT(?, '% limit ?;')

I tried different approaches to use ESCAPE the key word or adding \\ but all didn't success. The same code was working earlier in Spring Boot 2.7

I don't know what problem there are after the upgrade!
Any ideas?

Update This normal query works fine with the List

@Query(value = "SELECT u.* FROM  user u WHERE u.userId LIKE CONCAT(?1, '%;') OR u.userId IN ?2)", nativeQuery = true)
List<UserEntity> search(String search, List<String> userIds);

If we replace the List with pagination, then it fails

@Query(value = "SELECT u.* FROM  user u WHERE u.userId LIKE CONCAT(?1, '%;') OR u.userId IN ?2)", nativeQuery = true)
Page<UserEntity> search(String search, List<String> userIds, Pageable pageable);

Upvotes: 1

Views: 639

Answers (1)

Hany Sakr
Hany Sakr

Reputation: 2939

To resolve the semicolon issue with pagination, I did the below work-around by replacing pagination using Page with List and create my own custom pagination logic with additional counter query

@Query(value = "SELECT u.* FROM  user u WHERE u.userId LIKE CONCAT(?1, ';%') OR u.userId IN ?2) limit ?3, ?4", nativeQuery = true)
List<UserEntity> search(String search, List<String> userIds, int page, int size);

@Query(value = "SELECT count(u.userId) FROM  user u WHERE u.userId LIKE CONCAT(?1, ';%') OR u.userId IN ?2)", nativeQuery = true)
Integer searchCount(String search, List<String> userIds);

Integer total = userDao.searchCount(search , userIds);
List list = userDao.search(search, userIds , page * size, size);
Page<UserEntity> = new PageImpl(list, PageRequest.of(page, size), total);

Upvotes: 0

Related Questions