Thread: Best Practice to Create a Connection Pool in PostgreSQL

Greg James wrote:
>
> Hi,
>
> I’ve been looking through the mail archives in the JDBC forum and have
> seen a number of different ways to create a JDBC connection pool to
> Postgres. Could someone point me in the right direction, eg
> Jdbc3ConnectionPool, Apache's DBCP, etc.?
>


One reason there are so many ways is that there are different
requirements for connection pools.

Thanks for the advice.

Because I'm using Apache Tomcat 6, I ended up using the built-in Jakarta
Commons DBCP to create a connection pool to Postgres.  Not sure if this is a
best practice or not, but it appears to be working well.

For those interested in doing this on Tomcat, but don't know where to start,
my setup is below.

Thanks to those who provided the examples I found on the web and the text
"Professional Apache Tomcat 6" from Wrox.

I created a file "context.xml" in my web app's META-INF directory with the
following information:

<Context path="/your_web_app_name">
  <Resource name="jdbc/YourPool"
    auth="Container"
    type="javax.sql.DataSource"
    maxActive="100"
    maxIdle="30"
    maxWait="20000"
    removeAbandoned="true"
    removeAbandonedTimeout="120"
    username="username"
    password="password"
    driverClassName="org.postgresql.Driver"
    url="jdbc:postgresql://your_server_ip:5432/your_postgres_database_name"
  />
</Context>

From a java class you can access the connection pool by using the following
imports...

import java.sql.*;
import javax.sql.*;
import javax.naming.*;

...and java code wrapped in a try/catch block...

String dsString = "java:/comp/env/jdbc/YourPool";
DataSource ds = (DataSource) new InitialContext().lookup(dsString);
Connection con = ds.getConnection();

-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of John R Pierce
Sent: Tuesday, September 16, 2008 7:58 PM
To: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] Best Practice to Create a Connection Pool in PostgreSQL

Greg James wrote:
>
> Hi,
>
> I've been looking through the mail archives in the JDBC forum and have
> seen a number of different ways to create a JDBC connection pool to
> Postgres. Could someone point me in the right direction, eg
> Jdbc3ConnectionPool, Apache's DBCP, etc.?
>


One reason there are so many ways is that there are different
requirements for connection pools.


--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

On Tue, Sep 16, 2008 at 7:47 PM, Greg James <gregcjames@comcast.net> wrote:

I would like to ask question on this subject as well.

I've read a OOP design patterns book that features Java when
illustrating various patterns.  The book shows show to create various
mechanisms for pooling and releasing various resources (including
connections).  The book demonstrates this by using a hash map that
reference many linked lists for a particular kind of connection and
the link lists can reference many connection objects for one kind of
connection.

Would a hand crafted pooling mechanism not compete well with some of
the existing solutions mentioned?


--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

Why do you want to reinvent the wheel when some nice folks at Apache and
elsewhere have done the hard work, unit tested the code and maintained it. I
would roll out my own copy only when you are looking for a specific feature
that the driver is lacking.
--
View this message in context:
http://old.nabble.com/Best-Practice-to-Create-a-Connection-Pool-in-PostgreSQL-tp19524075p27153320.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.