Is it possible to map ResultSet to List of Maps?

nrbhattad · **Joined:** Tue Jun 21, 2005 1:45 pm **Posts:** 38

A sql-query is defined as below --

Code:

<sql-query name="findAllPolicycoveragesForPolicyinfo" >
   <return  entity-name="polcov">
          <return-property name="covno" column="covno"/>
            <return-property name="covdesc" column="covdesc"/>
            <return-property name="limitsno" column="limitsno"/>
       </return>
      select a.covno as covno, a.covdesc as covdesc, b.limitsno as limitsno
       from coveragelku a, policycoverage b 
       where policyno = :policyno and customerno = :customerno 
       and a.covno = b.covno union select covno, covdesc, null 
       from coveragelku where covno not in
       (select c.covno as covno from coveragelku c, policycoverage d 
       where c.covno = d.covno and d.policyno = :policyno and d.customerno = :customerno)
   </sql-query>

Below code returns an error--

Code:

Session session = HibernateUtil.currentSession();
        
        List results = session.getNamedQuery("findAllPolicycoveragesForPolicyinfo")
                         .setParameter("policyno", new Integer(1))
                         .setParameter("customerno", new Integer(1))
                         .list();
        System.out.println(results);

Code:

org.hibernate.MappingException: Unknown entity: polcov
   at org.hibernate.impl.SessionFactoryImpl.getEntityPersister(SessionFactoryImpl.java:569)
   at org.hibernate.loader.custom.SQLQueryReturnProcessor.getSQLLoadable(SQLQueryReturnProcessor.java:53)
   at org.hibernate.loader.custom.SQLQueryReturnProcessor.processRootReturn(SQLQueryReturnProcessor.java:119)
   at org.hibernate.loader.custom.SQLQueryReturnProcessor.processReturn(SQLQueryReturnProcessor.java:98)
   at org.hibernate.loader.custom.SQLQueryReturnProcessor.process(SQLQueryReturnProcessor.java:87)

I want the result in "List of Maps" fashion... As documented in 5.4 Dynamic models, hibernate supports Maps instead of POJOs...

So is there a way to configure returned resultset in the form of List of Maps?

nrbhattad · **Joined:** Tue Jun 21, 2005 1:45 pm **Posts:** 38

Hibernate Gurus, plz help!!

max · **Posted:** Wed Jun 29, 2005 6:06 am

no matter if you have maps or pojos you need to have an entity mapped.

If sql-query doesnt work with that then its a bug...it should probably work (but i dont have a concrete test for it)

nrbhattad · **Joined:** Tue Jun 21, 2005 1:45 pm **Posts:** 38

max wrote:

no matter if you have maps or pojos you need to have an entity mapped.

If sql-query doesnt work with that then its a bug...it should probably work (but i dont have a concrete test for it)

thanks max...
just resolved the issue with below mapping --
this is a different example than the earlier one...

Code:

<class entity-name="custaccresult">
    <id
        name="accountno"
        type="java.lang.Integer"
        column="accountno"
        unsaved-value="0"
    >
        <generator class="identity" />
    </id>
    
    <property
        name="active"
        type="java.lang.String"
        column="active"
        length="1"
    />
    <property
        name="accountname"
        type="java.lang.String"
        column="accountname"
        length="1"
    />
    <property
        name="acc_checked"
        type="java.lang.String"
        column="acc_checked"
        length="1"
    />
</class>

   <sql-query name="findAllAccountsForCustomer" >
      <return entity-name="custaccresult" >
       </return>   
      select a.accountno as accountno, a.accountname as accountname, b.active as active, 1 acc_checked 
       from Account a, CustomerAccount b 
       where customerno = :customerno
       and a.accountno = b.accountno union select accountno, accountname, null, 0 acc_checked 
       from Account where accountno not in
       (select c.accountno as accountno from Account c, CustomerAccount d 
       where c.accountno = d.accountno and d.customerno = :customerno)
   </sql-query>

only thing that bothers me is for each sql query(like above where acc_checked is a dummy column) i would need to define a corresponding mapping class.....

wouldnt it be better if hibernate could just return the resultset as is in list of maps fashion? that way there wont be a need to define mapping class every time for complicated sqls...

in above example, custaccresult is not a physical table... its defined to map resultset-->Map... however, "id" is a reqd element under class.... so the idea of defining an id field for a resultset is kindof confusing.....

max · **Posted:** Wed Jun 29, 2005 6:53 am

add a jira request for allowing resulttransformers to native sql queries and provide your use case.

max · **Posted:** Wed Jun 29, 2005 6:56 am

but note, you would still need to map the returned values as <return-scalar> so hibernate know what to do with them ,)