Newbie question - Join to another table.

mnsweeps · **Joined:** Tue Oct 17, 2006 10:57 am **Posts:** 14

Lets say I have a table
CUSTOMER with 3 columns CUSTOMER_ID, NAME , STATUS_ID. The STATUS_ID maps to LOOKUP table which has 2 columns LOOKUPTYPE_ID, LOOKUP_ID , DESCRIPTION.

I know the LOOKUPTYPE_ID is 100 for STATUS_ID . So all I need to do is join the STATUS_ID to LOOKUP_ID and grab the DESCRIPTION.

My java mapping class looks like this.

customer
{
customer_id,
name,
status_id,
status_description.

}

How do i map the status_description to the lookup description column? or whats the strategy to be used. Currently the HBM mapping file is simple as

<class name="customer" table="customer">
<id name="id" column="customer_id">
<generator class="native"/>
</id>
<property name="name" type="name"/>
<property name="status_id" column="status_id"/>

</class>

What should I do to map the status_description to the lookup description column?

SORRY FOR THE NOVICE QUESTION..I am just starting on this.

Thanks

CWitherow · **Posted:** Tue Oct 17, 2006 11:25 am

The most likely approach is to have a mapping and POJO for each table. The CUSTOMER mapping/pojo would have a many-to-one relationship to LOOKUP

Customer.java

Code:

public class Customer {
  private Long id;
  private String name;
  private Lookup status;
  // Getters/Setters/Constructors ...
}

Lookup.java

Code:

public class Lookup {
  private Long id;
  private String description;
  // Getters/Setters/Constructors ...
}

Customer.hbm.xml

Code:

<class name="Customer" table="customer">
  <id name="id" column="customer_id" type="long">
    <generator class="native"/>
  </id>
  <property name="name" column="name" type="string" />
  <many-to-one name="status" column="status_id" class="Lookup" />
</class>

Lookup.hbm.xml

Code:

<class name="Lookup" table="lookup">
  <id name="id" column="lookup_id" type="long">
    <generator class="native"/>
  </id>
  <property name="description" type="description" type="string" />
</class>

Code to reference lookup description (after Customer object has been retrieved)

Code:

customer.getStatus().getDescription()

Curtis ...

jhg · **Joined:** Mon Oct 23, 2006 11:03 pm **Posts:** 17

I tried this pattern, but when inserting records in the primary (not lookup)
table I get a constraint violation because Hibernate is trying to insert
null for the foreign key to the lookup.

Error:

Code:

Hibernate: 
    /* get current state com.wholefoods.ittoolkit.ws.code.ReqState */ select
        reqstate_.REQ_STATE_ID,
        reqstate_.DESCRIPTION as DESCRIPT2_12_ 
    from
        ITTOOLKIT.CODE_REQ_STATE reqstate_ 
    where
        reqstate_.REQ_STATE_ID=?
Hibernate: 
    /* get current state com.wholefoods.ittoolkit.ws.ccf.ReqTeam */ select
        reqteam_.TEAM_ID,
        reqteam_.NAME as NAME6_ 
    from
        ITTOOLKIT.CR_TEAM reqteam_ 
    where
        reqteam_.TEAM_ID=?
Hibernate: 
    /* insert com.wholefoods.ittoolkit.ws.ccf.Request
        */ insert 
        into
            ITTOOLKIT.CR_REQUEST
            (VERSION, ALT_ID, REQ_STATE_ID, BREAK_FIX, TITLE, DESCRIPTION, URGENCY, REQ_TEAM_ID, REQ_USER_ID, TEST_USER_ID, PROJECT_NAME, START_DATE, SCHED_DATE, MIG_PATH_FROM, MIG_PATH_TO, ON_COMPL_NOTIFY, CHANGE_DOC, TEST_DOC, BACKOUT_DOC, CHANGE_TS, CHANGE_USER, REQ_ID) 
        values
            (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
11:48:15,765  WARN org.hibernate.util.JDBCExceptionReporter:77 - SQL Error: 1400, SQLState: 23000
11:48:15,781 ERROR org.hibernate.util.JDBCExceptionReporter:78 - ORA-01400: cannot insert NULL into ("ITTOOLKIT"."CR_REQUEST"."REQ_STATE_ID")

11:48:15,781  WARN org.hibernate.util.JDBCExceptionReporter:77 - SQL Error: 1400, SQLState: 23000
11:48:15,781 ERROR org.hibernate.util.JDBCExceptionReporter:78 - ORA-01400: cannot insert NULL into ("ITTOOLKIT"."CR_REQUEST"."REQ_STATE_ID")

Main table mapping:

Code:

<hibernate-mapping package="com.wholefoods.ittoolkit.ws.ccf">
    <class name="Request" table="CR_REQUEST">
    
        <id name="reqId" column="REQ_ID" type="long">
            <generator class="sequence">
                <param name="sequence">CR_REQUEST_ID</param>
            </generator>
        </id>
        .
        .
        .
        <many-to-one name="reqState"      column="REQ_STATE_ID" class="com.wholefoods.ittoolkit.ws.code.ReqState" />
        .
        .
        .
    </class>
</hibernate-mapping>

Lookup table mapping:

Code:

<hibernate-mapping package="com.wholefoods.ittoolkit.ws.code">
  <class name="ReqState" table="CODE_REQ_STATE">
    <id name="id" column="REQ_STATE_ID"/>
    <property name="description" column="DESCRIPTION" not-null="true" />
  </class>
</hibernate-mapping>

Calling Java:

Code:

        Session sess = HibernateUtil.getSessionFactory().openSession();
        Transaction  tx = sess.beginTransaction();
        
        Request req = new Request();
        .
        .
        .
        req.setReqState(new ReqState(1));
        .
        .
        .
        sess.save(req);
        tx.commit();

Request.java

Code:

package com.wholefoods.ittoolkit.ws.ccf;

import java.util.*;

import com.wholefoods.ittoolkit.ws.code.ReqState;

public class Request
{
    .
    .
    .
    private ReqState    reqState        = null;
    .
    .
    .
    public ReqState getReqState()
    {
        return reqState;
    }
    public void setReqState(ReqState reqState)
    {
        this.reqState = reqState;
    }
    .
    .
    .
}

ReqState.java

Code:

package com.wholefoods.ittoolkit.ws.code;

public class ReqState
{
    private int     id              = 0;
    private String  description     = null;
    
    public ReqState() { }
    
    public ReqState(int id)
    {
        this.id          = id;
    }
    
    public ReqState(int id, String description)
    {
        this.id          = id;
        this.description = description;
    }
    .
    .
    .
}

jhg · **Joined:** Mon Oct 23, 2006 11:03 pm **Posts:** 17

I tried adding not-null=true to the many-to-one reference from
Request to ReqState,

Code:

        <many-to-one name="reqState"      column="REQ_STATE_ID" not-null="true" class="com.wholefoods.ittoolkit.ws.code.ReqState" />

but that changed the error to:

Code:

org.hibernate.PropertyValueException: not-null property references a null or transient value: com.wholefoods.ittoolkit.ws.ccf.Request.reqState

Stepping through the code in Eclipse's debugger shows that
Request.reqState is most certainly NOT null at the point where
the save() and commit() are called.

Obviously I must be missing something simple in my mapping definition.
Can anyone help?