将SQL查询转换为HQL

Question

我需要将此SQL查询转换为HQL

select
        * 
    from
        ( select
            routemaste0_.ROUTE_ID as col_0_0_,
            routemaste0_.ROUTE_CODE as col_1_0_,
            routemaste0_.START_PLACE_ID as col_2_0_,
            routemaste0_.END_PLACE_ID as col_3_0_,
            routemaste0_.IS_ACTIVE as col_4_0_,
            routemaste0_.LINKED_ROUTE as col_5_0_
        from
            OPRS_ROUTE_MASTER routemaste0_
            inner join OPRS_ROUTE_HALTS routehalts0_
                 on routemaste0_.route_id = routehalts0_.route_id
        where routehalts0_.PLACE_ID = '51'
        order by
            routemaste0_.ROUTE_ID ASC  )

我试过这种方式

SELECT    rm.id , 
          rm.routeCode , 
          rm.startPlaceId , 
          rm.endPlaceId , 
          rm.active , 
          rm.linkedRoute 
FROM      RouteMaster rm  
          INNER JOIN  rm.routeHalts AS rh 
WHERE     rm.id = rh.routeId  
          AND  rh.placeId = :PlaceId  
ORDER BY  rm.id  ASC

但是没有得到预期的结果。我担心的是，我需要执行inner join on条件。

谁能帮帮我？

Answer 1

我认为问题的原因可能是route_id不是任何表中的主键。这就是为什么我建议你在这种情况下尝试Theta样式的连接：

SELECT  rm.id , rm.routeCode , rm.startPlaceId , rm.endPlaceId , rm.active, rm.linkedRoute 
FROM RouteMaster rm, RouteHalts rh
WHERE rm.routeId = rh.routeId AND rh.placeId = :PlaceId  
ORDER BY  rm.id  A

在您的HQL查询的变体中，您不需要在where条件中使用rm.id = rh.routeId部分。