How to use Join in Java program with Hibernate?

Question

I got Query for fetch two tables data combined

I am trying to use it in Java program which work with Hibernate3. When Ever i try to fetch data, I got exception. My complete code is in Question in Coderanch, 1st reply have complete java, xml files..

Here I my query which I used in java program

String selectQuery = "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";

and another for trial

  String selectQuery = "select student.roll_no, student.name, exam_dates.subject, exam_dates.date "
            + "from student student "
            + "INNER JOIN exam_dates exam_dates on exam_dates.roll_no = student.roll_no";

By using it,I get exception as follows:-

14:19:07,752  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 INNER JOIN exam_dates exam_dates on exam_dates.roll_no = student.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:99)
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)

Can Anyone help me?

Note that there is no point in giving your exam_dates table an exam_dates alias. — Arnaud
– Arnaud, Commented Jun 11, 2018 at 8:51
Just a tip: you may to use @Entity for your classes and define that join on that level using annotations. It helps to refuse a lot of similary errors then. — severus256
– severus256, Commented Jun 11, 2018 at 8:53
Should I change something in student.java or exam_dates.java or xml files for use 'Join' or 'Inner Join' in java program? — Mandar Khire
– Mandar Khire, Commented Jun 11, 2018 at 8:55
I added @Entity, Column etc in student.java & exam_dates.java, but confuse how 'Join' or 'Inner join' use by that way? — Mandar Khire
– Mandar Khire, Commented Jun 11, 2018 at 9:10

saurabh kedia · Accepted Answer · 2018-06-11 10:20:47Z

1

Create your entity class as

@Entity
public class Student{
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long rollNo;
    private String name;
    @OneToMany
    private ExamDates examDates;

    public void setRollNo(Long rollNo) {
        this.rollNo = rollNo;
    }

    public void getRollNo() {
        return this.rollNo;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getExamDates() {
        return this.examDates;
    }

    public void setExamDates(ExamDates examDates) {
        this.examDates = examDates;
    }
}

where ExamDates is another class mapping to your exam_dates table. When you will call

Student s = entityManager.find(Student.class, rollNo);

it will give you student object and then call s.getExamDates which will give you the exam dates

answered Jun 11, 2018 at 10:20

saurabh kedia

3312 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Mandar Khire Over a year ago

As per above instruction, is there need to use query for fetching data from two tables? my query is 'select s.roll_no, s.name, ed.subject, ed.date from student s join exam_dates ed on ed.roll_no = s.roll_no where (ed.roll_no, ed.date) in ( select roll_no, max(date) from exam_dates where date <= current_date group by roll_no );'

Mandar Khire Over a year ago

above query I used in query browser of mysql & get expected result. Now I am trying to fetch same result in java program.

saurabh kedia Over a year ago

@Mandar Khire: Before I answer that can I know whether you have used hibernate before or not as this requires complex entities and use of Criterion classes, so if you are not aware of all those then I recommend you to please go through all that once.

Mandar Khire Over a year ago

I used hibernate for individual tables, not for complex queries.

saurabh kedia Over a year ago

In such case use the query itself but remember that HQL is different from SQL and you need to use property name not the column name. Using session.createQuery, you need to provide to this method a HQL query, and not a SQL query. You can use session.createSQLQuery with a SQL query, or write your query as a HQL query, something like: select cv.field from table1 alias1 JOIN alias1.x alias2, where x is your association element in the first class. Hope this helps!

|

Collectives™ on Stack Overflow

How to use Join in Java program with Hibernate?

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related