Continuous Development Blog

Append Method handler

Another dynamic query builder. But this one , use simple StringBuilder instead of trying to parse the argument.  So more fast and adjustable dynamic query builder

For more see test cases and example dao

   @YMethod(type = YMethodType.APPEND,select = "pbyte,count(id)",groupBy = "pbyte")
    List<SimpleBean> append2(@YParameter("pint>=?") int i);

    @YMethod(type = YMethodType.APPEND,select = "pbyte,count(id)",groupBy = "pbyte",having = "count(id)>10")
    List<SimpleBean> append3(@YParameter("pint>=?") int i);

group and having support with aggregation support

Append and JPA criteria methods now support Projections

Execute Method handler

bulk method support

   @YMethod(type = YMethodType.EXECUTE,query = "update SimpleBean set pint=:newInt where id=:id ")
    int execute1(@YParameter(value = "newInt") int newInt,@YParameter(value = "id") long id);

experimental criteria query parser for JPA

Criteria method is now support more sophisticated queries . please see test cases and example dao

    @YMethod(type = YMethodType.CRITERIA)
    SearchResultList<SimpleBean> criteria2(@YParameter("pint >=") Integer arg1,@YParameter("pint <=") Integer arg2);

    @YMethod(type = YMethodType.CRITERIA)
    SearchResultList<SimpleBean> criteria3(@YParameter("pstring is null") boolean apply);

    @YMethod(type = YMethodType.CRITERIA)
    SearchResultList<Object> criteria2CheckApplied(@YParameter("pint>=20000") boolean apply1,@YParameter( "pint<=50000") boolean apply2);

quick code snippet to get the identifier field name for a given entity class from both hibernate and JPA 2.0 APIs.

for hibernate

public String getIdProperty(Class entityClass) {
String idProperty=sessionAccessor.getSessionFactory()
                                              .getClassMetadata(entityClass)
                                              .getIdentifierPropertyName();
return idProperty;
}

for JPA 2.0 metamodel API this method or to get value of id field see comment below

public String getIdProperty(Class entityClass) {
        String idProperty;
        Metamodel metamodel = getEntityManager().getMetamodel();
        EntityType entity = metamodel.entity(entityClass);
        Set<SingularAttribute> singularAttributes = entity.getSingularAttributes();
        for (SingularAttribute singularAttribute : singularAttributes) {
            if (singularAttribute.isId()){
                idProperty=singularAttribute.getName();
                break;
            }
        }
        if(idProperty==null)
            throw new RuntimeException("id field not found");
        return idProperty;
    }

to

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();

It is very common way of usage that  when ever you need some new attribute for your model objects/table you add a new column to the related database table.  After several requirements you will notice: Read more