0

I am creating a simple blogging website just as a learning project using Java servlets on Tomcat.

There will be two pages:

  • first page is a form to enter the subject and text for the blog, inserts them into a MySQL database using JDBC.
  • second page is a page which fetches data from the database and displays it.

I was wondering if instead of opening and closing the JDBC database connection in each page separately, I should create a separate class which keeps a connection open.

However, I don't want to instantiate it, and don't want to make it static.

Is there a way to do this?

Also, is it a good practice and is it safe to keep a database connection open as long as the server is running?

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Sid
  • 125
  • 2
  • 11

2 Answers2

4

Think of a connection pool

Connection pools give you a whole bunch of advantages, but most of all they solve the problems of

  1. Making a real database connection is costly. The connection pool always has a few extra connections around and gives you one of those.
  2. If the connections fail, the connection pool knows how to open a new one
  3. Very important: every thread gets its own connection. This means that threading is handled where it should be: at the DB level. DBs are super efficient and can handle concurrent request with ease.
  4. Other stuff (like centralizing location of JDBC connect strings, etc.), but there are millions of articles, books, etc. on this

The following page on Tomcat's website describes how to connect Tomcat and mySQL in detail. You do not want to roll your own, there are too many DataSource pools already available that have been debugged and tried in production environments.

The main thing about using a pool is that a connection is not terminated when you call close, instead it is just returned to the pool. Therefore it is important to make sure that you close your resources in a try/finally block. Look here for a sample.

You're in a servlet, so you should use the connection pool the container provides. Your JNDI code will be completely normal except for how you obtain the connection. As far as I know, all servlet containers have connection pools.

Prashant Shilimkar
  • 8,402
  • 13
  • 54
  • 89
  • thnaks, I was thinking along the same lines, but I did not have the faintest idea there was something called a connection pool. – Sid Jan 11 '14 at 04:53
0

You should install a JDBC datasource on Tomcat (see Tomcat JNDI Datasource HOW-TO). Use the datasource to establish your JDBC connection, and you will have pooled connections.

Glenn Lane
  • 3,892
  • 17
  • 31