using join table but can't get date from join table

slim7 · **Joined:** Sat Nov 19, 2005 7:34 pm **Posts:** 4

How can I populate the date field from the join table in address.java object ??

I am using 3 tables (from hib examples , b/c if i can get this to work my real problem on my project will be solved)
person
address
personaddress

the join table just has three fields , personid, addressid, and date.

How can I populate the date field from the join table in address.java object ??
THis is my problem

Hibernate version: 3

Mapping documents:

Code:

<hibernate-mapping package="hibExample">
   <class name="Person">
      <id name="id" column="personId">
         <generator class="native"/>
      </id>

      <set name="addresses" table="PersonAddress">
         <key column="personId"/>
         <many-to-many column="addressId" unique="false" class="Address"/>
      </set>
      <property name="name" column="name"/>

   </class>
   <class name="Address">
      <id name="id" column="addressId">
         <generator class="native"/>
      </id>
      <property name="street" column="street"/>
       <property name="date" column="date" type="java.lang.String"/> 
   </class>
</hibernate-mapping>

<------------- here is my database tables --------------------------->

Code:

CREATE TABLE `address` (
  `addressId` bigint(20) NOT NULL auto_increment,
  `street` varchar(255) default NULL,
  PRIMARY KEY  (`addressId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `person` (
  `personId` bigint(20) NOT NULL auto_increment,
  `name` varchar(100) default NULL,
  PRIMARY KEY  (`personId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `personaddress` (
  `personId` bigint(20) NOT NULL,
  `addressId` bigint(20) NOT NULL,
  `date` varchar(100) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Code between sessionFactory.openSession() and session.close():
i'm just reading from db ( criteria.list() ) no writing or updates !
Full stack trace of any exception that occurs:

Name and version of the database you are using: MySQL 5

The generated SQL (show_sql=true):

Debug level Hibernate log excerpt:

Code:

DEBUG - Static SQL for entity: hibExample.Person
DEBUG -  Version select: select personId from Person where personId =?
DEBUG -  Snapshot select: select person_.personId, person_.name as name0_ from Person person_ where person_.personId=?
DEBUG -  Insert 0: insert into Person (name, personId) values (?, ?)
DEBUG -  Update 0: update Person set name=? where personId=?
DEBUG -  Delete 0: delete from Person where personId=?
DEBUG -  Identity insert: insert into Person (name) values (?)
DEBUG - Static SQL for entity: hibExample.Address
DEBUG -  Version select: select addressId from Address where addressId =?
DEBUG -  Snapshot select: select address_.addressId, address_.street as street2_, address_.date as date2_ from Address address_ where address_.addressId=?
DEBUG -  Insert 0: insert into Address (street, date, addressId) values (?, ?, ?)
DEBUG -  Update 0: update Address set street=?, date=? where addressId=?
DEBUG -  Delete 0: delete from Address where addressId=?
DEBUG -  Identity insert: insert into Address (street, date) values (?, ?)
DEBUG - Static SQL for collection: hibExample.Person.addresses
DEBUG -  Row insert: insert into PersonAddress (personId, addressId) values (?, ?)
DEBUG -  Row update: update PersonAddress set addressId=? where personId=? and addressId=?
DEBUG -  Row delete: delete from PersonAddress where personId=? and addressId=?
DEBUG -  One-shot delete: delete from PersonAddress where personId=?
DEBUG - Static select for entity hibExample.Person: select person0_.personId as personId0_0_, person0_.name as name0_0_ from Person person0_ where person0_.personId=?
DEBUG - Static select for entity hibExample.Person: select person0_.personId as personId0_0_, person0_.name as name0_0_ from Person person0_ where person0_.personId=?
DEBUG - Static select for entity hibExample.Person: select person0_.personId as personId0_0_, person0_.name as name0_0_ from Person person0_ where person0_.personId=? for update
DEBUG - Static select for entity hibExample.Person: select person0_.personId as personId0_0_, person0_.name as name0_0_ from Person person0_ where person0_.personId=? for update
DEBUG - Static select for action ACTION_MERGE on entity hibExample.Person: select person0_.personId as personId0_0_, person0_.name as name0_0_ from Person person0_ where person0_.personId=?
DEBUG - Static select for action ACTION_REFRESH on entity hibExample.Person: select person0_.personId as personId0_0_, person0_.name as name0_0_ from Person person0_ where person0_.personId=?
DEBUG - Static select for entity hibExample.Address: select address0_.addressId as addressId2_0_, address0_.street as street2_0_, address0_.date as date2_0_ from Address address0_ where address0_.addressId=?
DEBUG - Static select for entity hibExample.Address: select address0_.addressId as addressId2_0_, address0_.street as street2_0_, address0_.date as date2_0_ from Address address0_ where address0_.addressId=?
DEBUG - Static select for entity hibExample.Address: select address0_.addressId as addressId2_0_, address0_.street as street2_0_, address0_.date as date2_0_ from Address address0_ where address0_.addressId=? for update
DEBUG - Static select for entity hibExample.Address: select address0_.addressId as addressId2_0_, address0_.street as street2_0_, address0_.date as date2_0_ from Address address0_ where address0_.addressId=? for update
DEBUG - Static select for action ACTION_MERGE on entity hibExample.Address: select address0_.addressId as addressId2_0_, address0_.street as street2_0_, address0_.date as date2_0_ from Address address0_ where address0_.addressId=?
DEBUG - Static select for action ACTION_REFRESH on entity hibExample.Address: select address0_.addressId as addressId2_0_, address0_.street as street2_0_, address0_.date as date2_0_ from Address address0_ where address0_.addressId=?
DEBUG - Static select for collection hibExample.Person.addresses: select addresses0_.personId as personId1_, addresses0_.addressId as addressId1_, address1_.addressId as addressId2_0_, address1_.street as street2_0_, address1_.date as date2_0_ from PersonAddress addresses0_ left outer join Address address1_ on addresses0_.addressId=address1_.addressId where addresses0_.personId=?
DEBUG - initializing class SessionFactoryObjectFactory
DEBUG - registered: ff8080810797e760010797e768080000 (unnamed)
INFO  - Not binding factory to JNDI, no JNDI name configured
DEBUG - instantiated session factory
INFO  - Checking 0 named queries
DEBUG - opened session at timestamp: 4637183688384512
DEBUG - about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
DEBUG - opening JDBC connection
DEBUG - total checked-out connections: 0
DEBUG - using pooled JDBC connection, pool size: 0
DEBUG - select this_.personId as personId0_0_, this_.name as name0_0_ from Person this_
DEBUG - preparing statement
DEBUG - about to open ResultSet (open ResultSets: 0, globally: 0)
DEBUG - processing result set
DEBUG - result set row: 0
DEBUG - returning '5' as column: personId0_0_
DEBUG - result row: EntityKey[hibExample.Person#5]
DEBUG - Initializing object from ResultSet: [hibExample.Person#5]
DEBUG - Hydrating entity: [hibExample.Person#5]
DEBUG - returning 'Joe' as column: name0_0_
DEBUG - result set row: 1
DEBUG - returning '6' as column: personId0_0_
DEBUG - result row: EntityKey[hibExample.Person#6]
DEBUG - Initializing object from ResultSet: [hibExample.Person#6]
DEBUG - Hydrating entity: [hibExample.Person#6]
DEBUG - returning 'mary' as column: name0_0_
DEBUG - done processing result set (2 rows)
DEBUG - about to close ResultSet (open ResultSets: 1, globally: 1)
DEBUG - about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
DEBUG - closing statement
DEBUG - total objects hydrated: 2
DEBUG - resolving associations for [hibExample.Person#5]
DEBUG - creating collection wrapper:[hibExample.Person.addresses#5]
DEBUG - done materializing entity [hibExample.Person#5]
DEBUG - resolving associations for [hibExample.Person#6]
DEBUG - creating collection wrapper:[hibExample.Person.addresses#6]
DEBUG - done materializing entity [hibExample.Person#6]
DEBUG - initializing non-lazy collections
DEBUG - after autocommit
DEBUG - aggressively releasing JDBC connection
DEBUG - closing JDBC connection [ (open PreparedStatements: 0, globally: 0) (open ResultSets: 0, globally: 0)]
DEBUG - returning connection to pool, pool size: 1

zzantozz · **Joined:** Fri Oct 28, 2005 10:46 am **Posts:** 37

Map a PersonAddress class which has the date field and necessary relationships to Person and Address.

moonlight · **Joined:** Mon Aug 29, 2005 9:46 am **Posts:** 102

Just adding some info to what zzantozz said...
You must map this PersonAddress class just as you mapped the other two ones. If you have this mapping and still things aren't working, please show them to us.
In this mapping you'll have a mapping relation to class Address, which will already have the date informed, right? By adding a relation to Address you add the date to the table.

By the way, you're not really persisting a Date. You're persisting a String field which happens to have a date content.

:)

slim7 · **Joined:** Sat Nov 19, 2005 7:34 pm **Posts:** 4

So what you two are saying is add a new java class, i.e. PersonAdress.java or somethign w/ the date as a field, would there be an id and what would it be ? And I don't know how to do that mapping or I am confused about adding another mapping for personaddress class , how would i map that ??

zzantozz · **Joined:** Fri Oct 28, 2005 10:46 am **Posts:** 37

Yes, a class like this:

Code:

public class PersonAddress {
    private int id;
    private Date date;
    private Person person;
    private Address address;

    // Whatever methods
}

Then your Person and Address both have a one-to-many collection of PersonAddress. It might be better to call it Occupancy or something like more meaningful. I assume this is to track historically who lived where, and that's the reason for the many-to-many Person-Address relationship as well as the date.

slim7 · **Joined:** Sat Nov 19, 2005 7:34 pm **Posts:** 4

But what would the mapping look like for that class? Does it have one at all or should I leave my current mappings how they are ??? The mappings are the hardest thing right now, not the java, I've been doing java for years and i'm about ready to just go back to straight jdbc .

zzantozz · **Joined:** Fri Oct 28, 2005 10:46 am **Posts:** 37

Without looking at the reference guide:

Code:

<class name="PersonAddress">
    <id name="id">
        <generator ... />
    </id>
    <property name="date" column="dateOccupied"/> <!-- "date" is a database reserved word -->
    <many-to-one name="person" column="personId"/>
    <many-to-one name="address" column="addressId"/>
</class>

To make this relationship bidirectional from Person:

Code:

<set name="addresses" table="pastLocations" inverse="true">
    <key column="personId"/>
    <one-to-many class="PersonAddress"
</set>

And something very similar from Address.