Hibernate version: 3.1

Mapping documents: see below

Full stack trace of any exception that occurs: see below

Name and version of the database you are using: Postgres 8.0.3

The generated SQL (show_sql=true): n/a

I am in the process of converting a hibernate 3.0.x project to using EJB 3. So far, I have been able to convert all of my classes to entity beans successfully... except one. I have been struggling with this last class for several days and have tried a few approaches, but none have worked. Here is my hibernate 3.0.x mapping file that I am trying to port (works beautifully in hibernate 3.0.x)

<hibernate-mapping package="com.serviceratings.hibernate.data">
<class name="CategoryRating" mutable="false">
<subselect>
select
br.business_id, br.category_id, rd.criteria_id, count(*) as cnt, avg(value) as average
from
rateit.business_rating br,
rateit.rating_detail rd,
rateit.criteria c
where
rd.business_rating_id = br.id and
c.id = rd.criteria_id
group by
br.business_id, br.category_id, rd.criteria_id, c.index
order by
br.business_id, br.category_id, c.index
</subselect>
<synchronize table="rateit.business_rating" />
<synchronize table="rateit.rating_detail" />
<synchronize table="rateit.criteria" />
<composite-id>
<key-many-to-one name="business" column="business_id" class="Business" />
<key-many-to-one name="category" column="category_id" class="Category" />
<key-many-to-one name="criteria" column="criteria_id" class="Criteria" />
</composite-id>

<property name="count" column="cnt" type="java.lang.Integer" />
<property name="average" column="average" type="java.lang.Double" />
</class>

</hibernate-mapping>

The referenced classes have been converted to entity beans already.

Attempt 1: My first attempt was to use a named native query and result set mapping and embedding the query in the native query annotation. The result is the same as described in attempt 3 below.

Attempt 2: I then created a database view from the query (minus the order by) and tried to create the class as an entity bean pointing at the view. During application start up, the database validation code of EJB errored out saying that the table did not exist. It didn't seem to like the view (I did use @Table(name="<view>")).

Attempt 3: Here is attempt 3... a combination of 1 and 2.
@Entity()
@NamedNativeQuery(name="categoryRating", queryString="select * from rateit.business_category_rating order by business_id, category_id, index", resultSetMapping="categoryRating")
@SqlResultSetMapping(name="categoryRating",
entities=@EntityResult(name="com.bizmerit.ejb3.entity.CategoryRating",
fields={
@FieldResult(name="count", column="cnt"),
@FieldResult(name="average", column="average")
})//,
// columns={ @ColumnResult(name="business_id"), @ColumnResult(name="criteria_id"), @ColumnResult(name="category_id")}
)
public class CategoryRating extends BaseEntity
{
private Business business;
private Category category;
private Criteria criteria;
private Integer count;
private Double average;
...

I commented out the columns because I got an error that scalars were not supported.

Here is the error I get:

org.hibernate.HibernateException: Missing table: CategoryRating
at org.hibernate.cfg.Configuration.validateSchema(Configuration.java:953)
at org.hibernate.tool.hbm2ddl.SchemaValidator.validate(SchemaValidator.java:116)
at org.hibernate.impl.SessionFactoryImpl.<init>(SessionFactoryImpl.java:299)
at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1145)
at org.hibernate.ejb.Ejb3Configuration.createEntityManagerFactory(Ejb3Configuration.java:358)
at org.hibernate.ejb.Ejb3Configuration.createEntityManagerFactory(Ejb3Configuration.java:484)
at org.hibernate.ejb.Ejb3Configuration.createContainerEntityManagerFactory(Ejb3Configuration.java:202)
at org.hibernate.ejb.HibernatePersistence.createContainerEntityManagerFactory(HibernatePersistence.java:78)
at org.jboss.ejb3.Ejb3Deployment.initializeManagedEntityManagerFactory(Ejb3Deployment.java:512)
at org.jboss.ejb3.Ejb3Deployment.create(Ejb3Deployment.java:253)
at org.jboss.ejb3.Ejb3JmxDeployment.create(Ejb3JmxDeployment.java:230)
at org.jboss.ejb3.Ejb3Module.createService(Ejb3Module.java:34)
at org.jboss.system.ServiceMBeanSupport.jbossInternalCreate(ServiceMBeanSupport.java:245)
at org.jboss.system.ServiceMBeanSupport.jbossInternalLifecycle(ServiceMBeanSupport.java:228)
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.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher.java:141)
at org.jboss.mx.server.Invocation.dispatch(Invocation.java:80)
at org.jboss.mx.server.Invocation.invoke(Invocation.java:72)
at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:245)
at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:644)
at org.jboss.system.ServiceController$ServiceProxy.invoke(ServiceController.java:943)
at $Proxy0.create(Unknown Source)
at org.jboss.system.ServiceController.create(ServiceController.java:341)
at org.jboss.system.ServiceController.create(ServiceController.java:284)
at sun.reflect.GeneratedMethodAccessor5.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher.java:141)
at org.jboss.mx.server.Invocation.dispatch(Invocation.java:80)
at org.jboss.mx.server.Invocation.invoke(Invocation.java:72)
at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:245)
at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:644)
at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:176)
at $Proxy10.create(Unknown Source)
at org.jboss.ejb3.EJB3Deployer.create(EJB3Deployer.java:208)
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.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher.java:141)
at org.jboss.mx.server.Invocation.dispatch(Invocation.java:80)
at org.jboss.mx.server.Invocation.invoke(Invocation.java:72)
at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:245)
at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:644)
at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:176)
at $Proxy11.create(Unknown Source)
at org.jboss.deployment.MainDeployer.create(MainDeployer.java:935)
at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:789)
at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:753)
at sun.reflect.GeneratedMethodAccessor15.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher.java:141)
at org.jboss.mx.server.Invocation.dispatch(Invocation.java:80)
at org.jboss.mx.interceptor.AbstractInterceptor.invoke(AbstractInterceptor.java:118)
at org.jboss.mx.server.Invocation.invoke(Invocation.java:74)
at org.jboss.mx.interceptor.ModelMBeanOperationInterceptor.invoke(ModelMBeanOperationInterceptor.java:127)
at org.jboss.mx.server.Invocation.invoke(Invocation.java:74)
at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:245)
at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:644)
at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:176)
at $Proxy6.deploy(Unknown Source)
at org.jboss.deployment.scanner.URLDeploymentScanner.deploy(URLDeploymentScanner.java:319)
at org.jboss.deployment.scanner.URLDeploymentScanner.scan(URLDeploymentScanner.java:489)
at org.jboss.deployment.scanner.AbstractDeploymentScanner$ScannerThread.doScan(AbstractDeploymentScanner.java:192)
at org.jboss.deployment.scanner.AbstractDeploymentScanner$ScannerThread.loop(AbstractDeploymentScanner.java:203)
at org.jboss.deployment.scanner.AbstractDeploymentScanner$ScannerThread.run(AbstractDeploymentScanner.java:182)
09:13:51,528 INFO [EJB3Deployer] Deployed: file:/C:/work/jboss-4.0.3SP1/server/default/deploy/bizmerit.ejb3

It seems to be looking for a table called CategoryRating.

Any help is appreciated using any of the approaches or using a completely different approach.

Thanks,

Dan.

Using <subselect> is not yet possible
using a view works. There seems to be a bug with the hibernate.hbm2ddl.auto validate option and views, so disable this feature (hibernate.hbm2ddl.auto none)

Can hibernate.hbm2ddl.auto validate views yet?

Thanks,
Eric

afaik yes.

We are using Seam 1.1.6 with it's bundled version of hibernate-all.jar (unsure of the version) and turning on validation causes a failure when checking our entities against our views - it's tells us that the table name "view xyz" cannot be found. I can paste a stack trace if necessary.