Multiple schema access

[ander.gborges]

Hi People,

I am looking for a solution to my problem for days but I didn´t find anything that could really help me.
In my app I am using JBoss 4.2.3 GA and EJB3.

What I need:
Allow that each customer of my system (Today 5 but more than 2000 sooner) have it own database (Can be the same database but diferent schema). I mean, use the same entities and sessions.

I tryied to create the entityManager inside my stateless session, but without success. Today I was deep inside the jboss code looking for the code that instantiates the EntityManager when I put the code

 @PersistenceContext(unitName = "default")
 protected EntityManager entityManager;

because I realized that if I found this code, I would be able to change the unitName programatically and force JBoss to instantiate the correct EM and then I wouldn´t have to change any session or entity code.

I beg anyone to give me a north, could be links, concepts or any kind of example. I would really apreciate that.

Thanks in advance.

Anderson Geison Borges.

[peterj]

There is no such capability build into EJB, nor is there an option in JBoss AS to support this. I would be interested to hear what your use case is (that is, why you think you need a separate database per user).

I can think of two ways to implement this.

a) Using JDBC: write your own JDBC layer (you will probably have to implement only the Driver interface, but perhaps also the Connection interface) that sits between JBoss AS and the real JDBC driver. When a connection is made, have your code look up the user for that connection and use a database call (such as 'use somedb' in MySQL) to switch the database. [There have been other posts in the forums where people wanted the connection to use the user's account name as the database login id, and if I recall correctly someone posted that the Oracle JDBC driver had some kind of option to enable this. You could search for those posts, they might give you some other clues as to what is possible.]

b) Use one database for everyone and add a userId field to each of the tables. Then change all of the queries to add "and userId=xxx" to the where clause. This way each user should see only his or her data.

[ander.gborges]

Hi Peter,
Thanks for your response!

My use case: I am developing a app where little companies will register to use for some days for free. After that period, these companies will have to pay or leave the aplication. With separeted data bases, it is possible to just erase the unused database without affect any other, is possible to make daily backups and restore only the database of a specific company without any kind of danger of handle another's companies DBs.

The app is already developed today in the model "B" you wrote, but actually we have only 5 clients and we are having some trouble when handle data. I am talking about more than 2000 companies, so we thought that separated databases would be the solution for that.

about the "A" option you wrote: I did not understand why implement Driver or Connection interface, because when using pure Hibernate I found out that is pretty easy to create Session objects that points to diferents databases, so I thought that to do it in JBoss would be as easy.

I was planning to use the security declaration to get not only the userName and password but also the database name, and them, when the user call any action, I could get the database Name through the session context and then inject the correct entityManager to the sessionBean.


Well, now I am seriously thinking about use any kind of interceptor to get the queries String and change it to point to the user database (databasename.table for example), or even to give up the EJB model and handle the transactions for myself.

Sorry if I did not made myself clear, I am not a native english speaker.

Thanks!