In this article, I share my experience in business application modernization and some of the key findings on migrating a complex Oracle Forms application to a modern Java-based web application implemented in Java with Vaadin.
This article is based on my talk at DawsCon 2021:
From low-code to in-house development
It started with a request from a new customer to support the modernization of the user interface of his ERP (Enterprise Resource Planning) product. The customer has been very successful with his ERP product in the market in Switzerland and Germany for more than thirty years. However, the current version, which is based on Oracle Forms, cannot be used in the browser or on mobile devices. Therefore, a project was started to find a replacement for Oracle Forms.
UI Transformation from Oracle Forms to Web:
The first choice was a low-code tool that promised to generate the essential parts of the user interface through a UI designer and, to keep the programming effort as low as possible, through an abstract programming model. However, the project did not progress as expected. An initial analysis showed that the low-code tool was a typical 80/20 solution. In other words, the first 80% of the implementation was simple because the standard cases were covered by the tool, but the remaining 20% involved a lot of effort, as many special cases had crept in over the long life of the ERP product.
Based on the fact that the low-code tool generated a web UI based on Vaadin, we agreed to create a prototype with Vaadin Flow. It turned out that Vaadin could be a very good fit for the UI modernization. After some discussion, the customer decided to part with the low-code tool and use Vaadin as the UI framework.
One aspect that makes this project challenging is the size and dynamic character of the application. The database consists of around 1800 tables and views, 4600 procedures and functions, and over 100 user-defined types. The application menu includes more than 800 entries. The forms and dialogs change depending on the user rights and/or the data at runtime. The customers also need the option to adapt the user interface to their needs.
UI Designer:
The diagram below shows the concept: The user accesses a UI module and the UI is generated dynamically based on the data and authorizations. This functionality poses certain challenges, especially with regard to the performance of the application, since a lot of data has to be loaded from the database.
Runtime-generated UI:
Data access
As mentioned before, there are 1800 tables and views and 4600 procedures in the database of the ERP system. A way to access them had to be found. JDBC was set as the database interface of course, but is an O/R mapper like Hibernate the right approach here? How much effort does the mapping of 1800 entities mean? And how can the 4600 procedures be called type-safe? Since the UI is generated at runtime, the type information from the database is required. How can these be accessed? Fortunately, all of these questions are answered by a single framework: jOOQ.
jOOQ integrates SQL in Java and makes it possible to use SQL in a type-safe manner. jOOQ generates Java code based on the metadata of the database and offers a domain-specific language (DSL), in the form of a Fluent API, to write type-safe SQLs, and to execute procedures and functions.
To generate the code, the jOOQ CodeGen Maven plug-in can be used:
<plugin>
<groupId>org.jooq</groupId>
<artifactId>jooq-codegen-maven</artifactId>
<configuration>
<generator>
<database>
<name>org.jooq.meta.extensions.ddl.DDLDatabase</name>
<properties>
<property>
<key>scripts</key>
<value>src/main/resources/db/migration/*.sql</value>
</property>
</properties>
</database>
<target>
<packageName>io.seventytwo.demo.database</packageName>
<directory>target/generated-sources/jooq</directory>
</target>
</generator>
</configuration>
</plugin>
The database can either be accessed directly via a JDBC connection or DDL scripts can be used as the basis for generation. The snippet above shows only a part of the configuration options of the jOOQ code generator.
In the above example, FlyWay is used and the result can be seen in the next screenshot. A class is generated for all database objects. In the example, there is an Employee and a Department table as well as a VEmployee view.
In addition, three record classes are generated. A record is the representation of a data record in the table or view, and if the table contains the primary key, an updatable record is generated. An UpdatableRecord enables insert, update or delete operations/actions.
The classes generated by jOOQ contain all the metadata of the table. In the generated code, we also find table columns with information on the name, type, whether the column is nullable and whether it is a primary key column, among other things. Even the comment from the database is transferred. This is extremely useful and increases efficiency because the developer does not have to go to the database to look up this information. This information can also be used to validate user input.
Now let’s have a look at how to use the generated metamodel and the DSL to write type-safe SQL:
// Insert
dsl.insertInto(DEPARTMENT)
.columns(DEPARTMENT.NAME)
.values("IT")
.execute();
// Find all
List<EmployeeRecord> records = dsl.selectFrom(EMPLOYEE).fetch();
// Select with projection
List<Record1<String>> records =
dsl.select(EMPLOYEE.NAME)
.from(EMPLOYEE)
.join(DEPARTMENT).on(DEPARTMENT.ID.eq(EMPLOYEE.DEPARTMENT_ID))
.where(DEPARTMENT.NAME.eq("IT"))
.fetch();
It is important to mention that jOOQ not only converts the Java code into SQL, but also takes the used database into account and, under certain circumstances, emulates functionality. This results in an independence that allows the used database to be exchanged. jOOQ supports all common database products.
Use jOOQ in combination with Vaadin
The most important component in developing data-centric applications is the Grid. Vaadin provides a Grid that has excellent support for paging, sorting and filtering. Let’s have a look how to use jOOQ with the Vaadin Grid. First we create a Grid based on VEmployeeRecord and add the columns:
Grid<VEmployeeRecord> grid = new Grid<>();
grid.addColumn(VEmployeeRecord::getEmployeeId)
.setHeader("ID")
.setSortProperty("ID");
grid.addColumn(VEmployeeRecord::getEmployeeName)
.setHeader("Name")
.setSortProperty(V_EMPLOYEE.EMPLOYEE_NAME.getName());
grid.addColumn(VEmployeeRecord::getDepartmentName)
.setHeader("Department")
.setSortProperty(V_EMPLOYEE.DEPARTMENT_NAME.getName());
To load the data, a data provider must be used. There are two variants, depending on where the data is stored. For small amounts of data, an in-memory data provider that is based on a collection can be used. The CallbackDataProvider, which supports lazy loading, is perfect for a larger amount of records. The grid data is automatically loaded when scrolling in lazy-loading mode. A FetchCallback, which returns a stream of records, and a CountCallback, which returns the number of records, must be passed to the CallbackDataProvider.
As an optional third parameter, a ValueProvider, which must return a unique key, can also be provided. This key is used to identify the grid item. Without this ValueProvider, equals() and hashCode() of the data class are used. The two callback methods receive a VaadinQuery as a parameter. The query contains offset, limit, sorting, and, if configured, a filter object to filter and sort the result set.
The next code example shows how well Vaadin and jOOQ go together:
dataProvider = new CallbackDataProvider<VEmployeeRecord, Condition>(
query -> dsl.selectFrom(V_EMPLOYEE)
.where(query.getFilter().orElse(DSL.noCondition()))
.orderBy(createOrderBy(query))
.offset(query.getOffset())
.limit(query.getLimit())
.fetchStream(),
query -> dsl.selectCount()
.from(V_EMPLOYEE)
.where(query.getFilter().orElse(DSL.noCondition()))
.fetchOneInto(Integer.class),
VEmployeeRecord::getEmployeeId)
.withConfigurableFilter();
We use withConfigurableFilter() to generate a ConfigurableFilterDataProvider.
The next code snippet shows how a jOOQ Condition (the where clause) is generated, based on the input in a TextField, and then passed to the setFilter() method:
TextField filter = new TextField("Filter");
filter.setValueChangeMode(ValueChangeMode.EAGER);
filter.addValueChangeListener(event -> {
if (StringUtils.isNotBlank(event.getValue())) {
dataProvider.setFilter(upper(V_EMPLOYEE.EMPLOYEE_NAME)
.like("%" + event.getValue().toUpperCase() + "%"));
} else {
dataProvider.setFilter(null);
}
});
Forms
The second essential element of data-centric applications are forms. Vaadin has the concept of data binding. The Binder class is responsible to bind values from a Java object to UI components, and to take care of the conversion and validation. A binding can be writable or just read-only. A converter must be specified for all fields that are not of the string type. Common converters, e.g. to convert strings into numbers or date types, are already built in. But writing your own converter is also simple.
The next code example highlights how to use binding together with jOOQ records. It also shows how a field can be marked as a mandatory field. In order to bind an object, the setBean() method must be called by the binder. The validate() method can be used to check whether the input is valid:
Binder<EmployeeRecord> binder = new Binder<>();
TextField id = new TextField("ID");
binder.forField(id)
.withConverter(new StringToIntegerConverter("Must not be empty"))
.bind(EmployeeRecord::getId, null);
TextField name = new TextField("Name");
binder.forField(name)
.asRequired()
.withValidator(s -> s.length() >=3, "Name is to short")
.bind(EmployeeRecord::getName, EmployeeRecord::setName);
Since the EmployeeRecord is of type UpdatableRecord, we can simply call store() to save the changes:
Button save = new Button("Save", buttonClickEvent ->
transactionTemplate.executeWithoutResult(transactionStatus -> {
if (binder.validate().isOk()) {
employeeRecord.store();
}
}));
Conclusion
Vaadin and jOOQ are a great combination to develop type-safe data-centric web applications. Vaadin enables Java developers to quickly and efficiently develop web applications with a great set of very convenient components. The fact that no REST API is required is especially important and means great savings in the development and maintenance of the application. On the data access layer, the full power of the database can be used efficiently and with type safety with jOOQ. There is no need for manual mapping of Java classes to database tables, as is the case with O/R mapping frameworks like Hibernate. KISS (Keep it simple and stupid)!
Code Samples
You can find source code samples on GitHub.