Search keywords in database

Question

I'm using Eclipselink and have a tricky problem regarding JPA NamedQueries.

My database table contains a column which is from type VARCHAR and stores a comma separated list of keywords as one String.

How can I create a NamedQuery in JPA to search theese keywords? I'd like to give a list of Strings as a parameter and as a result I'd like to have a list of objects which keyword list contain one of the Strings from the parameter list. Maybe like the following:

List<String> keywordList = new ArrayList<String>();
keywordList.add("test");
keywordList.add("car");    

List<Object> result = em.createNamedQuery("findObjectByKeywords", Object.class)
                            .setParameter("keywords", keywordList)
                            .getResultList();

Unfortunately I'm not such a big database/SQL expert. Maybe someone of you can help me?

I hope you understand my problem.

Edit: I am developing on Weblogic 10.3.6, which means I am not able to use JPA 2.0 features.

Edit2: I managed to activate JPA 2.0 in my Weblogic Server with the help of Oracle Enterprise Pack for Eclipse. Problem solved, I think.

I doubt that something like this is directly supported by the JPQL. You could try generating a `LIKE` for each keyword against the keyword-list column and separate each `LIKE` with an `OR`. That sounds poor and probably won't let you use a named query though. — Bhesh Gurung, May 13 '14 at 14:36
Other approach would be to have a named query with just one `LIKE` but then you would have to run the query once for each keyword in your `keywordList`. — Bhesh Gurung, May 13 '14 at 14:51
You can use a query as specified in this link which defines [where column in list][1] jpql. [1]: http://stackoverflow.com/questions/2772305/jpql-in-clause-java-arrays-or-lists-sets — Cahit Gungor, May 13 '14 at 23:01
@CahitGungor he could if the columns wasn't 'a comma separated list of keywords as one String'. In that case a pattern matching is needed... — makasprzak, May 14 '14 at 06:36

makasprzak · Accepted Answer · 2014-05-14T09:29:26.870

VALID FOR JPA2.0

As Bhesh commented a simple JPQL won't make it. The resulting SQL has to contain a where clause similar to following:

where keywords like '%keyword1%' or keywords like '%keyword2%' or ... or keywords like '%keywordN%'

This means: We need a loop here!

You could try to build a JPQL by yourself like Bhesh suggested in his first comment, though as he also stated it is not a brilliant idea. But don't worry - JPA provides also a Criteria API which comes handy in such situations. So, although you're not going to have a named query, you can still make it with JPA this way:

public List<YourEntity> findAllByKeywords(List<String> keywords){
    CriteriaBuilder builder = entityManager.getCriteriaBuilder();
    CriteriaQuery<YourEntity> query = builder.createQuery(YourEntity.class);
    Root<YourEntity> root = query.from(YourEntity.class);

    List<Predicate> predicates = new LinkedList<>();
    for (String keyword : keywords) {
        predicates.add(builder.like(root.<String>get("keywords"), "%" + keyword + "%"));
    }

    return entityManager.createQuery(
            query.select(root).where(
                    builder.or(
                            predicates.toArray(new Predicate[predicates.size()])
                    )
            ))
            .getResultList();
}

or (always slightly better with Guava)

public List<YourEntity> findAllByKeywords(List<String> keywords){
    final CriteriaBuilder builder = entityManager.getCriteriaBuilder();
    CriteriaQuery<YourEntity> query = builder.createQuery(YourEntity.class);
    final Root<YourEntity> root = query.from(YourEntity.class);
    return entityManager.createQuery(
            query.select(root).where(
                    builder.or(
                            transform(keywords, toPredicateFunction(builder, root)).toArray(new Predicate[]{})
                    )
            ))
            .getResultList();
}

private Function<String, Predicate> toPredicateFunction(final CriteriaBuilder builder, final Root<YourEntity> root) {
    return new Function<String, Predicate>() {
        @Override
        public Predicate apply(String input) {
            return builder.like(root.<String>get("keywords"), "%" + input + "%");
        }
    };
}

Looks like exactly what I want. Thank you! I'll give it a try this afternoon when I'm at work and give you feedback then. — LarsBauer, May 14 '14 at 05:25
Ok, I checked your suggestion. The problem is I am developing on Weblogic 10.3.6. Because of this I am forced to use JPA 1.0 and so I am not able to use Criteria API. Too bad... Any idea, how to implement this without CriteriaQuery? — LarsBauer, May 14 '14 at 08:45
Oh.. then you need to fall back to concatenation of JPQL by yourself like BheshGurung suggested, although this is not going to be easy. You need to pay attention to SQL injection vulnerability issues. Also, actually, the most proper way after all would be to rethink your model and have a ElementCollection association (separate table for keywords like @Arkadiry suggested). This would be WAY more efficient and much easier to implement. — makasprzak, May 14 '14 at 09:27

Search keywords in database

1 Answers1