• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

How to do cross table queries?

 
Bartender
Posts: 1971
17
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 187
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Bartender
Posts: 1971
17
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 187
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Bartender
Posts: 1971
17
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 187
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Bartender
Posts: 1971
17
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 187
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Nice ! I'm glad you were able to work it out and I could help
 
Mike London
Bartender
Posts: 1971
17
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
jpaeditor.png
[Thumbnail for jpaeditor.png]
JPA Editor in Intelij 12 Ultimate
 
Honk if you love justice! And honk twice for tiny ads!
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic