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

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       lt - 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       lt - 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

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

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

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

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

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 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       lt - 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       lt - 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

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

[java] Hibernate Query By Example and Projections

Examples related to java

Examples related to hibernate

Examples related to criteria

Examples related to projection