Hello Ranchhands. To start off, I use the Java Persistence API (specifically Toplink Essentials) for the ORM layer, MySQL for the backend, and Servlet 2.5 for the frontend.
My application's requirement is that each user/client have their own database. Every one of these sql databases has the same exact same structure. When someone uses the service they provide an ID (namely as part of the uri, but it could be a form value) which is looked up in a registry and, if valid, is used as the SQL Database name for the connection.
Currently, I achieve this by overriding the toplink.jdbc.url property every time I create an EntityManager by passing a Map with that entry in it to Persistence.createEntityManagerFactory(String, Map). Although I use the same persistence unit, "user".
Now, it seems once that worked, we neglected to test if it would connect to the correct database, and not cache. And so, of course, that exact problem occurred during the public release. It turns out Toplink doesn't support multiple datasources per Persistence Unit.
My question to all of you out there is: How do you all handle multiple clients with multiple databases in JPA?
If I could get a reply, that would be incredibly helpful.