invalid column name error while using JPA nativequery in netweaver server with Oracle 10g database

Question

I'm getting invalid column name error while using JPA nativequery in netweaver server .The database is Oracle 10g

here is my code

Query query = em.createNativeQuery("select et.eqt_desc from  ge_equip_type et",GeEquipType.class);
final List<String> equipList = query.getResultList();

Following is the exception thrown :

SQLException occurred executing a query, mapped to select et.eqt_desc from ge_equip_type et [EXCEPTION]javax.ejb.EJBException: nested exception is: javax.persistence.PersistenceException: SQLException occurred executing a query, mapped to select et.eqt_desc from ge_equip_type et
..................
..................
Caused by: java.sql.SQLException: Invalid column name at oracle.jdbc.driver.OracleStatement.getColumnIndex(OracleStatement.java:3651) at oracle.jdbc.driver.OracleResultSetImpl.findColumn(OracleResultSetImpl.java:2543) at com.sap.engine.services.dbpool.wrappers.ResultSetWrapper.findColumn(ResultSetWrapper.java:87)
at com.sap.engine.services.orpersistence.query.EntityResultFactory$NamedEntityResult.getColumnNumber(EntityResultFactory.java:239)
at com.sap.engine.services.orpersistence.core.PrimaryKey.createFromEntityResult(PrimaryKey.java:246)
at com.sap.engine.services.orpersistence.core.StoreManager.processEntityRow(StoreManager.java:1631)
at com.sap.engine.services.orpersistence.core.StoreManager.resultSet2ObjectList(StoreManager.java:1602)
at com.sap.engine.services.orpersistence.core.StoreManager.executeQueryWithoutProcessingEntityInfos(StoreManager.java:1120) at com.sap.engine.services.orpersistence.core.StoreManager.executeQuery(StoreManager.java:1141) at com.sap.engine.services.orpersistence.core.PersistenceContextImpl.executeQuery(PersistenceContextImpl.java:937)

GeEquipType class

package com.jnj.e2.cpl.orm;

import java.io.Serializable;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.SequenceGenerator;
import javax.persistence.Table;
import javax.persistence.Version;

@Entity
@Table(name = "GE_EQUIP_TYPE")
@SequenceGenerator(name = "SEQ_EQT", sequenceName = "SEQ_EQT", allocationSize = 1, initialValue = 1)
@SuppressWarnings("serial")
public class GeEquipType implements Serializable {
  @Id
  @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "SEQ_EQT")
  @Column(name = "EQT_ID")
  private Long uid;

  @Column(name = "EQT_CODE")
  private String code;

  @Column(name = "EQT_DESC")
  private String description;

  @Column(name = "EQT_CLEAN_VAL_SPAN_DAYS")
  private Long cleaningValiditySpan;

  @Column(name = "EQT_INDC_PRODUCT_YN")
  private String inDirectContactWithProduct;

  @ManyToOne
  @JoinColumn(name = "EQT_PRT_ID")
  private GePrinterType printerType;

  @Version
  @Column(name = "EQT_OLV")
  private Long version;

  public Long getUid() {
    return uid;
  }

  public void setUid(Long uid) {
    this.uid = uid;
  }

  public String getCode() {
    return code;
  }

  public void setCode(String code) {
    this.code = code;
  }

  public String getDescription() {
    return description;
  }

  public void setDescription(String description) {
    this.description = description;
  }

  public Long getCleaningValiditySpan() {
    return cleaningValiditySpan;
  }

  public void setCleaningValiditySpan(Long cleaningValiditySpan) {
    this.cleaningValiditySpan = cleaningValiditySpan;
  }

  public String getInDirectContactWithProduct() {
    return inDirectContactWithProduct;
  }

  public void setInDirectContactWithProduct(String inDirectContactWithProduct) {
    this.inDirectContactWithProduct = inDirectContactWithProduct;
  }

  public GePrinterType getPrinterType() {
    return printerType;
  }

  public void setPrinterType(GePrinterType printerType) {
    this.printerType = printerType;
  }

  public Long getVersion() {
    return version;
  }

  public void setVersion(Long version) {
    this.version = version;
  }

  @Override
  public int hashCode() {
    return 31 + (uid == null ? 0 : uid.hashCode());
  }

  @Override
  public boolean equals(Object obj) {
    if (this == obj) {
      return true;
    } else if (obj == null || getClass() != obj.getClass()) {
      return false;
    }

    final GeEquipType that = (GeEquipType) obj;

    if (uid == null) {
      if (that.uid != null) {
        return false;
      }
    } else if (!uid.equals(that.uid)) {
      return false;
    }

    return true;
  }
}

Note :Query works when i fetch all columns or primary key column but not String columns.

Anyone please help.

Answer 1

There are few solutions for this issue 1, You can try @Transient to all the other columns in that table. 2, You can try Hql which can return object[] and convert it to the class after getting list of objects. 3, is to use criteria api to get the query.

The 1 option also is where I have an issue with, where it is working with native query but not with hql. if you code has both native queries and hql using that table, that is a problem I don't know the solution for.

Answer 2

Can you try as

String sql = "select et.eqt_desc from  ge_equip_type et";
Query query = em.createNativeQuery(sql, GeEquipType.class);
List<GeEquipType> equipList = (List<GeEquipType>) query.getResultList();

Besides, make sure you have Oracle JDBC JAR files in your project settings.