Hibernate: Unexpected Error while using Named Query

Question

I am getting an error when i am trying to display few contents of a table using named query in hibernate. I have tried looking for answers, with no success. The code is listed below.

<sql-query name="ActiveCustomers">
<return alias="cts" class="Customer"/>
    SELECT 
        cts.cid AS {cts.cid},
        cts.cname AS {cts.cname},
        cts.email AS {cts.email},           
        cts.status {cts.status}
    FROM Customers cts
    WHERE cts.status=:st
</sql-query>

From the client side I am invoking it as shown below:

SessionFactory sf=CHibernateUtil.getSessionFactory();
        Session session=sf.openSession();
        tx=session.beginTransaction();

        list=session.getNamedQuery("ActiveCustomers").setString("st","Active").list();
        for(Customer c:list){
            System.out.println(c);
        }

        tx.commit();
        session.close();

But I am getting this error:

Hibernate:

    SELECT 
        cts.cid AS cid0_,
        cts.cname AS cname0_0_,
        cts.email AS email0_0_,         
        cts.status status0_0_
    FROM Customers cts
    WHERE cts.status=?



org.hibernate.exception.GenericJDBCException: could not execute query
at org.hibernate.exception.ErrorCodeConverter.handledNonSpecificException(ErrorCodeConverter.java:92)
.
.
 Caused by: java.sql.SQLException: Column 'city0_0_' not found.

But If i add all the columns of the table then it works. But that's not my requirement. My requirement is to display only cid,cname,email and status.

User27854 · Accepted Answer · 2014-09-23 10:44:49Z

1

Finally figured it out.

When we are using the below syntax we need to specify the entire class variables and the return type is also an object type or the class type.

<sql-query name="ActiveCustomers">
<return alias="cts" class="Customer"/>
SELECT 
    cts.cid AS {cts.cid},
    cts.cname AS {cts.cname},
    cts.email AS {cts.email},           
    cts.status {cts.status}
FROM Customers cts
WHERE cts.status=:st
</sql-query>

On the other hand when we want to select few or specific columns then we need to use a different tag called

<return scalar ...

here the return type is object array and from that object array we need to print out the values. Now to meet our requirement the above query can be re-written as follows:

Also note the difference in the syntax of select statements.

<sql-query name="ActiveCustomers">
<return-scalar column="cid" type="string"/>
<return-scalar column="cname" type="string"/>
<return-scalar column="email" type="string"/>
<return-scalar column="status" type="string"/>

    SELECT 
        cts.cid AS cid,
        cts.cname AS cname,
        cts.email AS email,         
        cts.status status
    FROM Customers cts
    WHERE cts.status=:st
</sql-query>

The change in the client side is how we read the data ..

 for(Object obj[]:aclist){
            for(Object o:obj){
                System.out.println(o);
            }
        }

edited Sep 23, 2014 at 10:44

answered Sep 23, 2014 at 4:34

User27854

9042 gold badges20 silver badges51 bronze badges

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

2 Comments

Learner Over a year ago

user2900314, I am not clear on what exact change you made o fix the issue, you said we need to use a different tag called what is that different tag? Also you have provided 2 <sql-query> in your answer out of which 1 is matching with your question. Can you please provide more details on how you fixed this issue?

User27854 Over a year ago

@Chaitanya There was some issues in editing.. I have fixed it now, have a look at it now, and let me know if you need any more clarifications. The first <sql-query> is were we had issues and the second one is corrected one.

Sandeep Vaid · Accepted Answer · 2014-09-23 04:40:31Z

0

may be in below you are missing AS ( cts.status AS {cts.status} )

<sql-query name="ActiveCustomers">
<return alias="cts" class="Customer"/>

SELECT
cts.cid AS {cts.cid},
cts.cname AS {cts.cname},
cts.email AS {cts.email},

cts.status {cts.status}
FROM Customers cts
WHERE cts.status=:st </sql-query>

answered Sep 23, 2014 at 4:40

Sandeep Vaid

1,46911 silver badges7 bronze badges

2 Comments

User27854 Over a year ago

No that does not work, the entire query has to be rewritten. Please read my answer that has been listed above.

Sandeep Vaid Over a year ago

Yes. You have to use scalar here as well.

Collectives™ on Stack Overflow

Hibernate: Unexpected Error while using Named Query

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related