> 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_toas...@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-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org --------------------------------------------------------------------- To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
