native sql ... any way without mapped class

sboulay · **Joined:** Wed Feb 25, 2004 10:58 am **Posts:** 43

is there anyway to use native sql without having to map a class to the database and instead use a transiant object ?

the createSqlQuery seems to always take a class that has to be mapped .. is there anyway to use an object that is not mapped not using hql .. I am already aware of report sytle queries and dynamice instantiation with hql

Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version:

Mapping documents:

Code between sessionFactory.openSession() and session.close():

Full stack trace of any exception that occurs:

Name and version of the database you are using:

The generated SQL (show_sql=true):

Debug level Hibernate log excerpt:

jlindwall · **Posted:** Fri Jun 17, 2005 3:19 pm

I'm afraid I do not have your solution, I'm simply a new hibernate user possiby in the same situation as you. I'm not sure if native SQL or some other feature of hibernate might resolve my issue; I'm curious if my issue equates to yours?

I have mapped a class to a table, for example's sake let's call it Vehicle. A Vehicle contains numerous fields including ID and NAME. Certain business logic in the system is more "set oriented" then "object oriented" -- it does not want to load a single Vehicle. It wants a (possibly large) list of Vehicle IDs and NAMES that meet certain criteria. In my case this information is read-only and is used to populate a drop-down list in the UI.

What I would love to get directly from hibernate is a Map with the Vehicle ID as the key and the Vehicle NAME as the value.

For this use case I do not want to load 10000 fully-loaded Vehicle instances and weed through it to manually build such a Map. It would be nice if hibernate could instead create such a Map efficiently for me. I'd prefer not to create a VehicleIdAndAbbrev mapped class just for this purpose. Such an object would be transient and immutable and serve no purpose outside this narrow use case.

Is this scenario like yours?

pksiv · **Posted:** Fri Jun 17, 2005 3:31 pm

jlindwall wrote:

I'm afraid I do not have your solution, I'm simply a new hibernate user possiby in the same situation as you. I'm not sure if native SQL or some other feature of hibernate might resolve my issue; I'm curious if my issue equates to yours?

I have mapped a class to a table, for example's sake let's call it Vehicle. A Vehicle contains numerous fields including ID and NAME. Certain business logic in the system is more "set oriented" then "object oriented" -- it does not want to load a single Vehicle. It wants a (possibly large) list of Vehicle IDs and NAMES that meet certain criteria. In my case this information is read-only and is used to populate a drop-down list in the UI.

What I would love to get directly from hibernate is a Map with the Vehicle ID as the key and the Vehicle NAME as the value.

For this use case I do not want to load 10000 fully-loaded Vehicle instances and weed through it to manually build such a Map. It would be nice if hibernate could instead create such a Map efficiently for me. I'd prefer not to create a VehicleIdAndAbbrev mapped class just for this purpose. Such an object would be transient and immutable and serve no purpose outside this narrow use case.

Is this scenario like yours?

You can map a lightweight object to the same table as the full object as long as you label it mutable=false. I use this quite extensively to populate comboboxes etc...

You can then return the list of objects - which only contain ID & Name with a standard hibernate query and put them into a map if you choose.

Here is an example of something similar - http://www.hibernate.org/41.html

sboulay · **Joined:** Wed Feb 25, 2004 10:58 am **Posts:** 43

yes .. I see what your saying but the problem is that you still have to map the class. I have alot of native sql is queries that produce reports and just want to dump into my dao's.

Since I am using the Spring framework it turns out I can mix and match dao's easily so I am using hibernate for the workflow part of the application and ibatis for native sql queries.

max · **Posted:** Sat Jun 18, 2005 2:29 am

with latest hibernate 3 you can do stuff like (notice the lack of alias and {} replacements which were required before):

Code:

 <class name="WhateverDAO">
   <id name="id">...</id>
   <property name="Name"/>
</class>

<sql-query name="myQuery">
   <return class="WhateverDAO"/>
  select id,,name from Whatever
</sql-query>

AFAIK that is very much equivalent to the amount of stuff you need to do in ibatis ?

jlindwall · **Posted:** Mon Jun 20, 2005 3:34 pm

I don't know about the original poster, sboulay, but max's suggestion is perfect for my needs.

I defined a "mapped" class (though not tied to any specific table). I can then write custom queries that populate these guys. One query can load the data fom the VEHICLE table, another can use the PARTS table, etc. Nice!

Now I just need to learn how to parameterize the named queries and I'm all set... THanks from me; I hope this solves sboulay's problem too!

jlindwall · **Posted:** Mon Jun 20, 2005 4:16 pm

I figured out how to pas params to my named query -- it was easy.

FYI: My Hibernate implementation of this DAO consists of 74 lines of clean java code and 46 lines on config (hbm.xml). The previous implementation written using a homemade JDBC wrapper framework was 268 lines of yucky java code! Wheeeee!

mahikty · **Posted:** Tue Jun 21, 2005 4:47 pm

jlindwall wrote:

I figured out how to pas params to my named query -- it was easy.

FYI: My Hibernate implementation of this DAO consists of 74 lines of clean java code and 46 lines on config (hbm.xml). The previous implementation written using a homemade JDBC wrapper framework was 268 lines of yucky java code! Wheeeee!

Could you share the piece of code that solves your problem.

jlindwall · **Posted:** Tue Jun 21, 2005 5:24 pm

(Tangent: What's a "bump posting"?)

Here's the relevant parts of the solution that I used:

The IdAbbrev.java object:

Code:

public class IdAbbrev
{
    private Integer id;
    private String abbreviation;

    public Integer getId()
    {
        return id;
    }

    public void setId(Integer id)
    {
        this.id = id;
    }

    public String getAbbreviation()
    {
        return abbreviation;
    }

    public void setAbbreviation(String abbreviation)
    {
        this.abbreviation = abbreviation;
    }
}

Hibernate mapping file fragment:

Code:

  <class name="IdAbbrev" mutable="false" >
    <id name="id"  >
      <generator class="increment"/>
    </id>
    <property name="abbreviation" />
  </class>

  <sql-query name="getVehicleIdAbbrevs">
   <return class="IdAbbrev"/>
    select vehicle_id as id,abbrv as abbreviation from vehicle, vehicle_typ vt where vehicle_typ = ft.vehicle_typ and vt.has_motor = :hasMotor
  </sql-query>

Using this in my Spring DAO implementation:

Code:

public List getVehicleIdsAndAbbreviations(Boolean hasMotor)
{
    Query q = getSession().getNamedQuery("getVehicleIdAbbrevs");
    q.setParameter("hasMotor", hasMotor);

    return q.list();
}

mahikty · **Posted:** Tue Jun 21, 2005 10:12 pm

Thanks Jindwell.

jlindwall · **Posted:** Wed Jun 22, 2005 11:21 am

I goofed on the query I provided above (typed ft instead of vt); here's teh correction::

Code:

    select vehicle_id as id,abbrv as abbreviation from vehicle, vehicle_typ vt where vehicle_typ = vt.vehicle_typ and vt.has_motor = :hasMotor 

beniji · **Posted:** Wed Jun 22, 2005 8:46 pm

also for dynamic models, have a look at session.getSession(EntityMode.MAP)