jQuery DataTables with JAX-RS

Posted on Monday Mar 24, 2014 at 05:21PM in Technology

jQuery DataTables plugin can delegate some data processing to server-side with interaction with JSON[2].
- It's efficient way when we have to go with large data sets.
So I have tried it with JAX-RS.

Environment

DataTables 1.9.4
WildFly 8.0.0.Final
Oracle JDK8
PostgreSQL 9.2.4

Sample data

In this example, we use some tables on PostgreSQL as the data source.
- These tables are parts of the job repository of jBeret (jBatch implementation of WildFly).
We will execute a SQL which returns data set like this:

 jobexecutionid | jobname |        starttime        |         endtime         | batchstatus 
----------------+---------+-------------------------+-------------------------+-------------
           2167 | myjob   | 2014-03-19 14:31:13.343 |                         | STARTING
           2166 | myjob   | 2014-03-19 14:14:38.158 | 2014-03-19 14:14:59.388 | FAILED
           2165 | myjob   | 2014-03-19 14:13:24.104 | 2014-03-19 14:14:25.59  | STOPPED
           2164 | myjob   | 2014-03-19 14:11:32.238 |                         | STARTING
           2163 | myjob   | 2014-03-19 14:03:41.07  | 2014-03-19 14:10:29.391 | STOPPED
           2162 | myjob   | 2014-03-19 13:54:41.017 | 2014-03-19 13:56:55.365 | STOPPED
           2161 | myjob   | 2014-03-19 13:54:26.902 | 2014-03-19 13:54:38.077 | STOPPED
           2160 | myjob   | 2014-03-19 13:53:49.291 | 2014-03-19 13:54:22.496 | STOPPED
(8 rows)

jbatch=#

Resources

JobExecutionReportService.java

Make sure a datasource named “java:jboss/jdbc/JBatchDS” configured correctly on the application server.

package org.nailedtothex.jaxrs_datatables;

import javax.annotation.PostConstruct;
import javax.annotation.Resource;
import javax.ejb.Stateless;
import javax.sql.DataSource;
import javax.ws.rs.GET;
import javax.ws.rs.Path;
import javax.ws.rs.Produces;
import javax.ws.rs.QueryParam;
import javax.ws.rs.core.MediaType;
import java.sql.*;
import java.text.Format;
import java.text.MessageFormat;
import java.util.*;

@Stateless
@Path("/JobExecutionReportService")
public class JobExecutionReportService {
    private static final String SQL_COUNT = "SELECT COUNT(1) FROM job_execution";
    private static final String SQL_FETCH = "SELECT\n" +
            "    e.jobexecutionid,\n" +
            "    i.jobname,\n" +
            "    e.starttime,\n" +
            "    e.endtime,\n" +
            "    e.batchstatus\n" +
            "FROM\n" +
            "    job_execution e\n" +
            "    LEFT JOIN job_instance i ON e.jobinstanceid = i.jobinstanceid\n" +
            "ORDER BY\n" +
            "    e.jobexecutionid {0}\n" +
            "LIMIT ?\n" +
            "OFFSET ?";
    private static final Set<String> VALID_SORT_DIR = Collections.unmodifiableSet(new HashSet<>(Arrays.asList("asc", "desc")));

    @Resource(lookup = "java:jboss/jdbc/JBatchDS")
    private DataSource ds;
    private Format sqlFormat;

    @PostConstruct
    protected void init() {
        sqlFormat = new MessageFormat(SQL_FETCH);
    }

    @GET
    @Produces(MediaType.APPLICATION_JSON)
    public DataTablesBean getJobExecutionReport(
            @QueryParam("sEcho") Integer sEcho,
            @QueryParam("iDisplayLength") Integer iDisplayLength,
            @QueryParam("iDisplayStart") Integer iDisplayStart,
            @QueryParam("sSortDir_0") String sSortDir_0) throws SQLException {

        final Long count;
        try (Connection cn = ds.getConnection();
             Statement st = cn.createStatement();
             ResultSet rs = st.executeQuery(SQL_COUNT);) {
            rs.next();
            count = rs.getLong(1);
        }

        if (sSortDir_0 == null || !VALID_SORT_DIR.contains(sSortDir_0)) {
            throw new IllegalArgumentException(sSortDir_0);
        }

        final String sql = sqlFormat.format(new Object[]{sSortDir_0});
        final List<List<String>> aaData = new ArrayList<>();

        try (Connection cn = ds.getConnection();
             PreparedStatement ps = cn.prepareStatement(sql)) {
            ps.setInt(1, iDisplayLength);
            ps.setInt(2, iDisplayStart);
            try (ResultSet rs = ps.executeQuery()) {
                final int columns = rs.getMetaData().getColumnCount();
                while (rs.next()) {
                    List<String> data = new ArrayList<>(columns);
                    for (int i = 1; i <= columns; i++) {
                        data.add(rs.getString(i));
                    }
                    aaData.add(data);
                }
            }
        }

        final DataTablesBean bean = new DataTablesBean();
        bean.setsEcho(sEcho);
        bean.setiTotalDisplayRecords(String.valueOf(count));
        bean.setiTotalRecords(String.valueOf(count));
        bean.setAaData(aaData);
        return bean;
    }

}

DataTablesBean.java

package org.nailedtothex.jaxrs_datatables;

import java.util.List;

public class DataTablesBean {
    private Integer sEcho;
    private String iTotalRecords;
    private String iTotalDisplayRecords;
    private List<List<String>> aaData;

    public String getiTotalRecords() {
        return iTotalRecords;
    }

    public void setiTotalRecords(String iTotalRecords) {
        this.iTotalRecords = iTotalRecords;
    }

    public String getiTotalDisplayRecords() {
        return iTotalDisplayRecords;
    }

    public void setiTotalDisplayRecords(String iTotalDisplayRecords) {
        this.iTotalDisplayRecords = iTotalDisplayRecords;
    }

    public List<List<String>> getAaData() {
        return aaData;
    }

    public void setAaData(List<List<String>> aaData) {
        this.aaData = aaData;
    }

    public Integer getsEcho() {
        return sEcho;
    }

    public void setsEcho(Integer sEcho) {
        this.sEcho = sEcho;
    }
}

index.html

<!DOCTYPE html>
<html>
<head lang="en">
    <meta charset="UTF-8">
    <link rel="stylesheet" type="text/css"
          href="http://ajax.aspnetcdn.com/ajax/jquery.dataTables/1.9.4/css/jquery.dataTables.css">
    <script type="text/javascript" charset="utf8"
            src="http://ajax.aspnetcdn.com/ajax/jQuery/jquery-1.8.2.min.js"></script>
    <script type="text/javascript" charset="utf8"
            src="http://ajax.aspnetcdn.com/ajax/jquery.dataTables/1.9.4/jquery.dataTables.min.js"></script>
    <script>
        $(document).ready(function () {
            $('#example').dataTable({
                "bFilter": false,
                "sPaginationType": "full_numbers",
                "aaSorting": [
                    [ 0, "desc" ]
                ],
                "aoColumns": [
                    { "bSortable": true },
                    { "bSortable": false },
                    { "bSortable": false },
                    { "bSortable": false },
                    { "bSortable": false }
                ],
                "bProcessing": true,
                "bServerSide": true,
                "sAjaxSource": "webapi/JobExecutionReportService"
            });
        });
    </script>
    <title></title>
</head>
<body>
<table id="example">
    <thead>
    <tr>
        <th>id</th>
        <th>jobName</th>
        <th>startTime</th>
        <th>endTime</th>
        <th>batchStatus</th>
    </tr>
    </thead>
    <tbody>
    </tbody>
</table>
</body>
</html>

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>org.nailedtothex</groupId>
    <artifactId>jaxrs-datatables</artifactId>
    <version>1.0-SNAPSHOT</version>
    <packaging>war</packaging>

    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                </configuration>
            </plugin>
        </plugins>
    </build>

    <dependencies>
        <dependency>
            <groupId>javax</groupId>
            <artifactId>javaee-api</artifactId>
            <version>7.0</version>
        </dependency>
    </dependencies>

</project>

web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns="http://java.sun.com/xml/ns/javaee"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://java.sun.com/xml/ns/javaee
          http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd"
         version="3.0">
    <servlet-mapping>
        <servlet-name>javax.ws.rs.core.Application</servlet-name>
        <url-pattern>/webapi/*</url-pattern>
    </servlet-mapping>
    <welcome-file-list>
        <welcome-file>index.html</welcome-file>
    </welcome-file-list>
</web-app>