In this help topic you will learn how to bind a jqxGrid to a MySQL database using JSP (JavaServer Pages).
Important: before proceeding, please make sure you have followed the instructions of the tutorial Configure MySQL, Eclipse and Tomcat for Use with jQWidgets.
To populate the grid, we need a JSP file that connects to the Northwind database and retieves data from it.
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:
<% // (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();%>
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>In this example is demonstrated how to populate a jqxGrid with data retrieved by a JSP page.</title> <link type="text/css" rel="Stylesheet" href="css/jqx.base.css" /> <script type="text/javascript" src="js/jquery.js"></script> <script type="text/javascript" src="js/jqxcore.js"></script> <script type="text/javascript" src="js/jqxdata.js"></script> <script type="text/javascript" src="js/jqxbuttons.js"></script> <script type="text/javascript" src="js/jqxscrollbar.js"></script> <script type="text/javascript" src="js/jqxmenu.js"></script> <script type="text/javascript" src="js/jqxcheckbox.js"></script> <script type="text/javascript" src="js/jqxlistbox.js"></script> <script type="text/javascript" src="js/jqxdropdownlist.js"></script> <script type="text/javascript" src="js/jqxgrid.js"></script> <script type="text/javascript" src="js/jqxgrid.selection.js"></script> <script type="text/javascript"> $(document).ready(function () { var source = { datatype: "json", datafields: [{ name: 'FirstName', type: 'string' }, { name: 'LastName', type: 'string' }, { name: 'Title', type: 'string' }, { name: 'BirthDate', type: 'date' }], id: 'EmployeeID', url: 'select-data.jsp', async: true }; 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' }] }); }); </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"></div></body></html>
Through jqxDataAdapter, the grid is populated by the data retrieved from the database
by select-data.jsp
. 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.