tomcat-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Ric Bernat" <...@brinydeep.net>
Subject RE: Tomcat 7 JDBC Connection Pool - question about usage from Java code
Date Sat, 25 Oct 2014 21:49:47 GMT
> First of all, let me apologize for typos and comprehension errors. I'm
writing 
> without glasses (magnification on screen is 200% and I'm taking frequent
breaks).

I am sorry to hear this is a burden for you. I appreciate you effort.

> I'm not sure why you're not using JNDI and letting Tomcat manage the
pooling.

The one and only reason I am not using JNDI is that new databases are
created at runtime. When a new company comes along, a new Postgres database
is created on the fly. I was assuming that I could not easily create new
JNDI database declarations (for just-created databases) at runtime. Perhaps
I am wrong about this.

> Now, in Spring you can wire things up in a bean. Here's an article with an
example.

I am not using Spring dependency injection -- just the JdbcTemplate library.
I'm using Guice for DI.

> Switching between multiple databases may be an issue.

We will definitely have multiple databases: likely dozens per server. That
is why I need to understand and configure the connection pooling precisely.

Thanks for your input.

-----Original Message-----
From: Mark Eggers [mailto:its_toasted@yahoo.com.INVALID] 
Sent: Saturday, October 25, 2014 1:44 PM
To: Tomcat Users List
Subject: Re: Tomcat 7 JDBC Connection Pool - question about usage from Java
code

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 10/25/2014 12:12 PM, Ric Bernat wrote:
> I am using Tomcat 7.0.53, and I am using Tomcat JDBC connection 
> pooling to connect to multiple PostgreSQL (9.3) databases. My 
> application is a JAX-RS (Jersey) web server application that provides 
> a set RESTful web services (no UI). My data layer uses the Spring 
> JdbcTemplate library.
> 
> First, let me say that I have everything working, and I am moving on 
> to tuning and optimization. I have read the Tomcat JDBC connection 
> pool docs, searched the web, asked a question on SO, and am still 
> struggling with a fairly basic concept or two.
> 
> My Java code looks pretty much like the "Plain Ol' Java" example from 
> the docs here:
> 
> http://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html#Plain_Ol%27_Jav
> a
>
>  As I said, my questions are fairly fundamental, so let me state my 
> first, fairly safe, assumption to get this started. Please let me know 
> if this assumption holds water.
> 
> Assumption #1: If I run code like this twice,|the two resulting 
> connections will come from the same Tomcat connection pool, even 
> though I am instantiating two separate instances of
> |||PoolProperties|, each on a separate thread in response to a
> separate JAX-RS web request.
> 
> PoolProperties poolProperties = new PoolProperties();
> ||||poolProperties.setDriverClassName("org.postgresql.Driver");
> ||||poolProperties.setUrl("jdbc:postgresql://myserver/db_a");
> ||||poolProperties.setMaxActive(10); ||||DataSource dataSource =
> org.apache.tomcat.jdbc.pool.DataSource(poolProperties);
> ||||NamedParameterJdbcTemplate namedParameterJdbcTemplate = new
> NamedParameterJdbcTemplate(dataSource);
> 
> To restate: I am assuming the two database connections for two 
> separate calls of this above code will both come from a single Tomcat 
> JDBC connection pool. Is this assumption correct?
> 
> [I will mention here that I have stripped out most of my code in order 
> to make the examples and discussion concise. I am actually setting 
> around 15 attributes on PoolProperties, but they don't matter for the 
> sake of this discussion. Also, the data access code is in a separate 
> class from the DataSource-creating code, but I show them together here 
> to keep it to the point.]
> 
> Next, I'll move on to my first question.
> 
> Question #1: Let's say I now run code just like the code above twice, 
> but connect to a different database each time. For example, first 
> "db_a", and then "db_b". Does a single connection pool hold 
> connections to both databases, or are two separate connection pools 
> created, one for db_a and one for db_b, each with a maxActive setting 
> of 10? Here is what the second run of the code would look
> like: || PoolProperties poolProperties = new PoolProperties();
> ||||poolProperties.setDriverClassName("org.postgresql.Driver");
> ||||poolProperties.setUrl("jdbc:postgresql://myserver/db_b");
> ||||poolProperties.setMaxActive(10); ||||DataSource dataSource =
> org.apache.tomcat.jdbc.pool.DataSource(poolProperties);
> ||||NamedParameterJdbcTemplate namedParameterJdbcTemplate = new
> NamedParameterJdbcTemplate(dataSource);
> 
> |The reason I ask, btw, is that I need to be able to control the
> total number of database connections my application opens to Postgres. 
> If separate database pools are opened for each database URL (i.e., 
> each database), then the math for how to set maxActive will be quite 
> different than the math I would use if a single connection pool 
> contains all connections to all databases.
> 
> Thanks in advance for any input.
> 
> Ric

First of all, let me apologize for typos and comprehension errors. I'm
writing without glasses (magnification on screen is 200% and I'm taking
frequent breaks).

That said, I'm not sure why you're not using JNDI and letting Tomcat manage
the pooling.

For each database connection you want, define a resource as per
documentation:

http://tomcat.apache.org/tomcat-7.0-doc/jndi-datasource-examples-howto.html

And as the following document points out here, change the factory attribute
to org.apache.tomcat.jdbc.pool.DataSourceFactory.

http://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html

Now, in Spring you can wire things up in a bean. Here's an article with an
example.

http://www.codejava.net/frameworks/spring/configuring-spring-mvc-jdbctemplat
e-with-jndi-data-source-in-tomcat

Note, I have not carefully read the above document nor have I tried this.
Caveat emptor. I might play with this once the new glasses arrive.

Switching between multiple databases may be an issue. However it looks like
you can do this with an AbstractRoutingDataSource extension.
There are several examples that I found searching via Google.

So, in short:

1. Configure pooling with Tomcat and JNDI 2. Reference the JNDI names in
Spring 3. Use an extended AbstractRoutingDataSource to programmatically
switch

. . . just my (vision - impaired) 2 cents /mde/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2

iQEcBAEBAgAGBQJUTAuZAAoJEEFGbsYNeTwtl9QIAKYDmFC+wwmQu/oGuZ+HxNkp
z/zggIVEnsShM1jnz5uzKIALOhghFf/ZCswPB3gn86ZLZJ237v1kkCPzNv1ZnQZX
yjHIfHlrBj3jsJ9TG07wbuzLeqVt0Rqrjh3b0+yYwjlFcAihNUHGE9EbbnG/y/bV
wmp79KfkBHptvOmhp0zk+pIrjiYcUFSwaNNhxDZHWnWlGeoaoR0J/nllGUQN1D7q
qFkoSBrEGR9FvAbESKOJDIpy/Qg511r6VylcLZH+6Xg9a38YBkdEgA/aGiMxaDcE
g64c/e73cl8v7EPRcTnC1sW8GH1Yx9nTybm5U/rPJ7i7yHEYGRndT5QAiw9fgdc=
=iYMF
-----END PGP SIGNATURE-----

---
This email is free from viruses and malware because avast! Antivirus
protection is active.
http://www.avast.com


---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org


Mime
View raw message