Hi! I hope someone can give me some advice with having multiple databases for one EJB application. The problem is that each customer that sign up will get a "private" database with only the customers data. I have not seen any other solution to this than deploy the same classes again but with different deployment descriptors (persistence.xml) and a different name. The estimated customer count is around (today) 5000 customers with their own database. But if that is the only solution, is that a good solution?
I tried to add all different databases in the persistence.xml and dynamically choose (in runtime) which context to use. But you cannot have static methods on an annotation (PersistenceContext), the idea was to get the name of the context from a method. But the application will have to be redeployed after modifying the persistence.xml descriptors.
Can this be achieved in some other way? What I have understood can an extended persistence context solve this kind of problem. But I want to stay away from it as long as I can.
Instead of separate databases, use separate schemas within a single database.
All you'd need to do then is change the SQL queries for each customer to use their specific schema name (if the database user you use has access to all schemas).
I'm not sure if EJB3 supports that directly in entity beans, but it's worth looking into (and if it doesn't maybe it's time to submit a feature request, as IMO it should).
Perhaps you should re-consider the 'private database per customer notion'. Why do u want to create even schemas per customer, where you can achieve the same fate using mere tables? At the end of the day, you need to let the customer the look and feel and control over data , as if they have a private database..rite? For that why create 1000's of schemas, dont mention about individual databases.
Instead try a good database design which can cater for the numerous customers.
Creating 1000's of schemas..per customer, doesn't make sense to me
No pain No gain;)
Now we have redesigned the the database schema to support more than one customer, it was the only right thing to do.
It was an old design (with different requirements) that was influencing the new design that lead to this issue...