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

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