After my first post I came up with a new idea that maybe could be the solution to our problems.
What if we put the client ID (or database name) as an attribute in every entity bean, and made that attribute part of the key (so the cache won't mix them up). Then we let all the entity beans be BMP, and within every SQL statement we add the database name (as in "SELECT FROM DB1000.articles ...", "INSERT INTO DB1050.address ..."). I have only looked briefly into BMP and I don't know if my theory will hold. How do you specify the object key in BMP? Does all the fields have to be fields in the database table? Would this be a valid solution? (Though somewhat tied to the MySQL database) > -----Original Message----- > From: A mailing list for Enterprise JavaBeans development > [mailto:[EMAIL PROTECTED]]On Behalf Of Mattias Jiderhamn > Sent: Thursday, August 01, 2002 12:55 PM > To: [EMAIL PROTECTED] > Subject: Multiple databases... > > > Hi all subscribers. > Joined this list to be able to discuss this one question. I have > spent a lot > of thought on this and I'll try to describe things rather detailed, so try > to bear with me. > > We are developing a web application in Java with extensive > database use. We > have one "general", system database (SYSTEM) with texts, user > info, settings > etc. Every user of the system is associated to "clients" (usally > the company > where they work). Most users have only one client but some have several. A > client can have "unlimited" number of user (mostly < 5) associated to it. > There can be unlimited number of clients (currently ~30). > Ex: > User1 has access to client 1 > User2 has access to client 1 and 53 > > Every client has their own database with their data. When a user loggs in > s/he is associated with the database of a) the only client s/he > has acces to > or b) the client s/he chooses for the session. > > Today we are not using EJB or any third party middleware but only our own > proprietary solution. We have one application in the J2EE server (Resin) > with one connection pool to our MySQL database (initially the SYSTEM > database). I have built a wrapper around Resins connection pool > so that when > a thread requests a connection, the connection is first switched to the > correct database, with MySQLs "USE database" command, depending > on the user > of the session running the thread as mentioned above. There are also some > other features to handle transactions etc. > > Much of the use is through the web interface, where the data is > fetched from > the database, put into hashtables and output on the page. But > there are also > lots of background work, for which we have classes mapping to > records in our > database tables. > > The biggest problem I see here is with caching. Since we have no object > cache we have to read the object from the database every time we > use it, and > write it to the database every time we change it. Since we also read > directly from the database a lot, we can not have an object cache. I would > like to have the same interface for both "background" and "web" > (both being > object oriented), but then we need to solve the performance issue. > > I have thought about EJB many times. I have also looked briefly at other > solutions that allow object caching. But it always comes down to the same > problem: I can't see how to get it working with differens databases for > different users. And of course, how would the object cache know > wich client > the user who updated an object was logged into, when it is time > to write it > back to the database? > > > Does anyone have a solution for this? > I saw someone mentioning Webgain TopLink in another post > (http://www.jguru.com/faq/view.jsp?EID=421472), could someone expain how > that could be of any help? > > Summary of what we want: > - Multiple (structurally identical) databases (main reasons: maintance and > security) > - Database selected at run-time > - Object-Relational mapping with caching > - Preferrably a non-proprietary solution > > Mattias Jiderhamn > Expert Systems > [EMAIL PROTECTED] > > ================================================================== > ========= > To unsubscribe, send email to [EMAIL PROTECTED] and include > in the body > of the message "signoff EJB-INTEREST". For general help, send email to > [EMAIL PROTECTED] and include in the body of the message "help". > > =========================================================================== To unsubscribe, send email to [EMAIL PROTECTED] and include in the body of the message "signoff EJB-INTEREST". For general help, send email to [EMAIL PROTECTED] and include in the body of the message "help".
