[hibernate-issues] [Hibernate-JIRA] Created: (HHH-2167) createSQL query doesnt work

[bansi (JIRA)]

createSQL query doesnt work
---------------------------

         Key: HHH-2167
         URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2167
     Project: Hibernate3
        Type: Improvement

  Components: query-sql  
    Versions: 3.0 final    
 Environment: Oracle 9.2 Database, Solaris OS
    Reporter: bansi
    Priority: Blocker



You will see lots of people like me & my team who would like to leverage the flexibility of SQL & persistence management of Hibernate  . So for user community like us who come with a strong background of SQL experience Hibernate 3 is the way to go even for simple or complex queries. Not sure whether any performance is involved here. But i am able to run even a simple sql query using hibernate3 . 

public List getUser(final String bemsId) throws DataAccessException {
HibernateTemplate ht = new HibernateTemplate(this.sessionFactory);
return (List) ht.execute(new HibernateCallback() {
public Object doInHibernate(Session session) throws HibernateException {
Query query = session.createSQLQuery("select c.last_name, c.first_name from user c where c.userid=?");
((SQLQuery) query).addScalar( "last_name", Hibernate.STRING); 
((SQLQuery) query).addScalar( "first_name", Hibernate.STRING); 
query.setString(0, userId);
return query.list();
}
});

}

 Note : I have  even tried addEntity("user", User.class) but it doesnt work
This is how i retrieve 

 for (Iterator it = user.iterator(); it.hasNext();) {
                     User user = (User) it.next();
                     System.out.println("LastName: " + user.getLastName());
                     System.out.println("FirstName: " + user.getFirstName());
                   }


I get a ClassCast Exception error or Bad Grammer error . 

I am quite suprised to know When i used : addEntity("user", User.class) i got invalid Column error with Bad Grammar


Any pointers/suggestions to write simple/complex sql  queries in Hibernate with examples will be highly appreciated. Pl note we dont want to use HQL 

Regards
Bansi


-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira