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

2 revs · Accepted Answer · 2017-05-23 12:16:23Z

27

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.

edited May 23, 2017 at 12:16

community wiki

2 revs
Rajesh

Sign up to request clarification or add additional context in comments.

2 Comments

Derrick Over a year ago

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

Rajesh Over a year ago

Welcome Derick .

Dharman · Accepted Answer · 2025-01-17 17:49:18Z

1

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

edited Jan 17 at 17:49

Dharman♦

34k27 gold badges106 silver badges157 bronze badges

answered Jan 7 at 5:03

J.Zhang

111 bronze badge

Comments

Rasmus Franke · Accepted Answer · 2012-10-12 14:35:19Z

0

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

answered Oct 12, 2012 at 14:35

Rasmus Franke

4,5348 gold badges48 silver badges64 bronze badges

1 Comment

cee_el Over a year ago

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

cee_el · Accepted Answer · 2012-10-14 07:11:00Z

0

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

answered Oct 14, 2012 at 7:11

cee_el

751 gold badge1 silver badge6 bronze badges

Comments

henrykodev · Accepted Answer · 2022-01-19 00:02:01Z

0

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.

answered Jan 19, 2022 at 0:02

henrykodev

3,1045 gold badges31 silver badges41 bronze badges

Comments

Vikcen · Accepted Answer · 2024-08-14 12:15:28Z

0

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.

answered Aug 14, 2024 at 12:15

Vikcen

1832 silver badges9 bronze badges

Collectives™ on Stack Overflow

Hibernate native query : Invalid Column Name Error SQL-17006

6 Answers 6

2 Comments

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

2 Comments

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related