Last quarter we announced the beta release of our JSP wrappers for Kendo UI. These wrappers are JSP and while our demos are built on Spring MVC, you can use the wrappers with any JSP page.

They provide a strongly typed experience by adding in a special HTML tags that allow you to fully configure a Kendo UI widget while writing a minimum amount of JavaScript. The helps you to know that your widgets are configured correctly at design time, thusly saving you valuable development cycles.

In preparation for our next release, I have been creating some sample code for how to get started with the JSP wrappers by building an application that addresses many of the common enterprise development challenges. This includes things like hierarchical data, treeviews, grids, custom grid editor fields, validation and full CRUD operations.

In this post, we'll take a look at how you can get started with the JSP wrappers, and we'll additionally create a foundation for keeping our Java code clean and maintainable

Prerequistes

The first step is of course to download the beta of the JSP wrappers if you haven't already. Keep in mind that this beta will be a full release in March, which will be upon us before we know it.

Inside that download you will see two folders. One contains the Spring demos of all the widgets. I highly suggest that you get this up and running. There is a great walkthrough here on configuring your environment to load up the demos.

The other folder is called kendo-taglib and contains the Kendo UI JSP Wrappers.

Fire up a new project

You will need Java 1.7 and Tomcat 7 for the sample project in this article.

Let's create a new Dynamic Web Project. In Eclipse, select File/New/Dynamic Web Application.

Name it EmployeeDashboard

newdynamicwebapp

A look at the project structure

In this newly created application you will see several folders. Most we are not going to touch. The two that are important are Java Resources and WebContent.

You can think of it this way: Any HTML, CSS or JavaScript is going in the WebContent directory. Any Java classes that we create are going in the JavaResources folder.

Installing the wrappers in the project

There is a single .JAR file in the kendo-taglib directory. All you need to do is drag that file into the WebContent/WEB-INF/lib directory. Go ahead and select Copy Files when you are prompted.

You will also be needing the Kendo UI CSS files, JavaScript file and jQuery.

From your Kendo UI JSP download folder, drag the styles and js folders to the WebContent folder in your project. Remember to always select Copy files and folders.

Setting up our sample data

The database is just the Northwind database and I'm using SQLite as the database so that I can distribute it to you in a working form. In order to connect this database, we need a driver. You can download the SQLite driver here. You add it to your project the same way that you did the Kendo UI JSP wrappers. Just drag the JAR into the WebContent/WEB-INF/lib.

The database itself is provided as a file in the GitHub repo for the source code from this project. It's simply called Sample.db. I have put this in the WebContent/data.

Connecting and retrieving data

Expand the Java Resources folder. Right click the src folder and select new/Package. Name it repositories.

newpackage

Right click the repostories package you just created and select new/Class.

Name the class EmployeeRepository.

employeesrepository

This class is going to be where we do all our database manipulation. We will be needing a SQLConnection object.

Add SQL Connection Object

package repositories;

import java.sql.Connection;

public class EmployeeRepository {

  // a class level sql connection object 
  private Connection _conn = null;

}

 

To properly connect to the database, we'll need to create an instance of the SQLite driver in the constructor of this class. We need to know what it's physical path on the server is. That is information that we can obtain in our servlet (which we haven't written yet), so we can just pass it in as a string to the constructor and then initialize the driver.

Initialize Driver And Connection

try {

  // initialize the database driver
  Class.forName("org.sqlite.JDBC");

  // set the connection instance
  _conn = DriverManager.getConnection("jdbc:sqlite:" + path);

} catch (ClassNotFoundException | SQLException e) {
  e.printStackTrace();
}

 

Notice that I also initialized the SQL Connection there as well.

Modeling data

Before we actually read from the database, we need to be able to create a container of employee objects. We don't have an employee object yet. This is referred to as our model. The model is simply a class that represents an employee.

Right click the Java Resources/src folder and create a new package named models.

Right click the new models package and create a new class called Employee.

This class just needs to have the same properties that an employee in the database has. In the Employees table, there are quite a few fields. For the sake of this project, we only need...

  • EmployeeID
  • FirstName
  • LastName
  • ReportsTo (We will call this ManagerID in our model)

Employee Model Properties

package models;

public class Employee {

  private int EmployeeID;
  private int ManagerID;
  private String LastName;
  private String FirstName;

}

In order to be able to set these variables to values when we use this class, we need to create getters and setters. These getters and setters are simply methods that will get and set the value of a specific property. We could type all of this out, but it's easier to make Eclipse do this for us.

Select all the private variables. Right click them and select Source/Generate Getters And Setters

Screen Shot 2013-02-04 at 2.58.52 PM

generategetterssetters

I always change the insertion point drop down so that it puts them after the last field I declared (in this case it's FirstName).

Now your class should look like this:

Employee Model Class

package models;

public class Employee {

  private int EmployeeID;
  private int ManagerID;
  private String LastName;
  private String FirstName;

  public int getEmployeeID() {
    return EmployeeID;
  }
  public void setEmployeeID(int employeeID) {
    EmployeeID = employeeID;
  }
  public int getManagerID() {
    return ManagerID;
  }
  public void setManagerID(int managerID) {
    ManagerID = managerID;
  }
  public String getLastName() {
    return LastName;
  }
  public void setLastName(String lastName) {
    LastName = lastName;
  }
  public String getFirstName() {
    return FirstName;
  }
  public void setFirstName(String firstName) {
    FirstName = firstName;
  }

}

Now we're all set to get the data from the database.

Switch back to the EmployeeRepository class.

Add a method to listEmployeees which will return a collection of employee model objects. You will need to import several classes so pay attention to the import statements at the top.

EmployeeRepository.java

package repositories;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import models.Employee;

public class EmployeeRepository {

  private Connection _conn = null;

  public EmployeeRepository(String path) {

    // initialize the database driver
        try {
          Class.forName("org.sqlite.JDBC");

      // set the connection instance
      _conn = DriverManager.getConnection("jdbc:sqlite:" + path);

    } catch (ClassNotFoundException | SQLException e) {
      e.printStackTrace();
    }

  }

  public List<Employee> listEmployees (){

    // create a new empty list of employees to return as a result
    List<Employee> employees = new ArrayList<Employee>();

    try {

      // use prepared statements to prevent sql injection attacks
      PreparedStatement stmt = null;

      // the query to send to the database
      String query = "SELECT e.EmployeeID, e.FirstName, e.LastName, e.ReportsTo AS ManagerID "+
                 "From Employees e";

      // create the prepared statement with the connection
      stmt = _conn.prepareStatment(query);

      // execute the query into a result set
      ResultSet rs = stmt.executeQuery();

      // iterate through the result set
      while(rs.next()) {

        // create a new employee model object
        Employee employee = new Employee();

        // select fields out of the database and set them on the class
        employee.setEmployeeID(rs.getInt("EmployeeID"));
        employee.setFirstName(rs.getString("FirstName"));
        employee.setLastName(rs.getString("LastName"));
        employee.setManagerID(rs.getInt("ManagerID"));

        // add the class to the list
        employees.add(employee);
      }

      // close the result set and statement
      rs.close();
      stmt.close();
    }
    catch (SQLException e) {
      e.printStackTrace();
    }

    // return the result list
    return employees;

  }

}

 

All we did here was execute a simple SQL statement against the database, iterate through a ResultSet object and map the database fields to a new employee model object, which was then added to a collection which we will return from this method.

Note that if you are working on an application of larger scale in Java, many developers will choose some sort of ORM framework to keep SQL out of their Java code. For the sake of simplicity in explanation, only prepared statements are used here.

Delivering data over the web

Now that we are able to actually get the data out of the database, we need to be able to "expose" the data on the web. This will be done with a Servlet.

Java Servlets allow you to respond to HTTP requests made from the web and determine what is returned.

Right click the Java Resoures/src directory and create an api package.

Right click the api package and select new/Servlet. Name it Employees. Edit the URL mappings so that instead of /Employees it says /api/employees. Click Finish.

A new servlet is created with methods that will respond to a GET (doGet) and a POST (doPost).

If you recall, the constructor that we created for the EmployeeRepository takes in the path the the SQLite database. We can get it's path by looking at the getRealPath method that is on the servlet context. However, we can't access it until the init method. We need to just override it and new up the repository class there.

package api;

import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import repositories.EmployeeRepository;


/**
 * Servlet implementation class Employees
 */
@WebServlet(description = "A servlet to return data about employees from the database", urlPatterns = { "/api/employees" })
public class Employees extends HttpServlet {
  private static final long serialVersionUID = 1L;

  // employee repository class
  private EmployeeRepository _repository = null;

    /**
     * @see HttpServlet#HttpServlet()
     */
    public Employees() {
        super();
        // TODO Auto-generated constructor stub
    }

    public void init() throws ServletException {
      super.init();

      // create a new instance of the repository class. pass in the path to the data/sample.db
      // file which we can get by getting the servlet context, then calling 'getRealPath'
      _repository = new EmployeeRepository(this.getServletContext().getRealPath("data/sample.db"));
    }

  /**
   * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
   */
  protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    // TODO Auto-generated method stub
  }

  /**
   * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
   */
  protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    // TODO Auto-generated method stub
  }

}

 

Returning employee data as JSON

In order to be able to return our employees data as JSON, download the Google Gson JAR and drag it into WebContent/WEB-INFO/lib.

Now just get the list of Employees from the listEmployees method in the EmployeeRepository object and use the Gson library to turn it into JSON. We then write that result to the response stream and set our return content type to JSON.

package api;

import java.io.IOException;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.google.gson.Gson;
import repositories.EmployeeRepository;


/**
 * Servlet implementation class Employees
 */
@WebServlet(description = "A servlet to return data about employees from the database", urlPatterns = { "/api/employees" })
public class Employees extends HttpServlet {
  private static final long serialVersionUID = 1L;

  // employee repository class
  private EmployeeRepository _repository = null;
  private Gson _gson = null;

    /**
     * @see HttpServlet#HttpServlet()
     */
    public Employees() {
        super();

        // initialize the Gson library
        _gson = new Gson();
    }

    public void init() throws ServletException {
      super.init();

      // create a new instance of the repository class. pass in the path to the data/sample.db
      // file which we can get by getting the servlet context, then calling 'getRealPath'
      _repository = new EmployeeRepository(this.getServletContext().getRealPath("data/sample.db"));
    }

  /**
   * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
   */
  protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    try {

      // get the employees from the database
      List<models.Employee> employees = _repository.listEmployees();

      // set the content type we are sending back as JSON
      response.setContentType("application/json"); 

      // convert the list to json and write it to the response
      response.getWriter().print(_gson.toJson(employees));

    } catch (Exception e) {
      e.printStackTrace();
    }
  }

  /**
   * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
   */
  protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    // TODO Auto-generated method stub
  }

}

Since we changed the URL Mapping when we created this servlet, it will respond to requests made to server/api/employees.

If you browse to this url, you should see the employees from the database returned as JSON. In my case, this url is http://localhost:8080/EmployeeDashboard/api/employees.

employeesjson

Consuming JSON with the Kendo UI Wrappers

Now that we have our data exposed over the web, we can consume it with Kendo UI and AJAX.

Right click the WebContent folder and select new/JSP File. Name it index.jsp. Click Finish.

index

In order to use the Kendo UI Wrappers, we need to include the tag library at the top of the page, the Kendo UI CSS, jQuery and Kendo UI JavaScript files.

Once that is done, we can use the special kendo html tags that will construct widgets. We'll use a Kendo UI TreeView to display the employees.

<%@ page language="java" contentType="text/html; charset=US-ASCII"
    pageEncoding="US-ASCII"%>
<%@taglib prefix="kendo" uri="http://www.kendoui.com/jsp/tags"%>
<!DOCTYPE html>
<html>
<head>
  <meta http-equiv="Content-Type" content="text/html; charset=US-ASCII">
  <title>Insert title here</title>
  <!-- kendo css files –>
  <link href="styles/kendo.common.min.css" rel="stylesheet">
  <link href="styles/kendo.default.min.css" rel="stylesheet">
  <!-- jquery and kendo javascript. jquery MUST be first. –>
  <script src="js/jquery.min.js"></script>
  <script src="js/kendo.all.min.js"></script>
</head>
<body>
 <kendo:treeView name="employees" dataTextField="FirstName">
  <kendo:dataSource>
    <kendo:dataSource-transport read="api/employees"></kendo:dataSource-transport>
  </kendo:dataSource>
 </kendo:treeView>
</body>
</html>

 

The TreeView configuration requires us to set the DataSource. The DataSource has a Transport configuration that will define endpoints. We just need to read the URL that returns the JSON employee data. We also set the textField so that the TreeView knows which field in the data to display.

treeviewflat

The TreeView shows the list of employees as a flat list. It's not much of a TreeView. That's because we need to return the data in hierarchical form.

Lazy loading hierarchical data

We want to "lazy load" our TreeView. That is, we will show top level objects and as the user drills down, we will make requests for the employees with the ManagerID of the employee that was expanded in the TreeView.

To do this, the TreeView needs two important pieces of information. The first one is whether or not the current item has any children. The second is what field it should pass to the read transport to get those children.

We'll use a field called HasChildren that we will need to define. I'm also going to set the textField to FullName which is another field we will be adding to our model.

The TreeView will send it's parameters over as JSON by default even for a GET. That means we need to use the parameterMap to specify a function which will return the EmployeeID in key/value query string format. This is currently a beta requirement and won't be necessary in the official release as it is fixed internally.

<%@ page language="java" contentType="text/html; charset=US-ASCII"
    pageEncoding="US-ASCII"%>
<%@taglib prefix="kendo" uri="http://www.kendoui.com/jsp/tags"%>
<!DOCTYPE html>
<html>
<head>
  <meta http-equiv="Content-Type" content="text/html; charset=US-ASCII">
  <title>Insert title here</title>
  <!-- kendo css files –>
  <link href="styles/kendo.common.min.css" rel="stylesheet">
  <link href="styles/kendo.default.min.css" rel="stylesheet">
  <!-- jquery and kendo javascript. jquery MUST be first. –>
  <script src="js/jquery.min.js"></script>
  <script src="js/kendo.all.min.js"></script>
</head>
<body>
 <kendo:treeView name="employees" dataTextField="FullName">
  <kendo:dataSource>
    <kendo:dataSource-transport read="api/employees">
      <kendo:dataSource-transport-parameterMap>
        <script>
          function parameterMap(options, operation) {
            if (operation === "read") {
              return { EmployeeID: options.EmployeeID };
            } 
            else {
              return options;
            }
          }
        </script>
      </kendo:dataSource-transport-parameterMap>
    </kendo:dataSource-transport>
    <kendo:dataSource-schema>
      <kendo:dataSource-schema-hierarchical-model hasChildren="HasChildren" id="EmployeeID"></kendo:dataSource-schema-hierarchical-model>
    </kendo:dataSource-schema>
  </kendo:dataSource>
 </kendo:treeView>
</body>
</html>

 

In the Employee model class, create two new fields called FullName and HasChildren. The FullName property is just a concatenation of the FirstName and LastName fields.

 package models;

public class Employee {

  private int EmployeeID;
  private int ManagerID;
  private String LastName;
  private String FirstName;
  private String FullName;
  private Boolean HasChildren;

  public int getEmployeeID() {
    return EmployeeID;
  }
  public void setEmployeeID(int employeeID) {
    EmployeeID = employeeID;
  }
  public int getManagerID() {
    return ManagerID;
  }
  public void setManagerID(int managerID) {
    ManagerID = managerID;
  }
  public String getLastName() {
    return LastName;
  }
  public void setLastName(String lastName) {
    LastName = lastName;
  }
  public String getFirstName() {
    return FirstName;
  }
  public void setFirstName(String firstName) {
    FirstName = firstName;
  }
  public String getFullName() {
    return FullName;
  }
  public void setFullName() {
    FullName = FirstName + " " + LastName;
  }
  public Boolean getHasChildren() {
    return HasChildren;
  }
  public void setHasChildren(Boolean hasChildren) {
    HasChildren = hasChildren;
  }
}
 

The TreeView will be sending over the ManagerID with each request for more employees, so we need to read that off of the request in the servlet and send it down to the EmployeeRepository listEmployees method. Where we can apply it in the WHERE clause of the prepared statement.

We'll also need to determine if our employee has any children. We can accomplish that by selecting a count of their direct reports, and then setting the model field to true if that count is greater than 0.

package repositories;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import models.Employee;

public class EmployeeRepository {

  private Connection _conn = null;

  public EmployeeRepository() { }

  public EmployeeRepository(String path) {

    // initialize the database driver
        try {
          Class.forName("org.sqlite.JDBC");

      // set the connection instance
      _conn = DriverManager.getConnection("jdbc:sqlite:" + path);

    } catch (ClassNotFoundException | SQLException e) {
      e.printStackTrace();
    }

  }

  public List<Employee> listEmployees (int managerId){

    // create a new empty list of employees to return as a result
    List<Employee> employees = new ArrayList<Employee>();

    try {

      // use prepared statements to prevent sql injection attacks
      PreparedStatement stmt = null;

      // the query to send to the database
      String query = "SELECT e.EmployeeID, e.FirstName, e.LastName, e.ReportsTo AS ManagerID, "+
                 "(SELECT COUNT(*) FROM Employees WHERE ReportsTo = e.EmployeeID) AS DirectReports " +
                 "From Employees e ";     

      // add a where clause to the query
      // if the employee doesn't have a manager
      if (managerId == 0) {
        // select where employees reportsto is null
        query += "WHERE e.ReportsTo IS NULL";
        stmt = _conn.prepareStatement(query);
      // otherwise
      } else {
        // select where the reportsto is equal to the employeeId parameter
        query +=  "WHERE e.ReportsTo = ?";
        stmt = _conn.prepareStatement(query);
        stmt.setInt(1, managerId);
      }

      // execute the query into a result set
      ResultSet rs = stmt.executeQuery();

      // iterate through the result set
      while(rs.next()) {

        // create a new employee model object
        Employee employee = new Employee();

        // select fields out of the database and set them on the class
        employee.setEmployeeID(rs.getInt("EmployeeID"));
        employee.setFirstName(rs.getString("FirstName"));
        employee.setLastName(rs.getString("LastName"));
        employee.setManagerID(rs.getInt("ManagerID"));
        employee.setHasChildren(rs.getInt("DirectReports") > 0);
        employee.setFullName();

        // add the class to the list
        employees.add(employee);
      }
    }
    catch (SQLException e) {
      e.printStackTrace();
    }

    // return the result list
    return employees;

  }

}

 

Now when we run the application, only the top level employee is shown - Andrew Fuller. If we expand his node, we see his direct reports. If you inspect the network requests, you will see that the request for data is not made until the node is expanded, giving us some nice "lazy loading" so that only the data we need is loaded from the server.

employeetree

Next week we'll look at how to select an item in the TreeView and populate a Kendo UI Grid with related details.

Project source code

For now you can grab the source code for this on our GitHub repo.

See you next week!


Burke Holland is the Director of Developer Relations at Telerik
About the Author

Burke Holland

Burke Holland is a web developer living in Nashville, TN and the Director of Developer Relations at Telerik. He enjoys working with and meeting developers who are building mobile apps with jQuery / HTML5 and loves to hack on social API's. Burke works for Telerik as a Developer Advocate focusing on Kendo UI.

Related Posts

Comments