AJAX based CRUD Operations in Java Web Applications using jTable jQuery plugin




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)! 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) :





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!


advertise here

Related

jTable 900320017079248170

Post a Comment

  1. Its veru good Article
    But can you explain how did you do the configuration of column(hide and show)

    ReplyDelete
    Replies
    1. Hi,

      You can do this by setting list:true or false option in the fields section,

      fields: {
      userid: {
      title:'S.NO',
      key: true,
      list: true,
      }
      }

      Check this out, http://www.jtable.org/ApiReference#fopt-list

      Delete
  2. Hi, what a superb tutorial,... Now I want to have pagination in it, I know that it is possible, but how do I implement it?, I would need it asap,... ;-)

    ReplyDelete
    Replies
    1. Hi,

      I will try to write an article on how to implement paging and sorting in jtable in the coming week. For now, to understand in simple terms you just have to get the total record count of your table in server side and return json as shown below,

      { Result = "OK", Records = students, TotalRecordCount = tableRowCount}

      In the view(jsp),

      $('#StudentTableContainer').jtable({
      title: 'The Student List',
      paging: true, //Enable paging
      pageSize: 10, //Set page size (default: 10)

      actions: {
      listAction: '/Demo/StudentList',
      deleteAction: '/Demo/DeleteStudent',
      updateAction: '/Demo/UpdateStudent',
      createAction: '/Demo/CreateStudent'
      },
      fields: {
      StudentId: {
      key: true,
      create: false,
      edit: false,
      list: false
      },
      Name: {
      title: 'Name',
      width: '23%'
      },
      EmailAddress: {
      title: 'Email address',
      list: false
      }
      }
      }
      });

      Hope this helps!

      Delete
    2. Article on Pagination,

      http://www.programming-free.com/2014/02/jtable-pagination-in-java-web.html

      Delete
  3. :-) and while you are @ it,... en inputfield tosearch, like it is possible for the datatables,... thank you very much in advance for your response!

    ReplyDelete
    Replies
    1. It is possible to search fields in jTable also but unlike datatables, this feature is kept external and can be done only on button click event. Refer to this link below,

      http://jtable.org/Demo/Filtering

      Hope this helps!

      Delete
  4. hi Priya!!
    Excellent article.waiting for Pagination one.....

    ReplyDelete
    Replies
    1. It is here,

      http://www.programming-free.com/2014/02/jtable-pagination-in-java-web.html

      Delete
  5. hello priya,
    i was executing the same program with struts2 but not getting data in index.jsp.plz help me

    ReplyDelete
    Replies
    1. The example above will run only is sime MVC model and not in struts2 architecture. You may have to tweak this code to make it work.

      Delete
  6. Hi priya ..
    I am woking on it to improve ..can you suggest me how to place the (delete/save button first and then cancel) button as it is general way to display in the screen..not like (cancel save).how to display as (save cancel)??
    can you suggest me anything.

    ReplyDelete
    Replies
    1. Hi Chandan,

      Play around with jquery.jtable.js file. The components of popup dialog are dynamically populated using jquery and you can locate it there.

      Thanks,
      Priya

      Delete
  7. Hello Priya,
    Every thing is all right,nice article,I loved it.One thing I am facing problem at retrieve and setting combo box value in java how to solve it . Could you please suggest ?

    ReplyDelete
  8. I implemented (insert/update/delete/retrieve colour change etc) but I could not implement combobox value update and how to do view operation if I am showing small amount of data.Please suggest.....Awaiting your positive note

    ReplyDelete
  9. Hi, nice tutorial, do you know if it's possible to have multiple primary keys?

    ReplyDelete
  10. Dear Priya,
    Hope all is well at your end.Q:--How to show error message in jTable?

    ReplyDelete
    Replies
    1. Hi,

      Please raise your questions here on jTable jQuery plugin to get immediate help.

      https://github.com/hikalkan/jtable/issues?state=open

      Hope this helps!

      Thanks,
      Priya

      Delete
  11. Hi Priya,
    Its indeed a great tutorial. One problem that I'm facing is the jtable with data is launching in a new browser tab / window instead of in the div within the current window that I've specified. Any reason why this could be happening.

    Any suggestion will be appreciated.

    ReplyDelete
    Replies
    1. Thank you. This issue sounds very weird. Post your code with the details of your analysis on the issue in any of technical forums to get immediate help.

      Delete
  12. Hi Priya,
    Do you have any working example of Parent -child based implementaion?
    -thanks

    ReplyDelete
  13. Hello Priya
    You mentionned at some point that you would try to add a tutorial on pagination which would be absolutely great. Can we expect it some time ?
    thanks

    ReplyDelete
    Replies
    1. Hi Anna,

      Just published my tutorial on Pagination in jTable here,

      http://www.programming-free.com/2014/02/jtable-pagination-in-java-web.html

      Thanks,
      Priya

      Delete
    2. Hello
      Very clear, thanks a lot, good job!

      Delete
  14. Hi Priya

    I am able to add more than one record in the jtable by using a for loop and jquery but the problem in CHROME browser is it adds all the records at a time not in a sequential order of for loop.It works fine in IE in a sequential order.can you please help me what I need to look into..
    Below is my code snippet
    for(var i=0; i<final_details.length;i++) {
    //alert("line item no before add="+final_details[i].line['#text']);
    $('#TranItemTableContainer').jtable('addRecord', {
    record: {
    line_no:final_details[i].line['#text'],
    item_type:final_details[i].itemType['#text'],
    item_no:final_details[i].itemno['#text'],
    qty:final_details[i].Quantity['#text'],
    ext_amt:final_details[i].Extamt['#text']
    }
    });

    }

    ReplyDelete
  15. hi priya
    this very nice post.can you provide this application in structs2 ?

    ReplyDelete
  16. can any one help , i am getting Can not load options for field detps while passing
    options: 'CRUDController?action=getDepts' in depts filed , how i can handle it in java where it is in C# return new { Result = "OK", Options = depts};

    ReplyDelete
  17. Hi Priya:

    I want know how i can upload photo user using that!. Please, help me!

    ReplyDelete
    Replies
    1. we can include image path that ll be displayed in jtable which can be in this way..
      image: {
      title: 'image',
      width: '10%',
      sorting: false,
      edit: false,
      create: false,
      display: function (responseJson) {
      var $img=null;
      $img = $('&ltimg src="Path of image" height="20px" title="Tooltip" /&gt');
      });
      return $img;
      }

      Delete
  18. How i can make my jtable scrollable with fixed header table?

    ReplyDelete
  19. Hello Priya,
    Could you suggest how to change color of child table data .i.e. Color of parent table is blue and wants to change of color of child table header and title.How could i do this? plz suggest....

    ReplyDelete
    Replies
    1. Hi Ranjan

      u can do it in jquery something like this





      div.jtable-main-container > table.jtable > thead {
      background-color: #c30000;
      position:absolute;
      }

      Delete
  20. hi.... i am facing a problem while running this code just (AJAX based CRUD operations in Java Web Application using jquery jTable plugin
    Demo by Priya Darshini, Tutorial at www.programming-free.com) this info is displayed ...nothing more information is displayd

    ReplyDelete
    Replies
    1. Did you create mysql table first and change database connection properties before running the sample code provided?

      Thanks,
      Priya

      Delete

emo-but-icon

Currency Converter

Built using AngularJS and ASP.NET Web API

SPONSORED

SUBSCRIBE


item