Building a better UI – JSP Wrappers part 2
Last week we dove headfirst into Java, Servlets and the Kendo UI JSP Wrappers. This week we'll expand on that experience and explore the most popular and feature rich control in the Kendo UI arsenal - the venerable Grid.
The Grid widget isn't just an interface for displaying rows of data. No; it's a complete CRUD slinging, filtering, sorting, paging, grouping, aggregating powerhouse. The reality is that many applications are mostly just a Grid laid on top of a database providing a user with a convenient view and a meaningful and safe avenue to control that data.
You should definitely pick up last weeks project (Part 1) before you proceed. If you aren't following along in the code, then at least read through the previous article to get you up to speed since I'm not going to repeat a lot of the plumbing that was laid down last week.
Grab the source
If you just want the completed code from this article, grab the completed project from the GitHub repo.
Lets get started...
The products model
We are going to create a grid that allows editing of the Products table. Before we model this table in our code, lets have a look at what the schema for the table looks like in the database.
Notice that the Products table is related to both the Categories and Suppliers tables. This means that in order for us to effectively edit a product, we need to be able to edit its supplier and category as well. To do this, we are going to setup models for not just products, but categories and suppliers as well.
Right-click the models and create a new class. Call it Supplier. Create properties for the SupplierID and SupplierName and generate getters and setters. It's also a good idea to create a constructor that takes in these properties as variables so it makes constructing one of these objects slightly less painful.
package models;
public class Supplier {
private int SupplierID;
private String SupplierName;
public int getSupplierID() {
return SupplierID;
}
public void setSupplierID(int supplierID) {
SupplierID = supplierID;
}
public String getSupplierName() {
return SupplierName;
}
public void setSupplierName(String supplierName) {
SupplierName = supplierName;
}
public Supplier() { }
public Supplier(int supplierID, String supplierName) {
setSupplierID(supplierID);
setSupplierName(supplierName);
}
}
Now repeat the same process but this time call the class Category and give it it's respective properties.
package models;
public class Category {
private int CategoryID;
private String CategoryName;
public int getCategoryID() {
return CategoryID;
}
public void setCategoryID(int categoryID) {
CategoryID = categoryID;
}
public String getCategoryName() {
return CategoryName;
}
public void setCategoryName(String categoryName) {
CategoryName = categoryName;
}
public Category() { }
public Category(int categoryID, String categoryName) {
setCategoryID(categoryID);
setCategoryName(categoryName);
}
}
Now we are ready to create the Product model. For it's Category and Supplier, we'll use the model objects we just created. Modeling our data this way matches the database model more closely and stops us from duplicating properties like CategoryID, which appears in both the Category model and the Product model.
Right-click the models package and create a new class called Product. We are not going to be using all of the fields from the database, but just enough to examine some of the finer points of the grid.
package models;
public class Product {
private int ProductID;
private String ProductName;
private models.Supplier Supplier;
private models.Category Category;
private float UnitPrice;
private int UnitsInStock;
private Boolean Discontinued;
public int getProductID() {
return ProductID;
}
public void setProductID(int productID) {
ProductID = productID;
}
public String getProductName() {
return ProductName;
}
public void setProductName(String productName) {
ProductName = productName;
}
public float getUnitPrice() {
return UnitPrice;
}
public void setUnitPrice(float unitPrice) {
UnitPrice = unitPrice;
}
public int getUnitsInStock() {
return UnitsInStock;
}
public void setUnitsInStock(int unitsInStock) {
UnitsInStock = unitsInStock;
}
public Boolean getDiscontinued() {
return Discontinued;
}
public void setDiscontinued(Boolean discontinued) {
Discontinued = discontinued;
}
public models.Supplier getSupplier() {
return Supplier;
}
public void setSupplier(models.Supplier supplier) {
Supplier = supplier;
}
public models.Category getCategory() {
return Category;
}
public void setCategory(models.Category category) {
Category = category;
}
}
Products repository
As we are about to create another repository object, we are going to be duplicating code. This is usually a bad idea. In our scenario, all our repositories have at the very least a Connection object at the top and a constructor where the driver is initalized. At this point, it's a good idea to create a base repository class that our other classes can inherit from.
Right-click the repositories folder and create a new class called Repository. This class will simply hold the connection object at the top and will also take care of initialing the driver in the constructor.
Repository Base Class
package repositories;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class Repository {
public Connection conn = null;
public RepositoryBase(String path) {
// initialize the database driver
try {
Class.forName("org.sqlite.JDBC");
// set the connection instance
connection = DriverManager.getConnection("jdbc:sqlite:" + path);
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
}
}
Now we can alter the EmployeesRepository to inherit from this class which will remove some code.
public class EmployeeRepository extends Repository {
public EmployeeRepository(String path) {
super(path);
}
...
Right-click the repositories folder and add a new item called ProductsRepository. Change the Superclass so that it uses Repository.
Implement a listProducts method which will return all of the products from the database in a collection of Product model objects.
package repositories;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class ProductRepository extends Repository {
public ProductRepository(String path) {
super(path);
}
public List<models.product> listProducts() throws SQLException {
List<models.product> products = new ArrayList<models.product>();
PreparedStatement stmt = null;
ResultSet rs = null;
try {
String sql = "SELECT p.ProductID, p.ProductName, p.SupplierID, s.CompanyName, " +
"p.CategoryID, c.CategoryName, p.UnitPrice, p.UnitsInStock, p.Discontinued " +
"FROM Products p " +
"JOIN Suppliers s ON p.SupplierID = s.SupplierID " +
"JOIN Categories c ON p.CategoryID = c.CategoryID";
stmt = super.conn.prepareStatement(sql);
rs = stmt.executeQuery();
while(rs.next()) {
models.Product product = new models.Product();
product.setProductID(rs.getInt("ProductID"));
product.setProductName(rs.getString("ProductName"));
product.setSupplier(new models.Supplier(rs.getInt("SupplierID"), rs.getString("CompanyName")));
product.setCategory(new models.Category(rs.getInt("CategoryID"), rs.getString("CategoryName")));
product.setUnitPrice(rs.getFloat("UnitPrice"));
product.setUnitsInStock(rs.getInt("UnitsInStock"));
product.setDiscontinued(rs.getBoolean("Discontinued"));
products.add(product);
}
}
finally {
rs.close();
stmt.close();
}
return products;
}
}
Products servlet
Right-click the api folder and create a new servlet. Call it Products and change the URL mapping to /api/products.
This servlet needs to have a repository instance as well as the overridden init method for supporting our file based database. We can then implement the doGet method to return the simple list of products as 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 repositories.ProductsRepository;
import com.google.gson.Gson;
/**
* Servlet implementation class Products
*/
@WebServlet("/api/products")
public class Products extends HttpServlet {
private static final long serialVersionUID = 1L;
// employee repository class
private repositories.ProductsRepository _repository;
private Gson _gson;
public Products() {
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 ProductsRepository(this.getServletContext().getRealPath("data/sample.db"));
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// set the content type we are sending back as JSON
response.setContentType("application/json");
try {
List<models.Product> products = _repository.listProducts();
// convert the list to json and write it to the response
response.getWriter().print(_gson.toJson(products));
}
catch (Exception e) {
response.sendError(500);
}
}
}
Creating the Kendo UI Grid
We are now ready to create a products page with a grid. Add a new JSP page to your project called products.jsp. Remember to include the Kendo UI CSS, JavaScript and jQuery in your project along with the Kendo UI tag library.
<%@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> <!-- products grid will go here --> </body> </html>
Creating the initial grid is really quite easy. Just a kendo:grid tag and a defined read attribute for the transport will do the trick.
<body>
<kendo:grid name="products">
<kendo:dataSource>
<kendo:dataSource-transport read="api/products"></kendo:dataSource-transport>
</kendo:dataSource>
</kendo:grid>
</body>
However that's not exactly the way we want the data displayed. All the ID columns are visible and since the Supplier and Category are actually objects, they just show up as [object Object]. We can fix this by defining the columns that we want to display.
<body>
<kendo:grid name="products">
<kendo:dataSource>
<kendo:dataSource-transport read="api/products"></kendo:dataSource-transport>
</kendo:dataSource>
<kendo:grid-columns>
<kendo:grid-column title="Name" field="ProductName" />
<kendo:grid-column title="Supplier" field="Supplier.SupplierName" />
<kendo:grid-column title="Category" field="Category.CategoryName" />
<kendo:grid-column title="Price" field="UnitPrice" />
<kendo:grid-column title="# In Stock" field="UnitsInStock" />
<kendo:grid-column title="Discontinued" field="Discontinued" />
</kendo:grid-columns>
</kendo:grid>
</body>
Grid paging
This looks a lot better. With a recordset this large though, there are two drawbacks. The first one is that we are returning all of the data at one time when we might only need to see one page. The second is that we have to scroll through an exhaustive list of items. This is hard on the eyes.
The Kendo UI Grid supports two types of paging: client-side and server-side.
Client-side paging
Client-side paging happens when you retrieve all of the records from the server and then page through them in the browser. That eliminates the second of the two problems that we have. To turn this on, you only need to set pageable="true" on the grid and pageSize on the grid DataSource.
<body>
<kendo:grid name="products" pageable="true">
<kendo:dataSource pageSize="10">
<kendo:dataSource-transport read="api/products"></kendo:dataSource-transport>
</kendo:dataSource>
<kendo:grid-columns>
<kendo:grid-column title="Name" field="ProductName" />
<kendo:grid-column title="Supplier" field="Supplier.SupplierName" />
<kendo:grid-column title="Category" field="Category.CategoryName" />
<kendo:grid-column title="Price" field="UnitPrice" />
<kendo:grid-column title="# In Stock" field="UnitsInStock" />
<kendo:grid-column title="Discontinued" field="Discontinued" />
</kendo:grid-columns>
</kendo:grid>
</body>
Now we have a nice pager control at the bottom with a total count in the right-hand corner. A much better all around UX.
Server-side paging
This works, but we are still slamming all 77 record from the database into browser memory. 77 records probably won't make the browser complain, but imagine if we had 7700. You would definitely not want to send all of those records to your user. That's an abuse of bandwidth, memory and your user's good sensibilities.
For this reason we can turn on server-side paging with the serverPaging="true" setting.
Since this is the beta, we also need to tweak the parameters a bit in the parameterMap as they are sent as JSON by default.
<body>
<kendo:grid name="products" pageable="true">
<kendo:dataSource pageSize="10" serverPaging="true">
<kendo:dataSource-transport read="api/products">
<kendo:dataSource-transport-parameterMap>
<script>
function parameterMap(options, operation) {
return {
skip: options.skip,
take: options.take
};
}
</script>
</kendo:dataSource-transport-parameterMap>
</kendo:dataSource-transport>
</kendo:dataSource>
<kendo:grid-columns>
<kendo:grid-column title="Name" field="ProductName" />
<kendo:grid-column title="Supplier" field="Supplier.SupplierName" />
<kendo:grid-column title="Category" field="Category.CategoryName" />
<kendo:grid-column title="Price" field="UnitPrice" />
<kendo:grid-column title="# In Stock" field="UnitsInStock" />
<kendo:grid-column title="Discontinued" field="Discontinued" />
</kendo:grid-columns>
</kendo:grid>
</body>
The current beta stringifies all request parameters as to send them as JSON. The final release will NOT do this, so the parameterMap step will be unnecessary.
Once we do this, the grid will expect us to do 2 things.
- Only return the current page of items
- ALWAYS return a total count of all of the items in the table
To do this, we are going to create a generic response to send back to the Kendo UI DataSource. It will contain the grand total, as well as a collection of the items in the current page.
Right-click the models package and create a new class called DataSourceResult. Create two properties in that class – Total and Data.
package models;
import java.util.List;
public class DataSourceResult {
private int Total;
private List<?> Data;
public int getTotal() {
return Total;
}
public void setTotal(int total) {
Total = total;
}
public List<?> getData() {
return Data;
}
public void setData(List<?> data) {
Data = data;
}
}
Notice that we are sending back a list of type ?. That allows us to send back a list of anything so that we can use this DataSourceResult model object over and over again with data of any type. This prevents us from having to create an EmployeesResponse, ProductsResponse and so on and so forth.
We need to modify our ProductsRepository to support paging.
With SQLite3, we can use the LIMIT clause which looks like this: LIMIT skip,take. We tell it how many records to skip, and then how many to return. This gives us the paging we need. We can add the take and skip parameters to the doList method and then apply them to the prepared statement.
public List<models.Product> listProducts(int skip, int take) throws SQLException {
List<models.Product> products = new ArrayList<models.Product>();
PreparedStatement stmt = null;
ResultSet rs = null;
try {
String sql = "SELECT p.ProductID, p.ProductName, p.SupplierID, s.CompanyName, " +
"p.CategoryID, c.CategoryName, p.UnitPrice, p.UnitsInStock, p.Discontinued " +
"FROM Products p " +
"JOIN Suppliers s ON p.SupplierID = s.SupplierID " +
"JOIN Categories c ON p.CategoryID = c.CategoryID " +
"LIMIT ?,?";
stmt = super.conn.prepareStatement(sql);
stmt.setInt(1, skip);
stmt.setInt(2, take);
rs = stmt.executeQuery();
while(rs.next()) {
models.Product product = new models.Product();
product.setProductID(rs.getInt("ProductID"));
product.setProductName(rs.getString("ProductName"));
product.setSupplier(new models.Supplier(rs.getInt("SupplierID"), rs.getString("CompanyName")));
product.setCategory(new models.Category(rs.getInt("CategoryID"), rs.getString("CategoryName")));
product.setUnitPrice(rs.getFloat("UnitPrice"));
product.setUnitsInStock(rs.getInt("UnitsInStock"));
product.setDiscontinued(rs.getBoolean("Discontinued"));
products.add(product);
}
}
finally {
rs.close();
stmt.close();
}
return products;
}
The doList method will provide the Data portion of the DataSourceResult, but we need a method to return a total count of the records. Add a method called getCount which will execute a simple COUNT on the products table.
public int getCount() throws SQLException {
PreparedStatement stmt = null;
ResultSet rs = null;
int total = 0;
try {
// create a prepared statement
stmt = super.conn.prepareStatement("SELECT COUNT(*) AS Total FROM Products");
// execute the statment into the result set
rs = stmt.executeQuery();
while(rs.next()) {
total = rs.getInt("Total");
}
}
finally {
rs.close();
stmt.close();
}
return total;
}
Switch over to the Products servlet.
In the servlet, we can retrieve the skip and take parameters right off of the request by using the getParameter method. We will give them a default value in case no values were sent. Then we just pass them into the ProductsRepository which will apply them to the query.
Instead of serializing a Products model, we are now going to return a DataSourceResult model instead. This means we need to construct one. We use the getCount method for the Total and the doList for the Data.
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// set the content type we are sending back as JSON
response.setContentType("application/json");
// get the take and skip parameters
int skip = request.getParameter("skip") == null ? 0 : Integer.parseInt(request.getParameter("skip"));
int take = request.getParameter("take") == null ? 20 : Integer.parseInt(request.getParameter("take"));
try {
// create a new DataSourceResult to send back
models.DataSourceResult result = new models.DataSourceResult();
// set the total property
result.setTotal(_repository.getCount());
// set the data
result.setData(_repository.listProducts(skip, take));
// convert the DataSourceReslt to JSON and write it to the response
response.getWriter().print(_gson.toJson(result));
}
catch (Exception e) {
response.sendError(500);
}
}
If you were to try and run the application now, you would see that the grid will display no records at all. This is due to the fact that we completely changed the composition of the JSON response by introducing the DataSourceResult. The Products data is no longer the top level element. We need to tell the grid where in the JSON it can find the repeating data elements (Data), and which field contains the total number of records (Total).
This is what the DataSource Schema is for. We simply set data="Data" and total="Total".
<body>
<kendo:grid name="products" pageable="true">
<kendo:dataSource pageSize="10" serverPaging="true">
<kendo:dataSource-transport read="api/products">
<kendo:dataSource-transport-parameterMap>
<script>
function parameterMap(options, operation) {
return {
skip: options.skip,
take: options.take
};
}
</script>
</kendo:dataSource-transport-parameterMap>
</kendo:dataSource-transport>
<kendo:dataSource-schema data="Data" total="Total"></kendo:dataSource-schema>
</kendo:dataSource>
<kendo:grid-columns>
<kendo:grid-column title="Name" field="ProductName" />
<kendo:grid-column title="Supplier" field="Supplier.SupplierName" />
<kendo:grid-column title="Category" field="Category.CategoryName" />
<kendo:grid-column title="Price" field="UnitPrice" />
<kendo:grid-column title="# In Stock" field="UnitsInStock" />
<kendo:grid-column title="Discontinued" field="Discontinued" />
</kendo:grid-columns>
</kendo:grid>
</body>
Now the grid paging is being done by the server. That means that we only ever get 10 records at a time. The first ten records are loaded when the grid loads. Any subsequent pages are loading just-in-time as the user pages through the grid. If you have a wait time for records, the grid will automatically display a loader for you to give your users a visual indicator that work is indeed in process.
You can open your browser developer tools (F12) and watch the network traffic. Notice that as you move from page to page, a new request is sent to the server and you get back just the 10 records you requested.
Building a better UI
Building HTML5 applications is not about doing everything in the browser, it's about levering the the server (data operations) and the browser (displaying data) in the areas where they are the strongest.
Delivering data in small chunks to your users as they need it without making a complete round-trip to the server is not only highly efficient, but it also sets you up for success in lower bandwidth scenarios - like mobile devices.
Grab the source
Grab the completed source from today's project on the GitHub repo.
Next week we'll round this out by adding full CRUD to our grid. This will be very interesting since Suppliers and Categories are in different tables, but the user doesn't need to know that. We'll also implement some validation and take a look at some options for creating editing interfaces within the Kendo UI Grid.
Burke Holland
Burke Holland is a web developer living in Nashville, TN and was the Director of Developer Relations at Progress. He enjoys working with and meeting developers who are building mobile apps with jQuery / HTML5 and loves to hack on social API's. Burke worked for Progress as a Developer Advocate focusing on Kendo UI.
Comments
All articles
Topics
Latest Stories
in Your Inbox
Subscribe to be the first to get our expert-written articles and tutorials for developers!
All fields are required