Documentation

Bind jqxTree to MySQL Database using JSP

In this help topic you will learn how to bind a jqxTree 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.

1. Connect to the Database and Retrieve the Tree Data

To populate the tree, 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-tree-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:

  1. Makes a database connection.
  2. Selects the necessary data from the database in a ResultSet.
  3. Converts the ResultSet to a JSON array.
  4. Prints (returns) the JSON array.
<%
	// (A) database connection
	// "jdbc:mysql://localhost:3306/northwind" - the database url of the form jdbc:subprotocol:subname
	// "root" - the database user on whose behalf the connection is being made
	// "abcd" - the user's password
	Connection dbConnection = DriverManager.getConnection(
			"jdbc:mysql://localhost:3306/northwind", "root", "abcd");
	// (B) retrieve necessary records from database
	Statement getFromDb = dbConnection.createStatement();
	ResultSet employees = getFromDb
			.executeQuery("SELECT EmployeeID, FirstName, LastName, Title, ReportsTo 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("ReportsTo",
				new JsonPrimitive(employees.getString("ReportsTo")));
		recordsArray.add(currentRecord);
	}
	// (D)
	out.print(recordsArray);
	out.flush();
%>

2. Create a Page with a jqxTree

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 jqxTree with data from a MySQL database.</title>
    <link rel="stylesheet" href="css/jqx.base.css" type="text/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/jqxpanel.js"></script>
    <script type="text/javascript" src="js/jqxtree.js"></script>
    <script type="text/javascript" src="js/jqxradiobutton.js"></script>
    <script type="text/javascript" src="js/jqxcheckbox.js"></script>
    <script type="text/javascript">
        $(document).ready(function () {
            var source = {
                datatype: "json",
                datafields: [{
                    name: 'EmployeeID'
                }, {
                    name: 'FirstName'
                }, {
                    name: 'Title'
                }, {
                    name: 'ReportsTo'
                }],
                id: 'EmployeeID',
                url: 'select-tree-data.jsp',
                async: false
            };
            var dataAdapter = new $.jqx.dataAdapter(source);
            dataAdapter.dataBind();
            var records = dataAdapter.getRecordsHierarchy('EmployeeID',
                'ReportsTo', 'items', [{
                    name: 'EmployeeID',
                    map: 'id'
                }, {
                    name: 'FirstName',
                    map: 'label'
                }, {
                    name: 'Title',
                    map: 'value'
                }, {
                    name: 'ReportsTo',
                    map: 'parentid'
                }]);
            $('#jqxTree').jqxTree({
                source: records,
                width: '200px',
                height: '300px',
                submitCheckedItems: true,
                checkboxes: true
            });
            $('#jqxTree').jqxTree('expandAll');
        });
    </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 class='default'>
    <div id='jqxTree'></div>
</body>
</html>

Through jqxDataAdapter, the tree is populated by the data retrieved from the database by select-tree-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.