join and where clause in hibernate

1
votes

I am using join and where clause in hibernate 3.but i cant reach the solution.I got the error.

Query qry= session.createQuery("SELECT addemployee.eid,addemployee.fname,addemployee.location,"
        + "empdet.jtitle,empdet.leadname FROM addemployee LEFT JOIN empdet ON addemployee.eid = empdet.eid WHERE (addemployee.eid ='"+id+"')");

        List l = qry.list();
        Iterator it=l.iterator();

        while(it.hasNext())
        {

            Object rows[] = (Object[])it.next();
            System.out.println(rows[0]+separator+rows[1]+separator+rows[2]+separator+rows[3]+separator+rows[4]);
        }

Issue: org.hibernate.hql.ast.QuerySyntaxException: unexpected token: ON near line 1, column 127 [SELECT addemployee.eid,addemployee.fname,addemployee.location,empdet.jtitle,empdet.leadname FROM addemployee LEFT JOIN empdet ON addemployee.eid = empdet.eid WHERE (addemployee.eid ='206')]

2
javamysqlhibernate
Which DB is it? What happens when you run this query directly on DB using a tool like DBVis or SQL developer? SyntaxException suggests that it is wrongly formatted - Pat
It looks like you're trying to pass SQL to a method that requires HQL. Have you tried with createSQLQuery instead of createQuery? - Dawood ibn Kareem
is your addemployee.eid a String or an Integer? - msagala25
I am using Mysql 5.7 - Kanika
eid-integer and rest of the fields hold varchar. - Kanika

2 Answers

0
votes

Try to use session.createSQLQuery() instead.

and don't put enclosed apostrophe (''), you are inputting numbers, not varchar.

like this.

Query qry= session.createSQLQuery("SELECT addemployee.eid,addemployee.fname,addemployee.location,"
    + "empdet.jtitle,empdet.leadname FROM addemployee LEFT JOIN empdet ON addemployee.eid = empdet.eid WHERE (addemployee.eid ="+id+")");
0
votes

Hibernate Session's createQuery() method requires valid HQL syntax. You can check how to write joins here. Basically, in HQL you work with your entities, not SQL tables. So you don't need to write ON, because you already map association between entities.

If you still want to write native SQL query, you need to use session.createSQLQuery(); instead