CODEWITHSUNDEEP
javahibernatejpaorm
cee_el
cee_el

Reputation: 75

Hibernate native query : Invalid Column Name Error SQL-17006

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

Upvotes: 6

Views: 58410

Answers (6)

J.Zhang
J.Zhang

Reputation: 11

Just change the column value to uppercase, such as: @Column(name = "FILEID"), it works fine.

Upvotes: 1

Vikcen
Vikcen

Reputation: 183

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.

Upvotes: 0

henrykodev
henrykodev

Reputation: 3084

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.

Upvotes: 0

Rajesh
Rajesh

Reputation: 1941

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.

Upvotes: 27

cee_el
cee_el

Reputation: 75

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...

Upvotes: 0

Rasmus Franke
Rasmus Franke

Reputation: 4524

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

Upvotes: 0

Related Questions