CODEWITHSUNDEEP
hibernatejpa
Eric Huang
Eric Huang

Reputation: 1264

Hibernate JPA QueryBuilder.like

I recently trying to refactor my Hibernate Session API to JPA API and I ran in to some problems

Here is my old Session code

Criteria criteria = session().createCriteria(Book.class);       

    // SEARCH both name, ispn field in the db
    if( search != null && !search.isEmpty() ){
        Criterion name = Restrictions.ilike("name", search, MatchMode.ANYWHERE);  // This use to return a list of similar results..
        Criterion ispn = Restrictions.ilike("ispn", search, MatchMode.ANYWHERE);

        LogicalExpression orExp = Restrictions.or(name, ispn);

        criteria.add(orExp);
    }

Here is my try of implementing CriteriaBuilder

CriteriaBuilder builder = entityManager.getCriteriaBuilder();

    CriteriaQuery<Book> criteria = builder.createQuery(Book.class);

    Root<Book> root = criteria.from(Book.class);

    criteria.select(root);



    if( search != null && !search.isEmpty() ){

        System.out.println(search);

        Predicate predicateName = builder.like(root.get(Book_.name), search); // root.get(Book_.name) must match exactly `search` for it to return anything.

        // Predicate predicateIspn = builder.like(root.get(Book_.ispn), search);

        criteria.where(predicateName);
    }

    List<Book> books = entityManager.createQuery(criteria).getResultList();

    return books;

Questions

  1. Predicate predicateName = builder.like(root.get(Book_.name), search); CirteriaBuilder.like doesn't seem to work I have to make sure search matches perfectly to root.get(Book_.name) for it to return results.

I have looked at hibernate docs it seems like CriteriaBuilder.like only takes 2 argument one is taken from db mappingroot.get(Book_.name) compare to input search there is no option where I can tell it to MatchMode.ANYWHERE. Any suggestions? or maybe I use the wrong method?

  1. LogicalExpression orExp = Restrictions.or(name, ispn); it use to check if the input matches name or ispn. But with JPA CriteriaBuilder how would I do this? I looked around for some tutorials Just couldn't find a tutorial that has a complete CRUD examples...

Thank you,

Upvotes: 3

Views: 3583

Answers (3)

Gurpreet Singh
Gurpreet Singh

Reputation: 400

MatchMode is a best util which helps to eliminate direct use of '%' in your hibernate code. But not aware of any such utility in JPA.

Using best practices, for wrapping search into '%'+'%, we can reuse the code of MatchMode enum itself to have a solution:

public staic String wrapToLike(String value) {
    return value == null ? "%" : MatchMode.ANYWHERE.toMatchString(value);
}

MatchMode.ANYWHERE.toMatchString(str) : toMatchString method converts the value string to enum from which it is called.

Options:

  • ANYWHERE: '%' + value + '%'
  • END: '%' + value
  • START: value + '%'
  • EXACT: value

Upvotes: 1

xyz
xyz

Reputation: 5417

  1. wrap search into '%'+'%' -> builder.like(root.get(Book_.ispn), '%'+search+'%') .

better create util method and reuse it. like :

    public staic String wrapToLike(String value) {

        return value == null ? "%" : "%"+value"%";
    }

or :

//root.get(Book_.name) - path
public Predicate createLikePredicate(CriteriaBuilder builder , Path path , String search)
    return builder.like(path, wrapToLike(search))
}

2.

 Predicate predicateName = builder.like(root.get(Book_.name), wrapToLike(search)); 
 Predicate predicateIspn = builder.like(root.get(Book_.ispn), wrapToLike(search)); 

 Restrictions.or(predicateName ,predicateIspn);

it should looks like :

public List<Book> findBooksBySearchKey(String search) {
    CriteriaBuilder builder = entityManager.getCriteriaBuilder();
    CriteriaQuery<Book> criteria = builder.createQuery(Book.class);
    Root<Book> root = criteria.from(Book.class);
    criteria.select(root);
    applySearchPredicate(builder, criteria, root, search);
    return entityManager.createQuery(criteria).getResultList();
}

private void applySearchPredicate(CriteriaBuilder builder, CriteriaQuery<Book> criteria, Root<Book> root, String search) {
    if (search != null && search.trim().length() > 0) {
        Predicate predicateName = builder.like(root.get(Book_.name), wrapToLike(search));
        Predicate predicateIspn = builder.like(root.get(Book_.ispn), wrapToLike(search));
        criteria.where(builder.or(predicateName , predicateIspn));
    }
}

public static String wrapToLike(String value) {
    return value == null ? "%" : "%"+value+"%";
}

Upvotes: 3

cнŝdk
cнŝdk

Reputation: 32145

Similar to SQL LIKE CriteriaBuilder.like expects a pattern wrapped between two %, so you just need to wrap your second search element like this : "%"+search+"%":

Predicate predicateName = builder.like(root.get(Book_.name), "%"+search+"%");

For further details you can check:

Upvotes: 2

Related Questions