Hibernate urgent query with multiple joins on the same table returning incorrect results

Question

I use my own SQL query, in which I have a table of players that I join three times, first to get the name of the batsman, then to get the name of the bowler, and then to get the name of the finder. Now the first connection works, but the next two also return the same name ie the name batsman.

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 do not use aliases for the selected columns, because when I did this, hibernate threw an exception for each column in which I use an alias

Error processing request; The nested exception is javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: query cannot be completed] with the main reason java.sql.SQLException: Column 'over_no' not found.

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

I’ve been stuck here the last two days, please, any help would be great.

Answer 1

his pending issue is tracking down Hibernate errors.

See this ticket

also in hibernation forums. This clearly shows that this is a sleep error.

See the Hibernate forum discussion

Answer 2

Try wrapping your selection in another select statement, and it should work. I use stored procedures, but this 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 must use aliases, otherwise you will have two columns with the same name that will cause an error

Answer 3

You tried to change

  order by over_no, delivery_no; 

to

  order by del.over_no, del.delivery_no; 

Answer 4

The same problem was detected. Another workaround is to use org.hibernate.Session#doWork and execute the request in a 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() }