Hi,

I tested TopLink, I can specify the raw SQL in the tool. Can I do the same thing in Hibernate?

thanks

Yes, in Hibernate 2.1.

Max has implemented createSQLQuery() and also now in CVS the <sql-query/> mapping element.

Hi,

I tried a very simple query using createSQLQuery() but got invalid column name, what can be wrong?

select * from CM_CONTRACT_SHORTCUT
[WARN] JDBCExceptionReporter - -SQL Error: 17006, SQLState: null
[ERROR] JDBCExceptionReporter - -Invalid column name
[WARN] JDBCExceptionReporter - -SQL Error: 17006, SQLState: null
[ERROR] JDBCExceptionReporter - -Invalid column name
[ERROR] JDBCExceptionReporter - -SQLException occurred <java.sql.SQLException: Invalid column name>java.sql.SQLException: Invalid column name
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java)
at oracle.jdbc.driver.OracleStatement.get_column_index(OracleStatement.java)
at oracle.jdbc.driver.OracleResultSetImpl.findColumn(OracleResultSetImpl.java)
at oracle.jdbc.driver.OracleResultSet.getLong(OracleResultSet.java)
at net.sf.hibernate.type.LongType.get(LongType.java:14)
at net.sf.hibernate.type.NullableType.nullSafeGet(NullableType.java:61)
at net.sf.hibernate.type.NullableType.nullSafeGet(NullableType.java:52)
at net.sf.hibernate.loader.Loader.getKeyFromResultSet(Loader.java:286)
at net.sf.hibernate.loader.Loader.doResultSet(Loader.java:180)
at net.sf.hibernate.loader.Loader.doFind(Loader.java:111)
at net.sf.hibernate.loader.Loader.find(Loader.java:731)
at net.sf.hibernate.loader.SQLLoader.list(SQLLoader.java:68)
at net.sf.hibernate.impl.SessionImpl.findBySQL(SessionImpl.java:3695)
at net.sf.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:46)
at com.neoforma.hibernate.test.CMSUtil.backendexport(CMSUtil.java:127)
at com.neoforma.hibernate.test.HibernateMain.main(HibernateMain.java:36)

java.sql.SQLException: Invalid column name
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java)
at oracle.jdbc.driver.OracleStatement.get_column_index(OracleStatement.java)
at oracle.jdbc.driver.OracleResultSetImpl.findColumn(OracleResultSetImpl.java)
at oracle.jdbc.driver.OracleResultSet.getLong(OracleResultSet.java)
at net.sf.hibernate.type.LongType.get(LongType.java:14)

RTFM.

Hi,

It works, there is error in the document:

List cats = session.createSQLQuery(
"SELECT {cat.*} FROM CAT AS {cat} WHERE ROWNUM<10",
"cat",
Cat.class
).list();


You can't put 'AS' there.

You can on most databases.