Re: Details of connection pooling

2020-11-20 Thread 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 

Phil

Details of connection pooling

2020-11-20 Thread 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")?