aspose file tools*
The moose likes Object Relational Mapping and the fly likes How to do cross table queries? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "How to do cross table queries?" Watch "How to do cross table queries?" New topic
Author

How to do cross table queries?

Mike London
Ranch Hand

Joined: Jul 12, 2002
Posts: 1034
I have a students table, a courses table, and a StudentCourses table for all the courses the student is taking.

A regular SQL statement like this works:



But how to do this query in Hibernate?

I can persist to the studentcourse (M:M) reslover table, but how do I get a listing like I can above in SQL?

My HQL looks like this:



With the associated Java code:


However, when running this class I get the following Exception:



So, I'm a bit confused how you do cross table queries?

Any suggestions would be appreciated.

Thanks,

mike
Emanuel Kadziela
Ranch Hand

Joined: Mar 24, 2005
Posts: 186
If you look at a typical HQL query, i.e. (I copied this straight from the hibernate.org online docs)


you see that the query actually follows Java entity associations, NOT the relational table associations. That's how you have to structure the queries. Since you didn't list your java entity classes in your question, I can't tell how exactly this can be done in your example, but hopefully you get the idea.

The other thing to consider is using a native SQL query and helping hibernate translate that into the resulting java objects.
Mike London
Ranch Hand

Joined: Jul 12, 2002
Posts: 1034
Emanuel Kadziela wrote:If you look at a typical HQL query, i.e. (I copied this straight from the hibernate.org online docs)


you see that the query actually follows Java entity associations, NOT the relational table associations. That's how you have to structure the queries. Since you didn't list your java entity classes in your question, I can't tell how exactly this can be done in your example, but hopefully you get the idea.

The other thing to consider is using a native SQL query and helping hibernate translate that into the resulting java objects.


Yeah, I saw that too on the Hibernate site.

If you look at my previous posting from today, I've listed all the classes, database layout, etc.

In particular, how would you list all Students and their associated classes when the entity class for the M:M Resolver table (Studentcourse) looks like this:



Here's the query using the JPA fields in the classes:



This error occurs it looks like in the Studentcourse code below (see bold). I'm not totally sure...

Exception in thread "main" java.lang.IllegalArgumentException: org.hibernate.QueryException: could not resolve property: COURSE_ID of: models.Studentcourse [Select s.studentName, c.courseName, c.courseId FROM models.Course as c, models.Student as s, models.Studentcourse as sc WHERE c.courseId = sc.[b]COURSE_ID AND s.STUDENT_ID = sc.STUDENT_ID ORDER BY s.STUDENT_NAME ASC, c.COURSE_NAME][/b]

-----

The code that's then failing, calling this query is:



I've tried using List<Studentcourse> ...., but that gives the same error as above.

This is really confusing.

I've been Googling all afternoon, but nothing is clicking yet...

Thanks,

-mike
Emanuel Kadziela
Ranch Hand

Joined: Mar 24, 2005
Posts: 186
I do not see any java entity classes (Student.java or Course.java) in your question. So far I see you posted Studentcourse.java.

From the error it appears you are trying to access a field called STUDENT_NAME of the class models.Student, but hibernate complains that it is not defined. Is it defined, with that exact spelling and capitalization in your java class?
Mike London
Ranch Hand

Joined: Jul 12, 2002
Posts: 1034
Emanuel Kadziela wrote:I do not see any java entity classes (Student.java or Course.java) in your question. So far I see you posted Studentcourse.java.

From the error it appears you are trying to access a field called STUDENT_NAME of the class models.Student, but hibernate complains that it is not defined. Is it defined, with that exact spelling and capitalization in your java class?


I fixed that.

Here's the Student class.



How does the "mappedBy" entry get used in the query? That may be the issue. I'm not sure.

I've tweaked the query until the latest update in the last update to the query:



Suggestions?

Thanks!!!

-mike
Emanuel Kadziela
Ranch Hand

Joined: Mar 24, 2005
Posts: 186
I misread the error, sorry, hibernate is complaining about not being able to find the field COURSE_ID in the class models.Studentcourse. And from your posting of the models.Studentcourse class I see that there is no field named COURSE_ID. Once again, I believe you are trying to access the DATABASE columns, and you need to work with the JAVA fields.
Mike London
Ranch Hand

Joined: Jul 12, 2002
Posts: 1034
Emanuel Kadziela wrote:I misread the error, sorry, hibernate is complaining about not being able to find the field COURSE_ID in the class models.Studentcourse. And from your posting of the models.Studentcourse class I see that there is no field named COURSE_ID. Once again, I believe you are trying to access the DATABASE columns, and you need to work with the JAVA fields.


Resolved:




I wasn't sure how to access the other fields in the Studentcourse table's class (since they got created during Entity class reverse engineering), but using a JPA query editor quickly showed me what I needed to do.

Appreciate all the replies.

-- mike
Emanuel Kadziela
Ranch Hand

Joined: Mar 24, 2005
Posts: 186
Nice ! I'm glad you were able to work it out and I could help
Mike London
Ranch Hand

Joined: Jul 12, 2002
Posts: 1034
Yes, I really appreciate it.

The JPA query editor in Intellij 12 really helped a lot. With it's "Intellisense", my query came together in about a minute! Can't believe I waited so long to use that editor.

And, what I saw confirmed what you were saying as well -- about using class fields and not database fields. This was a little confusing since Intellij created these field names itself during entity reverse engineering.

Thanks again.

Cheers!

-mike



[Thumbnail for jpaeditor.png]

 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: How to do cross table queries?
 
Similar Threads
Hibernate HQL and QBE questions
Constraint Violation Question
Returning A List Of Variables From A Folder Of Documents And Returning Them Into A New Document
Problem with update data to database
Enthuware question doubt regarding JPQL