[Pljava-dev] pljava reninitializing jdbc connection pool for every invocation

Hi

Following is the scenario:

A plpgsql function calls a java method using pljava. The initialization
of the jdbc pool seems to be happening with every call to the
getConnection method.

Is there a way to implement singletons within pljava

This is the java code that initializes the pool

private static Jdbc3PoolingDataSource pool;

private static Logger logger = Logger.getLogger(PostgresAdapter.class);

private static PropertyResourceBundle resourceBundle;

public static void setupPool() throws Exception
{

logger.info("***setting up postgres
pool");

PropertyResourceBundle
resourceBundle = null;

resourceBundle = new
PropertyResourceBundle(new FileInputStream("postgres-ds.properties"));

pool = new Jdbc3PoolingDataSource();

logger.info("***finished setting up
postgres pool");

pool.setServerName(resourceBundle.getString("serverName"));

pool.setPortNumber(Integer.parseInt(resourceBundle.getString("portNumber
")));

pool.setDatabaseName(resourceBundle.getString("databaseName"));

pool.setUser(resourceBundle.getString("user"));

pool.setPassword(resourceBundle.getString("password"));

pool.setMaxConnections(25);

}

public static Connection getConnection() throws
Exception {

if(pool == null) {

setupPool();

}

Connection con =
pool.getConnection();

con.setAutoCommit(false);

return con;

}

Many thx in advance for answers

Sriram

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.pgfoundry.org/pipermail/pljava-dev/attachments/20060320/3c1ff720/attachment.html>

Hi Sriram,
PostgreSQL spawns a new backend process each time you make a connection.
PL/Java will initialize a Java Virtual Machine instance the first time
you call a PL/Java function using a connection. The JVM becomes a part
of the backend process and its lifespan is thus equal to the lifespan of
your connection. So while its indeed possible to create singletons in
PL/Java, such a singleton is per connection.

From the looks of it, you are trying to set up a pool of DataSources
that will allow you to connect to other PostgreSQL instances from within
PL/Java. I'm a bit curious why you would like to do that. Can you shed
some light on what it is you want to do? Perhaps I can help suggesting
an alternative.

Kind Regards,
Thomas Hallgren

Sriram Dandapani wrote:
>
> Hi
>
>
>
> Following is the scenario:
>
>
>
> A plpgsql function calls a java method using pljava. The
> initialization of the jdbc pool seems to be happening with every call
> to the getConnection method.
>
> Is there a way to implement singletons within pljava
>
>
>
> This is the java code that initializes the pool
>
>
>
> private static Jdbc3PoolingDataSource pool;
>
> private static Logger logger = Logger.getLogger(PostgresAdapter.class);
>
> private static PropertyResourceBundle resourceBundle;
>
>
>
> public static void setupPool() throws Exception {
>
> logger.info("***setting up
> postgres pool");
>
> PropertyResourceBundle
> resourceBundle = null;
>
> resourceBundle = new
> PropertyResourceBundle(new FileInputStream("postgres-ds.properties"));
>
> pool = new Jdbc3PoolingDataSource();
>
> logger.info("***finished setting
> up postgres pool");
>
>
> pool.setServerName(resourceBundle.getString("serverName"));
>
>
> pool.setPortNumber(Integer.parseInt(resourceBundle.getString("portNumber")));
>
>
> pool.setDatabaseName(resourceBundle.getString("databaseName"));
>
>
> pool.setUser(resourceBundle.getString("user"));
>
>
> pool.setPassword(resourceBundle.getString("password"));
>
> pool.setMaxConnections(25);
>
> }
>
>
>
> public static Connection getConnection()
> throws Exception {
>
> if(pool == null) {
>
> setupPool();
>
> }
>
> Connection con = pool.getConnection();
>
> con.setAutoCommit(false);
>
> return con;
>
> }
>
>
>
> Many thx in advance for answers
>
>
>
> Sriram
>
>
>
> ------------------------------------------------------------------------
>
> _______________________________________________
> Pljava-dev mailing list
> Pljava-dev at gborg.postgresql.org
> http://gborg.postgresql.org/mailman/listinfo/pljava-dev
>