How to use DataSource in Tomcat 8

Introduction

This works for Tomcat 7, 8 and 9, but here I will use Tomcat 8.

Installation

Copy wanted jdbc driver to $TOMCAT_HOME/lib.

$ cp ~/.m2/repository/com/h2database/h2/1.4.185/h2-1.4.185.jar ~/bin/apache-tomcat-8.5.15/lib/

$ cp ~/.m2/repository/mysql/mysql-connector-java/5.1.34/mysql-connector-java-5.1.34.jar ~/bin/apache-tomcat-8.5.15/lib/

DataSource Configuration

Add global datasource configuration in $TOMCAT_HOME/conf/server.xml. Here is a minimal server.xml with H2 database.

<?xml version="1.0" encoding="UTF-8"?>
<Server port="8005" shutdown="SHUTDOWN">

    <Listener className="org.apache.catalina.startup.VersionLoggerListener" />
    <Listener className="org.apache.catalina.core.AprLifecycleListener"
        SSLEngine="on" />
    <Listener className="org.apache.catalina.core.JreMemoryLeakPreventionListener" />
    <Listener className="org.apache.catalina.mbeans.GlobalResourcesLifecycleListener" />
    <Listener className="org.apache.catalina.core.ThreadLocalLeakPreventionListener" />

    <GlobalNamingResources>

        <Resource name="UserDatabase" auth="Container"
            type="org.apache.catalina.UserDatabase" description="User database that can be updated and saved"
            factory="org.apache.catalina.users.MemoryUserDatabaseFactory"
            pathname="conf/tomcat-users.xml" />

        <Resource name="ExampleDS" auth="Container" type="javax.sql.DataSource"
            driverClassName="org.h2.Driver" url="jdbc:h2:mem:example" username="sa"
            password="" maxTotal="20" maxIdle="10" maxWaitMillis="-1" />
    </GlobalNamingResources>

    <Service name="Catalina">

        <Connector port="8080" protocol="HTTP/1.1" connectionTimeout="20000"
            redirectPort="8443" />

        <Connector port="8009" protocol="AJP/1.3" redirectPort="8443" />

        <Engine name="Catalina" defaultHost="localhost">

            <Host name="localhost" appBase="webapps" unpackWARs="true"
                autoDeploy="true">

                <Valve className="org.apache.catalina.valves.AccessLogValve"
                    directory="logs" prefix="localhost_access_log" suffix=".txt"
                    pattern="%h %l %u %t "%r" %s %b" />

            </Host>
        </Engine>
    </Service>
</Server>

And here is for MySQL

<Resource name="jdbc/TestDB" auth="Container" type="javax.sql.DataSource" 
    driverClassName="com.mysql.jdbc.Driver"
    url="jdbc:mysql://localhost:3306/javatest"
    username="javauser" password="javadude" 
    maxTotal="20" maxIdle="10" maxWaitMillis="-1" />

For a complete list of configuration for Apache Commons DBCP, see http://commons.apache.org/proper/commons-dbcp/configuration.html.

Application

Now add a resource link in you application $APP_HOME/META-INF/context.xml.

<?xml version="1.0" encoding="UTF-8"?>
<Context path="/example-tomcat">

    <!-- java:/comp/env/jdbc/ExampleDS -->
    <ResourceLink name="jdbc/ExampleDS" global="ExampleDS"
        type="javax.sql.DataSource" />
</Context>

And to access inside your tomcat app.

package se.magnuskkarlsson.example.tomcat;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;

@WebServlet(name = "HelloServlet", urlPatterns = "/hello")
public class HelloServlet extends HttpServlet {

    private static final long serialVersionUID = 1L;

    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        try {
            resp.getWriter().println("Data from database " + getDBValue());
        } catch (Exception e) {
            super.log("FAILED to read from database. Cause " + e.getMessage());
            resp.getWriter().println("FAILED to read from database. Cause " + e.getMessage());
        }
    }

    protected int getDBValue() throws NamingException, SQLException {
        Connection conn = null;
        PreparedStatement prepstmt = null;
        ResultSet rs = null;
        try {
            Context ctx = new InitialContext();
            DataSource ds = (DataSource) ctx.lookup("java:/comp/env/jdbc/ExampleDS");
            conn = ds.getConnection();
            prepstmt = conn.prepareStatement("SELECT 1");
            rs = prepstmt.executeQuery();
            rs.next();
            return rs.getInt(1);
        } finally {
            closeQuietly(rs);
            closeQuietly(prepstmt);
            closeQuietly(conn);
        }
    }

    protected void closeQuietly(AutoCloseable closeable) {
        if (closeable != null) {
            try {
                closeable.close();
            } catch (Exception IGNORE) {
            }
        }
    }

}