Spring JPA Repository dynamic query

Question

Currently I have been using following Spring JPA Repository base custom query and it works fine,

 @Query("SELECT usr FROM User usr  WHERE usr.configurable = TRUE "
              + "AND (" +
                        "lower(usr.name) like lower(:filterText) OR lower(usr.userType.classType.displayName) like lower(:filterText) OR lower(usr.userType.model) like lower(:filterText)"
              +      ")"
              + "")
  public List<User> findByFilterText(@Param("filterText") String filterText, Sort sort);

I need to modify this query when filter text going to be a comma separated value. But as following manner it will be a dynamic query and how can I execute it.

Dynamic query I need to build,

String sql = "SELECT usr FROM User usr WHERE usr.configurable = TRUE";

for(String word : filterText.split(",")) {
                sql += " AND (lower(usr.name) like lower(:" + word + ") OR lower(usr.userType.classType.displayName) like lower(:" + word + ") OR lower(usr.userType.model) like lower(:" + word + "))";
}

Answer 1

Per JB Nizet and the spring-data documentation, you should use a custom interface + repository implementation.

Create an interface with the method:

public interface MyEntityRepositoryCustom {
    List<User> findByFilterText(Set<String> words);
}

Create an implementation:

@Repository
public class MyEntityRepositoryImpl implements MyEntityRepositoryCustom {
    @PersistenceContext
    private EntityManager entityManager;

    public List<User> findByFilterText(Set<String> words) {
        // implementation below
    }
}

Extend the new interface in your existing Repository interface:

public interface MyEntityRepository extends JpaRepository<MyEntity, Long>, MyEntityRepositoryCustom {
    // other query methods
}

Finally, call the method somewhere else:

dao.findByFilterText(new HashSet<String>(Arrays.asList(filterText.split(","))));

Query implementation

Your method of producing the sql variable, namely by concatenating some strings into the query is bad. Do not do this.

The word which you are concatenating must be a valid JPQL identifier, namely a : followed by a java identifier start, optionally followed by some java identifier part. This means that if your CSV contains foo bar,baz, you will attempt to use foo bar as an identifier and you'll get an exception.

You can instead use CriteriaBuilder to construct the query in a safe way:

public List<User> findByFilterText(Set<String> words) {
    CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    CriteriaQuery<User> q = cb.createQuery(User.class);
    Root<User> user = q.from(User.class);

    Path<String> namePath = user.get("name");
    Path<String> userTypeClassTypeDisplayName = 
                     user.get("userType").get("classType").get("displayName");
    Path<String> userTypeModel = user.get("userType").get("model");
    List<Predicate> predicates = new ArrayList<>();
    for(String word : words) {
        Expression<String> wordLiteral = cb.literal(word);
        predicates.add(
                cb.or(
                    cb.like(cb.lower(namePath), cb.lower(wordLiteral)),
                    cb.like(cb.lower(userTypeClassTypeDisplayName),
                            cb.lower(wordLiteral)),
                    cb.like(cb.lower(userTypeModel), cb.lower(wordLiteral))
                )
        );
    }
    q.select(doc).where(
            cb.and(predicates.toArray(new Predicate[predicates.size()]))
    );

    return entityManager.createQuery(q).getResultList();
}

Answer 2

I've been looking for the solution myself : The naming of the "Custom" repository interface and implentation is very strict (as said there How to add custom method to Spring Data JPA)

So, to be clear, the whole code : (But @beerbajay was right)

The custom method interface

public interface MyEntityRepositoryCustom {
    List<MyEntity> findSpecial();
}

The custom method implementation

public class MyEntityRepositoryImpl implements MyEntityRepositoryCustom {
    @PersistenceContext
    private EntityManager em;

    //custom method implementation
    public List<Object> findSpecial() {
        List<Object> list = em.createNativeQuery("select name, value from T_MY_ENTITY").getResultList();
        return list;
    }
}

The "original" repository

@Repository
public interface MyEntityRepository extends JpaRepository<MyEntity,Long>, MyEntityRepositoryCustom {
    //original methods here... do not redefine findSpecial()...
}

You can now use the "original" repository with the new custom methods

@Service
public class MyService {
    @Autowired
    private DataRepository r;

    public void doStuff() {
        List<Object> list = r.findSpecial();
    }
}

Answer 3

Spring Data JPA has a way to create Custom and Dynamic queries with "Specifications": Spring Data - Specifications

First, your interface which extends JPARepository or CRUDRepository should also implement JpaSpecificationExecutor<...> and that's all you need. Your repository now has a new method findAll which accepts a Specification<...> object, and your can use the method Beerbajay used to create Criteria Queries by overriding the method toPredicate(...) and there you are free to build (almost) any query you want like so:

Specification<...> spec = new Specification<...>() {
   	@Override
   	public Predicate toPredicate(Root<...> entity, CriteriaQuery<?> query, CriteriaBuilder cb) {			    
   	    List<Predicate> conditions = buildManyPredicates(cb, entity);
   	    return cb.and(conditions.toArray(new Predicate[conditions.size()]));
   	}
 };

repository.findAll(spec, PageRequest.of(0, 10));

This solves the problem of Spring Data trying to parse the methods you added in the custom interface (because there is no custom interface)