Hibernate issue - Invalid column name

Question

I have added two columns in the sql to get the values through hibernate.My databse is oracle and those fields datatype i number. So i have created the beans with long and (tried Integer too) but when retrieving the values(executing the valuesquery).

Its giving me an error

org.hibernate.type.LongType - could not read column value from result set
java.sql.SQLException: Invalid column name
    at oracle.jdbc.driver.OracleStatement.getColumnIndex(OracleStatement.java:3711)
    at oracle.jdbc.driver.OracleResultSetImpl.findColumn(OracleResultSetImpl.java:2806)
    at oracle.jdbc.driver.OracleResultSet.getLong(OracleResultSet.java:444)
    at weblogic.jdbc.wrapper.ResultSet_oracle_jdbc_driver_OracleResultSetImpl.getLong(Unknown Source)
    at org.hibernate.type.LongType.get(LongType.java:28)
    at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:163)
    at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:189)

tABLE DEFINITION :

    CREATE TABLE "PRODUCTLIST"
(
   PRICELIST_PUBLISH_KEY decimal(22) NOT NULL,
   PRODUCT_NBR varchar2(54) NOT NULL,
   PRODUCT_KEY decimal(22),
   PRODUCT_DESCRIPTION varchar2(360),
   PRODUCT_FAMILY_NBR varchar2(30),
   PRODUCT_FAMILY_DESCR varchar2(180),
   PRODUCT_GROUP_NBR varchar2(30),
   PRODUCT_GROUP_DESCR varchar2(180),
   PRODUCT_LINE_NBR varchar2(30),
   PRODUCT_LINE_DESCR varchar2(180),
   PRODUCT_CLASS_CODE varchar2(6),
   LAST_PP_GENERATED_DATE_KEY decimal(22),
   LAST_PP_GENERATED_DATE date,
   PUBLISH_PERIOD_KEY decimal(22) NOT NULL,
   PUBLISH_PERIOD_DATE date,
   PL_KEY decimal(22),
   PRODUCTLIST varchar2(750),
   SALES_KEY decimal(22),
   PRODUCT varchar2(60),
   DM_EXTRACTED_BY_USER varchar2(90)
)

sql :

        Query query =  session.createSQLQuery(channelQuery)
                                   .addScalar("PRODUCT",Hibernate.STRING)
                                 .addScalar("PRODUCTLIST",Hibernate.STRING)
                                 .addScalar("PRODUCTKEY",Hibernate.LONG)
                                 .addScalar("SALESKEY",Hibernate.LONG)
                           .setResultTransformer(Transformers.aliasToBean(SearchResult.class));          
               return query.list();
             }
  });

Please help me to fix the issue ?

Please show us the table definition (CREATE TABLE ...) and the actual query you are running. — user330315
– user330315, Commented Jun 24, 2013 at 22:44
SELECT distinct p.product, p.productprice, p.PL_KEY, p.SALES_KEY FROM productlist pub WHERE p.productnbr in ('1002102') Which is running in toad , but while ruuning through hibernate giving me an error invalid column name. — user2505078
– user2505078, Commented Jun 24, 2013 at 22:54
Particularly after added fields "p.PL_KEY, p.SALES_KEY" its getting error.before there was no error. These two fields datatype is number in oracle. — user2505078
– user2505078, Commented Jun 24, 2013 at 22:56

eternay · Accepted Answer · 2013-06-25 19:36:11Z

In your table definition, I can't see all the fields you're using in the addScalar() methods: there are no PRODUCTKEY nor SALESKEY fields. Instead I can see a PRODUCT_KEY and a SALES_KEY fields (underscores). I think you should use the correct name of the fields in the addScalar() methods.

But if your query is the one you put in your comments, you have to correct some details:

you should use p instead of pub as alias for the table name. As there is only one table in the query, you can suppress the alias.
In your SELECT clause, p.productprice is not an existing field in your table. Maybe you want to use p.pricelist instead.
In your WHERE clause, p.productnbr is not an existing field in your table. You should use p.product_nbr instead.
Then you should change the field names in the addScalar() methods to match those you are using in the query.

Modified query

SELECT distinct p.product, p.productlist, p.PL_KEY, p.SALES_KEY
FROM productlist p
WHERE p.product_nbr in ('1002102')

Your code should be:

Query query =  session.createSQLQuery(channelQuery)
    .addScalar("PRODUCT",Hibernate.STRING)
    .addScalar("PRODUCTLIST",Hibernate.STRING)
    .addScalar("PL_KEY",Hibernate.LONG)
    .addScalar("SALES_KEY",Hibernate.LONG)
    .setResultTransformer(Transformers.aliasToBean(SearchResult.class));          
return query.list();

If you define aliases in your query, then you can use the alias names instead of the field names. For example, with this query:

SELECT distinct p.product, p.productlist, p.PL_KEY as PRODUCTKEY, p.SALES_KEY as SALESKEY
FROM productlist p
WHERE p.product_nbr in ('1002102')

you can use the following code (it's your original code):

Query query =  session.createSQLQuery(channelQuery)
    .addScalar("PRODUCT",Hibernate.STRING)
    .addScalar("PRODUCTLIST",Hibernate.STRING)
    .addScalar("PRODUCTKEY",Hibernate.LONG)
    .addScalar("SALESKEY",Hibernate.LONG)
    .setResultTransformer(Transformers.aliasToBean(SearchResult.class));          
return query.list();

Collectives™ on Stack Overflow

Hibernate issue - Invalid column name

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related