How should I connect to a MySQL data source from Eclipse?

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 J2EE 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

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).

Answer 2

A common practice is to configure this as a DataSource in the webserver 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.

It's unclear which webserver (servletcontainer/appserver) you're using, so I'll just give a Tomcat example. You need to configure the datasource as per the webserver-supplied JNDI documentation. In case of Tomcat it's here: JNDI Resources HOW-TO. 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 the webapplication context 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 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 SomeRuntimeException(jndiname + " is missing in JNDI!", e);
    }
}

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

..and replace all DriverManager.getConnection() calls by new Database("jdbc/db") and use database.getConnection() to acquire the Connection. You can obtain the value jdbc/db from some config file (Properties file?).

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 driver is now moved to Tomcat. For more hints and other basic JDBC/JNDI examples you may find this article useful as well.