Repository navigation
Support query internal LIKE expressions [DATAJPA-292] #704
Description
Michael Isvy opened DATAJPA-292 and commented
Let us consider the following method:
@Query("SELECT owner FROM Owner owner WHERE owner.lastName LIKE :lastName%")
Collection<Owner> findByLastName(String lastName) throws DataAccessException;
The above code is not supported because, according to JPA, we should have something like this:
entityManager.createQuery("SELECT owner FROM Owner owner WHERE owner.lastName LIKE :lastName")
query.setParam("lastName", lastName + "%")
So the current suggested way in Spring Data is to rely on query generation using the 'StartingWith' suffix:
Collection<Owner> findByLastNameStartingWith(String lastName) throws DataAccessException;
It works, except when we'd like to use a fetch join.
It would be great to have the possibility to do this:
@Query("SELECT owner FROM Owner owner left join fetch owner.pets WHERE owner.lastName LIKE :lastName%")
Collection<Owner> findByLastName(String lastName) throws DataAccessException;
I think the best way to handle it is to add some preprocessing code so Spring Data would first search for "%" characters and add them to the parameter's value (if any)
Activity
spring-projects-issues commentedon Mar 13, 2013
Oliver Drotbohm commented
Implemented in the latest snapshots. One thing to note is that references to the same parameter can only be equipped with the very same LIKE expression. The following query snippet is invalid:
As we can only bind a single parameter once
spring-projects-issues commentedon Apr 17, 2013
Michael Isvy commented
Just so you know I've updated Spring-Petclinic consequently and it works just fine.
See here:
https://github.com/SpringSource/spring-petclinic/blob/master/src/main/java/org/springframework/samples/petclinic/repository/springdatajpa/SpringDataOwnerRepository.java
spring-projects-issues commentedon May 3, 2013
Alex commented
That broke backward compatibility for me!
Older query where I passed the pattern as a parameter now does not work anymore, for example this one won't compile:
Since the pattern is provided by the client (which I can't modify) I can't put it statically in the definition. Is there a workaround for this?
spring-projects-issues commentedon May 3, 2013
Oliver Drotbohm commented
Any chance you open a new ticket and attach the exception you see?
spring-projects-issues commentedon May 3, 2013
Alex commented
Done with DATAJPA-341, thanks