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.
Login
FAQ
Search
