Table join on ID with WHERE specifier

rherrick · **Posted:** Tue Oct 25, 2005 5:24 pm

I'm sorry if this is a stupid question, but I have not been able to find an example of this anywhere. I'm very much a Hibernate novice, so even basic things are at my current knowledge horizon. If there is a simple example of it, you can just point me there and tell me to shut up :)

I have two tables containing employee information. One is standard stuff, name, title, phone, etc. In another table, I have employee role information. This is many-to-one, multiple roles to each employee. The key relationship is on employee ID. So that's pretty straightforward, like this:

Code:

Table EMPLOYEE:
EMPLOYEEID
NAME
etc...

Table EMPLOYEEROLE:
EMPLOYEEID
ROLE
ACTIVE

To find all the roles for a particular user, the SQL is just this:

Code:

select ROLE from EMPLOYEEROLE where EMPLOYEEID='xxx' and ACTIVE='Y'

The problem comes in that roles can be active or inactive based on a yes_no flag in the role table. I only care about active roles.

So what I'd like to have is this:

Code:

public class Employee
{
   public Integer getId();
   public void setId(Integer id);
   public String getName();
   public void setName(String name);
   public List getRoles();
   public void setRoles(List roles);
}

Then, when I call getRoles(), I get only those roles where employee ID is the join criteria and the ACTIVE column is 'Y'.

So for an example, let's say you've got this row in the EMPLOYEE table:

Code:

1 Bob

Then in EMPLOYEEROLE, you've got these:

Code:

1   Admin       N
1   Editor      Y
1   Writer      Y
1   Proof       N

If this works the way I'd likst, once I get the Employee object for employee ID 1, I can call getName() and it'll return Bob and I can call getRoles() and it'll return a List containing "Editor" and "Writer" (and not containing Admin and Proof, since these are filtered out because of the 'N' ACTIVE code).

How in the world do I do this? I'm poking desperately through Hibernate in Action and I can't figure it out! Any help would be greatly appreciated and will surely be rewarded soon after posting by a stranger purchasing you your favorite food item :)

rherrick · **Posted:** Tue Oct 25, 2005 5:30 pm

I'm using Hibernate 2.0, I believe, not 3.

massep · **Joined:** Thu Aug 19, 2004 9:28 am **Posts:** 63

rherrick wrote:

I'm sorry if this is a stupid question, but I have not been able to find an example of this anywhere. I'm very much a Hibernate novice, so even basic things are at my current knowledge horizon. If there is a simple example of it, you can just point me there and tell me to shut up :)

I have two tables containing employee information. One is standard stuff, name, title, phone, etc. In another table, I have employee role information. This is many-to-one, multiple roles to each employee. The key relationship is on employee ID. So that's pretty straightforward, like this:

Code:

Table EMPLOYEE:
EMPLOYEEID
NAME
etc...

Table EMPLOYEEROLE:
EMPLOYEEID
ROLE
ACTIVE

To find all the roles for a particular user, the SQL is just this:

Code:

select ROLE from EMPLOYEEROLE where EMPLOYEEID='xxx' and ACTIVE='Y'

The problem comes in that roles can be active or inactive based on a yes_no flag in the role table. I only care about active roles.

So what I'd like to have is this:

Code:

public class Employee
{
   public Integer getId();
   public void setId(Integer id);
   public String getName();
   public void setName(String name);
   public List getRoles();
   public void setRoles(List roles);
}

Then, when I call getRoles(), I get only those roles where employee ID is the join criteria and the ACTIVE column is 'Y'.

So for an example, let's say you've got this row in the EMPLOYEE table:

Code:

1 Bob

Then in EMPLOYEEROLE, you've got these:

Code:

1   Admin       N
1   Editor      Y
1   Writer      Y
1   Proof       N

If this works the way I'd likst, once I get the Employee object for employee ID 1, I can call getName() and it'll return Bob and I can call getRoles() and it'll return a List containing "Editor" and "Writer" (and not containing Admin and Proof, since these are filtered out because of the 'N' ACTIVE code).

How in the world do I do this? I'm poking desperately through Hibernate in Action and I can't figure it out! Any help would be greatly appreciated and will surely be rewarded soon after posting by a stranger purchasing you your favorite food item :)

I don't know if this works in H2 but it does in H3. When you map your association between Employee and EmployeeRole, use the WHERE attribute of the set tag <set... where="ROLE=Y">

P.S.: You can implement many mapping from one table to another so, you could call this one activeRoles.

Cheers.