2

The company I am working for stores their client data in a separate database schema for each client. They indicate that this cannot be changed at this time. Is there an efficient way to pull data and update data in all schemas without configuring a connection for each schema? Everything I can find when I search seems to be talking about using one or a couple of schemas, but I need to use many (100+) simultaneously.

Improve this question
1

2 Answers 2

Reset to default
1

In any given persistence context, each JPA entity class is mapped to a specific base table. Whether and how easily you can access multiple schemas via a single DB connection is a function of your DBMS, your JDBC driver, and perhaps your particular database, but even a combination that in general supports the kind of access you would need will still not allow you to map the same entity class to multiple distinct base tables in the same persistence context.

You might be able to use the same entity classes for different clients by associating a different persistence context with each client, but that will not allow you use the same DB connection for all of them. Thus, if using the same connection were possible for you at all, it would require different entity classes per client.

Improve this answer
2
  • Thanks for the clear response. It looks like my best bet in this case will be separate DB connections serving each client schema. Do you have any insight to how much of a toll this would take scaling up to 100 clients or more? I also wonder if spawning and closing of connections in smaller batches would be better?
    – user123959
    Commented Jan 9, 2018 at 21:24
  • @user123959, Performance questions are best answered by careful experimentation. Accurate performance predictions are notoriously difficult.
    – John Bollinger
    Commented Jan 9, 2018 at 21:28
0

Have you considered creating a new DB user and creating SYNONYMS for each of the tables in the separate database schemas ?

You could then map JPA entitys to the SYNONYM names that you have created..

Using this approach you could still use the one DB connection but with SYNONYMS to the DB tables in the other schemas...

Improve this answer
4
  • Mappings from entity classes to tables can already be customized in the persistence context configuration, so I don't see how synonyms (in DBMSs that support them) add anything new. Perhaps you could clarify?
    – John Bollinger
    Commented Jan 8, 2018 at 16:45
  • Hi John No nothing to clarify I just saw it as a handy solution to "without configuring a connection for each schema" ...
    – mkane
    Commented Jan 8, 2018 at 17:37
  • Just to clarify, would synonyms still require separate entity classes for each schema?
    – user123959
    Commented Jan 9, 2018 at 21:25
  • I do not think that you would require separate entity classes . I think you could use the HibernateInterceptor and some creative code to dynamically refer to the synonyms ...
    – mkane
    Commented Jan 10, 2018 at 10:34

Not the answer you're looking for? Browse other questions tagged or ask your own question.