I am getting Invalid Column name error when running my application. Below are attached log and entity and repo class.
When I run Select * query then it works but it does not work when I select certain columns.
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
I caanot use Select * because I need some column names only for my application. Please stick with JPA only.
ERROR LOG:
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"}}
I have entity: ResourceHierarchy but not 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 "]";
}
}
I have ResourceHierarchyRepository like this
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);
}
Do I need to create reorg_view_x entity as well and then fetch records in reorgViewX repository instead of doing them in ResourceHierarchy? But I think there are some other easy solutions.
CodePudding user response：
JPA cannot convert few selected columns to entities directly. Now here you have two options:
If you can write your query in
JPQL, then you can use the constructor to build your object with few selected fields. Below is the minimal example:@Query("SELECT new ResourceHierarchy(fieldOne, fieldTwo) FROM ResourceHierarchy WHERE ... ") public List<ResourceHierarchy> method(String params, ..);If you want to stick with the
native query, then you must change the return type of repository method toList<Map<String, Object>>.