How to configure Tomcat to connect with MySQL

Question

Could someone provide a few details on how to configure Tomcat to access MySQL?

1. In which directory within Tomcat do I place mysql-connector-java-5.1.13-bin? Should I place it under Tomcat 6.0\webapps\myapp\WEB-INF\lib?

2. Do I need to add configuration to context.xml or server.xml?

3. Should I create a web.xml file and place it under Tomcat 6.0\webapps\myapp\WEB-INF? If so, then what should the contents of this file look like?

Estragon

Answer 1

1: Where to place mysql-connector-java-5.1.13-bin in Tomcat directory? Should I place it under Tomcat 6.0\webapps\myapp\WEB-INF\lib?

That depends on where the connections are to be managed. Normally you would like to create a connection pooled JNDI datasource to improve connecting performance. In that case, Tomcat is managing the connections and need to have access to the JDBC driver. You should then drop the JAR file in Tomcat/lib.

But if you're doing it the basic way using DriverManager#getConnection(), then it in fact don't matter if you drop it in Tomcat/lib or YourApp/WEB-INF/lib. You however need to realize that the one in Tomcat/lib will apply for all deployed webapps and that the one in YourApp/WEB-INF/lib will override the one in Tomcat/lib for only the particular webapp.

---

2: Do I need to confirgure context.xml or server.xml files?

That depends on where the connections are to be managed. When using a JNDI datasource, it suffices to configure it using YourApp/META-INF/context.xml like follows (just create file if not exist):

<?xml version="1.0" encoding="UTF-8"?>
<Context>
    <Resource
        name="jdbc/yourdb" type="javax.sql.DataSource"
        maxActive="100" maxIdle="30" maxWait="10000" 
        url="jdbc:mysql://localhost:3306/yourdb"
        driverClassName="com.mysql.jdbc.Driver"
        username="yourname" password="yourpass"
    />
</Context>

and the YourApp/WEB-INF/web.xml as follows:

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

If you're doing it the basic DriverManager way, then it's all up to you. Hardcoded, properties file, XML file, etcetera. You should manage it youself. Tomcat won't (and can't) do anything useful for you.

Noted should be that the YourApp/META-INF/context.xml is specific to Tomcat and clones. Each servletcontainer/appserver has its own way of defining JNDI resources. In Glassfish for example, you'd like to do that through the webbased admin interface.

---

3: Should I write web.xml file and need to place under Tomcat 6.0\webapps\myapp\WEB-INF? If Yes, then what should be the contents of file?

You should always supply one. It's not only to configure resources, but also to define servlets, filters, listeners and that kind of mandatory stuff to run your webapp. This file is part of the standard Servlet API.

See also:

• Is it safe to use a static java.sql.Connection instance in a multithreaded system?
• How should I connect to JDBC database / datasource in a servlet based application?
• Where do I have to place the JDBC driver for Tomcat's connection pool?
• DAO Tutorial - basic JDBC/DAO tutorial, targeted on Tomcat/JSP/Servlet

Nolte

Answer 2

The answer to your questions:

1. One option is what you've mentioned: place the driver under WEB-INF/lib directory in your WAR file. The other would be in $TOMCAT_HOME/lib directory. The advantage of the latter would be that you don't need to copy the connector jar into every single project you deploy on that application server. Disadvantage is you will need to remember to put the jar file in place before deploying your application in a different application server.
2. If you need to change something in the default configuration, yes. Otherwise, there are context.xml and server.xml files with default options shipped with tomcat installations.
3. Your application's (WAR) web.xml should be under WEB-INF directory in your deploy file. You can look at the accepted content to that file in Java EE's servlet container specification. Usually, you place your servlet, filter and their corresponding mappings in that file.

Intratelluric