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

Creating the Primary Modules

Table of Contents

CustomerManager Class
Customer Controller Class

Now we can start creating modules. The process of creating modules is described here, using the customer module as an example. Creating the product module is similar and, if you are interested, you can examine its source code in the .zip download linked at the end of this chapter.

First, we implement a class for working with jqGrid, inheriting it from our abstract class ru.ibase.fbjavaex.jqgrid.JqGrid. It will be able to search and sort by the NAME field in reversing order. Track the source code below for explanatory comments.

package ru.ibase.fbjavaex.jqgrid;

import org.jooq.*;
import java.util.List;
import java.util.Map;

import static ru.ibase.fbjavaex.exampledb.Tables.CUSTOMER;

/**
 * Customer grid
 *
 * @author Simonov Denis
 */
public class JqGridCustomer extends JqGrid {

    /**
     * Adding a search condition
     *
     * @param query
     */
    private void makeSearchCondition(SelectQuery<?> query) {
        switch (this.searchOper) {
            case "eq":
                // CUSTOMER.NAME = ?
               query.addConditions(CUSTOMER.NAME.eq(this.searchString));
               break;
            case "bw":
                // CUSTOMER.NAME STARTING WITH ?
               query.addConditions(CUSTOMER.NAME.startsWith(this.searchString));
               break;
            case "cn":
                // CUSTOMER.NAME CONTAINING ?
               query.addConditions(CUSTOMER.NAME.contains(this.searchString));
               break;
        }
    }


    /**
     * Returns the total number of records
     *
     * @return
     */
    @Override
    public int getCountRecord() {
        // query that returns the number of records
        SelectFinalStep<?> select
            = dsl.selectCount()
                 .from(CUSTOMER);

        SelectQuery<?> query = select.getQuery();
        // if perform a search, then add the search condition
        if (this.searchFlag) {
            makeSearchCondition(query);
        }

        return (int) query.fetch().getValue(0, 0);
    }

    /**
     * Returns the grid records
     *
     * @return
     */
    @Override
    public List<Map<String, Object>> getRecords() {
        // Basic selection query
        SelectFinalStep<?> select =
            dsl.select()
               .from(CUSTOMER);

        SelectQuery<?> query = select.getQuery();
        // if perform a search, then add the search condition
        if (this.searchFlag) {
            makeSearchCondition(query);
        }
        // set the sort order
        switch (this.sOrd) {
            case "asc":
                query.addOrderBy(CUSTOMER.NAME.asc());
                break;
            case "desc":
                query.addOrderBy(CUSTOMER.NAME.desc());
                break;
        }
        // limit the number of records
        if (this.limit != 0) {
            query.addLimit(this.limit);
        }

        if (this.offset != 0) {
            query.addOffset(this.offset);
        }
        // return an array of maps
        return query.fetchMaps();
    }
}
      

CustomerManager Class

The CustomerManager class that is defined next is a kind of business layer between the corresponding controller and the database. We will use it for adding, editing and deleting a customer. All operations in this layer will be performed in a SNAPSHOT-level transaction.

package ru.ibase.fbjavaex.managers;

import org.jooq.DSLContext;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Isolation;

import static ru.ibase.fbjavaex.exampledb.Tables.CUSTOMER;
import static ru.ibase.fbjavaex.exampledb.Sequences.GEN_CUSTOMER_ID;

/**
 * Customer manager
 *
 * @author Simonov Denis
 */
public class CustomerManager {

    @Autowired(required = true)
    private DSLContext dsl;

    /**
     * Adding a customer
     *
     * @param name
     * @param address
     * @param zipcode
     * @param phone
     */
    @Transactional(propagation = Propagation.REQUIRED,
                   isolation = Isolation.REPEATABLE_READ)
    public void create(String name, String address, String zipcode, String phone) {
        if (zipcode != null) {
            if (zipcode.trim().isEmpty()) {
                zipcode = null;
            }
        }

        int customerId = this.dsl.nextval(GEN_CUSTOMER_ID).intValue();

        this.dsl
                .insertInto(CUSTOMER,
                        CUSTOMER.CUSTOMER_ID,
                        CUSTOMER.NAME,
                        CUSTOMER.ADDRESS,
                        CUSTOMER.ZIPCODE,
                        CUSTOMER.PHONE)
                .values(
                        customerId,
                        name,
                        address,
                        zipcode,
                        phone
                )
                .execute();
    }

    /**
     * Editing a customer
     *
     * @param customerId
     * @param name
     * @param address
     * @param zipcode
     * @param phone
     */
    @Transactional(propagation = Propagation.REQUIRED,
                   isolation = Isolation.REPEATABLE_READ)
    public void edit(int customerId, String name, String address,
                     String zipcode, String phone) {

        if (zipcode != null) {
            if (zipcode.trim().isEmpty()) {
                zipcode = null;
            }
        }

        this.dsl.update(CUSTOMER)
                .set(CUSTOMER.NAME, name)
                .set(CUSTOMER.ADDRESS, address)
                .set(CUSTOMER.ZIPCODE, zipcode)
                .set(CUSTOMER.PHONE, phone)
                .where(CUSTOMER.CUSTOMER_ID.eq(customerId))
                .execute();
    }

    /**
     * Deleting a customer
     *
     * @param customerId
     */
    @Transactional(propagation = Propagation.REQUIRED,
                   isolation = Isolation.REPEATABLE_READ)
    public void delete(int customerId) {
        this.dsl.deleteFrom(CUSTOMER)
                .where(CUSTOMER.CUSTOMER_ID.eq(customerId))
                .execute();
    }
}
        

Customer Controller Class

Controller classes start with the @Controller annotation. The @RequestMapping annotation preceding the method is necessary for directing the actions of the controller, for specifying the path that will be used to call the action.

  • The path is specified in the value attribute
  • The method attribute specifies the HTTP request method (PUT, GET, POST, DELETE)
  • The index method will be the input point of our controller. It is responsible for displaying the JSP page (view) that contains the layout for displaying the grid, the tool bar and the navigation bar.

Data for display are loaded asynchronously by the jqGrid component. The path is /customer/getdata, to which the getData method is connected.

getData Method

The getData method contains the additional @ResponseBody annotation for indicating that our method returns the object for serialization into a specific format. The annotation @RequestMapping contains the attribute produces = MediaType.APPLICATION_JSON, directing that the returned object be serialized into the JSON format.

It is in the getData method that we work with the JqGridCustomer class described earlier. The @RequestParam annotation enables the value of the parameter to be retrieved from the HTTP request. This class method works with GET requests.

  • The value attribute in the @RequestParam annotation defines the name of the parameter to be retrieved from the HTTP request.
  • The Required attribute can designate the HTTP request parameter as mandatory.
  • The defaultValue attribute supplies the value that is to be used if the HTTP parameter is not specified.

Customer Action Methods

The addCustomer method is used to add a new customer. It is connected with the /customer/create path and, unlike the previous method, it works with the POST request. The method returns {success: true} if the customer is added successfully. If an error occurs, it returns an object with the error message. The addCustomer method works with the CustomerManager business layer method.

The editCustomer method is connected with the /customer/edit path. The deleteCustomer method is connected with the /customer/delete path. Both methods operate on existing customer records.

package ru.ibase.fbjavaex.controllers;

import java.util.HashMap;
import java.util.Map;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RequestParam;
import javax.ws.rs.core.MediaType;

import org.springframework.beans.factory.annotation.Autowired;

import ru.ibase.fbjavaex.managers.CustomerManager;

import ru.ibase.fbjavaex.jqgrid.JqGridCustomer;
import ru.ibase.fbjavaex.jqgrid.JqGridData;


/**
 * Customer Controller
 *
 * @author Simonov Denis
 */
@Controller
public class CustomerController {


    @Autowired(required = true)
    private JqGridCustomer customerGrid;

    @Autowired(required = true)
    private CustomerManager customerManager;


    /**
     * Default action
     * Returns the JSP name of the page (view) to display
     *
     * @param map
     * @return name of JSP template
     */
    @RequestMapping(value = "/customer/", method = RequestMethod.GET)
    public String index(ModelMap map) {
        return "customer";
    }

    /**
     * Returns JSON data for jqGrid
     *
     * @param rows number of entries per page
     * @param page page number
     * @param sIdx sorting field
     * @param sOrd sorting order
     * @param search should the search be performed
     * @param searchField search field
     * @param searchString value for searching
     * @param searchOper search operation
     * @return JSON data for jqGrid
     */
    @RequestMapping(value = "/customer/getdata",
            method = RequestMethod.GET,
            produces = MediaType.APPLICATION_JSON)
    @ResponseBody
    public JqGridData getData(
            // number of entries per page
            @RequestParam(value = "rows", required = false,
                          defaultValue = "20") int rows,
            // page number
            @RequestParam(value = "page", required = false,
                          defaultValue = "1") int page,
            // sorting field
            @RequestParam(value = "sidx", required = false,
                          defaultValue = "") String sIdx,
            // sorting order
            @RequestParam(value = "sord", required = false,
                          defaultValue = "asc") String sOrd,
            // should the search be performed
            @RequestParam(value = "_search", required = false,
                          defaultValue = "false") Boolean search,
            // search field
            @RequestParam(value = "searchField", required = false,
                          defaultValue = "") String searchField,
            // value for searching
            @RequestParam(value = "searchString", required = false,
                          defaultValue = "") String searchString,
            // search operation
            @RequestParam(value = "searchOper", required = false,
                          defaultValue = "") String searchOper,
            // filters
            @RequestParam(value="filters", required=false,
                          defaultValue="") String filters) {
        customerGrid.setLimit(rows);
        customerGrid.setPageNo(page);
        customerGrid.setOrderBy(sIdx, sOrd);
        if (search) {
            customerGrid.setSearchCondition(searchField, searchString, searchOper);
        }

        return customerGrid.getJqGridData();
    }

    @RequestMapping(value = "/customer/create",
            method = RequestMethod.POST,
            produces = MediaType.APPLICATION_JSON)
    @ResponseBody
    public Map<String, Object> addCustomer(
            @RequestParam(value = "NAME", required = true,
                          defaultValue = "") String name,
            @RequestParam(value = "ADDRESS", required = false,
                          defaultValue = "") String address,
            @RequestParam(value = "ZIPCODE", required = false,
                          defaultValue = "") String zipcode,
            @RequestParam(value = "PHONE", required = false,
                          defaultValue = "") String phone) {
        Map<String, Object> map = new HashMap<>();
        try {
            customerManager.create(name, address, zipcode, phone);
            map.put("success", true);
        } catch (Exception ex) {
            map.put("error", ex.getMessage());
        }
        return map;
    }

    @RequestMapping(value = "/customer/edit",
            method = RequestMethod.POST,
            produces = MediaType.APPLICATION_JSON)
    @ResponseBody
    public Map<String, Object> editCustomer(
            @RequestParam(value = "CUSTOMER_ID", required = true,
                          defaultValue = "0") int customerId,
            @RequestParam(value = "NAME", required = true,
                          defaultValue = "") String name,
            @RequestParam(value = "ADDRESS", required = false,
                          defaultValue = "") String address,
            @RequestParam(value = "ZIPCODE", required = false,
                          defaultValue = "") String zipcode,
            @RequestParam(value = "PHONE", required = false,
                          defaultValue = "") String phone) {
        Map<String, Object> map = new HashMap<>();
        try {
            customerManager.edit(customerId, name, address, zipcode, phone);
            map.put("success", true);
        } catch (Exception ex) {
            map.put("error", ex.getMessage());
        }
        return map;
    }

    @RequestMapping(value = "/customer/delete",
            method = RequestMethod.POST,
            produces = MediaType.APPLICATION_JSON)
    @ResponseBody
    public Map<String, Object> deleteCustomer(
            @RequestParam(value = "CUSTOMER_ID", required = true,
                          defaultValue = "0") int customerId) {
        Map<String, Object> map = new HashMap<>();
        try {
            customerManager.delete(customerId);
            map.put("success", true);
        } catch (Exception ex) {
            map.put("error", ex.getMessage());
        }
        return map;
    }
}
          

Customer Display

The JSP page for displaying the customer module contains nothing special: the layout with the main parts of the page, the table for displaying the grid and the block for displaying the navigation bar. JSP templates are fairly unsophisticated. If you wish, you can replace them with other template systems that support inheritance.

The ../jspf/head.jspf file contains common scripts and styles for all website pages and the ../jspf/menu.jspf file contains the website's main menu. Their code is not reproduced here: it is quite simple and you can examine it in the project's source if you are curious.

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<c:set var="cp" value="${pageContext.request.servletContext.contextPath}"
       scope="request" />

<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>An example of a Spring MVC application using Firebird
               and jOOQ</title>

        <!-- Scripts and styles -->
        <%@ include file="../jspf/head.jspf" %>
        <script src="${cp}/resources/js/jqGridCustomer.js"></script>
    </head>
    <body>
        <!-- Navigation menu -->
        <%@ include file="../jspf/menu.jspf" %>

        <div class="container body-content">

            <h2>Customers</h2>

            <table id="jqGridCustomer"></table>
            <div id="jqPagerCustomer"></div>

            <hr/>
            <footer>
                <p>© 2016 - An example of a Spring MVC application
                using Firebird and jOOQ</p>
            </footer>
        </div>

<script type="text/javascript">
    $(document).ready(function () {
        JqGridCustomer({
            baseAddress: '${cp}'
        });
    });
</script>

    </body>
</html>
            

The basic logic on the client side is concentrated in the /resources/js/jqGridCustomer.js JavaScript module.

var JqGridCustomer = (function ($) {

  return function (options) {
      var jqGridCustomer = {
          dbGrid: null,
          options: $.extend({
              baseAddress: null,
              showEditorPanel: true
          }, options),
          // return model description
          getColModel: function () {
              return [
                  {
                      label: 'Id',
                      name: 'CUSTOMER_ID', // field name
                      key: true,
                      hidden: true
                  },
                  {
                      label: 'Name',
                      name: 'NAME',
                      width: 240,
                      sortable: true,
                      editable: true,
                      edittype: "text", // input field type in the editor
                      search: true,
                      searchoptions: {
                          // allowed search operators
                          sopt: ['eq', 'bw', 'cn']
                      },
                      // size and maximum length for the input field
                      editoptions: {size: 30, maxlength: 60},
                      editrules: {required: true}
                  },
                  {
                      label: 'Address',
                      name: 'ADDRESS',
                      width: 300,
                      sortable: false, // prohibit sorting
                      editable: true,
                      search: false, // prohibit search
                      edittype: "textarea", // Memo field
                      editoptions: {maxlength: 250, cols: 30, rows: 4}
                  },
                  {
                      label: 'Zip Code',
                      name: 'ZIPCODE',
                      width: 30,
                      sortable: false,
                      editable: true,
                      search: false,
                      edittype: "text",
                      editoptions: {size: 30, maxlength: 10}
                  },
                  {
                      label: 'Phone',
                      name: 'PHONE',
                      width: 80,
                      sortable: false,
                      editable: true,
                      search: false,
                      edittype: "text",
                      editoptions: {size: 30, maxlength: 14}
                  }
              ];
          },
          // grid initialization
          initGrid: function () {
              // url to retrieve data
              var url = jqGridCustomer.options.baseAddress
                      + '/customer/getdata';
              jqGridCustomer.dbGrid = $("#jqGridCustomer").jqGrid({
                  url: url,
                  datatype: "json", // data format
                  mtype: "GET", // request type
                  colModel: jqGridCustomer.getColModel(),
                  rowNum: 500, // number of rows displayed
                  loadonce: false, // load only once
                  sortname: 'NAME', // Sorting by NAME by default
                  sortorder: "asc",
                  width: window.innerWidth - 80,
                  height: 500,
                  viewrecords: true, // display the number of records
                  guiStyle: "bootstrap",
                  iconSet: "fontAwesome",
                  caption: "Customers",
                  // navigation item
                  pager: 'jqPagerCustomer'
              });
          },
          // editing options
          getEditOptions: function () {
            return {
              url: jqGridCustomer.options.baseAddress + '/customer/edit',
              reloadAfterSubmit: true,
              closeOnEscape: true,
              closeAfterEdit: true,
              drag: true,
              width: 400,
              afterSubmit: jqGridCustomer.afterSubmit,
              editData: {
                // In addition to the values from the form, pass the key field
                CUSTOMER_ID: function () {
                  // get the current row
                  var selectedRow = jqGridCustomer.dbGrid.getGridParam("selrow");
                  // get the value of the field CUSTOMER_ID
                  var value = jqGridCustomer.dbGrid.getCell(selectedRow,
                              'CUSTOMER_ID');
                  return value;
                }
              }
            };
          },
          // Add options
          getAddOptions: function () {
            return {
              url: jqGridCustomer.options.baseAddress + '/customer/create',
              reloadAfterSubmit: true,
              closeOnEscape: true,
              closeAfterAdd: true,
              drag: true,
              width: 400,
              afterSubmit: jqGridCustomer.afterSubmit
            };
          },
          // Edit options
          getDeleteOptions: function () {
            return {
              url: jqGridCustomer.options.baseAddress + '/customer/delete',
              reloadAfterSubmit: true,
              closeOnEscape: true,
              closeAfterDelete: true,
              drag: true,
              msg: "Delete the selected customer?",
              afterSubmit: jqGridCustomer.afterSubmit,
              delData: {
                // pass the key field
                CUSTOMER_ID: function () {
                  var selectedRow = jqGridCustomer.dbGrid.getGridParam("selrow");
                  var value = jqGridCustomer.dbGrid.getCell(selectedRow,
                              'CUSTOMER_ID');
                  return value;
                }
              }
            };
          },
          // initializing the navigation bar with editing dialogs
          initPagerWithEditors: function () {
              jqGridCustomer.dbGrid.jqGrid('navGrid', '#jqPagerCustomer',
                  {
                       // buttons
                       search: true,
                       add: true,
                       edit: true,
                       del: true,
                       view: true,
                       refresh: true,
                       // button captions
                       searchtext: "Search",
                       addtext: "Add",
                       edittext: "Edit",
                       deltext: "Delete",
                       viewtext: "View",
                       viewtitle: "Selected record",
                       refreshtext: "Refresh"
                  },
                  jqGridCustomer.getEditOptions(),
                  jqGridCustomer.getAddOptions(),
                  jqGridCustomer.getDeleteOptions()
             );
          },
          // initialize the navigation bar without editing dialogs
          initPagerWithoutEditors: function () {
              jqGridCustomer.dbGrid.jqGrid('navGrid', '#jqPagerCustomer',
                  {
                       // buttons
                       search: true,
                       add: false,
                       edit: false,
                       del: false,
                       view: false,
                       refresh: true,
                       // button captions
                       searchtext: "Search",
                       viewtext: "View",
                       viewtitle: "Selected record",
                       refreshtext: "Refresh"
                  }
              );
          },
          // initialize the navigation bar
          initPager: function () {
              if (jqGridCustomer.options.showEditorPanel) {
                  jqGridCustomer.initPagerWithEditors();
              } else {
                  jqGridCustomer.initPagerWithoutEditors();
              }
          },
          // initialize
          init: function () {
              jqGridCustomer.initGrid();
              jqGridCustomer.initPager();
          },
          // processor of the results of processing forms (operations)
          afterSubmit: function (response, postdata) {
              var responseData = response.responseJSON;
              // check the result for error messages
              if (responseData.hasOwnProperty("error")) {
                  if (responseData.error.length) {
                      return [false, responseData.error];
                  }
              } else {
                  // if an error was not returned, refresh the grid
                  $(this).jqGrid(
                          'setGridParam',
                          {
                              datatype: 'json'
                          }
                  ).trigger('reloadGrid');
              }
              return [true, "", 0];
          }
      };
      jqGridCustomer.init();
      return jqGridCustomer;
  };
})(jQuery);
          

Visual Elements
The jqGrid grid

is created in the initGrid method and is bound to the html element with the jqGridCustomer identifier. The grid column desciptions are returned by the getColModel method.

Each column in jqGrid has a number of properties available. The source code contains comments explaining column properties. You can read more details about configuring the model of jqGrid columns in the ColModel API section of the documentation for the jqGrid project.

The navigation bar
can be created either with edit buttons or without them, using the initPagerWithEditors and initPagerWithoutEditors methods, respectively. The bar constructor binds it to the element with the jqPagerCustomer identifier. The options for creating the navigation bar are described in the Navigator section of the jqGrid documentation.
Functions and Settings for Options

The getEditOptions, getAddOptions, getDeleteOptions functions return the options for the edit, add and delete dialog boxes, respectively.

The url property defines the URL to which the data will be submitted after the OK button in clicked in the dialog box.

The afterSubmit property marks the event that occurs after the data have been sent to the server and a response has been received back.

The afterSubmit method checks whether the controller returns an error. The grid is updated if no error is returned; otherwise, the error is shown to the user.

Note

The editData property allows you to specify the values of additional fields that are not shown in the edit dialog box. Edit dialog boxes do not show the values of hidden fields and it is rather tedious if you want to display automatically generated keys.

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