JDBC connection pooling : Java Glossary

* 0-9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z (all)

JDBC connection pooling

There is quite an overhead to setting up a connection to an SQL (Standard Query Language) database via JDBC (Java Data Base Connectivity). You want to avoid doing it if possible for every transaction. On the other hand, there is a fair overhead for each connection and sometimes a fairly small limit on the simultaneous number of connections enforced by licencing. So you can’t very well leave a connection open per session. JDBC connections are single thread, so you cannot easily use a single connection for mulitiple purposes. The solution is to use a pool of connections, which works much like a pool of corporate vehicles. The pool of connections stay open, with a connection assigned to only a single given purpose at a time. Happily, it is unlikely you will need to write your own code to manage the pool of connnections.

Tips

A web application has to explictly close ResultSets, Statements and Connections. Failure to close these resources results in them never being available again for reuse — a db connection pool leak. You will eventually run out of connections.
Try to stay away from third party jars. If JDBC is enough, build on Tomcat.
If you need JPA or EJB3, go with JBoss or GlassFish. JBoss has Hibernate with it, while GlassFish has Oracle TopLink.
If you go with Hibernate, try to stick with JPA, do not use any Hibernate specific features.
If you take MVC design, you need third party jars for the implementation if you go with Tomcat. It does not have even the standard JSF in it. Tomcat would need maybe Apache MyFaces, while JBoss or GlassFish come with some implementation already there.
In Tomcat, read up on how configure a DataSource to a connection pool in the JNDI (Java Naming and Directory Interface) and JDBC datasource documentation.
Even though it looks as though you are using a PreparedStatement only once, behind-the-scenes code miraculously manages to recycle them. PreparedStatement also protects you from stray characters in your data from being interpreted as SQL commands.
In addition to putting JNDI code in your Servlet, you must configure the servlet womb. That will likely include making a special jar available to the Servlet womb.

Learning More

Oracle’s Miscellaneous documentation on connection pooling

Apache DBCP: connection pooling
Apache MyFaces
EE connection pooling
EJB
GlassFish
Hibernate
Java Ranch connection pooling tips
JBoss
JDBC
JDBC 3 connection pooling
JNDI
JPA
JSF
MiniConnectionPoolManager: a lightweight standalone JDBC connection pool manager
MVC
Oracle TopLink
performance tuning connection pooling
SQL
Tomcat
Tomcat: JNDI connection pooling how to
Tomcat: JNDI Datasource connection pooling examples

standard footer
	This page is posted on the web at:	http://mindprod.com/jgloss/jdbcconnectionpooling.html
	Optional Replicator mirror of mindprod.com on local hard disk J:	J:\mindprod\jgloss\jdbcconnectionpooling.html
	Please read the feedback from other visitors, or send your own feedback about the site. Contact Roedy. Please feel free to link to this page without explicit permission.
	Canadian Mind Products IP:[65.110.21.43] Your face IP:[3.12.152.102]
Feedback	You are visitor number