Newbie here so I hope this makes sense.
I have been refactoring some Hibernate SQL queries out of Java code into named SQL queries into my Hibernate mapping file. I noticed when I ran the exact same SQL that returns a tuple of three objects, I was getting the objects in a different order than when I specified the query in the code and explicitly provided the alias and class arrays to the createSQLQuery method. I even changed the order of the return elements in the XML but it made no difference.
I poked around the Hibernate source code and noticed that when the Binder class loads the mapping in the XML for the <sql-query> element and <return> element, the alias and class names are placed into a HashMap so the order they are declared in is lost. When the stored query is used, an array of keys and values are returned from this map so the order would be whatever they are stored in this Map.
It seems that this order may be critical for knowing what order to expect the tuples when they are retuned from the list() method of the query. I was under the assumption (can't seem to find any documentation on this for SQL queries) that the order following the "select" would be the order of the tuples. But it doesn't seem the case for me.
Can anyone confirm this or point me to some documentation I've overlooked? If this is the case, is there some way I can ensure (predict) the order of the tuples returned in the list() from a named query? I've included snippets of the mapping file and code and this is similar to the example in the Hibernate In Action book on page 284-285 except I have three alias/classes in the query.
In my example, when useNamedQuery is false, I get a StudyBean, ReportBean and PatientBean object in tuples[0], tuple[1] and tuple[2] respectively. When useNamedQuery is true, I get a PatientBean, StudyBean and ReportBean, in that order.
TIA
bill
Hibernate version: 2.1.6
Mapping documents:
...snippet...
Code:
<sql-query name="getStudyListForReferringPhysician"><![CDATA[
select {study.*}, {report.*}, {patient.*}
from StudyLevel as study
left join Reports as report
on study.StuInsUID = report.StudyUID
left join PatientLevel as patient
on study.PatParent = patient.PatID
where study.RefPhyNam = :referringPhysicianName
order by study.StuInsUID
]]>
<return alias="study" class="com.compressus.estation.bean.StudyBean"/>
<return alias="report" class="com.compressus.estation.bean.ReportBean"/>
<return alias="patient" class="com.compressus.estation.bean.PatientBean"/>
</sql-query>
Code between sessionFactory.openSession() and session.close():...snippet...
Code:
boolean useNamedQuery = false;
Query q;
if ( useNamedQuery )
{
q = hibSession.getNamedQuery( "getStudyListForReferringPhysician" );
}
else
{
String sql = "select {study.*}, {report.*}, {patient.*}"
+ " from StudyLevel as study"
+ " left join Reports as report"
+ " on study.StuInsUID = report.StudyUID"
+ " left join PatientLevel as patient"
+ " on study.PatParent = patient.PatID"
+ " where study.RefPhyNam = :referringPhysicianName"
+ " order by study.StuInsUID";
String[] phNames = { "study", "report", "patient" };
Class[] phClasses = { StudyBean.class, ReportBean.class, PatientBean.class };
q = hibSession.createSQLQuery(sql, phNames, phClasses);
}
q.setString("referringPhysicianName", name);
Iterator itTuples = q.list().iterator();
while (itTuples.hasNext())
{
Object[] tuple = (Object[]) itTuples.next();
log.debug( "tuple.length=" + tuple.length );
log.debug( "tuple[0]=" + tuple[0] );
log.debug( "tuple[1]=" + tuple[1] );
log.debug( "tuple[2]=" + tuple[2] );
...
}
Name and version of the database you are using: mySQL v4.0.20a
Login
FAQ
Search
