Invalid identifier on a JPA query using Hibernate EntityManager

Question

I've been trying and googling whole day for solution and still have no luck.

I tried to wire up Spring 3.0.5 and JPA 2.0 using hibernate provider. I was testing a simple select query for my entity when I got this error:

DEBUG [JDBCExceptionReporter] could not execute query [select crewiocs0_.EMPNO as EMPNO0_, crewiocs0_.ADDR1 as ADDR2_0_ from VW_CREW_SMS crewiocs0_]
java.sql.SQLException: ORA-00904: "CREWIOCS0_"."ADDR1": invalid identifier

while the SQL shown by Hibernate as this:

DEBUG [QueryTranslatorImpl] HQL: FROM id.co.asyst.sync.model.entity.CrewIOCS
DEBUG [QueryTranslatorImpl] SQL: select crewiocs0_.EMPNO as EMPNO0_, crewiocs0_.ADDR1 as ADDR2_0_ from VW_CREW_SMS crewiocs0_    
DEBUG [SQL] select crewiocs0_.EMPNO as EMPNO0_, crewiocs0_.ADDR1 as ADDR2_0_ from VW_CREW_SMS crewiocs0_

Well, here's my code...

Entity:

@Entity
@Table(name="VW_CREW_SMS")
public class CrewIOCS implements Serializable { //extends Crew { // {
    private static final long serialVersionUID = 1L;
    private String addr1;
    private String employeeNumber;

    @Id
    @Column(name="EMPNO")
    public String getEmployeeNumber() {
        return employeeNumber;
    }

    public void setEmployeeNumber(String employeeNumber) {
        this.employeeNumber = employeeNumber;
    }

    @Column(name="ADDR1")
    public String getAddr1() {
        return this.addr1;
    }

    public void setAddr1(String addr1) {
        this.addr1 = addr1;
    }
}

db.xml (which being imported to springContext.xml):

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:mvc="http://www.springframework.org/schema/mvc"
    xmlns:context="http://www.springframework.org/schema/context"
    xmlns:tx="http://www.springframework.org/schema/tx"
    xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
    http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-3.0.xsd  
    http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsd
    http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd" default-autowire="byName">

    <!-- Scans within the base package of the application for @Components to configure as beans -->
    <bean id="placeholderConfig"
        class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
        <property name="location" value="classpath:db.properties" />
    </bean>

    <bean id="iocsDataSource" class="org.apache.commons.dbcp.BasicDataSource"
        destroy-method="close">
        <property name="driverClassName" value="${db.iocs.driver}" />
        <property name="url" value="${db.iocs.url}" />
        <property name="username" value="${db.iocs.username}" />
        <property name="password" value="${db.iocs.password}" />
    </bean>
    <bean id="iocsEntityManagerFactory"
        class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
        <property name="persistenceUnitName" value="iocs"/>
        <property name="dataSource" ref="iocsDataSource"/>
        <property name="jpaVendorAdapter">
        <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter"> 
               <property name="showSql" value="true" />
               <property name="databasePlatform" value="${db.iocs.dialect}" />
            </bean>
        </property>      
    </bean>

    <bean
    class="org.springframework.orm.jpa.support.PersistenceAnnotationBeanPostProcessor" />
</beans>

DAO beans:

@Repository
public class AbstractIOCSDaoBean<T> implements Dao<T> {

    private static Logger logger = Logger.getLogger(AbstractIOCSDaoBean.class);
    private EntityManager em;
    private Class<T> entityBeanType;

    @PersistenceContext(unitName = "iocs")
    public void setEntityManager(EntityManager em) {
        this.em = em;
    }

    protected EntityManager getEntityManager() {
        if (!(em instanceof EntityManager)) {
            throw new IllegalStateException("Entity Manager has not been set for DAO");
        }
        return em;
    }

    @SuppressWarnings("unchecked")
    public AbstractIOCSDaoBean() {
        this.entityBeanType = (Class<T>) ((ParameterizedType) getClass()
            .getGenericSuperclass()).getActualTypeArguments()[0];
    }

    public Class<T> getEntityBeanType() {
        return entityBeanType;
    }

    @SuppressWarnings("unchecked")
    public List<T> list() {
        logger.debug("on list");
        return em.createQuery("FROM " + getEntityBeanType().getName()).getResultList();
    }
}

@Repository
public class CrewIOCSDaoBean extends AbstractIOCSDaoBean<CrewIOCS> 
    implements CrewIOCSDao {
}

persistence.xml:

<?xml version="1.0" encoding="UTF-8"?>
<persistence xmlns="http://java.sun.com/xml/ns/persistence"
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:schemaLocation="http://java.sun.com/xml/ns/persistence
  http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd"
 version="1.0">
    <persistence-unit name="iocs" transaction-type="RESOURCE_LOCAL"/>
</persistence>         

The entity were successfully bind however:

DEBUG [Configuration] Process annotated classes
INFO  [AnnotationBinder] Binding entity from annotated class: id.co.asyst.sync.model.entity.CrewIOCS
DEBUG [Ejb3Column] Binding column: Ejb3DiscriminatorColumn{logicalColumnName'DTYPE', discriminatorTypeName='string'}
DEBUG [EntityBinder] Import with entity name CrewIOCS
INFO  [EntityBinder] Bind entity id.co.asyst.sync.model.entity.CrewIOCS on table VW_CREW_SMS
DEBUG [Ejb3Column] Binding column: Ejb3Column{table=org.hibernate.mapping.Table(VW_CREW_SMS), mappingColumn=EMPNO, insertable=true, updatable=true, unique=false}
DEBUG [PropertyBinder] binding property employeeNumber with lazy=false
DEBUG [SimpleValueBinder] building SimpleValue for employeeNumber
DEBUG [PropertyBinder] Building property employeeNumber
DEBUG [Ejb3Column] Binding column: Ejb3Column{table=org.hibernate.mapping.Table(VW_CREW_SMS), mappingColumn=ADDR1, insertable=true, updatable=true, unique=false}
DEBUG [PropertyBinder] binding property addr1 with lazy=false
DEBUG [SimpleValueBinder] building SimpleValue for addr1
DEBUG [PropertyBinder] Building property addr1
DEBUG [SimpleValueBinder] Setting SimpleValue typeName for employeeNumber
DEBUG [SimpleValueBinder] Setting SimpleValue typeName for addr1

...

DEBUG [HqlSqlBaseWalker] select << begin [level=1, statement=select]
DEBUG [FromElement] FromClause{level=1} :  id.co.asyst.sync.model.entity.CrewIOCS (no alias) -> crewiocs0_
DEBUG [HqlSqlBaseWalker] select : finishing up [level=1, statement=select]
DEBUG [HqlSqlWalker] processQuery() :  ( SELECT ( FromClause{level=1} VW_CREW_SMS crewiocs0_ ) )
DEBUG [HqlSqlWalker] Derived SELECT clause created.
DEBUG [JoinProcessor] Using FROM fragment [VW_CREW_SMS crewiocs0_]
DEBUG [HqlSqlBaseWalker] select >> end [level=1, statement=select]
DEBUG [AST] --- SQL AST ---
\-[SELECT] QueryNode: 'SELECT'  querySpaces (VW_CREW_SMS)
   +-[SELECT_CLAUSE] SelectClause: '{derived select clause}'
   |  +-[SELECT_EXPR] SelectExpressionImpl: 'crewiocs0_.EMPNO as EMPNO0_'    {FromElement{explicit,not a collection join,not a fetch join,fetch non-lazy properties,classAlias=null,role=null,tableName=VW_CREW_SMS,tableAlias=crewiocs0_,origin=null,columns={,className=id.co.asyst.sync.model.entity.CrewIOCS}}}
   |  \-[SQL_TOKEN] SqlFragment: 'crewiocs0_.ADDR1 as ADDR2_0_'
\-[FROM] FromClause: 'FROM' FromClause{level=1, fromElementCounter=1, fromElements=1, fromElementByClassAlias=[], fromElementByTableAlias=[crewiocs0_], fromElementsByPath=[], collectionJoinFromElementsByPath=[], impliedElements=[]}
      \-[FROM_FRAGMENT] FromElement: 'VW_CREW_SMS crewiocs0_' FromElement{explicit,not a collection join,not a fetch join,fetch non-lazy properties,classAlias=null,role=null,tableName=VW_CREW_SMS,tableAlias=crewiocs0_,origin=null,columns={,className=id.co.asyst.sync.model.entity.CrewIOCS}}
DEBUG [ErrorCounter] throwQueryException() : no errors

EDIT:

Here's a snippet for the oracle view:

CREATE OR REPLACE FORCE VIEW "INTERFACE_IOCS"."VW_CREW_SMS" ("EMPNO", "EMPNO_OLD", "CRTYPE", "FLEET", "CAT", "RANK", "ROSTER", "SEX", "ROSTER_NAME", "GIVEN_NAME", "RELIGION", "CREW_CODE", "BOX_NO", "PAGER_NO", "PASS_NAME", "PASS_NO", "PASS_EXP", "MED_EXP", "BIRTH_DATE", "DOJ_GA", "DOJ_CAT", "ADDR1", "ADDR2", "ADDR3", "POST_CODE", "PHONE_NO", "SMS_NAME")
    AS
    -- long pl/sql goes to here

Any idea what's going wrong? Did I miss something? It ran hibernate-entitymanager-3.6.8 and hibernate-jpa-2.0-api-1.0.1 libs.

Thanks in advance.

Solution

In your entity, the @Column annotation should only be used on your getters (not on your setters as well).

Comments

[Patrick]

@Ifu If this was the correct answer, do you mind selecting it?

[user2601995]

Annotations may also be used on the members.