Retrieve data as custom object Hibernate Criteria

Question

The task is to retrieve data from the database for certain list of columns and return as custom already existing class.

I tried to resolve this task with following code:

public List<EntityOne> getServiceProviders(EntityTwo EntityTwo) {
        Criteria criteria = createCriteria(EntityOne.class);
        criteria.add(Restrictions.eq("EntityTwo", EntityTwo));
        criteria.createAlias("spid", "two");
        criteria.addOrder(Order.asc("two.entityName"));

        criteria.setProjection(Projections.projectionList()
                .add(Projections.property("entityId"), "entityId")
                .add(Projections.property("publishStatus"), "publishStatus")
                .add(Projections.property("two.entityName"), "two.entityName")
                .add(Projections.property("two.entityId"), "two.entityId")
        );

        return criteria.list();
    }

But I receive a list of data that is not grouped in class as I wanted.

Answer 1

Your question is not very clear especially where you are attempting to use Restrictions.eq("EntityTwo", EntityTwo) as this will not give proper results. Hibernate however provides a means to return EntityOne as an Object from the Columns selected using Hibernate Transformers class. In your case, you will need to write a custom class with getter setter of the columns you are returning. Note that is is important that the variables be named exactly like the alias columns.

Since your example is not clear, let me illustrate with a simple example: Say I need all purchase OrderAmount grouped by OrderDate and OrderNumber

 public static List<YourCustomEntity> getAggregateOrders(){
    Session session = HibernateUtil.getSessionFactory().openSession();
    Transaction tx = null;
    List<YourCustomEntity> list =  new ArrayList<YourCustomEntity>();
    try {
        tx = session.beginTransaction();
        Criteria cr = session.createCriteria(PurchaseOrders.class);
        cr.setProjection(Projections.projectionList()
                .add(Projections.sum("orderAmount").as("sumOrderAmount"))
                .add(Projections.groupProperty("orderNumber").as("agOrderNumber"))
                .add(Projections.groupProperty("orderDate").as("agOrderDate")));
        cr.setResultTransformer(Transformers.aliasToBean(YourCustomEntity.class));
        list = (List<YourCustomEntity>) cr.list();
   }catch (Exception asd) {
        System.out.println(asd.getMessage());
        if (tx != null) {
            tx.rollback();
        }
    } finally {
        session.close();
    }
     return list;
}

In this regard, you will need your customEntity to have the three columns returned above. For example:

public class YourCustomEntity {

    private double sumOrderAmount;
    private String agOrderNumber;
    private Date agOrderDate;
   //And then the getters and setters

NB: Notice the naming of the variables is the same as the column aliases.