java - 使用联接表获取数据时在 JPA 中出现错误“无效列”
问题描述
运行我的应用程序时出现 Invalid Column name 错误。下面是附加的日志和实体以及 repo 类。
当我运行 Select * 查询时,它可以工作,但是当我选择某些列时它不起作用。
select * from reorg_view_x x join resource_hierarchy rh on (rh.resource_id=x.faoi_rid)where not exists (select 1 from resource_hierarchy where resource_name =x.t_aoi)and x.region= :region and trunc (x.reorg_dt) = :reorgDt
我不能使用 Select * 因为我只需要一些列名用于我的应用程序。请仅坚持使用 JPA。
错误日志:
Hibernate: select distinct x.t_aoi resourceName, x.faoi_prid parentResourceId, rh.time_zone timeZone, rh.language language, to_date(created_dttm) from reorg_view_x x join resource_hierarchy rh on (rh.resource_id=x.faoi_rid)where not exists (select 1 from resource_hierarchy where resource_name =x.t_aoi)and x.region= ? and trunc (x.reorg_dt) = ?
{"logType":"DEBUG","logLevel":"WARN","logTimestamp":"2021-09-15T12:58:11.696-05:00","logger":"org.hibernate.engine.jdbc.spi.SqlExceptionHelper","label":"SQL Error: 17006, SQLState: 99999","runtime":{"instance":"unknown","clusterName":"unknown","namespace":"unknown","image":"unknown","platformIdentifier":"AJSC7_JERSEY"},"application":{"deploymentUnitName":"com.att.dmp.ReorgResourceCreatorMs","motsApplicationAcronym":"MOTS_ID"}}
{"logType":"DEBUG","logLevel":"ERROR","logTimestamp":"2021-09-15T12:58:11.697-05:00","logger":"org.hibernate.engine.jdbc.spi.SqlExceptionHelper","label":"Invalid column name","runtime":{"instance":"unknown","clusterName":"unknown","namespace":"unknown","image":"unknown","platformIdentifier":"AJSC7_JERSEY"},"application":{"deploymentUnitName":"com.att.dmp.ReorgResourceCreatorMs","motsApplicationAcronym":"MOTS_ID"}}
{"logType":"DEBUG","logLevel":"ERROR","logTimestamp":"2021-09-15T12:58:11.773-05:00","logger":"com.att.dmp.service.RestServiceImpl","label":"Exception in createAoi service:","runtime":{"instance":"unknown","clusterName":"unknown","namespace":"unknown","image":"unknown","platformIdentifier":"AJSC7_JERSEY"},"application":{"deploymentUnitName":"com.att.dmp.ReorgResourceCreatorMs","motsApplicationAcronym":"MOTS_ID"}}
我有实体:ResourceHierarchy 但没有 reorg_view_x
import java.util.Date;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
@Entity
@Table(name="resource_hierarchy")
public class ResourceHierarchy {
@Id
@Column(name="RESOURCE_ID", nullable = false)
private String resourceID;
@Column(name="RESOURCE_NAME", nullable = false)
private String resourceName;
@Column(name="PARENT_RESOURCE_ID", nullable = false)
private String parentResourceID;
@Column(name="RESOURCE_TYPE", nullable = false)
private String resourceType;
@Column(name="TIME_ZONE", nullable = false)
private String timeZone;
@Column(name="LANGUAGE", nullable = false)
private String language;
@Column(name="EMAIL_ADDRESS")
private String emailAddress;
@Column(name="ORGANIZATION")
private String organization;
@Column(name="CREATED_BY")
private String createdBy;
@Column(name="CREATED_DTTM", nullable = false)
private Date createdDttm;
@Column(name="LAST_UPDATED_BY")
private String lastUpdatedBy;
@Column(name="LAST_UPDATED_DTTM")
private Date lastUpdatedDttm;
@Column(name="ACTIVE_FLAG")
private String activeFlag;
@Column(name="CAPACITY_TYPE")
private String capacityType;
@Column(name="DWA_FLAG")
private String dwaFlag;
public String getResourceID() {
return resourceID;
}
public void setResourceID(String resourceID) {
this.resourceID = resourceID;
}
public String getResourceName() {
return resourceName;
}
public void setResourceName(String resourceName) {
this.resourceName = resourceName;
}
public String getParentResourceID() {
return parentResourceID;
}
public void setParentResourceID(String parentResourceID) {
this.parentResourceID = parentResourceID;
}
public String getResourceType() {
return resourceType;
}
public void setResourceType(String resourceType) {
this.resourceType = resourceType;
}
public String getTimeZone() {
return timeZone;
}
public void setTimeZone(String timeZone) {
this.timeZone = timeZone;
}
public String getLanguage() {
return language;
}
public void setLanguage(String language) {
this.language = language;
}
public String getEmailAddress() {
return emailAddress;
}
public void setEmailAddress(String emailAddress) {
this.emailAddress = emailAddress;
}
public String getOrganization() {
return organization;
}
public void setOrganization(String organization) {
this.organization = organization;
}
public String getCreatedBy() {
return createdBy;
}
public void setCreatedBy(String createdBy) {
this.createdBy = createdBy;
}
public Date getCreatedDttm() {
return createdDttm;
}
public void setCreatedDttm(Date createdDttm) {
this.createdDttm = createdDttm;
}
public String getLastUpdatedBy() {
return lastUpdatedBy;
}
public void setLastUpdatedBy(String lastUpdatedBy) {
this.lastUpdatedBy = lastUpdatedBy;
}
public Date getLastUpdatedDttm() {
return lastUpdatedDttm;
}
public void setLastUpdatedDttm(Date lastUpdatedDttm) {
this.lastUpdatedDttm = lastUpdatedDttm;
}
public String getActiveFlag() {
return activeFlag;
}
public void setActiveFlag(String activeFlag) {
this.activeFlag = activeFlag;
}
public String getCapacityType() {
return capacityType;
}
public void setCapacityType(String capacityType) {
this.capacityType = capacityType;
}
public String getDwaFlag() {
return dwaFlag;
}
public void setDwaFlag(String dwaFlag) {
this.dwaFlag = dwaFlag;
}
@Override
public String toString() {
return "ResourceHierarchy [resourceID=" + resourceID + ", resourceName=" + resourceName + ", parentResourceID="
+ parentResourceID + ", resourceType=" + resourceType + ", timeZone=" + timeZone + ", language="
+ language + ", emailAddress=" + emailAddress + ", organization=" + organization + ", createdBy="
+ createdBy + ", createdDttm=" + createdDttm + ", lastUpdatedBy=" + lastUpdatedBy + ", lastUpdatedDttm="
+ lastUpdatedDttm + ", activeFlag=" + activeFlag + ", capacityType=" + capacityType + ", dwaFlag="
+ dwaFlag + "]";
}
}
我有这样的ResourceHierarchyRepository
import java.util.List;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import com.att.dmp.entity.ResourceHierarchy;
@Repository
public interface ResourceHierarchyRepository extends JpaRepository<ResourceHierarchy, String> {
@Query(value="select distinct x.t_aoi resourceName, x.faoi_prid parentResourceId, rh.time_zone timeZone, rh.language language, to_date(created_dttm) from reorg_view_x x join resource_hierarchy rh on (rh.resource_id=x.faoi_rid)where not exists (select 1 from resource_hierarchy where resource_name =x.t_aoi)and x.region= :region and trunc (x.reorg_dt) = :reorgDt", nativeQuery=true)
List<ResourceHierarchy> findAOI( @Param("reorgDt") String reorgDt,@Param("region") String region);
}
我是否还需要创建 reorg_view_x 实体,然后在 reorgViewX 存储库中获取记录,而不是在 ResourceHierarchy 中执行它们?但我认为还有其他一些简单的解决方案。
解决方案
JPA 无法将少数选定的列直接转换为实体。现在在这里你有两个选择:
如果您可以在 中编写查询
JPQL
,那么您可以使用构造函数来构建具有少量选定字段的对象。下面是最小的例子:@Query("SELECT new ResourceHierarchy(fieldOne, fieldTwo) FROM ResourceHierarchy WHERE ... ") public List<ResourceHierarchy> method(String params, ..);
如果要坚持使用
native query
,则必须将存储库方法的返回类型更改为List<Map<String, Object>>
.
推荐阅读
- flutter - WidgetSpan 访问父 TextSpan fontSize
- python - 无法创建文件错误,文件不存在
- mysql - SQL - 如何从过去 5 年中选择日期以包括最早年份的 1 月 1 日
- javascript - 带有自定义字体系列的 Syncfusion JavaScript (ES5) 导出图表
- for-loop - 是否可以在 Kotlin 中编写一个 for 循环,在其中我可以在迭代期间将索引更改为我想要的任何值?
- oracle - “ORA-00925:缺少 INTO 关键字”与 sqlalchemy
- rust - 定义表示不透明 C 结构的 Rust 类型的交叉编译安全方法,其大小在编译时已知
- python - 如何使用列表中的数据在 Kivy 的滚动视图中添加一行?Python
- python - 机器人在用户点击反应后发送消息,当他删除他的反应时(python,discord)
- algorithm - 给定一个语法,如何为不完整的字符串找到有效的 AST?