Support query internal LIKE expressions [DATAJPA-292]

[spring-projects-issues]

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)

---

Referenced from: commits 60c8664, d40c83d, ddd1b9e, 285a994

[spring-projects-issues]

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:

… firstname like %?1 and lastname like ?1%

As we can only bind a single parameter once

[spring-projects-issues]

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]

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:

@Query("FROM User j WHERE j.name LIKE :substring OR j.description LIKE :substring")
Page<User> searchUsers(@Param("substring") String substring, Pageable page);

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]

Oliver Drotbohm commented

Any chance you open a new ticket and attach the exception you see?

[spring-projects-issues]

Alex commented

Done with DATAJPA-341, thanks