如何实现JPQL查询?

huangapple go评论101阅读模式
英文:

How to implement JPQL query?

问题

我在PostgreSQL中有一个有效的查询:

select s.id, s.seat_number as available_seat, s.row_number as available_row, rm.room_name as screening_room
from seats s
join rooms rm on rm.id=s.room_id
left join (
    select r.seat_id from reserved_seats r
    join reservations  res on res.id=r.reservation_id AND res.screening_id = 3 ) res on res.seat_id=s.id
where res.seat_id is null AND s.room_id=3
ORDER BY s.id;

但是我在将其翻译为JPA查询语言时犯了错误。

我可以在JPQL中使用嵌套SELECT吗?

答案是使用原生查询

@Query(value =
            "SELECT s.id seatId, s.seat_number availableSeat, " +
                    "s.row_number availableRow, rm.name screeningRoom \n" +
                    "FROM seats s\n" +
                    "JOIN rooms rm on rm.id=s.room_id\n" +
                    "   LEFT JOIN (\n" +
                    "       SELECT r.seat_id FROM reserved_seats r\n" +
                    "       JOIN reservations res ON res.id=r.reservation_id " +
                    "       AND res.screening_id = :screeningId) res ON res.seat_id=s.id\n" +
                    "WHERE res.seat_id IS NULL AND s.room_id=:roomId AND s.row_number=:rowNumber\n" +
                    "ORDER BY s.id;", nativeQuery = true)
英文:

I have working query in PostgreSQL:

select s.id, s.seat_number as available_seat, s.row_number as available_row, rm.room_name as screening_room
from seats s
         join rooms rm on rm.id=s.room_id
         left join (
    select r.seat_id from reserved_seats r
                              join reservations  res on res.id=r.reservation_id AND res.screening_id = 3 ) res on res.seat_id=s.id
where res.seat_id is null AND s.room_id=3
ORDER BY s.id;

But I make mistakes translating it into the JPA query language.

如何实现JPQL查询?

Can I use nested SELECTs in JPQL?

The answer is to use native query:

@Query(value =
            "SELECT s.id seatId, s.seat_number availableSeat, " +
                    "s.row_number availableRow, rm.name screeningRoom \n" +
                    "FROM seats s\n" +
                    "JOIN rooms rm on rm.id=s.room_id\n" +
                    "   LEFT JOIN (\n" +
                    "       SELECT r.seat_id FROM reserved_seats r\n" +
                    "       JOIN reservations res ON res.id=r.reservation_id " +
                    "       AND res.screening_id = :screeningId) res ON res.seat_id=s.id\n" +
                    "WHERE res.seat_id IS NULL AND s.room_id=:roomId AND s.row_number=:rowNumber\n" +
                    "ORDER BY s.id;", nativeQuery = true)

答案1

得分: 0

@Query(value =
            "SELECT s.id seatId, s.seat_number availableSeat, " +
                    "s.row_number availableRow, rm.name screeningRoom \n" +
                    "FROM seats s\n" +
                    "JOIN rooms rm on rm.id=s.room_id\n" +
                    "   LEFT JOIN (\n" +
                    "       SELECT r.seat_id FROM reserved_seats r\n" +
                    "       JOIN reservations res ON res.id=r.reservation_id " +
                    "       AND res.screening_id = :screeningId) res ON res.seat_id=s.id\n" +
                    "WHERE res.seat_id IS NULL AND s.room_id=:roomId AND s.row_number=:rowNumber\n" +
                    "ORDER BY s.id;", nativeQuery = true)
英文:

The answer is to use native query:

@Query(value =
            "SELECT s.id seatId, s.seat_number availableSeat, " +
                    "s.row_number availableRow, rm.name screeningRoom \n" +
                    "FROM seats s\n" +
                    "JOIN rooms rm on rm.id=s.room_id\n" +
                    "   LEFT JOIN (\n" +
                    "       SELECT r.seat_id FROM reserved_seats r\n" +
                    "       JOIN reservations res ON res.id=r.reservation_id " +
                    "       AND res.screening_id = :screeningId) res ON res.seat_id=s.id\n" +
                    "WHERE res.seat_id IS NULL AND s.room_id=:roomId AND s.row_number=:rowNumber\n" +
                    "ORDER BY s.id;", nativeQuery = true)

huangapple
  • 本文由 发表于 2020年3月15日 22:16:41
  • 转载请务必保留本文链接:https://go.coder-hub.com/60693841.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定