10 Replies Latest reply on Feb 16, 2007 5:37 PM by Newsgroup_User

    Passing Database Pool Connection to Java Object

    michaelprichard
      SO I have a set of Java libs that i would like to use within CFMX. In fact, I would like to do all my business logic with those libraries. Question is, the classes require I pass a database connection to them. How would I do this?
        • 1. Passing Database Pool Connection to Java Object
          BKBK Adobe Community Professional & MVP
          In Coldfusion you can just code the usual Java DB connection directly. That is, something like

          <cfscript>
          classObj = CreateObject("java", "java.lang.Class");
          classObj.forName("etc.etc..Driver");
          driverManager = CreateObject("java", "java.sql.DriverManager");
          connectionURL="jdbc: etc etc. ";
          conn=driverManager.getConnection(connectionURL,username,password);
          myQuery="SELECT blah1, blah2 FROM blah3";
          preparedStatement = conn.prepareStatement(myQuery);
          rs=preparedStatement.executeQuery();
          while (rs.next()) {
          etc etc
          }
          rs.close();
          conn.close();
          </cfscript>

          That is for single calls. To set up a permanent connection, you could include in the libraries a class whose job is simply to establish the connection to the DB. I should expect the code to contain some of the themes above, namely,

          getConnection (String url, String user, String password)
          connectionURL = "jdbc:etc"
          java.sql.Connection conn = java.sql.DriverManager.getConnection (connectionURL, username, password);
          etc. etc.

          You could also find it convenient to store connection and user data data in a properties object of the java.util.Properties class.
          java.util.Properties properties = new java.util.Properties ();
          properties.setProperty ("user", "bkbk");
          properties.setProperty ("password", "knoflook");
          java.sql.Connection conn = java.sql.DriverManager.getConnection (connectionURL, properties);






          • 2. Re: Passing Database Pool Connection to Java Object
            michaelprichard Level 1
            Thanks for the response.

            Anyway I can grab from the CF datasources? I would like to grab from those pools?
            • 3. Re: Passing Database Pool Connection to Java Object
              BKBK Adobe Community Professional & MVP
              Anyway I can grab from the CF datasources?
              This should be simpler than creating a database connection for your library, which I assumed was your question. When you import a library into a CFML page, e.g. using the cfimport tag, all the Coldfusion datasources are available to it.

              • 4. Re: Passing Database Pool Connection to Java Object
                michaelprichard Level 1
                awesome.

                How do I pass them to the java object? What var do I use?

                Thanks,
                -Michael
                • 5. Re: Passing Database Pool Connection to Java Object
                  BKBK Adobe Community Professional & MVP
                  How do I pass them to the java object? What var do I use?
                  It depends on what your library will be doing and how you would go about the coding. Afterall, you intend to use your libraries to perform some Coldfusion function. If they "expose" their functionality to the CFML page, then you can just use <cfquery name="myQ" datasource="myDSN"> directly. That is, the library will simply know the datasource by its name, in this case, "myDSN".

                  If not, then I can think of two alternative routes. Extend the library by creating a Java class whose purpose is to establish a database connection. The code is already sketched above. All that the class would need to do is create a java.sql.Connection object to pass to the library, whenever required. Alternatively, define your datasource or cfide.adminapi.administrator object or cfide.adminapi.datasource object or queries, as the case may be, in a Coldfusion component. Then invoke the component in a Java class that you've created specially for that purpose. This so-called CFC Proxy is at present restricted to MX7, Enterprise Edition.

                  • 6. Re: Passing Database Pool Connection to Java Object
                    BKBK Adobe Community Professional & MVP
                    I stumbled across this Coldfusion TechNote on making a JDBC connection in CFML. It complements the above, and more. In particular, study the download at the bottom.

                    • 7. Re: Passing Database Pool Connection to Java Object
                      monkey_woo_too Level 1
                      I recommend that you use the DBCP library for your JDBC connection pooling - Jrun connection pooling does not work whatsoever.
                      • 8. Re: Passing Database Pool Connection to Java Object
                        BKBK Adobe Community Professional & MVP
                        > Jrun connection pooling does not work whatsoever.
                        I'm not sure you're talking about the reference I gave. The technote is about a basic, pure Java JDBC test connection implemented in Coldusion without any connection pooling.

                        • 9. Re: Passing Database Pool Connection to Java Object
                          monkey_woo_too Level 1
                          sure, i wasn't referring to your post.

                          I was making the point that when michaelprichard tries to implement connection pooling, which he should do for obvious reasons, he should use DBCP, not the built in JRun datasource connection pooling.

                          I only mention it, because I spent a week trying to figure out why my ap wouldn't perform under load until i read on the serverside that JRun doesn't pool it's connections (even though it says it does)
                          • 10. Re: Passing Database Pool Connection to Java Object
                            Level 7
                            gotta chime in here. i've been using c3p0
                            ( http://sourceforge.net/projects/c3p0/) in a heavy load java app for over 2
                            years and haven't had one problem with it. i switched to it from dbcp b/c of
                            some problems i was having with that package.



                            "monkey woo too" <webforumsuser@macromedia.com> wrote in message
                            news:er1k3f$abv$1@forums.macromedia.com...
                            > sure, i wasn't referring to your post.
                            >
                            > I was making the point that when michaelprichard tries to implement
                            > connection
                            > pooling, which he should do for obvious reasons, he should use DBCP, not
                            > the
                            > built in JRun datasource connection pooling.
                            >
                            > I only mention it, because I spent a week trying to figure out why my ap
                            > wouldn't perform under load until i read on the serverside that JRun
                            > doesn't
                            > pool it's connections (even though it says it does)
                            >
                            >