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