Question

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 project will be packed into the dertom.war file. 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.

Figure: Libraries

We need additional libraries for our project. The Derby JARs for the Derby database, JSTL JARs for the presentation layer and Apache Commons Lang JARs for some helper classes.

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 INTEGER 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.

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.

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.

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="/dertom" >

  <Resource name="jdbc/testdb" 
        auth="Container"
        type="javax.sql.DataSource" 
        username="app" 
        password="app"        
        driverClassName="org.apache.derby.jdbc.ClientDriver"
        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 datasource. The database name is called testdb .

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.

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 http-equiv="Content-Type" content="text/html; 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 forEach and out tags are used to print the attributes of each of the returned car objects.

saveCar.jsp

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html>
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
    <title>Car saved</title>
  </head>
  <body>
    <h3>The <c:out value="${carName}"/> was saved</h3>
  </body>
</html>

In the saveCar.jsp page, we simply inform that the car with the given name was saved.

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 http-equiv="Content-Type" content="text/html; charset=UTF-8">
    <title>Returned car</title>
  </head>
  <body>
    
    <h3>Returned car</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 http-equiv="Content-Type" content="text/html; 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 http-equiv="Content-Type" content="text/html; 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 whose methods we call to get access to 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 datasource. 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 datasource. It is called from the JdbcDAO class. The details of the datasource 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) || 
        NumberUtils.isNumber(param));
  }
  
  public static boolean validateId(String param) {

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

     if (null == param || "".equals(param) || !NumberUtils.isNumber(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.isNumber() 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 VIEW_ALL_CARS = "readall";
  private static final String VIEW_ONE_CAR = "read";
  private static final String VIEW_SAVE_CAR = "save";

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

    String actionName = request.getParameter(ACTION_KEY);
    String page = "/unknown.jsp";

    if (VIEW_ALL_CARS.equals(actionName)) {
    
      CarDAO carDAO = new JdbcDAO();
      request.setAttribute("carList", carDAO.findAll());
      page = "/allCars.jsp";
    }

    if (VIEW_ONE_CAR.equals(actionName)) {

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

      if (ValidateParameter.validateId(sid)) {

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

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

        page = "/showCar.jsp";
        
      } else {

        page = "/wrongParams.jsp";
      }
    }

    if (VIEW_SAVE_CAR.equals(actionName)) {

      String sname = request.getParameter("name");
      String sprice = request.getParameter("price");

      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.setAttribute("carName", sname);
        page = "/carSaved.jsp";
        
      } else {

        page = "/wrongParams.jsp";
      }
    }

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

  @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);
  }
}

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

private static final String ACTION_KEY = "action";
private static final String VIEW_ALL_CARS = "readall";
private static final String VIEW_ONE_CAR = "read";
private static final String VIEW_SAVE_CAR = "save";

There are three types of actions available: reading all cars, reading a single car, and saving a new car.

if (VIEW_ALL_CARS.equals(actionName)) {

  CarDAO carDAO = new JdbcDAO();
  request.setAttribute("carList", carDAO.findAll());
  page = "/allCars.jsp";
}

For the view all 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 allCars.jsp page.

if (VIEW_ONE_CAR.equals(actionName)) {

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

  if (ValidateParameter.validateId(sid)) {

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

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

    page = "/showCar.jsp";
    
  } else {

    page = "/wrongParams.jsp";
  }
}

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 wrongParams.jsp page.

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 (VIEW_SAVE_CAR.equals(actionName)) {

  String sname = request.getParameter("name");
  String sprice = request.getParameter("price");

  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.setAttribute("carName", sname);
    page = "/carSaved.jsp";
    
  } else {

    page = "/wrongParams.jsp";
  }
}

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 carSaved.jsp for the creation of the message to the user.

Testing the application

Now we navigate to the http://localhost:8084/dertom/controller?action=save&name=Renault&price=18000 URL to insert a new car.

Figure: Displaying all cars

To get all cars from the table, we navigate to the http://localhost:8084/dertom/controller?action=readall URL.

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 .