Database persistance

This chapter shows you how to create a JNDI resource that represents a JDBC DataSource in Tomcat, and then how to access the DataSource. One of the most important benefits of using a JNDI DataSource is utilizing database connection pooling services provided by the container

As the first step, we’ll setup the database and database tables for our project, therefore you should be familiar with the MySQL Workbench tool. Use this tool to create databases and tables which are needed to authenticate users, validate them against predefined roles, and to store GPS data. Whether you create these tables within one single database scheme or seperate schemes is up to you. User authentification is an administrative task on a company level and should not be stored in an application specific database. For simplicity sake, though, we’ll create a database ‘tomcat’ which holds all our tables for authenticating users and GPS data. Let’s start with the authentication tables for Tomcat.

CREATE DATABASE IF NOT EXISTS tomcat;
 
USE tomcat;
 
CREATE TABLE users(
    username VARCHAR(15) NOT NULL PRIMARY KEY,
    password VARCHAR(32) NOT NULL
);
 
CREATE TABLE user_roles(
    username VARCHAR(15) NOT NULL,
    rolename VARCHAR(20) NOT NULL,
    PRIMARY KEY(username, rolename)
);
 
 
CREATE USER 'tomcat'@'localhost' IDENTIFIED BY 'tomcat';
GRANT SELECT ON tomcat.* TO 'tomcat'@'localhost';
FLUSH PRIVILEGES;

Please note that this setup of tables and user ‘tomcat’ corresponds to the JDBCRealm definition in Tomcat’s server.xml configuration file. Open server.xml and add the following entry, preferably after the org.apache.catalina.realm.UserDatabaseRealm entry:

<Realm className="org.apache.catalina.realm.JDBCRealm"
    driverName="org.gjt.mm.mysql.Driver"
    connectionURL="jdbc:mysql://localhost/tomcat"
    connectionName="tomcat" connectionPassword="tomcat"
    userTable="users" userNameCol="username" userCredCol="password"
    userRoleTable="user_roles" roleNameCol="rolename"
    digest="md5"/>

Now our servlet comes into play. Remember lines 48 – 50 of our servlet code:

      ServletContext ctx = this.getServletContext();
      DBConnectionManager dbManager = (DBConnectionManager) ctx.getAttribute("DBManager");
      Connection connection = dbManager.getConnection();

We haven’t seen the corresponding DBConnectionManager Java code yet. Tomcat provides a JNDI InitialContext implementation instance for each web application running under it, in a manner that is compatible with those provided by a Java Enterprise Edition application server. The Java EE standard provides a standard set of elements in the /WEB-INF/web.xml file to reference/define resources:

package org.psychomechanics.iot.arduino_recv;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

public class DBConnectionManager {

	private Connection connection;

	public DBConnectionManager() throws ClassNotFoundException, SQLException{
		/* 
		 * See paragraph "JDBC Data Sources" at https://tomcat.apache.org/tomcat-8.0-doc/jndi-resources-howto.html
		 */
		Context initContext;
		try {
			initContext = new InitialContext();
			Context envContext  = (Context)initContext.lookup("java:/comp/env");
			DataSource ds = (DataSource)envContext.lookup("jdbc/tomcat");
			connection = ds.getConnection();
		} catch (NamingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public Connection getConnection(){
		return this.connection;
	}

	public void closeConnection(){
		try {
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

To prevent instantiating the DBCOnnectionManager class every time data is sent to the servlet, we let the ServletContextListener create it and make it permannet by putting a reference to it into the servlet context. From there our servlet freely retrieves the reference and make use of it. See the ServletContextListener code:

package org.psychomechanics.iot.arduino_recv;

import java.sql.SQLException;

import javax.servlet.ServletContext;
import javax.servlet.ServletContextEvent;
import javax.servlet.ServletContextListener;
import javax.servlet.annotation.WebListener;

@WebListener
public class MyServletContextListener implements ServletContextListener {

	public void contextInitialized(ServletContextEvent servletContextEvent) {
		ServletContext ctx = servletContextEvent.getServletContext();

		try {
			DBConnectionManager connectionManager = new DBConnectionManager();
			ctx.setAttribute("DBManager", connectionManager);
			System.out.println("DB Connection initialized successfully.");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void contextDestroyed(ServletContextEvent servletContextEvent) {
		ServletContext ctx = servletContextEvent.getServletContext();
		DBConnectionManager dbManager = (DBConnectionManager) ctx.getAttribute("DBManager");
		dbManager.closeConnection();
		System.out.println("Database connection closed for Application.");
	}
}

We are almost there, but not yet. A few settings need to be done in the deployment descriptor web.xml:

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns="http://java.sun.com/xml/ns/javaee"
	xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"
	version="2.5">
	<display-name>arduino_recv</display-name>
	<login-config>
		<auth-method>BASIC</auth-method>
		<realm-name>default</realm-name>
	</login-config>
	<resource-ref>
		<description>DB Connection</description>
		<res-ref-name>jdbc/tomcat</res-ref-name>
		<res-type>javax.sql.DataSource</res-type>
		<res-auth>Container</res-auth>
	</resource-ref>
	<welcome-file-list>
		<welcome-file>index.html</welcome-file>
	</welcome-file-list>
</web-app>

Well, this was a lot to digest. For more information about what we’ve done here, see https://tomcat.apache.org/tomcat-8.0-doc/jndi-resources-howto.html. where you can find much of the code of this section. Pay particular attention to the paragraph “JDBC Data Sources”.