I created an entity mapping for a helper class we use to improve performance. The entity is not mapped to a table. It's more like a DTO we return from native SQL queries. The entity mapping is shown below.
What I'm curious about are the rules with respect to using this mapping from a named sql query. In the first query shown below I have no problem returning VwNote. The second query that does not return columns for every property in the mapping gave me trouble. I could never get it working. I was not sure if Hibernate required placeholders in the query results for all mapped properties. I tried returning null and empty string in the sql query for the missing columns but always got hibernate errors (illegal named query) trying to deploy the application. Is it possible to return VwNote from a native query in a mapping file if the query results do not contain columns for every mapped property? If so, can I get a helping hand with the syntax in the mapping file. Thanks.
Grant
Code:
<hibernate-mapping>
<class name="gov.hhs.acf.cb.nytd.models.helper.VwNote">
<id name="noteId" type="string" />
<property name="recordNumber" type="string"/>
<property name="elementName" type="string"/>
<property name="elementDescription" type="string"/>
<property name="datumValue" type="string"/>
<property name="noteText" type="string"/>
</class>
</hibernate-mapping>
Code:
<sql-query name="getDatumNotes">
<return alias="note" class="gov.hhs.acf.cb.nytd.models.helper.VwNote" />
select
datum_.rowid as {note.noteId},
this_.RECORDNUMBER as {note.recordNumber},
element_.NAME as {note.elementName},
element_.DESCRIPTION as {note.elementDescription},
datum_.VALUE as {note.datumValue},
datum_.NOTES as {note.noteText}
from TRANSMISSIONRECORD this_
inner join TRANSMISSION trans_
on this_.TRANSMISSIONID = trans_.TRANSMISSIONID
inner join DATUM datum_
on this_.TRANSMISSIONRECORDID = datum_.TRANSMISSIONRECORDID
inner join ELEMENT element_
on datum_.ELEMENTID = element_.ELEMENTID
where trans_.TRANSMISSIONID = :transmissionId
and datum_.notes is not null
order by this_.RECORDNUMBER, element_.NAME
</sql-query>
Code:
<sql-query name="getRecordNotes">
<return alias="note" class="gov.hhs.acf.cb.nytd.models.helper.VwNote" />
select
this_.rowid as {note.noteid},
this_.RECORDNUMBER as {note.recordNumber},
null as {note.elementName},
null as {note.elementDescription},
null as {note.datumValue},
this_.NOTES as {note.noteText}
from TRANSMISSIONRECORD this_
where this_.NOTES is not null
and this_.TRANSMISSIONID = :transmissionId
</sql-query>
Login
FAQ
Search
