Servlet FreeMarker JdbcTemplate tutorial - CRUD operations

last modified July 13, 2020

In this tutorial, we create a simple Java web application with basic CRUD operations. We use FreeMarker template engine, Servlet technology, and JdbcTemplate library. MySQL is used to store the data. The application is finally deployed on Tomcat server.

The sources for this tutorial are available at author's Github repository.

CRUD (Create, Read, Update and Delete) are four basic functions of persistent storage. In case of relational databases, they are equivalent to INSERT, SELECT, UPDATE, and DELETE statements.

FreeMarker is a popular template engine for the Java programming language. Templates are written in the FreeMarker Template Language (FTL). A template engine combines static data with dynamic data to generate content. A template is an intermediate representation of the content; it specifies how the output will be produced.

JDBCTemplate is a Spring library for simplifying programming with JDBC. It takes care of tedious and error-prone low-level details such as handling transactions, cleaning up resources, and correctly handling exceptions. It is included in Spring's spring-jdbc module.

Managing users

Our application will manage users. It allows to add a new user, modify it, delete it, and list all available users.

mysql> CREATE TABLE Users(Id INTEGER PRIMARY KEY AUTO_INCREMENT, 
                          Firstname TEXT, Lastname TEXT, Email TEXT);

We create a Users table in a MySQL testdb database.

<dependencies>
    <dependency>
        <groupId>javax</groupId>
        <artifactId>javaee-web-api</artifactId>
        <version>7.0</version>
        <scope>provided</scope>
    </dependency>
    
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-jdbc</artifactId>
        <version>4.2.6.RELEASE</version>
    </dependency>

    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>6.0.2</version>
    </dependency>        
    
    <dependency>
        <groupId>org.freemarker</groupId>
        <artifactId>freemarker</artifactId>
        <version>2.3.25-incubating</version>
    </dependency>                        
    
</dependencies>

In the Maven pom.xml file, we provide four dependencies: javaee-web-api is a set of libraries for developing Java web applications, spring-jdbc module contains the JDBCTemplate library, mysql-connector-java is a MySQL Java driver, and freemarker is the FreeMarker library.

<?xml version="1.0" encoding="UTF-8"?>
<Context path="/ServletFreemarkerEx">

    <Resource name="jdbc/testdb" 
              auth="Container"
              type="javax.sql.DataSource" 
              username="user7" 
              password="s$cret"              
              driverClassName="com.mysql.jdbc.Driver"
              url="jdbc:mysql://localhost:3306/testdb"
              maxActive="10" 
              maxIdle="4"/>

</Context>

In the context.xml file, we define the application context path (its name) and a MySQL datasource.

<?xml version="1.0" encoding="UTF-8"?>

<web-app xmlns="http://xmlns.jcp.org/xml/ns/javaee"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd"
         version="3.1">
    
    <servlet>
        <servlet-name>freemarker</servlet-name>
        <servlet-class>freemarker.ext.servlet.FreemarkerServlet</servlet-class>

        <init-param>
            <param-name>TemplatePath</param-name>
            <param-value>/WEB-INF/template/</param-value>
        </init-param>
        <init-param>
            <param-name>NoCache</param-name>
            <param-value>true</param-value>
        </init-param>
        <init-param>
            <param-name>ResponseCharacterEncoding</param-name>
            <param-value>fromTemplate</param-value>
        </init-param>
        <init-param>
            <param-name>ExceptionOnMissingTemplate</param-name>
            <param-value>true</param-value>
        </init-param>
        <init-param>
            <param-name>incompatible_improvements</param-name>
            <param-value>2.3.25-incubating</param-value>
        </init-param>
        <init-param>
            <param-name>template_exception_handler</param-name>
            <param-value>html_debug</param-value>
        </init-param>
        <init-param>
            <param-name>template_update_delay</param-name>
            <param-value>0 s</param-value>
        </init-param>
        <init-param>
            <param-name>default_encoding</param-name>
            <param-value>UTF-8</param-value>
        </init-param>
        <init-param>
            <param-name>output_encoding</param-name>
            <param-value>UTF-8</param-value>
        </init-param>
        <init-param>
            <param-name>locale</param-name>
            <param-value>en_US</param-value>
        </init-param>
        <init-param>
            <param-name>number_format</param-name>
            <param-value>0.##########</param-value>
        </init-param>
        <load-on-startup>1</load-on-startup>
    </servlet>

    <servlet-mapping>
        <servlet-name>freemarker</servlet-name>
        <url-pattern>*.ftl</url-pattern>
    </servlet-mapping>
    
    <security-constraint>
        <web-resource-collection>
            <web-resource-name>FreeMarker MVC Views</web-resource-name>
            <url-pattern>*.ftl</url-pattern>
        </web-resource-collection>
        <auth-constraint>
        </auth-constraint>
    </security-constraint>    
    
    <session-config>
        <session-timeout>
            30
        </session-timeout>
    </session-config>
</web-app>

Inside the web.xml file we set up the FreemarkerServlet, which is used to process the FreeMarker .ftl files.

package com.zetcode.bean;

public class User {
    
    private Long id;
    private String firstName;
    private String lastName;
    private String email;

    public Long getId() {
        return id;
    }

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

    public String getFirstName() {
        return firstName;
    }

    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }
    
    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }    
}

We have a User bean that contains four attributes: id, firstName, lastName, and email.

package com.zetcode.util;

import java.util.logging.Level;
import java.util.logging.Logger;
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) {

        Context ctx = null;
        DataSource ds = null;
        
        try {
            ctx = new InitialContext();
            ds = (DataSource) ctx.lookup(jndiName);
        } catch (NamingException ex) {
            Logger.getLogger(ServiceLocator.class.getName()).log(
                Level.SEVERE, null, ex);
        }
        
        return ds;
    }
}

The ServiceLocator contains code to look up the data source. It uses the JNDI API to do the job.

package com.zetcode.service;

import com.zetcode.bean.User;
import com.zetcode.util.ServiceLocator;
import java.util.List;
import javax.sql.DataSource;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

public class DatabaseService {

    public static User getUserById(Long id) {

        String sql = "SELECT * FROM Users WHERE Id = ?";

        JdbcTemplate jtm = getJDBCTempate();
        User user = (User) jtm.queryForObject(sql, new Object[]{id},
                new BeanPropertyRowMapper(User.class));

        return user;
    }
    
    public static void addUser(User user) {
        
        
        String sql = "INSERT INTO Users(Firstname, Lastname, Email) VALUES(?, ?, ?)";
        
        JdbcTemplate jtm = getJDBCTempate();
        jtm.update(sql, new Object[] {user.getFirstName(), 
            user.getLastName(), user.getEmail()});
    }
    
    public static void deleteUser(Long id) {
        
        String sql = "DELETE From Users WHERE Id = ?";
        
        JdbcTemplate jtm = getJDBCTempate();
        jtm.update(sql, new Object[] {id});
    }
    
    public static void updateUser(User user) {
        
        String sql = "UPDATE Users SET Firstname=?, Lastname=?, Email=? WHERE Id=?";
        
        JdbcTemplate jtm = getJDBCTempate();
        jtm.update(sql, new Object[] {user.getFirstName(), 
            user.getLastName(), user.getEmail(), user.getId()});
        
    }

    public static List<User> getAllUsers() {

        String sql = "SELECT * FROM Users";

        JdbcTemplate jtm = getJDBCTempate();
        
        List<User> users = (List<User>) jtm.query(sql,
                new BeanPropertyRowMapper(User.class));
        
        return users;
    }
    
    private static JdbcTemplate getJDBCTempate() {
        
        DataSource ds = ServiceLocator.getDataSource("java:comp/env/jdbc/testdb");
        JdbcTemplate jtm = new JdbcTemplate(ds);
        
        return jtm;
    }
}

In the DatabaseService we have methods to perform database operations utilizing the JDBCTemplate library.

public static User getUserById(Long id) {

    String sql = "SELECT * FROM Users WHERE Id = ?";

    JdbcTemplate jtm = getJDBCTempate();
    User user = (User) jtm.queryForObject(sql, new Object[]{id},
            new BeanPropertyRowMapper(User.class));

    return user;
}

The getUserById() method returns a user identified by its ID. The queryForObject() runs the specified SQL statement and returns an object. The BeanPropertyRowMapper converts the returned row into the target class (User).

public static List<User> getAllUsers() {

    String sql = "SELECT * FROM Users";

    JdbcTemplate jtm = getJDBCTempate();
    
    List<User> users = (List<User>) jtm.query(sql,
            new BeanPropertyRowMapper(User.class));
    
    return users;
}

The getAllUsers() method returns all users from the table. The query() method returns a list of types.

package com.zetcode.web;

import com.zetcode.bean.User;
import com.zetcode.service.DatabaseService;
import java.io.IOException;
import java.util.List;
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 = "MyController", urlPatterns = {"/MyController"})
public class MyController extends HttpServlet {

    private static final String ADD_USER_VIEW = "addUser.ftl";
    private static final String UPDATE_USER_VIEW = "updateUser.ftl";
    private static final String LIST_USERS_VIEW = "listUsers.ftl";
    
    private static final String USER_ADDED_VIEW = "userAdded.html";
    private static final String USER_DELETED_VIEW = "userDeleted.html";
    private static final String USER_MODIFIED_VIEW = "userUpdated.html";
    
    private static final String DELETE_ACTION = "deleteUser";
    private static final String ADD_ACTION = "addUser";
    private static final String UPDATE_ACTION = "updateUser";
    private static final String LIST_ACTION = "listUsers";

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

        response.setContentType("text/html;charset=UTF-8");

        String path = "";

        String action = request.getParameter("action");

        if (DELETE_ACTION.equals(action)) {
            Long userId = Long.parseLong(request.getParameter("userId"));
            DatabaseService.deleteUser(userId);

            path = USER_DELETED_VIEW;
        } else if (ADD_ACTION.equals(action)) {

            path = ADD_USER_VIEW;

        } else if (UPDATE_ACTION.equals(action)) {

            Long userId = Long.parseLong(request.getParameter("userId"));
            User user = DatabaseService.getUserById(userId);
            request.setAttribute("user", user);
            path = UPDATE_USER_VIEW;

        } else if (LIST_ACTION.equals(action)) {

            List<User> users = DatabaseService.getAllUsers();
            request.setAttribute("users", users);
            path = LIST_USERS_VIEW;
        }

        RequestDispatcher dispatcher = request.getRequestDispatcher(path);
        dispatcher.forward(request, response);
    }

    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        
        String path = "";

        response.setContentType("text/html;charset=UTF-8");

        String action = request.getParameter("action");

        if (ADD_ACTION.equals(action)) {

            User user = new User();
            user.setFirstName(request.getParameter("firstName"));
            user.setLastName(request.getParameter("lastName"));
            user.setEmail(request.getParameter("email"));

            DatabaseService.addUser(user);
            path = USER_ADDED_VIEW;

        } else if (UPDATE_ACTION.equals(action)) {

            User user = new User();
            user.setId(Long.parseLong(request.getParameter("id")));
            user.setFirstName(request.getParameter("firstName"));
            user.setLastName(request.getParameter("lastName"));
            user.setEmail(request.getParameter("email"));

            DatabaseService.updateUser(user);
            path = USER_MODIFIED_VIEW;
        }

        response.sendRedirect(path);
    }
}

MyController is a controller class that manages incoming requests and delegates to specific service methods. We have two methods: the doGet() handles HTTP GET requests and the doPost() handles HTTP POST requests.

private static final String ADD_USER_VIEW = "addUser.ftl";
private static final String UPDATE_USER_VIEW = "updateUser.ftl";
private static final String LIST_USERS_VIEW = "listUsers.ftl";

These are three FreeMarker template views.

private static final String USER_ADDED_VIEW = "userAdded.html";
private static final String USER_DELETED_VIEW = "userDeleted.html";
private static final String USER_MODIFIED_VIEW = "userUpdated.html";

Here we have three HTML views. They are used for confirmation of our tasks.

private static final String DELETE_ACTION = "deleteUser";
private static final String ADD_ACTION = "addUser";
private static final String UPDATE_ACTION = "updateUser";
private static final String LIST_ACTION = "listUsers";

We have four different actions: to delete a user, to add a new user, to update a user, and to list all users.

if (DELETE_ACTION.equals(action)) {
    Long userId = Long.parseLong(request.getParameter("userId"));
    DatabaseService.deleteUser(userId);

    path = USER_DELETED_VIEW;
}

When a delete action is received, we find ou the ID from the request and call the DatabaseService's deleteUser() method. Then a view is selected.

} else if (UPDATE_ACTION.equals(action)) {

    Long userId = Long.parseLong(request.getParameter("userId"));
    User user = DatabaseService.getUserById(userId);
    request.setAttribute("user", user);
    path = UPDATE_USER_VIEW;

}

When we click on the update link, this code is executed. A user is retrieved from the database and the User object is added to the request object. The UPDATE_USER_VIEW will forward the application to the template file, which has a form for updating the user. After the form is submitted, a POST request is send to the controller and its doPost() method is executed.

} else if (LIST_ACTION.equals(action)) {

    List<User> users = DatabaseService.getAllUsers();
    request.setAttribute("users", users);
    path = LIST_USERS_VIEW;
}

Here we retrieve all users and set the list of users to the request object. We select the LIST_USERS_VIEW view.

response.sendRedirect(path);

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 user multiple times).

<!DOCTYPE html>
<html>
    <head>
        <title>Add new user</title>
        <meta charset="UTF-8">
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
        </head>
    <body>
        
        <form action="MyController?action=addUser" method="post"> 
            
            <label for="fname">First name:</label>
            <input id="fname" type="text" name="firstName">
            <br>
            
            <label for="lname">Last name:</label>
            <input id="lname" type="text" name="lastName">
            <br>

            <label for="email">Email:</label>
            <input id="email" type="text" name="email">            
            <br>
            
            <button type="submit">Submit</button>
            
        </form>

    </body>
</html>

The addUser.ftl is a template file that contains a form to add a new user.

<form action="MyController?action=addUser" method="post"> 

The form calls the MyController servlet and sets an action parameter to addUser.

<!DOCTYPE html>
<html>
    <head>
        <title>Update user</title>
        <meta charset="UTF-8">
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
        <style>
            input[readonly] {
                background-color:lightGray;
            }
        </style>
        </head>
    <body>
        
        <form action="MyController?action=updateUser" method="post"> 
            
            <label for="id">Id:</label>
            <input id="id" type="text" name="id" readonly value="${user.id}">
            <br>                
            
            <label for="fname">First name:</label>
            <input id="fname" type="text" name="firstName" value="${user.firstName}">
            <br>
            
            <label for="lname">Last name:</label>
            <input id="lname" type="text" name="lastName" value="${user.lastName}">
            <br>

            <label for="email">Email:</label>
            <input id="email" type="text" name="email" value="${user.email}">            
            <br>
            
            <button type="submit">Submit</button>
            
        </form>

    </body>
</html>

The addUser.ftl template file contains a form to update a specific user.

<style>
    input[readonly] {
        background-color:lightGray;
    }
</style>

This CSS style paints the background of a readonly input tag in light gray colour.

<label for="id">Id:</label>
<input id="id" type="text" name="id" readonly value="${user.id}">
<br>   

The ID is a readonly parameter. The ${user.id} is a FreeMarker interpolation that resolves to the ID of the user. Before reaching updateUser.ftl file, the request obtains a user object, which is about to be modified.

<!DOCTYPE html>
<html>
    <head>
        <title>List users</title>
        <meta charset="UTF-8">
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
        </head>
    <body>
        <table>
            <thead>
                <tr>
                    <th>User Id</th>
                    <th>First Name</th>
                    <th>Last Name</th>
                    <th>Email</th>
                    <th colspan="2">Action</th>
                </tr>
            </thead>
            <tbody>
                <#list users as user>
                    <tr>
                        <td>${user.id}</td>
                        <td>${user.firstName}</td>
                        <td>${user.lastName}</td>
                        <td>${user.email}</td>
                        <td><a href="MyController?action=updateUser&userId=${user.id}">Update</a></td>
                        <td><a href="MyController?action=deleteUser&userId=${user.id}">Delete</a></td>
                    </tr>
                </#list>
            </tbody>
        </table>
        
        <p>
            <a href="MyController?action=addUser">Add new user</a>
        </p>
    </body>
</html>

The listUsers.ftl template file lists all users from the Users database table.

<#list users as user>

The FreeMarker #list directive iterates over all elements of the users collection.

<td>${user.id}</td>
<td>${user.firstName}</td>
<td>${user.lastName}</td>
<td>${user.email}</td>

These FreeMarker interpolations show the user data.

<td><a href="MyController?action=updateUser&userId=${user.id}">Update</a></td>  

This link sends an update action to the application controller; it also sends the ID of the user to be modified.

<td><a href="MyController?action=deleteUser&userId=${user.id}">Delete</a></td>  

This link sends a delete action to the application controller; it also sends the ID of the user to be deleted.

<a href="MyController?action=addUser">Add new user</a>  

This link sends an add user action to the controller.

<!DOCTYPE html>
<html>
    <head>
        <title>Main page</title>
        <meta charset="UTF-8">
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
    </head>
    <body>
        <a href="MyController?action=listUsers">List all users</a>
    </body>
</html>

This is a home page with a link that sends a list users action to the controller.

<!DOCTYPE html>
<html>
    <head>
        <title>User added</title>
        <meta charset="UTF-8">
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
    </head>
    <body>
        <p>
            New user has been added successfully. 
            <a href="MyController?action=listUsers">List all users</a>
        </p>
    </body>
</html>

This view informs that a user has been successfully added to the database table.

<!DOCTYPE html>
<html>
    <head>
        <title>User deleted</title>
        <meta charset="UTF-8">
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
    </head>
    <body>
        <p>
            User has been successfully deleted.
            <a href="MyController?action=listUsers">List all users</a>
        </p>
    </body>
</html>

This view informs about a user deletion.

<!DOCTYPE html>
<html>
    <head>
        <title>User modified</title>
        <meta charset="UTF-8">
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
    </head>
    <body>
        <p>
            User has been modified successfully. 
            <a href="MyController?action=listUsers">List all users</a>
        </p>
    </body>
</html>

This view informs about the modification of a user.

In the above screenshot, we can see a list of users. The application is deployed on a NetBeans' built-in Tomcat server, which is listening on port 8084.

In this tutorial, we have created a Java web application that performs CRUD operations. It used FreeMarker, Servlet, and JDBCTemplate. You might also be interested in the related tutorials: JdbcTemplate tutorial, FreeMarker tutorial, Java tutorial, Introduction to Play, Introduction to Spark, or Introduction to Stripes.