Hibernate version:3.13

Name and version of the database you are using:MS SQL

I've been trying to figure out how to map a particular scenario, and i keep coming up blank. Maybe somebody out there has done this, or has worked with this sort of thing so they have an answer or a hint.

Class Client is mapped to a Client table.
Class CientPool contains an instance of a Client and a collection of other Clients to support a particular relationship.

Our tables consist of
Client with an identity/primary key clientID along with a bunch of other attributes.

Another table called ClientPool has 2 columns, clientID, and pooledClientID and the combination is unique.

Example Table below



So, I would load a ClientPool object with ID 3, and expect a Client Object and a collection of Client objects for IDs 4 and 5.

I prefer not to add another table with a single key column and join 3 tables as it seems unecessary, but i don't know how i might create a mapping file such that I could read/write/delete a ClientPool object with the above table layout.

Any suggestions?

thanks for any help.

Would something like

work for you? that would create a unique combination of client_id and pooled_client_ids. The only drawback based on ur original posting was that when you query for a clientpool with a specified id, you would get a list of clientpool objects , each of which would have the same clientid that was requested but a different clientpool id. So in my case my db looks like this



and a testcase like


was successful

You describe an approach to map it and load them individually. The table you describe is fine.

What I also need a ClientPool object that has a list of Clients in them, so that users of the class could remove/add clients from the collection and issue an update of the ClientPool object thereby modifying the list.

So for example, i have a ClientPool with the following methods:

(sets also)
Client getPrimaryClient()
void setPrimaryClient(Client client)
List <Client> getPooledClients()
void setPooledClients(List <Client> clients)


So, i would load the ClientPool for Client = 1, and then add or remove pooled Clients to it, and save it.

It's as if what ClientPool really represents is a reference to a single Client in the Client table along with a many to one relationship to it using the ClientPool table.

I think you may have a fundamental problem with your table design.

What you are describing certainly sounds like a classic many-many relationship between clients and client pools. What you have designed is the client table and the mapping table. I suggest you make a clientpool table with the pool id as the primary key and probably a description field. Then you can use a classic manyTomany mapping that will generate a collection of pools on your client side and a collection of clients on your pool side. You should have a client entity and a pool entity and Hibernate should generate a client table, a pool table and a client-pool mapping table for you.

You are quite correct, although clientpool really has no attributes of it's own even though you suggest a description, but i wouldn't even know what to call it.

In the classic approach, as you've described it ( I think), i would have 3 tables roughly as follows: (but I really need the Pool table to simpy use the ClientID as the primary key, and also map it to a reference to the Client Object)



I'm only left with figuring out how to map Pool to ClientPool such that the primary key is an entity referencing a Client instance in Client Table.

Okay, so in the above scenario Client1 has a ClientPool that contains Clients 2 and 3.

Client 4, has a ClientPool that contains Clients 1 and 5

I think that is what you are describing to do above? and yes, that is classic.

My only problem would be in convincing my DBA that I needed 3 tables where 2 logically handles it (in his mind). The reality is that i don't need a description only a unique ClientID I can get what i need from 2 tables. If i want to get a list of clients (IDs) in a particular clients pool, the 2 table approach works fine, i simply select ClientIDs from the mapping table.

I'm fine with doing it the classic way (more db access), but i was hoping there was a way that would obviate my need to lobby for it with the DBA, who doesn't know java, or ever heard of hibernate ... and will have a hard time understanding why we need to join 3 tables and not 2. Thus my original question.

I suppose I could write my own persisting class, and instantiate the ClientPool object with my own select from the mapping table, but then i think i'd have to manage the persistance of that collection myself. sigh ... i guess this is the price to pay to allow Hibernate to do that for me.

Perhaps there is no other way.

Thank you both for you input - i'll either move forward in the classic approach, or write more code ....

By the way, it may make sense for me to describe my real motivation and what i'm running into. Maybe there is a way to solve my initial problem.

I could very easily place the collection (ClientPool) in the Client object and map it there. I would then have my 2 tables, and everything is fine.

My problem here (and maybe somebody can comment) is that when I load the Client object i do not (in 99 % of the cases) want to load that collection. I really don't want the Client object to be aware of it. This is because the Client Object is pretty heavy, and i dont want to deal with delivering the object to a remote client as a proxy when the remote client isn't interested in it, and will never want to update it.

When the client returns a copy back to the server and i do a merge, i need that collection to be ignored (neither updated, inserted, or anything)

Best way i thought to handle that was to have no reference to it at all, and to create this relationship outside of the Client class.

Thus my problem ... why do things that appear so simple end up being difficult to implement? business as usual.

Ok, more context is helpful.

So your clientpool collection in client loads when you do a merge even if it is a lazy intantiation? I was thinking that the lazy load option would have the effect you want, but I could be wrong there.

I see two other options for your mapping --

If client pool is just a filter, doesn't your DBA have a codes table you can use? You might be able to implement Clientpool as a kind of code, then use the classic many-many approach. That should keep the DBA and Hibernate both happy.

Otherwise, you could consider a 1-1 mapping between your client and your client with the clientpool collection. That's still 3 tables though -- and probably not as nice as a clientpool table.

HIbernate makes some assumptions about your ERD mapping & when you try something different it gets complicated.