Pages Navigation Menu

Coding is much easier than you think

CRUD Operations in Java Web Applications using jTable jQuery plugin via Ajax

 
Struts-2-jTable-jQuery-plug-in-Create-record-animation-effect
 
In the previous article “Setting up JQuery jTable plugin in Java Web Applications” I have explained how to setup jTable plugin in java web application. This article describes on how to implement “Ajax based curd operation in Java Web Applications using the JQuery jTable plugin and it will not explain how to setup jTable plugin in java web application. So If you have not read the previous articles “Setting up JQuery jTable plugin in Java Web Applications I will recommend that you read that article first because it explains how you can integrate the JTable plug-in with a J2EE application, this article will assume that the code for the integration of the jQuery JTable plug-in is implemented, and only the code required for implementing CRUD operation will be explained here.
 

Steps done to set up our application for jTable

 
Libraries required for the setup,

 
Create a dynamic project in eclipse and setup above required libraries as explained here. The final project structure of this looks as below.
 
Curd in jtable java web application
 
Setup from the browser perspective: jTable
 
jTable plugin allows you to issue an ajax request via jQuery plugin and expects a JSON object as a response, hence the following configuration needs to be made in Jsp file
 

JSP

 





CRUD operations using jTable in J2EE












AJAX based CRUD operations using jTable in J2ee

 

JS File

 

$(document).ready(function() {
	$('#StudentTableContainer').jtable({
		title : 'Students List',
		actions : {
			listAction : 'Controller?action=list',
			createAction : 'Controller?action=create',
			updateAction : 'Controller?action=update',
			deleteAction : 'Controller?action=delete'
		},
		fields : {
			studentId : {
				title : 'Student Id',
				width : '30%',
				key : true,
				list : true,
				edit : false,
				create : true
			},
			name : {
				title : 'Name',
				width : '30%',
				edit : true
			},
			department : {
				title : 'Department',
				width : '30%',
				edit : true
			},
			emailId : {
				title : 'Email',
				width : '20%',
				edit : true
			}
		}
	});
	$('#StudentTableContainer').jtable('load');
});

 
I have explained the working of above jTable js file in my previous article “Setting up JQuery jTable plugin in Java Web Applications”
 
Now create a student table in Oracle database using the query below. On this table we are going to perform CRUD operation via ajax
 

create table Student(studentid int,name varchar(50),department varchar(50),
email varchar(50));

 

CurdDao

Create a class that performs CRUD operation in database
 

package com.dao;

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

import com.jdbc.DataAccessObject;
import com.model.Student;

public class CrudDao {

private Connection dbConnection;
private PreparedStatement pStmt;

public CrudDao() {
	dbConnection = DataAccessObject.getConnection();
}

public void addStudent(Student student) {
	String insertQuery ="INSERT INTO STUDENT(STUDENTID, NAME," +
			"DEPARTMENT, EMAIL) VALUES (?,?,?,?)";
	try {
		pStmt = dbConnection.prepareStatement(insertQuery);
		pStmt.setInt(1, student.getStudentId());
		pStmt.setString(2, student.getName());
		pStmt.setString(3, student.getDepartment());
		pStmt.setString(4, student.getEmailId());
		pStmt.executeUpdate();
	} catch (SQLException e) {
		System.err.println(e.getMessage());
	}
}

public void deleteStudent(int userId) {
	String deleteQuery = "DELETE FROM STUDENT WHERE STUDENTID = ?";
	try {
		pStmt = dbConnection.prepareStatement(deleteQuery);
		pStmt.setInt(1, userId);
		pStmt.executeUpdate();
	} catch (SQLException e) {
		System.err.println(e.getMessage());
	}
}

public void updateStudent(Student student)  {
	String updateQuery ="UPDATE STUDENT SET NAME = ?," +
			"DEPARTMENT = ?, EMAIL = ? WHERE STUDENTID = ?";
	try {
		pStmt = dbConnection.prepareStatement(updateQuery);
		pStmt.setString(1, student.getName());
		pStmt.setString(2, student.getDepartment());
		pStmt.setString(3, student.getEmailId());
		pStmt.setInt(4, student.getStudentId());
		pStmt.executeUpdate();

	} catch (SQLException e) {
		System.err.println(e.getMessage());
	}
}

public List getAllStudents() {
	List students = new ArrayList();

	String query = "SELECT * FROM STUDENT ORDER BY STUDENTID";
	try {
		Statement stmt = dbConnection.createStatement();
		ResultSet rs = stmt.executeQuery(query);
		while (rs.next()) {
			Student student = new Student();

			student.setStudentId(rs.getInt("STUDENTID"));
			student.setName(rs.getString("NAME"));
			student.setDepartment(rs.getString("DEPARTMENT"));
			student.setEmailId(rs.getString("EMAIL"));
			students.add(student);
		}
	} catch (SQLException e) {
		System.err.println(e.getMessage());
	}
	return students;
}
}

I hope the above code is self explanatory
 
Setup from the server’s perspective: Servlet
 
jTable uses the POST method by default while making AJAX calls to the server and in server side, we will convert Java objects created under different CRUD operation to JSON strings that will be parsed by jTable pugin in the JSP page and will be rendered on the web page. This conversion of Java Object to Json format is done using Google gson jar. I have used the below method of gson library to convert java object to json object
 

Gson gson = new GsonBuilder().setPrettyPrinting().create();
String jsonArray = gson.toJson(JSONROOT);

 
Now let us look into the different response created for CRUD operations
 

Reading

 
Method to jTable to get a list of records:
 

HashMap JSONROOT = new HashMap();

if (action.equals("list")) {
try{
	// Fetch Data from Student Table
	studentList = dao.getAllStudents();

	// Return in the format required by jTable plugin
	JSONROOT.put("Result", "OK");
	JSONROOT.put("Records", studentList);

	// Convert Java Object to Json
	String jsonArray = gson.toJson(JSONROOT);

	response.getWriter().print(jsonArray);
} catch (Exception ex) {
	JSONROOT.put("Result", "ERROR");
	JSONROOT.put("Message", ex.getMessage());
	String error = gson.toJson(JSONROOT);
	response.getWriter().print(error);
}
}

 
For read operations, Result property must be “OK” if operation is successful. If an error occurs, then Result property must be “ERROR”. If Result is “OK”, the Records property will contain an array of records to show in the MySql table. If it’s ERROR, a Message property will contain an error message to show to the user. A sample return value for listAction is show below
 
{“Result”:”OK”,”Records”:[
{
“studentId”: 1,
“name”: “Muthu vijay”,
“department”: “CSE”,
“emailId”: “[email protected]
},
{
“studentId”: 2,
“name”: “Bashit”,
“department”: “EEE”,
“emailId”: “[email protected]
},
{
“studentId”: 3,
“name”: “Haripriya”,
“department”: “IT”,
“emailId”: “[email protected]
}
]}
 

Creating & Updating

 
Creating and Updating a record is optional. If you allow user to create/update a record, you must supply an action to jTable to create a new record. In case of create you must return the newly created object in JSON format, where as in case of update you must return the updated object via its respective action, which is done via gson library. A sample return value for createAction/UpdateAction is shown below
 
{“Result”:”OK”,”Record”:{
“studentId”: 9,
“name”: “Lahir nisha”,
“department”: “CSE”,
“emailId”: “[email protected]
}}
 

if (action.equals("create") || action.equals("update")) {
try{
	Student student = new Student();
	if (request.getParameter("studentId") != null) {
		int studentId = Integer.parseInt(request.getParameter("studentId"));
		student.setStudentId(studentId);
	}

	if (request.getParameter("name") != null) {
		String name = request.getParameter("name");
		student.setName(name);
	}

	if (request.getParameter("department") != null) {
		String department = request.getParameter("department");
		student.setDepartment(department);
	}

	if (request.getParameter("emailId") != null) {
		String emailId = request.getParameter("emailId");
		student.setEmailId(emailId);
	}

	if (action.equals("create")) {
		// Create new record
		dao.addStudent(student);
	} else if (action.equals("update")) {
		// Update existing record
		dao.updateStudent(student);
	}

	// Return in the format required by jTable plugin
	JSONROOT.put("Result", "OK");
	JSONROOT.put("Record", student);

	// Convert Java Object to Json
	String jsonArray = gson.toJson(JSONROOT);
	response.getWriter().print(jsonArray);
} catch (Exception ex) {
	JSONROOT.put("Result", "ERROR");
	JSONROOT.put("Message", ex.getMessage());
	String error = gson.toJson(JSONROOT);
	response.getWriter().print(error);
}
}

 

Deleting

 
Similar to update/create option, delete record is optional. If you allow user to delete a record, you must supply an action to jTable to delete a record, and response of delete operation is similar to update.
 

if (action.equals("delete")) {
try{
	// Delete record
	if (request.getParameter("studentId") != null) {
		int studentId = Integer.parseInt(request.getParameter("studentId"));
		dao.deleteStudent(studentId);

		// Return in the format required by jTable plugin
		JSONROOT.put("Result", "OK");

		// Convert Java Object to Json
		String jsonArray = gson.toJson(JSONROOT);
		response.getWriter().print(jsonArray);
	}
} catch (Exception ex) {
	JSONROOT.put("Result", "ERROR");
	JSONROOT.put("Message", ex.getMessage());
	String error = gson.toJson(JSONROOT);
	response.getWriter().print(error);
}
}

 

Model class

 
Create Model class used in the controller, which will have getters and setters for fields specified in jTable script.

package com.model;

public class Student {

private int studentId;
private String name;
private String department;
private String emailId;

public int getStudentId() {
	return studentId;
}

public String getName() {
	return name;
}

public String getDepartment() {
	return department;
}

public String getEmailId() {
	return emailId;
}

public void setStudentId(int studentId) {
	this.studentId = studentId;
}

public void setName(String name) {
	this.name = name;
}

public void setDepartment(String department) {
	this.department = department;
}

public void setEmailId(String emailId) {
	this.emailId = emailId;
}
}

 

DAO Class

 
Create utility class which connect to database Using Oracle JDBC driver

package com.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;

public class DataAccessObject {
	private static Connection connection = null;

	public static Connection getConnection() {
		if (connection != null)
			return connection;
		else {
			// Store the database URL in a string
		String serverName = "127.0.0.1";
		String portNumber = "1521";
		String sid = "XE";
		String dbUrl = "jdbc:oracle:thin:@" + serverName + ":" + portNumber
				+ ":" + sid;
		try {
		Class.forName("oracle.jdbc.driver.OracleDriver");
		// set the url, username and password for the database
		connection = DriverManager.getConnection(dbUrl, "system", "admin");
		} catch (Exception e) {
			e.printStackTrace();
		}
		return connection;
	}
	}
}

 

web.xml

 


  index.jsp


  Controller
  com.servlet.Controller


  Controller
  /Controller

 

Demo

 
On running the application
 
Integrating-jQuery-jTable-plugin-with-Struts2-framework
 
On clicking ‘Add new record’
Struts-2-using-jTable-jQuery-plug-in-Create-record
 
Now the new record will be added with fade out animation
Struts-2-jTable-jQuery-plug-in-Create-record-animation-effect
 
On clicking edit button
Struts-2-jTable-jQuery-plug-in-Update-record
 
On clicking delete button
Struts-2-jTable-jQuery-plug-in-Delete-record
 
In the next article Pagination in Java Web Applications using jQuery jTable plugin I have implemented paging feature to the CRUD example demonstrated here.
 
download
 

Reference

jTable official website
AJAX based CRUD tables using ASP.NET MVC 3 and jTable jQuery plug-in
Wikipedia : JSON
 

39 Comments

  1. Hi,
    I am new to jtable. I have done cloning of data but instead of using typical format i want to display the data into my own JSP. For cloning if i use-
    $(‘#FormTableContainer’).jtable(‘showCreateForm’); —–>This works but show data into a simple dialog box
    To use my own JSP when i use below code this doesnt work
    $.ajax({
    url:”/my.jsp” OR url:”/my”
    });

  2. This helped me a lot thanks

  3. Dear Mohaideen Jamil, It is a nice and simple topic. I have implemented successfull. Please let me know how we can implement SORT and SEARCH function

  4. how to implement parent child records with JTable?

  5. the problem is when i edit existing record and press Save button nothing happens edit form is still there until i press cancel button.

  6. Hi Jamil,

    Thank you for such a fantastic post please keep the good work.

  7. Hi! Thanks for the tutorial. The link to the source code seems to be broken. Can you upload the correct link.

    Thanks!

    • Hi, Link works good, may be your internet provider would have blocked access to dropbox website.

  8. Hi,How to right align specific column data in jable.

  9. how to add 2 jquery jtable in same jsp page and what will be the change in struts.xml file

  10. the problem is when i edit existing record and press Save button nothing happens edit form is still there until i press cancel button.

  11. Hi.
    i got error probleme with communicating to the server “An error occured while communicating to the server”

  12. i got error problem with communicating to the server

  13. How can i put my list in Pages???

  14. hi

    i tried u r posting example when i am getting an error running the application in dilog box “An error occured while communicating to the server.” i added all js files what is the problem?
    i tried a lot.please give me the solution

    • Hi that error is returned when your servlet is not triggered via jtable jquery java script. Please check your code syntax.. This code works fine from my end.

  15. Sorry, this plugin is open source or test with java?

  16. Thank you for the very good Article jamil, I have problem in edit of record [code] if (action.equals(“update”)) {
    System.out.println(“Inside Update Student Ctrl”);[/code] the message is not printing in the console

    • the problem is when i edit existing record and press Save button nothing happens edit form is still there until i press cancel button.

  17. Hello again. I am trying to make a call out to ObjectYController prior to showing the ObjectX edit form (the two tables/objects are related and the specific record in X clicked on by the user drives what objects in Y to filter to show as options in the edit form popup for X). My code right now looks something like this due to the asynchronous nature of getJSON. Is there a way to get the scope of “self” to be the jtable widget or somehow make the servlet call synchronous (instead of a callback?). I have successfully created overrides for edit forms in other places, but none so far that have required a call to the server side prior to showing the edit form.

    [code]
    /* pre-process the request for the edit form
    ***********************************************************************/
    _requestEditForm: function ($tableRow) {
    var self = this;
    var record = JSON.stringify($tableRow.data(‘record’));
    this._$sourcingRow = $tableRow; // remember this for later
    $.getJSON(‘ObjectFooController’,{“action”:”getfilteredfoo”,”scenid”:”88″,”record”:record},self._showEditForm);
    },

    /* Shows edit dialog form.
    *************************************************************************/
    _showEditForm: function (data) {
    var self = this;
    // NOT THE SAME SCOPE OF SELF/THIS DESPITE BEING A METHOD OF JTABLE EXTENSION
    }
    [/code]

    • Actually, I answered my own question. Not the most elegant, but possibly the only way

      [code]
      /* pre-process the request for the sourcing form
      ***********************************************************************/
      _requestEditForm: function ($tableRow) {
      var self = this;
      var record = JSON.stringify($tableRow.data(‘record’));
      this._$sourcingRow = $tableRow; // remember this for later
      $.getJSON(‘ObjectFooController’,{“action”:”getunusedfoo”,”scenid”:”88″,”record”:record},self.createCallBack(this));
      },

      /* Shows source dialog form.
      // I ENDED UP HAVING TO SCOPE _showEditForm OUTSIDE THE EXTENSION AT GLOBAL LEVEL
      // _showEditForm LOOKS MUCH LIKE THE ORIGINAL OVERRIDE METHOD WITH
      // AN EARLY CALL TO self=jqueryRef INSTEAD OF ASSIGNING TO “this”
      *************************************************************************/
      createCallBack : function (jqueryRef){
      return function(data){
      _showEditForm(jqueryRef,data);
      };
      },

      [/code]

  18. Thank you for the article, but i have a small request i’d like to add a search field so for example i only wanna show the rows that have the same text was in text field i don’t know how to apply to the code in java. Thank you

  19. I thought String.trim() would work, but ended up just removing the the new line char with

    [code]
    String errMessage = e.getMessage();
    errMessage = errMessage.replace(“\n”,””);
    [/code]

    … on a side note, I can’t get the popup create/edit forms to render the css in ie9 (works fine in chrome) any tips on where to start looking for a problem with my setup?

  20. I am facing issue with loading of CSS … there are no errors but somehow it is unable to load css and

    • I made a small mistake, need to update my code.. Please add the tag < !DOCTYPE html> before <html> tag in jsp file, then it will work fine

  21. I am facing issue in editing the application using jquery, so please let me know how to use jquery – edit implementaion

  22. Can iget the source code

  23. Great article. Got me started on a big project, but your catch block has a syntax error that prevents the client from correctly parsing the json and won’t display anything meaningful other than jtable default “Error communicating with the server”. Error result jason should be built as follows (note the extra escaped quote surrounding the error message.

    [code]
    catch (Exception e) {
    String error = “{\”Result\”:\”ERROR\”,\”Message\”:\”” + e.getMessage()+ \””}”;
    response.getWriter().print(error);
    System.err.println(e.getMessage());
    }
    [/code]

    • Hi, Thankyou so much for pointing out the mistake. I have updated the code now.

      • No problem. Another one I forgot to mention is that JDBC will sometimes put a new line character in its exception message that also breaks the json parser used in jtable. I had to manually strip those out in another instance. Nearly impossible to find without the chrome javascript debugger.

        • Ya I too faced that issue.. I just replaced it with custom message.. please share here if you had found any solution for that issue..

    • Thank you so much for this. I spent hours trying to figure this one out. You made my day!!