> 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".
