-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 3 posts ] 
Author Message
 Post subject: Table join on ID with WHERE specifier
PostPosted: Tue Oct 25, 2005 5:24 pm 
Newbie

Joined: Tue Oct 25, 2005 5:00 pm
Posts: 3
Location: Long Beach, CA
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 :)

_________________
Thx,
Rick


Top
 Profile  
 
 Post subject: BTW, Hibernate 2 not 3
PostPosted: Tue Oct 25, 2005 5:30 pm 
Newbie

Joined: Tue Oct 25, 2005 5:00 pm
Posts: 3
Location: Long Beach, CA
I'm using Hibernate 2.0, I believe, not 3.

_________________
Thx,
Rick


Top
 Profile  
 
 Post subject: Re: Table join on ID with WHERE specifier
PostPosted: Thu Nov 24, 2005 4:16 pm 
Regular
Regular

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.


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 3 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.