Hibernate native query with multiple joins on same table returning wrong results

Question

I am using a native sql query where I have a players table to which I join three times, first to get the batsman name, then to get bowler name and then to get the fielder name. Now the first join works, but the next two also return the same name i.e the batsman name.

Here is the sql query

 select 
    del.over_no , 
    del.delivery_no , 
    batsman.sname , 
    outType.name , 
    outBy.sname , 
    fielder.sname , 
    bep.runs, 
    bep.deliveries, 
    bep.fours, 
    bep.sixes

    from delivery del 
    INNER JOIN batsman_performance bep ON del.innings_id=bep.innings_id 
    INNER JOIN ref_player batsman ON del.batsman_id = batsman.id
    INNER JOIN ref_player outBy ON del.bowler_id = outBy.id
    LEFT OUTER JOIN ref_player fielder ON del.fielder_id1= fielder.id
    INNER JOIN ref_out_type outType ON del.out_type_id=outType.id
    and del.out_type_id IS NOT NULL 
    and del.innings_id=:innings_id 
    and bep.player_id = del.batsman_id
    order by over_no, delivery_no;

I am not using aliases for the selected columns because when i did, hibernate threw an exception for whichever column I use an alias

Request processing failed; nested exception is javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query] with root cause java.sql.SQLException: Column 'over_no' not found.

This query is working when I run it on my mysql client and returns the correct dataset but when I run it in my code, the result set somehow overrides the two subsequent joins on ref_player table, leaving me with the batsman name in all three columns, i.e same name in batsman.sname, outBy.sname and fielder.sname columns.

I am stuck here for the last two days, Please any help would be great.

`Query query = em.createNativeQuery(sqlQuery); ((QueryImpl)query).getHibernateQuery().setResultTransformer(Transformers.TO_LIST);` Then after setting the parameters, `List> result =query.getResultList();` — Khizar, Aug 13 '12 at 07:59
Have you tried enabling hibernate sql logging to see what it is converting your code into behind the scenes? If not, add the following to your persistence.xml inside the tags: — skel625, Sep 10 '12 at 05:46
yes i did that ... rather i traced the query tht was being run on the mysql server. It was getting the right result, so something is going wrong with hibernate. But i will still check those again and see if I can find an error there — Khizar, Sep 10 '12 at 09:26

score 5 · Answer 1 · edited Sep 11 '12 at 21:40

Try to wrap your select in another select statement and it should work. I am using stored procedures but it should not make any difference

SELECT * FROM (

SELECT 
    del.over_no , 
    del.delivery_no , 
    batsman.sname , 
    outType.name , 
    outBy.sname , 
    fielder.sname , 
    bep.runs, 
    bep.deliveries, 
    bep.fours, 
    bep.sixes

    from delivery del 
    INNER JOIN batsman_performance bep ON del.innings_id=bep.innings_id 
    INNER JOIN ref_player batsman ON del.batsman_id = batsman.id
    INNER JOIN ref_player outBy ON del.bowler_id = outBy.id
    LEFT OUTER JOIN ref_player fielder ON del.fielder_id1= fielder.id
    INNER JOIN ref_out_type outType ON del.out_type_id=outType.id
    and del.out_type_id IS NOT NULL 
    and del.innings_id=:innings_id 
    and bep.player_id = del.batsman_id
    order by over_no, delivery_no
) AS subselection;

In the above you actually should use aliases otherwise you will have two columns with the same name which will throw an error

Thanx alot man for the reply, i'll test it and post the outcome :) — Khizar, Sep 12 '12 at 04:01

score 2 · Accepted Answer · answered Sep 10 '12 at 09:56

2

its a pending issue on the Hibernate bug tracking.

See this ticket

also in hibernate forums. This clearly shows this is bug a hibernates end.

See Hibernate Forum Discussion

answered Sep 10 '12 at 09:56

Zahid Riaz

2,879
3
27
38

score 0 · Answer 3 · answered Aug 15 '12 at 11:10

0

did you try changing

 order by over_no, delivery_no;

to

 order by del.over_no, del.delivery_no;

answered Aug 15 '12 at 11:10

Mihriban Minaz

3,043
2
32
52

thanks for informing. as u see, i dont have much reputation :) – Mihriban Minaz Aug 17 '12 at 06:58

score 0 · Answer 4 · answered Jun 12 '13 at 09:16

Discovered the same issue. Another workaround is to use org.hibernate.Session#doWork and perform the query on the JDBC connection:

entityManager.unwrap(Session.class).doWork(new Work() {
  void execute(Connection c) throws SQLException {
   PreparedStatement stmt = c.prepareStatement("SELECT ... JOIN ... JOIN ...");
   try {
     ...
   } finally {
     stmt.close() 
   }

Hibernate native query with multiple joins on same table returning wrong results

4 Answers4