Given the really stripped-down essence of two classes


and



and that the relationship table does get filled in with the district-school id mappings,

how would I write the following straight-SQL query
" select aa.name, bb.name FROM districts AS aa
JOIN school_district_relationship AS rr
ON aa.id = rr.id_district
JOIN schools AS bb
ON bb.id = rr.id_school
ORDER BY 1,2
;
"

in corresponding HQL to put in a DAOImpl class?

Follow-on question: what's the best way to make use of the result-set? Could I make an anonymous- or inner-class to match the structure of the returned rows? I seek the wisdom of the group for I'm swimming at the deep end of the pool here.

TIA,

Still-learning Stuart

After RTFMing a bit I can answer the second question: the Query returns a list of Objects. However you can create a class to match your resultset and use this to capture the results.

List<MyCoolReport> results = null;
MyCoolReport result = null;
String sqlcmd;

sqlcmd = " aa.code, aa.fiscalYear, aa.roomId FROM mytable AS aa ";
Query hqlQuery = this.session_a.createQuery( sqlcmd); // HQL uses CLASS names not TABLE names
results = hqlQuery.list()

And as for the 1st question, you can do this
sqlcmd = " aa.name, aa.district.name FROM School AS aa ";

But what if I want to do something more complex, like
SELECT bb.name, count(*) FROM District AS aa
JOIN school_district_relationship AS rr
ON aa.id = rr.id_district
JOIN school as bb
ON bb.id = rr.id_school
GROUP BY 1 ORDER BY 1;