Hi All,
currently I'm writing an app using Hibernate 3 against an existing Oracle 10g database. I'm new to Hibernate and databases in general having spent most of my time writing Swing apps and so I'm wondering how I would model this SQL query. Am I going along the right lines of creating a POJO for each table in the query? How would I then map these in the hbm.xml file so that I can execute this query? What would the best way of executing this query be? SQL, HQL or using the Critera interface?

Here's the SQL.

stSql = "SELECT Act.ID AS SwitchBoardID, ActRelationship.sequenceNumber, ActRelationship.OutboundActID, Concept.Concept AS ItemText"
stSql = stSql & " FROM Role LEFT JOIN (((Act LEFT JOIN ActRelationship ON Act.ID = ActRelationship.InboundActID)"
stSql = stSql & " LEFT JOIN Concept ON Act.Code = Concept.ConceptCode)"
stSql = stSql & " RIGHT JOIN Participation ON Act.ID = Participation.ActID) ON Role.ID = Participation.RoleID"
stSql = stSql & " WHERE (((Role.ScoperEntityID)=" & OrgContext & ")"
stSql = stSql & " AND ((Role.PlayerEntityID)=" & UserEntityID & ")"
stSql = stSql & " AND ((ActRelationship.OutboundActID)=" & BtnOption & "));"

Thanks in advance

Angus

well, your post asks about pretty much everything, so first I shall point you to the book at the top of this page :)

for a good place to start, I'd recommend reading up on mapping tables ( http://www.hibernate.org/hib_docs/v3/re ... pping.html ) and start writing your hbm.xml files.

Yes, writing a POJO for each table is a good practice. HQL is a good way to write queries because it is database independent (Criteria is better for dynamic queries, or projections)