Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp
I am trying to perform a case-insensitive LIKE query against the "name" property of a joined subclass. The property actually exists in the parent class. It works fine for non-joined subclasses (including the parent class of the joined subclass).
Quote:
Expression.sql("lower({alias}.name) like lower(?)", nameStart+"%", Hibernate.STRING)
Hibernate version: 3.0
Mapping documents:
Code:
<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class name="com.xmltravel.fab.core.domain.location.Location" table="location">
<id name="id" column="locationId" unsaved-value="null">
<generator class="native"/>
</id>
<natural-id>
<property name="code"/>
</natural-id>
<property name="name" unique="true"/>
<component name="geoLocation">
<property name="latitude"/>
<property name="longitude"/>
<property name="altitude"/>
</component>
<joined-subclass name="com.xmltravel.fab.core.domain.location.Airport" table="airport">
<key column="locationId"/>
<many-to-one name="associatedCity" column="associatedCityId"/>
<set name="associatedResorts" table="resort_airport" lazy="true">
<key column="resortId"/>
<many-to-many class="com.xmltravel.fab.core.domain.location.Resort" />
</set>
</joined-subclass>
<joined-subclass name="com.xmltravel.fab.core.domain.location.City" table="city">
<key column="locationId"/>
<set name="airports" lazy="true" inverse="true">
<key column="associatedCityId"/>
<one-to-many class="com.xmltravel.fab.core.domain.location.Airport" />
</set>
</joined-subclass>
</class>
<class name="com.xmltravel.fab.core.domain.location.Resort" table="resort">
<id name="id" column="resortId" unsaved-value="null">
<generator class="native"/>
</id>
<natural-id>
<property name="name"/>
</natural-id>
<property name="shortName"/>
<component name="geoLocation">
<property name="latitude"/>
<property name="longitude"/>
<property name="altitude"/>
</component>
<many-to-one name="parent" column="parentResortId"/>
<many-to-one name="country" column="countryId"/>
<set name="children" lazy="true" inverse="true">
<key column="parentResortId"/>
<one-to-many class="com.xmltravel.fab.core.domain.location.Resort" />
</set>
<set name="feederAirports" table="resort_airport" lazy="true" inverse="true">
<key column="locationId"/>
<many-to-many class="com.xmltravel.fab.core.domain.location.Airport" />
</set>
<set name="aliases" table="resort_alias" lazy="true">
<key column="resortId"/>
<element type="string" column="alias" not-null="true"/>
</set>
</class>
<class name="com.xmltravel.fab.core.domain.location.Country" table="country">
<id name="id" column="countryId" unsaved-value="null">
<generator class="native"/>
</id>
<natural-id>
<property name="code"/>
</natural-id>
<property name="name" unique="true"/>
<many-to-one name="resort" column="resortId"/>
</class>
</hibernate-mapping>
Code:
/**
* Perform a case insensitive like query on the name property
* @param partialName
* @return
*/
protected List findByNameStartsWith(final String nameStart)
{
if ( nameStart == null || nameStart.length() < 2 ) throw new FABUncheckedException ("must be at least 2 characters!");
return (List) getHibernateTemplate().executeFind(new HibernateCallback()
{
public Object doInHibernate(Session session) throws HibernateException, SQLException
{
return session.createCriteria(getPersistentClass())
.add( Expression.sql("lower({alias}.name) like lower(?)", nameStart+"%", Hibernate.STRING) )
.addOrder( Order.asc("name") )
.list();
}
});
}
Full stack trace of any exception that occurs:
org.springframework.jdbc.BadSqlGrammarException: Bad SQL grammar [] in task 'Hibernate operation'; nested exception is java.sql.SQLException: Column not found: THIS_.NAME in statement [select this_.locationId as locationId0_, this_1_.code as code0_0_, this_1_.name as name0_0_, this_1_.latitude as latitude0_0_, this_1_.longitude as longitude0_0_, this_1_.altitude as altitude0_0_, this_.associatedCityId as associat2_1_0_ from airport this_ inner join location this_1_ on this_.locationId=this_1_.locationId where lower(this_.name) like lower(?) order by this_1_.name asc]
java.sql.SQLException: Column not found: THIS_.NAME in statement [select this_.locationId as locationId0_, this_1_.code as code0_0_, this_1_.name as name0_0_, this_1_.latitude as latitude0_0_, this_1_.longitude as longitude0_0_, this_1_.altitude as altitude0_0_, this_.associatedCityId as associat2_1_0_ from airport this_ inner join location this_1_ on this_.locationId=this_1_.locationId where lower(this_.name) like lower(?) order by this_1_.name asc]
at org.hsqldb.jdbc.jdbcUtil.throwError(Unknown Source)
at org.hsqldb.jdbc.jdbcPreparedStatement.<init>(Unknown Source)
at org.hsqldb.jdbc.jdbcConnection.prepareStatement(Unknown Source)
at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:396)
at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:334)
at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:88)
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1162)
at org.hibernate.loader.Loader.doQuery(Loader.java:390)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:218)
at org.hibernate.loader.Loader.doList(Loader.java:1593)
at org.hibernate.loader.Loader.list(Loader.java:1577)
at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:111)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1322)
at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:300)
at com.xmltravel.fab.core.dao.impl.hibernate.BaseDaoImpl$4.doInHibernate(BaseDaoImpl.java:215)
at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:310)
at org.springframework.orm.hibernate3.HibernateTemplate.executeFind(HibernateTemplate.java:291)
at com.xmltravel.fab.core.dao.impl.hibernate.BaseDaoImpl.findByNameStartsWith(BaseDaoImpl.java:208)
at com.xmltravel.fab.core.dao.impl.hibernate.BaseDaoImpl.findByCodeOrNameStart(BaseDaoImpl.java:240)
at com.xmltravel.fab.core.dao.impl.hibernate.location.AirportDaoImpl.findMatchingAirports(AirportDaoImpl.java:106)
at com.xmltravel.fab.core.dao.impl.hibernate.location.LocationDaoTestCase.testAll(LocationDaoTestCase.java:95)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at junit.framework.TestCase.runTest(TestCase.java:154)
at junit.framework.TestCase.runBare(TestCase.java:127)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:118)
at junit.framework.TestSuite.runTest(TestSuite.java:208)
at junit.framework.TestSuite.run(TestSuite.java:203)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:478)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:344)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)
Name and version of the database you are using: HSQLDB
The generated SQL (show_sql=true):
Works ok with base class (Location):
Hibernate: select location0_.locationId as locationId, location0_.code as code0_, location0_.name as name0_, location0_.latitude as latitude0_, location0_.longitude as longitude0_, location0_.altitude as altitude0_, location0_1_.associatedCityId as associat2_1_, case when location0_1_.locationId is not null then 1 when location0_2_.locationId is not null then 2 when location0_.locationId is not null then 0 end as clazz_ from location location0_ left outer join airport location0_1_ on location0_.locationId=location0_1_.locationId left outer join city location0_2_ on location0_.locationId=location0_2_.locationId where location0_.code=?
Hibernate: select this_.locationId as locationId0_, this_.code as code0_0_, this_.name as name0_0_, this_.latitude as latitude0_0_, this_.longitude as longitude0_0_, this_.altitude as altitude0_0_, this_1_.associatedCityId as associat2_1_0_, case when this_1_.locationId is not null then 1 when this_2_.locationId is not null then 2 when this_.locationId is not null then 0 end as clazz_0_ from location this_ left outer join airport this_1_ on this_.locationId=this_1_.locationId left outer join city this_2_ on this_.locationId=this_2_.locationId where lower(this_.name) like lower(?) order by this_.name asc
Doesn't work when same thing is done for joined subclass (Airport):
Hibernate: select airport0_.locationId as locationId, airport0_1_.code as code0_, airport0_1_.name as name0_, airport0_1_.latitude as latitude0_, airport0_1_.longitude as longitude0_, airport0_1_.altitude as altitude0_, airport0_.associatedCityId as associat2_1_ from airport airport0_ inner join location airport0_1_ on airport0_.locationId=airport0_1_.locationId where airport0_1_.code=?
Hibernate: select this_.locationId as locationId0_, this_1_.code as code0_0_, this_1_.name as name0_0_, this_1_.latitude as latitude0_0_, this_1_.longitude as longitude0_0_, this_1_.altitude as altitude0_0_, this_.associatedCityId as associat2_1_0_ from airport this_ inner join location this_1_ on this_.locationId=this_1_.locationId where lower(this_.name) like lower(?) order by this_1_.name asc
- SQL Error: -28, SQLState: S0022
- Column not found: THIS_.NAME in statement [select this_.locationId as locationId0_, this_1_.code as code0_0_, this_1_.name as name0_0_, this_1_.latitude as latitude0_0_, this_1_.longitude as longitude0_0_, this_1_.altitude as altitude0_0_, this_.associatedCityId as associat2_1_0_ from airport this_ inner join location this_1_ on this_.locationId=this_1_.locationId where lower(this_.name) like lower(?) order by this_1_.name asc]
- SQL Error: -28, SQLState: S0022
- Column not found: THIS_.NAME in statement [select this_.locationId as locationId0_, this_1_.code as code0_0_, this_1_.name as name0_0_, this_1_.latitude as latitude0_0_, this_1_.longitude as longitude0_0_, this_1_.altitude as altitude0_0_, this_.associatedCityId as associat2_1_0_ from airport this_ inner join location this_1_ on this_.locationId=this_1_.locationId where lower(this_.name) like lower(?) order by this_1_.name asc]
Basically it looks like the alias is expected to be this_.name but in fact it is this_1_.name.
Am I doing something wrong or is this a bug?
Cheers,
Adam.
Login
FAQ
Search