I have two mysql tables, person and personoptional. At some stage I want to check the value of 'isemployee' column (it can be 0 or 1). I have written the query below and it is quite right, but i am having trouble in writing it in the .hbm file. Can someone please guide me how to do it.

CREATE TABLE IF NOT EXISTS person(
personid int auto_increment NOT NULL,
firstname varchar (50) NOT NULL,
lastname varchar (50) NULL,
PRIMARY KEY (personid)
) TYPE = INNODB;

CREATE TABLE IF NOT EXISTS personoptional (
personoptionalid int auto_increment NOT NULL,
personid int NOT NULL,
isemployee tinyint DEFAULT 0 NOT NULL,
joiningdate dateTime NOT NULL,
PRIMARY KEY (personoptionalid),
index(personid),
FOREIGN KEY (personid) REFERENCES person (personid) ON DELETE CASCADE ON UPDATE CASCADE
) TYPE = INNODB;


<!-- Person.hbm.xml -->
<hibernate-mapping package="com.test">
<class name="Person" table="person">
<id name="personId" type="java.lang.Integer" column="personid">
<generator class="increment"/>
</id>
<property name="firstName" column="firstname" type="java.lang.String" update="true"/>
<property name="lastName" column="lastname" type="java.lang.String" update="true"/>
</class>
</hibernate-mapping>

<!-- PersonOptional.hbm.xml -->
<hibernate-mapping package="com.test">
<class name="PersonOptional" table="personoptinal">
<id name="personOptionalId" type="java.lang.Integer" column="personoptionalid">
<generator class="increment"/>
</id>
<property name="personId" column="personid" type="java.lang.Integer"/>
<property name="isEmployee" column="isemployee" type="java.lang.Integer" update="true"/>
<property name="joiningDate" column="joiningdate" type="java.util.Date" update="false"/>
</class>
</hibernate-mapping>

String queryString = " select p from Person as p, PersonOptional as po " +
"where p.personId = po.personId and po.isEmployee = 1 " +
"p.firstName=Mike and p.lastName=Tompkins";

You need to reference the FK in your mapping. For example, you could put the following mapping in your PersonOptional.hbm.xml:


<many-to-one name="person" class="com.test.person" fetch="join">
<column name="personid" />
</many-to-one>


You will need to add a property in your PersonOptional class called "person" referencing the person object.

Then code your query something like this:

String queryString = " select po.person from PersonOptional as po " +
"where po.isEmployee = 1 " +
"po.person.firstName=Mike and po.person.lastName=Tompkins";


Paul

Thank you Paul. I have done it the way you suggested and it works now.

You're welcome, maybe you can hook me up with the credit when you have a chance :)


There should be a link at the bottom of my post.


Thanks,

Paul