Hi,

I'm trying to set up a bit more complex mapping, but I don't get it to work.

I have Account and Company as tables which are related via a Person (both relationships simply 1:n). An Account has Funds (m:n, mapping table ACCOUNT_FUND). Now I want to give some statistics about Funds per Company. To avoid some complexity I introduced a view ACCOUNTS_BY_COMPANY.

What I want to do at the end is something like the following:



but dynamically filtered by Company, so it is a bit more complex:



(See the additional where clause.)

And this is the part I don't get to work.

1. Hibernate does not like views when validating the schema.
Sorry, couldn't resist. But this one is really annoying as you always only get exceptions on runtime.

2. Filter



Even with :companyId being only one value instead of a collection (which I need at the end) I get an obvious error. The condition is not applied to the join, but to the resulting temporary table:



But the resulting temporary table has no longer any ACCOUNTS_BY_COMPANY.COMPANY_ID and I don't want to put it in as I would need additional calculations (first per company, second sum all values of selected companies) which only add inaccuracies.

3. Custom SQL Queries

So I got the idea it could work with custom SQL queries, especially as the reference seems to have similar queries with parameterized selection:



This one works with executing the named query directly and applying the parameter. But that's not what I want. I neither get a FundStatisticsData object nor does it even apply my custom types, so I could execute pure SQL as well. And the recommended <return-property> does not have any support for types (where I don't understand why it should by different than <property> inside <class>). So what does Hibernate buy me here?

But it also DOES NOT WORK with a pure query on the mapped class FundStatisticsData:


fails with searching a table FundStatisticsData instead of using the specified SQL query via <loader query-ref=""/>. Additionally setting the param :companyId lets it fail in searching the parameter ("Could not locate named parameter [companyId].").

Now I really don't know how to go on or what to try else. This mapping does not look THAT complex.

Thanks in advance,
Jörg

Using a named SQL query instead of a view is usually what I do, largely because I don’t have the ability to run DDL SQL on our database servers, and also because the schema is complete garbage.

Your fundStatisticsData SQL query should be returning one result if you want to use the loader mapping element. As your mapping stands now Hibernate will use the fundStatisticsData query when loading by id:

session.load(FundStatisticsData.class, companyId);

If you want to return multiple instances of FundStatisticsData, as I suspect, then you’ll need to use a named SQL query.

Query qry = session.getNamedQuery(“fundStatisticsData”);
qry.setParameter(“companyId”, companyId);
List results = qry.list();

Trying to run an HQL query (from FundStatisticsData) against a class that is not mapped to a table, i.e. mapped via a custom SQL query will not work. Unfortunately using custom SQL to load entities takes away a lot of the power Hibernate would normally provide.