Hibernate Query By Example and Projections

Question

Hi all,

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 ?

Answer 1

hmm, setProjection should be working on the Criteria and I don't see how a Projection would alter the example passed.

Have you had trouble using a normal criteria setup (without example)?

ex: Restrictions.eq("city",'TEST')

Answer 2

Yes, I have already tried used regular restrictions. It seems to me that the problems are in the alias, but if I remove the alias i should not be able to use the Transformers.aliasToBean thing to convert the Object[][] that the projected criteria returns to a regular User.

I'm going nuts on this...

Answer 3

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.

Answer 4

Hi, thank you for your time. Right now, I'm using a custom transformer that basically does the object construction from the 'raw' data, kinda like the code you posted.

I managed to use both Example and Projections by using a detached criteria for a subquery, but now the problem is that since I projected over my original class, my fetch joins no longer work.

I think that the projection makes hibernate 'lose' information about the original class, so it ends up doing N+1 selects, where N is for each joined relation (OneToOne). Anyway, that's for another post I guess.

Answer 5

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 very. 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.

Answer 6

Hi all,

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

Answer 7

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.