Is it possible to add wildcards to @Query parameters?

Question

I've defined my ContactDao as follows:

public interface ContactDao extends JpaRepository<Contact, Long> {

  /**
   * Finds all contacts that the given user has entered where the contact's full name matches {@code name}.
   * @param userId The current user's LDAP id.
   * @param name The name to search for.
   * @return A list of contacts matching the specified criteria.
   */
  @Query(" select c from Form as f" +
           " inner join f.contacts as c" +
           " where f.requestorUserId = :userId" +
           " and lower(c.fullName) like lower(:name)" +
           " order by lower(c.fullName)")
  List<Contact> findUserContactsByUserIdAndName(@Param("userId") String userId, @Param("name") String name);

}

The above is working perfectly, and the SQL generated is what I'd write myself. The problem is that I'd like to add surrounding wild-cards to the :name parameter. Is there any nice way to do this? I've had a look at the Spring Data JPA reference document and I can't find anything regarding wildards and @query.

The following hack works, but is a bit ugly:

and lower(c.fullName) like '%' || lower(:name) || '%'

Does anyone have a better solution?

Thanks, Muel.

Answer 1

I would also not call it a hack - it's the way JPQL syntax is defined for exactly these problems.

However, there is an alternative using a CriteriaBuilder/CriteriaQuery, but maybe you find it even more complex (but you get compile-time type safety in return).