Mapping an object to an SQL [SOLVED]

ajaygautam · **Joined:** Wed May 24, 2006 4:40 pm **Posts:** 10

Hello,

I am going through the documentation to figure out a way to map the following (sample) sql statement:

Code:

select 
   a.id, b.name, c.city
from
   emp a, identity b, address c
where
   a.id = b.id and a.id = c.id

I *don't* want to create domain objects and mapping for emp, identity and address.

All I want is to have one object, mapped to the output of the sql statement.

Code:

public class Data {
   private int id;
   private String name;
   private String city;
   // ... and other details ...
}

Technically, this should be the job of a stored procedure or a view. Unfortunately, I cannot change the database.

Any help / pointers would be appreciated.

Hibernate version: 3.2.5 ga

Name and version of the database you are using: Sybase 11

Thanks

Ajay

ffray · **Posted:** Thu Jul 31, 2008 12:01 pm

Hi Ajay,

how about creating a native query and supply a result-set-mapping?
This works for me when using JPA, but there's also a plain hibernate solution for this, see the docs, section 16.4 (http://www.hibernate.org/hib_docs/reference/en/html_single/).
Note that this solution requires the Data-class is an Entity.

Something (in your orm.xml) like:

Code:

<named-native-query name="grabdata"
            result-set-mapping="grabdataRsMapping">
    <query>
        select 
            a.id as data_id, b.name as data_name, c.city as data_city
        from 
            emp a, identity b, address c 
        where
             a.id = b.id and a.id = c.id
    </query>
</named-native-query>

<sql-result-set-mapping name="grabdataRsMapping">
    <entity-result entity-class="foo.bar.Data">
        <field-result name="id" column="data_id" />
        <field-result name="name" column="data_name" />
        <field-result name="city" column="data_city" />
    </entity-result>
</sql-result-set-mapping>

Regards,

Florian

Cameron McKenzie · **Posted:** Thu Jul 31, 2008 4:41 pm

You can create a class in Hibernate that has properties that map to multiple talbes. I'm not sure if it's the way that I'd do it, but it's certainly possible.

Here's a little tutorial on the topic:

Mapping one Java Class to Multiple Database Tables with Hibernate and JPA Annotations

ajaygautam · **Joined:** Wed May 24, 2006 4:40 pm **Posts:** 10

Thanks ffray, your solution worked. Here is what I did:

sql-mapping.hbm.xml:

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">

<hibernate-mapping>
<sql-query name="my-query">
   <return class="com.blah.db.domain.Results"/>
        select
            a.id as data_id, b.name as data_name, c.city as data_city
        from
            emp a, identity b, address c
        where
             a.id = b.id and a.id = c.id 
</sql-query>
</hibernate-mapping>

Results.hbm.xml:

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">

<hibernate-mapping>
   <class name="com.blah.db.domain.Results" lazy="false">
      <composite-id>
         <key-property name="data_id"/>
         <key-property name="data_name"/>
      </composite-id>

      <property name="data_city"/>
   </class>
</hibernate-mapping>

Include both these mappings in hibernate.cfg.xml
Java code snippet:

Code:

            Query q = session.getNamedQuery("my-query");
            LOG.info("Running query...");
            List data = q.list(); // list of Results
            LOG.info("return size: " + data.size());

ofcourse, this is not the real thing, but should be enough to get someone else started on something similar.

Thanks to Cameron too. You book seems good, I have added it to my wish list. (I am not using annotation right now, so did not look into your solution much)

Thanks again

Ajay

ysbelman · **Joined:** Thu Oct 25, 2007 5:24 pm **Posts:** 12

I have not seen a working example of mapping result set into a NON-ENTITY pojo. Hibernate config file allows it and i've used it before (very handy), but JPA wants to only deal with entities.

In the following oracle JPA example, you can see SqlResultSetMapping is referring to entities. That means it must have an Id, and a unique one, or your results will not get populated in the result List property. In oracle you can use ROWNUM to generate a temporary identifier for your result set, but that's just wacky.

Code:

@SqlResultSetMapping(
    name="OrderResults",
    entities={
        @EntityResult(
            entityClass=Order.class, 
            fields={
                @FieldResult(name="id",       column="order_id"),
                @FieldResult(name="quantity", column="order_quantity"),
                @FieldResult(name="item",     column="order_item")
            }
        ),

Code:

entityManager.createNativeQuery("SELECT ROWNUM as order_id, quantity as order_quantity, item as order_item FROM...");

Has anyone experienced something better with JPA?