Details of connection pooling

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Details of connection pooling

Rob Sargent
I'm using tomcat 9.0.+ and wish to use the built in connection pooling
to connect to a postgres server.

I would like to understand the lookup mechanism for the next available
connection. I create a context

    contextResource.setName("jdbc/sgsdb");
    contextResource.setType("javax.sql.DataSource");
    String turl =String.format("jdbc:postgresql://%s:%d/%s", host, port, dbname);
    contextResource.setProperty("url",turl);
    contextResource.setProperty("driverClassName","org.postgresql.Driver");

which names a specific database.  Then I make a user specific connection

    DataSource ds = (DataSource)env.lookup("jdbc/sgsdb");
    conn =ds.getConnection(user, pwd);

1. To access a different database do I need to make a database-specific
DataSource?

2. Is the username used to locate an available connection for that user
(maybe creating one if not found) or is the user overlain on an existing
connection (perhaps "set role user")?



Reply | Threaded
Open this post in threaded view
|

Re: Details of connection pooling

Phil Steitz

On 11/20/20 10:25 AM, Rob Sargent wrote:

> I'm using tomcat 9.0.+ and wish to use the built in connection pooling
> to connect to a postgres server.
>
> I would like to understand the lookup mechanism for the next available
> connection. I create a context
>
>    contextResource.setName("jdbc/sgsdb");
>    contextResource.setType("javax.sql.DataSource");
>    String turl =String.format("jdbc:postgresql://%s:%d/%s", host,
> port, dbname);
>    contextResource.setProperty("url",turl);
> contextResource.setProperty("driverClassName","org.postgresql.Driver");
>
> which names a specific database.  Then I make a user specific connection
>
>    DataSource ds = (DataSource)env.lookup("jdbc/sgsdb");
>    conn =ds.getConnection(user, pwd);
>
> 1. To access a different database do I need to make a
> database-specific DataSource?

Yes.

>
> 2. Is the username used to locate an available connection for that
> user (maybe creating one if not found) or is the user overlain on an
> existing connection (perhaps "set role user")?

No, all connections from a (default, DBCP BasicDatacsource) pool share
the same database credentials.

If you need separate pools per user, have a look at  the
PerUserPoolDataSource [1] provided by Commons DBCP.

[1] https://s.apache.org/dlghr <https://s.apache.org/dlghr>

Phil

>
>
>
>