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.