CRUD Operations in Java Web Applications using jTable jQuery plugin

DEMO DOWNLOAD

This post is a part of the series of articles on using jQuery jTable in Java Web Applications. In my previous post I explained how to setup jTable and how to get data from server side to display in jTable. Now let us see how to use jTable jQuery plugin to perform AJAX based CRUD operations in Java Web Applications (using MySql Server and Model 2 Approach with JSP, Servlets and POJO's).

1. First let us create an user table with the following simple query in MySql Server and enter some dummy records in it.

create table tblUser(userid int,firstname varchar(50),lastname varchar(50),email varchar(50)); 

2. Next step is to create a dynamic web project in eclipse and setup all required libraries in it. I have explained how to download and setup jTable in Java Web Project here.    

In addition to that, we need to add MySql Java Connector jar file to Project Location/WEB-INF/lib folder.

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 + "]";
  }
  
  
 }

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

package com.programmingfree.utility;

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("/config.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 

5. 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.utility.DBUtility;
public class CrudDao {
 
 private Connection connection;

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

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

  } catch (SQLException e) {
   e.printStackTrace();
  }
 }
 
 public void deleteUser(int userId) {
  try {
   PreparedStatement preparedStatement = connection
     .prepareStatement("delete from tblUser 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 tblUser set lastname=?,email=?" +
       "where userid=?");
   // Parameters start with 1   
   preparedStatement.setString(1, user.getLastName());
   preparedStatement.setString(2, user.getEmail());   
   preparedStatement.setInt(3, 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 tblUser");
   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 tblUser 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;
 }

}

6. Now it is the right time to create controller servlet that would call the data access methods to perform real CRUD operations. But before that, let us see what kind of response jTable jQuery plugin expects for each type of operation. This is important because jTable will work as expected only when we provide response in the proper format as expected by the plugin. 

All CRUD operations must return response in JSON format. Following are the examples of responses to be sent to jTable for each type of operation,

CREATE RECORD

For create operations, the response from server side must return newly created record (as JSON object)! A sample return value for createAction can be:

{
 "Result":"OK",
 "Record":{"PersonId":5,"Name":"Dan Brown","Age":55,"RecordDate":"\/Date(1320262185197)\/"}
}

The returning JSON object must contain a Result property that's value can be "OK" or "ERROR". If it's "OK",Record property is the created record. 

LIST RECORDS (READ OPERATION)

For read operations, Result property can be "OK" or "ERROR". If it is "OK", Records property must be an array of records. If it is "ERROR", a Message property can explain reason of the error to show to the user.

{
 "Result":"OK",
 "Records":[
  {"PersonId":1,"Name":"Benjamin Button","Age":17,"RecordDate":"\/Date(1320259705710)\/"},
  {"PersonId":2,"Name":"Douglas Adams","Age":42,"RecordDate":"\/Date(1320259705710)\/"},
  {"PersonId":3,"Name":"Isaac Asimov","Age":26,"RecordDate":"\/Date(1320259705710)\/"},
  {"PersonId":4,"Name":"Thomas More","Age":65,"RecordDate":"\/Date(1320259705710)\/"}
 ]
}

Heads Up!! - Note for listing records the response should be a JSON Array and not simple JSON Object whereas for create operations it is the opposite of it, should return JSON object and not a JSON Array. Also, for create operation the second parameter in the JSON Response is "Record" and for list operations it is "Records" in plural.

UPDATE & DELETE RECORD

For update and delete operations, server side response is very simple with Result parameter alone such as this,

{"Result":"OK"}

Let us now go ahead and create controller servlet that receives request from JSP, dispatches request to underlying POJO and then send back response to JSP in JSON format as explained above.

package com.programmingfree.controller;

import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

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

import com.google.gson.Gson;
import com.google.gson.JsonArray;
import com.google.gson.JsonElement;
import com.google.gson.reflect.TypeToken;
import com.programmingfree.dao.CrudDao;
import com.programmingfree.model.User;


public class CRUDController extends HttpServlet {
 private static final long serialVersionUID = 1L;
 private CrudDao dao;
    
    public CRUDController() {
        dao=new CrudDao();
    }


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

 
 protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
  if(request.getParameter("action")!=null){
   List<User> lstUser=new ArrayList<User>();
   String action=(String)request.getParameter("action");
   Gson gson = new Gson();
   response.setContentType("application/json");
   
   if(action.equals("list")){
    try{      
    //Fetch Data from User Table
    lstUser=dao.getAllUsers();   
    //Convert Java Object to Json    
    JsonElement element = gson.toJsonTree(lstUser, new TypeToken<List<User>>() {}.getType());
    JsonArray jsonArray = element.getAsJsonArray();
    String listData=jsonArray.toString();    
    //Return Json in the format required by jTable plugin
    listData="{\"Result\":\"OK\",\"Records\":"+listData+"}";   
    response.getWriter().print(listData);
    }catch(Exception ex){
     String error="{\"Result\":\"ERROR\",\"Message\":"+ex.getMessage()+"}";
     response.getWriter().print(error);
     ex.printStackTrace();
    }    
   }
   else if(action.equals("create") || action.equals("update")){
    User user=new User();
    if(request.getParameter("userid")!=null){       
       int userid=Integer.parseInt(request.getParameter("userid"));
       user.setUserid(userid);
    }
    if(request.getParameter("firstName")!=null){
     String firstname=(String)request.getParameter("firstName");
     user.setFirstName(firstname);
    }
    if(request.getParameter("lastName")!=null){
       String lastname=(String)request.getParameter("lastName");
       user.setLastName(lastname);
    }
    if(request.getParameter("email")!=null){
       String email=(String)request.getParameter("email");
       user.setEmail(email);
    }
    try{           
     if(action.equals("create")){//Create new record
      dao.addUser(user);     
      lstUser.add(user);
      //Convert Java Object to Json    
      String json=gson.toJson(user);     
      //Return Json in the format required by jTable plugin
      String listData="{\"Result\":\"OK\",\"Record\":"+json+"}";           
      response.getWriter().print(listData);
     }else if(action.equals("update")){//Update existing record
      dao.updateUser(user);
      String listData="{\"Result\":\"OK\"}";         
      response.getWriter().print(listData);
     }
    }catch(Exception ex){
      String error="{\"Result\":\"ERROR\",\"Message\":"+ex.getStackTrace().toString()+"}";
      response.getWriter().print(error);
    }
   }else if(action.equals("delete")){//Delete record
    try{
     if(request.getParameter("userid")!=null){
      String userid=(String)request.getParameter("userid");
      dao.deleteUser(Integer.parseInt(userid));
      String listData="{\"Result\":\"OK\"}";        
      response.getWriter().print(listData);
     }
    }catch(Exception ex){
    String error="{\"Result\":\"ERROR\",\"Message\":"+ex.getStackTrace().toString()+"}";
    response.getWriter().print(error);
   }    
  }
  }
  }
}

Make sure web.xml contains proper servlet mapping as shown below,

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd" id="WebApp_ID" version="3.0">
  <display-name>jTableJavaExample</display-name>
  <welcome-file-list>
    <welcome-file>index.html</welcome-file>
    <welcome-file>index.htm</welcome-file>
    <welcome-file>index.jsp</welcome-file>
    <welcome-file>default.html</welcome-file>
    <welcome-file>default.htm</welcome-file>
    <welcome-file>default.jsp</welcome-file>
  </welcome-file-list>
<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>
</web-app>

7. Finally create JSP file, with definitions for jTable action urls and fields,

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>AJAX based CRUD operations using jTable in Servlet and JSP</title>
<!-- Include one of jTable styles. -->
<link href="css/metro/crimson/jtable.css" rel="stylesheet" type="text/css" />
<link href="css/jquery-ui-1.10.3.custom.css" rel="stylesheet" type="text/css" />
<!-- Include jTable script file. -->
<script src="js/jquery-1.8.2.js" type="text/javascript"></script>
<script src="js/jquery-ui-1.10.3.custom.js" type="text/javascript"></script>
<script src="js/jquery.jtable.js" type="text/javascript"></script>
<script type="text/javascript">
    $(document).ready(function () {
        $('#PersonTableContainer').jtable({
            title: 'Table of people',
            actions: {
                listAction: 'CRUDController?action=list',
                createAction:'CRUDController?action=create',
                updateAction: 'CRUDController?action=update',
                deleteAction: 'CRUDController?action=delete'
            },
            fields: {
                userid: {
                 title:'S.NO',
                    key: true,
                    list: true,
                    create:true
                },
                firstName: {
                    title: 'First Name',
                    width: '30%',
                    edit:false
                },
                lastName: {
                    title: 'Last Name',
                    width: '30%',
                    edit:true
                },
                email: {
                    title: 'Email',
                    width: '20%',
                    edit: true
                }                
            }
        });
        $('#PersonTableContainer').jtable('load');
    });
</script>
</head>
<body>
<div style="width:60%;margin-right:20%;margin-left:20%;text-align:center;">
<h1>AJAX based CRUD operations in Java Web Application using jquery jTable plugin</h1>
<h4>Demo by Priya Darshini, Tutorial at <a href="http://www.programming-free.com/2013/07/setup-load-data-jtable-jsp-servlet.html">www.programming-free.com</a></h4>
<div id="PersonTableContainer"></div>
</div>
</body>
</html>

Note that we have initialized jTable first with action urls to perform crud operations and then called 'load' method to load the table with server side data. jTable fires AJAX calls for each type of operation user performs on the table and sends required parameters along with request to server side. The field name should exactly match the name of columns as how we have defined it in the model class.

That is all! We are now good to run the project. If you have downloaded the source code I have provided, make sure you have created a mysql table with the same structure as mentioned in this article before trying to execute the project.

When the page initially loads, jTable plugin calls the url provided in listAction option to fetch results from server side and creates an html table with header, content and required icons as shown in the below screenshot,

When the user clicks on the '+ Add new record' link present at the top right corner of the table, a create record form is automatically generated by jTable such as this,

Note that in this example, only lastname and email columns are made editable with 'edit' option set to true. Hence when the user clicks on edit icon, the edit form is automatically generated such as this,

When the user clicks delete icon for a record, a confirmation dialog is shown to the user by jTable (confirmation is optional but open as default) :

Articles on jTable in Java Web Applications

• jQuery jTable plugin in Java Web Applications - An Introduction
• Setup and Load Data in jTable using Servlets and JSP
• AJAX based CRUD Operations in Java Web Applications using jTable jQuery plugin
• Understanding User Interface Options
• jTable Pagination in Java Web Applications

These articles on jTable jQuery plugin have been included in the Official jTable Documentation. Thanks to Halil İbrahim Kalkan for that.

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!