Hibernate Query By Example and Projections

Question

To make it short: hibernate doesn't support projections and query by example? I found this post:

The code is this:

User usr = new User();
usr.setCity = 'TEST';
getCurrentSession().createCriteria(User.class)
.setProjection( Projections.distinct( Projections.projectionList()
.add( Projections.property("name"), "name")
.add( Projections.property("city"), "city")))
.add( Example.create(usr))

Like the other poster said, The generated sql keeps having a where class refering to just y0_= ? instead of this_.city.

I already tried several approaches, and searched the issue tracker but found nothing about this.

I even tried to use Projection alias and Transformers, but it does not work:

User usr = new User();
usr.setCity = 'TEST';
getCurrentSession().createCriteria(User.class)
.setProjection( Projections.distinct( Projections.projectionList()
.add( Projections.property("name"), "name")
.add( Projections.property("city"), "city")))
.add( Example.create(usr)).setResultTransformer(Transformers.aliasToBean(User.class));

Has anyone used projections and query by example ?

Avion

could you show the full generated sql? – Ecospecies

I added a solution that includes the SQL generated when I was having a similar problem. – Serpasil

Answer 1

Can I see your User class? This is just using restrictions below. I don't see why Restrictions would be really any different than Examples (I think null fields get ignored by default in examples though).

getCurrentSession().createCriteria(User.class)
.setProjection( Projections.distinct( Projections.projectionList()
.add( Projections.property("name"), "name")
.add( Projections.property("city"), "city")))
.add( Restrictions.eq("city", "TEST")))
.setResultTransformer(Transformers.aliasToBean(User.class))
.list();

I've never used the alaistToBean, but I just read about it. You could also just loop over the results..

List<Object> rows = criteria.list();
for(Object r: rows){
  Object[] row = (Object[]) r;
  Type t = ((<Type>) row[0]);
}

If you have to you can manually populate User yourself that way.

Its sort of hard to look into the issue without some more information to diagnose the issue.

Ecospecies

Answer 2

The problem seems to happen when you have an alias the same name as the objects property. Hibernate seems to pick up the alias and use it in the sql. I found this documented here and here, and I believe it to be a bug in Hibernate, although I am not sure that the Hibernate team agrees.

Either way, I have found a simple work around that works in my case. Your mileage may vary. The details are below, I tried to simplify the code for this sample so I apologize for any errors or typo's:

Criteria criteria = session.createCriteria(MyClass.class)
    .setProjection(Projections.projectionList()
        .add(Projections.property("sectionHeader"), "sectionHeader")
        .add(Projections.property("subSectionHeader"), "subSectionHeader")
        .add(Projections.property("sectionNumber"), "sectionNumber"))
    .add(Restrictions.ilike("sectionHeader", sectionHeaderVar)) // <- Problem!
    .setResultTransformer(Transformers.aliasToBean(MyDTO.class));

Would produce this sql:

select
    this_.SECTION_HEADER as y1_,
    this_.SUB_SECTION_HEADER as y2_,
    this_.SECTION_NUMBER as y3_,
from
    MY_TABLE this_ 
where
    ( lower(y1_) like ? ) 

Which was causing an error: java.sql.SQLException: ORA-00904: "Y1_": invalid identifier

But, when I changed my restriction to use "this", like so:

Criteria criteria = session.createCriteria(MyClass.class)
    .setProjection(Projections.projectionList()
        .add(Projections.property("sectionHeader"), "sectionHeader")
        .add(Projections.property("subSectionHeader"), "subSectionHeader")
        .add(Projections.property("sectionNumber"), "sectionNumber"))
    .add(Restrictions.ilike("this.sectionHeader", sectionHeaderVar)) // <- Problem Solved!
    .setResultTransformer(Transformers.aliasToBean(MyDTO.class));

It produced the following sql and my problem was solved.

select
    this_.SECTION_HEADER as y1_,
    this_.SUB_SECTION_HEADER as y2_,
    this_.SECTION_NUMBER as y3_,
from
    MY_TABLE this_ 
where
    ( lower(this_.SECTION_HEADER) like ? ) 

Thats, it! A pretty simple fix to a painful problem. I don't know how this fix would translate to the query by example problem, but it may get you closer.

Serpasil

Paul: I'm glad it helped. I have been surprised that this answer has gone unnoticed for so long as it seems to be a big problem and this solution is not intuitive or documented. – Serpasil

The related Hibernate bug is hibernate.onjira.com/browse/HHH-817 and has been marked as fixed in version 3.6.0Beta4 of Hibernate. – Goliard

@RyanCook Hi! This solution works, but I was wondering if there was a workaround with Examples, as we can have as many as 10 filters (i.e 10 Restrictions) and we'd have to verify for each if the field is null or empty). – Bullnose

I have kill 2 days in this issue. this keyword solve this very well. Thanks a lot @Ryan – Autography

I have another issue with Order.asc("sectionHeader"). When I use same property in Projection, Restriction & Order too. !! – Autography

Answer 3

The real problem here is that there is a bug in hibernate where it uses select-list aliases in the where-clause:

http://opensource.atlassian.com/projects/hibernate/browse/HHH-817

Just in case someone lands here looking for answers, go look at the ticket. It took 5 years to fix but in theory it'll be in one of the next releases and then I suspect your issue will go away.

Embree

Answer 4

I'm facing a similar problem. I'm using Query by Example and I want to sort the results by a custom field. In SQL I would do something like:

select pageNo, abs(pageNo - 434) as diff
from relA
where year = 2009
order by diff

It works fine without the order-by-clause. What I got is

Criteria crit = getSession().createCriteria(Entity.class);
crit.add(exampleObject);
ProjectionList pl = Projections.projectionList();
pl.add( Projections.property("id") );
pl.add(Projections.sqlProjection("abs(`pageNo`-"+pageNo+") as diff", new String[] {"diff"}, types ));
crit.setProjection(pl);

But when I add

crit.addOrder(Order.asc("diff"));

I get a org.hibernate.QueryException: could not resolve property: diff exception. Workaround with this does not work either.

PS: as I could not find any elaborate documentation on the use of QBE for Hibernate, all the stuff above is mainly trial-and-error approach

Midrib

Answer 5

ProjectionList pl = Projections.projectionList();
pl.add(Projections.property("id"));
pl.add(Projections.sqlProjection("abs(`pageNo`-" + pageNo + ") as diff", new String[] {"diff"}, types ), diff); ---- solution
crit.addOrder(Order.asc("diff"));
crit.setProjection(pl);

Agile

add aliias after sqlProjection , that will be used in Ordering – Agile

Answer 6

I do not really think so, what I can find is the word "this." causes the hibernate not to include any restrictions in its query, which means it got all the records lists. About the hibernate bug that was reported, I can see it's reported as fixed but I totally failed to download the Patch.

Reiss