Hi,

when I use Criteria Projections, then I cannot get root entity anymore. E.g.

createCriteria(Employee.class).setProjection(Projections.projectionList().add(Projections.sqlProjection("(SELECT DISTINCT...)", mySQLProj, Hibernate.INTEGER)).add(Restrictions...);

Now, I want the result to be a List<Employee>, or when I use AliasToEntityMapResultTransformer, I'd like to have a map of "Employee" and "mySQLProj". AFAIK to achieve this I can do:
1) add Projections.id() and then fetch Employee entities by id manually (em.find(...)).
2) add Projections to all properties of Employee and then construct the Employee manually (maybe AliasToBeanResultTransformer may help, but id does not matter, the main problem is that I have to add Projections to all properties).
3) making a new entity subclassing Employee with a new column, which is mapped to DB view or using @Formula - I do not find this solution too complicated for such a simple task.

Is there some easier way how to achieve this? I am missing some Projections.rootEntity().
Thanks
Andy
Andy

Hi Andy,

You can try using hql query . It is simple. We can make use of StringBuffer class to store the query and add queries dynamically with the business validations.

Hi shynate26,

I do not want to use HQL, I have a library build upon Criteria, which dynamically creates Restrictions, Order, and paging as the user requests. It would be very difficult to make it in HQL.

Hi Andy,

If it is a library build up , then its left with you how to achieve the querying condition. At present I cant think of how your library would be. It would be answerable if you share how your library is.


Or since your projection involved more than one entity, it is returning the join of 2 entities ? Is this what your concern is ?

-Cheers!

Hi shynate26,

the library and Criteria works fine for all cases but this one. Normally, it returns List<Employee> and I am happy with that. Just in one very special occasion, I need to order the Employees by an SQL subselect. I have successfully added this subselect as the Projections.sqlProjection(...), and I do ordering by NativeSQLOrder, see http://opensource.atlassian.com/projects/hibernate/browse/HHH-2381. So far, all is fine. The list is ordered as I want. (The user can add more order or filter criteria in the UI and it is still working). I just cannot find an easy solution, how to get the Employee object from the Criteria result. Currently, I have implemented the solution 1). It works, but I do not find it optimal.