Continuous Development Blog

Hi everybody, this was my surprise project , achivy.com my first startup, my first ruby on rails project

you can check the  about page  but it is simply a gamification platform for lots of social sites and more, it now supports foursquare, gowalla, stackoverflow etc….

Achivy adds some fun and gamification to your social life. Add Twitter,Tumblr, Foursquare Gowalla , Flickr, Stackoverflow, Klout and many other applications to your profile to see how many achievements, badges,scores you have and follow your progress. You can keep up with your friends, and see the other people’s achievements and activities – achivies.

more to come soon…

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