Firebird Documentation IndexFirebird 3.0 Developer's GuideCreating an Application with jOOQ and Spring MVC → Writing the Application Code
Firebird Home Firebird Home Prev: Working with TransactionsFirebird Documentation IndexUp: Creating an Application with jOOQ and Spring MVCNext: Creating the Primary Modules

Writing the Application Code

We will display the data of our application using the JavaScript component jqGrid. Currently, jqGrid is distributed under a commercial licence, but it is free for non-commercial purposes. You can use the free-jqGrid fork instead.

To display data and page-by-page navigation elements in this grid, we need to return data in the JSON format, the structure of which looks like this:

{
    total: 100,
    page: 3,
    records: 3000,
    rows: [
       {id: 1, name: "Ada"},
       {id: 2, name: "Smith"},
      …
    ]
}
      

where

The following code creates a class demonstrating this structure:

package ru.ibase.fbjavaex.jqgrid;

import java.util.List;
import java.util.Map;

/**
 * A class describing the structure that is used in jqGrid
 * Designed for JSON serialization
 *
 * @author Simonov Denis
 */
public class JqGridData {

    /**
     * Total number of pages
     */
    private final int total;

    /**
     * The current page number
     */
    private final int page;

    /**
     * Total number of records
     */
    private final int records;

    /**
     * The actual data
     */
    private final List<Map<String, Object>> rows;

    /**
     * Constructor
     *
     * @param total
     * @param page
     * @param records
     * @param rows
     */
    public JqGridData(int total, int page, int records,
                      List<Map<String, Object>> rows) {
        this.total = total;
        this.page = page;
        this.records = records;
        this.rows = rows;
    }

    /**
     * Returns the total number of pages
     *
     * @return
     */
    public int getTotal() {
        return total;
    }

    /**
     * Returns the current page
     *
     * @return
     */
    public int getPage() {
        return page;
    }

    /**
     * Returns the total number of records
     *
     * @return
     */
    public int getRecords() {
        return records;
    }

    /**
     * Return list of map
     * This is an array of data to display in the grid
     *
     * @return
     */
    public List<Map<String, Object>> getRows() {
        return rows;
    }
}
        

Now we will write an abstract class that will return that structure depending on the search and sorting conditions. It will be a parent class for the entity-specific classes that return similar structures.

/*
 * Abstract class for working with JqGrid
 */
package ru.ibase.fbjavaex.jqgrid;

import java.util.Map;
import java.util.List;
import org.jooq.DSLContext;
import org.springframework.beans.factory.annotation.Autowired;

/**
 * Working with JqGrid
 *
 * @author Simonov Denis
 */
public abstract class JqGrid {


    @Autowired(required = true)
    protected DSLContext dsl;

    protected String searchField = "";
    protected String searchString = "";
    protected String searchOper = "eq";
    protected Boolean searchFlag = false;
    protected int pageNo = 0;
    protected int limit = 0;
    protected int offset = 0;
    protected String sIdx = "";
    protected String sOrd = "asc";

    /**
     * Returns the total number of records
     *
     * @return
     */
    public abstract int getCountRecord();

    /**
     * Returns the structure for JSON serialization
     *
     * @return
     */
    public JqGridData getJqGridData() {
        int recordCount = this.getCountRecord();
        List<Map<String, Object>> records = this.getRecords();

        int total = 0;
        if (this.limit > 0) {
            total = recordCount / this.limit + 1;
        }

        JqGridData jqGridData = new JqGridData(
            total,
            this.pageNo,
            recordCount,
            records);
        return jqGridData;
    }


    /**
     * Returns the number of records per page
     *
     * @return
     */
    public int getLimit() {
        return this.limit;
    }

    /**
     * Returns the offset to retrieve the first record on the page
     *
     * @return
     */
    public int getOffset() {
        return this.offset;
    }

    /**
     * Returns field name for sorting
     *
     * @return
     */
    public String getIdx() {
        return this.sIdx;
    }

    /**
     * Returns the sort order
     *
     * @return
     */
    public String getOrd() {
        return this.sOrd;
    }

    /**
     * Returns the current page number
     *
     * @return
     */
    public int getPageNo() {
        return this.pageNo;
    }

    /**
     * Returns an array of records as a list of maps
     *
     * @return
     */
    public abstract List<Map<String, Object>> getRecords();

    /**
     * Returns field name for search
     *
     * @return
     */
    public String getSearchField() {
        return this.searchField;
    }

    /**
     * Returns value for search
     *
     * @return
     */
    public String getSearchString() {
        return this.searchString;
    }

    /**
     * Returns the search operation
     *
     * @return
     */
    public String getSearchOper() {
        return this.searchOper;
    }

    /**
     * Sets the limit on the number of display records
     *
     * @param limit
     */
    public void setLimit(int limit) {
        this.limit = limit;
    }

    /**
     * Sets the number of records to skip
     *
     * @param offset
     */
    public void setOffset(int offset) {
        this.offset = offset;
    }

    /**
     * Sets the sorting
     *
     * @param sIdx
     * @param sOrd
     */
    public void setOrderBy(String sIdx, String sOrd) {
        this.sIdx = sIdx;
        this.sOrd = sOrd;
    }

    /**
     * Sets the current page number
     *
     * @param pageNo
     */
    public void setPageNo(int pageNo) {
        this.pageNo = pageNo;
        this.offset = (pageNo - 1) * this.limit;
    }

    /**
     * Sets the search condition
     *
     * @param searchField
     * @param searchString
     * @param searchOper
     */
    public void setSearchCondition(String searchField, String searchString,
                                   String searchOper) {
        this.searchFlag = true;
        this.searchField = searchField;
        this.searchString = searchString;
        this.searchOper = searchOper;
    }
}
      

Note

Notice that this class contains the DSLContext dsl property that will be used to build jOOQ queries for retrieving data.

Prev: Working with TransactionsFirebird Documentation IndexUp: Creating an Application with jOOQ and Spring MVCNext: Creating the Primary Modules
Firebird Documentation IndexFirebird 3.0 Developer's GuideCreating an Application with jOOQ and Spring MVC → Writing the Application Code