EJB 3 to Multiple Identical-Schema Databases

[Dave Manley]

I'm about to develop a JEE app, hopefully using EJB 3, in which a user will connect to one of 250+ databases. Yes it's a bad design, but it is what I inherited. All databases have the same schema, pl/sql, etc. The database which the user needs to log into is passed in with the login information.

Does anyone have a good suggestion on how this might be done?

Thanks in advance.

[Jeanne Boyarsky]

Dave,
I wouldn't use EJB for this. I would use regular JDBC (or a framework that facilitates it) and pass the information in at runtime. For EJB (2 or 3), you need to define the datasources in advance. I doubt you have all that information (including passwords) available. Also, EJB would need to create a new EJB for each of the 250 databases. Even if it is possible, this is a lot of redundancy.

[Jesus Angeles]

As mentioned, jdbc seems one the best solution. Look at using Spring layer for jdbc, to simplify the interaction with jdbc. That module in Spring shields you from error-prone low-levels details when using jdbc.

[Adam Tkaczyk]

If all your databases have the same schema I think the best solution is to write some sort of common contract for all business operations. The only think that would change is datasource. That approach reduce bad redundancy and makes your code more flexible

[Roger Chung-Wee]

I wouldn't use EJB for this. I would use regular JDBC (or a framework that facilitates it) and pass the information in at runtime. For EJB (2 or 3), you need to define the datasources in advance. I doubt you have all that information (including passwords) available.

I don't understand this. If 250 data sources need to be created, then it is a tedious job but it is only done once. Also, the passwords must surely be known, otherwise how can the connections be made to the DBs?

What we don't know is whether the application would benefit from connection pooling and considerable transaction management. If the answer is yes, then EJB must be a strong candidate solution.

[Jesus Angeles]

Originally posted by Adam Tkaczyk:
If all your databases have the same schema I think the best solution is to write some sort of common contract for all business operations. The only think that would change is datasource. That approach reduce bad redundancy and makes your code more flexible

When using jdbc for this, there wont be any redundancy. There will be a mapping between input database string, and the datasource parameters like password; which can be put in something like a properties file.

When using ejb 3 for this, there will at least be 1 set of domain classes, per database. If there are 30 tables on each database, that is at least 7500 classes. In the business codes, I am thinking if we can make a superclass of the domain set and use polymorphism when doing business calls.

Like,

Book = new Book001();
...

representing the first database.

book = em.find(Book.class,Integer.valueOf(bookId));

[Jeanne Boyarsky]

Originally posted by Roger Chung-Wee:
I don't understand this. If 250 data sources need to be created, then it is a tedious job but it is only done once. Also, the passwords must surely be known, otherwise how can the connections be made to the DBs?

They aren't necessarily known at deployment time. Dave indicated the user types it in. Suppose these passwords change every 28 days. In that case, the user would know the latest password and the deployer would not be able to set it.

What we don't know is whether the application would benefit from connection pooling and considerable transaction management. If the answer is yes, then EJB must be a strong candidate solution.

EJB perhaps. That doesn't require entity beans or JPA. A session bean can take care of the transaction management. And a standalone connection pool can take care of the connection pooling.

[Adam Tkaczyk]

Facts
1) There is 250+ databases that have the same schema
2) user and password are needed to logon

I sugest to write ConnectionFactory (singleton) class that has access to credentials and can create JDBC connection (or connection pool).

After that we can write collection of classes that will service business operations (common for every schema). Those classes can take ConnectionFactory instance as constructors or methods argument and use this instance to obtain JDBC connection and perform their jobs.

This solution is some kind of "Strategy" design pattern.

[Dave Manley]

Thanks for all the responses. I'm overwhelmed. Let me calrify some points.

All database passwords are known. There is a single user, we call it java_user, that does all of the work in the database. The userid/password that the user enters (with the database identifier) is handled by LDAP at the SSO layer. The database connection would be handled at the server level (datasources). I'm sorry I didn't make that clear, Jeanne.

It's not a high-volume system, at all. At peak times there may be a couple thousand transactions per minute, but that would be unusual and very seldom. There are about 110 tables.

One of my bigger concerns was the number of connections in the connection pool. I would probably need somewhere around 1000-1200 max connections to server the users at the 250 locations (Air Force bases.) Also, 250 x 110 tables means 27500 entity objects (with 110 super classes if I considered Jesus' solution.)

If this gives anyone a better perspective, please respond. Otherwise I will consider the current posts a bit more and possible come back for more info.

Thanks all.

[Roger Chung-Wee]

I very much doubt whether 2,000 transactions a minutes spread over 250 databases will be a problem. You may find that two or three connections per pool will be quite sufficient. Of course, I am assuming that your DBs can support this. What DBMS do you have?

[Dave Manley]

We have Oracle 9.0.2 databases, 5 - 15 databases per HP server running HPUX (depending on the data requirements for the particular databases). 10g's much larger footprint is a big hindrance in our moving up, now. The databases are running the applications now, running Oracle Form 9i. All the logic on the Oralce Forms and database (lots of plsql) are very "one-database-per-location"-specific.

On the application side, with EJB 3 (my first EJB application), I could, I suppose, get the Resource info dynamically for the session beans, based on the users database name. That means I can't use the @PersistenceContext and @PersistenceUnit annotations without having 250 different beans. And entity beans belong to the Context in which they are retrieved or created. Does that sound right?

By the way, if it helps, we are using Oracle 10.1.3 AS on Dell boxes running Red Hat Linux.