Pagination in Java Web Applications using jQuery jTable plugin
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 and in the article “Ajax based CRUD operation in Java Web Applications using JQuery jTables plugin” I have implemented Ajax based CRUD operation using JTable plugin.
This is the third article on jQuery jTable plugin in java web application that describes on how to implement pagination feature to do server side paging and it will not explain how to setup jTable plugin in java web application here. So If you have not read the previous articles “Setting up JQuery jTable plugin in Java web application” and “Ajax based curd operation in Java web application using JQuery jTables plugin”, I will recommend that you read that article first because first one explains how you can integrate the JTable plug-in with a Java web application and in second article explains on how to implement ajax based curd operation using jTable plugin. 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 pagination in Java web application using jTable will be explained here.
Setup
As described above, the prerequisite for this code is that you integrate the jQuery jTable plugin into the Java web application. You can find detailed instructions at JQuery jTable plugin in Java web application, so now I am going to take the sample application I created for explaining AJAX based crud operations in jTable plugin and continue to implement paging for that application.
1. Download sample application from here and import the project in eclipse.
2. Follow the steps in this article here to create table in database and make sure you have atleast 5 records in the table.
Now on running this application in Tomcat Server, you will see a table displaying records without pagination such as the one shown below.
Steps to Enable Paging:
Changes from the browser perspective: jTable
To enable paging, paging option must set to true. You can also set pageSize option (default value is 10) in jQuery Script code.
$('#StudentTableContainer').jtable({
//...
paging: true, //Set paging enabled
pageSize: 3, //Set page size
actions: {
//...
},
fields: {
//...
}
});
Note: pageSize sets the initial number of records to be displayed per page.
Modified Jsp page is shown below
<!DOCTYPE html>
<html>
<head>
<title>Pagination in Java Web Applications using jTable plugin</title>
<!-- Include one of jTable styles. -->
<link href="css/metro/blue/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. -->
<!-- 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() {
$('#StudentTableContainer').jtable({
title : 'Students List',
paging: true, //Enable paging
pageSize: 3, //Set page size (default: 10)
actions: {
listAction: 'Controller?action=list',
createAction:'Controller?action=create',
updateAction: 'Controller?action=update',
deleteAction: 'Controller?action=delete'
},
fields : {
studentId : {
title : 'Student Id',
sort :true,
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');
});
</script>
</head>
<body>
<div style="text-align: center;">
<h4>Pagination in Java Web Applications jTable</h4>
<div id="StudentTableContainer"></div>
</div>
</body>
</html>
Changes from the server’s perspective: Servlet
If paging is enabled in jsp then jTable sends two query string parameters to the server on listAction AJAX call:
• jtStartIndex: Start index of records for current page.
• jtPageSize: Count of maximum expected records.
And it expects additional information from server:
• TotalRecordCount: Total count of records.
In our previous example the url specified in the ‘listAction‘ option has business logic to fetch all records from database. Now in order to handle pagination this ‘listAction’ option should return only the part of resultset for each page, So handle this there are two changes that has to be done in the server side .
1. In order to return only a subset of records according to the page offset (jtStartIndex and jtPageSize), sql query used in CRUDDao should be modified with query below,
In case of Oracle database:
“SELECT * from (Select M.*, Rownum R from STUDENT M) where r > ” + < jtStartIndex> +” and r <= "+< jtStartIndex + jtPageSize >;
In case of MySql database:
select * from STUDENT limit <jtStartIndex>,<jtPageSize>
Changes made in CRUDDao at getAllStudents function
public List<Student> getAllStudents(int startPageIndex, int recordsPerPage)
{
List<Student> students = new ArrayList<Student>();
int range = startPageIndex+recordsPerPage;
String query="SELECT * from
(Select M.*, Rownum R From STUDENT M) where
r > " + startPageIndex +" and r <= "+range;
System.out.println(query);
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;
}
2. As mentioned above, jTable need TotalRecordCount to be present in the json response, For which add the following function in CRUDDao which returns total Record Count value present in database.
public int getStudentCount()
{
int count=0;
try
{
Statement stmt = dbConnection.createStatement();
ResultSet rs = stmt.executeQuery("SELECT COUNT(*) AS COUNT FROM STUDENT");
while (rs.next())
{
count=rs.getInt("COUNT");
}
}
catch (SQLException e)
{
System.err.println(e.getMessage());
}
return count;
}
Changes made in Controller
Now open ‘CRUDContoller.java’ file and replace the logic inside the if loop -> ‘if(action.equals(“list”))’ with the below code,
if(action.equals("list"))
{
try
{
// Fetch Data from User Table
int startPageIndex = Integer.parseInt(request.getParameter("jtStartIndex"));
int recordsPerPage = Integer.parseInt(request.getParameter("jtPageSize"));
// Fetch Data from Student Table
studentList = dao.getAllStudents(startPageIndex, recordsPerPage);
// Get Total Record Count for Pagination
int userCount = dao.getStudentCount();
// Convert Java Object to Json
String jsonArray = gson.toJson(studentList);
// Return Json in the format required by jTable plugin
jsonArray = "{\"Result\":\"OK\",\"Records\":" + jsonArray
+ ",\"TotalRecordCount\":" + userCount + "}";
response.getWriter().print(jsonArray);
}
catch(Exception ex){
String error="{\"Result\":\"ERROR\",\"Message\":"+ex.getMessage()+"}";
response.getWriter().print(error);
}
}
Now on running the application, with the above changes, the final demo looks as shown below:
 |
|
Read More
Tab Style Login and Signup example using jQuery in Java web application
In this post, I am going to describe on how to design tab style login and registration panel using jquery ui tabs in java web applications. Here I’m using oracle database as a back end for registering and authenticating users.
Libraries required for the setup,
Now create a dynamic web project in eclipse and create two folders under WebContent and name it as ‘js’ and ‘css’. And add the downloaded jQuery javascript and css files to it. The final project structure will be as shown below
Now create a user table in Oracle database using the query below.
CREATE TABLE users (name VARCHAR (50), email VARCHAR (50), password VARCHAR (50), gender VARCHAR (50))
Jsp page
<!DOCTYPE html>
<html>
<head>
<title>Login and registration</title>
<link rel="stylesheet" href="css/jquery-ui.css">
<link rel="stylesheet" href="css/style.css">
<script src="js/jquery-1.11.1.js"></script>
<script src="js/jquery-ui.js"></script>
<script>
$(function() {
$( "#tabs" ).tabs();
});
</script>
</head>
<body>
<div class="wrapper">
<div class="container">
<div id="tabs">
<ul>
<li><a href="#login">Login</a></li>
<li><a href="#register">Sign up</a></li>
</ul>
<div id="login">
<%
if("Invalid Email or password".equals((String)session.getAttribute("error"))){ %>
<h6> Invalid Email or password. Please try again.</h6>
<%} %>
<form method="post" action="LoginController">
<label for="email">Email:</label>
<br/>
<input type="text" name="email" id="email"/>
<br/>
<label for="password">Password:</label>
<br/>
<input type="password" name="password" id="password" />
<br/>
<br/>
<input type="submit" value="Login">
</form>
</div>
<div id="register">
<form method="post" action="RegistrationController">
<label for="fullname">Name:</label><br/>
<input type="text" name="fullname" id="fullname" />
<br/>
<label for="email">Email:</label><br/>
<input type="text" name="email" id="email" />
<br/>
<label for="password">Password:</label><br/>
<input type="password" name="password" id="password" />
<br/>
<label for="gender">Gender:</label><br/>
<select name="gender" id="gender">
<option value="Male">Male</option>
<option value="Female">Female</option>
</select>
<br/>
<br/>
<input type="submit" value="Register">
</form>
</div>
</div>
</div>
</div>
</body>
</html>
User defined CSS
body {
background-color: #e6e6e6;
font-family: Helvetica;
font-size: 17px;
color: #666;
margin: 0px;
padding: 0px;
}
.wrapper {
width: 1024px;
height: 650px;
margin: 0 auto;
background: white;
margin: 0 auto;
}
.container {
min-height: 400px;
border-top: 1px solid gray;
padding: 50px;
}
Recommended reading :
Controller
File: LoginController.java
LoginController doPost method gets triggered on clicking Login button and its doGet Method gets triggered on clicking logout
package com.controller;
import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import com.dao.UserDAO;
public class LoginController extends HttpServlet {
private static final long serialVersionUID = 1L;
protected void doPost(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {
String error;
String email = request.getParameter("email");
String password = request.getParameter("password");
HttpSession session = request.getSession();
UserDAO userDAO = new UserDAO();
String userName = userDAO.verifyUser(email, password);
if (userName == null) {
error = "Invalid Email or password";
session.setAttribute("error", error);
response.sendRedirect("index.jsp");
} else {
session.setAttribute("user", userName);
response.sendRedirect("welcome.jsp");
}
}
@Override
protected void doGet(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {
if ("logout".equalsIgnoreCase(request.getParameter("param"))) {
HttpSession session = request.getSession();
session.removeAttribute("user");
session.invalidate();
response.sendRedirect("index.jsp");
}
}
}
SignupController
This controller gets triggred on clicking Register button under Signup tab
package com.controller;
import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.dao.UserDAO;
public class SignupController extends HttpServlet {
private static final long serialVersionUID = 1L;
protected void doPost(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {
String name = request.getParameter("fullname");
String email = request.getParameter("email");
String password = request.getParameter("password");
String gender = request.getParameter("gender");
UserDAO userDAO = new UserDAO();
int result = userDAO.createUser(name, email, password, gender);
if (result == 1) {
response.sendRedirect("success.jsp");
}
}
}
Also read :
UserDAO
Create a class that performs registration and authentication operation in database
package com.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import com.jdbc.DBUtility;
public class UserDAO {
private Connection dbConnection;
private PreparedStatement pStmt;
private String SQL_SELECT = "SELECT name FROM users WHERE email = ? AND password = ?";
private String SQL_INSERT = "INSERT INTO users (name,email,password,gender) VALUES (?,?,?,?)";
public UserDAO() {
dbConnection = DBUtility.getConnection();
}
public String verifyUser(String email, String password) {
String userName = null;
try {
pStmt = dbConnection.prepareStatement(SQL_SELECT);
pStmt.setString(1, email);
pStmt.setString(2, password);
ResultSet rs = pStmt.executeQuery();
while (rs.next()) {
userName = rs.getString("name");
}
} catch (Exception e) {
System.err.println(e.getMessage());
}
return userName;
}
public int createUser(String name, String email, String password,
String gender) {
int result = 0;
try {
pStmt = dbConnection.prepareStatement(SQL_INSERT);
pStmt.setString(1, name);
pStmt.setString(2, email);
pStmt.setString(3, password);
pStmt.setString(4, gender);
result = pStmt.executeUpdate();
} catch (Exception e) {
System.err.println(e.getMessage());
}
return result;
}
}
DBUtility
Next let us create a utility class to handle connections to database.
package com.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
public class DBUtility {
private static Connection connection = null;
public static Connection getConnection() {
if (connection != null)
return connection;
else {
// database URL
String dbUrl = "jdbc:oracle:thin:@localhost:1521:XE";
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;
}
}
}
In addition to above files there are 2 other jsp pages,
1. welcome.jsp page which get displayed when the user logged into the application.
2. Success.jsp page which gets displayed once user registration is successful.
I have not showcased up these files here, you may download the files to see complete example.
Note: Since this article is intended to demonstrate the usage of tabs feature in jquery, so I have not included any validation parts in it.
Demo
On running the application
On clicking Signup tab
|
|
Read More
Autocomplete in java web application using Jquery and JSON
This article will describe how to implement jQuery Autocomplete in java web application. jQuery Autcomplete is part of the jQuery UI library which allows converting a normal textbox into an autocompleter textbox by providing a data source for the autocompleter values.
Here when user types a character in text box ,jQuery will fire an ajax request using autocomplete plugin to the controller, this controller(Servlet) in turn call the dao class which connects to the database and returns the required data back as an array list. After getting the data we convert this list to json format and return it back to the success function of our ajax call.
Library
gson-2.2.2.jar
ojdbc14.jar
servlet-api.jar
jquery-1.10.2.js
jquery-ui.js
jquery-ui.css
Project Structure
Jsp page
Now create a jsp page with Autocompleter feature and make sure that you referred the jQuery core and jQueryUI libraries.
<!DOCTYPE html> <html> <head> <meta charset="ISO-8859-1"> <title>Autocomplete in java web application using Jquery and JSON</title> <script src="//code.jquery.com/jquery-1.10.2.js"></script> <script src="//code.jquery.com/ui/1.10.4/jquery-ui.js"></script> <script src="autocompleter.js"></script> <link rel="stylesheet" href="//code.jquery.com/ui/1.10.4/themes/smoothness/jquery-ui.css"> <!-- User defied css --> <link rel="stylesheet" href="style.css"> </head> <body> <div class="header"> <h3>Autocomplete in java web application using Jquery and JSON</h3> </div> <br /> <br /> <div class="search-container"> <div class="ui-widget"> <input type="text" id="search" name="search" class="search" /> </div> </div> </body> </html>
Js file
Here we get data from database via ajax and apply autocompleter
$(document).ready(function() {
$(function() {
$("#search").autocomplete({
source : function(request, response) {
$.ajax({
url : "SearchController",
type : "GET",
data : {
term : request.term
},
dataType : "json",
success : function(data) {
response(data);
}
});
}
});
});
});
When a user types a character in text box ,jQuery will fire an ajax request to the controller, in this case controller is SearchController as mentioned in the above js file.
Controller
Creating The Controller To Handle Ajax Calls
package com.servlet;
import java.io.IOException;
import java.util.ArrayList;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.dao.DataDao;
import com.google.gson.Gson;
public class Controller extends HttpServlet {
private static final long serialVersionUID = 1L;
protected void doGet(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {
response.setContentType("application/json");
try {
String term = request.getParameter("term");
System.out.println("Data from ajax call " + term);
DataDao dataDao = new DataDao();
ArrayList<String> list = dataDao.getFrameWork(term);
String searchList = new Gson().toJson(list);
response.getWriter().write(searchList);
} catch (Exception e) {
System.err.println(e.getMessage());
}
}
}
This servlet will call the business class which in turn creates the necessary connection and returns the data back as an array list to the controller. After getting the data we convert it to json format and return it back to the success function of our ajax call.
Business class
Creating Methods To Get Data From Database
package com.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
public class DataDao {
private Connection connection;
public DataDao() throws Exception {
connection = DBUtility.getConnection();
}
public ArrayList<String> getFrameWork(String frameWork) {
ArrayList<String> list = new ArrayList<String>();
PreparedStatement ps = null;
String data;
try {
ps = connection.prepareStatement("SELECT * FROM JAVA_FRAMEWORK WHERE FRAMEWORK LIKE ?");
ps.setString(1, frameWork + "%");
ResultSet rs = ps.executeQuery();
while (rs.next()) {
data = rs.getString("FRAMEWORK");
list.add(data);
}
} catch (Exception e) {
System.out.println(e.getMessage());
}
return list;
}
}
Data Access object
Connecting To Database Using JDBC
package com.dao;
import java.sql.Connection;
import java.sql.DriverManager;
public class DBUtility {
private static Connection connection = null;
public static Connection getConnection() throws Exception {
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;
Class.forName("oracle.jdbc.driver.OracleDriver");
// set the url, username and password for the databse
connection = DriverManager.getConnection(dbUrl, "system", "admin");
return connection;
}
}
}
web.xml
Make sure you have done servlet mapping properly in web.xml file. An example of this is given below,
<welcome-file-list> <welcome-file>index.jsp</welcome-file> </welcome-file-list> <servlet> <servlet-name>SearchController</servlet-name> <servlet-class>com.servlet.Controller</servlet-class> </servlet> <servlet-mapping> <servlet-name>SearchController</servlet-name> <url-pattern>/SearchController</url-pattern> </servlet-mapping>
Demo
|
|
Read More
Ajax based Gridview in Java Web Applications using jQuery DataTable plugin
In this post, I am going to explain on how to use DataTable plugin to display data in Gridview format in Java web application.
DataTable is a jQuery plugin which adds a lot of functionality to plain HTML tables, such as filtering, paging sorting, changing page length, server side processing etc.
Library required
In this example instead of using database, I am going to retrieve values from a csv file and display it in html table. For this, I am going to use OpenCSV library which simplifies the work of parsing CSV files. Here the Data table will load the data by making an Ajax call.
Note:
• Refer the article on how to Read / Write CSV file in Java using Opencsv library/ .
Now create a dynamic web project in eclipse and create two folders named ‘js’ and ‘css’ under WebContent, and add the following javascript files from DataTable to the ‘js’ folder
• jquery.dataTables.js
• jquery.js
Add the following css files from DataTable & jQuery ui to ‘css’ folder.
• demo_page.css
• demo_table_jui.css
• jquery-ui-x.x.x.css
Download the csv file from which the data is to be read from here and place it under src folder. This css files contains four columns of csv file – company, country, revenue, and year.
Project Structure
Model class
Create a model class that gets and sets the data from the four columns (company, country, revenue, and year) of the csv file.
package com.model;
public class RevenueReport {
public RevenueReport(String company, String country, String year,
String revenue) {
this.company = company;
this.country = country;
this.year = year;
this.revenue = revenue;
}
private String company;
private String country;
private String year;
private String revenue;
public String getCountry() {
return country;
}
public String getRevenue() {
return revenue;
}
public String getCompany() {
return company;
}
public String getYear() {
return year;
}
public void setCountry(String country) {
this.country = country;
}
public void setRevenue(String revenue) {
this.revenue = revenue;
}
public void setCompany(String company) {
this.company = company;
}
public void setYear(String year) {
this.year = year;
}
}
Recommended reading:
- AJAX implementation in Java web application using JQuery
- Dynamic Dependent Select Box in JSP & Servlet using JQuery and JSON via Ajax
- Autocomplete in java web application using Jquery and JSON
Business class
Create a Business Service class that would fetch data from the csv file using model class.
package com.service;
import java.io.*;
import java.io.InputStreamReader;
import java.util.LinkedList;
import java.util.List;
import au.com.bytecode.opencsv.CSVReader;
import com.model.RevenueReport;
public class BusinessService {
public static List<RevenueReport> getCompanyList() {
List<RevenueReport> listOfCompany = new LinkedList<RevenueReport>();
String fileName = "Company_Revenue.csv";
InputStream is = Thread.currentThread().getContextClassLoader()
.getResourceAsStream(fileName);
BufferedReader br = new BufferedReader(new InputStreamReader(is));
try {
CSVReader reader = new CSVReader(br);
String[] row = null;
while ((row = reader.readNext()) != null)
{
listOfCompany.add(new RevenueReport(row[0], row[1], row[2], row[3]));
}
reader.close();
} catch (IOException e) {
System.err.println(e.getMessage());
}
return listOfCompany;
}
}
Jsp
Now create the jsp file to display the data fetched from csv file in html table and enhance the table features using DataTable plugin.
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Gridview in Servlet using jQuery DataTable plugin</title>
<link href="css/demo_table_jui.css" rel="stylesheet" />
<link href="css/jquery-ui.css" rel="stylesheet" />
<link href="css/demo_page.css" rel="stylesheet" />
<script src="js/jquery.js"></script>
<script src="js/jquery.dataTables.js"></script>
<script>
// Ajax call to Servlet to display data via DataTables
$(document).ready(function() {
$(".jqueryDataTable").dataTable({
"sPaginationType" : "full_numbers",
"bProcessing" : false,
"bServerSide" : false,
"sAjaxSource" : "displayData",
"bJQueryUI" : true,
"aoColumns" : [
{ "mData": "company" },
{ "mData": "country" },
{ "mData": "year" },
{ "mData": "revenue" }
]
} );
} );
</script>
</head>
<body id="dt_example">
<div id="container">
<h1>Ajax based Gridview using jQuery DataTable plugin</h1>
<div id="demo_jui">
<table class="display jqueryDataTable">
<thead>
<tr>
<th>Company</th>
<th>Country</th>
<th>Year</th>
<th>Revenue</th>
</tr>
</thead>
<tbody>
</tbody>
</table>
</div>
</div>
</body>
</html>
DataTable Parameters class
In reply to each request for information that DataTables makes to the server, it expects to get a well formed JSON object with some parameters.So Create a DataTable Parameters class with all those required parameters
package com.dataTable;
import java.util.List;
import com.model.RevenueReport;
public class DataTableParameters {
// Data table plugin parameter
int iTotalRecords;
int iTotalDisplayRecords;
String sEcho;
String sColumns;
List<RevenueReport> aaData;
public int getiTotalRecords() {
return iTotalRecords;
}
public void setiTotalRecords(int iTotalRecords) {
this.iTotalRecords = iTotalRecords;
}
public int getiTotalDisplayRecords() {
return iTotalDisplayRecords;
}
public void setiTotalDisplayRecords(int iTotalDisplayRecords) {
this.iTotalDisplayRecords = iTotalDisplayRecords;
}
public String getsEcho() {
return sEcho;
}
public void setsEcho(String sEcho) {
this.sEcho = sEcho;
}
public String getsColumns() {
return sColumns;
}
public void setsColumns(String sColumns) {
this.sColumns = sColumns;
}
public List<RevenueReport> getAaData() {
return aaData;
}
public void setAaData(List<RevenueReport> aaData) {
this.aaData = aaData;
}
}
Servlet implementation
package com.servlet;
import java.io.IOException;
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.GsonBuilder;
import com.dataTable.DataTableParameters;
import com.model.RevenueReport;
import com.service.BusinessService;
public class DataTableServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
protected void doGet(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {
response.setContentType("application/json");
// Call business service class to get list of company
List<RevenueReport> listOfCompany = BusinessService.getCompanyList();
DataTableParameters dataTableParam = new DataTableParameters();
//Set the list fetched in aaData
dataTableParam.setAaData(listOfCompany);
Gson gson = new GsonBuilder().setPrettyPrinting().create();
//Convert Java Object to Json
String json = gson.toJson(dataTableParam);
response.getWriter().print(json);
}
protected void doPost(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {
doGet(request, response);
}
}
web.xml
Make sure you have done servlet mapping properly in web.xml file. An example of this is given below,
<web-app> <servlet> <display-name>displayData</display-name> <servlet-name>displayData</servlet-name> <servlet-class>com.servlet.DataTableServlet</servlet-class> </servlet> <servlet-mapping> <servlet-name>displayData</servlet-name> <url-pattern>/displayData</url-pattern> </servlet-mapping> <welcome-file-list> <welcome-file>index.jsp</welcome-file> </welcome-file-list> </web-app>
Demo
|
|
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 pagination in Java web application using jTable 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.
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
<html> <head> <title>CRUD operations using jTable in J2EE</title> <!-- jTable Metro styles. --> <link href="css/metro/blue/jtable.css" rel="stylesheet" type="text/css" /> <link href="css/jquery-ui-1.10.3.custom.css" rel="stylesheet" type="text/css" /> <!-- 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> <!-- User Defined Jtable js file --> <script src="js/userDefieneJTableJs.js" type="text/javascript"></script> </head> <body> <div style="text-align: center;"> <h4>AJAX based CRUD operations using jTable in J2ee</h4> <div id="StudentTableContainer"></div> </div> </body> </html>
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”, hence I’m not going to explain it again.
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<Student> getAllStudents() {
List<Student> students = new ArrayList<Student>();
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(studentList);
Now let us look into the different response created for CRUD operations
Reading
Method to jTable to get a list of records:
if (action.equals("list")) {
try {
// Fetch Data from Student Table
studentList = dao.getAllStudents();
// Convert Java Object to Json
String jsonArray = gson.toJson(studentList);
// Return Json in the format required by jTable plugin
jsonArray = "{\"Result\":\"OK\",\"Records\":"+ jsonArray + "}";
response.getWriter().print(jsonArray);
} catch (Exception e) {
String error = "{\"Result\":\"ERROR\",\"Message\":" + e.getMessage()+ "}";
response.getWriter().print(error);
System.err.println(e.getMessage());
}
}
catch (Exception e) {
String error = "{\"Result\":\"ERROR\",\"Message\":"
+ "Exception on listing records }";
response.getWriter().print(error);
System.err.println(e.getMessage());
}
}
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
Creating a record is optional. If you allow user to create a record, you must supply an action to jTable to create a new record. This method must return the newly created object in JSON format, which is done via gson library. A sample return value for createAction is shown below
{“Result”:”OK”,”Record”:{
“studentId”: 9,
“name”: “Lahir nisha”,
“department”: “CSE”,
“emailId”: “[email protected]”
}}
if (action.equals("create")) {
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);
}
try {
// Create new record
dao.addStudent(student);
// Convert Java Object to Json
String json = gson.toJson(student);
// Return Json in the format required by jTable plugin
String jsonData = "{\"Result\":\"OK\",\"Record\":"+ json + "}";
response.getWriter().print(jsonData);
} catch (Exception e) {
String error = "{\"Result\":\"ERROR\",\"Message\":" + e.getMessage()+ "}";
response.getWriter().print(error);
}
}
Updating
Update a record is optional. If you allow user to edit (update) a record, then you must supply an action to jTable to update a record, here if update option is successful, then, Result property must be “OK”. If error then Result property must be “ERROR”.
if (action.equals("update")) {
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);
}
try {
// Update existing record
dao.updateStudent(student);
// Convert Java Object to Json
String json = gson.toJson(student);
String jsonData = "{\"Result\":\"OK\",\"Record\":" + json + "}";
response.getWriter().print(jsonData);
} catch (Exception e) {
String error = "{\"Result\":\"ERROR\",\"Message\":" + e.getMessage()+ "}";
response.getWriter().print(error);
}
}
Deleting
Similar to update 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);
String jsonData = "{\"Result\":\"OK\"}";
response.getWriter().print(jsonData);
}
} catch (Exception e) {
String error = "{\"Result\":\"ERROR\",\"Message\":" + e.getMessage()+ "}";
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
<welcome-file-list> <welcome-file>index.jsp</welcome-file> </welcome-file-list> <servlet> <servlet-name>Controller</servlet-name> <servlet-class>com.servlet.Controller</servlet-class> </servlet> <servlet-mapping> <servlet-name>Controller</servlet-name> <url-pattern>/Controller</url-pattern> </servlet-mapping>
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
http://www.codeproject.com/Articles/277576/AJAX-based-CRUD-tables-using-ASP-NET-MVC-and-jTa
Dynamic Dependent Select Box in JSP & Servlet using JQuery and JSON via Ajax
In my previous post, I explained about making AJAX calls from JSP page to servlet and updating a part of the JSP page with the response of simple java object from the Servlet. In this post we shall make the servlet to return complex Java Objects such as lists, maps, etc with the help of JSON and JQuery.
Library required
Google’s gson
Steps done to set up our Servlet for JSON
From the browser perspective: jQuery
jQuery allows you to issue an ajax request via get or post method and expects a JSON object as a response, as described in my previous post the first and second of these method are the url and a key-value pair and the third argument of get method is a function that defines what is to be done with the response that is got back from the Servlet.
Recommended reading:
Jsp Page
Now, let us create a JSP page with two drop down lists, one contains values for sports and the other that is going to be populated with values for players based on the value selected in the first drop down list. This is done without page refresh, by making AJAX calls to the Servlet on first drop down list change event.
<html>
<head>
<title>AJAX in Servlet using JQuery and JSON</title>
<script src="js/jquery-1.11.1.js" type="text/javascript"></script>
<script>
$(document).ready(function() {
$('#sports').change(function(event) {
var sports = $("select#sports").val();
$.get('JsonServlet', {
sportsName : sports
}, function(jsonResponse) {
var select = $('#player');
select.find('option').remove();
$.each(jsonResponse, function(key, value) {
$('<option>').val(key).text(value).appendTo(select);
});
});
});
});
</script>
</head>
<body>
<h3>AJAX in Servlet using JQuery and JSON</h3>
Select Favorite Sports:
<select id="sports">
<option>Select Sports</option>
<option value="Football">Football</option>
<option value="Cricket">Cricket</option>
</select>
<br /> <br />
Select Favorite Player:
<select id="player">
<option>Select Player</option>
</select>
</body>
</html>
Also read:
- Gridview in Java Web Applications using jQuery DataTable plugin
- CRUD Operations in Java Web Applications using jTable jQuery plugin via Ajax
- Autocomplete in Java web application
From the server’s perspective: Servlet
In Servlet, we are going to use a GSON library to convert Java objects (lists, maps etc) to JSON strings that will be parsed by JQuery in the JSP page and will be displayed on the web page.
Servlet
package com.action;
import java.io.IOException;
import java.util.*;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.google.gson.Gson;
public class JsonServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
protected void doGet(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {
String sportsName = request.getParameter("sportsName");
Map<String, String> playerMap = new LinkedHashMap<String, String>();
String json = null;
if (sportsName.equals("Football")) {
playerMap.put("1", "Lionel Messi");
playerMap.put("2", "Cristiano Ronaldo");
playerMap.put("3", "David Beckham");
playerMap.put("4", "Diego Maradona");
} else if (sportsName.equals("Cricket")) {
playerMap.put("1", "Sourav Ganguly");
playerMap.put("2", "Sachin Tendulkar");
playerMap.put("3", "Lance Klusener");
playerMap.put("4", "Michael Bevan");
} else if (sportsName.equals("Select Sports")) {
playerMap.put("1", "Select Player");
}
json = new Gson().toJson(playerMap);
response.setContentType("application/json");
response.getWriter().write(json);
}
}
In the above code, we create a map(playerMap) and populates its value based on the sportsName parameter passed to the Servlet in the AJAX call made by the JQuery’s get() method. This map object is converted to json strings and the response is sent back to the JSP page.
Web.xml
Servlet mapping should be done in web.xml as shown below
<welcome-file-list> <welcome-file>index.jsp</welcome-file> </welcome-file-list> <servlet> <servlet-name>JsonServlet</servlet-name> <servlet-class>com.action.JsonServlet</servlet-class> </servlet> <servlet-mapping> <servlet-name>JsonServlet</servlet-name> <url-pattern>/JsonServlet/*</url-pattern> </servlet-mapping>
Demo
On selecting ‘Football’ in the first dropdown list
On selecting ‘Cricket’ in the first dropdown list
|
|
Read More