CODEWITHSUNDEEP
javadatabasespringhibernate
Martin
Martin

Reputation: 1350

How to do a like query with a variable set of strings?

I am triying to do a "like query" with a variable set of strings, in order to retrieve in a single query all texts that contains a set of words, that is:

 public long countByTextLike(Set<String> strings) {
        CriteriaBuilder builder = manager.getCriteriaBuilder();
        CriteriaQuery<Long> query = builder.createQuery(Long.class);
        Root<Example> root = query.from(Example.class);


        query.select(builder.count(root.get("id"))).where(
                builder.and(
                        builder.equal(root.get("lang"), "EN")
                )
        );

        //this does not work
        for (String word : strings) {
            query.where(builder.or(builder.like(root.get("text"), word)));
        }

        return manager.createQuery(query).getSingleResult();

    }

unfortunately this does not work because the where is overwritten in each loop. Only the last word of loop is used and "AND" restictions are being overwriten. How is possible to do a "like query" with a variable number of strings? It is not posible?

I am using the spring framework but i think that the question could be extendable to hibernate

Upvotes: 0

Views: 92

Answers (1)

AndresDLRG
AndresDLRG

Reputation: 136

You can use predicates, and then add them all with only one where clause

public long countByTextLike(Set<String> strings) {
    CriteriaBuilder builder = currentSession().getCriteriaBuilder();
    CriteriaQuery<Long> query = builder.createQuery(Long.class);
    Root<Example> root = query.from(Example.class);

    Predicate[] predicates = new Predicate[strings.size()];

    query.select(builder.count(root.get("id")));

    Predicate langPredicate = builder.equal(root.get("lang"), "EN");

    int cont = 0;
    for (String word : strings) {
        Predicate pred = builder.like(root.get("text"), "%" + word + "%");
        predicates[cont++] = pred;
    }

    Predicate orPredicate = builder.or(predicates);

    Predicate finalPredicate = builder.and(orPredicate, langPredicate);

    return manager.createQuery(query).where(finalPredicate).getSingleResult();

}

Upvotes: 2

Related Questions