Hi -
This is such a trivial operation and I have blown most of the day trying to map it into HBM at my rookie+ skill level.

In HBM3 + MySQL, I am trying to join 2 tables which have no association declared in HBM, via a session.createSQLQuery(), like this:


which generates this SQL



The manual gives hope http://docs.jboss.org/hibernate/stable/core/reference/en/html/querysql.html#d0e13696 that this can be done, and indeed if I remove the join it works fine. However in its current syntax I cannot get away from the error:


From other queries I have ascertained that 'formSect1_15_1_' is the primary key column on the joined table. If I include that column in the projection then HBM cannot understand why it is there.

I very much do not want to create an association between these 2 entities. Strangely I have not seen a similar case in the HBM manual, Bauer/King's book, and in forums. I'm sure it's out there somewhere but I have not found it. Any suggestions on getting this query to work would be greatly appreciated -

Thanks -
Kent

Hello Kent,
I'm not sure If I can answer to your post but I ran into a similar situation before..
It appears to me that you only need instances of UserResponse. When you do q.addEntity("fs", FormSection.class); Hibernate tries to return you instances of FormSection too. And it appears to me FormSection instance is trying to load some other objects?
Anyways Just a thought have you tried this? And what does hibernate complain about?:-
StringBuilder sql = new StringBuilder(256);
sql.append("select {ur.*} from UserResponse ur ");
sql.append("join FormSection fs on ur.sectionId = fs.formSectionId ");
sql.append("where ur.userFormId = :formId ");
sql.append("and fs.sequence = :seqId ");

SQLQuery q = session.createSQLQuery(sql.toString());
q.addEntity("ur", UserResponse.class);
//with out an entity or a join(which you cannot do)
//q.addEntity("fs", FormSection.class);

q.setLong("formId", userFormId);
q.setLong("seqId", sequenceNum);

return q.list();