Mailing-List: contact users-help@tomcat.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Tomcat Users List" <users@tomcat.apache.org>
Received-SPF: pass (nike.apache.org: domain of its_toasted@yahoo.com
 designates 98.138.91.230 as permitted sender)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s2048; d=yahoo.com;
	b=BP0i3zZ5KZeyPm0fyJBGOkjprV+RVcWrGZqNFWOdcUsSJsES150Xlq9w7Q7kWEtundhWSCq0lOor15Ju6wA8dgNIuim+anVRP795pK7+gzNwLhIXebc5PJMqiwQSDL53FMOU4QBKqoFipHRQ7OzbFWlghpUyaJdEJ2xTvGnIZ89Hp03k+iZtvo14nI5lSCEy/UZneKotvWV60xqpvz9+YiNTG7UQ2ibEFft6TvanPSr9ONRqx9qMMPLWFVYRC2o2KXecOSJmtgWnn10v4zT1FkI0N2+hDCFYxOsOFRfYvNd0LALE089JWO03mRFrPXXe1pb72RYKFdM4qlCwlg+rzQ==;
Message-ID: <544C0B99.6000306@yahoo.com>
Date: Sat, 25 Oct 2014 13:44:09 -0700
From: Mark Eggers <its_toasted@yahoo.com.INVALID>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
 rv:31.0) Gecko/20100101 Thunderbird/31.2.0
MIME-Version: 1.0
To: Tomcat Users List <users@tomcat.apache.org>
Subject: Re: Tomcat 7 JDBC Connection Pool - question about usage from Java
 code
References: <1414264381-595417.166630678.fs9PJCfO1011514@rs145.luxsci.com>
In-Reply-To: <1414264381-595417.166630678.fs9PJCfO1011514@rs145.luxsci.com>
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: 8bit

-----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_Java
>
>  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-jdbctemplate-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