Hints to solution: 1. Multiple Deployments of Application 2. JNDI Partitioning (clients/x/ssbn, clients/y/ssbn) 3. Component is more than Java code its configuration (Entity->CMP->DataSourceX,Entity->CMP->DataSourceY). Same code different data.
Regards, William -----Original Message----- From: A mailing list for Enterprise JavaBeans development [mailto:[EMAIL PROTECTED]] On Behalf Of Mattias Jiderhamn Sent: 02 August 2002 09:20 To: [EMAIL PROTECTED] Subject: Re: Multiple databases - New idea > From your example, it looks like you are using multiple schemas - not > multiple databases. Multiple databases would require you to have one > datasource per client (and also one connection pool per client). Your right. Some vendors call it schemas but MySQL calls it databases. Anyway, there is only one datasoure/connection pool and the specific schema/database could be selected selected at run-time (either by "SELECT * FROM database.table" or "USE database; SELECT * FROM table") Does this insight open up for other possibilities? > When it comes to vendor-specific solutions, WebSphere has an > architected solution for making request-specific data available > without passing it as explicit parameters. It is managed through a > reserved jndi-mapped service called Workspace. The user-interface > logic looks up the Workspace object (which is like a Hashmap) and > assignes a name-value pair for the client identifier. The BMP > implementation looks up the workspace object to obtain the value of > the client identifier. This seems to be somewhat similar to what we do today. When a request comes in a servlet filter puts the Thread and a bean with session info (including client) into a Hashtable held by a singleton. The same Hashtable is accessed when it's time to access the database. Would this be possible in an EJB app-server too? (Do we have to put it in a JNDI resource even when Resin uses local EJBs?) > > 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". > > > > ======================================================================== === 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".
