• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Hibernate HQL and QBE questions

 
Jane Somerfield
Ranch Hand
Posts: 193
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have two tables: Student and Class. They have a many-to-many relationship through a
join table (Student_Class). I'm using Oracle 10g. The table structures are listed below using SQLPLUS:

SQL>desc Student
IDNumber
STUDENT_NAMEVarchar2(50)

SQL>desc Class
IDNumber
CLASS_NAMEVarchar2(50)

SQL> desc Student_Class
IDNumber
STUDENT_IDNubmer
CLASS_IDNumber


The IDs are the PKs and the Student_ID and Class_ID are the FKs.


I can find the classes that Mary is taking with following query:

SQL>select cl.CLASS_NAME from Class cl, Student st, Student_Class sc where st.ID=sc.STUDENT_ID and cl.ID=cs.CLASS_ID and st.STUDENT_NAME like 'Mary%'

CLASS_NAME
------------
Math
English
Physics

I can find the classes that Mike is taking with following query

SQL>select cl.CLASS_NAME from Class cl, Student st, Student_Class sc where st.ID=sc.STUDENT_ID and cl.ID=cs.CLASS_ID and st.STUDENT_NAME like 'Mike%'

CLASS_NAME
------------
Math
English
Chemistry


I can find the classes that Mary or Mike are taking with following

SQL>select distinct cl.CLASS_NAME from Class cl, Student st, Student_Class sc where st.ID=sc.STUDENT_ID and cl.ID=cs.CLASS_ID and (st.STUDENT_NAME like 'Mike%' or st.STUDENT_NAME like 'Mary%')

CLASS_NAME
------------
Math
English
Chemistry
Physics


Can anyone help me to wirte a Hibernate HQL or QBE to do above queries (especially the last one)? Thanks.
 
Mark Spritzler
ranger
Sheriff
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
HQL is just like SQL, except you use Class names and attributes names instead of table and field names. So we can't really post an HQL query for you because we don't know your class and attribute names that you have mapped to those tables. But besides the "distinct" in that last query, your HQL will basically looks the exact same, except change the table name to you class name and your fields to the class' attribute.

Mark
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic