Is this possible?

awschmid · **Posted:** Thu May 24, 2007 5:17 pm

Hi,

I'm looking into hibernate as a possibility for a large database project I'll be working on. It's not clear to me after reading the documentation whether the following would be possible.

The database in question is storing 12 months of information for each account. Each row has the account number, the year, and the month. So each account has 12 rows in the database. This database is updated monthly and always has exactly 12 rows for each account.

Ideally, I would like to have one of two options:

1. A java "account" object with all of the remaining fields (meaning not the account number, month, and year) represented as arrays. For example, a property double[] balances to hold the last 12 months balances.

OR

2. An "MasterAccount" (maybe?) object with one field being the account number, and the other being an array of "account" objects, each with their own fields (say balance), so I could do the following: MasterAccount.getAccounts().get(0).getBalance().

Could anyone first of all tell me if this is possible. And secondly, if it is even possible, point me in the right direction of how to go about with a mapping file for this scenario? Remember, I am a newbie to Hibernate!

Thanks,
Andrew

amila733 · **Posted:** Fri May 25, 2007 12:54 am

Hi

You can do this with hibernate

You can use Table-per-class Hierarchy

You can get idea from
http://www.hibernate.org/hib_docs/v3/reference/en/html_single/

Send your database structure for more detail

Amila

(Don't forget to rate if helps)

awschmid · **Posted:** Fri May 25, 2007 9:19 am

Hi Amila,

Thanks for the response. However, I don't see yet how Table per class hierarchy could be applied to the problem I stated. Maybe a concrete example would help.

Consider a single table "Accounts", with only 4 fields:

char "AccountNum", int "ObservationMonth", int "ObservationYear", and double "Balance"
with sample data:

"123abc",1,2007,450

"123abc",2,2007,500

"123abc",3,2007,400

"123abc",4,2007,1000

"123abc",5,2007,450

"123abc",6,2007,450

"123abc",7,2007,450

"123abc",8,2007,450

"123abc",9,2007,200

"123abc",10,2007,200

"123abc",11,2007,150

"123abc",12,2007,0

The idea is to query the table, grouping by "AccountNum", and have a single object for each individual "AccountNum" with an array of the other fields. Just showing how to do this with even one field would help.

Thanks

amila733 · **Posted:** Mon May 28, 2007 3:17 am

Hi

This is a sample code for you

Code:

package lk.wts.test;

import java.util.Set;


public class Accounts {
   String accountNum;
   String name;
   Set details;
   
   public String getAccountNum() {
      return accountNum;
   }
   public void setAccountNum(String accountNum) {
      this.accountNum = accountNum;
   }
   
   public String getName() {
      return name;
   }
   public void setName(String name) {
      this.name = name;
   }
   
   public Set getDetails() {
      return details;
   }
   public void setDetails(Set details) {
      this.details = details;
   }   
   
}

Accounts.hbm.xml

Code:

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

<hibernate-mapping
>     
    <class
        name="lk.wts.test.Accounts"
        table="Accounts"
    >

      
        <id
            name="accountNum"
            column="AccountNum"
            type="java.lang.String"
        >
            <generator class="assigned">              
            </generator>
        </id>

        <property
            name="name"
            type="java.lang.String"
            update="true"
            insert="true"
            column="Name"
        />

        <set
            name="details"
            lazy="false"
            inverse="true"
            cascade="all"
            sort="unsorted"
        >

            <key
                column="AccountNum"
            >
            </key>

            <one-to-many
                  class="lk.wts.test.AccountsDetails"
            />

        </set>
        
    </class>

</hibernate-mapping>

AccountsDetails.class

Code:

package lk.wts.test;

import java.io.Serializable;

import org.apache.commons.lang.builder.HashCodeBuilder;


public class AccountsDetails implements Serializable{
   
   
   String accountNum;
   int observationMonth;
   int observationYear;
   double balance;
   
   
   public String getAccountNum() {
      return accountNum;
   }
   public void setAccountNum(String accountNum) {
      this.accountNum = accountNum;
   }
   
   public double getBalance() {
      return balance;
   }
   public void setBalance(double balance) {
      this.balance = balance;
   }
   public int getObservationMonth() {
      return observationMonth;
   }
   public void setObservationMonth(int observationMonth) {
      this.observationMonth = observationMonth;
   }
   public int getObservationYear() {
      return observationYear;
   }
   public void setObservationYear(int observationYear) {
      this.observationYear = observationYear;
   }
   
   public boolean equals(Object arg0) {      
      if(arg0 instanceof AccountsDetails){
         AccountsDetails obj = (AccountsDetails)arg0;
         String key = this.accountNum+"_"+ this.observationMonth+"_"+ this.observationYear;
         return key.equals(obj.getAccountNum()+"_"+obj.getObservationMonth()+"_"+obj.getObservationYear());
      }
      return false;
      
   }
   public int hashCode() {
      HashCodeBuilder hashBuilder=new HashCodeBuilder();      
      return hashBuilder.append(this.accountNum+"_"+ this.observationMonth+"_"+ this.observationYear).hashCode();
   }   
}

AccountsDetail.hbm.xml

Code:

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

<hibernate-mapping
>     
    <class
        name="lk.wts.test.AccountsDetails"
        table="AccountsDetails"
    >
      
        <composite-id                      
        >
                     <key-property
                        name="accountNum"
                        type="java.lang.String"
                        column="AccountNum"
                />

                     <key-property
                        name="observationMonth"
                        type="int"
                        column="ObservationMonth"
                />

                     <key-property
                        name="observationYear"
                        type="int"
                        column="ObservationYear"
                />

        </composite-id>

        <property
            name="balance"
            type="double"
            update="true"
            insert="true"
            column="Balance"
        />
        

    </class>

</hibernate-mapping>

try this query in your application

Code:

Query q=sess.createQuery("select a from Accounts a where a.accountNum='123abc'");
      List list=q.list();

You didn't rate my answer

Amila

(Don't forget to rate if helps)

awschmid · **Posted:** Tue May 29, 2007 2:41 pm

For anyone else facing a similar problem, the above solution does not work for the described scenario (all data in a SINGLE table). The one-to-many tag is expecting a foreign key reference, which there is none in this scenario. Secondly, Hibernate won't let you specify a single field as the foreign key, and a composite ID with multiple fields in the other table.

If anyone else has an idea on how to go about doing this I would love to hear some ideas.

Regards,

Andrew

amila733 · **Posted:** Wed May 30, 2007 2:31 am

Hi Andrew

I didn't notice that single table. I thought it in ralationally.

Ok never mind. You can map two classes to a single table. like this

Change your
AccountDetails.hbm.xml

Code:

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

<hibernate-mapping
>     
    <class
        name="lk.wts.test.AccountsDetails"
        table="AccountsDetails"
    >

       <id   
        column="AccountNum"    
        name="accountNum"               
        >
                     <generator class="assigned"></generator>

                     

        </id>
      <property
            name="observationYear"
            type="double"
            update="true"
            insert="true"
            column="ObservationYear"
        />
        <property
            name="observationMonth"
            type="double"
            update="true"
            insert="true"
            column="ObservationMonth"
        />
        <property
            name="balance"
            type="double"
            update="true"
            insert="true"
            column="Balance"
        />

        <!--
            To add non XDoclet property mappings, create a file named
                hibernate-properties-Accounts.xml
            containing the additional properties and place it in your merge dir.
        -->

    </class>

</hibernate-mapping>

remove your name property from accounts class and accounts.hbm.xml file.

Change table name in accounts.hbm.xml to AccountsDetail

Code:

<class 
        name="lk.wts.test.Accounts" 
        table="AccountsDetail" 
    > 

try this querry in your code

Code:

Query q=sess.createQuery("select a  from Accounts a where a.accountNum='123abc' group by a.accountNum");
List list=q.list();

Amila

(Don't forget to rate if helps)

sidern · **Joined:** Mon Apr 16, 2007 2:05 pm **Posts:** 5

Using table-per-class Hierarchy was the most annoying thing until I found this thread. It should be easier from now on.