Documentation

jqxGrid CRUD using JSP

In this help topic you will learn how to use jqxGrid in a CRUD (Create, Read, Update, Delete) application scenario and send INSERT, UPDATE and DELETE commands to the server to update a MySQL database through JSP.

Important: before proceeding, please make sure you have followed the instructions of the tutorial Configure MySQL, Eclipse and Tomcat for Use with jQWidgets.

1. Connect to the Database and Retrieve the Grid Data

To populate the grid, we need a JSP file that connects to the Northwind database and retieves data from it (Read functionality).

Create a new JSP by right-clicking the project's WebContent folder, then choosing New → JSP File. Name the file select-data.jsp.

Import the necessary classes in the beginning of the JSP:

<%@ page import="java.sql.*"%>
<%@ page import="com.google.gson.*"%>

Finally, add a scriptlet to the JSP that does the following:

Makes a database connection.
Selects the necessary data from the database in a ResultSet.
Converts the ResultSet to a JSON array.
Prints (returns) the JSON array.

<%
	// (A) database connection
	// "jdbc:mysql://localhost:3306/northwind" - the database url of the form jdbc:subprotocol:subname
	// "dbusername" - the database user on whose behalf the connection is being made
	// "dbpassword" - the user's password
	Connection dbConnection = DriverManager.getConnection(
			"jdbc:mysql://localhost:3306/northwind", "dbusername", "dbpassword");
	// (B) retrieve necessary records from database
	Statement getFromDb = dbConnection.createStatement();
	ResultSet employees = getFromDb
			.executeQuery("SELECT EmployeeID, FirstName, LastName, Title, BirthDate FROM employees");
	// (C) format returned ResultSet as a JSON array
	JsonArray recordsArray = new JsonArray();
	while (employees.next()) {
		JsonObject currentRecord = new JsonObject();
		currentRecord.add("EmployeeID",
				new JsonPrimitive(employees.getString("EmployeeId")));
		currentRecord.add("FirstName",
				new JsonPrimitive(employees.getString("FirstName")));
		currentRecord.add("LastName",
				new JsonPrimitive(employees.getString("LastName")));
		currentRecord.add("Title",
				new JsonPrimitive(employees.getString("Title")));
		currentRecord.add("BirthDate",
				new JsonPrimitive(employees.getString("BirthDate")));
		recordsArray.add(currentRecord);
	}
	// (D)
	out.print(recordsArray);
	out.flush();
%>

2. Create a Page with a jqxGrid

Create a new HTML page by right-clicking the project's WebContent folder, then choosing New → HTML File. Here is the code of the page in our example:

<!DOCTYPE html>
<html lang="en">
<head>
    <title>CRUD operations with jqxGrid using JSP and MySQL.</title>
    <link type="text/css" rel="Stylesheet" href="jqwidgets/styles/jqx.base.css" />
    <script type="text/javascript" src="scripts/jquery-1.11.1.min.js"></script>
    <script type="text/javascript" src="jqwidgets/jqxcore.js"></script>
    <script type="text/javascript" src="jqwidgets/jqxdata.js"></script>
    <script type="text/javascript" src="jqwidgets/jqxbuttons.js"></script>
    <script type="text/javascript" src="jqwidgets/jqxscrollbar.js"></script>
    <script type="text/javascript" src="jqwidgets/jqxmenu.js"></script>
    <script type="text/javascript" src="jqwidgets/jqxcheckbox.js"></script>
    <script type="text/javascript" src="jqwidgets/jqxlistbox.js"></script>
    <script type="text/javascript" src="jqwidgets/jqxdropdownlist.js"></script>
    <script type="text/javascript" src="jqwidgets/jqxgrid.js"></script>
    <script type="text/javascript" src="jqwidgets/jqxgrid.selection.js"></script>
    <script type="text/javascript" src="scripts/demos.js"></script>
    <script type="text/javascript">
        $(document).ready(function() {
            var firstNames = ["Nancy", "Andrew", "Janet", "Margaret", "Steven", "Michael", "Robert", "Laura", "Anne"];
            var lastNames = ["Davolio", "Fuller", "Leverling", "Peacock", "Buchanan", "Suyama", "King", "Callahan", "Dodsworth"];
            var titles = ["Sales Representative", "Vice President, Sales", "Sales Representative", "Sales Representative", "Sales Manager", "Sales Representative", "Sales Representative", "Inside Sales Coordinator", "Sales Representative"];
            var generaterow = function() {
                var row = {};
                var firtnameindex = Math.floor(Math.random() * firstNames.length);
                var lastnameindex = Math.floor(Math.random() * lastNames.length);
                var k = firtnameindex;
                row["FirstName"] = firstNames[firtnameindex];
                row["LastName"] = lastNames[lastnameindex];
                row["Title"] = titles[k];
                var startDate = new Date(1950, 0, 1);
                row["BirthDate"] = new Date(startDate.getTime() + Math.random() * (new Date(1999, 0, 1).getTime() - startDate.getTime()));
                return row;
            }
            var source = {
                datatype: "json",
                datafields: [{
                    name: 'FirstName',
                    type: 'string'
                }, {
                    name: 'LastName',
                    type: 'string'
                }, {
                    name: 'Title',
                    type: 'string'
                }, {
                    name: 'BirthDate',
                    type: 'date'
                }],
                id: 'EmployeeId',
                url: 'jsp/select-data.jsp',
                async: true,
                addrow: function(rowid, rowdata, position, commit) {
                    var formattedBirthDate = $.jqx.formatDate(rowdata.BirthDate, 'yyyy-MM-dd');
                    var data = $.extend({}, rowdata);
                    data.BirthDate = formattedBirthDate;
                    $.ajax({
                        url: 'jsp/addrow.jsp',
                        data: data,
                        type: 'POST',
                        success: function(data, textStatus, jqXHR) {
							var newRowId = data != undefined ? parseInt(data) : 0
							if (!newRowId)
								commit(false);
							else
								commit(true, newRowId);							
                        },
                        error: function(jqXHR, textStatus, errorThrown) {
                            commit(false);
                        }
                    });
                },
                deleterow: function(rowid, commit) {
                    $.ajax({
                        url: 'jsp/deleterow.jsp',
                        data: {
                            row: rowid
                        },
                        type: 'POST',
                        success: function(data, textStatus, jqXHR) {
                            commit(true);
                        },
                        error: function(jqXHR, textStatus, errorThrown) {
                            commit(false);
                        }
                    });
                },
                updaterow: function(rowid, newdata, commit) {
                    var formattedBirthDate = $.jqx.formatDate(newdata.BirthDate, 'yyyy-MM-dd');
                    var data = $.extend({}, newdata);
                    data.BirthDate = formattedBirthDate;
                    data.id = rowid;
                    $.ajax({
                        url: 'jsp/updaterow.jsp',
                        data: data,
                        type: 'POST',
                        success: function(data, textStatus, jqXHR) {
                            commit(true);
                        },
                        error: function(jqXHR, textStatus, errorThrown) {
                            commit(false);
                        }
                    });
                }
            };
            var dataAdapter = new $.jqx.dataAdapter(source);
            $("#jqxgrid").jqxGrid({
                width: 550,
                autoheight: true,
                source: dataAdapter,
                columns: [{
                    text: 'First Name',
                    datafield: 'FirstName',
                    width: 100
                }, {
                    text: 'Last Name',
                    datafield: 'LastName',
                    width: 100
                }, {
                    text: 'Title',
                    datafield: 'Title',
                    width: 180
                }, {
                    text: 'Birth Date',
                    datafield: 'BirthDate',
                    cellsformat: 'd',
                    align: 'right',
                    cellsalign: 'right'
                }]
            });
            $("#addrowbutton, #deleterowbutton, #updaterowbutton").jqxButton({
                width: 150
            });
            $('#addrowbutton').click(function() {
                var datarow = generaterow();
                var commit = $("#jqxgrid").jqxGrid('addrow', null, datarow);
            });
            $('#deleterowbutton').click(function() {
                var selectedrowindex = $("#jqxgrid").jqxGrid('getselectedrowindex');
                var rowscount = $("#jqxgrid").jqxGrid('getdatainformation').rowscount;
                if (selectedrowindex >= 0 && selectedrowindex < rowscount) {
                    var id = $("#jqxgrid").jqxGrid('getrowid', selectedrowindex);
                    var commit = $("#jqxgrid").jqxGrid('deleterow', id);
                }
            });
            $('#updaterowbutton').click(
                function() {
                    var selectedrowindex = $("#jqxgrid").jqxGrid('getselectedrowindex');
                    var rowscount = $("#jqxgrid").jqxGrid('getdatainformation').rowscount;
                    if (selectedrowindex >= 0 && selectedrowindex < rowscount) {
                        var id = $("#jqxgrid").jqxGrid('getrowid', selectedrowindex);
                        var datarow = generaterow();
                        $("#jqxgrid").jqxGrid('updaterow', id, datarow);
                    }
                });
        });
    </script>
<script async src="https://www.googletagmanager.com/gtag/js?id=G-2FX5PV9DNT"></script><script>window.dataLayer = window.dataLayer || [];function gtag(){dataLayer.push(arguments);}gtag('js', new Date());gtag('config', 'G-2FX5PV9DNT');</script></head>
<body>
    <div id="jqxgrid" style="float: left;"></div>
    <div style="margin-left: 30px; float: left;">
        <div>
            <input id="addrowbutton" type="button" value="Add New Row" />
        </div>
        <div style="margin-top: 10px;">
            <input id="deleterowbutton" type="button" value="Delete Selected Row" />
        </div>
        <div style="margin-top: 10px;">
            <input id="updaterowbutton" type="button" value="Update Selected Row" />
        </div>
    </div>
</body>
</html>

Through jqxDataAdapter, the grid is populated by the data retrieved from the database by select-data.jsp.

When a new row is added, the source callback addrow is called, in which an Ajax call sends the new row's data to the file addrow.jsp (see Step 3).

When a row is deleted, the source callback deleterow is called, in which an Ajax call sends the deleted row's id to the file deleterow.jsp (see Step 4).

When a row is updated, the source callback updaterow is called, in which an Ajax call sends the updated row's data to the file updaterow.jsp (see Step 5).

3. `addrow.jsp`

Create a JSP file called addrow.jsp with the following content:

<%@ page import="java.lang.*"%>
<%@ page import="java.sql.*"%>
<%@ page import="com.google.gson.*"%>
<%
	String FirstName = request.getParameter("FirstName");
	String LastName = request.getParameter("LastName");
	String Title = request.getParameter("Title");
	String BirthDate = request.getParameter("BirthDate");
	if (FirstName == null || LastName == null || Title == null || BirthDate == null)
		return;
	//database connection
	// "jdbc:mysql://localhost:3306/northwind" - the database url of the form jdbc:subprotocol:subname
	Connection dbConnection = DriverManager.getConnection("jdbc:mysql://localhost:3306/northwind", "dbusername", "dbpassword");
	// insert record into the database
	String sql = "INSERT INTO employees (FirstName, LastName, Title, BirthDate) VALUES (?, ?, ?, ?)";
	PreparedStatement stmt = dbConnection.prepareStatement(sql);
	stmt.setString(1, FirstName);
	stmt.setString(2, LastName);
	stmt.setString(3, Title);
	stmt.setString(4, BirthDate);
			
	stmt.executeUpdate();
	
	Statement selectStmt = dbConnection.createStatement();
	ResultSet resultSet = selectStmt.executeQuery("SELECT LAST_INSERT_ID() as 'LastId'");
	resultSet.next();
	String lastInsertId = resultSet.getString("LastId");
	out.print(lastInsertId);
	out.flush();
%>

This JSP makes an INSERT query with the new row's data (Create functionality).

4. `deleterow.jsp`

Create a JSP file called deleterow.jsp with the following content:

<%@ page import="java.lang.*"%>
<%@ page import="java.sql.*"%>
<%@ page import="com.google.gson.*"%>
<%
	Integer rowToDelete = -1;
	if (null != request.getParameter("row"))
	{
		try
		{
			rowToDelete = Integer.parseInt(request.getParameter("row"));
		}
		catch (NumberFormatException nfe) {}
		{
		}		
	}
	
	//database connection
	// "jdbc:mysql://localhost:3306/northwind" - the database url of the form jdbc:subprotocol:subname
	Connection dbConnection = DriverManager.getConnection("jdbc:mysql://localhost:3306/northwind", "dbusername", "dbpassword");
	// delete specified record from the database
	String sql = "DELETE FROM employees WHERE EmployeeID = ?";
	PreparedStatement stmt = dbConnection.prepareStatement(sql);
	stmt.setInt(1, rowToDelete);
			
	stmt.executeUpdate();
	
%>

This JSP makes a DELETE query by the deleted row's id (Delete functionality).

3. `updaterow.jsp`

Create a JSP file called updaterow.jsp with the following content:

<%@ page import="java.lang.*"%>
<%@ page import="java.sql.*"%>
<%@ page import="com.google.gson.*"%>
<%
	Integer rowToUpdate = -1;
	if (null != request.getParameter("id"))
	{
		try
		{
			rowToUpdate = Integer.parseInt(request.getParameter("id"));
		}
		catch (NumberFormatException nfe) {}
		{
		}		
	}
	String FirstName = request.getParameter("FirstName");
	String LastName = request.getParameter("LastName");
	String Title = request.getParameter("Title");
	String BirthDate = request.getParameter("BirthDate");
	//database connection
	// "jdbc:mysql://localhost:3306/northwind" - the database url of the form jdbc:subprotocol:subname
	Connection dbConnection = DriverManager.getConnection("jdbc:mysql://localhost:3306/northwind", "dbusername", "dbpassword");
	// update record in the database					
	String sql = "UPDATE employees SET FirstName=?, LastName=?, Title=?, BirthDate=? WHERE EmployeeID=?";
	PreparedStatement stmt = dbConnection.prepareStatement(sql);
	stmt.setString(1, FirstName);
	stmt.setString(2, LastName);
	stmt.setString(3, Title);
	stmt.setString(4, BirthDate);
	stmt.setInt(5, rowToUpdate);
			
	stmt.executeUpdate();
%>

This JSP makes an UPDATE query with the updated row's data (Update functionality).

To run the page, right-click it and select Run As → Run on Server. In the window that appears, select Tomcat v8.0 Server at localhost and click Finish.