java - 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();
}