Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Minor Minor
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 1.3.1, 1.4 M1
    • Component/s: Core
    • Labels:
      None

      Description

      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

        Hide
        Oliver Gierke added a comment -

        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.

        Show
        Oliver Gierke added a comment - 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.
        Hide
        Michael Isvy added a comment -
        Show
        Michael Isvy added a comment - 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
        Hide
        Alex added a comment -

        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?

        Show
        Alex added a comment - 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?
        Hide
        Oliver Gierke added a comment -

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

        Show
        Oliver Gierke added a comment - Any chance you open a new ticket and attach the exception you see?
        Hide
        Alex added a comment -

        Done with DATAJPA-341, thanks

        Show
        Alex added a comment - Done with DATAJPA-341 , thanks

          People

          • Assignee:
            Oliver Gierke
            Reporter:
            Michael Isvy
          • Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: