Simple Hibernate Question - Is This Possible?

lukedewitt · **Joined:** Fri May 12, 2006 3:39 pm **Posts:** 13

Not sure exactely how to search the documentation for this question... so I think posting here is my best bet.

Basically I have two tables, Affiliate and Data. Affiliate objects will hold a List or Set of Data objects. I use Hibernate to bring in a single Affiliate object. With this Affiliate though, it will bring in ALL Data records from the database into this list/set with it. I don't want ALL the Data, I only need the 9 most recent entries into the database...

Is there a way that I can simply bring in these 9 objects when I bring in an Affiliate?

Thanks

--D

damnhandy · **Posted:** Fri Sep 01, 2006 1:59 pm

Yes it is possible, but you would want to use a formula in your one-to-many mapping declaration.

Ryan-

lukedewitt · **Joined:** Fri May 12, 2006 3:39 pm **Posts:** 13

Hey thanks for the quick response!

This is where I define my set...

<set name="WeatherDataList" table="WeatherData">
<key column="affiliateId" not-null="true" />
<one-to-many class="com.adsmack.smacklet.weather.domain.WeatherData" />
</set>

So where that is a part of my mapping I would have something along the lines of formula="SELECT * FROM weatherdata ORDER BY id DESC LIMIT 9" in my one-to-many class?

damnhandy · **Posted:** Fri Sep 01, 2006 2:49 pm

Actually, I was mistaken in recommending the <formula> element as its not applicable to a one-to-many. You should add a where attribute to your one-to-many. The SQL syntax for doing this is going to be dependent on what how, or if, your database can limit the result set. But your mapping might look soemthing like:

Code:

<set name="WeatherDataList" 
table="WeatherData">
<key column="affiliateId" not-null="true" />
<one-to-many class="com.adsmack.smacklet.weather.domain.WeatherData"  

  order-by="id DESC" 
  where="LIMIT 9"/>
</set>

I may be missing something here, but this should get you started.

Ryan-

lukedewitt · **Joined:** Fri May 12, 2006 3:39 pm **Posts:** 13

Hey, sorry I didn't get around to reading this until now. I tried what you said and I am getting a new error... this is the stack trace:

Sep 2, 2006 10:13:22 AM org.hibernate.cfg.Environment <clinit>
INFO: Hibernate 3.0
Sep 2, 2006 10:13:22 AM org.hibernate.cfg.Environment <clinit>
INFO: hibernate.properties not found
Sep 2, 2006 10:13:22 AM org.hibernate.cfg.Environment <clinit>
INFO: using CGLIB reflection optimizer
Sep 2, 2006 10:13:22 AM org.hibernate.cfg.Environment <clinit>
INFO: using JDK 1.4 java.sql.Timestamp handling
Sep 2, 2006 10:13:22 AM org.hibernate.cfg.Configuration configure
INFO: configuring from resource: /hibernate.cfg.xml
Sep 2, 2006 10:13:22 AM org.hibernate.cfg.Configuration getConfigurationInputStream
INFO: Configuration resource: /hibernate.cfg.xml
Sep 2, 2006 10:13:23 AM org.hibernate.cfg.Configuration addResource
INFO: Mapping resource: com/adsmack/smacklet/weather/persistence/hibernateDAO/WeatherData.hbm.xml
Sep 2, 2006 10:13:23 AM org.hibernate.cfg.HbmBinder bindRootPersistentClassCommonValues
INFO: Mapping class: com.adsmack.smacklet.weather.domain.WeatherData -> weatherData
Sep 2, 2006 10:13:23 AM org.hibernate.cfg.Configuration addResource
INFO: Mapping resource: com/adsmack/smacklet/weather/persistence/hibernateDAO/AffiliateWeather.hbm.xml
Sep 2, 2006 10:13:23 AM org.hibernate.util.XMLHelper$ErrorLogger error
SEVERE: Error parsing XML: XML InputStream(21) Attribute "order-by" must be declared for element type "one-to-many".
Sep 2, 2006 10:13:23 AM org.hibernate.util.XMLHelper$ErrorLogger error
SEVERE: Error parsing XML: XML InputStream(21) Attribute "where" must be declared for element type "one-to-many".

How can I declare these attributes for the one-to-many type?

Thanks,

--LD

damnhandy · **Posted:** Sat Sep 02, 2006 6:40 pm

Can you post your mapping files?

Ryan-

lukedewitt · **Joined:** Fri May 12, 2006 3:39 pm **Posts:** 13

Sure thing... here they are...

AffiliateWeather.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 default-lazy="false">

    <class name="com.adsmack.smacklet.weather.domain.AffiliateWeather" table="affiliateweather">
        <id name="id" type="string">
            <column name="id" />
            <generator class="assigned" />
        </id>
        
      <property name="location" />
      
      <set name="WeatherDataList" table="WeatherData">
         <key column="affiliateId" not-null="true" />
         <one-to-many class="com.adsmack.smacklet.weather.domain.WeatherData" order-by="id DESC" where="LIMIT 9" />
      </set>

   </class>
</hibernate-mapping>

WeatherData.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 default-lazy="false">

    <class name="com.adsmack.smacklet.weather.domain.WeatherData" table="weatherData">
        <id name="id" type="integer">
            <column name="id" />
            <generator class="increment" />
        </id>
      
      <property name="date" type="java.util.Calendar">
            <column name="date" sql-type="datetime" />
        </property>
      <property name="image" />
        <property name="forecast" />      
        <property name="temperature" />
        <property name="dewPoint" />      
        <property name="windSpeed" />
        <property name="windDirection" />      
        <property name="humidity" />
        <property name="pressure" />
        <property name="pressureTrend" />
        <property name="periodId" />
        <property name="name" />
        <property name="pop" />
        <property name="weekdayId" />
        <property name="high" />
        <property name="low" />
           
    </class>
</hibernate-mapping>

Sorry it took me so long to reply, my ISP was down all day yesterday and I am just getting into the office today.

Thanks,

--LD

pasha · **Joined:** Sat Nov 27, 2004 4:13 am **Posts:** 137

just change your mapping to:

Code:

...
<set name="WeatherDataList" table="WeatherData" order-by="id DESC" where="LIMIT 9">
  <key column="affiliateId" not-null="true" />
  <one-to-many class="com.adsmack.smacklet.weather.domain.WeatherData"/>
</set>
...

don't forget that LIMIT 9 does not work on all DBMSs

lukedewitt · **Joined:** Fri May 12, 2006 3:39 pm **Posts:** 13

pasha wrote:

don't forget that LIMIT 9 does not work on all DBMSs

Hey again (sorry for so many questions).

I'm getting this error...

60297 [http-8080-Processor24] ERROR org.hibernate.util.JDBCExceptionReporter - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 9 and weatherdat0_.affiliateId='ca_nb_fredericton' order by weatherdat0_.i' at line 1
ERROR:org.hibernate.exception.SQLGrammarException: could not initialize a collection: [com.adsmack.smacklet.weather.domain.AffiliateWeather.WeatherDataList#ca_nb_fredericton]

I am currently using MySQL (latest version) and if I was to type "Select * from weatherdata wd where affiliateId="ca_nb_fredericton" order by id desc limit 9" -- I would get exactely what I need, but when doing it through hibernate, I get that error... any idea why that may be happening? Do I need a more up to date .jar file perhaps?

Thanks again

--LD

lukedewitt · **Joined:** Fri May 12, 2006 3:39 pm **Posts:** 13

I got it.

Hibernate was trying to do the LIMIT before the ORDER BY so I just did

ORDER-BY="id DESC LIMIT 9"

And it worked... thank you all for your help!