Hi everybody

I have 4 table agency,title, employee and agency_employee . Since and an employee can work at multiple agencies and agencies can have multiple employee. I have a link table to get associated rows . Now for each agency the employee he/she works at . He/she can have a different
title.
I have tried separated and was able to get the many to many realtionship working. but when I tried to so the title for them in the link table I get an error saying it cannot insert a null value for title . Although I can see that the title object as been created successfully . I was wondering if any body could point me out in the right direction. This is what i have got so far.....
What do i need to do so that it associates the title to the link table ?
Thank you
-Reeve


Hibernate version: 3.0.1.

Mapping documents:
<hibernate-mapping package="orm">

<class name="PdAgencies" table="PD_AGENCIES">
<id name="agyLicenseId" column="AGY_LICENSE_ID" type="java.lang.Long">
<generator class="sequence">
<param name="sequence">PD_AGY_SEQ</param>
</generator>
</id>

<property name="maxUsers" column="MAX_USERS" type="java.lang.Long" not-null="true" />
<property name="maxGroups" column="MAX_GROUPS" type="java.lang.Long" not-null="true" />
<property name="nmeTrade" column="NME_TRADE" type="java.lang.String" />
<property name="nmeCorp" column="NME_CORP" type="java.lang.String" not-null="true" />
<property name="feinNum" column="FEIN_NUM" type="java.lang.String" />
<property name="bondExpDte" column="BOND_EXP_DTE" type="java.util.Date" />
<property name="flgLicRetInd" column="FLG_LIC_RET_IND" type="java.lang.Byte" not-null="true" />
<property name="flgLocked" column="FLG_LOCKED" type="java.lang.Byte" />
<property name="cdeBusType" column="CDE_BUS_TYPE" type="java.lang.String" not-null="true" />
<property name="cdeAgyType" column="CDE_AGY_TYPE" type="java.lang.String" not-null="true" />
<property name="faxNum" column="FAX_NUM" type="java.lang.String" />
<property name="email" column="EMAIL" type="java.lang.String" />
<property name="dteCreated" column="DTE_CREATED" type="java.util.Date" update="false" not-null="true" />
<property name="idLogonCreated" column="ID_LOGON_CREATED" type="java.lang.String" not-null="true" />
<property name="dteLastUpdated" column="DTE_LAST_UPDATED" type="java.util.Date" not-null="true" />
<property name="idLogonLastUpdated" column="ID_LOGON_LAST_UPDATED" type="java.lang.String" not-null="true" />


<!-- ############################################################################################
#
# BI - Directional Association
#
#################################################################################################-->

<!-- We use a one-to-many association to express the relationship
to a set of items. There is an intermediate entity class,
AgencyEmployee, which in fact makes this a many-to-many
association between Agency and Employee. -->



<set name="agencyUsers" cascade="all-delete-orphan" inverse="true" outer-join="false" lazy="true">
<key foreign-key="PAE_PA_FK">
<column name="AGY_LICENSE_ID" not-null="true" length="10"/>
</key>
<one-to-many class="PdAgencyEmployees"/>
</set>


</class>

</hibernate-mapping>


PDEmployee
============
<hibernate-mapping package="orm">

<class name="PdEmployees" table="PD_EMPLOYEES">
<id name="empId" column="EMP_ID" type="java.lang.Long">
<generator class="sequence">
<param name="sequence">PD_EMP_SEQ</param>
</generator>

</id>

<property name="firstName" column="FIRST_NAME" type="java.lang.String" />
<property name="lastName" column="LAST_NAME" type="java.lang.String" not-null="true" />
<property name="middleInitial" column="MIDDLE_INITIAL" type="java.lang.String" />
<property name="dob" column="DOB" type="java.util.Date" not-null="true" />
<property name="ssn" column="SSN" type="java.lang.String" not-null="true" />
<property name="flgSecOff" column="FLG_SEC_OFF" type="java.lang.Byte" not-null="true" />
<property name="cdeGender" column="CDE_GENDER" type="java.lang.String" not-null="true" />
<property name="cdeRace" column="CDE_RACE" type="java.lang.String" />
<property name="photo" column="PHOTO" type="java.lang.String" />
<property name="cdeTrainLvl" column="CDE_TRAIN_LVL" type="java.lang.String" />
<property name="address" column="ADDRESS" type="java.lang.String" />
<property name="cdeCounty" column="CDE_COUNTY" type="java.lang.String" />
<property name="city" column="CITY" type="java.lang.String" />
<property name="state" column="STATE" type="java.lang.String" />
<property name="zipCode" column="ZIP_CODE" type="java.lang.String" />
<property name="zip4" column="ZIP4" type="java.lang.String" />
<property name="dteCreated" column="DTE_CREATED" type="java.util.Date" not-null="true" />
<property name="idLogonCreated" column="ID_LOGON_CREATED" type="java.lang.String" not-null="true" />
<property name="dteLastUpdated" column="DTE_LAST_UPDATED" type="java.util.Date" not-null="true" />
<property name="idLogonLastUpdated" column="ID_LOGON_LAST_UPDATED" type="java.lang.String" not-null="true" />


<set name="agencyUsers"
cascade="all-delete-orphan"
inverse="true"
lazy="true">

<key foreign-key="PUA_PAF_FK">
<column name="APP_FUNC_ID" not-null="true" length="10"/>
</key>
<one-to-many class="PdUserGrpAppFunc"/>
</set>
</class>

</hibernate-mapping>

PdTitles
=========
<hibernate-mapping package="orm">

<class name="PdTitles" table="PD_TITLES">
<id name="titleId" column="TITLE_ID" type="java.lang.Short">
<generator class="sequence">
<param name="sequence">PD_TITLE_SEQ</param>
</generator>
</id>

<property name="cdeTitle" column="CDE_TITLE" type="java.lang.String" not-null="true" />
<property name="title" column="TITLE" type="java.lang.String" not-null="true" />
<property name="titleDesc" column="TITLE_DESC" type="java.lang.String" />


<!-- create a link to the agency employee table -->
<set name="agencyEmployees" cascade="all" inverse="true" lazy="true">
<key column="TITLE_ID"/>
<one-to-many class="PdAgencyEmployees"/>
</set>
</class>
</hibernate-mapping>



AgencyEmployee
============
<hibernate-mapping package="orm">

<class name="PdAgencyEmployees" table="PD_AGENCY_EMPLOYEES">
<!--
<composite-id name="id" class="PdAgencyEmployeesKey">
<key-many-to-one name="pdAgencies" column="AGY_LICENSE_ID" class="PdAgencies"/>
<key-many-to-one name="pdEmployees" column="EMP_ID" class="PdEmployees"/>
</composite-id>
-->
<composite-id name="id" class="PdAgencyEmployees$Id" unsaved-value="any">
<key-property name="agencyId"
access="field"
column="AGY_LICENSE_ID"
length="10"/>

<key-property name="employeeId"
access="field"
column="EMP_ID"
length="10"/>

</composite-id>


<property name="dteCreated" column="DTE_CREATED" type="java.util.Date" not-null="true" />
<property name="idLogonCreated" column="ID_LOGON_CREATED" type="java.lang.String" not-null="true" />
<property name="dteLastUpdated" column="DTE_LAST_UPDATED" type="java.util.Date" not-null="true" />
<property name="idLogonLastUpdated" column="ID_LOGON_LAST_UPDATED" type="java.lang.String" not-null="true" />

<!-- changed pdTitles from object to set and vice versa
PdTitles pdTitles == > PdTitles pdTitle
& added Set pdTitles so chnaged teh assocaietion from a object to source
<many-to-one name="pdTitles" column="TITLE_ID" class="PdTitles" not-null="true" />
-->
<many-to-one name="title" column="TITLE_ID" class="PdTitles" not-null="true" insert="false" update="false"/>

</class>

</hibernate-mapping>




Code between sessionFactory.openSession() and session.close():
public static void main(String[] args) {

BasicConfigurator.configure();
try {
unit un= new unit();
un.setSession(HibernateSessionFactory.currentSession());
Transaction tx = session.beginTransaction();


// create agency
PdAgencies agency = createAgency();
// save it


//create employee
PdEmployees employee = createEmployee();

// PdAgencyEmployees agencyEmployee = createEmployeeAgencyAssociation(agency,employee);
//create title
PdTitles title = createTitle();




//save it
session.save(agency);

session.save(employee);
//session.save(title);

session.save(title);
session.flush();

// create group and function linky

PdAgencyEmployees agencyEmployee = createEmployeeAgencyAssociation(agency,employee);
agencyEmployee.setPdTitle(title);
System.out.println("Done with assocaition");

createAgencyEmployeeTitleAssociation(agencyEmployee,title);


session.save(agencyEmployee);

session.flush();





tx.commit();
un.getSession().close();


} catch (HibernateException e) {
e.printStackTrace();
}
}

/**
* @param agencyEmployee
* @param title
*/
private static void createAgencyEmployeeTitleAssociation(PdAgencyEmployees agencyEmployee, PdTitles title) {
agencyEmployee.setPdTitle(title);
title.addAgencyEmployee(agencyEmployee);

}

/**
* @param agency
* @param employee
* @return
*/
private static PdAgencyEmployees createEmployeeAgencyAssociation(PdAgencies agency, PdEmployees employee) {
System.out.println("############# creating employee AGENCY FOR AN ASSOCAITOOPN #############");
PdAgencyEmployees assocation = new PdAgencyEmployees(employee,agency);
assocation.setDteCreated(new Date());
assocation.setDteLastUpdated(new Date());
assocation.setIdLogonCreated("id");
assocation.setIdLogonLastUpdated("idupdate");
return assocation;
}

/**
* @return
*/
private static PdEmployees createEmployee() {
System.out.println("############# creating employee#############");
PdEmployees employee = new PdEmployees();
employee.setCdeGender("M");
employee.setDob(new Date());
employee.setFlgSecOff(new Byte("0"));
employee.setFirstName("John");
employee.setLastName("Doe");
employee.setSsn("123-23-3011");

employee.setDteCreated(new Date());
employee.setDteLastUpdated(new Date());
employee.setIdLogonCreated("id");
employee.setIdLogonLastUpdated("idupdate");
return employee;
}

/**
* @return
*/
private static PdTitles createTitle() {
System.out.println("############# creating title #############");
PdTitles title = new PdTitles();
title.setCdeTitle("OFF");
title.setTitle("OFFICER");
return title;
}









private static PdAgencies createAgency()
{
GregorianCalendar gc = new GregorianCalendar();


System.out.println("############# creating agency #############");
PdAgencies agency = new PdAgencies();

// required attributes
agency.setCdeAgyType("DET");
agency.setCdeBusType("D");
agency.setDteCreated(new java.sql.Timestamp(gc.getTime().getTime()));
agency.setDteLastUpdated(new java.sql.Timestamp(gc.getTime().getTime()));
//agency.setDteLastUpdated(new Date());
agency.setFlgLicRetInd(new Byte("0"));

agency.setIdLogonCreated("zxyz1234011");
agency.setIdLogonLastUpdated("zxyz1234011");

agency.setMaxGroups(new Long(3));
agency.setMaxUsers(new Long(5));

agency.setNmeCorp("CorpNm");

//nullable fileds
agency.setNmeTrade("TradeNm");
agency.setFeinNum("12345");
agency.setBondExpDte(new Date());
agency.setFlgLocked(new Byte("0"));
agency.setFaxNum("1234567890");
agency.setEmail("norton@yahoo.com");

return agency;
}




/**
* @return Returns the session.
*/
public Session getSession() {
return session;
}
/**
* @param session The session to set.
*/
public static void setSession(Session session) {
unit.session = session;
}



Full stack trace of any exception that occurs:

Name and version of the database you are using:
Oracle 9.2.0.1

The generated SQL (show_sql=true):
Hibernate: select PD_AGY_SEQ.nextval from dual
Hibernate: select PD_EMP_SEQ.nextval from dual
Hibernate: select PD_TITLE_SEQ.nextval from dual
Hibernate: insert into PD_AGENCIES (MAX_USERS, MAX_GROUPS, NME_TRADE, NME_CORP, FEIN_NUM, BOND_EXP_DTE, FLG_LIC_RET_IND, FLG_LOCKED, CDE_BUS_TYPE, CDE_AGY_TYPE, FAX_NUM, EMAIL, DTE_CREATED, ID_LOGON_CREATED, DTE_LAST_UPDATED, ID_LOGON_LAST_UPDATED, AGY_LICENSE_ID) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into PD_EMPLOYEES (FIRST_NAME, LAST_NAME, MIDDLE_INITIAL, DOB, SSN, FLG_SEC_OFF, CDE_GENDER, CDE_RACE, PHOTO, CDE_TRAIN_LVL, ADDRESS, CDE_COUNTY, CITY, STATE, ZIP_CODE, ZIP4, DTE_CREATED, ID_LOGON_CREATED, DTE_LAST_UPDATED, ID_LOGON_LAST_UPDATED, EMP_ID) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into PD_TITLES (CDE_TITLE, TITLE, TITLE_DESC, TITLE_ID) values (?, ?, ?, ?)
Hibernate: insert into PD_AGENCY_EMPLOYEES (DTE_CREATED, ID_LOGON_CREATED, DTE_LAST_UPDATED, ID_LOGON_LAST_UPDATED, AGY_LICENSE_ID, EMP_ID) values (?, ?, ?, ?, ?, ?)
08:45:34,562 WARN JDBCExceptionReporter: SQL Error: 1400, SQLState: 23000
5344 [main] WARN org.hibernate.util.JDBCExceptionReporter - SQL Error: 1400, SQLState: 23000
08:45:34,562 ERROR JDBCExceptionReporter: ORA-01400: cannot insert NULL into ("SCOTT"."PD_AGENCY_EMPLOYEES"."TITLE_ID")



Debug level Hibernate log excerpt:
4266 [main] DEBUG org.hibernate.pretty.Printer - listing entities:
4266 [main] DEBUG org.hibernate.pretty.Printer - orm.PdAgencyEmployees{idLogonLastUpdated=idupdate, title=null, idLogonCreated=id, dteCreated=2005-03-10 08:47:21, dteLastUpdated=2005-03-10 08:47:21, id=component[agencyId,employeeId]{agencyId=7, employeeId=42}}
4266 [main] DEBUG org.hibernate.pretty.Printer - orm.PdTitles{cdeTitle=OFF, titleId=44, title=OFFICER, agencyEmployees=[orm.PdAgencyEmployees#component[agencyId,employeeId]{agencyId=7, employeeId=42}], titleDesc=null}
4266 [main] DEBUG org.hibernate.pretty.Printer - orm.PdAgencies{addresses=[], idLogonLastUpdated=zxyz1234011, flgLocked=0, nmeCorp=CorpNm, branches=[], bondExpDte=2005-03-10 08:47:21, dteLastUpdated=2005-03-10 08:47:21, nmeTrade=TradeNm, flgLicRetInd=0, phones=[], feinNum=12345, groups=[], faxNum=1234567890, maxUsers=5, maxGroups=3, idLogonCreated=zxyz1234011, dteCreated=2005-03-10 08:47:21, cdeAgyType=DET, email=norton@yahoo.com, cdeBusType=D, agencyUsers=[orm.PdAgencyEmployees#component[agencyId,employeeId]{agencyId=7, employeeId=42}], agyLicenseId=7}



[/code]

i fixed my problem. There was an error in the mapping for the link table