Home Java Creating applications using Firebird, jOOQand Spring MVC

Creating applications using Firebird, jOOQand Spring MVC

by admin

Hi all.This time we will describe the process of creating a web application in Java using Spring MVC framework, jOOQ library and Firebird DBMS.
To simplify development, you can use one of the common IDEs for Java (NetBeans, IntelliJ IDEA, Eclipse, JDeveloper, or others). Personally, I used NetBeans. For testing and debugging we also need to install one of the web or application servers (Apache Tomcat or Glass Fish).
After creating a template-based project, you need to convert its folder structure so that it was correct for MVC Spring 4. If the project was created in the NetBeans 8.2 environment, you must perform the following steps :

  1. Delete a file index.html
  2. Create a WEB-INF folder inside the Web Pages folder
  3. Inside the WEB-INF folder create folders jsp, jspf and resources
  4. Inside the folder resources create folders js and CSS
  5. Inside the folder jsp create file index.jsp

After our manipulations, the folder structure should look like this
Creating applications using Firebird, jOOQand Spring MVC
In the folder WEB-INF/jsp will be placed jsp pages, and in the folder jspf parts of pages that will be connected to other pages using the instruction :

<%@ include file="<file name> "%>

Folder resource is intended for placement of static web resources. In the folder WEB-INF/resources/css will be placed files of cascading stylesheets, in the folder WEB-INF/resources/fonts – font files, in the folder WEB-INF/resources/js – JavaScript files and third-party JavaScript libraries.
Now let’s fix the pom.xmlfile and write in general application properties, dependencies on library packages (Spring MVC, Jaybird, JDBC pool, JOOQ) and JDBC connection properties.
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> ru.ibase</groupId><artifactId> fbjavaex</artifactId><version> 1.0-SNAPSHOT</version><packaging> war</packaging><name> Firebird Java Example</name><properties><endorsed.dir> ${project.build.directory}/endorsed</endorsed.dir><project.build.sourceEncoding> UTF-8</project.build.sourceEncoding><spring.version> 4.3.4.RELEASE</spring.version><jstl.version> 1.2</jstl.version><javax.servlet.version> 3.0.1</javax.servlet.version><db.url> jdbc:firebirdsql://localhost:3050/examples</db.url><db.driver> org.firebirdsql.jdbc.FBDriver</db.driver><db.username> SYSDBA</db.username><db.password> masterkey</db.password></properties><dependencies><dependency><groupId> javax</groupId><artifactId> javaee-web-api</artifactId><version> 7.0</version><scope> provided</scope></dependency><dependency><groupId> javax.servlet</groupId><artifactId> javax.servlet-api</artifactId><version> ${javax.servlet.version}</version><scope> provided</scope></dependency><dependency><groupId> jstl</groupId><artifactId> jstl</artifactId><version> ${jstl.version}</version></dependency><!-- Working with JSON --><dependency><groupId> com.fasterxml.jackson.core</groupId><artifactId> jackson-core</artifactId><version> 2.8.5</version></dependency><dependency><groupId> com.fasterxml.jackson.core</groupId><artifactId> jackson-annotations</artifactId><version> 2.8.5</version></dependency><dependency><groupId> com.fasterxml.jackson.core</groupId><artifactId> jackson-databind</artifactId><version> 2.8.5</version></dependency><!-- Spring --><dependency><groupId> org.springframework</groupId><artifactId> spring-core</artifactId><version> ${spring.version}</version></dependency><dependency><groupId> org.springframework</groupId><artifactId> spring-web</artifactId><version> ${spring.version}</version></dependency><dependency><groupId> org.springframework</groupId><artifactId> spring-webmvc</artifactId><version> ${spring.version}</version></dependency><dependency><groupId> org.springframework</groupId><artifactId> spring-context</artifactId><version> ${spring.version}</version></dependency><dependency><groupId> org.springframework</groupId><artifactId> spring-jdbc</artifactId><version> ${spring.version}</version></dependency><!-- JDBC --><dependency><groupId> org.firebirdsql.jdbc</groupId><artifactId> jaybird-jdk18</artifactId><version> 3.0.0</version></dependency><!-- Pool of connections --><dependency><groupId> commons-dbcp</groupId><artifactId> commons-dbcp</artifactId><version> 1.4</version></dependency><!-- jOOQ --><dependency><groupId> org.jooq</groupId><artifactId> jooq</artifactId><version> 3.9.2</version></dependency><dependency><groupId> org.jooq</groupId><artifactId> jooq-meta</artifactId><version> 3.9.2</version></dependency><dependency><groupId> org.jooq</groupId><artifactId> jooq-codegen</artifactId><version> 3.9.2</version></dependency><!-- Testing --><dependency><groupId> junit</groupId><artifactId> junit</artifactId><version> 4.11</version><type> jar</type><scope> test</scope></dependency><dependency><groupId> org.springframework</groupId><artifactId> spring-test</artifactId><version> ${spring.version}</version><scope> test</scope></dependency></dependencies><build><plugins><plugin><groupId> org.apache.maven.plugins</groupId><artifactId> maven-compiler-plugin</artifactId><version> 3.1</version><configuration><soigse> 1.7</soigse><target> 1.7</target><compilerArguments><endorseddirs> ${endorsed.dir}</endorseddirs></compilerArguments></configuration></plugin><plugin><groupId> org.apache.maven.plugins</groupId><artifactId> maven-war-plugin</artifactId><version> 2.3</version><configuration><failOnMissingWebXml> false</failOnMissingWebXml></configuration></plugin><plugin><groupId> org.apache.maven.plugins</groupId><artifactId> maven-dependency-plugin</artifactId><version> 2.6</version><executions><execution><phase> validate</phase><goals><goal> copy</goal></goals><configuration><outputDirectory> ${endorsed.dir}</outputDirectory><silent> true</silent><artifactItems><artifactItem><groupId> javax</groupId><artifactId> javaee-endorsed-api</artifactId><version> 7.0</version><type> jar</type></artifactItem></artifactItems></configuration></execution></executions></plugin></plugins></build></project>

After you have written all the required dependencies, it is advisable to reload the POM to load all the required libraries. If this is not done, errors may occur while working with the project. The way to do this in NetBeans is this
Creating applications using Firebird, jOOQand Spring MVC
I don’t really like configuring via xml, so I’ll work through the Java configuration classes.
WebAppConfig.java

package ru.ibase.fbjavaex.config;import org.springframework.context.annotation.Bean;import org.springframework.context.annotation.ComponentScan;import org.springframework.context.annotation.Configuration;import org.springframework.web.servlet.config.annotation.EnableWebMvc;import org.springframework.web.servlet.config.annotation.ResourceHandlerRegistry;import org.springframework.web.servlet.config.annotation.WebMvcConfigurerAdapter;import org.springframework.web.servlet.view.JstlView;import org.springframework.web.servlet.view.UrlBasedViewResolver;import org.springframework.http.converter.json.MappingJackson2HttpMessageConverter;import org.springframework.http.converter.HttpMessageConverter;import com.fasterxml.jackson.databind.ObjectMapper;import com.fasterxml.jackson.databind.SerializationFeature;import java.util.List;@Configuration@ComponentScan("ru.ibase.fbjavaex")@EnableWebMvcpublic class WebAppConfig extends WebMvcConfigurerAdapter {@Overridepublic void configureMessageConverters(List<HttpMessageConverter<?> > httpMessageConverters) {MappingJackson2HttpMessageConverter jsonConverter = new MappingJackson2HttpMessageConverter();ObjectMapper objectMapper = new ObjectMapper();objectMapper.configure(SerializationFeature.WRITE_DATES_AS_TIMESTAMPS, false);jsonConverter.setObjectMapper(objectMapper);httpMessageConverters.add(jsonConverter);}@Beanpublic UrlBasedViewResolver setupViewResolver() {UrlBasedViewResolver resolver = new UrlBasedViewResolver();resolver.setPrefix("/WEB-INF/jsp/");resolver.setSuffix(".jsp");resolver.setViewClass(JstlView.class);return resolver;}@Overridepublic void addResourceHandlers(ResourceHandlerRegistry registry) {registry.addResourceHandler("/resources/**").addResourceLocations("/WEB-INF/resources/");}}

In this configuration class, we set where the web resources and JSP views are searched. The configureMessageConverters method specifies that the date should be serialized to a string representation (by default it is serialized to a numeric representation as timestamp).
Now let’s get rid of Web.xml file and instead create WebInitializer.javafile.
WebInitializer.java

package ru.ibase.fbjavaex.config;import javax.servlet.ServletContext;import javax.servlet.ServletException;import javax.servlet.ServletRegistration.Dynamic;import org.springframework.web.WebApplicationInitializer;import org.springframework.web.context.support.AnnotationConfigWebApplicationContext;import org.springframework.web.servlet.DispatcherServlet;public class WebInitializer implements WebApplicationInitializer {@Overridepublic void onStartup(ServletContext servletContext) throws ServletException {AnnotationConfigWebApplicationContext ctx = new AnnotationConfigWebApplicationContext();ctx.register(WebAppConfig.class);ctx.setServletContext(servletContext);Dynamic servlet = servletContext.addServlet("dispatcher", new DispatcherServlet(ctx));servlet.addMapping("/");servlet.setLoadOnStartup(1);}}

It remains to configure the IoC containers to implement dependencies. We’ll come back to this step later, but for now let’s move on to generating classes to work with the database via jOOQ.

Generating classes to work with the database via jOOQ

We will work with the database using the library jOOQ jOOQ allows you to build SQL queries from jOOQ objects and code (similar to LINQ). jOOQ is more tightly integrated with the database than the ORM, so it can use additional features besides the simple CRUD SQL queries used in Active Record. For example jOOQ can work with stored procedures and functions, sequences, use windows functions and other DBMS specific features. Full documentation on how to use jOOQ can be found at link
The jOOQ classes for working with the database are generated based on the database schema. Our application will work with the database whose model is shown in the figure below.
Creating applications using Firebird, jOOQand Spring MVC
In addition to tables, our database also contains stored procedures and sequences.At the end of this article is a link to the database creation script.

Warning.
This model is just an example.Your subject area may be more complex, or completely different.The model used in this article is simplified as much as possible so as not to clutter the description of working with components with a description of creating and modifying a data model.

To generate jOOQ classes that work with our database, you need to download the following binaries by link or via the maven repository :

  • jooq-3.9.2.jar
    The main library that is included in our jOOQ application.
  • jooq-meta-3.9.2.jar
    A utility that is included in your build to navigate the database schema through the generated objects.
  • jooq-codegen-3.9.2.jar
    A utility that is included in your build to generate the database schema.

In addition, to connect to the Firebird database via JDBC, you will need to download the driver jaybird-full-3.0.0.jar
Now we need to create a configuration file example.xml, which will be used to generate the database schema classes.
example.xml

<?xml version="1.0" encoding="UTF-8" standalone="yes"?><configuration xmlns="http://www.jooq.org/xsd/jooq-codegen-3.8.0.xsd"><!-- DB connection Configuration --><jdbc><driver> org.firebirdsql.jdbc.FBDriver</driver><url> jdbc:firebirdsql://localhost:3050/examples</url><user> SYSDBA</user><password> masterkey</password><properties><property><key> charSet</key><value> utf-8</value></property></properties></jdbc><generator><name> org.jooq.util.JavaGenerator</name><database><!-- Database type. Format :org.util.[database].[database]Database --><name> org.jooq.util.firebird.FirebirdDatabase</name><inputSchema> </inputSchema><!-- All objects that are generated from your schema(Java Regular Expression.Use filters to limit the number of objects).Watch out for case sensitivity. Depending on your database, this may be important! --><includes> .*</includes><!-- Objects that are excluded during generation from your schema.(Java regular expression).In this case, we exclude the RDB$ system tables, MON$ monitoring tablesand SEC$ security pseudo-tables. --><excludes>RDB\$.*| MON\$.*| SEC\$.*</excludes></database><target> <target><!-- The name of the package in which the generated classes will be unloaded --><packageName> ru.ibase.fbjavaex.exampledb</packageName><!--Directory to host the generated classes.This uses the Maven directory structure. --><directory> e:/OpenServer/domains/localhost/fbjavaex/src/main/java/</directory></target>;</generator></configuration>

Now go to the command line and run the following command :

java -cp jooq-3.9.2.jar;jooq-meta-3.9.2.jar;jooq-codegen-3.9.2.jar;jaybird-full-3.0.0.jar;. org.jooq.util.GenerationTool example.xml

This command creates the necessary classes and allows you to write queries to database objects in Java. You can find more details about the class generation process at Code generation

IoC Container Configuration

In Spring, Dependency Injection (DI) is done through a Spring IoC (Inversion of Control) container. Dependency Injection, is the process by which objects define their dependencies, i.e., the objects they work with, through constructor/factory method arguments or properties that have been set or returned by a factory method. The container then injects those dependencies when creating the bin. You can read more about injecting dependencies in the chapter The IoC container
I’m not a proponent of xml configuration, so we’ll use the annotation and Java configuration approach. The main features and parts of a Java-configuration IoC container are classes with @Configuration annotation and methods with annotation Bean Abstract Bean is used to indicate that the method creates, configures, and initializes a new object managed by the Spring IoC container. Such methods can be used as in classes with the @Configuration annotation. Our IoC container will return a connection pool, a transaction manager, an exception translator (converts SQLEx exceptions to Spring-specific DataAccessException), a DSL context (a starting point for building all requests using the Fluent API), and managers to implement business logic and grids to display data.
JooqConfig.java

/*** Container IoC configuration* to implement dependency injection.*/package ru.ibase.fbjavaex.config;import org.springframework.context.annotation.Bean;import org.springframework.context.annotation.Configuration;import javax.sql.DataSource;import org.apache.commons.dbcp.BasicDataSource;import org.springframework.jdbc.datasource.DataSourceTransactionManager;import org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy;import org.jooq.impl.DataSourceConnectionProvider;import org.jooq.DSLContext;import org.jooq.impl.DefaultDSLContext;import org.jooq.impl.DefaultConfiguration;import org.jooq.SQLDialect;import org.jooq.impl.DefaultExecuteListenerProvider;import ru.ibase.fbjavaex.exception.ExceptionTranslator;import ru.ibase.fbjavaex.managers.*;import ru.ibase.fbjavaex.jqgrid.*;/*** Spring IOC container configuration class*/@Configurationpublic class JooqConfig {/*** Returns a pool of connections** @return*/@Bean(name = "dataSource")public DataSource getDataSource() {BasicDataSource dataSource = new BasicDataSource();// define the connection configurationdataSource.setUrl("jdbc:firebirdsql://localhost:3050/examples");dataSource.setDriverClassName("org.firebirdsql.jdbc.FBDriver");dataSource.setUsername("SYSDBA");dataSource.setPassword("masterkey");dataSource.setConnectionProperties("charSet=utf-8");return dataSource;}/*** Returns transaction manager** @return*/@Bean(name = "transactionManager")public DataSourceTransactionManager getTransactionManager() {return new DataSourceTransactionManager(getDataSource())}@Bean(name = "transactionAwareDataSource")public TransactionAwareDataSourceProxy getTransactionAwareDataSource() {return new TransactionAwareDataSourceProxy(getDataSource())}/*** Returns the connection provider** @return*/@Bean(name = "connectionProvider")public DataSourceConnectionProvider getConnectionProvider() {return new DataSourceConnectionProvider(getTransactionAwareDataSource());}/*** Returns exception translator** @return*/@Bean(name = "exceptionTranslator")public ExceptionTranslator getExceptionTranslator() {return new ExceptionTranslator();}/*** Returns the DSL configuration of the context** @return*/@Bean(name = "dslConfig")public org.jooq.Configuration getDslConfig() {DefaultConfiguration config = new DefaultConfiguration();// using the SQL DBMS dialect Firebirdconfig.setSQLDialect(SQLDialect.FIREBIRD);config.setConnectionProvider(getConnectionProvider());DefaultExecuteListenerProvider listenerProvider = new DefaultExecuteListenerProvider (getExceptionTranslator());config.setExecuteListenerProvider(listenerProvider);return config;}/*** Returns DSL context** @return*/@Bean(name = "dsl")public DSLContext getDsl() {org.jooq.Configuration config = this.getDslConfig();return new DefaultDSLContext(config);}/*** Returns the customer manager** @return*/@Bean(name = "customerManager")public CustomerManager getCustomerManager() {return new CustomerManager(); }/*** Returns grid with customers** @return*/@Bean(name = "customerGrid")public JqGridCustomer getCustomerGrid() {return new JqGridCustomer();}/*** Returns the product manager** @return*/@Bean(name = "productManager")public ProductManager getProductManager() {return new ProductManager();}/*** Returns product grid** @return*/@Bean(name = "productGrid")public JqGridProduct getProductGrid() {return new JqGridProduct();}/*** Returns invoice manager** @return*/@Bean(name = "invoiceManager")public InvoiceManager getInvoiceManager() {return new InvoiceManager();}/*** Returns grid with invoice headers** @return*/@Bean(name = "invoiceGrid")public JqGridInvoice getInvoiceGrid() {return new JqGridInvoice();}/*** Returns grid with invoice positions** @return*/@Bean(name = "invoiceLineGrid")public JqGridInvoiceLine getInvoiceLineGrid() {return new JqGridInvoiceLine();}/*** Returns the working period** @return*/@Bean(name = "workingPeriod")public WorkingPeriod getWorkingPeriod() {return new WorkingPeriod();}}

Building SQL queries using jOOQ

Before we look at the implementation of managers and grids, let’s look at how to work with the database through jOOQ. There will only be a short introduction about constructing queries here, you can find a complete documentation about this in the chapter sql-building jOOQ documentation.
The org.jooq.impl.DSL class is the main class from which you will create all jOOQ objects. It acts as a static factory for table expressions, column (or field) expressions, conditional expressions, and many other parts of the query.
DSLContext refers to the org.jooq.Configuration object, which configures the behavior of jOOQ, when running queries. Unlike a static DSL, DSLContext allows you to create SQL statements that are already "configured" and ready to be executed. In our application, DSLContext is created in the JooqConfig configuration class in the getDsl method. The configuration for the DSLContext is returned by the getDslConfig method. In this method, we have specified that we will use the Firebird SQL dialect, a connection provider (defines how we get a connection through JDBC) and a SQL query execution listener.
jOOQ comes with its own DSL (or Domain Specific Language), which emulates SQL in Java. This means that you can write SQL statements almost as if Java originally supported them, about as much as .NET in C# does with LINQ to SQL.
jOOQ uses informal BNF notation, which models a unified SQL dialect suitable for most RDBMSs. Unlike other, simpler frameworks that use the "Fluent API" or "chain method", the jOOQ-based BNF interface hierarchy does not allow bad query syntax.
Let’s look at a simple SQL query:

SELECT *FROM author aJOIN book b ON a.id = b.author_idWHERE a.year_of_birth > 1920AND a.first_name = 'Paulo'ORDER BY b.title

In jOOQ it will look like this :

Result<Record> result =dsl.select().from(AUTHOR.as("a")).join(BOOK.as("b")).on(a.ID.equal(b.AUTHOR_ID)).where(a.YEAR_OF_BIRTH.greaterThan(1920).and(a.FIRST_NAME.equal("Paulo"))).orderBy(b.TITLE).fetch();

The AUTHOR and BOOK classes describing the corresponding tables must be generated beforehand. The process of generating jOOQ classes from a given database schema was described above.
In this case, we have given the tables AUTHOR and BOOK an alias using the as construct. Without aliases, this query would look like this :

Result<Record> result =dsl.select().from(AUTHOR).join(BOOK).on(AUTHOR.ID.equal(BOOK.AUTHOR_ID)).where(AUTHOR.YEAR_OF_BIRTH.greaterThan(1920).and(AUTHOR.FIRST_NAME.equal("Paulo"))).orderBy(BOOK.TITLE).fetch();

Now let’s look at a more complex query using aggregate functions and grouping.

SELECT AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, COUNT(*)FROM AUTHORJOIN BOOK ON AUTHOR.ID = BOOK.AUTHOR_IDWHERE BOOK.LANGUAGE = 'DE'AND BOOK.PUBLISHED > '2008-01-01'GROUP BY AUTHOR.FIRST_NAME, AUTHOR.LAST_NAMEHAVING COUNT(*) > 5ORDER BY AUTHOR.LAST_NAME ASC NULLS FIRSTOFFSET 1 ROWSFETCH FIRST 2 ROWS ONLY

In jOOQ it would look like this :

dsl.select(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, count()).from(AUTHOR).join(BOOK).on(BOOK.AUTHOR_ID.equal(AUTHOR.ID)).where(BOOK.LANGUAGE.equal("DE")).and(BOOK.PUBLISHED.greaterThan("2008-01-01")).groupBy(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME).having(count().greaterThan(5)).orderBy(AUTHOR.LAST_NAME.asc().nullsFirst()).limit(2).offset(1).fetch();

Note the limit on the number of records returned will be generated according to the specified SQL dialect.In the example above, the FIREIRD_3_0 dialect was used. If FIREBIRD_2_5 or just FIREBIRD were specified, the ROWS sentence would be used instead of OFSET … FETCH.
You can assemble the query in parts. This allows you to change it dynamically, which you can use to change the sort order or add more filtering parameters.

SelectFinalStep<?> select= dsl.select().from(PRODUCT);SelectQuery<?> query = select.getQuery();switch (searchOper) {case "eq":query.addConditions(PRODUCT.NAME.eq(searchString));break;case "bw":query.addConditions(PRODUCT.NAME.startsWith(searchString));break;case "cn":query.addConditions(PRODUCT.NAME.contains(searchString));break;}switch (sOrd) {case "asc":query.addOrderBy(PRODUCT.NAME.asc());break;case "desc":query.addOrderBy(PRODUCT.NAME.desc());break;}return query.fetchMaps();

Named and unnamed parameters

By default, whenever you use string, date, and number literals in a query and substitute external variables, jOOQ binds that variable or literal through unnamed parameters. For example, the following expression in Java:

dsl.select().from(BOOK).where(BOOK.ID.equal(5)).and(BOOK.TITLE.equal("Animal Farm")).fetch();

Equivalent to the full record form :

dsl.select().from(BOOK).where(BOOK.ID.equal(val(5))).and(BOOK.TITLE.equal(val("Animal Farm"))).fetch();

and converted into a sql query :

SELECT *FROM BOOKWHERE BOOK.ID = ?AND BOOK.TITLE = ?

You do not need to worry about what index the corresponding parameter has, the values will automatically be bound to the desired parameter. If you need to change the value of a parameter, you can do so by selecting the desired parameter by the index number (indexing starts with 1).

Select<?> select =dsl.select().from(BOOK).where(BOOK.ID.equal(5)).and(BOOK.TITLE.equal("Animal Farm"));Param<?> param = select.getParam("2");Param.setValue("Animals as Leaders");

Another way to assign a new value to a parameter is to call the bind method.

Query query1 =dsl.select().from(AUTHOR).where(LAST_NAME.equal("Poe"));query1.bind(1, "Orwell");

Additionally, jOOQ supports named parameters. In this case you have to create them explicitly using org.jooq.Param.

// Create a query with a named parameter. You can then use that name for// accessing the parameter againQuery query1 =dsl.select().from(AUTHOR).where(LAST_NAME.equal(param("lastName", "Poe")));Param<?> param1 = query.getParam("lastName");// Or, keep a reference to the typed parameter in order not to lose the <T> type information:Param<String> param2 = param("lastName", "Poe");Query query2 =dsl.select().from(AUTHOR).where(LAST_NAME.equal(param2));// You can now change the bind value directly on the Param reference:param2.setValue("Orwell");

Another way to assign a new value to a parameter is to call the bind method.

// Or, with named parametersQuery query2 =dsl.select().from(AUTHOR).where(LAST_NAME.equal(param("lastName", "Poe")));query2.bind("lastName", "Orwell");

Returning values from selective queries

jOOQ provides many ways to extract data from SQL queries. We won’t list all of them here, you can read more about them in the chapter Fetching jOOQ documentation. We’ll use fetchMaps in our example, which is useful for serializing the result in JSON.

Other types of requests

Now let’s see what other types of queries look like. For example, the following query to insert a record :

INSERT INTO AUTHOR(ID, FIRST_NAME, LAST_NAME)VALUES (100, 'Hermann', 'Hesse');

in jOOQ will look like this :

dsl.insertInto(AUTHOR, AUTHOR.ID, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME).values(100, "Hermann", "Hesse").execute();

This is the request to update the record :

UPDATE AUTHORSET FIRST_NAME = 'Hermann', LAST_NAME = 'HesseWHERE ID = 3;

using jOOQ is written as follows :

dsl.update(AUTHOR).set(AUTHOR.FIRST_NAME, "Hermann").set(AUTHOR.LAST_NAME, "Hesse").where(AUTHOR.ID.equal(3)).execute();

Record deletion request :

DELETE FROM AUTHORWHERE ID = 100;

looks like this :

dsl.delete(AUTHOR).where(AUTHOR.ID.equal(100)).execute();

You can also build more complex modifying queries in jOOQ, such as MERGE
A big advantage of jOOQ is the support for working with stored procedures. Stored procedures are extracted into a *.Routines.* package and then you can conveniently work with them, such as the following code in Java:

int invoiceId = dsl.nextval(GEN_INVOICE_ID).intValue();spAddInvoice(dsl.configuration(), invoiceId, customerId, invoiceDate);

Equivalent to getting the next generator value with a SQL query :

SELECT NEXT VALUE FOR GEN_INVOICE_ID FROM RDB$DATABASE

and the subsequent call of the stored procedure :

EXECUTE PROCEDURE SP_ADD_INVOICE(:INVOICE_ID, :CUSTOMER_ID, :INVOICE_DATE

jOOQ also gives you tools to build simple DDL queries, but we won’t cover them here.

Transaction handling

By default, jOOQ works in auto-confirmation mode, i.e. a new transaction is started for each SQL statement, and if there are no errors during SQL statement execution, the transaction is confirmed, otherwise it is rolled back. The default transaction is used with the READ_WRITE READ_COMMITED REC_VERSION WAIT parameters. That is the same as used by JDBC driver. You can change default isolation mode via connection pool parameters (see BasicDataSource.setDefaultTransactionIsolation in getDataSource method of JooqConfig configuration class).
In jOOQ there are several ways to manage transactions explicitly. Since we are developing an application using the Spring Framework, we will use a transaction manager defined in the configuration (JooqConfig). You can get the transaction manager by declaring the txMgr property in the class as follows :

@Autowiredprivate DataSourceTransactionManager txMgr;

In this case, a typical transaction script looks like this :

TransactionStatus tx = txMgr.getTransaction(new DefaultTransactionDefinition());try {// actions within a transactionfor (int i = 0; i < 2; i++)dsl.insertInto(BOOK).set(BOOK.ID, 5).set(BOOK.AUTHOR_ID, 1).set(BOOK.TITLE, "Book 5").execute();// transaction committxMgr.commit(tx);}catch (DataAccessException e) {// rollback the transactiontxMgr.rolback(tx);}

However, Spring allows such a scenario to be accomplished much more easily by using the @Transactional annotation specified before the class method. In this case, all actions performed in the method will be wrapped by the transaction.

/*** Customer deletion** @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();}

The propagation parameter defines how transactions will be handled if our method is called from an external transaction.

  • Propagation.REQUIRED – execute in an existing transaction, if there is one, otherwise create a new one.
  • Propagation.MANDATORY – execute in an existing transaction if there is one, otherwise generate an exception.
  • Propagation.SUPPORTS – execute in an existing transaction if there is one, otherwise execute outside of a transaction.
  • Propagation.NOT_SUPPORTED – always execute outside of a transaction. If there is an existing one, it will be stopped.
  • Propagation.REQUIRES_NEW – always execute in a new independent transaction. If there is an existing one, it will be stopped until the new transaction finishes executing.
  • Propagation.NESTED – if there is a current transaction, it will be executed in a new, so called nested transaction. If the nested transaction is canceled, it will not affect the external transaction; if the external transaction is canceled, the nested transaction will also be canceled. If there is no current transaction, a new transaction is simply created.
  • Propagation.NEVER – always execute outside of transaction, generate exception if existing transaction exists.

The isolation parameter specifies the isolation mode of the transaction. There are 5 supported values: DEFAULT, READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE. If the DEFAULT parameter value is specified, the default mode of transaction isolation will be used. Other isolation modes are taken from SQL standard. In Firebird the isolation modes are somewhat different and only READ_COMMITED mode meets all the criteria. Thus, in JDBC READ_COMMITTED mode corresponds to read_committed in Firebird, REPEATABLE_READ – concurrency (Snapshot), and SERIALIZABLE – consistency. In addition to isolation mode, Firebird supports additional transaction parameters (NO RECORD_VERSION and RECORD_VERSION, WAIT and NO WAIT). You can customize the mapping of standard isolation levels to Firebird transaction parameters by setting the properties of the JDBC connection (see more at Jaybird 2.1 JDBC driver Java Programmer’s Manual in the Using transactions chapter). If your transaction works with more than 1 request, we recommend the REPEATABLE_READ isolation mode to ensure data consistency.
In the @Transactional annotation, you can set whether the transaction is read-only using the readOnly property. By default, the transaction is in read-write mode.

Writing application code

We will display our application data using a JavaScript component jqGrid jqGrid is currently distributed under a commercial license, but is free for non-commercial purposes. You can use the fork instead. free-jqGrid To display the data in this grid and the page navigation elements we need to return the data in JSON format whose structure looks like this

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

where

  • total – total number of pages;
  • page – number of the current page;
  • records – total number of records;
  • rows – array of records on the current page.

Let’s create a class that describes this structure :
JqGridData.java

package ru.ibase.fbjavaex.jqgrid;import java.util.List;import java.util.Map;/*** A class that describes the structure used by jqGrid* Designed for serialization in JSON** @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 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 entries** @return*/public int getRecords() {return records;}/*** Returns a list of records* This is an array to display in the grid** @return*/public List<Map<String, Object> > getRows() {return rows;}}

Now let’s write an abstract class that will return the above structure depending on search and sort conditions. This class will be the ancestor of classes that return similar structures for specific entities.
JqGrid.java

/** 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 total number of records** @return*/public abstract int getCountRecord();/*** Returns a structure for serialization in JSON** @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 on the page** @return*/public int getLimit() {return this.limit;}/*** Returns the offset before retrieving the first record** @return*/public int getOffset() {return this.offset;}/*** Returns the name of the field for sorting** @return*/public String getIdx() {return this.sIdx;}/*** Returns the sort order** @return*/public String getOrd() {return this.sOrd;}/*** Returns current page number** @return*/public int getPageNo() {return this.pageNo;}/*** Returns an array of entries as a list of cards** @return*/public abstract List<Map<String, Object> > getRecords();/*** Returns the search field** @return*/public String getSearchField() {return this.searchField;}/*** Returns searchField** @return*/public String getSearchString() {return this.searchString;}/*** Returns the search operation** @return*/public String getSearchOper() {return this.searchOper;}/*** Sets a limit on the number of output 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 sorting** @param sIdx* @param sOrd*/public void setOrderBy(String sIdx, String sOrd) {this.sIdx = sIdx;this.sOrd = sOrd;}/*** Sets current page number** @param pageNo*/public void setPageNo(int pageNo) {this.pageNo = pageNo;this.offset = (pageNo - 1) * this.limit;}/*** Sets 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, this class contains a DSLContext dsl property that will be used to build data sampling queries using jOOQ.

Creating directories

Now we can start creating directories. We will describe the process of creating directories using the customer directory as an example. The product guide is created in a similar way, and if you want you can view its source code by following the link at the end of the article.
First let’s implement a class to work with jqGrid, it will be inherited from our abstract class ru.ibase.fbjavaex.jqgrid.JqGrid described above. It has a search and multi-directional sorting by the NAME field. The source code listing will provide explanatory comments.
JqGridCustomer.java

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*/@Overridepublic int getCountRecord() {// query that returns the number of recordsSelectFinalStep<?> select= dsl.selectCount().from(CUSTOMER);SelectQuery<?> query = select.getQuery();// if we search, we add a search conditionif (this.searchFlag) {makeSearchCondition(query);}// recover the quantityreturn (int) query.fetch().getValue(0, 0);}/*** Returns grid entries** @return*/@Overridepublic List<Map<String, Object> > getRecords() {// Basic selection requestSelectFinalStep<?> select =dsl.select().from(CUSTOMER);SelectQuery<?> query = select.getQuery();// if we search, we add a search conditionif (this.searchFlag) {makeSearchCondition(query);}// set sorting orderswitch (this.sOrd) {case "asc":query.addOrderBy(CUSTOMER.NAME.asc());break;case "desc":query.addOrderBy(CUSTOMER.NAME.desc());break;}// limit the number of recordsif (this.limit != 0) {query.addLimit(this.limit);}// offsetif (this.offset != 0) {query.addOffset(this.offset);}// return an array of mapsreturn query.fetchMaps();}}

We will add, edit and delete customer through the class CustomerManager, which is a kind of business layer between the corresponding controller and the database. We will perform all operations in this layer in a transaction with Snapshot isolation level.
CustomerManager.java

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 the 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();}/*** Customer Deletion** @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();}}

Now let’s move on to writing a controller. The controller classes start with the annotation Controller To define controller actions, you must add the @RequestMapping annotation before the method and specify the route in it where the controller action will be invoked. The route is specified in the value parameter. In parameter method you can specify HTTP request method (PUT, GET, POST, DELETE). The index method is our controller’s entry point and is responsible for displaying the JSP page (view). This page will contain the markup for the grid, toolbar and navigation.
The data for display is loaded asynchronously by the jqGrid component (the /customer/getdata route). The getData method is associated with this route. The method contains an additional @ResponseBody annotation, which says that our method returns an object for serialization into one of the formats. The @RequestMapping annotation specifies the parameter produces = MediaType.APPLICATION_JSON, which indicates that the returned object will be serialized into JSON format. It is in this method that we work with the JqGridCustomer class described above. The @RequestParam annotation allows the parameter value to be extracted from an HTTP request. This class method works with GET requests. The value parameter in the @RequestParam annotation specifies the name of the HTTP request parameter to extract. The required parameter specifies whether the HTTP request parameter is required. The defaultValue parameter specifies the default value that will be substituted if the HHTP parameter is missing.
The addCustomer method is intended to add a new customer. It is connected with the /customer/create route, and unlike the previous method works with a POST request. The method returns {success: true} in case of successful addition, and an object with an error text in case of error. This method works with the class of the business layer CustomerManager.
The editCustomer method is connected with the /customer/edit route and is intended for editing the customer. The deleteCustomer method is connected to the /customer/delete route and is used to delete the customer.
CustomerController.java

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*/@Controllerpublic class CustomerController {@Autowired(required = true)private JqGridCustomer customerGrid;@Autowired(required = true)private CustomerManager customerManager;/*** Default action* Returns the name of the JSP page (view) to display** @param map* @return JSP template name*/@RequestMapping(value = "/customer/", method = RequestMethod.GET)public String index(ModelMap map) {return "customer";}/*** Returns data in JSON format for jqGrid** @param rows number of rows per page* @param page page number* @param sIdx field for sorting* @param sOrd sort order* @param search whether to search* @param searchField search field* @param searchString search value* @param searchOper search operation* @return JSON for jqGrid*/@RequestMapping(value = "/customer/getdata", method = RequestMethod.GET, produces = MediaType.APPLICATION_JSON)@ResponseBodypublic JqGridData getData(// number of records on the page@RequestParam(value = "rows", required = false, defaultValue = "20") int rows, //number of the current page@RequestParam(value = "page", required = false, defaultValue = "1") int page, // field for sorting@RequestParam(value = "sidx", required = false, defaultValue = "") String sIdx, // sorting direction@RequestParam(value = "sord", required = false, defaultValue = "asc") String sOrd, // is the search being performed@RequestParam(value = "_search", required = false, defaultValue = "false") Boolean search, // search field@RequestParam(value = "searchField", required = false, defaultValue = ") String searchField, // search value@RequestParam(value = "searchString", required = false, defaultValue = "") String searchString, // search operation@RequestParam(value = "searchOper", required = false, defaultValue = ") String searchOper, // filter@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)@ResponseBodypublic 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)@ResponseBodypublic 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)@ResponseBodypublic 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;}}

The JSP page for displaying the customer directory does not contain anything special: a layout with the main parts of the page, a table for displaying the grid and a block for displaying the navigation bar. The JSP templates are not very advanced, so you can replace them with other systems that support inheritance. The file ../jspf/head.jspf contains common scripts and styles for all pages of the site, and the file ../jspf/menu.jspf the main menu of the site. We will not give their code, it’s pretty simple and if you want you can look it in the source code of the project.
Customers.jsp

<%@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> 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 - Example Spring MVC application using Firebird and jOOQ</p></footer></div><script type="text/javascript">$(document).ready(function () {JqGridCustomer({baseAddress: '${cp}'});});</script></body>;</html>

The main client-side logic is concentrated in the JavaScript module /resources/js/jqGridCustomer.js
jqGridCustomer.js

var JqGridCustomer = (function ($) {return function (options) {var jqGridCustomer = {dbGrid: null, // optionsoptions: $.extend({baseAddress: null, showEditorPanel: true}, options), //returns the modelgetColModel: function () {return [{label: 'Id', // signaturename: 'CUSTOMER_ID', // field namekey: true, // sign of the key fieldhidden: true // hidden}, {label: 'Name', // field labelname: 'NAME', // field namewidth: 240, // widthsortable: true, // sorting is allowededitable: true, // editing is allowededittype: "text", // field type in the editorsearch: true, // search is allowedsearchoptions: {sopt: ['eq', 'bw', 'cn'] // allowed search operators}, editoptions: {size: 30, maxlength: 60}, // size and maximum length for the input fieldeditrules: {required: true} // says the field is mandatory}, {label: 'Address', name: 'ADDRESS', { width: 300, sortable: false, //disallow sortingeditable: true, // editablesearch: false, //disallow searcheditype: "textare", // memo fieldeditoptions: {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}}];}, // initialize gridinitGrid: function () {// url to get datavar url = jqGridCustomer.options.baseAddress + '/customer/getdata';jqGridCustomer.dbGrid = $("#jqGridCustomer").jqGrid({url: url, datatype: "json", // format for receiving datamtype: "GET", // type of http requestcolModel: jqGridCustomer.getColModel(), rowNum: 500, // number of displayed rowsloadonce: false, // load only oncesortname: 'NAME', // default sorting by the NAME columnsortorder: "asc", // sorting orderwidth: window.innerWidth - 80, // width of gridheight: 500, // height of gridviewrecords: true, // display number of recordsguiStyle: "bootstrap", iconSet: "fontAwesome", caption: "Customers", // caption to grid// element for displaying the navigationpager: 'jqPagerCustomer'});}, // editing optionsgetEditOptions: function () {return {url: jqGridCustomer.options.baseAddress + '/customer/edit', reloadAfterSubmit: true, closeOnEscape: true, closeAfterEdit: true, drag: true, width: 400, afterSubmit: jqGridCustomer.afterSubmit, editData: {// pass the key field in addition to the values from the formCUSTOMER_ID: function () {// get the current rowvar selectedRow = jqGridCustomer.dbGrid.getGridParam("selrow");// get the value of the field we are interested invar value = jqGridCustomer.dbGrid.getCell(selectedRow, 'CUSTOMER_ID');return value;}}};}, // add optionsgetAddOptions: function () {return {url: jqGridCustomer.options.baseAddress + '/customer/create', reloadAfterSubmit: true, closeOnEscape: true, closeAfterAdd: true, drag: true, width: 400, afterSubmit: jqGridCustomer.afterSubmit};}, // delete optionsgetDeleteOptions: function () {return {url: jqGridCustomer.options.baseAddress + '/customer/delete', reloadAfterSubmit: true, closeOnEscape: true, closeAfterDelete: true, drag: true, msg: "Delete selected customer?afterSubmit: jqGridCustomer.afterSubmit, delData: {// pass the key fieldCUSTOMER_ID: function () {var selectedRow = jqGridCustomer.dbGrid.getGridParam("selrow");var value = jqGridCustomer.dbGrid.getCell(selectedRow, 'CUSTOMER_ID');return value;}}};}, // Initializing the navigation panel along with the edit dialogsinitPagerWithEditors: function () {jqGridCustomer.dbGrid.jqGrid('navGrid', '#jqPagerCustomer', {// buttonssearch: true, // searchadd: true, // addedit: true, // editdel: true, // deleteview: true, // view recordrefresh: true, // updating//button signaturesearchtext: "Search", addtext: "Add", edittext: "Edit", deltext: "Delete", viewtext: "View", viewtitle: "Selected entry", refreshtext: "refresh"}, jqGridCustomer.getEditOptions(), jqGridCustomer.getAddOptions(), jqGridCustomer.getDeleteOptions());}, // Initializing the navigation bar together without the edit dialogsinitPagerWithoutEditors: function () {jqGridCustomer.dbGrid.jqGrid('navGrid', '#jqPagerCustomer', {// buttonssearch: true, // searchadd: false, // addedit: false, // editdel: false, // deletingview: false, // view recordrefresh: true, // updating// button signaturesearchtext: "Search", viewtext: "view", viewtitle: "Selected record", refreshtext: "refresh"});}, // initialization of the navigation barinitPager: function () {if (jqGridCustomer.options.showEditorPanel) {jqGridCustomer.initPagerWithEditors();} else {jqGridCustomer.initPagerWithoutEditors();}}, // initializationinit: function () {jqGridCustomer.initGrid();jqGridCustomer.initPager();}, // handler of the forms processing results (operations)afterSubmit: function (response, postdata) {var responseData = response.responseJSON;// check the result for error messagesif (responseData.hasOwnProperty("error")) {if (responseData.error.length) {return [false, responseData.error];}} else {// if no error is returned, update the grid$(this).jqGrid('setGridParam', {datatype: 'json'}).trigger('reloadGrid');}return [true, "", 0];}};jqGridCustomer.init();return jqGridCustomer;};})(jQuery);

The jqGrid grid is created in the initGrid method and is attached to the html element with the jqGridCustomer identifier.The description of the grid columns (columns) is returned by the getColModel method. Each column in the jqGrid has quite a few possible properties. The source code includes comments explaining the properties of the columns. You can read more about configuring the jqGrid column model in the documentation of the jqGrid project under ColModel API
A navigation panel can be created with or without edit buttons (initPagerWithEditors and initPagerWithoutEditors methods, respectively). The panel’s constructor attaches it to the element with the jqPagerCustomer identifier. The options for creating a navigation panel are described in Navigator jqGrid documentation.
The getEditOptions, getAddOptions, getDeleteOptions functions return the edit, add and delete dialog options respectively. The url property specifies to which address the data will be sent after the OK button is clicked in the dialog box. The afterSubmit property is the event that occurs after the data is sent to the server and a response is received from it. The afterSubmit method checks if our controller returned an error. If there is no error, the grid is updated, otherwise the error is reported to the user. Pay attention to the editData property. It allows you to set the values of additional fields that are not part of the edit dialog. The thing is, the edit dialogs don’t include the values of the hidden fields, and you don’t really want to display the automatically generated keys.

Creating magazines

Unlike directories, journals contain a fairly large number of entries and are frequently updated. Most journals contain a field with the date the document was created. In order to reduce the amount of selectable data, it is customary to introduce such a notion as a work period in order to reduce the amount of data transmitted to the client. A work period is a date range within which working documents are required. The working period is described by the WorkingPeriod class. This class is created through the workingPeriod bin in the configuration class ru.ibase.fbjavaex.config.JooqConfig.
WorkingPeriod.java

package ru.ibase.fbjavaex.config;import java.sql.Timestamp;import java.time.LocalDateTime;/*** Work period** @author Simonov Denis*/public class WorkingPeriod {private Timestamp beginDate;{ private Timestamp endDate;/*** Constructor*/WorkingPeriod() {// in real applications is calculated from the current datethis.beginDate = Timestamp.valueOf("2015-06-01 00:00:00");this.endDate = Timestamp.valueOf(LocalDateTime.now().plusDays(1));}/*** Returns the start date of the work period** @return*/public Timestamp getBeginDate() {return this.beginDate;}/*** Returns the end date of the work period** @return*/public Timestamp getEndDate() {return this.endDate;}/*** Setting the start date of the work period** @param value*/public void setBeginDate(Timestamp value) {this.beginDate = value;}/*** Set the end date of the work period** @param value*/public void setEndDate(Timestamp value) {this.endDate = value;}/*** Setting a work period** @param beginDate* @param endDate*/public void setRangeDate(Timestamp beginDate, Timestamp endDate) {this.beginDate = beginDate;this.endDate = endDate;}}

In our application there will be one log "Invoices". Invoice – consists of a header, where the general attributes are described (number, date, customer…), and invoice items (product name, quantity, cost, etc.). The invoice header is displayed in the main grid, and the items can be viewed in the detail grid, which is expanded by clicking on the "+" sign on the desired document.
Let’s implement a class for viewing invoice caps via jqGrid, it will be inherited from our abstract class ru.ibase.fbjavaex.jqgrid.JqGrid described above. It has the ability to search for the name of the customer and the date of invoice. In addition, this class supports sorting by date in both directions.
JqGridInvoice.java

package ru.ibase.fbjavaex.jqgrid;import java.sql.*;import org.jooq.*;import java.util.List;import java.util.Map;import org.springframework.beans.factory.annotation.Autowired;import ru.ibase.fbjavaex.config.WorkingPeriod;import static ru.ibase.fbjavaex.exampledb.Tables.INVOICE;import static ru.ibase.fbjavaex.exampledb.Tables.CUSTOMER;/*** Invoice log grid handler** @author Simonov Denis*/public class JqGridInvoice extends JqGrid {@Autowired(required = true)private WorkingPeriod workingPeriod;/*** Adding a search condition** @param query*/private void makeSearchCondition(SelectQuery<?> query) {// adding a search condition to a query, if a search is performed// different operators are available for different fields// comparisons when searchingif (this.searchString.isEmpty()) {return;}if (this.searchField.equals("CUSTOMER_NAME")) {switch (this.searchOper) {case "eq";: // equalquery.addConditions(CUSTOMER.NAME.eq(this.searchString));break;case "bw": // starting withquery.addConditions(CUSTOMER.NAME.startsWith(this.searchString));break;case "cn": // containingquery.addConditions(CUSTOMER.NAME.contains(this.searchString));break;}}if (this.searchField.equals("INVOICE_DATE")) {Timestamp dateValue = Timestamp.valueOf(this.searchString);switch (this.searchOper) {case "eq": // =query.addConditions(INVOICE.INVOICE_DATE.eq(dateValue));break;case "lt": // <query.addConditions(INVOICE.INVOICE_DATE.lt(dateValue));break;case "le": // <=query.addConditions(INVOICE.INVOICE_DATE.le(dateValue));break;case "gt": // >query.addConditions(INVOICE.INVOICE_DATE.gt(dateValue));break;case "ge": // > =query.addConditions(INVOICE.INVOICE_DATE.ge(dateValue));break;}}}/*** Returns the total number of records** @return*/@Overridepublic int getCountRecord() { SelectFinalStep<?> select= dsl.selectCount().from(INVOICE).where(INVOICE.INVOICE_DATE.between(this.workingPeriod.getBeginDate(), this.workingPeriod.getEndDate()));SelectQuery<?> query = select.getQuery();if (this.searchFlag) {makeSearchCondition(query);}return (int) query.fetch().getValue(0, 0);}/*** Returns a list of invoices** @return*/@Overridepublic List<Map<String, Object> > getRecords() {SelectFinalStep<?> select= dsl.select(INVOICE.INVOICE_ID, INVOICE.CUSTOMER_ID, CUSTOMER.NAME.as("CUSTOMER_NAME"), INVOICE.INVOICE_DATE, INVOICE.PAID, INVOICE.TOTAL_SALE).from(INVOICE).innerJoin(CUSTOMER).on(CUSTOMER.CUSTOMER_ID.eq(INVOICE.CUSTOMER_ID)).where(INVOICE.INVOICE_DATE.between(this.workingPeriod.getBeginDate(), this.workingPeriod.getEndDate()));SelectQuery<?query = select.getQuery();// adding a search conditionif (this.searchFlag) {makeSearchCondition(query);}// Adding sortingif (this.sIdx.equals("INVOICE_DATE")) {switch (this.sOrd) {case "asc":query.addOrderBy(INVOICE.INVOICE_DATE.asc());break;case "desc":query.addOrderBy(INVOICE.INVOICE_DATE.desc());break;}}// add limit and offsetif (this.limit != 0) {query.addLimit(this.limit);}if (this.offset != 0) {query.addOffset(this.offset);}return query.fetchMaps();}}

The class for viewing invoice positions through jqGrid is somewhat simpler. First, its entries are filtered by the invoice header code, and second, we will not implement search and custom sorting in it.
JqGridInvoiceLine.java

package ru.ibase.fbjavaex.jqgrid;import org.jooq.*;import java.util.List;import java.util.Map;import static ru.ibase.fbjavaex.exampledb.Tables.INVOICE_LINE;import static ru.ibase.fbjavaex.exampledb.Tables.PRODUCT;/*** Grid handler for invoice log items** @author Simonov Denis*/public class JqGridInvoiceLine extends JqGrid {private int invoiceId;public int getInvoiceId() {return this.invoiceId;}public void setInvoiceId(int invoiceId) {this.invoiceId = invoiceId;}/*** Returns the total number of records** @return*/@Overridepublic int getCountRecord() { SelectFinalStep<?> select= dsl.selectCount().from(INVOICE_LINE).where(INVOICE_LINE.INVOICE_ID.eq(this.invoiceId));SelectQuery<?> query = select.getQuery();return (int) query.fetch().getValue(0, 0);}/*** Returns invoice positions** @return*/@Overridepublic List<Map<String, Object>> GetRecords() {SelectFinalStep<?> select= dsl.select(INVOICE_LINE.INVOICE_LINE_ID, INVOICE_LINE.INVOICE_ID, INVOICE_LINE.PRODUCT_ID, PRODUCT.NAME.as("PRODUCT_NAME"), INVOICE_LINE.QUANTITY, INVOICE_LINE.SALE_PRICE, INVOICE_LINE.SALE_PRICE.mul(INVOICE_LINE.QUANTITY).as("TOTAL")).from(INVOICE_LINE).innerJoin(PRODUCT).on(PRODUCT.PRODUCT_ID.eq(INVOICE_LINE.PRODUCT_ID)).where(INVOICE_LINE.INVOICE_ID.eq(this.invoiceId));SelectQuery<?> query = select.getQuery();return query.fetchMaps();}}

We will add, edit, delete invoices (and their positions) and pay them through the class ru.ibase.fbjavaex.managers.InvoiceManager, which is a kind of business layer. We will perform all operations in this layer in a transaction with Snapshot isolation level. In this class, all database operations are performed using stored procedure calls (this is not mandatory, just one option shown).
InvoiceManager.java

package ru.ibase.fbjavaex.managers;import java.sql.Timestamp;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.Sequences.GEN_INVOICE_ID;import static ru.ibase.fbjavaex.exampledb.Routines.spAddInvoice;import static ru.ibase.fbjavaex.exampledb.Routines.spEditInvoice;import static ru.ibase.fbjavaex.exampledb.Routines.spPayForInovice;import static ru.ibase.fbjavaex.exampledb.Routines.spDeleteInvoice;import static ru.ibase.fbjavaex.exampledb.Routines.spAddInvoiceLine;import static ru.ibase.fbjavaex.exampledb.Routines.spEditInvoiceLine;import static ru.ibase.fbjavaex.exampledb.Routines.spDeleteInvoiceLine;/*** Invoice Manager** @author Simonov Denis*/public class InvoiceManager {@Autowired(required = true)private DSLContext dsl;/*** Adding an invoice header** @param CustomerID* @param invoiceDate*/@Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.REPEATABLE_READ)public void create(Integer customerId, Timestamp invoiceDate) {int invoiceId = this.dsl.nextval(GEN_INVOICE_ID).intValue();spAddInvoice(this.dsl.configuration(), invoiceId, customerId, invoiceDate);}/*** Edit Invoice** @param InvoiceID* @param customerId* @param invoiceDate*/@Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.REPEATABLE_READ)public void edit(Integer invoiceId, Integer customerId, Timestamp invoiceDate) {spEditInvoice(this.dsl.configuration(), invoiceId, customerId, invoiceDate);}/*** Invoice Payment** @param InvoiceID*/@Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.REPEATABLE_READ)public void pay(Integer invoiceId) {spPayForInovice(this.dsl.configuration(), invoiceId);}/*** Deleting an invoice** @param InvoiceID*/@Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.REPEATABLE_READ)public void delete(Integer invoiceId) {spDeleteInvoice(this.dsl.configuration(), invoiceId);}/*** Adding an invoice item** @param InvoiceID* @param productId* @param quantity*/@Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.REPEATABLE_READ)public void addInvoiceLine(Integer invoiceId, Integer productId, Integer quantity) {spAddInvoiceLine(this.dsl.configuration(), invoiceId, productId, quantity);}/*** Edit invoice position** @param invoiceLineId* @param quantity*/@Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.REPEATABLE_READ)public void editInvoiceLine(Integer invoiceLineId, Integer quantity) {spEditInvoiceLine(this.dsl.configuration(), invoiceLineId, quantity);}/*** Deleting an invoice item** @param invoiceLineId*/@Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.REPEATABLE_READ)public void deleteInvoiceLine(Integer invoiceLineId) {spDeleteInvoiceLine(this.dsl.configuration(), invoiceLineId);}}

Now let’s move on to writing a controller. Our controller’s input point will be the index method, which is responsible for displaying the JSP page (view). This page contains the markup for the grid, toolbar and navigation.
Data for displaying invoice caps is loaded asynchronously by the jqGrid component (/invoice/getdata route). The getData method (similar to directories) is associated with this route. The invoice positions are returned by the getDetailData method (/invoice/getdetaildata route). The code of the invoice on which the detail grid was expanded is passed to this method. The addInvoice, editInvoice, payInvoice, deleteInvoice methods add, edit, pay and delete the invoice. The addInvoiceLine, editInvoiceLine, deleteInvoiceLine methods add, edit and delete the invoice position.
InvoiceController.java

package ru.ibase.fbjavaex.controllers;import java.sql.Timestamp;import java.util.HashMap;import java.util.Map;import java.util.Date;import java.text.ParseException;import java.text.SimpleDateFormat;import java.beans.PropertyEditorSupport;import javax.ws.rs.core.MediaType;import org.springframework.beans.factory.annotation.Autowired;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.RequestParam;import org.springframework.web.bind.annotation.ResponseBody;import org.springframework.web.bind.annotation.InitBinder;import org.springframework.web.bind.WebDataBinder;import ru.ibase.fbjavaex.jqgrid.JqGridInvoice;import ru.ibase.fbjavaex.jqgrid.JqGridInvoiceLine;import ru.ibase.fbjavaex.managers.InvoiceManager;import ru.ibase.fbjavaex.jqgrid.JqGridData;/*** Invoice Controller** @author Simonov Denis*/@Controllerpublic class InvoiceController {@Autowired(required = true)private JqGridInvoice InvoiceGrid;@Autowired(required = true)private JqGridInvoiceLine invoiceLineGrid;@Autowired(required = true)private InvoiceManager invoiceManager;/*** Describes how a string is converted to a date* from HTTP request input parameters** @param binder*/@InitBinderpublic void initBinder(WebDataBinder binder) {binder.registerCustomEditor(Timestamp.class, new PropertyEditorSupport() {@Overridepublic void setAsText(String value) {try {if ((value == null) || (value.isEmpty())) {setValue(null);} else {Date parsedDate = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss").parse(value);setValue(new Timestamp(parsedDate.getTime()))}} catch (ParseException e) {throw new java.lang.IllegalArgumentException(value);}}});}/*** Default action* Returns the name of the JSP page (view) to display** @param map* @return JSP page name*/@RequestMapping(value = "/invoice/", method = RequestMethod.GET)public String index(ModelMap map) {return "invoice";}/*** Returns list of invoices in JSON format for jqGrid** @param rows number of records per page* @param page number of current page* @param sIdx sorting field* @param sOrd sort order* @param search search flag* @param searchField search field* @param searchString search value* @param searchOper comparison operation* @param filters filter* @return*/@RequestMapping(value = "/invoice/getdata", method = RequestMethod.GET, produces = MediaType.APPLICATION_JSON)@ResponseBodypublic JqGridData getData(@RequestParam(value = "rows", required = false, defaultValue = "20") int rows, @RequestParam(value = "page", required = false, defaultValue = "1") int page, @RequestParam(value = "sidx", required = false, defaultValue = "") String sIdx, @RequestParam(value = "sord", required = false, defaultValue = "asc") String sOrd, @RequestParam(value = "_search", required = false, defaultValue = "false") Boolean search, @RequestParam(value = "searchField", required = false, defaultValue = "") String searchField, @RequestParam(value = "searchString", required = false, defaultValue = "") String searchString, @RequestParam(value = "searchOper", required = false, defaultValue = "") String searchOper, @RequestParam(value = "filters", required = false, defaultValue = "") String filters) {if (search) {invoiceGrid.setSearchCondition(searchField, searchString, searchOper);}invoiceGrid.setLimit(rows);invoiceGrid.setPageNo(page);invoiceGrid.setOrderBy(sIdx, sOrd);return invoiceGrid.getJqGridData();}/*** Adds an invoice** @param CustomerID customer code* @param InvoiceDate invoice date* @return*/@RequestMapping(value = "/invoice/create", method = RequestMethod.POST, produces = MediaType.APPLICATION_JSON)@ResponseBodypublic Map<String, Object> addInvoice(@RequestParam(value = "CUSTOMER_ID", required = true, defaultValue = "0") Integer customerId, @RequestParam(value = "INVOICE_DATE", required = false, defaultValue = "") Timestamp invoiceDate) {Map<String, Object> map = new HashMap<> ();try {invoiceManager.create(customerId, invoiceDate);map.put("success", true);} catch (Exception ex) {map.put("error", ex.getMessage());}return map;}/*** Edits invoice** @param InvoiceID invoice code* @param CustomerID customer code* @param InvoiceDate invoice date* @return*/@RequestMapping(value = "/invoice/edit", method = RequestMethod.POST, produces = MediaType.APPLICATION_JSON)@ResponseBodypublic Map<String, Object> editInvoice(@RequestParam(value = "INVOICE_ID", required = true, defaultValue = "0") Integer invoiceId, @RequestParam(value = "CUSTOMER_ID", required = true, defaultValue = "0") Integer customerId, @RequestParam(value = "INVOICE_DATE", required = false, defaultValue = "") Timestamp invoiceDate) {Map<String, Object> map = new HashMap<> ();try {invoiceManager.edit(invoiceId, customerId, invoiceDate);map.put("success", true);} catch (Exception ex) {map.put("error", ex.getMessage());}return map;}/*** Pays the invoice** @param InvoiceID invoice code* @return*/@RequestMapping(value = "/invoice/pay", method = RequestMethod.POST, produces = MediaType.APPLICATION_JSON)@ResponseBodypublic Map<String, Object> payInvoice(@RequestParam(value = "INVOICE_ID", required = true, defaultValue = "0") Integer invoiceId) {Map<String, Object> map = new HashMap<> ();try {invoiceManager.pay(invoiceId);map.put("success", true);} catch (Exception ex) {map.put("error", ex.getMessage());}return map;}/*** Deletes Invoice** @param InvoiceID invoice code* @return*/@RequestMapping(value = "/invoice/delete", method = RequestMethod.POST, produces = MediaType.APPLICATION_JSON)@ResponseBodypublic Map<String, Object> deleteInvoice(@RequestParam(value = "INVOICE_ID", required = true, defaultValue = "0") Integer invoiceId) {Map<String, Object> map = new HashMap<> ();try {invoiceManager.delete(invoiceId);map.put("success", true);} catch (Exception ex) {map.put("error", ex.getMessage());}return map;}/*** Returns a list of invoice items** @param invoice_id invoice code* @return*/@RequestMapping(value = "/invoice/getdetaildata", method = RequestMethod.GET, produces = MediaType.APPLICATION_JSON)@ResponseBodypublic JqGridData getDetailData(@RequestParam(value = "INVOICE_ID", required = true) int invoice_id) {invoiceLineGrid.setInvoiceId(invoice_id);return invoiceLineGrid.getJqGridData();}/*** Adds invoice item** @param InvoiceID invoice code* @param ProductID product code* @param quantity quantity* @return*/@RequestMapping(value = "/invoice/createdetail", method = RequestMethod.POST, produces = MediaType.APPLICATION_JSON)@ResponseBodypublic Map<String, Object> addInvoiceLine(@RequestParam(value = "INVOICE_ID", required = true, defaultValue = "0") Integer invoiceId, @RequestParam(value = "PRODUCT_ID", required = true, defaultValue = "0") Integer productId, @RequestParam(value = "QUANTITY", required = true, defaultValue = "0") Integer quantity) {Map<String, Object> map = new HashMap<> ();try {invoiceManager.addInvoiceLine(invoiceId, productId, quantity);map.put("success", true);} catch (Exception ex) {map.put("error", ex.getMessage());}return map;}/*** Edits invoice position** @param invoiceLineId invoice position code* @param quantity quantity of goods* @return*/@RequestMapping(value = "/invoice/editdetail", method = RequestMethod.POST, produces = MediaType.APPLICATION_JSON)@ResponseBodypublic Map<String, Object> editInvoiceLine(@RequestParam(value = "INVOICE_LINE_ID", required = true, defaultValue = "0") Integer invoiceLineId, @RequestParam(value = "QUANTITY", required = true, defaultValue = "0") Integer quantity) {Map<String, Object> map = new HashMap<> ();try {invoiceManager.editInvoiceLine(invoiceLineId, quantity);map.put("success", true);} catch (Exception ex) {map.put("error", ex.getMessage());}return map;}/*** Removes invoice position** @param invoiceLineId code of invoice position* @return*/@RequestMapping(value = "/invoice/deletedetail", method = RequestMethod.POST, produces = MediaType.APPLICATION_JSON)@ResponseBodypublic Map<String, Object> deleteInvoiceLine(@RequestParam(value = "INVOICE_LINE_ID", required = true, defaultValue = "0") Integer invoiceLineId) {Map<String, Object> map = new HashMap<> ();try {invoiceManager.deleteInvoiceLine(invoiceLineId);map.put("success", true);} catch (Exception ex) {map.put("error", ex.getMessage());}return map;}}

In general, the invoice controller is similar to the directory controllers with two exceptions :

  1. The controller displays and works with both main and detail grid data.
  2. Invoices are filtered by date field, so that only those invoices that are within the work period are sampled.

There are many peculiarities when working with dates.
The java.sql.Timestamp type in Java supports accuracy to nanoseconds, while in Firebird the maximum accuracy of the TIMESTAMP type is a ten thousandth of a second. This is not really a big problem.
The date and time types in Java support working with time zones. On the other hand, Firebird currently does not support the TIMESTAMP WITH TIMEZONE type. In this case, Java assumes that the dates in the database are stored in the server’s time zone (not in UTC as you might think). However, when serialized in JSON the time will be converted to UTC. This should be taken into account when processing the time in JavaScript.

Warning.
Java takes the time offset from its own time zone database, not from the operating system. This significantly increases the requirement that the JDK version is up to date. If you have an old JDK installed, the date and time may not work correctly.

By default the date is serialized in JSON in numeric representation (as number of nanoseconds elapsed since January 1, 1970). This is not always convenient. To serialize the date in text representation in the WebAppConfig class described above, in the configureMessageConverters method specify the SerializationFeature.WRITE_DATES_AS_TIMESTAMPS configuration property of the date conversion to false.

@Configuration@ComponentScan("ru.ibase.fbjavaex")@EnableWebMvcpublic class WebAppConfig extends WebMvcConfigurerAdapter {@Overridepublic void configureMessageConverters(List<HttpMessageConverter<?> > httpMessageConverters) {MappingJackson2HttpMessageConverter jsonConverter = new MappingJackson2HttpMessageConverter();ObjectMapper objectMapper = new ObjectMapper();objectMapper.configure(SerializationFeature.WRITE_DATES_AS_TIMESTAMPS, false);jsonConverter.setObjectMapper(objectMapper);httpMessageConverters.add(jsonConverter);}…}

The initBinder method of the InvoiceController describes how the text representation of the date sent by the browser is converted to a value of type Timestamp.
The JSP page contains markup for displaying a grid with invoice caps and a navigation bar. The invoice positions are displayed when the invoice header is expanded as a drop-down grid.
Invoices.jsp

<%@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> Example of a Spring MVC application using Firebird and jOOQ</title><!-- Scritps and Styles --><%@ include file="../jspf/head.jspf" %><script src="${cp}/resources/js/jqGridProduct.js"> </script><script src="${cp}/resources/js/jqGridCustomer.js"> </script><script src="${cp}/resources/js/jqGridInvoice.js"> </script></head><body><!-- Navigation menu --><%@ include file="../jspf/menu.jspf" %><div class="container body-content"><h2> Invoices</h2><table id="jqGridInvoice"> </table><div id="jqPagerInvoice"> </div><hr /><footer><p> © 2016 - An example of a Spring MVC application using Firebird and jOOQ</p></footer></div><script type="text/javascript">var invoiceGrid = null;$(document).ready(function () {invoiceGrid = JqGridInvoice({baseAddress: '${cp}'});});</script></body></html>

The main client-side logic is concentrated in the JavaScript module /resources/js/jqGridInvoice.js
jqGridInvoice.js

var JqGridInvoice = (function ($, jqGridProductFactory, jqGridCustomerFactory) {return function (options) {var jqGridInvoice = {dbGrid: null, detailGrid: null, // optionsoptions: $.extend({baseAddress: null}, options), // returns column options (model) of the invoicegetInvoiceColModel: function () {return [{label: 'Id', // signaturename: 'INVOICE_ID', // field namekey: true, // sign of the key fieldhidden: true // hidden}, {label: 'Customer Id', // signaturename: 'CUSTOMER_ID', // field namehidden: true, // hiddeneditrules: {edithidden: true, required: true}, // hidden and requirededitable: true, // editableeditype: 'custom', // custom typeeditoptions: {custom_element: function (value, options) {// add a hidden inputreturn $("<input> ").attr('type', 'hidden').attr('rowid', options.rowId).addClass("FormElement").addClass("form-control").val(value).get(0);}}}, {label: 'Date', name: 'INVOICE_DATE', width: 60, // widthsortable: true, // allow sortingeditable: true, // editablesearch: true, // allowed to searchedittype: "text", // type of the input fieldalign: "right", // aligned on the right sideformatter: jqGridInvoice.dateTimeFormatter; // formatted as datesorttype: 'date', // sorted as dateformatoptions: {srcformat: 'Y-m-d\TH:i:s', // input formatnewformat: 'd.m.Y H:i:s' // output format}, editoptions: {// initiation of a form element for editingdataInit: function (element) {// create a datepicker$(element).datepicker({id: 'invoiceDate_datePicker', dateFormat: 'dd.mm.yy', minDate: new Date(2000, 0, 1), maxDate: new Date(2030, 0, 1)});}}, searchoptions: {// initialize the form element for the searchdataInit: function (element) {// create a datepicker$(element).datepicker({id: 'invoiceDate_datePicker', dateFormat: 'dd.mm.yy', minDate: new Date(2000, 0, 1), maxDate: new Date(2030, 0, 1)});}, searchoptions: { // search typessopt: ['eq', 'lt', 'le', 'gt', 'ge']}}}, {label: 'Customer', name: 'CUSTOMER_NAME', width: 250, editable: true, edittype: "text", editoptions: {size: 50, maxlength: 60, readonly: true // read-only}, editrules: {required: true}, search: true, searchoptions: {sopt: ['eq', 'bw', 'cn']}}, {label: 'Amount', name: 'TOTAL_SALE', width: 60, sortable: false, editable: false, search: false, align: "right", formatter: 'currency', // format as currencysorttype: 'number', searchrules: {"required": true, "number": true, "minValue": 0}}, {label: 'Paid', name: 'PAID', width: 30, sortable: false, editable: true, search: true, searchoptions: {sopt: ['eq']}, edittype: "checkbox", // checkmarkformatter: "checkbox", stype: "checkbox", align: "center", editoptions: {value: "1", offval: "0"}}];}, initGrid: function () {// url to get datavar url = jqGridInvoice.options.baseAddress + '/invoice/getdata';jqGridInvoice.dbGrid = $("#jqGridInvoice").jqGrid({url: url, datatype: "json", // format for receiving datamtype: "GET" // type of http request// model descriptioncolModel: jqGridInvoice.getInvoiceColModel(), rowNum: 500, // number of displayed rowsloadonce: false, // load only oncesortname: 'INVOICE_DATE', // default sorting by date columnsortorder: "desc", // sort orderwidth: window.innerWidth - 80, // width of gridheight: 500, // height of gridviewrecords: true, //display the number of recordsguiStyle: "bootstrap", iconSet: "fontAwesome", caption: "Invoices", // caption to gridpager: '#jqPagerInvoice', // element for displaying of page navigationsubGrid: true, // show a subgrid// javascript function to display the parent gridsubGridRowExpanded: jqGridInvoice.showChildGrid, subGridOptions: { // nested grid options// load data only oncereloadOnExpand: false, // load subGrid rows only when clicking on "+"selectOnExpand: true}});}, // function of date formattingdateTimeFormatter: function(cellvalue, options, rowObject) {var date = new Date(cellvalue);return date.toLocaleString().replace(", ", "");}, //returns the edit dialog templategetTemplate: function () {var template = "<div style='margin-left:15px;' id='dlgEditInvoice'> ";template += "<div> {CUSTOMER_ID} </div> ";template += "<div> Date: </div> <div> {INVOICE_DATE}</div> ";//customer input field with buttontemplate += "<div> <sup> *</sup> :</div> ";template += "<div> ";template += "<div style='float: left;'> {CUSTOMER_NAME}</div> ";template += "<a style='margin-left: 0.2em;' class='btn' onclick='invoiceGrid.showCustomerWindow(); return false;'> "template += "<span class='glyphicon glyphicon-folder-open'> </span> Select</a> ";template += "<div style='clear: both;'> </div> ";template += "</div> ";template += "<div> {PAID} Paid </div> ";template += "<hr style='width: 100%;'/> ";template += "<div> {sData} {cData} </div> ";template +="</div>"return template;}, // date conversion to UTC convertToUTC: function(datetime) {if(datetime) {// the date needs to be convertedvar dateParts = datetime.split('.');var date = dateParts[2].substring(0, 4) + '-' + dateParts[1] + '-' + dateParts[0];var time = dateParts[2].substring(5);if (!time) {time = '00:00:00';}var dt = Date.parse(date + 'T' + time);var s = dt.getUTCFullYear() + '-' +dt.getUTCMonth() + '-' +dt.getUTCDay() + 'T' +dt.getUTCHour() + ':' +dt.getUTCMinute() + ':' +dt.getUTCSecond() + ' GMT';return s;} elsereturn null;}, // returns invoice editing optionsgetEditInvoiceOptions: function () {return {url: jqGridInvoice.options.baseAddress + '/invoice/edit', reloadAfterSubmit: true, closeOnEscape: true, closeAfterEdit: true, drag: true, modal: true, top: $(".container.body-content").position().top + 150, left: $(".container.body-content").position().left + 150, template: jqGridInvoice.getTemplate(), afterSubmit: jqGridInvoice.afterSubmit, editData: {INVOICE_ID: function () {var selectedRow = jqGridInvoice.dbGrid.getGridParam("selrow");var value = jqGridInvoice.dbGrid.getCell(selectedRow, 'INVOICE_ID');return value;}, CUSTOMER_ID: function () {return $('#dlgEditInvoice input[name=CUSTOMER_ID]').val();}, INVOICE_DATE: function () {var datetime = $('#dlgEditInvoice input[name=INVOICE_DATE]').val();return jqGridInvoice.convertToUTC(datetime);}}};}, // returns options for adding an invoicegetAddInvoiceOptions: function () {return {url: jqGridInvoice.options.baseAddress + '/invoice/create', reloadAfterSubmit: true, closeOnEscape: true, closeAfterAdd: true, drag: true, modal: true, top: $(".container.body-content").position().top + 150, left: $(".container.body-content").position().left + 150, template: jqGridInvoice.getTemplate(), afterSubmit: jqGridInvoice.afterSubmit, editData: {CUSTOMER_ID: function () {return $('#dlgEditInvoice input[name=CUSTOMER_ID]').val();}, INVOICE_DATE: function () {var datetime = $('#dlgEditInvoice input[name=INVOICE_DATE]').val();return jqGridInvoice.convertToUTC(datetime);}}};}, // returns invoice editing optionsgetDeleteInvoiceOptions: function () {return {url: jqGridInvoice.options.BaseAddress + '/invoice/delete', reloadAfterSubmit: true, closeOnEscape: true, closeAfterDelete: true, drag: true, msg: "Delete the highlighted invoice?", afterSubmit: jqGridInvoice.afterSubmit, delData: {INVOICE_ID: function () {var selectedRow = jqGridInvoice.dbGrid.getGridParam("selrow");var value = jqGridInvoice.dbGrid.getCell(selectedRow, 'INVOICE_ID');return value;}}};}, initPager: function () {// displaying the navigation barjqGridInvoice.dbGrid.jqGrid('navGrid', '#jqPagerInvoice', {search: true, // searchadd: true, // addedit: true, // editdel: true, // deleteview: false, // view recordrefresh: true, // refreshsearchtext: "Search", addtext: "Add", edittext: "Edit", deltext: "Delete", viewtext: "View", viewtitle: "Selected entry", refreshtext: "refresh"}, jqGridInvoice.getEditInvoiceOptions(), jqGridInvoice.getAddInvoiceOptions(), jqGridInvoice.getDeleteInvoiceOptions());// adding a button for paying the invoicevar urlPay = jqGridInvoice.options.baseAddress + '/invoice/pay';jqGridInvoice.dbGrid.navButtonAdd('#jqPagerInvoice', {buttonicon: "glyphicon-usd", title: "Pay", caption: "Pay", position: "last", onClickButton: function () {// get the ID of the current recordvar id = jqGridInvoice.dbGrid.getGridParam("selrow"); if (id) { $.ajax({ url: urlPay, type: 'POST', data: {INVOICE_ID: id}, success: function (data) { // check if the error if (data.hasOwnProperty("error")) {jqGridInvoice.AlertDialog('Error', data.error);} else {// updating the grid$("#jqGridInvoice").jqGrid('setGridParam', {datatype: 'json'}).trigger('reloadGrid');}}});}}});}, init: function () {jqGridInvoice.initGrid();jqGridInvoice.initPager();}, afterSubmit: function (response, postdata) {var responseData = response.responseJSON;// checking the result for error messagesif (responseData.hasOwnProperty("error")) {if (responseData.error.length) {return [false, responseData.error];}} else {// updating the grid$(this).jqGrid('setGridParam', {datatype: 'json'}).trigger('reloadGrid');}return [true, "", 0];}, getInvoiceLineColModel: function (parentRowKey) {return [{label: 'Invoice Line ID', name: 'INVOICE_LINE_ID', key: true, hidden: true}, {label: 'Invoice ID', name: 'INVOICE_ID', hidden: true, editrules: {edithidden: true, required: true}, editable: true, edittype: 'custom', editoptions: {custom_element: function (value, options) {// create a hidden input element return $("<input> ") .attr('type', 'hidden') .attr('rowid', options.rowId) .addClass("FormElement") .addClass("form-control").val(parentRowKey) .get(0); } } }, { label: 'Product ID', name: 'PRODUCT_ID', hidden: true, editrules: {edithidden: true, required: true}, editable: true, edittype: 'custom', editoptions: { custom_element: function (value, options) { // create a hidden input element return $("<input> ") .attr('type', 'hidden').attr('rowid', options.rowId).addClass("FormElement").addClass("form-control").val(value).get(0);}}}, {label: 'Product', name: 'PRODUCT_NAME', width: 300, editable: true, edittype: "text", editoptions: {size: 50, maxlength: 60, readonly: true}, editrules: {required: true}}, {label: 'Price', name: 'SALE_PRICE', formatter: 'currency', editable: true, editoptions: {readonly: true}, align: "right", width: 100}, {label: 'Quantity', name: 'QUANTITY', align: "right", width: 100, editable: true, editrules: {required: true, number: true, minValue: 1}, editoptions: {dataEvents: [{type: 'change', fn: function (e) {var quantity = $(this).val() - 0;var price = $('#dlgEditInvoiceLine input[name=SALE_PRICE]').val() - 0;$('#dlgEditInvoiceLine input[name=TOTAL]').val(quantity * price);}}], defaultValue: 1}}, {label: 'Total', name: 'TOTAL', formatter: 'currency', align: "right", width: 100, editable: true, editoptions: {readonly: true}}];}, // returns options for editing the invoice itemgetEditInvoiceLineOptions: function () {return {url: jqGridInvoice.options.baseAddress + '/invoice/editdetail', reloadAfterSubmit: true, closeOnEscape: true, closeAfterEdit: true, drag: true, modal: true, top: $(".container.body-content").position().top + 150, left: $(".container.body-content").position().left + 150, template: jqGridInvoice.getTemplateDetail(), afterSubmit: jqGridInvoice.afterSubmit, editData: {INVOICE_LINE_ID: function () {var selectedRow = jqGridInvoice.detailGrid.getGridParam("selrow");var value = jqGridInvoice.detailGrid.getCell(selectedRow, 'INVOICE_LINE_ID');return value;}, QUANTITY: function () {return $('#dlgEditInvoiceLine input[name=QUANTITY]').val();}}};}, // returns options for adding an invoice itemgetAddInvoiceLineOptions: function () {return {url: jqGridInvoice.options.baseAddress + '/invoice/createdetail', reloadAfterSubmit: true, closeOnEscape: true, closeAfterAdd: true, drag: true, modal: true, top: $(".container.body-content").position().top + 150, left: $(".container.body-content").position().left + 150, template: jqGridInvoice.getTemplateDetail(), afterSubmit: jqGridInvoice.afterSubmit, editData: {INVOICE_ID: function () {var selectedRow = jqGridInvoice.dbGrid.getGridParam("selrow");var value = jqGridInvoice.dbGrid.getCell(selectedRow, 'INVOICE_ID');return value;}, PRODUCT_ID: function () {return $('#dlgEditInvoiceLine input[name=PRODUCT_ID]').val();}, QUANTITY: function () {return $('#dlgEditInvoiceLine input[name=QUANTITY]').val();}}};}, //returns options to delete invoice positiongetDeleteInvoiceLineOptions: function () {return {url: jqGridInvoice.options.baseAddress + '/invoice/deletedetail', reloadAfterSubmit: true, closeOnEscape: true, closeAfterDelete: true, drag: true, msg: "Delete selected position? ", afterSubmit: jqGridInvoice.afterSubmit, delData: {INVOICE_LINE_ID: function () {var selectedRow = jqGridInvoice.detailGrid.getGridParam("selrow");var value = jqGridInvoice.detailGrid.getCell(selectedRow, 'INVOICE_LINE_ID');return value;}}};}, // event handler of the parent grid//takes two parameters the identifier of the parent record// and the primary key of the recordshowChildGrid: function (parentRowID, parentRowKey) {var childGridID = parentRowID + "_table";var childGridPagerID = parentRowID + "_pager";// we send the primary key of the parent record// to filter the records of the waybill positionsvar childGridURL = jqGridInvoice.options.baseAddress + '/invoice/getdetaildata';childGridURL = childGridURL + "?INVOICE_ID=" + encodeURIComponent(parentRowKey);// add HTML elements to display the table and page navigation// as a child of the chosen row in the master grid$('<table> ').attr('id', childGridID).appendTo($('#' + parentRowID))$('<div> ').attr('id', childGridPagerID).addClass('scroll').appendTo($('#' + parentRowID));//create and initialize a child gridjqGridInvoice.detailGrid = $("#" + childGridID).jqGrid({url: childGridURL, mtype: "GET", datatype: "json", page: 1, colModel: jqGridInvoice.getInvoiceLineColModel(parentRowKey), loadonce: false, width: '100%', height: '100%', guiStyle: "bootstrap", iconSet: "fontAwesome", pager: "#" + childGridPagerID});//display the toolbar$("#" + childGridID).jqGrid('navGrid', '#' + childGridPagerID, {search: false, // searchadd: true, // addedit: true, // editdel: true, // deletionrefresh: true //refresh}, jqGridInvoice.getEditInvoiceLineOptions(), jqGridInvoice.getAddInvoiceLineOptions(), jqGridInvoice.getDeleteInvoiceLineOptions());}, // returns the template for the invoice position editorgetTemplateDetail: function () {var template = "<div style='margin-left:15px;' id='dlgEditInvoiceLine'> ";template += "<div> {INVOICE_ID} </div> ";template += "<div> {PRODUCT_ID} </div> ";//product input field with buttontemplate += ""div> Product<sup> *</sup> :</div> ";template += "<div> ";template += "<div style='float: left;'> {PRODUCT_NAME}</div> ";template += "<a style='margin-left: 0.2em;' class='btn' onclick='invoiceGrid.showProductWindow(); return false;'> ";template += "<span class='glyphicon glyphicon-folder-open'> </span> Select</a>";template += "<div style='clear: both;'> </div> ";template += "</div> ";template += "<div> Quantity: </div> <div> {QUANTITY} </div> ";template += "<div> Price: </div> <div> {SALE_PRICE} </div> ";template += "<div> Total: </div> <div> {TOTAL} </div> ";template += "<hr style='width: 100%;'/> ";template += "<div> {sData} {cData} </div> ";template += "</div>"return template;}, // displaying the product selection window from the directoryshowProductWindow: function () {var dlg = $('<div> ').attr('id', 'dlgChooseProduct').attr('aria-hidden', 'true').attr('role', 'dialog').attr('data-backdrop', 'static').css("z-index", '2000').addClass('modal').appendTo($('body'));var dlgContent = $("<div> ").addClass("modal-content").css('width', '760px').appendTo($('<div> ').addClass('modal-dialog').appendTo(dlg))var dlgHeader = $('<div> ').addClass("modal-header").appendTo(dlgContent);$(""button> ").addClass("close").attr('type', 'button').attr('aria-hidden', 'true').attr('data-dismiss', 'modal').html("×").appendTo(dlgHeader);$("<h5>").addClass("modal-title").html("Customer's choice").appendTo(dlgHeader);var dlgBody = $('<div> ').addClass("modal-body").appendTo(dlgContent);var dlgFooter = $('<div> ').addClass("modal-footer").appendTo(dlgContent);$("<button> ").attr('type', 'button').addClass('btn').html('OK').on('click', function () {var rowId = $("#jqGridProduct").jqGrid("getGridParam", "selrow");var row = $("#jqGridProduct").jqGrid("getRowData", rowId);$('#dlgEditInvoiceLine input[name=PRODUCT_ID]').val(row["PRODUCT_ID"]);$('#dlgEditInvoiceLine input[name=PRODUCT_NAME]').val(row["NAME"]);$('#dlgEditInvoiceLine input[name=SALE_PRICE]').val(row["PRICE"]);var price = $('#dlgEditInvoiceLine input[name=SALE_PRICE]').val() - 0;var quantity = $('#dlgEditInvoiceLine input[name=QUANTITY]').val() - 0;$('#dlgEditInvoiceLine input[name=TOTAL]').val(Math.round(price * quantity * 100) / 100);dlg.modal('hide');}).appendTo(dlgFooter);$("<button> ").attr('type', 'button').addClass('btn').html('Cancel').on('click', function () {dlg.modal('hide');}).appendTo(dlgFooter);$('<table> ').attr('id', 'jqGridProduct').appendTo(dlgBody);$('<div> ').attr('id', 'jqPagerProduct').appendTo(dlgBody);dlg.on('hidden.bs.modal', function () {dlg.remove();});dlg.modal();jqGridProductFactory({baseAddress: jqGridInvoice.options.baseAddress});}, //display the customer selection window from the directoryshowCustomerWindow: function () {// main block of the dialog boxvar dlg = $('<div> ').attr('id', 'dlgChooseCustomer').attr('aria-hidden', 'true').attr('role', 'dialog').attr('data-backdrop', 'static').css("z-index", '2000').addClass('modal').appendTo($('body'));// the block with the dialog contentvar dlgContent = $("<div> ").addClass("modal-content").css('width', '730px').appendTo($('<div> ').addClass('modal-dialog').appendTo(dlg));// block with a dialog headervar dlgHeader = $('<div> ').addClass("modal-header").appendTo(dlgContent);//"X" button to close$("<button> ").addClass("close").attr('type', 'button').attr('aria-hidden', 'true').attr('data-dismiss', 'modal').html("×").appendTo(dlgHeader);//signature$("<h5> ").addClass("modal-title").html("Customer's Choice").appendTo(dlgHeader);// dialog bodyvar dlgBody = $('<div>').addClass("modal-body").appendTo(dlgContent);// dialog basementvar dlgFooter = $('<div>').addClass("modal-footer").appendTo(dlgContent);// Button "OK"$("<button> ").attr('type', 'button').addClass('btn').html('OK').on('click', function () {var rowId = $("#jqGridCustomer").jqGrid("getGridParam", "selrow");var row = $("#jqGridCustomer").jqGrid("getRowData", rowId);// saving the customer ID and name// in the input elements of the parent form$('#dlgEditInvoice input[name=CUSTOMER_ID]').val(rowId);$('#dlgEditInvoice input[name=CUSTOMER_NAME]').val(row["NAME"]);dlg.modal('hide');}).appendTo(dlgFooter);// Cancel button;$("<button> ").attr('type', 'button').addClass('btn').html('Cancel').on('click', function () {dlg.modal('hide');}).appendTo(dlgFooter);// add a table to display the customers to the body of the dialog$('<table> ').attr('id', 'jqGridCustomer').appendTo(dlgBody);// add a navigation bar$('<div> ').attr('id', 'jqPagerCustomer').appendTo(dlgBody);dlg.on('hidden.bs.modal', function () {dlg.remove();});//display the dialog boxdlg.modal();jqGridCustomerFactory({baseAddress: jqGridInvoice.options.baseAddress});}, // window to display an erroralertDialog: function (title, error) {var alertDlg = $('<div> ').attr('aria-hidden', 'true').attr('role', 'dialog').attr('data-backdrop', 'static').addClass('modal').appendTo($('body'));var dlgContent = $("<div> ").addClass("modal-content").appendTo($('<div> ').addClass('modal-dialog').appendTo(alertDlg));var dlgHeader = $('<div> ').addClass("modal-header").appendTo(dlgContent);$("<button> ").addClass("close").attr('type', 'button').attr('aria-hidden', 'true').attr('data-dismiss', 'modal').html("×").appendTo(dlgHeader);$("<h5> ").addClass("modal-title").html(title).appendTo(dlgHeader);$('<div> ').addClass("modal-body").appendTo(dlgContent).append(error);alertDlg.on('hidden.bs.modal', function () {alertDlg.remove();});alertDlg.modal();}};jqGridInvoice.init();return jqGridInvoice;};})(jQuery, JqGridProduct, JqGridCustomer);

In the invoice log, the main grid is used to display caps, and the clickable grid is used to display positions.The subGrid property is set to true to display the child grid.The child grid is displayed using the subGridRowExpanded event, which is associated with the showChildGrid method. Positions are filtered by the primary invoice key. In addition to the main buttons, a custom button has been added to the navigation bar for the invoice header to pay the invoice using the jqGridInvoice.dbGrid.navButtonAdd function (see initPager method).
Unlike directories, the editing dialogs for journals are much more complex. Often they use selections from other directories. Therefore, such edit dialogs cannot be built automatically using jqGrid, but this library has the ability to build dialogs by template, which we will use. The template of the dialogs is returned by the getTemplate function. The opening of the customer directory to select a customer is performed by the function invoiceGrid.showCustomerWindow(). It uses the functions of the JqGridCustomer module described above. After the customer has been selected from the modal window, his code is inserted into the CUSTOMER_ID field. The editData property of the edit and add options describes the fields to be sent to the server using preprocessing or from invisible fields.
Now let’s get back to the date processing. As I mentioned above, the InvoiceController returns the date in UTC, but we need to display it in the current time zone. To do that, let’s set the date formatting function jqGridInvoice.dateTimeFormatter via the formatter property of the corresponding INVOICE_DATE field. When sending data to the server, we need to do the reverse operation – convert the time from the current time zone to UTC. This is done with function convertToUTC.
For the invoice item editor, a custom template is also used, which is returned by the getTemplateDetail function. The invoiceGrid.showProductWindow() function opens a window for selecting from the product catalog. This function uses the functions of the JqGridProduct module.
The code of the JqGridInvoice module is commented in detail so that you can understand the logic of its operation. You can find additional explanations in it.
Here are a few screenshots of the resulting web application.
Creating applications using Firebird, jOOQand Spring MVC
Creating applications using Firebird, jOOQand Spring MVC
Creating applications using Firebird, jOOQand Spring MVC
Creating applications using Firebird, jOOQand Spring MVC
This concludes my example. You can download the source code from link
You can download the database scripts here Firebird_2_5.sql and Firebird_3_0.sql
You can download ready databases from Firebird_2_5_DB and Firebird_3_0_DB

You may also like