hibernate mapping

pyrotechnique · **Joined:** Wed Sep 20, 2006 5:07 pm **Posts:** 2

Ok, so I have two tables that are linked by a foreign key. I'm trying to retrieve a record from one of the tables, based on criteria from both tables. Say I have a Person table and a User table, and the oid in the Person table is tied to the SSN in the user table. If I know first name, last name and SSN, of which the first two attributes come from the Person table and the latter from the User table, and I want to retrieve the unique User that matches all 3 of these attributes, how would I do this? I thought that by doing some kind of join in the hibernate mapping file and using HQL this might be possible, but I have not been successful in doing so. Does anyone have any suggestions?

pyrotechnique · **Joined:** Wed Sep 20, 2006 5:07 pm **Posts:** 2

anyone?

CWitherow · **Posted:** Fri Sep 22, 2006 1:52 pm

Person.hbm.xml:

Code:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 2.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
  <class name="mytest.Person" table="persons" lazy="false">
    <id column="person_id" name="id" type="java.lang.Long" unsaved-value="0">
      <generator class="native" />
    </id>
    <property column="first_name" name="firstName" type="string" />
    <property column="last_name" name="lastName" type="string" />
    <one-to-one name="user" property-ref="person" class="mytest.User" fetch="join" cascade="all" />
    ...
  </class>
</hibernate-mapping>

User.hbm.xml:

Code:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 2.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
  <class name="mytest.User" table="users" lazy="false">
    <id column="user_id" name="id" type="java.lang.Long" unsaved-value="0">
      <generator class="native" />
    </id>
    <many-to-one column="person_id" name="person" class="mytest.Person" />
    <property column="social_security_number" name="socialSecurityNumber" type="string" not-null="true" />
    <property column="user_name" name="userName" not-null="true" type="string" />
    ...
  </class>
</hibernate-mapping>

HQL:

Code:

SELECT p FROM Person p JOIN p.user u WHERE u.socialSecurityNumber = ? AND p.firstName = ? AND p.lastName = ?

Criteria:

Code:

Criteria crit = session.createCriteria(Person.class, "p").createAlias("p.user", "u").add(Restrictions.eq("u.socialSecurityNumber", ssn)).add(Restrictions.eq("p.firstName", firstName)).add(Restrictions.eq("p.lastName", lastName));

I hope I got everything here -- I had something very similar in my app but I needed to hack it up a bit for posting. Good luck.
Curtis ...