Win a copy of Pragmatic AI this week in the Artificial Intelligence forum!
  • 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 all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Jeanne Boyarsky
  • Liutauras Vilda
  • Campbell Ritchie
  • Tim Cooke
  • Bear Bibeault
Sheriffs:
  • Paul Clapham
  • Junilu Lacar
  • Knute Snortum
Saloon Keepers:
  • Ron McLeod
  • Ganesh Patekar
  • Tim Moores
  • Pete Letkeman
  • Stephan van Hulst
Bartenders:
  • Carey Brown
  • Tim Holloway
  • Joe Ess

Join query and hibernate 3  RSS feed

 
Ranch Hand
Posts: 623
Android Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
I use Java 1.8.0_144, Hibernate3.

I wrote small program to fetch combined data from 2 tables.
Query which I use for it, it works in Mysql Workbench. It has 'join' in it.

Its not working in Java program.
I got exception as follows:-


Question is How to solve this problem without migration in Hibernate latest version?
 
Mandar Khire
Ranch Hand
Posts: 623
Android Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
For reference
Following is my program.
Total files:-
Java Files:-
1. test_db.java
2. JDBCConnection.java
3. HibernateUtil.java
4. Utility.java
5. student.java
6. exam_dates.java

hibernate xml files
1. jdbcdetails.xml
2. hibernate1.cfg.xml
3. student.hbm.xml
4. exam_dates.hbm.xml

1. test_db.java


2. JDBCConnection.java


3. HibernateUtil.java


4. Utility.java


5. student.java


6. exam_dates.java


1. jdbcdetails.xmll


2. hibernate1.cfg.xml


3. student.hbm.xml


4. exam_dates.hbm.xml
 
Sheriff
Posts: 5037
138
Chrome Eclipse IDE Java Postgres Database VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
First, let me help you interpret a stack trace (error message) and how to post just the data you need to.  In your stack trace, look for your class names:

|17:57:27,310  INFO MemoryContextFactory:34 - Creating EJB3Unit initial JNDI context
org.hibernate.QueryException: outer or full join must be followed by path expression [select student.roll_no, student.name, exam_dates.subject, exam_dates.date from student student join exam_dates exam_dates on exam_dates.roll_no = student.roll_no where (exam_dates.roll_no, exam_dates.date) in (select roll_no, max(date) from exam_dates where date <= current_date group by roll_no)]
    at org.hibernate.hql.classic.FromParser.token(FromParser.java:170)
    at org.hibernate.hql.classic.ClauseParser.token(ClauseParser.java:86)
    at org.hibernate.hql.classic.PreprocessingParser.token(PreprocessingParser.java:108)
    at org.hibernate.hql.classic.ParserHelper.parse(ParserHelper.java:28)
    at org.hibernate.hql.classic.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:216)
    at org.hibernate.hql.classic.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:185)
    at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:77)
    at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:56)
    at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:72)
    at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:133)
    at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:112)
    at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1623)
    at test_db.get_table_by_join(test_db.java:101)
    at test_db$1.run(test_db.java:40)

    at java.awt.event.InvocationEvent.dispatch(Unknown Source)
    at java.awt.EventQueue.dispatchEventImpl(Unknown Source)
    at java.awt.EventQueue.access$500(Unknown Source)
    at java.awt.EventQueue$3.run(Unknown Source)
    at java.awt.EventQueue$3.run(Unknown Source)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
    at java.awt.EventQueue.dispatchEvent(Unknown Source)
    at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
    at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
    at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
    at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
    at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
    at java.awt.EventDispatchThread.run(Unknown Source)

This tells us that in the class test_db, method get_table_by_join, line 101 is the problem.  So post only that method or that class if it's small.  In this case all you need is:

 
Knute Snortum
Sheriff
Posts: 5037
138
Chrome Eclipse IDE Java Postgres Database VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Now, to reply to your question: MySQL (and any SQL) are very different from Hibernate.  Hibernate deals with objects and has its own query language.  You shouldn't expect a query that works in SQL to work in Hibernate -- in fact it most likely won't.  Do a Google search for hibernate query syntax and you will find many websites to assist you.
 
Mandar Khire
Ranch Hand
Posts: 623
Android Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Knute Snortum,


Now, to reply to your question: MySQL (and any SQL) are very different from Hibernate.  Hibernate deals with objects and has its own query language.  You shouldn't expect a query that works in SQL to work in Hibernate -- in fact it most likely won't.  Do a Google search for hibernate query syntax and you will find many websites to assist you.



I found HQL tutorial.
I tried to use query as


or

It gives me exception as follows:-

When I search related to exception, I found webpage
But not understand how to solve this problem.
Need guidance.
 
Mandar Khire
Ranch Hand
Posts: 623
Android Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I found
Hibernate – One-to-Many example (Annotation) & Hibernate One-to-Many Association on Join Table Annotations Example
As per it I add Annotations in exam_dates.java


& in student.java


But no use.
Where I am doing wrong?
All example I found are for add data in tables, not fetching data from tables.
Need guidance, Help.
 
Mandar Khire
Ranch Hand
Posts: 623
Android Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I also tried
following code in exam_dates.java

or

But getting same exception.
Need guidance,Help.
 
Mandar Khire
Ranch Hand
Posts: 623
Android Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I read Hibernate Reference Documentation 3.6.10.Final,
I found


3.4.2. Outer Join Fetching
If your database supports ANSI, Oracle or Sybase style outer joins, outer join fetching will often
increase performance by limiting the number of round trips to and from the database. This is,
however, at the cost of possibly more work performed by the database itself. Outer join fetching
allows a whole graph of objects connected by many-to-one, one-to-many, many-to-many and oneto-
one associations to be retrieved in a single SQL SELECT.
Outer join fetching can be disabled globally by setting the property hibernate.max_fetch_depth
to 0. A setting of 1 or higher enables outer join fetching for one-to-one and many-to-one
associations that have been mapped with fetch="join".
See Section 21.1, “Fetching strategies” for more information.



In Section 21.1 I found


Join fetching: Hibernate retrieves the associated instance or collection in the same SELECT,
using an OUTER JOIN.


&


<set name="permissions" <br /> fetch="join">
<key column="userId"/>
<one-to-many class="Permission"/>
</set >



<many-to-one name="mother" class="Cat" fetch="join"/>



But not understand how to use it so I can get result same as I get in mysql workbench by using query.
Need Guidance, Help.
 
Bartender
Posts: 19673
92
Android Eclipse IDE Linux
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
First, it looks like you're using old-style Hibernate. For many years now, the recommended use of Hibernate has been Hibernate JPA. Java Persistence Architecture is a fundamental part of the JEE specification and is well-supported by many vendors (not just Hibernate). There are books and videos and support forums galore for JPA.

Traditional Hibernate is now not only no longer a recommended platform for new design and development, it's going to be increasingly a bad platform to do maintenance on, as active support wanes both from Red Hat and from the outside community. And by "bad", I mean that it's going to become exponentially more expensive to get support and the jobs of people working it will become correspondingly more miserable (except for expensive specialized consultants). Now is a good time to migrate to Hibernate JPA, before the cost becomes too expensive.

In fact, I thing you're getting documentation from a mish-mash of sources, since some of the things you're trying look like JPA, but JPA doesn't use a HibernateSessionManager, because in JPA you use an EntityManager.

However, old-style or new, Hibernate is an Object Relational Mapping tool. That means that in particular, you usually don't do join queries in Hibernate. Instead you define object relational mappings and Hibernate generates the SQL internally, including whatever joins it needs. With raw database/JDBC, you query using SQL. With JPA, you use JPQL, which looks a lot like SQL, but isn't, and one of the key differences is in how joins are done.

Ultimately, I think you need to consider migrating to JPA, but even more, you need to thoroughly read a good book on whatever form of Hibernate you may be forced to use.
 
Mandar Khire
Ranch Hand
Posts: 623
Android Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Tim Holloway,
By reading your reply now I understand that which way I should go on learning path.

If you suggest good book/books for learn Hibernate JPA.
So I can migrate my example to Latest, updated Java, Hibernate, Mysql.
I selected 'join' for study purpose, if I get good book then I will study more complex keywords, techniques.

Still for study purpose if I dont have choice, then in Old-style hibernate what is way to solve this query? If any.
If there is way then I can compare how old-style worked & how new style working.

Need guidance.
Thanks,
 
Tim Holloway
Bartender
Posts: 19673
92
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm not up to date on books. I have an old copy of Pro JPA 2 from aPress that has been good for me. It's about JPA, not specifically Hibernate, but JPA is mostly independent of which provider you actually use, whether it's Hibernate, Apache OpenJPA or something else.

I never worked with the original Hibernate, but as far as I know, the join process was not that different from how JPA does it.
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!