Hibernate annotation version: 3.2.1 GA

Mapping documents:

@Entity
@NamedNativeQuery(name="getEmpDept", query="select e.ename,d.dname from emp e, dept d where e.deptId = d.deptId", resultClass=EmployeeDepartment.class)
class EmployeeDepartment {
private String ename;
private String dname;
@Column(name="ename")
public int getEname() {
return ename;
}
@Column(name="dname")
public int getDname() {
return dname;
}
}


Code between sessionFactory.openSession() and session.close():

session.createCriteria(EmployeeDepartment.class);

Full stack trace of any exception that occurs:

org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2214)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2095)
at org.hibernate.loader.Loader.list(Loader.java:2090)
at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:95)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1569)
at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:283)
at com.vmware.cp.inv.DomainInventoryDaoImpl.getDomainInventory(DomainInventoryDaoImpl.java:41)
at com.vmware.cp.inv.DomainInventoryDaoTest.getDomainInventory(DomainInventoryDaoTest.java:31)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.junit.internal.runners.TestMethodRunner.executeMethodBody(TestMethodRunner.java:99)
at org.junit.internal.runners.TestMethodRunner.runUnprotected(TestMethodRunner.java:81)
at org.junit.internal.runners.BeforeAndAfterRunner.runProtected(BeforeAndAfterRunner.java:34)
at org.junit.internal.runners.TestMethodRunner.runMethod(TestMethodRunner.java:75)
at org.junit.internal.runners.TestMethodRunner.run(TestMethodRunner.java:45)
at org.junit.internal.runners.TestClassMethodsRunner.invokeTestMethod(TestClassMethodsRunner.java:71)
at org.junit.internal.runners.TestClassMethodsRunner.run(TestClassMethodsRunner.java:35)
at org.junit.internal.runners.TestClassRunner$1.runUnprotected(TestClassRunner.java:42)
at org.junit.internal.runners.BeforeAndAfterRunner.runProtected(BeforeAndAfterRunner.java:34)
at org.junit.internal.runners.TestClassRunner.run(TestClassRunner.java:52)
at org.apache.maven.surefire.junit4.JUnit4TestSet.execute(JUnit4TestSet.java:62)
at org.apache.maven.surefire.suite.AbstractDirectoryTestSuite.executeTestSet(AbstractDirectoryTestSuite.java:138)
at org.apache.maven.surefire.suite.AbstractDirectoryTestSuite.execute(AbstractDirectoryTestSuite.java:125)
at org.apache.maven.surefire.Surefire.run(Surefire.java:132)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.apache.maven.surefire.booter.SurefireBooter.runSuitesInProcess(SurefireBooter.java:290)
at org.apache.maven.surefire.booter.SurefireBooter.main(SurefireBooter.java:818)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid column name 'CPUcnt'.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source)
at com.microsoft.sqlserver.jdbc.IOBuffer.processPackets(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.getPrepExecResponse(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PreparedStatementExecutionRequest.executeStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.CancelableRequest.execute(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeRequest(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(Unknown Source)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:92)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1778)
at org.hibernate.loader.Loader.doQuery(Loader.java:662)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
at org.hibernate.loader.Loader.doList(Loader.java:2211)


Name and version of the database you are using: Sql Server 2005

The generated SQL (show_sql=true): N/A

Debug level Hibernate log excerpt: N/A







I am using spring and hibernate in my project (class HibernateDaoSupport in package "org.springframework.orm.hibernate3.support")

I am trying to retrieve custom sql query results into my class. When I use the method "findByNamedQuery" of HibernateDaoSupport class, I receive the expected result. However, before retriving the records, I would like to filter few records based on some conditions at runtime.

In the above code snippet (EmployeeDepartment class), I would like to filter employees whose name start with "A". This can be achieved using binding parameters. But if I use parameters binding, it will have to be always supplied, which I dont want.

I did achieve the similar thing for my other class using this code

session.createCriteria(Employee.class).add(Expression.eq("ename", ename))

However I could not do the same for EmployeeDepartment class. Difference between these two classes is Employee class maps to exactly one table in database (so uses @Table annotation) but EmployeeDepartment class is populated by a native custom query.

Any help would be greatly appreciated

It sounds like you're confusing mapping with querying. The optional @Table annotation tells Hibernate which table your entity maps to. If you omit it, the ONE table name will be the same as the unqualified Java class name. If you want to map across more than one table, check this section of the manual:

http://www.hibernate.org/hib_docs/v3/re ... ation-join

The native query you've declared does not map your entity to more than one table; it simply gives a named query that you can invoke using the Query API which is not to be confused with the Criteria API.

If you want optional bind parameters you'll have to create two named queries. Using a DAO, you can abstract this away from the calling program.

Hope this clears things up a bit.

-Chris

Thanks Chris. I understand that named query is different than the hibernate mapping. However, I see the workaround of my problem.

I am thinking of creating a view and map the Java class to a view instead of mapping it to a table. That way I can create a criteria object, add any expression to that criteria and avoid the need to write multiple named queries and conditionally use them.

But what I was under the impression that if I can achieve mapping a Java class to a query using a view, there should be similar mechanism in hibernate without using a view as well. Unfortunately, this seems to be not the case.