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.