Tomcat Derby tutorial

In this tutorial, we work with Tomcat and Derby. We use the three-layer, controller, and DAO software patterns in our application. For the project creation, we use NetBeans IDE. The source code is available at author's Github repository.

Apache Derby is an open source relational database implemented entirely in Java. It has small footprint and is easy to deploy and install. It supports both embedded and client/server modes.

Apache Tomcat is an open source implementation of the Java Servlet, JavaServer Pages, Java Expression Language and Java WebSocket technologies.

Data Access Object (DAO) is an object that provides an abstract interface to a database or other persistence mechanism. The DAO completely hides the data source implementation details from its clients. It acts as an adapter between the component and the data source.

Application

We create a new web application in NetBeans. The application manages a simple Cars table. It will create new cars, retrieve one car and all cars.

The application is divided in three layers: presentation layer, model layer, and persistence layer. This provides modularity for the application, which is one of the most important software development patterns.

<dependency>
    <groupId>javax</groupId>
    <artifactId>javaee-web-api</artifactId>
    <version>7.0</version>
    <scope>provided</scope>
</dependency>

<dependency>
    <groupId>org.apache.derby</groupId>
    <artifactId>derbyclient</artifactId>
    <version>10.13.1.1</version>
</dependency>

<dependency>
    <groupId>javax.servlet</groupId>
    <artifactId>jstl</artifactId>
    <version>1.2</version>
</dependency>

<dependency>
    <groupId>org.apache.commons</groupId>
    <artifactId>commons-lang3</artifactId>
    <version>3.5</version>
</dependency>

We need these four dependencies. The Java web JARs for servlets and JSP pages, Derby JARs for the Derby database, JSTL JARs for additional helpful tags in the presentation layer, and the Apache Commons Lang JARs for some helper classes.

Database creation
Figure: Database creation

In the Services tab, we right-click on the Java DB node and select the Create Database option. We give it a testdb name. Note that the database is located in the .netbeans_derby directory of the user's home directory.

cars.sql
CREATE TABLE CARS(ID BIGINT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY 
    (START WITH 1, INCREMENT BY 1), NAME VARCHAR(30), PRICE INT);

INSERT INTO CARS(Name, Price) VALUES('Audi', 52642);
INSERT INTO CARS(Name, Price) VALUES('Mercedes', 57127);
INSERT INTO CARS(Name, Price) VALUES('Skoda', 9000);
INSERT INTO CARS(Name, Price) VALUES('Volvo', 29000);
INSERT INTO CARS(Name, Price) VALUES('Bentley', 350000);
INSERT INTO CARS(Name, Price) VALUES('Citroen', 21000);
INSERT INTO CARS(Name, Price) VALUES('Hummer', 41400);
INSERT INTO CARS(Name, Price) VALUES('Volkswagen', 21600);

This is the SQL to create the Cars table. Note that the ID of the car object is auto-incremented. We can use the NetBeans tools to create the Cars table. We right-click on the Databases node and select a New connection option.

Connection wizard
Figure: Connection wizard

We fill in the necessary details in the connection wizard. We use the Derby network driver, the Derby's port is 1527.

Connections
Figure: Connections

A new connection object is created; it is represented by an orange icon. Its context menu provides options to connect to the specified database and execute a command. The Execute command option shows a tool to execute SQL commands. In this window, we can use the above SQL to create the Cars table.

NetBeans Derby tool
Figure: NetBeans Derby tool

NetBeans has a useful Derby tool, which can be used to manage Derby databases.

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

In the context.xml file, which is located in the META-INF directory, we provide the data source. The resource is looked up with JNDI API.

Model layer

The model layer has the Car class.

Car.java
package com.zetcode.bean;

public class Car {

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getPrice() {
        return price;
    }

    public void setPrice(int price) {
        this.price = price;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    private Long id;
    private String name;
    private int price;
}

The Car class has three attributes and corresponding getter and setter methods.

Presentation layer

The presentation layer of the application contains the JSP pages, which show the results of our requests.

index.jsp
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
    <head>
        <meta charset="UTF-8">
        <title>Home page</title>
    </head>
    <body>
        <p>Available actions:</p>
        <ul>
            <li><a href="controller?action=listcars">Show all</a></li>
            <li><a href="controller?action=readbyid">Show car by ID</a></li>
            <li><a href="controller?action=readcar">Save a new car</a></li>
        </ul>
    </body>
</html>

The index.jsp page contains links for three available actions: showing all cars, displaying a car found by its ID, and saving a new car.

allCars.jsp
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html>
<html>
    <head>
        <meta charset="UTF-8">
        <title>Cars</title>
    </head>
    <body>
        
        <h3>All cars</h3>
        
        <table>
            <thead>
                <tr>
                    <th>Id</th>
                    <th>Name</th>
                    <th>Price</th>
                </tr>
            </thead>

            <c:forEach items="${carList}" var='car'>
                <tr>
                    <td>
                        <c:out value="${car.id}"/>
                    </td>
                    <td>
                        <c:out value="${car.name}"/>
                    </td>
                    <td>
                        <c:out value="${car.price}"/>
                    </td>                    
                </tr>
            </c:forEach>          

        </table>

    </body>
</html>

In the allCars.jsp page, the JSTL's <c:forEach> and <c:out> tags are used to print the attributes of each of the returned car objects.

readCar.jsp
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
    <head>
        <meta charset="UTF-8">
        <title>Enter car details</title>
    </head>
    <body>
        <p>Enter car details:</p>
        
        <form action="controller?action=savecar" method="post">
            
            <label for="carName">Name:</label>
            <input id="carName" type="text" name="carName">
            <br><br>
            
            <label for="carPrice">Price</label>
            <input id ="carPrice" type="text" name="carPrice">
            <br><br>
            
            <button type="submit">Submit</button>
            
        </form>
        
    </body>
</html>

In the readCar.jsp page, we have a form to enter details of a new car.

readCarId.jsp
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
    <head>
        <meta charset="UTF-8">
        <title>Enter car ID</title>
    </head>
    <body>
        <p>Enter car ID:</p>
        
        <form action="controller">
            
            <input type="hidden" name="action" value="viewcar">
            <label for="carId">Id:</label>
            <input id="carId" type="text" name="carId">
            <br>
            <br>
            
            <button type="submit">Submit</button>
            
        </form>
    </body>
</html>

In the readCarId.jsp file, we have a form to enter the car's ID we want to retrieve.

carSaved.jsp
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html>
<html>
    <head>
        <meta charset="UTF-8">
        <title>Car saved</title>
    </head>
    <body>
        <p>Successfully saved  <c:out value="${sessionScope.carName}"/>
        car priced <c:out value="${sessionScope.carPrice}"/></p>
    </body>
</html>

In the carSaved.jsp page, we simply inform that the car with the given name and price was saved. We use the <c:out> tag from the JSTL library.

showCar.jsp
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html>
<html>
    <head>
        <meta charset="UTF-8">
        <title>Returned car</title>
    </head>
    <body>
        
        <h3>Car details</h3>
        
        <p>
            ID: <c:out value="${returnedCar.id}"/> <br>
            Name: <c:out value="${returnedCar.name}"/> <br>
            Price: <c:out value="${returnedCar.price}"/> <br>
        </p>
        
    </body>
</html>

In the showCar.jsp page, we display the attributes of the retrieved car.

unknown.jsp
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
    <head>
        <meta charset="UTF-8">
        <title>Unknown action</title>
    </head>
    <body>
        <h3>Unknown action</h3>
    </body>
</html>

The unknown.jsp page is displayed when the controller receives an action that is not defined.

wrongParams.jsp
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
    <head>
        <meta charset="UTF-8">
        <title>Error</title>
    </head>
    <body>
        <h3>Wrong parameters specified</h3>
    </body>
</html>

The wrongParams.jsp page is displayed when the request parameters are not valid. A utility class named ValidateParameter is created to ensure the validity of request parameters.

Persistence layer

In the persistence layer, we apply the DAO pattern.

CarDAO.java
package com.zetcode.persistence;

import com.zetcode.bean.Car;
import java.util.List;

public interface CarDAO {

  public void saveCar(Car car);
  public Car findCar(Long id);
  public List<Car> findAll();
}

This is the CarDAO interface presents method signatures for accessing our database.

Executable.java
package com.zetcode.persistence;

import java.sql.SQLException;
import javax.naming.NamingException;


public interface Executable {
    
    void exec() throws SQLException, NamingException;
}

The Executable interface is a contract to put the try/catch/finally boilerplate into the exec() method.

JdbcDAO.java
package com.zetcode.persistence;

import com.zetcode.bean.Car;
import com.zetcode.util.ServiceLocator;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.naming.NamingException;
import java.sql.SQLException;
import javax.sql.DataSource;

public class JdbcDAO implements CarDAO {

    private static final String DATA_SOURCE = "java:comp/env/jdbc/testdb";
    private Connection con;
    private ResultSet rs;
    private PreparedStatement pst;

    @Override
    public void saveCar(Car car) {

        execute(() -> {
            DataSource ds = ServiceLocator.getDataSource(DATA_SOURCE);
            
            con = ds.getConnection();
            
            pst = con.prepareStatement("INSERT INTO CARS(Name, Price) VALUES(?, ?)");
            pst.setString(1, car.getName());
            pst.setInt(2, car.getPrice());
            pst.executeUpdate();
        });
    }

    @Override
    public Car findCar(Long id) {

        Car car = new Car();

        execute(() -> {
            DataSource ds = ServiceLocator.getDataSource(DATA_SOURCE);
            con = ds.getConnection();
            
            pst = con.prepareStatement("SELECT * FROM CARS WHERE Id = (?)");
            pst.setLong(1, id);
            rs = pst.executeQuery();
            
            if (rs.next()) {
                
                car.setId(rs.getLong(1));
                car.setName(rs.getString(2));
                car.setPrice(rs.getInt(3));
            }
        });

        return car;
    }

    @Override
    public List<Car> findAll() {

        List carList = new ArrayList();

        execute(() -> {
            DataSource ds = ServiceLocator.getDataSource(DATA_SOURCE);
            con = ds.getConnection();
            pst = con.prepareStatement("SELECT * FROM CARS");
            
            rs = pst.executeQuery();
            
            while (rs.next()) {
                Car car = new Car();
                car.setId(rs.getLong(1));
                car.setName(rs.getString(2));
                car.setPrice(rs.getInt(3));
                carList.add(car);
            }
        });

        return carList;
    }

    private void execute(Executable executable) {

        try {
            executable.exec();
        } catch (NamingException | SQLException e) {

            Logger lgr = Logger.getLogger(JdbcDAO.class.getName());
            lgr.log(Level.SEVERE, e.getMessage(), e);

        } finally {

            try {

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

                if (pst != null) {

                    pst.close();
                }

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

            } catch (SQLException e) {
                Logger lgr = Logger.getLogger(JdbcDAO.class.getName());
                lgr.log(Level.WARNING, e.getMessage(), e);
            }
        }
    }
}

JdbcDAO is a concrete implementation of the CarDAO interface. It uses JDBC to insert and retrieve data from the Cars table.

private static final String DATA_SOURCE = "java:comp/env/jdbc/testdb";

This is the JNDI resource name to locate the testdb database.

@Override
public void saveCar(Car car) {

    execute(() -> {
        DataSource ds = ServiceLocator.getDataSource(DATA_SOURCE);
        
        con = ds.getConnection();
        
        pst = con.prepareStatement("INSERT INTO CARS(Name, Price) VALUES(?, ?)");
        pst.setString(1, car.getName());
        pst.setInt(2, car.getPrice());
        pst.executeUpdate();
    });
}

The saveCar() method saves a new car object. The ServiceLocator.getDataSource() method looks up and returns the data source. The code is inserted into the execute() method, which will take care of the try/catch/finally boilerplate.

@Override
public Car findCar(Long id) {

    Car car = new Car();

    execute(() -> {
        DataSource ds = ServiceLocator.getDataSource(DATA_SOURCE);
        con = ds.getConnection();
        
        pst = con.prepareStatement("SELECT * FROM CARS WHERE Id = (?)");
        pst.setLong(1, id);
        rs = pst.executeQuery();
        
        if (rs.next()) {
            
            car.setId(rs.getLong(1));
            car.setName(rs.getString(2));
            car.setPrice(rs.getInt(3));
        }
    });

    return car;
}

The findCar() method retrieves a new car from the Cars table. It executes a prepared statement, which receives a car's ID. A new car bean is filled with the returned data.

private void execute(Executable executable) {

    try {
        executable.exec();
    } catch (NamingException | SQLException e) {

        Logger lgr = Logger.getLogger(JdbcDAO.class.getName());
        lgr.log(Level.SEVERE, e.getMessage(), e);

    } finally {

        try {

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

            if (pst != null) {

                pst.close();
            }

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

        } catch (SQLException e) {
            Logger lgr = Logger.getLogger(JdbcDAO.class.getName());
            lgr.log(Level.WARNING, e.getMessage(), e);
        }
    }
}

The repeating code that handles exceptions is placed in the execute() method.

Utility classes

We have created two utility classes: ServiceLocator and ValidateParameter. The classes are located in the com.zetcode.util package.

ServiceLocator.java
package com.zetcode.util;

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

public class ServiceLocator {

    public static DataSource getDataSource(String jndiName) throws NamingException {

        Context ctx = new InitialContext();
        DataSource ds = (DataSource) ctx.lookup(jndiName);

        return ds;
    }
}

The ServiceLocator looks up and returns the data source. It is called from the JdbcDAO class. The details of the data source are specified in the context.xml file.

ValidateParameter.java
package com.zetcode.util;

import org.apache.commons.lang3.math.NumberUtils;

public class ValidateParameter {
    
    private static final int MAX_PRICE_CAR = 10_000_000;

    public static boolean validateName(String param) {

        return !(null == param || "".equals(param));
    }
    
    public static boolean validateId(String param) {

        return !(null == param || "".equals(param) || 
                !NumberUtils.isCreatable(param));
    }   
    
    public static boolean validatePrice(String param) {

       if (null == param || "".equals(param) || !NumberUtils.isCreatable(param)) {
           return false;
       }
       
       int price = Integer.valueOf(param);
       
       return !(price < 0 || price > MAX_PRICE_CAR);
       
    } 
}

The ValidateParameter has static methods for validating request parameters. For instance, the ID must be a number and the price must not be negative. We use the NumberUtils.isCreatable() method from the Apache Commons Lang library to ensure that the parameter is a number.

Controller

The Controller is a servlet that receives the incoming requests, calls the DAO methods, and directs to the responses.

Controller.java
package com.zetcode.web;

import com.zetcode.bean.Car;
import com.zetcode.persistence.CarDAO;
import com.zetcode.persistence.JdbcDAO;
import com.zetcode.util.ValidateParameter;
import java.io.IOException;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

@WebServlet(name = "Controller", urlPatterns = {"/controller"})
public class Controller extends HttpServlet {

    private static final String ACTION_KEY = "action";

    private static final String READ_CAR_BY_ID_VIEW = "readCarId.jsp";
    private static final String SHOW_CAR_VIEW = "showCar.jsp";
    private static final String READ_CAR_VIEW = "readCar.jsp";
    private static final String CAR_SAVED_VIEW = "carSaved.jsp";
    private static final String ALL_CARS_VIEW = "allCars.jsp";
    
    private static final String UNKNOWN_VIEW = "unknown.jsp";
    private static final String WRONG_PARAMS_VIEW = "wrongParams.jsp";

    private static final String LIST_CARS_ACTION = "listcars";
    private static final String READ_CAR_BY_ID_ACTION = "readbyid";
    private static final String READ_CAR_ACTION = "readcar";
    private static final String VIEW_CAR_ACTION = "viewcar";
    private static final String SAVE_CAR_ACTION = "savecar";

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

        String actionName = request.getParameter(ACTION_KEY);
        String page = UNKNOWN_VIEW;

        if (LIST_CARS_ACTION.equals(actionName)) {
            CarDAO carDAO = new JdbcDAO();
            request.setAttribute("carList", carDAO.findAll());
            page = ALL_CARS_VIEW;
        }
        
        if (READ_CAR_BY_ID_ACTION.equals(actionName)) {
            page = READ_CAR_BY_ID_VIEW;
        }     
        
        if (READ_CAR_ACTION.equals(actionName)) {
            page = READ_CAR_VIEW;
        }             

        if (VIEW_CAR_ACTION.equals(actionName)) {

            String sid = request.getParameter("carId");

            if (ValidateParameter.validateId(sid)) {

                Long carId = Long.valueOf(sid);
                CarDAO carDAO = new JdbcDAO();

                request.setAttribute("returnedCar", carDAO.findCar(carId));

                page = SHOW_CAR_VIEW;
            } else {

                page = WRONG_PARAMS_VIEW;
            }
        }

        RequestDispatcher disp = getServletContext().getRequestDispatcher("/" + page);
        disp.forward(request, response);
    }

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

        String actionName = request.getParameter(ACTION_KEY);
        String page = UNKNOWN_VIEW;        
        
        if (SAVE_CAR_ACTION.equals(actionName)) {

            String sname = request.getParameter("carName");
            String sprice = request.getParameter("carPrice");

            if (ValidateParameter.validateName(sname)
                    && ValidateParameter.validatePrice(sprice)) {

                Car car = new Car();
                car.setName(sname);
                car.setPrice(Integer.valueOf(sprice));

                CarDAO carDAO = new JdbcDAO();
                carDAO.saveCar(car);

                request.getSession().setAttribute("carName", sname);
                request.getSession().setAttribute("carPrice", sprice);
                page = CAR_SAVED_VIEW;
            } else {

                page = WRONG_PARAMS_VIEW;
            }
        }    
        
        response.sendRedirect(page);
    }
}

The servlet is located in the com.zetcode.web package.

private static final String READ_CAR_BY_ID_VIEW = "readCarId.jsp";
private static final String SHOW_CAR_VIEW = "showCar.jsp";
private static final String READ_CAR_VIEW = "readCar.jsp";
...

These are various views used in our application.

private static final String LIST_CARS_ACTION = "listcars";
private static final String READ_CAR_BY_ID_ACTION = "readbyid";
private static final String READ_CAR_ACTION = "readcar";
...

These are various actions of the application. For instance, the READ_CAR_ACTION shows a view that contains a form where the user enter details of a new car.

if (LIST_CARS_ACTION.equals(actionName)) {
    CarDAO carDAO = new JdbcDAO();
    request.setAttribute("carList", carDAO.findAll());
    page = ALL_CARS_VIEW;
}

For the LIST_CARS_ACTION, we create the JdbcDAO object. We call its findAll() method and set the result to the carList attribute. The controller servlet then points to the ALL_CARS_VIEW.

if (VIEW_CAR_ACTION.equals(actionName)) {

    String sid = request.getParameter("carId");

    if (ValidateParameter.validateId(sid)) {

        Long carId = Long.valueOf(sid);
        CarDAO carDAO = new JdbcDAO();

        request.setAttribute("returnedCar", carDAO.findCar(carId));

        page = SHOW_CAR_VIEW;
    } else {

        page = WRONG_PARAMS_VIEW;
    }
}

For viewing one car, we get the ID of the car from the request parameter. The value is validated with the ValidateParameter.validateId() utility method. (The value cannot be null, empty, and it has to be a number.) If the parameter is not valid, the controller navigates to the WRONG_PARAMS_VIEW.

The DAO's findCar() tries to retrieve the car from the database. The returned car is inserted into the returnedCar attribute, which is later fetched in the showCar.jsp page.

if (ValidateParameter.validateName(sname)
        && ValidateParameter.validatePrice(sprice)) {

    Car car = new Car();
    car.setName(sname);
    car.setPrice(Integer.valueOf(sprice));

    CarDAO carDAO = new JdbcDAO();
    carDAO.saveCar(car);

    request.getSession().setAttribute("carName", sname);
    request.getSession().setAttribute("carPrice", sprice);
    page = CAR_SAVED_VIEW;
} else {

    page = WRONG_PARAMS_VIEW;
}

This code is place in the doPost() method. When we save a new car, we have two parameters: the name and price of the car. The ID is automatically created by Derby. The parameters are validated and a new Car object is created and filled with the parameters. The DAO's saveCar() saves the car object into the database. The name of the car is passed to the CAR_SAVED_VIEW for the creation of the message to the user. Since we use a redirect in the doPost() method, we place the name of the car and its price into the session object; doing a redirect operation we loose data from the original request.

response.sendRedirect(page);

Adhering to the Post/Redirect/Get pattern, we redirect to the view in the doPost() method. This prevents multiple form submissions. (For instance, we could accidentally add a car multiple times).

Displaying all cars
Figure: Displaying all cars

In this tutorial, we have created a web application, which manages car objects. The data was saved in the Derby database.

The application was divided into three layers. We have used the DAO pattern for data access. The application was created with NetBeans IDE. You can find more about Derby in ZetCode's Derby tutorial. In a Displaying database data in datagrid tutorial we show how to display data from a Derby database in a EasyUI datagrid control.