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

User · Answer

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.

User · Answer

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.

User · Answer

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

User · Answer

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 lt Object gt  rows   criteria list    for Object r  rows     Object   row    Object    r    Type t      lt Type gt   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

User · Answer

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

User · Answer

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

User · Answer

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 lt Object gt  rows   criteria list    for Object r  rows     Object   row    Object    r    Type t      lt Type gt   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

User · Answer

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 lt Object gt  rows   criteria list    for Object r  rows     Object   row    Object    r    Type t      lt Type gt   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

User · Answer

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

User · Answer

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

User · Answer

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 lt Object gt  rows   criteria list    for Object r  rows     Object   row    Object    r    Type t      lt Type gt   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

User · Answer

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

User · Answer

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

User · Answer

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

[java] Hibernate Query By Example and Projections

Examples related to java

Examples related to hibernate

Examples related to criteria

Examples related to projection