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

 

 
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,

• google-gson-2.2.4.jar
• jQuery-1.8.2.js
• jQuery UI
• jTable
• Oracle JDBC driver

 
Create a dynamic project in eclipse and setup above required libraries as explained here. The final project structure of this looks as below.
 

 
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

	
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
 

 
On clicking ‘Add new record’

 
Now the new record will be added with fade out animation

 
On clicking edit button

 
On clicking delete button

 
In the next article Pagination in Java Web Applications using jQuery jTable plugin I have implemented paging feature to the CRUD example demonstrated here.
 

 

Reference

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