Hibernate native query : Invalid Column Name Error SQL-17006

Question

package com.abc.def.model;

import javax.persistence.Column;
import javax.persistence.Id;
import javax.persistence.Entity;
import javax.persistence.Embeddable;
import javax.persistence.IdClass;
import java.util.Date;
import java.io.Serializable;



@NamedNativeQuery(name="getMetadata",query="
                  select a.name alias1,a.fullname alias2,
                         b.name alias3,b.age alias4,
                         c.height alias5,c.something alias6,
                         d.otherthing alias7
                  from lame_table_name a,
                       lame_table_name_2 b
                  where a.id = b.id
                     and b.id = c.id 
                     and c.id = d.id 
                     and d.id = :namedparameter
                  order by a.index,b.index
               ",
            resultClass=MetadataModel.class)


  @Entity
  @IdClass(SomeIdClass.class)

  public class MetadataModel{

  @Id @Column("alias1")
  private Type alias1property;

  @Id @Column("alias2")
  private Type2 alias2property;

  @Column("alias3")
  private Type3 alias3property;

  //getters and setters
  }

  @Embeddable
  class SomeIdClass implements Serializable{

  //serialVersionUID line

  @Id @Column("alias1")
  private Type alias1property;

  @Id @Column("alias2")
  private Type2 alias2property;

  //getter and setters
  }

The error is SQL-17006, Invalid Column Name, have been trying out variations of this setup the whole day Should I try putting Column("lame_table_name.name")

I also tried using SqlResultSetMapping (and removed @Column from fields of POJO) (and specifying all the column aliases in the columns attribute of SqlResultSetMapping) (are we supposed to specify the resultsetmapping again when executing the query via the setResultSetMapping method of the SQLQuery interface?)

package com.abc.def.model;

import javax.persistence.Column;
import javax.persistence.Id;
import javax.persistence.Entity;
import javax.persistence.Embeddable;
import javax.persistence.IdClass;
import java.util.Date;
import java.io.Serializable;
//other imports for the SqlResultSetMapping



@NamedNativeQuery(name="getMetadata",query="
                  select a.name alias1,a.fullname alias2,
                         b.name alias3,b.age alias4,
                         c.height alias5,c.something alias6,
                         d.otherthing alias7
                  from lame_table_name a,
                       lame_table_name_2 b
                  where a.id = b.id
                     and b.id = c.id 
                     and c.id = d.id 
                     and d.id = :namedparameter
                  order by a.index,b.index
               ",
            resultSetMapping="metaDataMapping")


@SqlResultSetMapping(name="metaDataMapping",
              entities=@EntityResult(entityClass=MetadataModel.class,
                fields = {@FieldResult(name="alias1Property",column="alias1")
                           //so on
                      }

                 )
            )

  @Entity
  @IdClass(SomeIdClass.class)

  public class MetadataModel{


  private Type alias1property;


  private Type2 alias2property;


  private Type3 alias3property;

  //getters and setters
  }

  //composite class, exactly as above

Answer 1

We should have all the table columns in the Select list for oracle.. If we keep only few columns. Eg, your table Employee has columns FirstName,LastName, EmpId, and if you have query like .

session.createSQLQuery("Select FirstName from Employee");

the above query won't work. it will throw Invalid column error Exception. So better put all the columns in Select clause for Oracle.

Courtesy : one answer Thanks, Rajesh.