9 Replies Latest reply on Aug 29, 2001 6:36 AM by Bill Pfeiffer

    How to use connectionPool?

    Raybin Luo Newbie

      Is there any example to show me how to use the
      connectionPool of JBoss? How to get the connection
      and how to release it. My mail box is lxbin@21cn.com. thx.

        • 2. Re: How to use connectionPool?
          John Hammen Newbie

          Actually, I think this is a good question - 'RTFM' is fine if the manual actually covers this subject, but as far as I can tell it does _not_. The section titled 'JDBC' gives a very good discussion (plus examples- excellent!) on how to set up connection pooling from the _administrator's_ standpoint. I found it extremely useful myself and was able to get a postgres pool up and going without a problem. At that point, however, I was disappointed that there was _no_ mention of how to actually use the pool. Is there such a section? I couldn't find it, and doing a keyword search on 'pool' gave me no satisfaction. I couldn't find anything useful in the examples either.

          Because I couldn't find this information, I followed the convention from Sun's J2EE tutorial. This blew up on me because they were telling me to grab and cache the connection in setEntityContext, and close it in unsetEntityContext, and when the number of my BMP entity beans hit the max number of db connections our DBA had set, Not Good Things started to happen. I finally got a little help from the users' mailing list archive in which I found mention of caching the Datasource in setEntityContext and then actually opening and closing the connection for each function that needed it, i.e. ejbLoad, ejbStore, ejbCreate, etc. It seemed a bit counter-intuitive to me to call close() on every function, because frankly, I don't want to close the connection, I just want to return it to the pool, but then I found another post that stated that close() is actually the method that does this.

          Bottom line: this may all seem obvious to me now, but the point is I'm still a newbie when it comes to EJB, and while I was able to find the information I needed eventually, I see no reason why this information should not included in the 'FM'.

          • 3. Re: How to use connectionPool?
            Bill Pfeiffer Newbie

            I think most of this stuff is documented somewhere between the online jboss docs and the ejb specs, but I'll try to answer the question.

            I'll first list the general steps and follow up with examples. The general steps:

            1. Edit the jboss.jcml file to add in (set up) your database connection pool.

            2. Make sure the EJB's that are going to use the connection pool have references to it in both the jboss.xml and ejb-jar.xml deployment files.

            3. In you ejb that needs a connection, obtain a connection from the pool using jndi lookup.

            Config/Code examples:

            1: Note that I've added in the OracleDriver here.

            <!-- JDBC -->

            oracle.jdbc.driver.OracleDriver,org.hsql.jdbcDriver,org.enhydra.instantdb.jdbc.idbDriver



            espeed
            org.opentools.minerva.jdbc.xa.wrapper.XADataSourceImpl
            jdbc:oracle:thin:@myhost:orcle
            myuser
            20
            mypassword



            2: The ejb-jar.xml file:

            .... entity bean stuff...
            <resource-ref>
            <res-ref-name>espeed</res-ref-name>
            <res-type>javax.sql.DataSource</res-type>
            <res-auth>Container</res-auth>
            </resource-ref>


            2: The jboss.xml file:


            <ejb-name>SBProcess</ejb-name>
            <jndi-name>RMI/org/arbfile/espeed/ejb/sb/espprocess/SBProcess</jndi-name>
            <configuration-name></configuration-name>
            <resource-ref>
            <res-ref-name>espeed</res-ref-name>
            <resource-name>espeed</resource-name>
            </resource-ref>


            3: The code using this:

            public static Connection getConnection(String connPoolName)
            {
            Connection dbConnection = null;

            try
            {
            Context naming = new InitialContext();
            Object pool = naming.lookup(connPoolName);
            DataSource dataSource = (DataSource)pool;
            dbConnection = dataSource.getConnection();
            }
            catch(Exception e)
            {
            System.out.println("Get Connection Failed: " + e.toString());
            }
            return dbConnection;
            }

            Usage:

            Connection conn = getConnection("java:/espeed");

            Hope this helps.

            Bill Pfeiffer


            • 4. Re: How to use connectionPool?
              PAtrick Newbie

              Great Example, How do I get a connection from a servlet/Jsp in the following senerio (using minerva connection pool)
              1. In servlet/jsp application no ejb's using Tomcat-Jboss 2.4

              2. In a servlet/Jsp app with ejb's using Tomcat-Jboss2.4
              thanks

              • 5. Re: How to use connectionPool?
                Bill Pfeiffer Newbie

                Forgot to mention that calling conn.close() does not actually 'close' the connection but simply returns it to the pool.

                • 6. Re: How to use connectionPool?
                  Patrick Newbie

                  I don't think that you can interact directly with a DataSource connection from a servlet/jsp, but I imagine that you would do exactly the same lookup as in the EJB [not sure if it'll work though]. Give it a try !

                  Once you have your EJB grabbing a DataSource via the ConnectionPool, simply have your servlet/jsp call the appropriate "business methods" of your EJB. Let the EJB worry about all the db interactions.

                  - Patrick

                  • 7. Re: How to use connectionPool?
                    Henry Lin Newbie

                    execuse me !
                    when i use u's example in my program and compiler it
                    it tell me:
                    class Context not found !
                    class DataSource not found !

                    Do i import java.? or com.? into my program ?

                    thank you !

                    • 8. Re: How to use connectionPool?
                      Bill Pfeiffer Newbie

                      More than likely you need to import the javax.naming and javax.sql packages (maybe other ejb support packages?). Also make sure that jboss support jars are included in your compile classpath.

                      • 9. Re: How to use connectionPool?
                        Bill Pfeiffer Newbie

                        I have code in a project I was working on a while back (JBoss 2.0) that indicates I was definitely accessing JBoss connection pool from a servlet. It actually appears (again from my old code) that I was using the getConnection() method I posted earlier in the same manner from both my EJB and servlets. Note that this was with JBoss/Tomcat running together inVM. If you run Tomcat seperately, I don't think it will work.