CRUD Operations using Servlet and FreeMarker Template Engine

DOWNLOAD

CRUD(Create, Read, Update, Delete) operations are the four basic operations any data driven websites would perform often. In Java Web Applications, this can be done using a simple Controller Servlet that would dispatch request to the corresponding underlying data access layer and forward the results to the front end view, which in most cases would be a JSP page. This is a very common topic and many tutorials are available on the same. I am going to repeat the same in this article, but with a small difference with FreeMarker Templates instead of JSP's as view in this application.

FreeMarker Template Engine - What and Why?

FreeMarker is a Java Template Engine, a generic tool to generate text output anything from text output to autogenerated source code. Although FreeMarker has some programming capabilities, it is not a full-blown programming language. Instead, Java programs prepare the data to be displayed (like issue SQL queries), and FreeMarker just generates textual pages that display the prepared data using templates.

So now let us look into some of the advantages of using FreeMarker templates instead of JSP as view for Java Web Applications that follows MVC Architecture.

1. FreeMarker is designed to be practical for the generation of HTML Web pages, particularly by servlet-based applications following the MVC (Model View Controller) pattern. The idea behind using the MVC pattern for dynamic Web pages is that you separate the designers (HTML authors) from the programmers. In larger projects, time constraints often dictate that the HTML and Java be developed in parallel.First, JSP relies too heavily on Java syntax. HTML coders seldom know Java syntax well enough to author JSP pages entirely by themselves. 

With FreeMarker, designers can change the appearance of a page without programmers having to change or recompile code, because the application logic (Java programs) and page design (FreeMarker templates) are separated. Templates do not become polluted with complex program fragments. This separation is useful even for projects where the programmer and the HTML page author is the same person, since it helps to keep the application clear and easily maintainable.

2. No servlet specific scopes and other highly technical things in templates. It was made for MVC from the beginning, it focuses only on the presentation.

3. Easier to read, more terse syntax. For example: <#if x>...</#if> instead of <c:if test="${x}">...</c:if>

You can read a list of advantages of using FreeMarker over JSP here in FreeMarker FAQ page.

CRUD with MySql, Servlets and FreeMarker Template Engine

1. Let us start by downloading required libraries,

• MySql Java Connector
• freemarker.jar

2. First, let us create user table in MySql Server and have some dummy values added to it.

CREATE TABLE users (
  `userid` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(45) DEFAULT NULL,
  `lastname` varchar(45) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`userid`)
)

3. Open Eclipse. Go to File -> Add New -> Dynamic Web Project. Now add the required libraries to the newly created project's WebContent\WEB-INF\lib folder. The project structure should look like this after completing implementation,

3. Now let us create a model class('User.java') that contains getters and setters for the fields we have in mySql table.

package com.programmingfree.model;

 public class User {

  private int userid;
  private String firstName;
  private String lastName;
 
  private String email;
  public int getUserid() {
   return userid;
  }
  public void setUserid(int userid) {
   this.userid = userid;
  }
  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;
  }
  @Override
  public String toString() {
   return "User [userid=" + userid + ", firstName=" + firstName
     + ", lastName=" + lastName + ", email="
     + email + "]";
  }
  
  
 }

3. Next let us create a utility class to handle connections to database. The connection string properties are kept in a configuration file called "db.properties" in the src folder.

package com.programmingfree.util;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;


public class DBUtility {
 private static Connection connection = null;

    public static Connection getConnection() {
        if (connection != null)
            return connection;
        else {
            try {
             Properties prop = new Properties();
                InputStream inputStream = DBUtility.class.getClassLoader().getResourceAsStream("/db.properties");
                prop.load(inputStream);
                String driver = prop.getProperty("driver");
                String url = prop.getProperty("url");
                String user = prop.getProperty("user");
                String password = prop.getProperty("password");
                Class.forName(driver);
                connection = DriverManager.getConnection(url, user, password);
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            } catch (SQLException e) {
                e.printStackTrace();
            } catch (FileNotFoundException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            }
            return connection;
        }

    }

}

Properties configuration file should have contents such as this,

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/databasename
user=username
password=xxxxxx

4. Next step is to create a class that performs database operations such as select, create, delete and update.

package com.programmingfree.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.List;

import com.programmingfree.model.User;
import com.programmingfree.util.DBUtility;
public class CrudDao {
 
 private Connection connection;

 public CrudDao() {
  connection = DBUtility.getConnection();
 }

 public void addUser(User user) {
  try {
   PreparedStatement preparedStatement = connection
     .prepareStatement("insert into users(firstname,lastname,email) values (?, ?, ? )");
   // Parameters start with 1
   preparedStatement.setString(1, user.getFirstName());
   preparedStatement.setString(2, user.getLastName());
   
   preparedStatement.setString(3, user.getEmail());
   preparedStatement.executeUpdate();

  } catch (SQLException e) {
   e.printStackTrace();
  }
 }
 
 public void deleteUser(int userId) {
  try {
   PreparedStatement preparedStatement = connection
     .prepareStatement("delete from users where userid=?");
   // Parameters start with 1
   preparedStatement.setInt(1, userId);
   preparedStatement.executeUpdate();

  } catch (SQLException e) {
   e.printStackTrace();
  }
 }
 
 public void updateUser(User user) throws ParseException {
  try {
   PreparedStatement preparedStatement = connection
     .prepareStatement("update users set firstname=?, lastname=?, email=?" +
       "where userid=?");
   // Parameters start with 1
   preparedStatement.setString(1, user.getFirstName());
   preparedStatement.setString(2, user.getLastName());
   
   preparedStatement.setString(3, user.getEmail());
   preparedStatement.setInt(4, user.getUserid());
   preparedStatement.executeUpdate();

  } catch (SQLException e) {
   e.printStackTrace();
  }
 }

 public List<User> getAllUsers() {
  List<User> users = new ArrayList<User>();
  try {
   Statement statement = connection.createStatement();
   ResultSet rs = statement.executeQuery("select * from users");
   while (rs.next()) {
    User user = new User();
    user.setUserid(rs.getInt("userid"));
    user.setFirstName(rs.getString("firstname"));
    user.setLastName(rs.getString("lastname"));
    
    user.setEmail(rs.getString("email"));
    users.add(user);
   }
  } catch (SQLException e) {
   e.printStackTrace();
  }

  return users;
 }
 
 public User getUserById(int userId) {
  User user = new User();
  try {
   PreparedStatement preparedStatement = connection.
     prepareStatement("select * from users where userid=?");
   preparedStatement.setInt(1, userId);
   ResultSet rs = preparedStatement.executeQuery();
   
   if (rs.next()) {
    user.setUserid(rs.getInt("userid"));
    user.setFirstName(rs.getString("firstname"));
    user.setLastName(rs.getString("lastname"));
    
    user.setEmail(rs.getString("email"));
   }
  } catch (SQLException e) {
   e.printStackTrace();
  }

  return user;
 }
}

5. Now create a controller servlet(CrudController.java) that will transfer control to data access class to perform database operations and forward the results to the view(.ftl files).

package com.programmingfree.controller;

import java.io.IOException;
import java.text.ParseException;


import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.programmingfree.dao.CrudDao;
import com.programmingfree.model.User;


public class CrudController extends HttpServlet {
 private static final long serialVersionUID = 1L;
    private static String INSERT_OR_EDIT = "/user.ftl";
    private static String LIST_USER = "/listUser.ftl";
    private static String ADD_USER = "/adduser.ftl";
    private CrudDao dao;


    public CrudController() {
      super();
         dao = new CrudDao();
    }

 
 protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
  String forward="";
  if(request.getParameter("action")!=null){
        String action = request.getParameter("action");

        if (action.equalsIgnoreCase("delete")){
            int userId = Integer.parseInt(request.getParameter("userId"));
            dao.deleteUser(userId);
            forward = LIST_USER;
            request.setAttribute("users", dao.getAllUsers());    
        } else if (action.equalsIgnoreCase("edit")){
            forward = INSERT_OR_EDIT;
            int userId = Integer.parseInt(request.getParameter("userId"));
            User user = dao.getUserById(userId);
            request.setAttribute("user", user);
        } else if (action.equalsIgnoreCase("listUser")){
            forward = LIST_USER;
            request.setAttribute("users", dao.getAllUsers());
        } 
        else {
            forward = ADD_USER;
        }
  }
  else{
   forward = LIST_USER;
            request.setAttribute("users", dao.getAllUsers());
  }
  

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

 
 protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
  User user = new User();
        user.setFirstName(request.getParameter("firstName"));
        user.setLastName(request.getParameter("lastName"));        
        user.setEmail(request.getParameter("email"));
        String userid = request.getParameter("userid");
        if(userid == null || userid.isEmpty())
        {
            dao.addUser(user);
        }
        else
        {
            user.setUserid(Integer.parseInt(userid));
            try {
    dao.updateUser(user);
   } catch (ParseException e) {    
    e.printStackTrace();
   }
        }
        RequestDispatcher view = request.getRequestDispatcher(LIST_USER);
        request.setAttribute("users", dao.getAllUsers());
        view.forward(request, response);
 }
}

6. This is one of the important step in this project, to update web.xml file and make an entry for freemarker.ext.servlet.FreemarkerServlet servlet. 

<?xml version="1.0" encoding="UTF-8"?>
<web-app id="WebApp_ID" version="3.0" xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xsi:schemalocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd">
  <display-name>FreeMarkerCRUDExample</display-name>
  <servlet>
  <servlet-name>freemarker</servlet-name>
  <servlet-class>freemarker.ext.servlet.FreemarkerServlet</servlet-class>

  <!-- FreemarkerServlet settings: -->
  <init-param>
   <param-name>TemplatePath</param-name>
   <param-value>/</param-value>
  </init-param>
  <init-param>
   <param-name>NoCache</param-name>
   <param-value>true</param-value>
  </init-param>
  <init-param>
   <param-name>ContentType</param-name>
   <param-value>text/html; charset=UTF-8</param-value> <!-- Forces UTF-8 output encoding! -->
  </init-param>

  <!-- FreeMarker settings: -->
  <init-param>
   <param-name>template_update_delay</param-name>
   <param-value>0</param-value> <!-- 0 is for development only! Use higher value otherwise. -->
  </init-param>
  <init-param>
   <param-name>default_encoding</param-name>
   <param-value>ISO-8859-1</param-value> <!-- The encoding of the template files. -->
  </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>



 <servlet>
  <servlet-name>CrudController</servlet-name>
  <servlet-class>com.programmingfree.controller.CrudController</servlet-class>
 </servlet>

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

 <!--
  Prevent the visiting of MVC Views from outside the servlet container.
  RequestDispatcher.forward/include should and will still work. Removing
  this may open security holes!
 -->
 <security-constraint>
  <web-resource-collection>
   <web-resource-name>FreeMarker MVC Views</web-resource-name>
   <url-pattern>*.ftl</url-pattern>
  </web-resource-collection>
  <auth-constraint>
   <!-- Nobody is allowed to visit these -->
  </auth-constraint>
 </security-constraint>
</web-app>

Note how we mapped "freemarker.ext.servlet.FreemarkerServlet" servlet with url-pattern *.ftl. Thus all the request that ends with .ftl will get processed by FreemarkerServlet servlet. 

For the controller servlet, we have mapped it to the url pattern '/CrudController'.

6. Finally it is time to create FTL templates as views in WebContent folder just like how we create JSP's.

listUser.ftl

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=EUC-KR">
<title>Show All Users</title>
</head>
<body>
<div>
    <table border="1" align="center" style="width:50%">
        <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.userid}</td>
                    <td>${user.firstName}</td>
                    <td>${user.lastName}</td>
                    
                    <td>${user.email}</td>
                    <td><a href="CrudController?action=edit&userId=${user.userid}">Update</a></td>
                    <td><a href="CrudController?action=delete&userId=${user.userid}">Delete</a></td>
                </tr>
            </#list>
        </tbody>
    </table>
    <p><a href="CrudController?action=insert">Add User</a></p>
    </div>
</body>
</html>

user.ftl

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=EUC-KR">
<title>Update user</title>
</head>
<body>
<div>
<br/>
    <form method="POST" action='CrudController' name="frmAddUser">    
        User ID : <input type="text" readonly="readonly" name="userid"
            value="${user.userid}" /> <br /> 
        First Name : <input
            type="text" name="firstName"
            value="${user.firstName}" /> <br /> 
        Last Name : <input
            type="text" name="lastName"
            value="${user.lastName}" /> <br />  
        Email : <input type="text" name="email"
            value="${user.email}" /> <br /> <input
            type="submit" value="Submit" />
    </form>
</div>
</body>
</html>

addUser.ftl

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=EUC-KR">
<title>Add new user</title>
</head>
<body>   
<div class="centered">
<br/>
    <form method="POST" action='CrudController' name="frmAddUser">
     
        User ID : <input type="text" readonly="readonly" name="userid"/> <br /> 
        First Name : <input
            type="text" name="firstName"/> <br /> 
        Last Name : <input
            type="text" name="lastName"/> <br /> 
        Email : <input type="text" name="email" /> <br /> <input
            type="submit" value="Submit" />
    </form>
</div>
</body>
</html>

Now let us take a closer look at how these freemarket templates work internally. An important idea behind FreeMarker (actually, behind Web MVC) is that presentation logic and "business logic" should be separated. In the template you only deal with presentation issues, that is, visual design issues, formatting issues. The data that will be displayed (such as the user name and so on) is prepared outside FreeMarker, usually by routines written in Java language or other general purpose language. So the template author doesn't have to know how these values are calculated. In fact, the way these values are calculated can be completely changed while the templates can remain the same, and also, the look of the page can be completely changed without touching anything but the template. This separation can be especially useful when the template authors (designers) and the programmers are different individuals.

The template is stored on the Web server, usually just like the static HTML page would be. But whenever someone visits this page, FreeMarker will step in and transform the template on-the-fly to plain HTML by replacing the ${...}-s with up-to-date content from database (e.g., replacing ${user.userid} with Priya Darshini) and send the result to the visitor's Web browser. 

You can read about the syntaxes and various other features of Freemarker Template Engine here.

Now run the application on Tomcat server and hit the action url in browser, (http://localhost:8080/ApplicationName/CrudController),

Note : Make sure you have created user table in MySql Server with the create statement given above and inserted a few dummy row in it, before running the sample application.

Please leave your comments and queries about this post in the comment sections in order for me to improve my writing skills and to showcase more useful posts.Thanks for reading!! 

Subscribe to GET LATEST ARTICLES!