0 Replies Latest reply on Feb 9, 2008 10:06 AM by Arnie Morein

    Named Query - results as object?

    Arnie Morein Newbie

      I'm not entirely sure if creating a non-table-related class in Hibernate is entirely supported, but it compiles w/o issue.

      In the mapping file below, the class is intended to represent the record returned by the Named Query defined below it.

      The problem is that the query tag doesn't take the return tag. I guess I could turn this into a sql-query but that seems to be counter intuitive - embedding dbms-specific (possibly) SQL in the abstraction layer when it shouldn't be necessary.

      After the .list() command completes, I get a List of Object arrays with no relation to the code that invoked it. Sure, I can do things like (String)objVar[0] to get at my values, but its very ugly.

      Is there no way to get the results of a HQL-based Named Query into a custom object?

      Thanks in advance.


      <?xml version="1.0" encoding="utf-8"?>
      <!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
      "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
      <!--
       Created to encapsulate the return results from a custom
       select query for the report. However, the query tag
       does not take the return attribute, only the sql-query
       does.
      -->
      <hibernate-mapping>
      
       <class name="us.tx.state.oag.olrap.report.AppsOnFile">
       <id name="applicationId" type="java.math.BigDecimal">
       <column name="application_id" precision="18" scale="0" />
       <generator class="assigned" />
       </id>
       <property name="emplid" type="java.lang.String">
       <column name="emplid" length="11" not-null="true" />
       </property>
       <property name="nameFirst" type="java.lang.String">
       <column name="name_first" length="30" not-null="true" />
       </property>
       <property name="nameMiddle" type="java.lang.String">
       <column name="name_middle" length="30" />
       </property>
       <property name="nameLast" type="java.lang.String">
       <column name="name_last" length="30" not-null="true" />
       </property>
       <property name="title" type="java.lang.String">
       <column name="title" length="60" not-null="true" />
       </property>
       <property name="deptId" type="java.lang.String">
       <column name="deptid" length="10" not-null="false" />
       </property>
       <property name="departmentName" type="java.lang.String">
       <column name="department_name" length="10" not-null="false" />
       </property>
       <property name="calculatedAward" type="java.math.BigDecimal">
       <column name="calculated_award" precision="11" not-null="true" />
       </property>
       <property name="baseAward" type="java.math.BigDecimal">
       <column name="base_award" precision="11" not-null="true" />
       </property>
       <property name="status" type="java.lang.String">
       <column name="status" length="60" not-null="true" />
       </property>
       </class>
      
       <query name="ApplicationsOnFileByStatusId">
       <query-param name="codeStatus" type="java.math.BigDecimal"/>
       <![CDATA[
       select apps.applicationId, emps.emplid, emps.nameFirst,
       emps.nameMiddle, emps.nameLast,
       emps.title, emps.deptId, emps.departmentName,
       apps.calculatedAward, apps.baseAward,
       status.description as status
       from us.tx.state.oag.olrap.db.InfoApplication as apps
       inner join apps.infoEmployees as emps
       inner join apps.codeStatus as status
       where apps.codeStatus.statusId = :codeStatus
       ]]>
       </query>
      
       <query name="ApplicationsOnFileByActive">
       <![CDATA[
       select apps.applicationId, emps.emplid, emps.nameFirst,
       emps.nameMiddle, emps.nameLast,
       emps.title, emps.deptId, emps.departmentName,
       apps.calculatedAward, apps.baseAward,
       status.description as status
       from us.tx.state.oag.olrap.db.InfoApplication as apps
       inner join apps.infoEmployees as emps
       inner join apps.codeStatus as status
       where apps.codeStatus.statusId in (
       select statusId from us.tx.state.oag.olrap.db.CodeStatus as status2
       where status2.description = 'Submitted'
       or status2.description = 'Qualified'
       or status2.description = 'Approved'
       )
       ]]>
       </query>
      
      </hibernate-mapping>