How should I connect to JDBC database / datasource in a servlet based application?

Question

I have an external MySQL server that's set up and working fine. I created a database connection in Eclipse and can view the database in the Data Source Explorer tab.

Now, I have a servlet that needs to access that database. How do I do it? Is there a way to reference that database connection created in the data source explorer, or do I have to define everything twice?

Also, what's the best way to open the connection? I've got the mysql-connector-java-5.1.11-bin.jar file included, and I've found two methods that work:

MysqlDataSource d = new MysqlDataSource();
d.setUser("user");
d.setPassword("pass");
d.setServerName("hostname.com");
d.setDatabaseName("db");
Connection c = d.getConnection();

and

Connection c = DriverManager.getConnection("jdbc:mysql://hostname.com/db","user","pass");

Neither is optimal, because first of all, they both use hard-coded strings for everything. This is a Java EE web app project, so is there a good place to put connection data? Or is there a way to forgo all that and just use the connection in the data source explorer?

Answer 1

A common practice is to configure this as a DataSource in the servlet container in question. It will provide you connection pooling facilities which will greatly improve performance. Also a common practice is to externalize the raw settings in some configuration file which is been placed in the classpath.

In case you're using Tomcat as servletcontainer, you need to configure the datasource as per its JNDI documentation. You'll see that there are several ways. Easiest way is to create a /META-INF/context.xml in the webcontent of your dynamic web project (to be clear, the /META-INF is at the same level as the /WEB-INF of the webapp) and fill it with something like:

<?xml version="1.0" encoding="UTF-8"?>

<Context>
    <Resource
        name="jdbc/db" type="javax.sql.DataSource"
        maxActive="100" maxIdle="30" maxWait="10000" 
        url="jdbc:mysql://hostname.com/db"
        driverClassName="com.mysql.jdbc.Driver"
        username="user" password="pass"
    />
</Context>

This roughly means that Tomcat server should create a datasource with the JNDI name jdbc/db with a maximum of 100 active connections, a maximum of 30 idle connections and a maximum wait time of 10000 milliseconds before a connection should be returned from your application (actually: closed by your application, so your application has 10 seconds time between acquiring the connection and closing the connection). The remnant of the settings should be familiar and self-explaining enough to you; those are the JDBC settings.

Finally in your web project, edit the file /WEB-INF/web.xml to add the following entry:

<resource-env-ref>
    <resource-env-ref-name>jdbc/db</resource-env-ref-name>
    <resource-env-ref-type>javax.sql.DataSource</resource-env-ref-type>
</resource-env-ref>

This roughly means that the webapplication should use the server-provided datasource with the name jdbc/db.

Then change your connection manager to something like this:

private DataSource dataSource;

public Database(String jndiname) {
    try {
        dataSource = (DataSource) new InitialContext().lookup("java:comp/env/" + jndiname);
    } catch (NamingException e) {
        // Handle error that it's not configured in JNDI.
        throw new IllegalStateException(jndiname + " is missing in JNDI!", e);
    }
}

public Connection getConnection() {
    return dataSource.getConnection();
}

..and replace all Class.forName(driver) calls by new Database("jdbc/db") and replace all DriverManager.getConnection() calls by database.getConnection(). You can if necessary obtain the value jdbc/db from some config file (Properties file?).

Alternatively, inject the DataSource via the @Resource annotation inside a container managed artifact, such as a @WebServlet servlet class:

@Resource(name="jdbc/db")
private DataSource dataSource;

That should be it. Just deploy your webapplication with the above changes and run it. Don't forget to place the database JDBC driver in the Tomcat/lib or to add its path to the shared.loader property of Tomcat/conf/catalina.properties, because the responsibility of loading the JDBC driver is now moved from the webapplication to the server. For more hints and other basic JDBC/JNDI examples you may find this article useful as well.

See also:

• How to install JDBC driver in Eclipse web project without facing java.lang.ClassNotFoundexception
• Where do I have to place the JDBC driver for Tomcat's connection pool?
• Is it safe to use a static java.sql.Connection instance in a multithreaded system?
• Show JDBC ResultSet in HTML in JSP page using MVC and DAO pattern
• How to retrieve and display images from a database in a JSP page?

Answer 2

You could set up a data source in whatever app server you're deploying your WAR to and fetch a reference to it with JNDI. Or you could package your WAR in an EAR and define the data source in the EAR's data-sources.xml file (and fetch a reference to it with JNDI).