Create applications using Firebird, jOOQ and Spring MVC
Hello. This time, the process of creating a Java web application using the Spring MVC framework, jOOQ library and Firebird DBMS will be described.
To simplify development, you can use one of the common IDEs for Java (NetBeans, IntelliJ IDEA, Eclipse, JDeveloper, or others). I personally used NetBeans. For testing and debugging, we also need to install one and web servers or application servers (Apache Tomcat or Glass Fish). Create a project based on the Maven template of the web application project.
After creating a project based on a template, you need to convert its folder structure so that it is correct for MVC Spring 4. If the project was created in NetBeans 8.2, then the following steps are necessary:
After our manipulations, the folder structure should look like this.
')
In the WEB-INF / jsp folder, the jsp page will be placed, and in the jspf folder, parts of the pages that will be connected to other pages using the instructions:
The resource folder is intended for hosting static web resources. The cascading style sheet files will be placed in the WEB-INF / resources / css folder, the font files in the WEB-INF / resources / fonts folder, in the WEB-INF / resources / js folder there are JavaScript files and third-party JavaScript libraries.
Now we will fix the pom.xml file and write into it the general properties of the application, dependencies on the library packages (Spring MVC, Jaybird, JDBC pool, JOOQ) and JDBC connection properties.
After you have registered all the necessary dependencies, it is advisable to reload the POM in order to load all the necessary libraries. If this is not done, then in the process of working with the project errors may occur. In NetBeans, this is done as follows.
I don’t really like configuring via xml, so I’ll work through Java configuration classes.
In this configuration class, we set the search location for web resources and JSP views. The configureMessageConverters method sets the date to be serialized into a string representation (by default, serialized in a numeric representation as a timestamp).
Now let's get rid of the Web.xml file instead of it, create the WebInitializer.java file.
It remains to configure IoC containers for dependency injection. We will return to this step later, and now we will proceed to the generation of classes for working with the database via jOOQ.
We will work with the database using the jOOQ library. jOOQ allows you to build SQL queries from jOOQ objects and code (like LINQ). jOOQ has closer integration with the database than ORM, therefore, in addition to simple CRUD SQL queries used in Active Record, it allows you to use additional features. For example, jOOQ can work with stored procedures and functions, sequences, use window functions and other features specific to a particular DBMS. Full documentation on working with jOOQ is available here .
The jOOQ classes for working with a database are generated based on a database schema. Our application will work with the database, the model of which is shown in the figure below.
In addition to tables, our database also contains stored procedures and sequences. At the end of this article there is a link to the database creation script.
To generate jOOQ classes that work with our database, you need to download the following binary files by reference or via the maven repository:
In addition, to connect to the Firebird database via JDBC, you will need to download the jaybird-full-3.0.0.jar driver .
Now you need to create the configuration file example.xml, which will be used to generate the database schema classes.
Now go to the command line and run the following command:
This command will create the necessary classes and allow you to write in Java language queries to database objects. You can learn more about the class generation process at the Code generation link.
In Spring, Dependency Injection (DI) is implemented via the Spring IoC (Inversion of Control) container. The introduction of dependencies is a process according to which objects define their dependencies, i.e. the objects they work with are through constructor / factory method arguments or properties that have been set or returned by the factory method. Then the inject container (hereinafter "injects") these dependencies when creating the bean. You can read more about dependency injection in the chapter The IoC container .
I’m not a supporter of xml configuration, so we’ll use an annotation approach and a java configuration. The main features and parts of the Java IoC configuration of the container are the classes with the @Configuration annotation and the methods with the Bean annotation. Annotation Bean is used to indicate that a method creates, configures, and initializes a new object managed by a Spring IoC container. Such methods can be used as in classes annotated with @Configuration. Our IoC container will return connection pool, transaction manager, exception translator (converts SQLException exceptions to Spring-specific DataAccessException exceptions), DSL context (starting point for building all queries using Fluent API), as well as managers for implementing business logic and grids for display data.
Before we consider the implementation of managers and grids, we will describe how to work with the database through jOOQ. Only brief information about query construction will be presented here, you can find complete documentation on this issue in the jOOQ sql-building chapter.
The org.jooq.impl.DSL class is the main class from which you will create all the jOOQ objects. It acts as a static factory for table expressions, column expressions (or fields), conditional expressions, and many other parts of the query.
DSLContext refers to the org.jooq.Configuration object, which customizes the jOOQ behavior when executing queries. Unlike static DSL, DSLContext allows you to create SQL statements that are already “tuned” and ready to be executed. In our application, DSLContext is created in the JooqConfig configuration class in the getDsl method. The configuration for DSLContext is returned by the getDslConfig method. In this method, we have indicated that we will use the Firebird DBMS SQL dialect, the connection provider (determines how we get the connection via JDBC), and the listener for executing SQL queries.
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, much the same way as .NET in C # does it using LINQ to SQL.
jOOQ uses informal BNF notation, which models a unified SQL dialect suitable for most DBMSs. Unlike other, simpler frameworks that use the “Fluent API” or “chaining method”, the hierarchy of the BNF interface based on jOOQ does not allow for poor query syntax.
Let's look at a simple query in SQL:
In jOOQ, it will look like this:
The classes AUTHOR and BOOK describing the corresponding tables must be generated in advance. The process of generating classes jOOQ according to a given DB scheme was described above.
In this case, we defined the AUTHOR and BOOK tables with an alias using the as construct. Without the use of aliases, this query would look like this:
Now let's look at a more complex query using aggregate functions and grouping.
In jOOQ, it will look like this:
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 the FIREBIRD_2_5 dialect or just FIREBIRD were specified, then the ROWS clause would be used instead of OFSET ... FETCH.
You can collect the request in parts. This allows you to change it dynamically, which can be used to change the sort order or add additional filtering options.
By default, each time you use a string, date, and number literal in the query, and also substitute external variables, jOOQ binds this variable or literal through unnamed parameters. For example, the following expression in Java:
Equivalent to the complete record:
and converted to sql query:
You do not need to worry about the index of the corresponding parameter, the values will automatically be tied to the desired parameter. If you need to change the value of a parameter, you can do it by selecting the desired parameter by the index number (indexing starts from 1).
Another way to assign a new value to a parameter is to call the bind method.
In addition, jOOQ supports named parameters. In this case, they must be explicitly created using org.jooq.Param.
Another way to assign a new value to a parameter is to call the bind method.
jOOQ provides many ways to extract data from SQL queries. We will not list all the ways here, you can read more about them in the Fetching chapter of the jOOQ documentation. In our example, we will use the return to the list of maps (fetchMaps method), which is convenient to use for serializing the result in JSON.
Now let's see what other types of queries look like. For example, the following request to insert a record:
in jOOQ it will look like this:
:
jOOQ :
:
:
, jOOQ , MERGE .
jOOQ . *.Routines.* , Java:
SQL :
:
jOOQ DDL , .
jOOQ , .. SQL , SQL , . READ_WRITE READ_COMMITED REC_VERSION WAIT. JDBC . (. BasicDataSource.setDefaultTransactionIsolation getDataSource JooqConfig).
There are several ways to explicitly manage transactions in jOOQ. Since we are developing an application using the Spring Framework, we will use the transaction manager specified in the configuration (JooqConfig). You can get a transaction manager by declaring the txMgr property in the class as follows:
In this case, a typical transaction scenario is as follows:
However, Spring makes it possible to implement such a script much easier using the @Transactional annotation specified before the class method. In this case, all actions performed in the method will be wrapped in a transaction.
The propagation parameter specifies how transactions will be handled if our method is called from an external transaction.
The isolation parameter specifies the transaction isolation mode. 5 values are supported: DEFAULT, READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE. If the value of the DEFAULT parameter is specified, the default transaction isolation mode will be used. The remaining isolation modes are taken from the SQL standard. In Firebird, there are several other isolation modes and the READ_COMMITED mode only fully meets all the criteria. Thus, in JDBC mode, READ_COMMITTED maps to read_committed to Firebird, REPEATABLE_READ to concurrency (Snapshot), and SERIALIZABLE to consistency. In addition, in addition to the 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 properties of the JDBC connection (for more details seeJaybird 2.1 JDBC driver Java Programmer's Manual Using transactions). 1 , REPEATABLE_READ .
@Transactional , readOnly. read-write.
We will display the data of our application using JavaScript component jqGrid . Currently, jqGrid is distributed under a commercial license, but is free for non-commercial purposes. Instead, you can use the free-jqGrid fork . To display the data in this grid and elements of page navigation, we need to return the data in JSON format, the structure of which looks like this.
Where
Create a class that describes this structure:
Now we will write an abstract class that will return the above structure depending on the search and sorting conditions. This class will be the ancestor of classes returning similar structures for specific entities.
Please note that this class contains the DSLContext dsl property, which will be used to build queries for data sampling using jOOQ.
Now we can start creating directories. We describe the process of creating directories on the example of a reference book of customers. The product directory is created in a similar way, and if you wish, you can view its source code at the link provided at the end of the article.
First, we implement the class for working with jqGrid, it will be inherited from our abstract class ru.ibase.fbjavaex.jqgrid.JqGrid described above. It has the ability to search and multi-directional sorting by the field NAME. The source code listing will provide explanatory comments.
, CustomerManager, . Snapshot.
We now turn to writing a controller. Controller classes begin with the Controller annotation . To determine the actions of the controller, you must add the @RequestMapping annotation before the method and indicate in it the route along which the controller's action will be triggered. The route is specified in the parameter value. In the method parameter, you can specify the HTTP request method (PUT, GET, POST, DELETE). The input point of our controller will be the index method, it is responsible for displaying the JSP page (view). This page contains markup for grid display, toolbar and navigation.
jqGrid ( /customer/getdata). getData. @ResponseBody, , . @RequestMapping produces = MediaType.APPLICATION_JSON, , JSON. JqGridCustomer . @RequestParam HTTP . GET . value @RequestParam HTTP . required , HTTP . defaultValue , HHTP .
addCustomer . /customer/create, POST . {success: true} , . CustomerManager.
editCustomer /customer/edit . deleteCustomer /customer/delete .
The JSP page for displaying the customer reference book contains nothing special: the markup with the main parts of the page, the table for displaying the grid, and the block for displaying the navigation bar. JSP templates are not a very advanced tool, if you prefer, you can replace them with other template systems that support inheritance. The ../jspf/head.jspf file contains common scripts and styles for all pages of the site, and the ../jspf/menu.jspf file contains the main menu of the site. We will not give their code, it is quite simple and, if you wish, you can view it in the source code of the project.
The main client-side logic is concentrated in the JavaScript module /resources/js/jqGridCustomer.js
jqGrid initGrid html jqGridCustomer. () getColModel. jqGrid . , . jqGrid jqGrid ColModel API .
( initPagerWithEditors initPagerWithoutEditors ). jqPagerCustomer. Navigator jqGrid.
getEditOptions, getAddOptions, getDeleteOptions , . url , OK . afterSubmit – , . afterSubmit , . , , . editData. , . , , .
. . , . – , . WorkingPeriod. workingPeriod ru.ibase.fbjavaex.config.JooqConfig.
In our application there will be one magazine "Invoices". Invoice - consists of a header, which describes common attributes (number, date, customer ...), and invoice items (product name, quantity, cost, etc.). The invoice header is displayed in the main grid, and the positions can be viewed in the detail grid, which is expanded by clicking on the “+” icon on the desired document.
We implement a class for viewing invoice header through 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 the account. In addition, this class supports sorting by date in both directions.
- jqGrid . -, , - .
, , ( ), , ru.ibase.fbjavaex.managers.InvoiceManager, . Snapshot. ( , ).
. index, JSP (). , .
jqGrid ( /invoice/getdata). getData ( ). getDetailData ( /invoice/getdetaildata). , . addInvoice, editInvoice, payInvoice, deleteInvoice , , . addInvoiceLine, editInvoiceLine, deleteInvoiceLine , .
In general, invoice controller controlers are similar to reference book controllers with two exceptions:
.
java.sql.Timestamp Java , Firebird TIMESTAMP . .
Java . , Firebird TIMESTAMP WITH TIMEZONE. Java , ( UTC ). JSON UTC. JavaScript.
JSON ( 1 1970). . WebAppConfig, , configureMessageConverters SerializationFeature.WRITE_DATES_AS_TIMESTAMPS false.
The initBinder method of the InvoiceController controller describes how the textual 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 headers and a navigation bar. The positions of the invoice are displayed when opening the invoice header, as a drop-down grid.
The main client-side logic is centered in the JavaScript module /resources/js/jqGridInvoice.js
, , . subGrid true. , subGridRowExpanded, showChildGrid. . jqGridInvoice.dbGrid.navButtonAdd (. initPager).
. . jqGrid, , . getTemplate. invoiceGrid.showCustomerWindow(). JqGridCustomer. CUSTOMER_ID. editData , , .
. , InvoiceController UTC, . jqGridInvoice.dateTimeFormatter formatter, INVOICE_DATE. – UTC. convertToUTC.
, , getTemplateDetail. invoiceGrid.showProductWindow(). JqGridProduct.
The code of the JqGridInvoice module is commented in detail so that you can understand the logic of its operation. Additional explanations can be found in it.
Finally, here are some screenshots of the resulting web application.
This completes my example. Source codes you can download the link .
You can download scripts to create a database using the links Firebird_2_5.sql and Firebird_3_0.sql .
You can download ready databases by clicking on Firebird_2_5_DB and Firebird_3_0_DB .
To simplify development, you can use one of the common IDEs for Java (NetBeans, IntelliJ IDEA, Eclipse, JDeveloper, or others). I personally used NetBeans. For testing and debugging, we also need to install one and web servers or application servers (Apache Tomcat or Glass Fish). Create a project based on the Maven template of the web application project.
After creating a project based on a template, you need to convert its folder structure so that it is correct for MVC Spring 4. If the project was created in NetBeans 8.2, then the following steps are necessary:
- Delete index.html file
- Create a WEB-INF folder inside the Web Pages folder
- Inside the WEB-INF folder create jsp, jspf and resources folders
- Inside the resources folder we create js and CSS folders
- Inside the jsp folder create the file index.jsp
After our manipulations, the folder structure should look like this.
')
In the WEB-INF / jsp folder, the jsp page will be placed, and in the jspf folder, parts of the pages that will be connected to other pages using the instructions:
<%@ include file="< >" %> The resource folder is intended for hosting static web resources. The cascading style sheet files will be placed in the WEB-INF / resources / css folder, the font files in the WEB-INF / resources / fonts folder, in the WEB-INF / resources / js folder there are JavaScript files and third-party JavaScript libraries.
Now we will fix the pom.xml file and write into it the general properties of the application, dependencies on the 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> <!-- 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> <!-- --> <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> <surce>1.7</surce> <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 registered all the necessary dependencies, it is advisable to reload the POM in order to load all the necessary libraries. If this is not done, then in the process of working with the project errors may occur. In NetBeans, this is done as follows.
I don’t really like configuring via xml, so I’ll work through 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") @EnableWebMvc public class WebAppConfig extends WebMvcConfigurerAdapter { @Override public 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); } @Bean public UrlBasedViewResolver setupViewResolver() { UrlBasedViewResolver resolver = new UrlBasedViewResolver(); resolver.setPrefix("/WEB-INF/jsp/"); resolver.setSuffix(".jsp"); resolver.setViewClass(JstlView.class); return resolver; } @Override public void addResourceHandlers(ResourceHandlerRegistry registry) { registry.addResourceHandler("/resources/**").addResourceLocations("/WEB-INF/resources/"); } } In this configuration class, we set the search location for web resources and JSP views. The configureMessageConverters method sets the date to be serialized into a string representation (by default, serialized in a numeric representation as a timestamp).
Now let's get rid of the Web.xml file instead of it, create the WebInitializer.java file.
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 { @Override public 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 IoC containers for dependency injection. We will return to this step later, and now we will proceed to the generation of classes for working with the database via jOOQ.
Generating classes for working with the database via jOOQ
We will work with the database using the jOOQ library. jOOQ allows you to build SQL queries from jOOQ objects and code (like LINQ). jOOQ has closer integration with the database than ORM, therefore, in addition to simple CRUD SQL queries used in Active Record, it allows you to use additional features. For example, jOOQ can work with stored procedures and functions, sequences, use window functions and other features specific to a particular DBMS. Full documentation on working with jOOQ is available here .
The jOOQ classes for working with a database are generated based on a database schema. Our application will work with the database, the model of which is shown in the figure below.
In addition to tables, our database also contains stored procedures and sequences. At the end of this article there is a link to the database creation script.
| Attention! This model is just an example. Your subject area may be more complicated, or completely different. The model used in this article is simplified as much as possible in order not to clutter up the description of working with components with the description of creating and modifying the data model. |
To generate jOOQ classes that work with our database, you need to download the following binary files by reference 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 assembly for navigating the database schema through the generated objects. - jooq-codegen-3.9.2.jar
A utility that is included in your build to generate a database schema.
In addition, to connect to the Firebird database via JDBC, you will need to download the jaybird-full-3.0.0.jar driver .
Now you need to create the 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"> <!-- --> <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> <!-- . : org.util.[database].[database]Database --> <name>org.jooq.util.firebird.FirebirdDatabase</name> <inputSchema></inputSchema> <!-- , ( Java. , ). . , ! --> <includes>.*</includes> <!-- , . ( Java). RDB$, MON$ SEC$. --> <excludes> RDB\$.* | MON\$.* | SEC\$.* </excludes> </database> <target> <!-- --> <packageName>ru.ibase.fbjavaex.exampledb</packageName> <!-- . Maven. --> <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 will create the necessary classes and allow you to write in Java language queries to database objects. You can learn more about the class generation process at the Code generation link.
Container IoC Configuration
In Spring, Dependency Injection (DI) is implemented via the Spring IoC (Inversion of Control) container. The introduction of dependencies is a process according to which objects define their dependencies, i.e. the objects they work with are through constructor / factory method arguments or properties that have been set or returned by the factory method. Then the inject container (hereinafter "injects") these dependencies when creating the bean. You can read more about dependency injection in the chapter The IoC container .
I’m not a supporter of xml configuration, so we’ll use an annotation approach and a java configuration. The main features and parts of the Java IoC configuration of the container are the classes with the @Configuration annotation and the methods with the Bean annotation. Annotation Bean is used to indicate that a method creates, configures, and initializes a new object managed by a Spring IoC container. Such methods can be used as in classes annotated with @Configuration. Our IoC container will return connection pool, transaction manager, exception translator (converts SQLException exceptions to Spring-specific DataAccessException exceptions), DSL context (starting point for building all queries using Fluent API), as well as managers for implementing business logic and grids for display data.
JooqConfig.java
/** * IoC * . */ 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 */ @Configuration public class JooqConfig { /** * * * @return */ @Bean(name = "dataSource") public DataSource getDataSource() { BasicDataSource dataSource = new BasicDataSource(); // dataSource.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; } /** * * * @return */ @Bean(name = "transactionManager") public DataSourceTransactionManager getTransactionManager() { return new DataSourceTransactionManager(getDataSource()); } @Bean(name = "transactionAwareDataSource") public TransactionAwareDataSourceProxy getTransactionAwareDataSource() { return new TransactionAwareDataSourceProxy(getDataSource()); } /** * * * @return */ @Bean(name = "connectionProvider") public DataSourceConnectionProvider getConnectionProvider() { return new DataSourceConnectionProvider(getTransactionAwareDataSource()); } /** * * * @return */ @Bean(name = "exceptionTranslator") public ExceptionTranslator getExceptionTranslator() { return new ExceptionTranslator(); } /** * DSL * * @return */ @Bean(name = "dslConfig") public org.jooq.Configuration getDslConfig() { DefaultConfiguration config = new DefaultConfiguration(); // SQL Firebird config.setSQLDialect(SQLDialect.FIREBIRD); config.setConnectionProvider(getConnectionProvider()); DefaultExecuteListenerProvider listenerProvider = new DefaultExecuteListenerProvider(getExceptionTranslator()); config.setExecuteListenerProvider(listenerProvider); return config; } /** * DSL * * @return */ @Bean(name = "dsl") public DSLContext getDsl() { org.jooq.Configuration config = this.getDslConfig(); return new DefaultDSLContext(config); } /** * * * @return */ @Bean(name = "customerManager") public CustomerManager getCustomerManager() { return new CustomerManager(); } /** * * * @return */ @Bean(name = "customerGrid") public JqGridCustomer getCustomerGrid() { return new JqGridCustomer(); } /** * * * @return */ @Bean(name = "productManager") public ProductManager getProductManager() { return new ProductManager(); } /** * * * @return */ @Bean(name = "productGrid") public JqGridProduct getProductGrid() { return new JqGridProduct(); } /** * * * @return */ @Bean(name = "invoiceManager") public InvoiceManager getInvoiceManager() { return new InvoiceManager(); } /** * * * @return */ @Bean(name = "invoiceGrid") public JqGridInvoice getInvoiceGrid() { return new JqGridInvoice(); } /** * * * @return */ @Bean(name = "invoiceLineGrid") public JqGridInvoiceLine getInvoiceLineGrid() { return new JqGridInvoiceLine(); } /** * * * @return */ @Bean(name = "workingPeriod") public WorkingPeriod getWorkingPeriod() { return new WorkingPeriod(); } } Building SQL queries using jOOQ
Before we consider the implementation of managers and grids, we will describe how to work with the database through jOOQ. Only brief information about query construction will be presented here, you can find complete documentation on this issue in the jOOQ sql-building chapter.
The org.jooq.impl.DSL class is the main class from which you will create all the jOOQ objects. It acts as a static factory for table expressions, column expressions (or fields), conditional expressions, and many other parts of the query.
DSLContext refers to the org.jooq.Configuration object, which customizes the jOOQ behavior when executing queries. Unlike static DSL, DSLContext allows you to create SQL statements that are already “tuned” and ready to be executed. In our application, DSLContext is created in the JooqConfig configuration class in the getDsl method. The configuration for DSLContext is returned by the getDslConfig method. In this method, we have indicated that we will use the Firebird DBMS SQL dialect, the connection provider (determines how we get the connection via JDBC), and the listener for executing SQL queries.
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, much the same way as .NET in C # does it using LINQ to SQL.
jOOQ uses informal BNF notation, which models a unified SQL dialect suitable for most DBMSs. Unlike other, simpler frameworks that use the “Fluent API” or “chaining method”, the hierarchy of the BNF interface based on jOOQ does not allow for poor query syntax.
Let's look at a simple query in SQL:
SELECT * FROM author a JOIN book b ON a.id = b.author_id WHERE a.year_of_birth > 1920 AND 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 classes AUTHOR and BOOK describing the corresponding tables must be generated in advance. The process of generating classes jOOQ according to a given DB scheme was described above.
In this case, we defined the AUTHOR and BOOK tables with an alias using the as construct. Without the use of 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 AUTHOR JOIN BOOK ON AUTHOR.ID = BOOK.AUTHOR_ID WHERE BOOK.LANGUAGE = 'DE' AND BOOK.PUBLISHED > '2008-01-01' GROUP BY AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME HAVING COUNT(*) > 5 ORDER BY AUTHOR.LAST_NAME ASC NULLS FIRST OFFSET 1 ROWS FETCH FIRST 2 ROWS ONLY In jOOQ, it will 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 the FIREBIRD_2_5 dialect or just FIREBIRD were specified, then the ROWS clause would be used instead of OFSET ... FETCH.
You can collect the request in parts. This allows you to change it dynamically, which can be used to change the sort order or add additional filtering options.
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, each time you use a string, date, and number literal in the query, and also substitute external variables, jOOQ binds this 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 complete record:
dsl.select() .from(BOOK) .where(BOOK.ID.equal(val(5))) .and(BOOK.TITLE.equal(val("Animal Farm"))) .fetch(); and converted to sql query:
SELECT * FROM BOOK WHERE BOOK.ID = ? AND BOOK.TITLE = ? You do not need to worry about the index of the corresponding parameter, the values will automatically be tied to the desired parameter. If you need to change the value of a parameter, you can do it by selecting the desired parameter by the index number (indexing starts from 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"); In addition, jOOQ supports named parameters. In this case, they must be explicitly created using org.jooq.Param.
// Create a query with a named parameter. You can then use that name for // accessing the parameter again Query 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 parameters Query query2 = dsl.select() .from(AUTHOR) .where(LAST_NAME.equal(param("lastName", "Poe"))); query2.bind("lastName", "Orwell"); Return Values from Selective Queries
jOOQ provides many ways to extract data from SQL queries. We will not list all the ways here, you can read more about them in the Fetching chapter of the jOOQ documentation. In our example, we will use the return to the list of maps (fetchMaps method), which is convenient to use 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 request to insert a record:
INSERT INTO AUTHOR (ID, FIRST_NAME, LAST_NAME) VALUES (100, 'Hermann', 'Hesse'); in jOOQ it will look like this:
dsl.insertInto(AUTHOR, AUTHOR.ID, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME) .values(100, "Hermann", "Hesse") .execute(); :
UPDATE AUTHOR SET FIRST_NAME = 'Hermann', LAST_NAME = 'Hesse' WHERE ID = 3; jOOQ :
dsl.update(AUTHOR) .set(AUTHOR.FIRST_NAME, "Hermann") .set(AUTHOR.LAST_NAME, "Hesse") .where(AUTHOR.ID.equal(3)) .execute(); :
DELETE FROM AUTHOR WHERE ID = 100; :
dsl.delete(AUTHOR) .where(AUTHOR.ID.equal(100)) .execute(); , jOOQ , MERGE .
jOOQ . *.Routines.* , Java:
int invoiceId = dsl.nextval(GEN_INVOICE_ID).intValue(); spAddInvoice(dsl.configuration(), invoiceId, customerId, invoiceDate); SQL :
SELECT NEXT VALUE FOR GEN_INVOICE_ID FROM RDB$DATABASE :
EXECUTE PROCEDURE SP_ADD_INVOICE(:INVOICE_ID, :CUSTOMER_ID, :INVOICE_DATE jOOQ DDL , .
jOOQ , .. SQL , SQL , . READ_WRITE READ_COMMITED REC_VERSION WAIT. JDBC . (. BasicDataSource.setDefaultTransactionIsolation getDataSource JooqConfig).
There are several ways to explicitly manage transactions in jOOQ. Since we are developing an application using the Spring Framework, we will use the transaction manager specified in the configuration (JooqConfig). You can get a transaction manager by declaring the txMgr property in the class as follows:
@Autowired private DataSourceTransactionManager txMgr; In this case, a typical transaction scenario is as follows:
TransactionStatus tx = txMgr.getTransaction(new DefaultTransactionDefinition()); try { // for (int i = 0; i < 2; i++) dsl.insertInto(BOOK) .set(BOOK.ID, 5) .set(BOOK.AUTHOR_ID, 1) .set(BOOK.TITLE, "Book 5") .execute(); // txMgr.commit(tx); } catch (DataAccessException e) { // txMgr.rolback(tx); } However, Spring makes it possible to implement such a script much easier using the @Transactional annotation specified before the class method. In this case, all actions performed in the method will be wrapped in a transaction.
/** * * * @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 specifies how transactions will be handled if our method is called from an external transaction.
- Propagation.REQUIRED - run in an existing transaction, if one exists, otherwise create a new one.
- Propagation.MANDATORY — , , .
- Propagation.SUPPORTS — , , .
- Propagation.NOT_SUPPORTED — . , .
- Propagation.REQUIRES_NEW — . , .
- Propagation.NESTED — , , , . , ; , . , .
- Propagation.NEVER — , .
The isolation parameter specifies the transaction isolation mode. 5 values are supported: DEFAULT, READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE. If the value of the DEFAULT parameter is specified, the default transaction isolation mode will be used. The remaining isolation modes are taken from the SQL standard. In Firebird, there are several other isolation modes and the READ_COMMITED mode only fully meets all the criteria. Thus, in JDBC mode, READ_COMMITTED maps to read_committed to Firebird, REPEATABLE_READ to concurrency (Snapshot), and SERIALIZABLE to consistency. In addition, in addition to the 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 properties of the JDBC connection (for more details seeJaybird 2.1 JDBC driver Java Programmer's Manual Using transactions). 1 , REPEATABLE_READ .
@Transactional , readOnly. read-write.
We will display the data of our application using JavaScript component jqGrid . Currently, jqGrid is distributed under a commercial license, but is free for non-commercial purposes. Instead, you can use the free-jqGrid fork . To display the data in this grid and elements of page navigation, we need to return the data in JSON format, the structure of which looks like this.
{ total: 100, page: 3, records: 3000, rows: [ {id: 1, name: "Ada"}, {id: 2, name: "Smith"}, … ] } Where
- total is the total number of pages;
- page - current page number;
- records - the total number of records;
- rows - an array of records on the current page.
Create a class that describes this structure:
JqGridData.java
package ru.ibase.fbjavaex.jqgrid; import java.util.List; import java.util.Map; /** * jqGrid * 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; /** * * * @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; } /** * * * @return */ public int getTotal() { return total; } /** * * * @return */ public int getPage() { return page; } /** * * * @return */ public int getRecords() { return records; } /** * * * * @return */ public List<Map<String, Object>> getRows() { return rows; } } Now we will write an abstract class that will return the above structure depending on the search and sorting conditions. This class will be the ancestor of classes returning similar structures for specific entities.
Jqgrid.java
/* * 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; /** * 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"; /** * * * @return */ public abstract int getCountRecord(); /** * 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; } /** * * * @return */ public int getLimit() { return this.limit; } /** * * * @return */ public int getOffset() { return this.offset; } /** * * * @return */ public String getIdx() { return this.sIdx; } /** * * * @return */ public String getOrd() { return this.sOrd; } /** * * * @return */ public int getPageNo() { return this.pageNo; } /** * * * @return */ public abstract List<Map<String, Object>> getRecords(); /** * * * @return */ public String getSearchField() { return this.searchField; } /** * * * @return */ public String getSearchString() { return this.searchString; } /** * * * @return */ public String getSearchOper() { return this.searchOper; } /** * * * @param limit */ public void setLimit(int limit) { this.limit = limit; } /** * , * * @param offset */ public void setOffset(int offset) { this.offset = offset; } /** * * * @param sIdx * @param sOrd */ public void setOrderBy(String sIdx, String sOrd) { this.sIdx = sIdx; this.sOrd = sOrd; } /** * * * @param pageNo */ public void setPageNo(int pageNo) { this.pageNo = pageNo; this.offset = (pageNo - 1) * this.limit; } /** * * * @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; } } Please note that this class contains the DSLContext dsl property, which will be used to build queries for data sampling using jOOQ.
Creating directories
Now we can start creating directories. We describe the process of creating directories on the example of a reference book of customers. The product directory is created in a similar way, and if you wish, you can view its source code at the link provided at the end of the article.
First, we implement the class for working with jqGrid, it will be inherited from our abstract class ru.ibase.fbjavaex.jqgrid.JqGrid described above. It has the ability to search and multi-directional sorting by the field NAME. 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; /** * * * @author Simonov Denis */ public class JqGridCustomer extends JqGrid { /** * * * @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; } } /** * * * @return */ @Override public int getCountRecord() { // , SelectFinalStep<?> select = dsl.selectCount() .from(CUSTOMER); SelectQuery<?> query = select.getQuery(); // , if (this.searchFlag) { makeSearchCondition(query); } // return (int) query.fetch().getValue(0, 0); } /** * * * @return */ @Override public List<Map<String, Object>> getRecords() { // SelectFinalStep<?> select = dsl.select() .from(CUSTOMER); SelectQuery<?> query = select.getQuery(); // , if (this.searchFlag) { makeSearchCondition(query); } // switch (this.sOrd) { case "asc": query.addOrderBy(CUSTOMER.NAME.asc()); break; case "desc": query.addOrderBy(CUSTOMER.NAME.desc()); break; } // if (this.limit != 0) { query.addLimit(this.limit); } // if (this.offset != 0) { query.addOffset(this.offset); } // return query.fetchMaps(); } } , CustomerManager, . Snapshot.
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; /** * * * @author Simonov Denis */ public class CustomerManager { @Autowired(required = true) private DSLContext dsl; /** * * * @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(); } /** * * * @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(); } /** * * * @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(); } } We now turn to writing a controller. Controller classes begin with the Controller annotation . To determine the actions of the controller, you must add the @RequestMapping annotation before the method and indicate in it the route along which the controller's action will be triggered. The route is specified in the parameter value. In the method parameter, you can specify the HTTP request method (PUT, GET, POST, DELETE). The input point of our controller will be the index method, it is responsible for displaying the JSP page (view). This page contains markup for grid display, toolbar and navigation.
jqGrid ( /customer/getdata). getData. @ResponseBody, , . @RequestMapping produces = MediaType.APPLICATION_JSON, , JSON. JqGridCustomer . @RequestParam HTTP . GET . value @RequestParam HTTP . required , HTTP . defaultValue , HHTP .
addCustomer . /customer/create, POST . {success: true} , . CustomerManager.
editCustomer /customer/edit . deleteCustomer /customer/delete .
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; /** * * * @author Simonov Denis */ @Controller public class CustomerController { @Autowired(required = true) private JqGridCustomer customerGrid; @Autowired(required = true) private CustomerManager customerManager; /** * * JSP () * * @param map * @return JSP */ @RequestMapping(value = "/customer/", method = RequestMethod.GET) public String index(ModelMap map) { return "customer"; } /** * JSON jqGrid * * @param rows * @param page * @param sIdx * @param sOrd * @param search * @param searchField * @param searchString * @param searchOper * @return JSON jqGrid */ @RequestMapping(value = "/customer/getdata", method = RequestMethod.GET, produces = MediaType.APPLICATION_JSON) @ResponseBody public 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) { customerGrid.setLimit(rows); customerGrid.setPageNo(page); customerGrid.setOrderBy(sIdx, sOrd); if (search) { customerGrid.setSearchCondition(searchField, searchString, searchOper); } return customerGrid.getJqGridData(); } @RequestMapping(value = "/customer/create", method = RequestMethod.POST, produces = MediaType.APPLICATION_JSON) @ResponseBody public Map<String, Object> addCustomer( @RequestParam(value = "NAME", required = true, defaultValue = "") String name, @RequestParam(value = "ADDRESS", required = false, defaultValue = "") String address, @RequestParam(value = "ZIPCODE", required = false, defaultValue = "") String zipcode, @RequestParam(value = "PHONE", required = false, defaultValue = "") String phone) { Map<String, Object> map = new HashMap<>(); try { customerManager.create(name, address, zipcode, phone); map.put("success", true); } catch (Exception ex) { map.put("error", ex.getMessage()); } return map; } @RequestMapping(value = "/customer/edit", method = RequestMethod.POST, produces = MediaType.APPLICATION_JSON) @ResponseBody public Map<String, Object> editCustomer( @RequestParam(value = "CUSTOMER_ID", required = true, defaultValue = "0") int customerId, @RequestParam(value = "NAME", required = true, defaultValue = "") String name, @RequestParam(value = "ADDRESS", required = false, defaultValue = "") String address, @RequestParam(value = "ZIPCODE", required = false, defaultValue = "") String zipcode, @RequestParam(value = "PHONE", required = false, defaultValue = "") String phone) { Map<String, Object> map = new HashMap<>(); try { customerManager.edit(customerId, name, address, zipcode, phone); map.put("success", true); } catch (Exception ex) { map.put("error", ex.getMessage()); } return map; } @RequestMapping(value = "/customer/delete", method = RequestMethod.POST, produces = MediaType.APPLICATION_JSON) @ResponseBody public Map<String, Object> deleteCustomer( @RequestParam(value = "CUSTOMER_ID", required = true, defaultValue = "0") int customerId) { Map<String, Object> map = new HashMap<>(); try { customerManager.delete(customerId); map.put("success", true); } catch (Exception ex) { map.put("error", ex.getMessage()); } return map; } } The JSP page for displaying the customer reference book contains nothing special: the markup with the main parts of the page, the table for displaying the grid, and the block for displaying the navigation bar. JSP templates are not a very advanced tool, if you prefer, you can replace them with other template systems that support inheritance. The ../jspf/head.jspf file contains common scripts and styles for all pages of the site, and the ../jspf/menu.jspf file contains the main menu of the site. We will not give their code, it is quite simple and, if you wish, you can view 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> Spring MVC Firebird jOOQ</title> <!-- --> <%@ include file="../jspf/head.jspf" %> <script src="${cp}/resources/js/jqGridCustomer.js"></script> </head> <body> <!-- --> <%@ 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 - Spring MVC Firebird 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, // options: $.extend({ baseAddress: null, showEditorPanel: true }, options), // getColModel: function () { return [ { label: 'Id', // name: 'CUSTOMER_ID', // key: true, // hidden: true // }, { label: 'Name', // name: 'NAME', // width: 240, // sortable: true, // editable: true, // edittype: "text", // search: true, // searchoptions: { sopt: ['eq', 'bw', 'cn'] // }, editoptions: {size: 30, maxlength: 60}, // editrules: {required: true} // , }, { label: 'Address', name: 'ADDRESS', width: 300, sortable: false, // editable: true, // search: false, // edittype: "textarea", // editoptions: {maxlength: 250, cols: 30, rows: 4} }, { label: 'Zip Code', name: 'ZIPCODE', width: 30, sortable: false, editable: true, search: false, edittype: "text", editoptions: {size: 30, maxlength: 10} }, { label: 'Phone', name: 'PHONE', width: 80, sortable: false, editable: true, search: false, edittype: "text", editoptions: {size: 30, maxlength: 14} } ]; }, // initGrid: function () { // url var url = jqGridCustomer.options.baseAddress + '/customer/getdata'; jqGridCustomer.dbGrid = $("#jqGridCustomer").jqGrid({ url: url, datatype: "json", // mtype: "GET", // http colModel: jqGridCustomer.getColModel(), rowNum: 500, // loadonce: false, // sortname: 'NAME', // NAME sortorder: "asc", // width: window.innerWidth - 80, // height: 500, // viewrecords: true, // guiStyle: "bootstrap", iconSet: "fontAwesome", caption: "Customers", // // pager: 'jqPagerCustomer' }); }, // getEditOptions: function () { return { url: jqGridCustomer.options.baseAddress + '/customer/edit', reloadAfterSubmit: true, closeOnEscape: true, closeAfterEdit: true, drag: true, width: 400, afterSubmit: jqGridCustomer.afterSubmit, editData: { // CUSTOMER_ID: function () { // var selectedRow = jqGridCustomer.dbGrid.getGridParam("selrow"); // var value = jqGridCustomer.dbGrid.getCell(selectedRow, 'CUSTOMER_ID'); return value; } } }; }, // getAddOptions: function () { return { url: jqGridCustomer.options.baseAddress + '/customer/create', reloadAfterSubmit: true, closeOnEscape: true, closeAfterAdd: true, drag: true, width: 400, afterSubmit: jqGridCustomer.afterSubmit }; }, // getDeleteOptions: function () { return { url: jqGridCustomer.options.baseAddress + '/customer/delete', reloadAfterSubmit: true, closeOnEscape: true, closeAfterDelete: true, drag: true, msg: " ?", afterSubmit: jqGridCustomer.afterSubmit, delData: { // CUSTOMER_ID: function () { var selectedRow = jqGridCustomer.dbGrid.getGridParam("selrow"); var value = jqGridCustomer.dbGrid.getCell(selectedRow, 'CUSTOMER_ID'); return value; } } }; }, // initPagerWithEditors: function () { jqGridCustomer.dbGrid.jqGrid('navGrid', '#jqPagerCustomer', { // search: true, // add: true, // edit: true, // del: true, // view: true, // refresh: true, // // searchtext: "", addtext: "", edittext: "", deltext: "", viewtext: "", viewtitle: " ", refreshtext: "" }, jqGridCustomer.getEditOptions(), jqGridCustomer.getAddOptions(), jqGridCustomer.getDeleteOptions() ); }, // initPagerWithoutEditors: function () { jqGridCustomer.dbGrid.jqGrid('navGrid', '#jqPagerCustomer', { // search: true, // add: false, // edit: false, // del: false, // view: false, // refresh: true, // // searchtext: "", viewtext: "", viewtitle: " ", refreshtext: "" } ); }, // initPager: function () { if (jqGridCustomer.options.showEditorPanel) { jqGridCustomer.initPagerWithEditors(); } else { jqGridCustomer.initPagerWithoutEditors(); } }, // init: function () { jqGridCustomer.initGrid(); jqGridCustomer.initPager(); }, // () afterSubmit: function (response, postdata) { var responseData = response.responseJSON; // if (responseData.hasOwnProperty("error")) { if (responseData.error.length) { return [false, responseData.error]; } } else { // $(this).jqGrid( 'setGridParam', { datatype: 'json' } ).trigger('reloadGrid'); } return [true, "", 0]; } }; jqGridCustomer.init(); return jqGridCustomer; }; })(jQuery); jqGrid initGrid html jqGridCustomer. () getColModel. jqGrid . , . jqGrid jqGrid ColModel API .
( initPagerWithEditors initPagerWithoutEditors ). jqPagerCustomer. Navigator jqGrid.
getEditOptions, getAddOptions, getDeleteOptions , . url , OK . afterSubmit – , . afterSubmit , . , , . editData. , . , , .
. . , . – , . WorkingPeriod. workingPeriod ru.ibase.fbjavaex.config.JooqConfig.
WorkingPeriod.java
package ru.ibase.fbjavaex.config; import java.sql.Timestamp; import java.time.LocalDateTime; /** * * * @author Simonov Denis */ public class WorkingPeriod { private Timestamp beginDate; private Timestamp endDate; /** * */ WorkingPeriod() { // this.beginDate = Timestamp.valueOf("2015-06-01 00:00:00"); this.endDate = Timestamp.valueOf(LocalDateTime.now().plusDays(1)); } /** * * * @return */ public Timestamp getBeginDate() { return this.beginDate; } /** * * * @return */ public Timestamp getEndDate() { return this.endDate; } /** * * * @param value */ public void setBeginDate(Timestamp value) { this.beginDate = value; } /** * * * @param value */ public void setEndDate(Timestamp value) { this.endDate = value; } /** * * * @param beginDate * @param endDate */ public void setRangeDate(Timestamp beginDate, Timestamp endDate) { this.beginDate = beginDate; this.endDate = endDate; } } In our application there will be one magazine "Invoices". Invoice - consists of a header, which describes common attributes (number, date, customer ...), and invoice items (product name, quantity, cost, etc.). The invoice header is displayed in the main grid, and the positions can be viewed in the detail grid, which is expanded by clicking on the “+” icon on the desired document.
We implement a class for viewing invoice header through 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 the account. 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; /** * - * * @author Simonov Denis */ public class JqGridInvoice extends JqGrid { @Autowired(required = true) private WorkingPeriod workingPeriod; /** * * * @param query */ private void makeSearchCondition(SelectQuery<?> query) { // , // // if (this.searchString.isEmpty()) { return; } if (this.searchField.equals("CUSTOMER_NAME")) { switch (this.searchOper) { case "eq": // equal query.addConditions(CUSTOMER.NAME.eq(this.searchString)); break; case "bw": // starting with query.addConditions(CUSTOMER.NAME.startsWith(this.searchString)); break; case "cn": // containing query.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; } } } /** * * * @return */ @Override public 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); } /** * - * * @return */ @Override public 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(); // if (this.searchFlag) { makeSearchCondition(query); } // if (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; } } // if (this.limit != 0) { query.addLimit(this.limit); } if (this.offset != 0) { query.addOffset(this.offset); } return query.fetchMaps(); } } - jqGrid . -, , - .
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; /** * - * * @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; } /** * * * @return */ @Override public 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); } /** * * * @return */ @Override public 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(); } } , , ( ), , ru.ibase.fbjavaex.managers.InvoiceManager, . Snapshot. ( , ).
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; /** * * * @author Simonov Denis */ public class InvoiceManager { @Autowired(required = true) private DSLContext dsl; /** * * * @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); } /** * * * @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); } /** * * * @param invoiceId */ @Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.REPEATABLE_READ) public void pay(Integer invoiceId) { spPayForInovice(this.dsl.configuration(), invoiceId); } /** * * * @param invoiceId */ @Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.REPEATABLE_READ) public void delete(Integer invoiceId) { spDeleteInvoice(this.dsl.configuration(), invoiceId); } /** * * * @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); } /** * * * @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); } /** * * * @param invoiceLineId */ @Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.REPEATABLE_READ) public void deleteInvoiceLine(Integer invoiceLineId) { spDeleteInvoiceLine(this.dsl.configuration(), invoiceLineId); } } . index, JSP (). , .
jqGrid ( /invoice/getdata). getData ( ). getDetailData ( /invoice/getdetaildata). , . addInvoice, editInvoice, payInvoice, deleteInvoice , , . addInvoiceLine, editInvoiceLine, deleteInvoiceLine , .
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; /** * - * * @author Simonov Denis */ @Controller public class InvoiceController { @Autowired(required = true) private JqGridInvoice invoiceGrid; @Autowired(required = true) private JqGridInvoiceLine invoiceLineGrid; @Autowired(required = true) private InvoiceManager invoiceManager; /** * , * HTTP * * @param binder */ @InitBinder public void initBinder(WebDataBinder binder) { binder.registerCustomEditor(Timestamp.class, new PropertyEditorSupport() { @Override public 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); } } }); } /** * * JSP () * * @param map * @return JSP */ @RequestMapping(value = "/invoice/", method = RequestMethod.GET) public String index(ModelMap map) { return "invoice"; } /** * JSON jqGrid * * @param rows * @param page * @param sIdx * @param sOrd * @param search * @param searchField * @param searchString * @param searchOper * @param filters * @return */ @RequestMapping(value = "/invoice/getdata", method = RequestMethod.GET, produces = MediaType.APPLICATION_JSON) @ResponseBody public 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(); } /** * * * @param customerId * @param invoiceDate * @return */ @RequestMapping(value = "/invoice/create", method = RequestMethod.POST, produces = MediaType.APPLICATION_JSON) @ResponseBody public 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; } /** * * * @param invoiceId * @param customerId * @param invoiceDate * @return */ @RequestMapping(value = "/invoice/edit", method = RequestMethod.POST, produces = MediaType.APPLICATION_JSON) @ResponseBody public 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; } /** * * * @param invoiceId * @return */ @RequestMapping(value = "/invoice/pay", method = RequestMethod.POST, produces = MediaType.APPLICATION_JSON) @ResponseBody public 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; } /** * * * @param invoiceId * @return */ @RequestMapping(value = "/invoice/delete", method = RequestMethod.POST, produces = MediaType.APPLICATION_JSON) @ResponseBody public 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; } /** * * * @param invoice_id * @return */ @RequestMapping(value = "/invoice/getdetaildata", method = RequestMethod.GET, produces = MediaType.APPLICATION_JSON) @ResponseBody public JqGridData getDetailData( @RequestParam(value = "INVOICE_ID", required = true) int invoice_id) { invoiceLineGrid.setInvoiceId(invoice_id); return invoiceLineGrid.getJqGridData(); } /** * * * @param invoiceId * @param productId * @param quantity * @return */ @RequestMapping(value = "/invoice/createdetail", method = RequestMethod.POST, produces = MediaType.APPLICATION_JSON) @ResponseBody public 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; } /** * * * @param invoiceLineId * @param quantity * @return */ @RequestMapping(value = "/invoice/editdetail", method = RequestMethod.POST, produces = MediaType.APPLICATION_JSON) @ResponseBody public 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; } /** * * * @param invoiceLineId * @return */ @RequestMapping(value = "/invoice/deletedetail", method = RequestMethod.POST, produces = MediaType.APPLICATION_JSON) @ResponseBody public 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, invoice controller controlers are similar to reference book controllers with two exceptions:
- The controller displays and works with the data of both the main and detail grid.
- The invoice invoice is filtered by the date field so that only those invoice invoices that are included in the working period are included in the sample.
.
java.sql.Timestamp Java , Firebird TIMESTAMP . .
Java . , Firebird TIMESTAMP WITH TIMEZONE. Java , ( UTC ). JSON UTC. JavaScript.
| Attention! Java , . JDK. JDK, . |
JSON ( 1 1970). . WebAppConfig, , configureMessageConverters SerializationFeature.WRITE_DATES_AS_TIMESTAMPS false.
@Configuration @ComponentScan("ru.ibase.fbjavaex") @EnableWebMvc public class WebAppConfig extends WebMvcConfigurerAdapter { @Override public 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 controller describes how the textual 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 headers and a navigation bar. The positions of the invoice are displayed when opening the invoice header, 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> Spring MVC Firebird jOOQ</title> <!-- --> <%@ 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> <!-- --> <%@ 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 - Spring MVC Firebird 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 centered in the JavaScript module /resources/js/jqGridInvoice.js
jqGridInvoice.js
var JqGridInvoice = (function ($, jqGridProductFactory, jqGridCustomerFactory) { return function (options) { var jqGridInvoice = { dbGrid: null, detailGrid: null, // options: $.extend({ baseAddress: null }, options), // () getInvoiceColModel: function () { return [ { label: 'Id', // name: 'INVOICE_ID', // key: true, // hidden: true // }, { label: 'Customer Id', // name: 'CUSTOMER_ID', // hidden: true, // editrules: {edithidden: true, required: true}, // editable: true, // edittype: 'custom', // editoptions: { custom_element: function (value, options) { // input return $("<input>") .attr('type', 'hidden') .attr('rowid', options.rowId) .addClass("FormElement") .addClass("form-control") .val(value) .get(0); } } }, { label: 'Date', name: 'INVOICE_DATE', width: 60, // sortable: true, // editable: true, // search: true, // edittype: "text", // align: "right", // formatter: jqGridInvoice.dateTimeFormatter, // sorttype: 'date', // formatoptions: { srcformat: 'Ymd\TH:i:s', // newformat: 'dmY H:i:s' // }, editoptions: { // dataInit: function (element) { // datepicker $(element).datepicker({ id: 'invoiceDate_datePicker', dateFormat: 'dd.mm.yy', minDate: new Date(2000, 0, 1), maxDate: new Date(2030, 0, 1) }); } }, searchoptions: { // dataInit: function (element) { // datepicker $(element).datepicker({ id: 'invoiceDate_datePicker', dateFormat: 'dd.mm.yy', minDate: new Date(2000, 0, 1), maxDate: new Date(2030, 0, 1) }); }, searchoptions: { // sopt: ['eq', 'lt', 'le', 'gt', 'ge'] } } }, { label: 'Customer', name: 'CUSTOMER_NAME', width: 250, editable: true, edittype: "text", editoptions: { size: 50, maxlength: 60, readonly: true // }, 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', // sorttype: '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", // formatter: "checkbox", stype: "checkbox", align: "center", editoptions: { value: "1", offval: "0" } } ]; }, initGrid: function () { // url var url = jqGridInvoice.options.baseAddress + '/invoice/getdata'; jqGridInvoice.dbGrid = $("#jqGridInvoice").jqGrid({ url: url, datatype: "json", // mtype: "GET", // http // colModel: jqGridInvoice.getInvoiceColModel(), rowNum: 500, // loadonce: false, // sortname: 'INVOICE_DATE', // sortorder: "desc", // width: window.innerWidth - 80, // height: 500, // viewrecords: true, // guiStyle: "bootstrap", iconSet: "fontAwesome", caption: "Invoices", // pager: '#jqPagerInvoice', // subGrid: true, // // javascript subGridRowExpanded: jqGridInvoice.showChildGrid, subGridOptions: { // // reloadOnExpand: false, // "+" selectOnExpand: true } }); }, // dateTimeFormatter: function(cellvalue, options, rowObject) { var date = new Date(cellvalue); return date.toLocaleString().replace(",", ""); }, // getTemplate: function () { var template = "<div style='margin-left:15px;' id='dlgEditInvoice'>"; template += "<div>{CUSTOMER_ID} </div>"; template += "<div> Date: </div><div>{INVOICE_DATE}</div>"; // template += "<div> Customer <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></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; }, // UTC convertToUTC: function(datetime) { if (datetime) { // var 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; } else return null; }, // - getEditInvoiceOptions: 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); } } }; }, // - getAddInvoiceOptions: 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); } } }; }, // - getDeleteInvoiceOptions: function () { return { url: jqGridInvoice.options.baseAddress + '/invoice/delete', reloadAfterSubmit: true, closeOnEscape: true, closeAfterDelete: true, drag: true, msg: " -?", 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 () { // jqGridInvoice.dbGrid.jqGrid('navGrid', '#jqPagerInvoice', { search: true, // add: true, // edit: true, // del: true, // view: false, // refresh: true, // searchtext: "", addtext: "", edittext: "", deltext: "", viewtext: "", viewtitle: " ", refreshtext: "" }, jqGridInvoice.getEditInvoiceOptions(), jqGridInvoice.getAddInvoiceOptions(), jqGridInvoice.getDeleteInvoiceOptions() ); // var urlPay = jqGridInvoice.options.baseAddress + '/invoice/pay'; jqGridInvoice.dbGrid.navButtonAdd('#jqPagerInvoice', { buttonicon: "glyphicon-usd", title: "", caption: "", position: "last", onClickButton: function () { // var id = jqGridInvoice.dbGrid.getGridParam("selrow"); if (id) { $.ajax({ url: urlPay, type: 'POST', data: {INVOICE_ID: id}, success: function (data) { // if (data.hasOwnProperty("error")) { jqGridInvoice.alertDialog('', data.error); } else { // $("#jqGridInvoice").jqGrid( 'setGridParam', { datatype: 'json' } ).trigger('reloadGrid'); } } }); } } } ); }, init: function () { jqGridInvoice.initGrid(); jqGridInvoice.initPager(); }, afterSubmit: function (response, postdata) { var responseData = response.responseJSON; // if (responseData.hasOwnProperty("error")) { if (responseData.error.length) { return [false, responseData.error]; } } else { // $(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) { // 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) { // 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 } } ]; }, // getEditInvoiceLineOptions: 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(); } } }; }, // getAddInvoiceLineOptions: 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(); } } }; }, // getDeleteInvoiceLineOptions: function () { return { url: jqGridInvoice.options.baseAddress + '/invoice/deletedetail', reloadAfterSubmit: true, closeOnEscape: true, closeAfterDelete: true, drag: true, msg: " ?", 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; } } }; }, // // // showChildGrid: function (parentRowID, parentRowKey) { var childGridID = parentRowID + "_table"; var childGridPagerID = parentRowID + "_pager"; // // var childGridURL = jqGridInvoice.options.baseAddress + '/invoice/getdetaildata'; childGridURL = childGridURL + "?INVOICE_ID=" + encodeURIComponent(parentRowKey); // HTML // $('<table>') .attr('id', childGridID) .appendTo($('#' + parentRowID)); $('<div>') .attr('id', childGridPagerID) .addClass('scroll') .appendTo($('#' + parentRowID)); // jqGridInvoice.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 }); // $("#" + childGridID).jqGrid('navGrid', '#' + childGridPagerID, { search: false, // add: true, // edit: true, // del: true, // refresh: true // }, jqGridInvoice.getEditInvoiceLineOptions(), jqGridInvoice.getAddInvoiceLineOptions(), jqGridInvoice.getDeleteInvoiceLineOptions() ); }, // getTemplateDetail: function () { var template = "<div style='margin-left:15px;' id='dlgEditInvoiceLine'>"; template += "<div>{INVOICE_ID} </div>"; template += "<div>{PRODUCT_ID} </div>"; // template += "<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> </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; }, // showProductWindow: 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(" ").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 }); }, // showCustomerWindow: function () { // var dlg = $('<div>') .attr('id', 'dlgChooseCustomer') .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', '730px') .appendTo($('<div>') .addClass('modal-dialog') .appendTo(dlg)); // var dlgHeader = $('<div>').addClass("modal-header").appendTo(dlgContent); // "X" $("<button>") .addClass("close") .attr('type', 'button') .attr('aria-hidden', 'true') .attr('data-dismiss', 'modal') .html("×") .appendTo(dlgHeader); // $("<h5>").addClass("modal-title").html(" ").appendTo(dlgHeader); // var dlgBody = $('<div>') .addClass("modal-body") .appendTo(dlgContent); // var dlgFooter = $('<div>').addClass("modal-footer").appendTo(dlgContent); // "OK" $("<button>") .attr('type', 'button') .addClass('btn') .html('OK') .on('click', function () { var rowId = $("#jqGridCustomer").jqGrid("getGridParam", "selrow"); var row = $("#jqGridCustomer").jqGrid("getRowData", rowId); // // $('#dlgEditInvoice input[name=CUSTOMER_ID]').val(rowId); $('#dlgEditInvoice input[name=CUSTOMER_NAME]').val(row["NAME"]); dlg.modal('hide'); }) .appendTo(dlgFooter); // "Cancel" $("<button>") .attr('type', 'button') .addClass('btn') .html('Cancel') .on('click', function () { dlg.modal('hide'); }) .appendTo(dlgFooter); // $('<table>') .attr('id', 'jqGridCustomer') .appendTo(dlgBody); // $('<div>') .attr('id', 'jqPagerCustomer') .appendTo(dlgBody); dlg.on('hidden.bs.modal', function () { dlg.remove(); }); // dlg.modal(); jqGridCustomerFactory({ baseAddress: jqGridInvoice.options.baseAddress }); }, // alertDialog: 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); , , . subGrid true. , subGridRowExpanded, showChildGrid. . jqGridInvoice.dbGrid.navButtonAdd (. initPager).
. . jqGrid, , . getTemplate. invoiceGrid.showCustomerWindow(). JqGridCustomer. CUSTOMER_ID. editData , , .
. , InvoiceController UTC, . jqGridInvoice.dateTimeFormatter formatter, INVOICE_DATE. – UTC. convertToUTC.
, , getTemplateDetail. invoiceGrid.showProductWindow(). JqGridProduct.
The code of the JqGridInvoice module is commented in detail so that you can understand the logic of its operation. Additional explanations can be found in it.
Finally, here are some screenshots of the resulting web application.
This completes my example. Source codes you can download the link .
You can download scripts to create a database using the links Firebird_2_5.sql and Firebird_3_0.sql .
You can download ready databases by clicking on Firebird_2_5_DB and Firebird_3_0_DB .
Source: https://habr.com/ru/post/331098/
All Articles