Hibernate version: 3.3.1 GA
Mapping documents:
<sql-query name="timetracker.hibernate.entities.TimeTrackSummary.SQL_SELECT_ALL" callable="true">
<![CDATA[
SELECT
{fn DATE(t.StartDateTime)} AS startDate,
{fn MONTH(t.StartDateTime)} AS startHour,
{fn HOUR(t.StartDateTime)} AS monthNumber,
pp.ProjectName AS parentProjectName,
pp.ProjectID AS parentProjectID,
p.ProjectName AS projectName,
t.ProjectID AS projectID,
SUM({fn TIMESTAMPDIFF(SQL_TSI_MINUTE, t.StartDateTime, t.EndDateTime)}) AS durationMinutes,
COUNT(*) AS summedRows,
a.TAGNAME AS tagName
FROM timetrack t
INNER JOIN project p ON (p.ProjectID = t.ProjectID)
INNER JOIN project pp ON (pp.ProjectID = p.ParentProjectID)
INNER JOIN tag a ON (a.TagID = t.TagID)
GROUP BY
DATE(t.StartDateTime),
MONTH(t.StartDateTime),
HOUR(t.StartDateTime),
pp.ProjectID,
pp.ProjectName,
t.ProjectID,
p.ProjectName,
a.TagName
]]>
</sql-query>

Name and version of the database you are using: JavaDB 10.4.1.3

I have a need to use <sql-query> within my mapping document as part of this, I have several requirements

1) I need to aggregate time ranges SUM(endtime - starttime)
2) join other tables
3) load everything into a single flat object

This is code that is part of a different project that is being ported to use Hibernate.

<sql-query> is being used since I have not found a good way to get the different between two dates using HQL.

The query work just fine with code like this...


@Test
public void hibernateTimeTrackSummaryRawSQLFetchTest() {
List timetracklist = null;
Query q = null;
List l = null;
Object[] oa = null;
q = session.getNamedQuery(
"timetracker.hibernate.entities.TimeTrackSummary.SQL_SELECT_ALL"
);

l = q.list();
System.out.println("Got result of " + l.size());
for(Object o : l) {
oa = (Object[]) o;
System.out.println("Item " + "[" +
oa[0].toString() + "][" +
oa[1].toString() + "][" +
oa[2].toString() + "][" +
oa[3].toString() + "][" +
oa[4].toString() + "][" +
oa[5].toString() + "][" +
oa[6].toString() + "][" +
oa[7].toString() + "][" +
oa[8].toString() + "][" +
oa[9].toString() + "]"
);
} // end-foreach

return;
}


However, when I try to setup a <return ... /> within the mapping file so I can use code like


@Test
public void hibernateTimeTrackSummarySQLFetchTest() {
List<TimeTrackSummary> list = null;
Query q = null;
q = session.getNamedQuery(
"timetracker.hibernate.entities.TimeTrackSummary.SQL_SELECT_ALL"
);

list = (List<TimeTrackSummary>) q.list();
System.out.println("Got result of " + list.size());

assertNotNull(list);
System.out.println("Tag returned " + list.size() + " items.");
if (true) {
for (Object tt : list) {
System.out.println(tt.toString());
} // end-foreach
} // end-if
return;
}


... using a mapping file of...


<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping >
<sql-query name="timetracker.hibernate.entities.TimeTrackSummary.SQL_SELECT_ALL" callable="true">
<return alias="summary" class="timetrack.hibernate.entities.TimeTrackSummary" />
<![CDATA[
SELECT
{fn DATE(t.StartDateTime)} AS {summary.startDate},
{fn MONTH(t.StartDateTime)} AS {summary.startHour},
{fn HOUR(t.StartDateTime)} AS {summary.monthNumber},
pp.ProjectName AS {summary.parentProjectName},
pp.ProjectID AS {summary.parentProjectID},
p.ProjectName AS {summary.projectName},
t.ProjectID AS {summary.projectID},
SUM({fn TIMESTAMPDIFF(SQL_TSI_MINUTE, t.StartDateTime, t.EndDateTime)}) AS {summary.durationMinutes},
COUNT(*) AS {summary.summedRows},
a.TAGNAME AS {summary.tagName}
FROM timetrack t
INNER JOIN project p ON (p.ProjectID = t.ProjectID)
INNER JOIN project pp ON (pp.ProjectID = p.ParentProjectID)
INNER JOIN tag a ON (a.TagID = t.TagID)
GROUP BY
DATE(t.StartDateTime),
MONTH(t.StartDateTime),
HOUR(t.StartDateTime),
pp.ProjectID,
pp.ProjectName,
t.ProjectID,
p.ProjectName,
a.TagName
]]>
</sql-query>
</hibernate-mapping>


... I get mapping file errors...


[ERROR] [2009-01-02 @ 00:06:46] [main|SessionFactoryImpl] Error in named query: timetracker.hibernate.entities.TimeTrackSummary.SQL_SELECT_ALL
org.hibernate.MappingException: Unknown entity: timetrack.hibernate.entities.TimeTrackSummary
at org.hibernate.impl.SessionFactoryImpl.getEntityPersister(SessionFactoryImpl.java:580)
at org.hibernate.loader.custom.sql.SQLQueryReturnProcessor.getSQLLoadable(SQLQueryReturnProcessor.java:335)
at org.hibernate.loader.custom.sql.SQLQueryReturnProcessor.processRootReturn(SQLQueryReturnProcessor.java:376)
at org.hibernate.loader.custom.sql.SQLQueryReturnProcessor.processReturn(SQLQueryReturnProcessor.java:355)
at org.hibernate.loader.custom.sql.SQLQueryReturnProcessor.process(SQLQueryReturnProcessor.java:171)
at org.hibernate.loader.custom.sql.SQLCustomQuery.<init>(SQLCustomQuery.java:87)
at org.hibernate.engine.query.NativeSQLQueryPlan.<init>(NativeSQLQueryPlan.java:67)
at org.hibernate.engine.query.QueryPlanCache.getNativeSQLQueryPlan(QueryPlanCache.java:136)
at org.hibernate.impl.SessionFactoryImpl.checkNamedQueries(SessionFactoryImpl.java:476)
at org.hibernate.impl.SessionFactoryImpl.<init>(SessionFactoryImpl.java:384)
at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1327)
at timetracker.hibernate.helpers.HibernateHelper.createSessionFactory(HibernateHelper.java:83)
at timetracker.hibernate.helpers.HibernateHelper.getInstance(HibernateHelper.java:53)
at timetracker.hibernate.testing.helping.HibernateHelperJUnitTest.setUp(HibernateHelperJUnitTest.java:85)
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:597)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:44)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:15)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:41)
at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:27)
at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:31)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:73)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:46)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:180)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:41)
at org.junit.runners.ParentRunner$1.evaluate(ParentRunner.java:173)
at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:28)
at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:31)
at org.junit.runners.ParentRunner.run(ParentRunner.java:220)
at junit.framework.JUnit4TestAdapter.run(JUnit4TestAdapter.java:39)
at org.apache.tools.ant.taskdefs.optional.junit.JUnitTestRunner.run(JUnitTestRunner.java:515)
at org.apache.tools.ant.taskdefs.optional.junit.JUnitTestRunner.launch(JUnitTestRunner.java:1031)
at org.apache.tools.ant.taskdefs.optional.junit.JUnitTestRunner.main(JUnitTestRunner.java:888)


Am I getting the "Unknown entity: Timetrack.hibernate.entities.TimeTrackSummary" error because I don't have a <class ...> setup in my mapping file? If so, how do you create a class that is really not bound to a specific table but is a composite of several tables?

If I follow the same sql-query approach using the <return alias="..." ... /> tag that doen't have a join or any aggregates, it works as expected. I am only having this problem when I join an additional table.

Thanks for your thoughts and advice.