-->
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.  [ 2 posts ] 
Author Message
 Post subject: Mapping entities across two databases via a key not an id
PostPosted: Fri Aug 19, 2016 1:01 pm 
Newbie

Joined: Fri Aug 19, 2016 12:10 pm
Posts: 1
Looking to map entities across two databases and also provide persistence across them.
I have session factories to connect to each and queries to each works fine.
Lets reference the databases as db0 and db1 with the follow pseudo DB tables and entities:

Consider db0.Product as:
- Long id
- String description
- Set<Supplier> suppliers

Consider db1.Supplier as:
- Long id
- String key
- String description

Consider db2.Supplier as:
- Long id
- String key
- String description

Consider db0.joinPS as:
- Long productId
- String supplierKey


I'm trying to join Product and Supplier by mapping db0.joinPS.supplierKey to db1.Supplier.key. Normally this would be done on the same database across ids or keys, but in this case I only have the key on db0 as db0.joinPS.supplierKey (in fact there are many Supplier instances on different dbs [db2, db3, ..., db10] with different ids but the same keys). How can I resolve the full entity associated with that key on db1 (or db2, db3, ..., db10).

In this case the two sessionFactories will respectively point to db0 and whatever the active Supplier db is (db1, db2, db3, etc).


Example mapping (I know this won't work as described below it):
Code:
@ManyToMany(cascade=CascadeType.ALL)
@JoinTable(name="joinPS",
joinColumns={@JoinColumn(name="productId")},
inverseJoinColumns={@JoinColumn(name="supplierKey")})
private Set<Supplier> suppliers= new HashSet<Supplier>();
public Set<Supplier> getSuppliers() {return suppliers;}
public void setSuppliers(Set<Supplier> suppliers) {this.suppliers= suppliers;}


The problem is obviously the supplierKey cannot be the inverseJoinColumn and somehow resolve db{x}.Supplier.
How can I somehow map this key to db{x}.Supplier.key and map the db{x}.Supplier collection of entities into the db0.Product.suppliers?

I thought of trying to use @Formula, but I think it will force me onto the same sessionFactory as db0, not across them.
I need a way to reference the other session factory to the Supplier DB for joining of the key column, AND I need to map that key to the Supplier DB's key to resolve it's full entity.

Hopefully that makes sense, but if you have any questions, please let me know.

If you're asking yourself why in the world I'd want to do this...use case wise is because there are a bunch of Products that are common and rather than redundantly storing them at every Supplier DB, they are instead stored at the Product DB. Furthermore, each Supplier DB has custom Products, so there will also be a Products table on those Supplier DBs as well (just to complicate things more :)). Those custom Products will have their own entity class though with id to id mapping with the Supplier DB sessionfactory.


Top
 Profile  
 
 Post subject: Re: Mapping entities across two databases via a key not an id
PostPosted: Mon Aug 29, 2016 3:59 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1630
Location: Romania
I don't think that's going to work since the SessionFactory is bound to one database/schema only and the @JoinColumn only allows you to specify the table name, not the database or schema.

If you're using PostgreSQL, try with FOREIGN TABLE. Maybe it works for your use case.


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 2 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.