Tomcat 5 / Oracle / JDBC connection via JNDI not working

Discussions

General J2EE: Tomcat 5 / Oracle / JDBC connection via JNDI not working

  1. I'm tring to connect to an oracle database using Tomcat 5's connection pooling mechanism. When I execute the code below, I get the following exception:

    org.apache.commons.dbcp.SQLNestedException: Cannot create JDBC driver of class '' for connect URL 'null', cause:
    java.sql.SQLException: No suitable driver
            at java.sql.DriverManager.getDriver(DriverManager.java:243)
            at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:743)
            at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:518)

    server.xml configuration
        <Host name="localhost" debug="0" appBase="webapps" unpackWARs="true"
            autoDeploy="true" xmlValidation="false" xmlNamespaceAware="false">

            <Logger className="org.apache.catalina.logger.FileLogger"
                directory="logs" prefix="localhost_log." suffix=".txt"
                timestamp="true"/>

            <DefaultContext>
                <Resource name="jdbc/hrJobPosterDb" auth="Container" scope="Shareable" type="javax.sql.DataSource"/>
                <ResourceParams name="jdbc/hrJobPosterDb">
                    <parameter>
                        <name>driverClassName</name>
                        <value>oracle.jdbc.driver.OracleDriver</value>
                    </parameter>

                    <parameter>
                        <name>url</name>
                        <value>jdbc:oracle:thin:@myDBServer.someurl.com:1521:cpdbdev1</value>
                    </parameter>

                    <parameter>
                        <name>username</name>
                        <value>hr_job_poster</value>
                    </parameter>

                    <parameter>
                        <name>password</name>
                        <value>hr_job_poster</value>
                    </parameter>

                    <parameter>
                        <name>maxActive</name>
                        <value>20</value>
                    </parameter>

                    <parameter>
                        <name>maxIdle</name>
                        <value>30000</value>
                    </parameter>

                    <parameter>
                        <name>maxWait</name>
                        <value>100</value>
                    </parameter>

                    <parameter>
                        <name>removeAbandoned</name>
                        <value>true</value>
                    </parameter>

                    <parameter>
                        <name>removeAbandonedTimeout</name>
                        <value>15</value>
                    </parameter>
                </ResourceParams>
            </DefaultContext>
          </Host>

    web.xml configuration
        <resource-ref>
            <res-ref-name>jdbc/hrJobPosterDb</res-ref-name>
            <res-type>javax.sql.DataSource</res-type>
            <res-auth>Container</res-auth>
        </resource-ref>

    Code snippet that produces error
    DataSource dataSource = null;

    // Obtain our environment naming context
    InitialContext initCtx = new InitialContext();
    dataSource = (DataSource) initCtx.lookup("java:comp/env/jdbc/hrJobPosterDb");

    //get pooled connection
    Connection conn = dataSource.getConnection(); //ERROR!!
  2. Keep the JDBC Driver in the lib Directory of your Tomcat., If you have already done this try to verify that the information what you are specifying in the URL of the Database like the Port etc., are Correct.,

    Hope it will help you,
       zaki
  3. Definitely sounds like the app server can't find the driver jar in its classpath. The previous reply indicating copying the jar to the tomcat/lib directory should solve this or at least rule it out as a cause.
  4. Hi Brent, it's probably too late to help you on this, but since I had struggled with this same problem for about twenty half-panicked late-night hours, I thought I would publish it here in the hope it would help others. And maybe it can help you clean up your fix even if you did get it working.

    There are several things missing from your configuration. The bright side is that makes it a good example.

    To recap briefly, your starting error is: Cannot create JDBC driver of class ' ' for connect URL 'null', cause: java.sql.SQLException: No suitable driver

    Solution:
    1. First, if you want to use JNDI in Tomcat to look up the database resource rather than hard-code it throughout your project, you want to be using "oracle.jdbc.pool.OracleDataSource" for your driverClassName resource parameter rather than "oracle.jdbc.driver.OracleDriver".

    This will improve your error to: Cannot create JDBC driver of class 'oracle.jdbc.pool.OracleDataSource' for connect URL '<whatever>', cause: No suitable driver.

    At least now it knows the class and recognizes the URL. I suspect the empty string for the class in the prior error was because it's looking for a class that implements the javax.sql.DataSource interface, and I don't believe the oracle.jdbc.driver.OracleDriver does that.

    As a note here, in moving into a JNDI-enabled DataSource, it appears that connection pooling comes along for the ride. That's all good as far as I'm concerned, but I find it a little curious that there doesn't seem to be a JNDI-enabled but non-connection-pooling option. I'm probably still missing something there.

    2. The next problem is that when you use this OracleDataSource driver class, the default factory used in Tomcat will not work for you. That's what the remaining "No suitable driver" bit is about.

    The default factory is org.apache.commons.dbcp.BasicDataSourceFactory. For whatever reason, this doesn't work. So you need to explicitly specify the factory in a new parameter. What you need to use is oracle.jdbc.pool.OracleDataSourceFactory. The XML looks like this, just pop it in alongside the other parameters in the <ResourceParams> element. Seems natural for it to be toward the top:

          <parameter>
            <name>factory</name>
            <value>oracle.jdbc.pool.OracleDataSourceFactory</value>
          </parameter>


    This gives you a new error, depending on your code, but basically, the context.lookup( ) method now returns null.

    3. The fix for this is to change the "type" attribute in your <Resource> element from "javax.sql.DataSource" to "oracle.jdbc.pool.OracleDataSource". Don't ask me how I figured that out. It came as one of hundreds of hints along the many Googles I ran while pursuing this problem.

    <Resource name="jdbc/hrJobPosterDb" auth="Container" scope="Shareable" type="oracle.jdbc.pool.OracleDataSource"/>


    4. Believe it or not, there is more. This last fix should have changed your error to something like: "java.sql.SQLException: invalid arguments in call". This one was a real head-scratcher. Anyway, turns out that you need to change the <name>username</name> element within it's <parameter> element to <name>user</name>. Can you believe that? It works with "username" when you use the OracleDriver class, but not with this one. Seems like "user" is the officially specified parameter name for this purpose, but the original Tomcat documentation and even Oracle documentation uses "username".

    Anyway, that seems to do the final trick. With that, the setup works for me, as I hope it does for you.
  5. Look for parameters name[ Go to top ]

    If you use oracle9i try to change parameter name - username - to - user -.
  6. Take a look at this thread http://javaonthefly.blogspot.com/2008/01/creating-connection-pool-for-tomcat.html