12 Replies Latest reply on Dec 4, 2001 3:04 PM by Adam Young

    Dynamic DataSource usage for CMP entity beans??

    Kevin Duffey Newbie

      Hey there all,

      We have a particularly odd situation (or so I think) where I work. Being in the media industry, we deal with radio, tv and cable. All three media types in most cases have the identical data associated with them, but we use different databases (think ASP model; radio stations call upon entity beans for the radio station database, and so on). Problem is, it appears we have to specify the datasource in the descriptor file of the entity bean. We would like to do the findByPrimaryKey and so on by allowing the ability to select dynamically which datasource we want used for the entity lookup; similar for storing entities. Is there any CMP based mechanism that provides this sort of flexibility available? Ideally we want a J2EE standard approach, not one specific to JBoss. This needs to remain portable.

      Thanks.

        • 1. Re: Dynamic DataSource usage for CMP entity beans??
          Andreas Schaefer Apprentice

          I don't think so. What you can do is to deploy the same EJBs under different JNDI names or paths like:
          ejb/tv/Station
          ejb/radio/Station

          which would allow you to specify the datasource for each of them but being able to use the same codebase meaning EJBs.

          Have fun - Andy

          • 2. Re: Dynamic DataSource usage for CMP entity beans??
            Kevin Duffey Newbie

            This is exactly what we wanted to avoid. As my co-worker put it, and I don't fully understand why it is this way yet, but in some applications each user would have a bean and their own datasource deployed, and we may have hundreds of users. So you can see where this is going to be a big issue for us. Again, I really don't know why each user will need this, but this is how it was explained to me. I know what we are trying to do is run databases at local stations as well as an ASP based model, which keeps the others in sync. But I have little info after that, just that it seems that it is going to get VERY ugly to have to deploy in this manner.

            So would you say BMP is the best method then? We were thinking of EJB 2.0 and using EJB QL or something to allow dynamic datasource...any idea if that would work?

            • 3. Re: Dynamic DataSource usage for CMP entity beans??
              David Budworth Newbie

              If you are trying to remain portable, but still have context based data sources, the only thing I can think of would be to use BMP.

              There is no way to specify datasource on the fly for CMP beans since the EJB spec doesn't define the mechanism for storage (so they don't allow datasource definitions)
              That's why ejb-jar.xml has no datasource specification.

              If you use BMP, you could easily roll your own. Personally, I would probably stick the datasource in a threadlocal static var somewhere, and have the BMP beans use that. That way you can set the datasource as soon as you know which to use (at web request or whatever). That way, you don't have to worry about passing the DS to every create/find/store method.

              That's just my $0.02

              • 4. Re: Dynamic DataSource usage for CMP entity beans??
                David Jencks Master

                I strongly suggest you rethink the need to have hundreds of database instances in one location. (As opposed to having hundreds of partial copies of one database, one per location). What would happen if you used one database and included a column in every (relevant) table indicating which one of what you are now thinking of as database instances the row refers to?

                • 5. Re: Dynamic DataSource usage for CMP entity beans??
                  Erik Sliman Apprentice

                  I agree with DavidJ. If it is possible to have the data stored physically together, than consider it.

                  If the databases are not created by you, yet your application has to use them, then this isn't an option for you. Is this the case?

                  You may have wondered onto the configuration problem I have been wondering how best to solve.

                  If your data is distributed, and perhaps your JBoss instances are also distributed, and, like me, you want just one JAR that can function on all instances, then even with BMP you may find yourself needing to address this configuration issue.

                  Setting aside your specific problem, and just viewing configurability in general, someone suggested that you can dynamically modify configuration at runtime. However, if this effectively modifies values that are originally set through deployment, then wouldn't this indicate that you'd lose the settings the next time you redeployed? If so, then it does not adequately address the persistence requirement. I am looking for something analogous to the Windows registry, or at least .INIs, where redeployment will not overwrite it, and you do not need to store it in a database.

                  The datasource is one element I would store. The deployment could define a default. However, persistent dynamic configuration (PDC) would override this default when found.

                  I foresee external EJB references becoming a problem until we achieve PDC. In order to define an external reference, you have to specify the host name:

                  protocol://host:1234/name/in/other/server

                  It is bad enough to have to "hard code" this in the deployment descriptor. But, if you deploy the JAR to multiple instances, then this means that each JAR has to have customized descriptors.

                  In my case, I have many IPs on one machine, and do not consider 'localhost' a viable option. This means that I will have to hardcode IPs for all my external references, even when I know the reference is within the current JBoss instance. This is necessary to support migration, where the next version of JBoss will run on a different IP on the same machine. Plus, this is the standard I use for all server software, to ensure isolation by IP of like software (multiple web-servers, databases, etc.)

                  One solution I am wondering about, but don't know enough to know if it would work, would be dynamic creation of XML documents to store application configuration information. This would be created and maintained at runtime, so would never be overwritten by redeployment. This would permit applications to be configured within each JBoss instance it is distributed to. Does anyone know if this is feasible? If so, anyone have any sample code?

                  I realize I digressed a bit from your original problem. Do you feel your problem overlaps with the problems I described?

                  • 6. Re: Dynamic DataSource usage for CMP entity beans??
                    David Jencks Master

                    2 things to think about

                    -1- There is some stuff about mbean persistence in the jmx spec: I haven't looked at it hard, but have read rumors that it will be added to jboss 3 soon. As I understand it, this would mean changes to mbean config on one server would be persistent over server stop/restart.

                    -2- If the problem is more along the lines of "how do I create many similar but not identical deployments" perhaps generating the jboss-service.xml or jboss.jcml files from a template, data, and XSLT would do the trick.


                    Also just to be a little more specific about my previous suggestion,

                    instead of

                    user1table (key, data)

                    user2table (key, data)

                    have

                    table (userid, key, data)
                    (pk = (userid, key))

                    Then on any machine you can label the datasource the same.

                    • 7. Re: Dynamic DataSource usage for CMP entity beans??
                      zx44093 Newbie

                      Adding a column to each table to specify who owns the row is not an option. In ASP business, most users do not like to mix their data with others and it's going to be very hard to separate them in the future if one user decide to host its own database.

                      Just wondering if there is a way to override the getDatasource() method in CMP? I imaging ejbStore(), ejbLoad() and ejbRemove() would have to call such a method before start the work right?

                      I simply do not like the way that a CMP bean is being boundled with a specific datasource at the time we deploy it. It won't work in ASP environment.

                      Has any one have done something strange like this?

                      • 8. Re: Dynamic DataSource usage for CMP entity beans??
                        Kevin Duffey Newbie

                        Sorry its been a while. Was out on vacation. If you or david gets this, maybe this will explain my issue a bit more.

                        Apparently, we have one group of servers at our ASP. For some reason or another, each of our clients wants a completely separate database. They don't like the idea of possibly sharing various tables within the same database instance. It also makes it much easier to manage (backup, dump, restore, etc) for the dba, and apparently if a given client wants their database local, it makes it real easy to do. By mixing the various clients data by using the "id" pattern in one table to point to another table, one thing does strike me as a possible problem: queries. They would take a long long time if we had 1000's of users each with 1000's of transaction per month. Eventually the "transaction" table would be huge, thus queries would take a lot longer to perform. All of these reasons point to why our team (not me though) agreed to house a completely separate database for each client. From what I gather, this would mean separate licenses as well. So our dilema, as we are pretty much sure, is we can not use CMP because there is no way to pass the DataSource to the CMP methods. I was hoping there would be a way to specify the DataSource per transaction, and not only at deployment time. As I think one of you said, the only way to use CMP in this situation is deploying each and every bean with a different descriptor for every client.

                        We have an odd situation in that some clients want to store the database at their location, while others are willing to "trust us" with the ASP based database, but only if they get their own database. Either way, the client is paying for the database license. Those that store it at thier location (so they can back it up, feel safe its there, etc) are able to work locally with no issues, except that some data is required to be updated via the ASP based database that is not specific to each client, but our application that all clients use. Therefore, we have to "sync" certain tables of local and ASP databases so that the local ones are as up to date as possible. Again, the ability to quickly move a clients data to their on local location is necessary as some are using the ASP based temporarily, then opting for a local run database.

                        Hope that makes a little more sense. It seems to me BMP is the only way to go in this case, where by we can specify the DataSource for each transaction based on the request.

                        • 9. Re: Dynamic DataSource usage for CMP entity beans??
                          David Jencks Master

                          I think it is very odd that all these users are sharing the application, classes, bean cache, etc, but not the database. This means among other things that you can't partition server resources among clients--- they are all sharing them seamlessly.

                          I think the only portable solution is to deploy a copy of the app for each client. Note that you can "cheat" and only use one copy of the classes it you put all the classes in lib/ext and deploy jars containing only the deployment descriptors for each client. This does prevent hot redeploy, but if everyone is using the same classes anyway there is nothing else going on to prevent you from bouncing the whole server.

                          I guess in rh we should think if this is something we want to support more formally with 3 layers of classloaders...

                          • 10. Re: Dynamic DataSource usage for CMP entity beans??
                            Dain Sundstrom Master

                            You could use MVCSoft tool to get you started on bmp. You would use EJB 2.0 then when you get it working modify the enerated code to support dynamic datasources.

                            On another point does the RH db pool support passing user name and password with jaas to the datasource create? So if I log in, I get the ds with my user name. If so, then you could configure the db user permissions to use a different default db for each user. If it doesn't support this, you are still screwed. :)

                            -dain

                            • 11. Re: Dynamic DataSource usage for CMP entity beans??
                              David Jencks Master

                              In rh, the only supplied PrincipalMapping always returns the same person. However, its pretty trivial to write on that say passes the supplied user through to the ManagedConnectionFactory. Lookup is also possible, although I'm not sure where you'd look up to.

                              Is the last thing you suggest with user permissions one of these proprietary Oracle or mssql features?

                              • 12. Re: Dynamic DataSource usage for CMP entity beans??
                                Adam Young Newbie

                                Could you create a Meta-Database that acts as a view of all the other databases? I know some technologies (MS Access comes to mind, please forgive) will let you specify that one table is actaully just an ODBC connection to another table. Then you merge the tables together using a view, and access that VIA the EJBs.

                                Another possibility is to have a scripts that modifies your jaws and ejb-jar files with the customer names for then name of the datapool, then deploy a jar file for each customer. This would also create a need to add entries to jaws.jcml. SO the complete script would:

                                1. Add -pool to jboss.jcml
                                2. Add -pool to the head of the ejb-jar and jaws.xml file
                                3. Force all you jndi lookups to get the customer name first, and then the EJB name. So if you had a MediaResource bean /MediaResource.


                                We do something similar to #3 for our sequences. In the ejb-jar, we specify the sequence name and pool like this:

                                ...
                                <primkey-field>id</primkey-field>
                                <env-entry>

                                <env-entry-name>connectionPoolName</env-entry-name>
                                <env-entry-type>java.lang.String</env-entry-type>
                                <env-entry-value>java:/SFGetCareReportPool</env-entry-value>
                                </env-entry>
                                <env-entry>

                                <env-entry-name>sequenceName</env-entry-name>
                                <env-entry-type>java.lang.String</env-entry-type>
                                <env-entry-value>reportquery_id_seq</env-entry-value>
                                </env-entry>



                                This information is now in your context, so if you create a new initial context in the bean you get it:

                                public Integer ejbCreate ()
                                throws CreateException
                                {
                                try {
                                IdGenerator idGenerator =
                                new IdGenerator(new InitialContext());
                                this.id = new Integer (idGenerator.generateId());
                                return this.id;
                                }catch (Exception e){
                                System.out.println(e);
                                throw new CreateException();
                                }
                                }


                                You can now acceess them like this

                                String poolName = initialContext
                                .lookup("java:comp/env/connectionPoolName");


                                This might be a bit of overkill.

                                It still doesn't help you with your multiple database problem. I would solve that by creating a META database that has the searchable information, and sufficient information to retrieve it from the customer databases.
                                But you've probably already thought of that.