JPA Criteria API by samples – Part-I

In this post I try to explain jpa criteria API by simple example on a single entity.

Although examples are pretty much simple it will help you with finalized version of JPA 2.0 API.

For Part II please read JPA Criteria API by samples – Part-II

and in this examples in my opinion building query by programming is not so easy and maintainable  but still possible.

PS: I tested it with hibernate 3.5.1

Simple Query

		Query query = entityManager.createQuery("from SimpleBean s");
		List<SimpleBean> list = query.getResultList();

		CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
		CriteriaQuery<Object> criteriaQuery = criteriaBuilder.createQuery();
		Root<SimpleBean> from = criteriaQuery.from(SimpleBean.class);
		CriteriaQuery<Object> select = criteriaQuery.select(from);
		TypedQuery<Object> typedQuery = entityManager.createQuery(select);
		List<Object> resultList = typedQuery.getResultList();
		assertEqualsList(list, resultList);

Simple Query with Order

               List<SimpleBean> expected= entityManager.createQuery("from SimpleBean s order by s.pbyte asc ,s.pint desc")
                                          .getResultList();

		//...
		CriteriaQuery<Object> select = criteriaQuery.select(from);
                select.orderBy(criteriaBuilder.asc(from.get("pbyte"))
                                ,criteriaBuilder.desc(from.get("pint")));
		TypedQuery<Object> typedQuery = entityManager.createQuery(select);
               //...

Simple Query with selected fields

		Query query = entityManager.createQuery("select s.id,s.pbyte from SimpleBean s ");
		List listExpected = query.getResultList();
                //...
		CriteriaQuery<Object> select = criteriaQuery.multiselect(from.get("id"),from.get("pbyte"));

Query with single criteria

        int arg1 = 20000;
        Query query = entityManager.createQuery("from SimpleBean s where s.pint>=:arg1");
        query.setParameter("arg1", arg1);
        List<SimpleBean> list = query.getResultList();

        //...
        CriteriaQuery<Object> select = criteriaQuery.select(from);

        Predicate predicate = criteriaBuilder.ge(from.get("pint"), arg1);
        criteriaQuery.where(predicate);
        //...

Query with multiple criterias

        int arg1 = 20000;
        int arg2 = 50000;
        Query query = entityManager.createQuery("from SimpleBean s where s.pint>=:arg1 and s.pint<=:arg2");
        query.setParameter("arg1", arg1);
        query.setParameter("arg2", arg2);
        List<SimpleBean> list = query.getResultList();

        //..
        Predicate predicate1 = criteriaBuilder.ge(from.get("pint"), arg1);
        Predicate predicate2 = criteriaBuilder.le(from.get("pint"), arg2);
        criteriaQuery.where(criteriaBuilder.and(predicate1, predicate2));
        //..

Query with single literal

        String arg1 = "name";
        Query query = entityManager.createQuery("from SimpleBean s where upper(s.pstring) like upper(:arg1)");
        query.setParameter("arg1", arg1);
        List<SimpleBean> list = query.getResultList();

        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<Object> criteriaQuery = criteriaBuilder.createQuery();
        Root from = criteriaQuery.from(SimpleBean.class);
        CriteriaQuery<Object> select = criteriaQuery.select(from);

        Expression<String> literal = criteriaBuilder.upper(criteriaBuilder.literal((String) arg1));
        Predicate predicate = criteriaBuilder.like(criteriaBuilder.upper(from.get("pstring")), literal);

        criteriaQuery.where(predicate);

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

Query with summary (min,max,avg)

        Query query = entityManager.createQuery("select min(s.pint) from SimpleBean s");

        Object minActual = query.getSingleResult();

        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<Object> criteriaQuery = criteriaBuilder.createQuery();
        Root from = criteriaQuery.from(SimpleBean.class);

        Expression minExpression = criteriaBuilder.min(from.get("pint"));
        CriteriaQuery<Object> select = criteriaQuery.select(minExpression);

        TypedQuery<Object> typedQuery = entityManager.createQuery(select);
        Object minExpected = typedQuery.getSingleResult();
        assertEquals(minActual, minExpected);

Query with aggreation (group by)

        Query query = entityManager.createQuery("select min(s.pint),s.pbyte from SimpleBean s group by s.pbyte");

        List listExpected = query.getResultList();

        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<Object> criteriaQuery = criteriaBuilder.createQuery();
        Root from = criteriaQuery.from(SimpleBean.class);

        Expression minExpression = criteriaBuilder.min(from.get("pint"));
        Path pbytePath = from.get("pbyte");
        CriteriaQuery<Object> select = criteriaQuery.multiselect(minExpression, pbytePath);

        CriteriaQuery<Object> groupBy = select.groupBy(pbytePath);

        TypedQuery<Object> typedQuery = entityManager.createQuery(select);
        List listActual = typedQuery.getResultList();
  • Trackback are closed
  • Comments (13)
    • Werner
    • September 25th, 2010

    In your example criteria with single criteria should it not been

    select.where(predicate) instead of criteriaQuery.where(predicate);

    Nice article btw,

    Cheers,

    Werner

  1. Nice article. If you are searching for a more compact way to write JPA queries, then consider using Querydsl : http://source.mysema.com/display/querydsl/Querydsl

    • SebastianB
    • October 26th, 2010

    I’m totally agree with Timo, querydsl is a very powerfull framework =)

  2. 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

    • tiw
    • November 17th, 2010

    Thanks for the example Altuure, following you on my RSS reader now, looking forward to more tutorials and examples from you.

    • Jørgen
    • May 6th, 2011

    Great article! Finally a nice way to make order dynamically!

    • Marcelo
    • June 8th, 2011

    Great article! congratulations help me to much! Thanks

  3. Excellent Post. Thank you so much. You have saved me a lot of working hours!!
    Regards

    • yvan
    • July 17th, 2011

    Predicate predicate1 = criteriaBuilder.ge(from.get(“pint”), arg1);
    Predicate predicate2 = criteriaBuilder.le(from.get(“pint”), arg2);

    i think this code not working

    • dave
    • August 10th, 2011

    Nice Post..

    BTW do you happen to know how the
    criteriaBuilder.function() works? im currently working on criteria api and i need to use mysql’s group_concat. using the criteriaBuilder.function(“group_concat”, String.class, rootObject.get(“field”)); throws me an NPE when i construct the typedQuery from the criteriaQuery.

    Thanks..

    • this exception is really depends on your provider and version , so a stack trace will be very helpful
      cheers

    • geekByDay
    • September 16th, 2011

    Excellent article! Thanks a lot!

    • FreeX
    • May 30th, 2012

    What about, using MAX funtion having SimpleBean.class and SimpleBeanPK.class

    http://stackoverflow.com/questions/10767741/how-to-wirte-jpql-select-max-with-embedded-id/10768113#10768113

Comment are closed.