Create varargs @Query for same database column

Question

I'm trying to implement a simple text search. I would like to allow a user to type any number of words, and check all of those words against the title of an article. A result would be returned if the title matched all of the words in the search (in any order).

Some example database queries would be something like this:

SELECT * FROM Article WHERE
    lower(title) like lower(concat('%', '<term1>', '%'));

SELECT * FROM Article WHERE
   lower(title) like lower(concat('%', '<term1>', '%'))
   AND lower(title) like lower(concat('%', '<term2>', '%'));

SELECT * FROM Article WHERE
   lower(title) like lower(concat('%', '<term1>', '%'))
   AND lower(title) like lower(concat('%', '<term2>', '%'));
   AND lower(title) like lower(concat('%', '<term3>', '%'));

Notice how the title column is searched in each case, and will be compared with either 1, 2, or 3 (or more) terms.

Can I create a single JPQL query to do this?

Something like this:

@Query("SELECT a FROM Article a WHERE lower(a.title) in %:terms%")
Iterable<Article> findAllContainingTitle(String... terms)

It appears Java's varargs work, but more in a sense of specific values within a set.

UPDATE 1

Thanks to this question, and this link I learned how to do this directly in a Postgres query:

SELECT * FROM Article WHERE ~~* ALL(ARRAY['%term1%', '%term2%', '%term3%']);

The challenge now has been to try and convert this to a Spring Data JPA query.

Note: the ~~* can also be exchanged with ILIKE (case insensitive LIKE statement).

score 0 · Answer 1 · answered Apr 16 '19 at 15:43

This does not answer my question, but it at least provides a workaround, should anyone else need something similar. Part of the requirement was to also use JOIN FETCH to pull back all data in a single query, and not run into the N+1 performance problem.

The workaround is to use CriteriaBuilder and CriteriaQuery to generate the query dynamically. I feel this creates a less readable query, but it certainly is better than nothing.

@Autowired
private EntityManager entityManager;

...

CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder()
CriteriaQuery<Content> criteriaQuery = criteriaBuilder.createQuery(Article.class);

Root<Article> rootArticle = crirteriaQuery.from(Article.class);

// Provide any number of joins here
rootArticle.fetch("author", JoinType.INNER);

// This is where we dynamically add all of the 'like' statements
List<Predicate> predicates = new ArrayList<>();
// Assume that 'searchQuery' is plain text from the user, that will be split on spaces to get individual search terms.
for (String searchTerm : searchQuery.split(" ")) {
    predicates.add(criteriaBuilder.like(
        criteriaBuilder.lower(rootArticle.get("title")),
        "%"+searchTerm.toLowerCase()+"%"));
}
criteriaQuery.where(predicates.toArray(new Predicate[0]));

TypedQuery<Article> query = entityManager.createQuery(criteriaQuery);
List<Article> articles = query.getResultList();

Special thanks to this answer which linked to this page which explained how to add the JOIN statements to a CriteriaQuery.

Create varargs @Query for same database column

1 Answers1