Hibernate is chugging along well for my other tables but something about this simple query is stuffing it in the translation from HQL to SQL.

A particular column in SQL called family_name iis mapped to familyName.

When I execute a HQL query as such:

from au.com.iman.claims.common.ClaimsPayment where familyName >= '' order by familyName

The SQL generated is:

select claimspaym0_.id as id, claimspaym0_.member_id as member_id, claimspaym0_.policy_id as policy_id, claimspaym0_.entry_date as entry_date, claimspaym0_.family_name as family_n5_, claimspaym0_.amount as amount, claimspaym0_.coverage as coverage from claims_payments claimspaym0_ where (familyName>='' ) order by familyName limit ?

Notice that it has successfully translated family_name for the select fields but not for the Order By or Where criteria???

Odd bug.




Hibernate version:
2.1.3
Mapping documents:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd" >

<hibernate-mapping
package="au.com.iman.claims.common">
<!--
Created by the Middlegen Hibernate plugin

http://boss.bekk.no/boss/middlegen/
http://hibernate.sourceforge.net/
-->

<class
name="ClaimsPayment"
table="claims_payments"
>

<id
name="id"
type="int"
column="id"
>
<generator class="assigned" />
</id>

<property
name="memberId"
type="int"
column="member_id"
length="4"
/>
<property
name="policyId"
type="int"
column="policy_id"
length="4"
/>
<property
name="entryDate"
type="java.sql.Date"
column="entry_date"
length="4"
/>
<property
name="familyName"
type="java.lang.String"
column="family_name"
length="80"
/>
<property
name="amount"
type="java.math.BigDecimal"
column="amount"
length="-1"
/>
<property
name="coverage"
type="java.lang.String"
column="coverage"
length="6"
/>

<!-- associations -->
<!-- bi-directional one-to-many association to Cheques -->
<set
name="cheques"
lazy="true"
inverse="true"
>
<key>
<column name="claims_payment_id" />
</key>
<one-to-many
class="Cheque"
/>
</set>
<!-- bi-directional one-to-many association to Claim -->
<set
name="claims"
lazy="true"
inverse="true"
>
<key>
<column name="claims_payment_id" />
</key>
<one-to-many
class="Claim"
/>
</set>
<!-- bi-directional one-to-many association to OverrideNot -->
<set
name="overrideNotes"
lazy="true"
inverse="true"
>
<key>
<column name="claims_payment_id" />
</key>
<one-to-many
class="OverrideNote"
/>
</set>

</class>
</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():

tx = hibernateSession.beginTransaction();
// Calculate max results needed.
int maxResults = queryEvent.getQueryStart().intValue()
+ queryEvent.getQueryReturnSize() - 1;
logger.info("max rows:" + maxResults);

String securityWhere = " ";

// Work out security where based on the user id's access rights.
// eg " AND customerCode = "FRED01"


String newQuery = " from " + event.getEventSubType() // ? queryEvent field?
+ " where " + queryEvent.getQueryWhere()
+ securityWhere + " order by "
+ queryEvent.getQueryOrderBy();


int queryStart = queryEvent.getQueryStart().intValue() -1 ;
int queryEnd = maxResults;
List rowsetList = hibernateSession.createQuery(
newQuery).setMaxResults(maxResults).setFetchSize(maxResults).setFirstResult(queryStart).list();

/*
* Now store the rowset result in the queryevent for returning..
*/


queryEvent.setQueryRowSet(rowsetList);

tx.commit();

Full stack trace of any exception that occurs:



Name and version of the database you are using:
Postgresql
The generated SQL (show_sql=true):
select claimspaym0_.id as id, claimspaym0_.member_id as member_id, claimspaym0_.policy_id as policy_id, claimspaym0_.entry_date as entry_date, claimspaym0_.family_name as family_n5_, claimspaym0_.amount as amount, claimspaym0_.coverage as coverage from claims_payments claimspaym0_ where (familyName>='' ) order by familyName limit ?
Debug level Hibernate log excerpt:

17:11:22,536 DEBUG [QueryTranslator] HQL: from au.com.iman.claims.common.ClaimsPayment where familyName >= '' order by familyName
17:11:22,537 DEBUG [QueryTranslator] SQL: select claimspaym0_.id as id, claimspaym0_.member_id as member_id, claimspaym0_.policy_id as policy_id, claimspaym0_.entry_date as entry_date, claimspaym0_.family_name as family_n5_, claimspaym0_.amount as amount, claimspaym0_.coverage as coverage from claims_payments claimspaym0_ where (familyName>='' ) order by familyName
17:11:22,538 DEBUG [BatcherImpl] about to open: 0 open PreparedStatements, 0 open ResultSets
17:11:22,539 DEBUG [SQL] select claimspaym0_.id as id, claimspaym0_.member_id as member_id, claimspaym0_.policy_id as policy_id, claimspaym0_.entry_date as entry_date, claimspaym0_.family_name as family_n5_, claimspaym0_.amount as amount, claimspaym0_.coverage as coverage from claims_payments claimspaym0_ where (familyName>='' ) order by familyName limit ?
17:11:22,540 INFO [STDOUT] Hibernate: select claimspaym0_.id as id, claimspaym0_.member_id as member_id, claimspaym0_.policy_id as policy_id, claimspaym0_.entry_date as entry_date, claimspaym0_.family_name as family_n5_, claimspaym0_.amount as amount, claimspaym0_.coverage as coverage from claims_payments claimspaym0_ where (familyName>='' ) order by familyName limit ?
17:11:22,541 DEBUG [BatcherImpl] preparing statement
17:11:22,719 DEBUG [JDBCExceptionReporter] SQL Exception
org.postgresql.util.PSQLException: ERROR: column "familyname" does not exist

at org.postgresql.util.PSQLException.parseServerError(PSQLException.java:139)
at org.postgresql.core.QueryExecutor.executeV3(QueryExecutor.java:154)

Thanks Myk

Funny thing is that this type of HQL works for a bunch of other tables.

Anyways, I'll implement that and also upgrade my version of hibernate to 2.1.6