Hello,
i have a MSSQL DB with two tables.
- table Resource(Res) contains infos like id, name,etc. of agents.
- table AgentStateDetail(Asd) logs the activity of the agents like busy,logged out, etc. One entry for each activity.
I would like to get agent Infos from table Res and the actually status of each agent from table Asd.
With native SQL i get what i want with this Query:
Code:
"select asd.eventDateTime, asd.eventType from db_cra.dbo.AgentStateDetail as asd CROSS JOIN db_cra.dbo.Resource res WHERE ( asd.eventDateTime = (SELECT MAX(asd2.eventDateTime) FROM db_cra.dbo.AgentStateDetail as asd2 WHERE asd2.agentID = res.resourceID)) AND (res.active = 1) GROUP BY res.resourceID, res.extension, res.resourceName,res.resourceLoginID, asd.eventDateTime, asd.eventType"
Now i would like to use hibernate. My Colleague said i have to use the <set> command for this in the mapping file.
Seems like there is a problem with the primary keys of the table. Th beans and the mapping files were automatically generated by hibernate tools. Seems like there are no relations between the two tables. Is it possible to create them?
I have attached the mapping files, the res-Tag.
the Resource.hbm.xml looks like this:
Code:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated 24.09.2007 10:24:30 by Hibernate Tools 3.2.0.b9 -->
<hibernate-mapping>
<class name="mssql.Resource" table="Resource">
<composite-id name="id" class="mssql.ResourceId">
<key-property name="resourceId" type="int">
<column name="resourceID" />
</key-property>
<key-property name="profileId" type="int">
<column name="profileID" />
</key-property>
</composite-id>
<property name="resourceLoginId" type="string">
<column name="resourceLoginID" length="50" not-null="true" />
</property>
....
th AgentStateDetail.hbm.xml looks like this:
Code:
<hibernate-mapping>
<class name="mssql.AgentStateDetail" table="AgentStateDetail">
<composite-id name="id" class="mssql.AgentStateDetailId">
<key-property name="agentId" type="int">
<column name="agentID" />
</key-property>
<key-property name="eventDateTime" type="timestamp">
<column name="eventDateTime" length="23" />
</key-property>
<key-property name="eventType" type="byte">
<column name="eventType" />
</key-property>
<key-property name="reasonCode" type="short">
<column name="reasonCode" />
</key-property>
<key-property name="profileId" type="int">
<column name="profileID" />
</key-property>
</composite-id>
<property name="gmtOffset" type="short">
<column name="gmtOffset" not-null="true" />
</property>
.....
I have tried to enter follwing Set parameters in Resource.hbm.xml
Code:
<set name="agentStateDetails" inverse="true">
<key>
<column name="agentID"/>
<column name="profileID"/>
</key>
<one-to-many class="mssql.AgentStateDetail" />
</set>
Code:
Compiling the code i get the following error:
15:37:27,328 INFO HbmBinder:2385 - Mapping collection: mssql.Resource.agentStateDetails -> AgentStateDetail
%%%% Error Creating HibernateSessionFactory %%%%
org.hibernate.MappingException: Foreign key (FK12A55C1DA5AE3C58:AgentStateDetail [agentID,profileID])) must have same number of columns as the referenced primary key (Resource [resourceID])
at org.hibernate.mapping.ForeignKey.alignColumns(ForeignKey.java:90)
I would be very glad about some help.
with kind regards
Martie
Login
FAQ
Search
