helpinghand
search
needassistance
 
How To
Use Mimer SQL with Tomcat 4
Category: Tools and Interfaces
Introduction

Tomcat is the reference implementation of Java Server Pages (JSP) and Servlets and it is a sub project of the Apache project. Version 4.0 implements the Servlet 2.3 and JSP 1.2 specifications.

As Tomcat is the reference implementation, it has always been a good choice for development, and since version 4, performance improvements make it a good candidate for deployment as well.

This article describes how to use Tomcat 4 database components with Mimer SQL. This includes how to set-up a connection pool, bind the pool to JNDI and how to use JDBCRealm to use a database for authentication.		Visit Tomcat
Click here to visit the project.
Description

Setting-up Tomcat

To set-up Tomcat you usually have to edit the main configuration file, server.xml. This file is located in <tomcat-home>/conf.

To use Tomcat 4 database components with Mimer SQL you have to make sure that Tomcat can find the Mimer JDBC driver. To do this without editing any files, simply copy the JDBC driver to <tomcat-home>/common/lib.

Authentication

Tomcat supports authentication as described in the Java Servlet 2.3 and JavaServer Pages 1.2 specifications and it provides a couple of techniques to store and validate user names, passwords and group belongings.

Storing User Information
JDBCRealm is a technique for storing user information in a database. The first step to enable storing user information is to create the necessary tables.

In Mimer SQL, you can use the following SQL statements to create the necessary tables:
create table USERS(
                  USER_NAME CHARACTER VARYING(15),
                  USER_PASS CHARACTER VARYING(60),
                  primary key(USER_NAME)
                  );
create table USER_ROLES(
                        USER_NAME CHARACTER VARYING(15),
                        ROLE_NAME CHARACTER VARYING(15),
                        primary key(USER_NAME,ROLE_NAME)
                        );

Configuring Tomcat

When you have created the tables, the next step is to configure Tomcat. By default, MemoryRealm is enabled. Start by removing or commenting out MemoryRealm, for example:
<!--
<Realm className="org.apache.catalina.realm.MemoryRealm"/>
-->

Add the JDBCRealm, for example:
<Realm  className="org.apache.catalina.realm.JDBCRealm" 
        debug="99" 
        digest="MD5"
        driverName="com.mimer.jdbc.Driver"
        connectionURL="jdbc:mimer://system:system@localhost/SystemDB"
        userTable="users" 
        userNameCol="user_name" 
        userCredCol="user_pass"
        userRoleTable="user_roles" 
        roleNameCol="role_name"/>

As you can see from the example above, you can define both the table names and the column names in server.xml.

The debug="99" tells the system to use debug, and digest="MD5" says that the passwords in the database should be encrypted using the MD5 algorithm. If you remove the digest attribute, the passwords can be stored in clear text.

Encrypting Passwords

There is a convenience utility to help you encrypt the passwords so you can insert them in the database. To use this, issue the command:

java -cp catalina.jar org.apache.catalina.realm.RealmBase -a MD5 password

This will print the encrypted password. Possible values for digest are MD5, SHA and MD2.

This is all that is needed to use standard security in your Web application.

Connection Pooling

The J2EE standard requires J2EE Application Servers to have a data source implementation, and Tomcat 4 conforms to this.

By default, Tomcat 4 uses a connection pool called Tyrex, but you can change that.

Setting-up a Connection Pool
To setup a connection pool using the default mechanism is quite simple. All configuration is done in the server.xml configuration file.

The following is an example of a pool:
<Resource name="jdbc/MimerPool" auth="Container" type="javax.sql.DataSource"/>
<ResourceParams name="jdbc/MimerPool">
    <parameter>
        <name>user</name>
        <value>HOTELADM</value>
    </parameter>
    <parameter>
        <name>password</name>
        <value>HOTELADM</value>
    </parameter>
    <parameter>
        <name>driverClassName</name>
        <value>com.mimer.jdbc.Driver</value>
    </parameter>
    <parameter>
        <name>driverName</name>
        <value jdbc:mimer://localhost/oneWindowDB</value>
    </parameter>
</ResourceParams>
Enter the above code in the web application’s context in server.xml.

Making the Data Source Available

To make the data source available to your application, you have to specify it in web.xml, the standard configuration file for your web application.

You do this as follows:
<resource-ref>
	<resource-ref-name>jdbc/MimerPool</resource-ref-name>
	<res-type>javax.sql.DataSource</res-type>
	<res-auth>Container</res-auth>
</resource-ref>

Including the Pool in Your Code

When you want to use the configured pool from within your Java code you fetch a data source via JNDI and then fetch a connection from that data source. This includes three steps:
  1. Get an initial context, for example:
    Context = new InitialContext();
  2. Get a data source, for example:
    DataSource ds = (DataSource) context.lookup("java:comp/env/jdbc/MimerPool");
  3. Get a connection, for example:
    Connection con = ds.getConnection();
Environment

  • Mimer SQL 8.2
  • Tomcat 4
  • Java Runtime Environment 1.3
Example

The following example is a simple servlet that queries the database we used in the examples above and prints all tables the current user has access to.
package se.mimer.informationtechnology.examples;

import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;
import java.util.*;
import javax.sql.*;
import java.sql.*;
import javax.naming.*;

/**
 * TestServlet retrieves and prints all tables in the database owned by the
 * current user. The connection is fetched from the connection pool in
 * Apache Tomcat
 */

public class TestServlet extends HttpServlet {
  private static final String CONTENT_TYPE = "text/html";
  private static DataSource dataSource = null;

  /**Initialize global variables*/
  public void init() throws ServletException {
    try {
      /**
       * Get an initial context to be used when querying JNDI. Since querying JNDI
       * is a quite expensive operation it's only done once. The dataSource will
       * be shared between all requests for the servlet, 
       * and that's what we want.
       */
      javax.naming.Context context = new javax.naming.InitialContext();
      //Get the Datasource from JNDI. This is the standard way to do it.
      dataSource = (DataSource) context.lookup("java:comp/env/jdbc/DBPool");
    }
    catch(NamingException ne) {
      throw new ServletException("Error looking up dataSource: " + 
                                  ne.getMessage());
    }
  }

  /**Process the HTTP Get request*/
  public void doGet(HttpServletRequest request, HttpServletResponse response) 
              throws ServletException, IOException {
    response.setContentType(CONTENT_TYPE);
    Connection con = null;
    ResultSet rs = null;
    String sql = "SELECT TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES";
    sql += " WHERE TABLE_SCHEMA = ?";
    PreparedStatement pstmt = null;
    PrintWriter out = response.getWriter();
    out.println("<html><head><title>Tables</title></head><body>");
    out.println("<table>");
    out.println("<tr><td><b>Name</b></td><td><b>Type</b></td></tr>");
    try {
      //Get a connection from the Datasource, i.e from the connection pool.
      con = dataSource.getConnection();
      //Prepare the statement for execution.
      pstmt = con.prepareStatement(sql);
      pstmt.setString(1, con.getMetaData().getUserName().toUpperCase());
      rs = pstmt.executeQuery();
      //Loop over the entire result set.
      while(rs.next()){
       out.println("<tr>");
       out.println("<td>" + rs.getString("TABLE_NAME") + "</td>");
       out.println("<td>" + rs.getString("TABLE_TYPE") + "</td>");
       out.println("</tr>");
      }
    }
    catch(SQLException sqle){
      throw new ServletException("Error getting connection: " + 
                                  sqle.getMessage());
    }
    finally{
      /**
       * To close the result set, statements and connection in a finally clause
       * is prefered. This way resources are always closed, even if an exception 
       * is thrown earlier.
       * To close result set, statements and connections in separate try clauses
       * garanetes that we at least tries to close everything. Even if, for 
       * example, the rs.close() fails, con.close() will execute.
       */
      try{
        //Close the result set
        rs.close();
      }
      catch(Exception e){
        //Do nothing
      }
      try{
        //Close the statement
        pstmt.close();
      }
      catch(Exception e){
        //Do nothing
      }
      try{
        //Close the conection. The connection isn't really closed, it's only 
        //given back to the connection pool and marked as available.
        con.close();
      }
      catch(Exception e){
        //Do Nothing
      }
    }
    out.println("</table>");
    out.println("</body></html>");

  }
  /**Clean up resources*/
  public void destroy() {
  }
}
Links

Here are some links you might find useful:

Benefits

Since Tomcat is the reference implementation of JSP and Servlets the code you write for it should work in any other JSP and Servlet container. This way you are not depending on a specific application server provider.
Last updated: 2002-08-27

 

Powered by Mimer SQL

Powered by Mimer SQL