Postgres Pro
Downloads
Home   >   mailing lists

Thread: Tomcat7 connection pool with postgresql

Tomcat7 connection pool with postgresql

From
Achilleas Mantzios
Date:
Hello,
although i have been a jboss/postgresql admin/dba for ages, it seems that i am stuck with tomcat.
i am trying in tomcat7 to set a connection pool'ed data source with postgresql.
i was following the tomcat7 docs here :
http://tomcat.apache.org/tomcat-7.0-doc/jndi-datasource-examples-howto.html#PostgreSQL

My server.xml portion looks as follows :
<GlobalNamingResources>
...
<Resource auth="Container" description="pgsql database for spring in action sia"
driverClassName="org.postgresql.Driver"
maxActive="20" maxIdle="10" maxWait="-1"
name="jdbc/postgres"
username="springuser" password="springuser"
type="javax.sql.DataSource"
url="jdbc:postgresql://localhost/sia"/>
</GlobalNamingResources>

my app's web.xml resource-ref goes as follows :
<resource-ref>
        <description>postgreSQL Datasource sia</description>
        <res-ref-name>jdbc/postgres</res-ref-name>
        <res-type>javax.sql.DataSource</res-type>
        <res-auth>Container</res-auth>
  </resource-ref>

in my .jsp this code fails  (ommiting boilerplate)

    InitialContext ic = new InitialContext();
        DataSource ds = (DataSource) ic.lookup( "java:/comp/env/jdbc/postgres" );
        Connection con=null;
    try {
        con = ds.getConnection();
                con.setAutoCommit(true);
    }
    catch () {...}
    finally {...}

with :

org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create JDBC driver of class '' for connect URL 'null'
        org.apache.tomcat.dbcp.dbcp.BasicDataSource.createConnectionFactory(BasicDataSource.java:1452)
        org.apache.tomcat.dbcp.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1371)
        org.apache.tomcat.dbcp.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044)
        org.apache.jsp.index_jsp._jspService(index_jsp.java:102)
        org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
        javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
        org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:433)
        org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:389)
        org.apache.jasper.servlet.JspServlet.service(JspServlet.java:333)
        javax.servlet.http.HttpServlet.service(HttpServlet.java:722)

   root cause
java.sql.SQLException: No suitable driver
        java.sql.DriverManager.getDriver(DriverManager.java:279)
        org.apache.tomcat.dbcp.dbcp.BasicDataSource.createConnectionFactory(BasicDataSource.java:1437)
        org.apache.tomcat.dbcp.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1371)
        org.apache.tomcat.dbcp.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044)
        org.apache.jsp.index_jsp._jspService(index_jsp.java:102)

....

However if i do the classic standalone classloading :
        Class.forName("org.postgresql.Driver");
                String url = "jdbc:postgresql://localhost/sia?user=springuser&password=springuser";
                con = DriverManager.getConnection(url);
it works ok.

To be honest, i haven't done any extensive reading on those matters, besides the official tomcat docs,
but time constraints are far too restrictive. Thank you all for your time!

PS users from the tomcat list, pls include my address since i am not subscribed in your list.

--
Achilleas Mantzios

Re: Tomcat7 connection pool with postgresql

From
"ml-tb"
Date:
Hi,

I think this is a 100% Tomcat question :-(


Am Freitag, 18. November 2011 schrieb Achilleas Mantzios:
> DataSource ds = (DataSource) ic.lookup
> ("java:/comp/env/jdbc/postgres");

use 'java:comp/env/jdbc/postgres' to get the ressource.

Bye Thomas

Re: Tomcat7 connection pool with postgresql

From
Samuel Gendler
Date:

On Fri, Nov 18, 2011 at 1:09 AM, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:

  root cause
java.sql.SQLException: No suitable driver
       java.sql.DriverManager.getDriver(DriverManager.java:279)
       org.apache.tomcat.dbcp.dbcp.BasicDataSource.createConnectionFactory(BasicDataSource.java:1437)
       org.apache.tomcat.dbcp.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1371)
       org.apache.tomcat.dbcp.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044)
       org.apache.jsp.index_jsp._jspService(index_jsp.java:102)


I think if you have tomcat instantiate the data source and db driver, the jar file with the driver must be available to tomcat's class loader.  Odds are good that you have the driver packaged up with your webapp, which means it is only available to the webapp's class loader.  Move it to wherever tomcat loads external jar files from (or add it to a classpath environment variable that gets read by the tomcat startup script).  A quickie place to drop it to see if I'm correct is $CATALINA_HOME/lib but I don't think it is recommend that you put it there permanently, since it will be mixed with the jars that ship as part of tomcat in there.

Re: Tomcat7 connection pool with postgresql

From
Achilleas Mantzios
Date:
Στις Friday 18 November 2011 13:13:18 ο/η Samuel Gendler έγραψε:
> On Fri, Nov 18, 2011 at 1:09 AM, Achilleas Mantzios <
> achill@matrix.gatewaynet.com> wrote:
> >
> >
> >   root cause
> > java.sql.SQLException: No suitable driver
> >        java.sql.DriverManager.getDriver(DriverManager.java:279)
> >
> >  org.apache.tomcat.dbcp.dbcp.BasicDataSource.createConnectionFactory(BasicDataSource.java:1437)
> >
> >  org.apache.tomcat.dbcp.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1371)
> >
> >  org.apache.tomcat.dbcp.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044)
> >        org.apache.jsp.index_jsp._jspService(index_jsp.java:102)
> >
> >
> I think if you have tomcat instantiate the data source and db driver, the
> jar file with the driver must be available to tomcat's class loader.  Odds
> are good that you have the driver packaged up with your webapp, which means
> it is only available to the webapp's class loader.  Move it to wherever

Thanx, but i actually had gone this step and had placed in dist $CATALINA_HOME/lib already.
Nothing explicit in the webapp about the driver.
I am starting to suspect that this has to do with declaring the resource as global under GlobalNamingResources
and forgetting to do the proper linkage xml in context. But i think it should be placed inside
context, in the first place and scrapping the GlobalNamingResources altogether.

> tomcat loads external jar files from (or add it to a classpath environment
> variable that gets read by the tomcat startup script).  A quickie place to
> drop it to see if I'm correct is $CATALINA_HOME/lib but I don't think it is
> recommend that you put it there permanently, since it will be mixed with
> the jars that ship as part of tomcat in there.
>



--
Achilleas Mantzios

Re: Tomcat7 connection pool with postgresql

From
Bruce Adams
Date:
Achilleas -

You need to put something in Tomcat's context.xml. Usually, the Resource
goes in context.xml, not in server.xml. Can you try simply moving your
existing Resource from server.xml to context.xml?

If you really need to put the Resource in server.xml, I think you also
need a ResourceLink in context.xml to make it available to web applications.

- Bruce

On 11/18/2011 04:09 AM, Achilleas Mantzios wrote:
> Hello,
> although i have been a jboss/postgresql admin/dba for ages, it seems that i am stuck with tomcat.
> i am trying in tomcat7 to set a connection pool'ed data source with postgresql.
> i was following the tomcat7 docs here :
> http://tomcat.apache.org/tomcat-7.0-doc/jndi-datasource-examples-howto.html#PostgreSQL
>
> My server.xml portion looks as follows :
> <GlobalNamingResources>
> ...
> <Resource auth="Container" description="pgsql database for spring in action sia"
> driverClassName="org.postgresql.Driver"
> maxActive="20" maxIdle="10" maxWait="-1"
> name="jdbc/postgres"
> username="springuser" password="springuser"
> type="javax.sql.DataSource"
> url="jdbc:postgresql://localhost/sia"/>
> </GlobalNamingResources>
>
> my app's web.xml resource-ref goes as follows :
> <resource-ref>
>          <description>postgreSQL Datasource sia</description>
>          <res-ref-name>jdbc/postgres</res-ref-name>
>          <res-type>javax.sql.DataSource</res-type>
>          <res-auth>Container</res-auth>
>    </resource-ref>
>
> in my .jsp this code fails  (ommiting boilerplate)
>
>     InitialContext ic = new InitialContext();
>          DataSource ds = (DataSource) ic.lookup( "java:/comp/env/jdbc/postgres" );
>          Connection con=null;
>     try {
>         con = ds.getConnection();
>                  con.setAutoCommit(true);
>     }
>     catch () {...}
>     finally {...}
>
> with :
>
> org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create JDBC driver of class '' for connect URL 'null'
>          org.apache.tomcat.dbcp.dbcp.BasicDataSource.createConnectionFactory(BasicDataSource.java:1452)
>          org.apache.tomcat.dbcp.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1371)
>          org.apache.tomcat.dbcp.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044)
>          org.apache.jsp.index_jsp._jspService(index_jsp.java:102)
>          org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
>          javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
>          org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:433)
>          org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:389)
>          org.apache.jasper.servlet.JspServlet.service(JspServlet.java:333)
>          javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
>
>     root cause
> java.sql.SQLException: No suitable driver
>          java.sql.DriverManager.getDriver(DriverManager.java:279)
>          org.apache.tomcat.dbcp.dbcp.BasicDataSource.createConnectionFactory(BasicDataSource.java:1437)
>          org.apache.tomcat.dbcp.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1371)
>          org.apache.tomcat.dbcp.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044)
>          org.apache.jsp.index_jsp._jspService(index_jsp.java:102)
>
> ....
>
> However if i do the classic standalone classloading :
>         Class.forName("org.postgresql.Driver");
>                  String url = "jdbc:postgresql://localhost/sia?user=springuser&password=springuser";
>                  con = DriverManager.getConnection(url);
> it works ok.
>
> To be honest, i haven't done any extensive reading on those matters, besides the official tomcat docs,
> but time constraints are far too restrictive. Thank you all for your time!
>
> PS users from the tomcat list, pls include my address since i am not subscribed in your list.
>

Re: Tomcat7 connection pool with postgresql

From
Achilleas Mantzios
Date:
Στις Friday 18 November 2011 13:38:52 γράψατε:
> Στις Friday 18 November 2011 13:13:18 ο/η Samuel Gendler έγραψε:
> > On Fri, Nov 18, 2011 at 1:09 AM, Achilleas Mantzios <
> > achill@matrix.gatewaynet.com> wrote:
> > >
> > >
> > >   root cause
> > > java.sql.SQLException: No suitable driver
> > >        java.sql.DriverManager.getDriver(DriverManager.java:279)
> > >
> > >  org.apache.tomcat.dbcp.dbcp.BasicDataSource.createConnectionFactory(BasicDataSource.java:1437)
> > >
> > >  org.apache.tomcat.dbcp.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1371)
> > >
> > >  org.apache.tomcat.dbcp.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044)
> > >        org.apache.jsp.index_jsp._jspService(index_jsp.java:102)
> > >
> > >
> > I think if you have tomcat instantiate the data source and db driver, the
> > jar file with the driver must be available to tomcat's class loader.  Odds
> > are good that you have the driver packaged up with your webapp, which means
> > it is only available to the webapp's class loader.  Move it to wherever
>
> Thanx, but i actually had gone this step and had placed in dist $CATALINA_HOME/lib already.
> Nothing explicit in the webapp about the driver.
> I am starting to suspect that this has to do with declaring the resource as global under GlobalNamingResources
> and forgetting to do the proper linkage xml in context. But i think it should be placed inside
> context, in the first place and scrapping the GlobalNamingResources altogether.
>

Now its ok, i had to declare the datasource inside META-INF/context.xml of the webapp.
Thanx! And indeed it was 100% tomcat-related ;)

> > tomcat loads external jar files from (or add it to a classpath environment
> > variable that gets read by the tomcat startup script).  A quickie place to
> > drop it to see if I'm correct is $CATALINA_HOME/lib but I don't think it is
> > recommend that you put it there permanently, since it will be mixed with
> > the jars that ship as part of tomcat in there.
> >
>
>
>



--
Achilleas Mantzios