What are the different ways of connecting to a database from a servlet? Which should I use?

[Pat Mig]

I'm trying to learn JSP + Servlets by building a simple page that allows users to register and log in. I'd like to use a Postgres database for that. I'm using Eclipse as an IDE and Tomcat as a container.
I managed to get my JSP page working and now I'm building my servlet, but I'm not sure about how to approach the database connection. The info I found online is a bit confusing as there seem to be different ways of connecting to a database, is that so?

For example, I've seen this pooling thing where a context.xml is created and then the db is accessed by creating a Context object.
But I've also seen a different approach where Class.forname(drivername) is called to then create a Connection object from a DriverManager.
On some other tutorials I've seen people create a database connection profile through Eclipse Data Source Explorer, where they import a driver they download from jdbc.postgresql.org.

I'm not sure if there are more ways or those are the main ones. I'm also confused about what are the differences between them and which approach would be the best, given that I am using Eclipse. I downloaded the jdbc driver but I don't know if or how I should use it.
Any help or links to a good tutorial that might clarify this will be very welcome

Thanks!!

[khadar valli]

Hi Mig,

In General, We will use the method "class.forname()" for JDBC connections. It is very simple to use and very flexible. You should add that jdbc driver to the lib folder inside of Web-content in your project. Then only your servlet will able to load the driver and will connect to the database.

Please refer this examples.. http://www.mkyong.com/tutorials/jdbc-tutorials/ . This link is for your reference only.

Hope it will help you.

Regards,
Khadar.

[Tim Moores]

In a servlet environment I would advise to use whatever DB connection pool is supported by the servlet container. In Tomcat's case, check out how the DefaultDS DataSource is set up in conf/context.xml.

If you want to learn JDBC, I advise to do so using a standalone app, not in a web app. You'll have much quicker turnaround during development and testing.

[Tim Holloway]

khadar valli wrote:Hi Mig,

In General, We will use the method "class.forname()" for JDBC connections.

No No No No No!

Servlets implement web applications. Web applications are not continuously-running programs, they're bits of code that run off of threads assigned on a very short-term basis. They Must not attempt to hold database Connections between requests, but instead are required to obtain and release them entirely within a single request and repeat as needed on subsequent requests. If you attempt to hold a Connection between requests, at best you could end up starving the database server and/or local machine for resources and at worst, the JEE server might attempt to serialize the Connection. And Connections are not serializable, which means that they can be damaged if that is done. In fact, you could potentially crash the whole server. Pool Connections are much more efficient than "brute-force" connections. They don't require all the extra OS and network overhead to open.

Use a Connection Pool instead. Obtain your Connections from the pool and release them back to the pool (close them) as soon as possible in order to make most efficient use of the finite set of Connections in the pool.

Connection pools are not appserver-specific. They are defined as part of the core implementation, so although the external implementation details may vary between various pool plugins and appservers, the consuming web application doesn't see those details and is invariant. The same code that works in Tomcat will work in WebSphere and in Wildfly.

You use JNDI to locate the pool you need (an app can have multiple pools if multiple databases are being connected to). Looking up the named connection pool will return a reference to an implementation of javax.sql.DataSource. Invoke the "getConnection()" method on this DataSource to obtain a connection from the pool - and, repeating - close that Connection as soon as you can.

Some J2EE persistence facilities may use alternative mechanisms to interface with their connection pools. For example, JPA-compliant ORMs use EntityManagers. These are usually built on top of a standard connection pool, however.