This question is a bit complicated for me to explain. So I will divide it in parts and try to draw some pictures.
Most questions that I’ve read before posting, especially on stackoverflow does not contemplate a certain subject, which is about relating a Class ‘A’ from Schema S’ to a Class ‘B’ from Schema X’
Current Configuration
- Mysql
- Java
- Hibernate
Explanation
I have 1 schema (call it S’) that contains tables which contains information that is accessed by several companies. Those companies can only Write/Alter their own defined Schemas. However, They access schema S’ to, for example, get a list of all users who can log-in to the system and are partners (which is done with a SQL query).
Therefore, I have a class on my app called User. This User class DOES NOT/SHOULD NOT exist in each company schema.
The picture below tries to illustrate the situation:
According to a few solutions in stackoverflow, a way to do this would be to have two different SessionFactory objects. One pointing to this shared schema and the other pointing to the schema that is owned by the company.
Problem
Suppose I have a Class called Book and the class User mentioned above, as such:
@Entity
public class Book {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private int id;
private String name;
@OneToOne
private User owner;
...
}
@Entity
@Table(schema = "S")
public Class User {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private int id;
private String username;
private String dateOfBirth;
....
}
If I follow the suggested solution, using two different SessionFactory, how can I persist class “Book” when it has a relationship with “User” ? The EntityManager wont persist the Object “User” that exists on the class “Book” because it wont be mapped on its SessionFactory.
if SessionFactory s1 is mapped to the shared schema “S” and SessionFactory s2 is mapped to the current company schema:
-
If i use the EntityManger from s1, it wont persist the class
“Book” since its not mapped. -
If I get an EntityManager from s2, the manager wont persist the
User Object.
What should I do to persist the class Book when it has a @OneToOne relationship to class User if the table Book is in schema X and the table User is in Schema ‘S’
Answer to friends 
- Are all the schemas for each company including S` stored in the same database instance or do you use multiple MySQL database instances?
Yes. Everything on the same database.
- How does the application know which company schema to use? Is that configured as a part of the application’s setup and you simply run multiple application instances where each instance is assigned to a given company or do you use something else?
This is how it works:
Every client/node has a .jar. When he/she opens the app, it connects to schema S’ asking for username and password. The Schema S’ has a table with the following columns : id, username, password, uniqueCompany (FK). This FK, is in a table on Schema S’ (as well) with the following columns: id, db_url, db_password, … and so on)
So basically, if the login is a success, the query* will return the database info that the user is supposed to use after that.
Since there are 1.n companies, there are several schemas.
To make it clear, the application knows which schema to use when the login is successfull.
If my english is a bit complicated, I can draw an illustration. But thanks a lot for the reply.
