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

Ballast

Answer 1

Well, earlier I was trying to specify both the columns and entities attributes in the resultsetmapping, so I tried removing the entity mappings, keeping the columns attribute, and calling the aliastobean result transformer, that plus writing setters to accept BigDecimal instead of Long (since its an Oracle DB), solved the issue...

Ballast

Answer 2

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.

Denazify

Thank You. I almost spent whole day resolving this issue and finally got the solution here. – Outface

Welcome Derick . – Denazify

Yeah, you saved my work day =) – Iain

Answer 3

Try @Column(name = "myprop") instead. Also note that Type/Type2/Type3 must be Simple types (Integer/Long/String/Date usually).

Giovannagiovanni

yeah, they are all one of Long,String,Date – Ballast

Answer 4

In my case, my I needed to give an inner-select body a name, as it was mysteriously truncating away a CASE column.

The following did not work - for some reason COLUMN_B gets mysteriously removed:

SELECT
  COLUMN_A,
  CASE WHEN ... THEN ... ELSE ... END as COLUMN_B,
  COLUMN_C
FROM (
   ...
)

Then following fixed the problem:

SELECT
  RES.COLUMN_A,
  CASE WHEN ... THEN ... ELSE ... END as COLUMN_B,
  RES.COLUMN_C
FROM (
   ...
) RES

The whole SQL is only dealing with one table.

Java code:

// The "sql" variable is the SQL statement shown above.
javax.persistence.Query res = myEntityManager.createNativeQuery(sql, SomeClass.class);

🙄

I'm using Spring Boot 2.4.3 with JPA and Oracle 12c.

Forewoman

Answer 5

In my case i had a native query like this:

@Query(value = "SELECT ID, FIELD1, FIELD2 FROM USERS WHERE FIELD2 = ?1", nativeQuery = true)
    Optional<User> findByUser(String field2);

And my table in Oracle had all these fields: ID, FIELD1, FIELD2, FIELD3, FIELD4, and getting that error when I ran it: "Invalid Column Name Error SQL-17006"

Solution: I put all the fields in the query head, like this:

@Query(value = "SELECT ID, FIELD1, FIELD2, FIELD3, FIELD4 FROM USERS WHERE FIELD2 = ?1", nativeQuery = true)
    Optional<User> findByUser(String field2);

The order of the fields in the query head doesn't matter.

Iain