创建查询时遇到 TypedQuery 问题,涉及两个表。

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

TypedQuery issue while creating query using two tables

问题

我在尝试创建以下TypedQuery时遇到了SemanticException

String findEmptyTableQuery = "select * from dining_table dt " +
             "where "+
             "dt.location = ?1 " +
             "and dt.id not in ( select dining_table_id " +
             "from reserved_slot )";
TypedQuery<DiningTable> findEmptyTable = entityManager.createQuery(
            findEmptyTableQuery, DiningTable.class);

在H2控制台中运行生成的查询正常,但我无法确定可能出现的问题。我猜想在查询字符串中没有正确指定表/列名。请问有谁能提供一些帮助?

异常信息:

org.hibernate.query.SemanticException: A query exception occurred [select * from dining_table dt where dt.location = ?1 and dt.id not in ( select dining_table_id from reserved_slot )]

感谢任何帮助。

编辑:映射关系

@OneToMany(
            mappedBy = "diningTable",
            cascade = CascadeType.ALL,
            orphanRemoval = true
    )
    @JsonIgnoreProperties("reservedSlots")
    private List<ReservedSlot> reservedSlots = new ArrayList<>();

编辑2:添加实体类

@Entity
public class DiningTable {

        @Id
        @Column(name = "ID")
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        private Long id;

        @Convert(converter= TablePositionConverter.class)
        @Column(name="POSITION")
        private TablePosition tablePosition;

        @Max(6)
        private int maxCapacity;

        @Convert(converter= TableLocationConverter.class)
        @Column(name="LOCATION")
        private TableLocation tableLocation;

        @OneToMany(mappedBy = "diningTable", cascade = CascadeType.ALL,
                orphanRemoval = true
        )
        @JsonIgnoreProperties("reservations")
        private List<Reservation> reservations = new ArrayList<>();

        @OneToMany(
                mappedBy = "diningTable",
                cascade = CascadeType.ALL,
                orphanRemoval = true
        )
        @JsonIgnoreProperties("reservedSlots")
        private List<ReservedSlot> reservedSlots = new ArrayList<>();

        @ManyToOne(fetch = FetchType.EAGER)
        private Restaurant restaurant;
}

@Entity
public class ReservedSlot {

    @Id
    @Column(name = "ID")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private int reservedChair;

    private int availableChair;

    private LocalDate reservationDate;

    private LocalDateTime arrivalTime;

    private LocalDateTime departureTime;

    @ManyToOne(fetch = FetchType.EAGER, cascade = CascadeType.PERSIST)
    @JsonIgnoreProperties("diningTable")
    private DiningTable diningTable;

    @OneToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "reservation_id")
    private Reservation reservation;
}
英文:

I am getting SemanticException while trying to create the TypedQuery like below

String findEmptyTableQuery = &quot;select * from dining_table dt &quot; +
&quot;where &quot;+
&quot;dt.location = ?1 &quot; +
&quot;and dt.id not in ( select dining_table_id &quot; +
&quot;from reserved_slot )&quot;;
TypedQuery&lt;DiningTable&gt; findEmptyTable = entityManager.createQuery(
findEmptyTableQuery, DiningTable.class);

While the resulting query runs fine in H2 console, I am unable to figure out what might be the issue here. I guess I am not specifying the table/column name correctly in the query string. Can anyone please shed some light ?

The exception

> org.hibernate.query.SemanticException: A query exception occurred [select * from dining_table dt where dt.location = ?1 and dt.id not in ( select dining_table_id from reserved_slot )]

Any help is appreciated.

Edit: The mapping

@OneToMany(
mappedBy = &quot;diningTable&quot;,
cascade = CascadeType.ALL,
orphanRemoval = true
)
@JsonIgnoreProperties(&quot;reservedSlots&quot;)
private List&lt;ReservedSlot&gt; reservedSlots = new ArrayList&lt;&gt;();

Edit 2: Adding Entities

@Entity
public class DiningTable {
@Id
@Column(name = &quot;ID&quot;)
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Convert(converter= TablePositionConverter.class)
@Column(name=&quot;POSITION&quot;)
private TablePosition tablePosition;
@Max(6)
private int maxCapacity;
@Convert(converter= TableLocationConverter.class)
@Column(name=&quot;LOCATION&quot;)
private TableLocation tableLocation;
@OneToMany(mappedBy = &quot;diningTable&quot;, cascade = CascadeType.ALL,
orphanRemoval = true
)
@JsonIgnoreProperties(&quot;reservations&quot;)
private List&lt;Reservation&gt; reservations = new ArrayList&lt;&gt;();
@OneToMany(
mappedBy = &quot;diningTable&quot;,
cascade = CascadeType.ALL,
orphanRemoval = true
)
@JsonIgnoreProperties(&quot;reservedSlots&quot;)
private List&lt;ReservedSlot&gt; reservedSlots = new ArrayList&lt;&gt;();
@ManyToOne(fetch = FetchType.EAGER)
private Restaurant restaurant;
@Entity
public class ReservedSlot {
@Id
@Column(name = &quot;ID&quot;)
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private int reservedChair;
private int availableChair;
private LocalDate reservationDate;
private LocalDateTime arrivalTime;
private LocalDateTime departureTime;
@ManyToOne(fetch = FetchType.EAGER, cascade = CascadeType.PERSIST)
@JsonIgnoreProperties(&quot;diningTable&quot;)
private DiningTable diningTable;
@OneToOne(fetch = FetchType.LAZY)
@JoinColumn(name = &quot;reservation_id&quot;)
private Reservation reservation;

答案1

得分: 1

String findEmptyTableQuery = "SELECT dt FROM DiningTable dt " +
        "LEFT JOIN dt.reservedSlots rs " +
        "WHERE dt.tableLocation = :location " +
        "AND rs.id IS NULL";

TypedQuery<DiningTable> findEmptyTable = entityManager.
        createQuery(findEmptyTableQuery, DiningTable.class);
// don't forget to set parameter
findEmptyTable.setParameter("location", location);
英文:
    String findEmptyTableQuery = &quot;SELECT dt FROM DiningTable dt &quot; +
&quot;LEFT JOIN dt.reservedSlots rs &quot; +
&quot;WHERE dt.tableLocation = :location &quot; +
&quot;AND rs.id IS NULL&quot;;
TypedQuery&lt;DiningTable&gt; findEmptyTable = entityManager.
createQuery(findEmptyTableQuery, DiningTable.class);
//don&#39;t forget to set parameter
findEmptyTable.setParameter(&quot;location&quot;, location);

huangapple
  • 本文由 发表于 2023年6月11日 20:19:31
  • 转载请务必保留本文链接:https://go.coder-hub.com/76450450.html
匿名

发表评论

匿名网友

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

确定