Hibernate本机查询在同一个表上有多个连接返回错误的结果

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.

我正在使用本机sql查询,其中我有一个玩家表,我加入了三次,首先得到击球手的名字,然后获得投球手的名字,然后获得外野手的名字。现在第一次加入工作,但接下来的两个也返回相同的名称,即击球手的名字。

Here is the sql query

这是sql查询

 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

我没有为所选列使用别名,因为当我这样做时,hibernate为我使用别名的任何列引发了异常

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.

请求处理失败;嵌套异常是javax.persistence.PersistenceException:org.hibernate.exception.SQLGrammarException:无法执行查询]的根本原因java.sql.SQLException:找不到列'over_no'。

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.

当我在我的mysql客户端上运行它并返回正确的数据集时,此查询正在工作,但是当我在我的代码中运行它时,结果集会以某种方式覆盖ref_player表上的两个后续连接,让我在所有三列中都有batsman名称,即batsman.sname,outBy.sname和fielder.sname列中的相同名称。

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

我最近两天都被困在这里,请帮助一下。

4 个解决方案

#1

its a pending issue on the Hibernate bug tracking.

它是关于Hibernate错误跟踪的待决问题。

See this ticket

看到这张票

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

也在hibernate论坛。这清楚地表明这是一个冬眠结束的错误。

See Hibernate Forum Discussion

请参阅Hibernate论坛讨论

#2

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包装在另一个select语句中,它应该可以工作。我正在使用存储过程,但它不应该有任何区别

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

在上面你实际应该使用别名,否则你将有两个具有相同名称的列将引发错误

#3

did you try changing

你尝试过改变吗?

 order by over_no, delivery_no;

 order by del.over_no, del.delivery_no;

#4

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

发现了同样的问题。另一种解决方法是使用org.hibernate.Session #doWork并对JDBC连接执行查询:

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

#1