JPQL子查询示例抛出错误关系“table_name”不存在，Spring Boot

Question

我试图在Spring Boot的一个查询示例JPQL中实现一个子查询，但出现了错误

我的实体表结构如下：

package com.ericsson.smart.meter.smartmeter.model;
import java.util.Date;
import java.util.List;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
import javax.persistence.Transient;
import lombok.AllArgsConstructor;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;
import lombok.ToString;

@NoArgsConstructor
@AllArgsConstructor
@ToString
@Getter
@Setter
@Entity
@Table(name = "\"P_CC_CASE\"", schema = "public")

public class Cases {

@Id
@Column(name = "\"CASE_ID\"")
private long caseId;

@Column(name = "\"CASE_FOCUS\"")
private String caseFocus;

@Column(name = "\"CASE_TYPE\"")
private String caseType;

@Column(name = "\"CASE_CATEGORY\"")
private String caseCategory;

@Column(name = "\"CASE_RPT_CATEGORY\"")
private String caseRptCategory;

@Column(name = "\"CASE_STATUS\"")
private String caseStatus;

@Column(name = "\"CASE_CUST_SEVERITY\"")
private String caseCustSeverity;

@Column(name = "\"CASE_EON_SEVERITY\"")
private String caseeonSeverity;

@Column(name = "\"CASE_PRIORITY\"")
private String casePriority;

@Column(name = "\"CASE_ISSUE\"")
private String caseIssue;

@Column(name = "\"CREATED_ON\"")
private Date createdOn;

@Column(name = "\"EXPECTED_RESPONSE_DATE\"")
private Date expectedResponseDate;

@Column(name = "\"LAST_CONTACT_DATE\"")
private Date lastContactDate;

@Column(name = "\"FIRST_RESPONSE_TO_CUST\"")
private Date firstResponseToCust;

@Column(name = "\"CONTACT_DATE\"")
private Date contactDate;

@Column(name = "\"CREATED_BY\"")
private String createdBy;

@Column(name = "\"ORGANIZATION_ID\"")
private String organizationId;

@Column(name = "\"QUEUE\"")
private String queue;

@Column(name = "\"CHANGED_BY\"")
private String changedBy;

@Column(name = "\"EQUIPMENT_ID\"")
private String equipmentId;

@Column(name = "\"EVENT_ID\"")
private String eventId;

@Column(name = "\"CASE_TITLE\"")
private String caseTitle;

@Column(name = "\"CASE_DESC\"")
private String caseDesc;

@Column(name = "\"SUPPORT_KEY\"")
private String supportKey;

@Column(name = "\"OBJEVENTS\"")
private String objevents;

@Column(name = "\"POD\"")
private String pod;

@Column(name = "\"CHNAGED_DATE\"")
private Date chnagedDate;

}

我尝试实现的查询如下：

SELECT T1.* FROM (SELECT * FROM "P_CC_CASE" WHERE "CASE_TYPE" IN ('GAS','HEAT','ELECTRICITY') OR "CASE_TYPE" IS NULL) T1;

@Query(value = GET_CASES_BY_PARAMS, nativeQuery = true)
List<Cases> searchCases(@Param("caseCategories") List<String> caseCategories);

我无法理解内部查询也返回一组类型Case，整个查询也是如此，但是当一起编写时会出现错误。有人能帮忙吗，我如何实现这个？

提前谢谢。

Answer 1

SELECT T1.* FROM (SELECT * FROM "P_CC_CASE" WHERE "CASE_TYPE" IN ('GAS','HEAT','ELECTRICITY') OR "CASE_TYPE" IS NULL) T1;

请将@Column和@Entity的value属性更改为@Column（“Column”）和@Entity（“P\u CC\u CASE”）

表名为“P\u CC\u CASE”，具有双配额。您需要在@Entity annotation处编写P\u CC\u大小写

从“P\u CC\u CASE”中选择*表示表名为字符串，因此无法找到它。

package com.ericsson.smart.meter.smartmeter.model;
import java.util.Date;
import java.util.List;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
import javax.persistence.Transient;
import lombok.AllArgsConstructor;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;
import lombok.ToString;

@NoArgsConstructor
@AllArgsConstructor
@ToString
@Getter
@Setter
@Entity
@Table(name = "P_CC_CASE", schema = "public")

public class Cases {

@Id
@Column(name = "CASE_ID")
private long caseId;

@Column(name = "CASE_FOCUS")
private String caseFocus;

...

}

还有另一个问题。如果nativeQuery为true，则不能使用jqpl。

@Query(value = GET_CASES_BY_PARAMS) List<Cases> searchCases(@Param("caseCategories") List<String> caseCategories);