Question

In this chapter, we will show how we can put together Derby with Apache Tomcat web container.

Apache Tomcat is a web container for the Java programming language. It is used to serve JSP pages and servlets. Apache Tomcat can be easily put together to work with Derby. Derby has a derby.war file in its lib subdirectory. This web archive is simply used to control the Derby database. Both Tomcat and Derby are projects of the Apache Software foundation.

$ pwd
/home/janbodnar/bin/tomcat
$ ls lib/derby*
lib/derbyclient.jar  lib/derby.jar  lib/derbynet.jar

First we have to copy the derbyclient.jar , derby.jar and derbynet.jar files into the lib subdirectory of the Tomcat installation directory.

$ ls webapps 
derby.war  docs  examples  host-manager  manager  ROOT

Then we have to copy the derby.war file into the webapps subdirectory file of the Tomcat installation directory. When the Tomcat starts, the file is unpacked and deployed.

$ export JAVA_OPTS=-Dderby.system.home=/home/janbodnar/programming/derby/dbs

When we start Derby via Tomcat, the DERBY_OPTS variable is not taken into account. The derby.system.home must be set prior to starting the Tomcat and Derby servers. We can set the Derby system directory in the JAVA_OPTS variable.

$ bin/startup.sh 
Using CATALINA_BASE:   /home/janbodnar/bin/tomcat
Using CATALINA_HOME:   /home/janbodnar/bin/tomcat
Using CATALINA_TMPDIR: /home/janbodnar/bin/tomcat/temp
Using JRE_HOME:    /home/janbodnar/bin/jdk1.6.0_30
Using CLASSPATH:     /home/janbodnar/bin/tomcat/bin/bootstrap.jar:
/home/janbodnar/bin/tomcat/bin/tomcat-juli.jar

With the startup.sh script we start the Tomcat server.

Figure: Tomcat startup page

We see the Tomcat welcome page when we navigate to localhost:8080 which is the default URL on which Tomcat listens.

Figure: Derby start

To start the Derby database, we navigate to localhost:8080/derby/derbynet . This will start the Derby. We have several buttons which can be used to start/stop the server, enable/disable logging or tracing.

<load-on-startup>0</load-on-startup>

We have to navigate to the above mentioned URL each time, we start the Tomcat server. To automatically start Derby, we can add the above line inside the the <servlet> tag of the web.xml file. The file is located in webapps/derby/WEB-INF directory.

Creating the testdb database

For those, who did not follow the tutorial from the beginning, we will create the testdb database again. We will add one table into the database. You can skip the creation of the database and the table if it already exists.

$ cat cars.sql 
CREATE SCHEMA USER12;
CREATE TABLE CARS(ID INT PRIMARY KEY, NAME VARCHAR(30), PRICE INT);
INSERT INTO CARS VALUES(1, 'Audi', 52642);
INSERT INTO CARS VALUES(2, 'Mercedes', 57127);
INSERT INTO CARS VALUES(3, 'Skoda', 9000);
INSERT INTO CARS VALUES(4, 'Volvo', 29000);
INSERT INTO CARS VALUES(5, 'Bentley', 350000);
INSERT INTO CARS VALUES(6, 'Citroen', 21000);
INSERT INTO CARS VALUES(7, 'Hummer', 41400);
INSERT INTO CARS VALUES(8, 'Volkswagen', 21600);

We will need this SQL file.

$ cat dbs/derby.properties 
derby.stream.error.logSeverityLevel=0
derby.database.fullAccessUsers=user12
derby.database.defaultConnectionMode=readOnlyAccess
derby.connection.requireAuthentication=true
derby.user.user12=34klq*
derby.user.user13=33kl33
derby.user.user14=14kl14
derby.user.user15=35rr++
derby.authentication.provider=builtin

Inside the Derby system directory, we have the derby.properties file. In this file, we configure some options. We set the log severity level to 0 to report all possible problems. This is done in test environments. We enable authentication. We create four users with corresponding passwords. Only one of them, user12, has full access priviliges. The others have only readOnlyAccess.

$ java  -Dderby.system.home=/home/janbodnar/programming/derby/dbs \
-Dij.protocol=jdbc:derby: -jar $DERBY_HOME/lib/derbyrun.jar ij
ij version 10.8
ij> 

We start the ij command line tool. We will use it to create a database and a table. The Derby system directory is located at /home/janbodnar/programming/derby/dbs .

ij> CONNECT 'jdbc:derby://localhost:1527/testdb;create=true;
user=user12;password=34klq*';

We create the testdb database and connect to it. We provide user credentials.

ij> run 'cars.sql';

We execute the cars.sql script, which creates a CARS table and fills it with data.

ij> SELECT * FROM CARS;
ID     |NAME              |PRICE    
------------------------------------------------------
1      |Audi              |52642    
2      |Mercedes            |57127    
3      |Skoda             |9000     
4      |Volvo             |29000    
5      |Bentley             |350000   
6      |Citroen             |21000    
7      |Hummer            |41400    
8      |Volkswagen          |21600    

8 rows selected

This is our CARS table. Next we will create a Java servlet, which will display these values in a web browser.

The project

We will create a simple web application that will connect to the Derby database. One Java servlet will connect to the Derby and retrieve all data from the CARS table.

$ tree
.
├── build.xml
├── context.xml
├── lib
│   └── servlet-api.jar
├── src
│   └── zetcode
│     └── SelectAllCars.java
└── web.xml

3 directories, 5 files

In our current working directory, we have an Ant build.xml file, the context.xml configuration file, the web.xml deployment descriptor file, and the src and lib subdirectories. The build.xml file is the Ant build file, which describes the tasks to build, deploy or clean the project. The web.xml defines the structure of the web application. In the lib directory we have the servlet-api.jar file, which is needed to compile the source file. (It can be found in the lib subdirectory of the Tomcat installation directory.) In the src directory, we have the Java source file.

The web.xml file defines the structure of the web application.

<?xml version="1.0" encoding="UTF8"?>

<web-app xmlns="http://java.sun.com/xml/ns/javaee"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://java.sun.com/xml/ns/javaee
            http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd"
  version="3.0"
  metadata-complete="true">

  <description>
    Servlet which connects to Derby
  </description>
  <display-name>Derby, Tomcat</display-name>

  <servlet>
    <servlet-name>SelectAllCars</servlet-name>
    <servlet-class>zetcode.SelectAllCars</servlet-class>
  </servlet>

  <servlet-mapping>
    <servlet-name>SelectAllCars</servlet-name>
    <url-pattern>/SelectAllCars</url-pattern>
  </servlet-mapping>

</web-app>

These are the contents of the the web.xml file. In this file, we register the SelectAllCars servlet.

<?xml version="1.0" encoding="UTF-8"?>
<Context>
  <Resource name="jdbc/testdb" 
        auth="Container"
        type="javax.sql.DataSource" 
        username="user12" 
        password="34klq*"        
        driverClassName="org.apache.derby.jdbc.ClientDriver"
        url="jdbc:derby://localhost:1527/testdb"
        maxActive="10" 
        maxIdle="4"/>

</Context>

In the context.xml file, we define the JDBC datasource. The context.xml file can be defined for all web applications or for a single application. The latter is our case.

We will show the Ant build file which will be used to build and deploy our tiny application.

<?xml version="1.0" ?>

<project name="allcars" default="deploy">
  
  <property name="src.dir" value="src"/>
  <property name="build.dir" value="build"/>
  <property name="dist.dir" value="dist"/>
  <property name="deploy.dir" value="/home/janbodnar/bin/tomcat/webapps"/>
  
  <echo>${ant.project.name}</echo>
  
  <target name="init">
    <mkdir dir="${build.dir}/classes" />
    <mkdir dir="${dist.dir}"/>
    <echo>Directories created.</echo>
  </target>
  
  <target name="compile" depends="init">
    <javac srcdir="${src.dir}" destdir="${build.dir}/classes" 
         includeantruntime="false">
      <classpath path="lib/servlet-api.jar"/>  
    </javac>
    <echo>Source files compiled.</echo>
  </target>  
  
  <target name="archive" depends="compile">
    <war destfile="${dist.dir}/${ant.project.name}.war" webxml="web.xml">
      <classes dir="${build.dir}/classes"/>
      <metainf file="context.xml"/>
    </war>
    <echo>Archive created.</echo>
  </target>  
  
  <target name="deploy" depends="archive">
    <copy file="${dist.dir}/${ant.project.name}.war" todir="${deploy.dir}"/>
    <echo>Project deployed.</echo>
  </target>  
  
  <target name="clean">
    <delete dir="${dist.dir}"/>
    <delete dir="${build.dir}"/>
    <echo>Project cleaned.</echo>
  </target>
  
</project>

The build file comprises five tasks. The initialization task will create the necessary directories. The compile task will compile the source code. The archive task will create a web archive. The deploy task will deploy the archive to the Tomcat server. Finally, the clean task will do the cleaning.

The following is the SelectAllCars servlet.

package zetcode;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;


public class SelectAllCars extends HttpServlet {

  protected void processRequest(HttpServletRequest request,
      HttpServletResponse response)
      throws ServletException, IOException {
    
    response.setContentType("text/html;charset=UTF-8");

    PrintWriter out = null;
    Connection con = null;
    Statement st = null;
    ResultSet rs = null;

    try {

      out = response.getWriter();

      Context ctx = new InitialContext();
      DataSource ds = (DataSource) ctx.lookup("java:comp/env/jdbc/testdb");

      con = ds.getConnection();
      st = con.createStatement();

      out.println("<html>");
      out.println("<head>");
      out.println("<title>SimpleServlet</title>");
      out.println("</head>");
      out.println("<body>");      

      rs = st.executeQuery("SELECT * FROM CARS");

      while (rs.next()) {
        out.print(rs.getInt(1));
        out.print(" ");
        out.print(rs.getString(2));
        out.print(" ");
        out.print(rs.getString(3));
        out.print("<br>");
      }

      out.println("</body>");
      out.println("</html>");

    } catch (NamingException | SQLException ex) {

      Logger lgr = Logger.getLogger(SelectAllCars.class.getName());
      lgr.log(Level.SEVERE, ex.getMessage(), ex);

    } finally {

      try {
        if (rs != null) {
          rs.close();
        }

        if (con != null) {
          con.close();
        }

        if (out != null) {
          out.close();
        }
        
      } catch (SQLException ex) {
        Logger lgr = Logger.getLogger(SelectAllCars.class.getName());
        lgr.log(Level.WARNING, ex.getMessage(), ex);
      }
    }
  }

  @Override
  protected void doGet(HttpServletRequest request,
      HttpServletResponse response)
      throws ServletException, IOException {
    processRequest(request, response);
  }

  @Override
  protected void doPost(HttpServletRequest request,
      HttpServletResponse response)
      throws ServletException, IOException {
    processRequest(request, response);
  }
}

In the above servlet, we connect to the Derby testdb database and fetch all rows from the CARS table.

Context ctx = new InitialContext();
DataSource ds = (DataSource) ctx.lookup("java:comp/env/jdbc/testdb");

We use the JNDI naming lookup to obtain the datasource. From the datasource, we create the connection object.

rs = st.executeQuery("SELECT * FROM CARS");

while (rs.next()) {
  out.print(rs.getInt(1));
  out.print(" ");
  out.print(rs.getString(2));
  out.print(" ");
  out.print(rs.getString(3));
  out.print("<br>");
}

We use the SQL statement to retrieve all data from the CARS table. We print the data from the result set object.

$ ant
Buildfile: /home/janbodnar/programming/derby/servlet/build.xml
   [echo] allcars

init:
  [mkdir] Created dir: /home/janbodnar/programming/derby/servlet/build/classes
  [mkdir] Created dir: /home/janbodnar/programming/derby/servlet/dist
   [echo] Directories created.

compile:
  [javac] Compiling 1 source file to /home/janbodnar/programming/derby/
servlet/build/classes
   [echo] Source files compiled.

archive:
    [war] Building war: /home/janbodnar/programming/derby/servlet/dist/allcars.war
   [echo] Archive created.

deploy:
   [copy] Copying 1 file to /home/janbodnar/bin/tomcat/webapps
   [echo] Project deployed.

BUILD SUCCESSFUL
Total time: 1 second

We launch the ant to build and deploy the project.

Figure: Output of the SelectAllCars servlet

We navigate to the localhost:8080/allcars/SelectAllCars URL and receive the output.

In the chapter, we have worked with Derby and Apache Tomcat.