Hibernate Version 3.1
This is a "reporting" application. All the data is coming out of a warehouse, and we have control over the schema: We are building custom tables to flatten out the data even more that the warehouse does already. (MSSQL 2000/2005 databases)
Here is the mapping for a typical entity class. You will notice that it uses a composite key, which we define in the underlying table.
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class name="com.wilsontechnology.hibernate.FeBt"
proxy="com.wilsontechnology.hibernate.FeBt" mutable="false"
table="FE_BTS">
<meta attribute="class-description" inherit="false">
@hibernate.class table="FE_BTS"
</meta>
<!-- <cache usage="read-only"/> -->
<cache usage="read-only" />
<composite-id name="comp_id"
class="com.wilsontechnology.hibernate.FeBtPK">
<meta attribute="field-description" inherit="false">
@hibernate.id generator-class="assigned"
</meta>
<key-property name="tregioncode" column="tregioncode"
type="java.lang.String" length="7">
<meta attribute="field-description">
@hibernate.property column="tregioncode"
</meta>
</key-property>
<key-property name="tcntrycode" column="tcntrycode"
type="java.lang.String" length="7">
<meta attribute="field-description">
@hibernate.property column="tcntrycode"
</meta>
</key-property>
<key-property name="tloccode" column="tloccode"
type="java.lang.String" length="10">
<meta attribute="field-description">
@hibernate.property column="tloccode"
</meta>
</key-property>
<key-property name="tdeptcode" column="tdeptcode"
type="java.lang.String" length="10">
<meta attribute="field-description">
@hibernate.property column="tdeptcode"
</meta>
</key-property>
<key-property name="tsectcode" column="tsectcode"
type="java.lang.String" length="10">
<meta attribute="field-description">
@hibernate.property column="tsectcode"
</meta>
</key-property>
<key-property name="tkinit" column="tkinit"
type="java.lang.String" length="10">
<meta attribute="field-description">
@hibernate.property column="tkinit"
</meta>
</key-property>
<key-property name="period" column="period"
type="java.lang.Integer" length="10">
<meta attribute="field-description">
@hibernate.property column="period"
</meta>
</key-property>
<key-property name="clnum" column="clnum"
type="java.lang.String" length="50" >
<meta attribute="field-description">
@hibernate.property column="clnum"
</meta>
</key-property>
<key-property name="matterno" column="matterno"
type="java.lang.String" length="50">
<meta attribute="field-description">
@hibernate.property column="matterno"
</meta>
</key-property>
</composite-id>
<property name="tregiondesc" type="java.lang.String"
column="tregiondesc" length="50">
<meta attribute="field-description">
@hibernate.property column="tregiondesc" length="50"
</meta>
</property>
<property name="tcntrydesc" type="java.lang.String"
column="tcntrydesc" length="50">
<meta attribute="field-description">
@hibernate.property column="tcntrydesc" length="50"
</meta>
</property>
<property name="tlocdesc" type="java.lang.String"
column="tlocdesc" length="50">
<meta attribute="field-description">
@hibernate.property column="tlocdesc" length="50"
</meta>
</property>
<property name="tdeptdesc" type="java.lang.String"
column="tdeptdesc" length="50">
<meta attribute="field-description">
@hibernate.property column="tdeptdesc" length="50"
</meta>
</property>
<property name="tsectdesc" type="java.lang.String"
column="tsectdesc" length="50">
<meta attribute="field-description">
@hibernate.property column="tsectdesc" length="50"
</meta>
</property>
<property name="tkalpha" type="java.lang.String"
column="tkalpha" length="100">
<meta attribute="field-description">
@hibernate.property column="tkalpha" length="100"
</meta>
</property>
<property name="clname" type="java.lang.String" column="clname"
length="60">
<meta attribute="field-description">
@hibernate.property column="clname" length="60"
</meta>
</property>
<property name="mdesc" type="java.lang.String" column="mdesc"
length="60">
<meta attribute="field-description">
@hibernate.property column="mdesc" length="60"
</meta>
</property>
<property name="costPer" type="java.math.BigDecimal"
column="cost_per" length="38">
<meta attribute="field-description">
@hibernate.property column="cost_per" length="38"
</meta>
</property>
<property name="costYtd" type="java.math.BigDecimal" column="cost_ytd"
length="10">
<meta attribute="field-description">
@hibernate.property column="cost_ytd" length="38"
</meta>
</property>
<property name="cltimePer" type="java.math.BigDecimal"
column="cltime_per" length="38">
<meta attribute="field-description">
@hibernate.property column="cltime_per" length="38"
</meta>
</property>
<property name="cltimeYtd" type="java.math.BigDecimal"
column="cltime_YTD" length="10">
<meta attribute="field-description">
@hibernate.property column="cltime_YTD" length="38"
</meta>
</property>
<property name="ftimePer" type="java.math.BigDecimal"
column="ftime_per" length="38">
<meta attribute="field-description">
@hibernate.property column="ftime_per" length="38"
</meta>
</property>
<property name="ftimeYtd" type="java.math.BigDecimal"
column="ftime_ytd" length="18">
<meta attribute="field-description">
@hibernate.property column="ftime_ytd" length="38"
</meta>
</property>
<property name="workValuePer" type="java.math.BigDecimal"
column="work_value_per" length="38">
<meta attribute="field-description">
@hibernate.property column="work_value_per" length="38"
</meta>
</property>
<property name="workValueYtd" type="java.math.BigDecimal"
column="work_value_ytd" length="10">
<meta attribute="field-description">
@hibernate.property column="work_value_ytd" length="38"
</meta>
</property>
<property name="stdHrsPer" type="java.math.BigDecimal"
column="std_hrs_per" length="38">
<meta attribute="field-description">
@hibernate.property column="std_hrs_per" length="38"
</meta>
</property>
<property name="stdHrsYtd" type="java.math.BigDecimal"
column="std_hrs_ytd" length="10">
<meta attribute="field-description">
@hibernate.property column="std_hrs_ytd" length="380"
</meta>
</property>
<property name="pwipPer" type="java.math.BigDecimal" column="pwip_per"
length="18">
<meta attribute="field-description">
@hibernate.property column="pwip_per" length="38"
</meta>
</property>
<!-- Associations -->
<!-- derived association(s) for compound key -->
<!-- end of derived association(s) -->
</class>
</hibernate-mapping>
I want to run a scalar Group By query that will group by all non-numeric columns except clnum, clname,matterno and mdesc. The specific problem I am having is that the query result is not cast to the managed entity class, in this case FeBt.class.
A simple SQLQuery just to test the concept works fine and returns a list of FeBt objects:
return session.createSQLQuery("Select * from FE_BTS where period = 200404").addEntity(FeBt.class).list();
But here is the query that I want to run that does the Group By. Notice that I included null strings and aliases for the excluded coumns: If I do not do this I get 'invalid column name' errors. Also, the .addScalar methods are probably superfluous, but I have the same problem with or without. I have followed the documentation on Native SQl pretty closely, and I suspect that the problem is tied to improperly referencing those columns that are elements of the composite key. This query just returns a list of untyped objects:
String sqlQuery = "select febt.tregioncode,febt.tregiondesc,febt.tcntrycode,febt.tcntrydesc,febt.tloccode,febt.tlocdesc," +
"febt.tdeptcode,febt.tdeptdesc,febt.tsectcode,febt.tsectdesc,febt.tkinit,febt.tkalpha,febt.period,'' as clnum, '' as clname,'' as matterno,'' as mdesc, " +
"sum(febt.cost_per) as cost_per,sum(febt.cost_ytd) as cost_ytd,sum(febt.cltime_per) as cltime_per,sum(febt.cltime_YTD) as cltime_YTD," +
"sum(febt.ftime_per)as ftime_per,sum(febt.ftime_ytd) as ftime_ytd," +
"sum(febt.work_value_per) as work_value_per,sum(febt.work_value_ytd) as work_value_ytd," +
"sum(febt.std_hrs_per) as std_hrs_per,sum(febt.std_hrs_ytd) as std_hrs_ytd, Sum(febt.pwip_per) as pwip_per " +
"from FE_BTS febt where febt.tcntrycode like :countryCode and febt.period = :period " +
"group by febt.tregioncode,febt.tregiondesc,febt.tcntrycode,febt.tcntrydesc,febt.tloccode,febt.tlocdesc," +
"febt.tdeptcode,febt.tdeptdesc,febt.tsectcode,febt.tsectdesc,febt.tkinit,febt.tkalpha,febt.period";
return session.createSQLQuery(sqlQuery)
.addScalar("comp_id.tregioncode", Hibernate.STRING)
.addScalar("tregiondesc", Hibernate.STRING)
.addScalar("tcntrycode", Hibernate.STRING)
.addScalar("tcntrydesc", Hibernate.STRING)
.addScalar("tloccode", Hibernate.STRING)
.addScalar("tlocdesc", Hibernate.STRING)
.addScalar("tdeptcode", Hibernate.STRING)
.addScalar("tdeptdesc", Hibernate.STRING)
.addScalar("tsectcode",Hibernate.STRING)
.addScalar("tsectdesc",Hibernate.STRING)
.addScalar("tkinit", Hibernate.STRING)
.addScalar("tkalpha", Hibernate.STRING)
.addScalar("period", Hibernate.INTEGER)
.addScalar("clnum", Hibernate.STRING)
.addScalar("clname", Hibernate.STRING)
.addScalar("matterno", Hibernate.STRING)
.addScalar("mdesc", Hibernate.STRING)
.addScalar("cost_per", Hibernate.BIG_DECIMAL)
.addScalar("cost_ytd", Hibernate.BIG_DECIMAL)
.addScalar("cltime_per", Hibernate.BIG_DECIMAL)
.addScalar("cltime_YTD", Hibernate.BIG_DECIMAL)
.addScalar("ftime_per", Hibernate.BIG_DECIMAL)
.addScalar("ftime_ytd", Hibernate.BIG_DECIMAL)
.addScalar("work_value_per", Hibernate.BIG_DECIMAL)
.addScalar("work_value_ytd", Hibernate.BIG_DECIMAL)
.addScalar("std_hrs_per", Hibernate.BIG_DECIMAL)
.addScalar("sstd_hrs_ytd", Hibernate.BIG_DECIMAL)
.addScalar("pwip_per", Hibernate.BIG_DECIMAL)
.addEntity(FeBt.class)
.setString("countryCode", cntrycode)
.setInteger("period",period)
.list();
Thanks for any insight you may have.
Login
FAQ
Search
