Database data from Derby in EasyUI datagrid

In this tutorial we show how to display database data from Derby in a EasyUI datagrid component. We create a simple Java web application, which reads data from a Derby database and sends it to the client browser. The data is displayed in a datagrid component.

EasyUI is a JavaScript library which provides essential functionality for building modern, interactive, JavaScript applications. EasyUI provides many useful components for the front end. EasyUI is built on top of JQuery.

Apache Derby is an open source relational database implemented entirely in Java. It has a small footprint and is easy to deploy and install. It supports both embedded and client/server modes. It is also known under the name of Java DB.

cars.sql
CREATE TABLE CARS(ID INTEGER NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY 
    (START WITH 1, INCREMENT BY 1), NAME VARCHAR(30), PRICE INT);

INSERT INTO CARS(NAME, PRICE) VALUES('Audi', 52642);
INSERT INTO CARS(NAME, PRICE) VALUES('Mercedes', 57127);
INSERT INTO CARS(NAME, PRICE) VALUES('Skoda', 9000);
INSERT INTO CARS(NAME, PRICE) VALUES('Volvo', 29000);
INSERT INTO CARS(NAME, PRICE) VALUES('Bentley', 350000);
INSERT INTO CARS(NAME, PRICE) VALUES('Citroen', 21000);
INSERT INTO CARS(NAME, PRICE) VALUES('Hummer', 41400);
INSERT INTO CARS(NAME, PRICE) VALUES('Volkswagen', 21600);

In our example, we use this database table.

<dependencies>
    <dependency>
        <groupId>javax</groupId>
        <artifactId>javaee-web-api</artifactId>
        <version>7.0</version>
        <scope>provided</scope>
    </dependency>
    
    <dependency>
        <groupId>org.apache.derby</groupId>
        <artifactId>derbyclient</artifactId>
        <version>10.12.1.1</version>
    </dependency>    
    
    <dependency>
        <groupId>com.googlecode.json-simple</groupId>
        <artifactId>json-simple</artifactId>
        <version>1.1.1</version>
    </dependency>
    
    <dependency>
        <groupId>org.apache.derby</groupId>
        <artifactId>derbyoptionaltools</artifactId>
        <version>10.12.1.1</version>
    </dependency>
        
</dependencies>

In the project, we use these four dependencies. The javaee-web-api is a set of JARs to build a Java web application, derbyclient is a Derby database driver, json-simple is a library for working with JSON, and derbyoptionaltools is used to transform an SQL result set to JSON.

index.html
<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>Cars</title>
        <link rel="stylesheet" type="text/css" href="http://www.jeasyui.com/easyui/themes/default/easyui.css">
        <link rel="stylesheet" type="text/css" href="http://www.jeasyui.com/easyui/themes/color.css">
        <script src="https://code.jquery.com/jquery-3.1.0.min.js"></script>
        <script type="text/javascript" src="http://www.jeasyui.com/easyui/jquery.easyui.min.js"></script>

    </head>
    <body>
        <h2>Cars</h2>
    
        <table id="dg" title="Cars" class="easyui-datagrid" style="width:700px;height:350px"
               url="GetCars"
               pagination="true"
               rownumbers="true" fitColumns="true" singleSelect="true">
            <thead>
                <tr>
                    <th field="NAME" width="50">Name</th>
                    <th field="PRICE" width="50">Price</th>
                </tr>
            </thead>
        </table>

    </body>
</html>

In the index.html file, we import the EasyUI and JQuery libraries. We use the EasyUI datagrid component, which is set with the class attribute. The url attribute points to the Java Servlet, which returns data in a JSON format. The names of the row fields must match the names of fields of the JSON data, returned from the servlet. JSON is s a popular lightweight data-interchange format.

GetCars.java
package com.zetcode.web;

import com.zetcode.service.CarService;
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 org.json.simple.JSONArray;

@WebServlet(name = "GetCars", urlPatterns = {"/GetCars"})
public class GetCars extends HttpServlet {

    protected void processRequest(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {

        response.setContentType("application/json");
        response.setCharacterEncoding("UTF-8");

        JSONArray ar = CarService.getCarsJSON();

        response.getWriter().write(ar.toJSONString());
    }

    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {

        processRequest(request, response);
    }

    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {

        processRequest(request, response);
    }
}

The GetCars Java servlet calls the service method, which retrieves data from the Derby database table.

response.setContentType("application/json");

We set the content type to application/json.

JSONArray ar = CarService.getCarsJSON();

The getCarsJSON() method returns the data in a JSON format.

response.getWriter().write(ar.toJSONString());

The data is send to the client.

CarService.java
package com.zetcode.service;

import com.zetcode.web.GetCars;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.apache.derby.optional.api.SimpleJsonUtils;
import org.json.simple.JSONArray;

public class CarService {

    private static JSONArray jarray;

    public static JSONArray getCarsJSON() {

        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;

        String url = "jdbc:derby://localhost:1527/testdb";
        
        String user = "app";
        String password = "app";

        try {

            DriverManager.registerDriver(new org.apache.derby.jdbc.ClientDriver());
            con = DriverManager.getConnection(url, user, password);
            pst = con.prepareStatement("SELECT NAME, PRICE FROM Cars");
            rs = pst.executeQuery();

            jarray = SimpleJsonUtils.toJSON(rs);

        } catch (SQLException ex) {

            Logger lgr = Logger.getLogger(GetCars.class.getName());
            lgr.log(Level.SEVERE, ex.getMessage(), ex);

        } finally {

            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
                if (con != null) {
                    con.close();
                }

            } catch (SQLException ex) {
                Logger lgr = Logger.getLogger(GetCars.class.getName());
                lgr.log(Level.WARNING, ex.getMessage(), ex);
            }
        }

        return jarray;
    }
}

The GetCars() method connects to the Derby database and executes the SELECT statement; the returned data is transformed into JSON. JDBC is used to execute the SQL.

pst = con.prepareStatement("SELECT NAME, PRICE FROM Cars");

We retrieve two columns from the table: NAME and PRICE.

jarray = SimpleJsonUtils.toJSON(rs);

We use the SimpleJsonUtils.toJSON() method to transform a result set into an array of JSON objects. The method is available in the Derby optionaltools library.

Displaying database data in a datagrid
Figure: Displaying database data in a datagrid

In this article, we have shown how to display database data from a Derby database in a datagrid control. The data was sent from the database to the datagrid using JSON format.

You might also be interested in the following related tutorials: Tomcat Derby tutorial, Apache Derby tutorial, Using jsGrid tutorial, jQuery Autocomplete tutorial, Displaying data from Derby in EasyUI datagrid, Using jQuery DatePicker in JSP and PHP, and Java tutorial.