JPA Criteria API by samples – Part-II

you can read first part from JPA Criteria API by samples – Part-I

some more examples with JPA criteria API

Simple Join query

        long category=200L;
        Query query = entityManager.createQuery("select s from OrderItem s " +
        		"where s.product.category=:cat");
        query.setParameter("cat", category);
        List<OrderItem> list = query.getResultList();

        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<Object> criteriaQuery = criteriaBuilder.createQuery();
        Root<OrderItem> from = criteriaQuery.from(OrderItem.class);
        Path<Object> path = from.join("product").get("category");

        CriteriaQuery<Object> select = criteriaQuery.select(from);
        select.where(criteriaBuilder.equal(path, category));

        TypedQuery<Object> typedQuery = entityManager.createQuery(select);
        List<Object> resultList = typedQuery.getResultList();
        assertEqualsList(list, resultList);

simple fetch join query

        long category=200L;
        Query query = entityManager.createQuery("select s from OrderItem s " +
        		"join fetch s.product where s.product.category=:cat");
        query.setParameter("cat", category);
        List<OrderItem> list = query.getResultList();

        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<Object> criteriaQuery = criteriaBuilder.createQuery();
        Root<OrderItem> from = criteriaQuery.from(OrderItem.class);
        Path<Object> path = from.join("product").get("category");

        from.fetch("product"); //FETCH product

        CriteriaQuery<Object> select = criteriaQuery.select(from);
        select.where(criteriaBuilder.equal(path, category));

        TypedQuery<Object> typedQuery = entityManager.createQuery(select);
        List<Object> resultList = typedQuery.getResultList();
        assertEqualsList(list, resultList);

subselect (subquery) join query

         Query query = entityManager.createQuery(
                "select s from OrderItem s join fetch s.product" +
                " where s.product.category in" +
                " (select sb.pbyte from SimpleBean sb where sb.pint>=30000)");

        List<OrderItem> list = query.getResultList();

        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<Object> criteriaQuery = criteriaBuilder.createQuery();
        Root<OrderItem> from = criteriaQuery.from(OrderItem.class);
        Path<Object> path = from.join("product").get("category");
        from.fetch("product");
        CriteriaQuery<Object> select = criteriaQuery.select(from);

        Subquery<SimpleBean> subquery = criteriaQuery.subquery(SimpleBean.class);
        Root fromSimpleBean = subquery.from(SimpleBean.class);
        subquery.select(fromSimpleBean.get("pbyte"));
        subquery.where(criteriaBuilder.ge(fromSimpleBean.get("pint"),30000));
        select.where(criteriaBuilder.in(path).value(subquery));

        TypedQuery<Object> typedQuery = entityManager.createQuery(select);
        List<Object> resultList = typedQuery.getResultList();
        assertEqualsList(list, resultList);
  • Trackback are closed
  • Comments (6)
    • SebastianB
    • October 25th, 2010

    Hi,

    thanks for this nice summary, but is there any possibility to “mix” JPA Criteria-Queries with native Restrictions like in Hibernate by the DetachedCriteria API?

    Criteria crit = session.createCriteria(Foo.class);
    crit.add(Restrictions.sqlRestriction(“name like ‘%Bar%’”));

    Thanks in advance

    Sebastian

    • Hi Sebastian,
      As fas as I can see, there is no native SQL support in JPA criteria API. but let me know if you can find a work around .

      Cheers

    • SebastianB
    • October 26th, 2010

    Hi,

    i just switched to querydsl (http://source.mysema.com/display/querydsl/Querydsl) and so much problems i had with JPA2-Criteria are gone. have a look, its worth it ;)

    Greetings

    Sebastian

  1. for dynamic search queries you can use yagdao (Yet Another Generic DAO)
    http://www.altuure.com/projects/yagdao/
    http://www.altuure.com/projects/yagdao/usage#criteriaquery

    • Angel.A
    • July 4th, 2011

    Hi mate, your post has been helpfull, but I have a complex query that I dont know how to do with Criteria.

    It would be lke this:

    SELECT * FROM TAGS
    WHERE (TAGS.TAGLISTID, TAGS.DATECREATION) IN(
    SELECT MAX(TAGLIST.ID), TAGLIST.DATECREATION FROM TAGLIST
    WEHERE TAGLIST.DATECREATION= ?
    GROUP BY TAGLIST.DATECREATION)

    there are some other WHEREs and ANDs, but the main problem is to make the where (a,b) in (max(c),d).

    Thanks in advance.

    • Srini
    • October 11th, 2011

    Hi,
    I tried to use the subquery in the selection of criteria query. I am able to do it in JPQL and I tried to do the same using JPA criteria and it is saying “java.lang.IllegalStateException: Subquery cannot occur in select clause”. Is there a way to use subquery in selection or is there a way to join subquery to the main query using JPA criteria?

Comment are closed.