Master-Detail with Hilla

In this post, I’ll explain how to use the web application framework Hilla to create a master-detail view with a Grid to display the data and a Form to edit the data.

What is Hilla?

Hilla integrates a Spring Boot Java back end with a reactive TypeScript front end. It helps you build apps faster with type-safe server communication, including UI components, and integrated tooling.

From hilla.dev

Hilla uses TypeScript with Lit and Webcomponents in the frontend and Spring Boot in the backend. In the backend you’ll create endpoints from which the API and the TypeScript will be generated. This makes the access to the backend much easier and it’s also typesafe and compile-time-checked.

How to Start?

The source code is available on GitHub: https://github.com/simasch/hilla-master-detail-with-filter

There are two ways to create a new project:
Use npx:

npx @vaadin/cli init --hilla my-hilla-app

Or use start.vaadin.com. Make sure that you delete all views and add one of those

The Endpoint

The demo project uses a SamplePerson entity. This is stored in an H2 database using Spring Data JPA.

To access the data from the frontend we need to create a Hilla Endpoint:

@Endpoint
@AnonymousAllowed
public class SamplePersonEndpoint {

    private final SamplePersonRepository repository;

    @Autowired
    public SamplePersonEndpoint(SamplePersonRepository repository) {
        this.repository = repository;
    }

    @Nonnull
    public Page<@Nonnull SamplePerson> list(String filter, Pageable pageable) {
        if (filter == null || filter.equals("")) {
            return repository.findAll(pageable);
        } else {
            return repository.findAllByFirstNameLikeIgnoreCaseOrLastNameLikeIgnoreCase(
                                                   filter + "%", filter + "%", pageable);
        }
    }

    public Optional<SamplePerson> get(@Nonnull UUID id) {
        return repository.findById(id);
    }

    @Nonnull
    public SamplePerson update(@Nonnull SamplePerson entity) {
        return repository.save(entity);
    }

    public void delete(@Nonnull UUID id) {
        repository.deleteById(id);
    }

    public long count(String filter) {
        if (filter == null || filter.equals("")) {
            return repository.count();
        } else {
            return repository.countAllByFirstNameLikeIgnoreCaseOrLastNameLikeIgnoreCase(
                                                              filter + "%", filter + "%");
        }
    }

}

Hilla Endpoints are secure by default and you can use the security annotations @RolesAllowed, @PermitAll etc. But as we don’t use authentication in the simple example we have to annotate the Endpoint with @AnonymousAllowed to allow unauthenticated access.

The @Nonnull annotations are used by the TypeScript generator to define the nullability. This topic is explained in the documentation.

From this Java class TypeScript code will be generated:

// @ts-ignore
import client from './connect-client.default';
// @ts-ignore
import { Subscription } from '@hilla/frontend';

import type SamplePerson from './com/example/application/data/entity/SamplePerson';
import type Pageable from './dev/hilla/mappedtypes/Pageable';

function _count(
 filter: string | undefined
): Promise<number>
{
 return client.call('SamplePersonEndpoint', 'count', {filter});
}

function _delete(
 id: string
): Promise<void>
{
 return client.call('SamplePersonEndpoint', 'delete', {id});
}

function _get(
 id: string
): Promise<SamplePerson | undefined>
{
 return client.call('SamplePersonEndpoint', 'get', {id});
}

function _list(
 filter: string | undefined,
 pageable: Pageable | undefined
): Promise<Array<SamplePerson | undefined>>
{
 return client.call('SamplePersonEndpoint', 'list', {filter, pageable});
}

function _update(
 entity: SamplePerson
): Promise<SamplePerson>
{
 return client.call('SamplePersonEndpoint', 'update', {entity});
}
export {
  _count as count,
  _delete as delete,
  _get as get,
  _list as list,
  _update as update,
};

As you can see all methods from the Endpoint are available and calling the backend will be very simple:

const data = await SamplePersonEndpoint.list(
                   this.filter, {pageNumber: params.page, pageSize: params.pageSize, sort});

As you can imagine changing the Endpoint on the Java side will result in changing the generated TypeScript code and you’ll get compiler errors if there are breaking changes.

The Entity

For simplicity, we directly use the JPA Entity in the Endpoint. We also use annotations to define nullability and looking at the email property also some validation.

@Entity
public class SamplePerson extends AbstractEntity {

    @Nonnull
    private String firstName;
    @Nonnull
    private String lastName;
    @Email
    @Nonnull
    private String email;
    @Nonnull
    private String phone;
    private LocalDate dateOfBirth;
    @Nonnull
    private String occupation;
    @Nonnull
    private boolean important;

...
}

This entity will result in two TypeScript types generated by Hilla.

export default interface SamplePerson extends AbstractEntity {
  firstName: string;
  lastName: string;
  email: string;
  phone: string;
  dateOfBirth?: string;
  occupation: string;
  important: boolean;
}
export default class SamplePersonModel<T extends SamplePerson = SamplePerson> extends AbstractEntityModel<T> {
  static createEmptyValue: () => SamplePerson;

  get firstName(): StringModel {
    return this[_getPropertyModel]('firstName', StringModel, [false]);
  }

  get lastName(): StringModel {
    return this[_getPropertyModel]('lastName', StringModel, [false]);
  }

  get email(): StringModel {
    return this[_getPropertyModel]('email', StringModel, [false, new Email()]);
  }

  get phone(): StringModel {
    return this[_getPropertyModel]('phone', StringModel, [false]);
  }

  get dateOfBirth(): StringModel {
    return this[_getPropertyModel]('dateOfBirth', StringModel, [true]);
  }

  get occupation(): StringModel {
    return this[_getPropertyModel]('occupation', StringModel, [false]);
  }

  get important(): BooleanModel {
    return this[_getPropertyModel]('important', BooleanModel, [false]);
  }
}

The SamplePerson interface is used by our code and SamplePersonModel will be used for form binding.

The View with the Grid

To create the view we use Lit. Also the View will be a Webcomponent. You’ll find the full source code here.

@customElement('master-detail-view')
export class MasterDetailView extends View {

The customElement decorator defines the name of the Webcomponent and we extend from a Hilla class View that finally extends LitElement.

The most important method is render where we create the content of the view.

 render() {
        return html`
            <vaadin-vertical-layout theme="padding">
                <vaadin-text-field label="Search" @value-changed=${this.search}></vaadin-text-field>
            </vaadin-vertical-layout>

            <vaadin-split-layout>
                <div class="grid-wrapper" style="width: 70%">
                    <vaadin-grid
                            id="grid"
                            theme="no-border"
                            .size=${this.gridSize}
                            .dataProvider=${this.gridDataProvider}
                            @active-item-changed=${this.itemSelected}
                            .selectedItems=${[personStore.selectedPerson]}
                    >
                        <vaadin-grid-sort-column path="firstName" auto-width></vaadin-grid-sort-column>
                        <vaadin-grid-sort-column path="lastName" auto-width></vaadin-grid-sort-column>
                        <vaadin-grid-sort-column path="email" auto-width></vaadin-grid-sort-column>
                        <vaadin-grid-sort-column path="phone" auto-width></vaadin-grid-sort-column>
                        <vaadin-grid-sort-column path="dateOfBirth" auto-width></vaadin-grid-sort-column>
                        <vaadin-grid-sort-column path="occupation" auto-width></vaadin-grid-sort-column>
                        <vaadin-grid-column
                                path="important"
                                auto-width
                                ${columnBodyRenderer<SamplePerson>((item) =>
                                        item.important
                                                ? html`
                                                    <vaadin-icon
                                                            icon="vaadin:check"
                                                            style="width: var(--lumo-icon-size-s); height: var(--lumo-icon-size-s); color: var(--lumo-primary-text-color);"
                                                    >
                                                    </vaadin-icon>`
                                                : html`
                                                    <vaadin-icon
                                                            icon="vaadin:minus"
                                                            style="width: var(--lumo-icon-size-s); height: var(--lumo-icon-size-s); color: var(--lumo-disabled-text-color);"
                                                    >
                                                    </vaadin-icon>`
                                )}
                        ></vaadin-grid-column>
                    </vaadin-grid>
                </div>
                <person-form
                        style="width: 30%"
                        @contact-form-saved=${this.contactFormSave}
                ></person-form>
            </vaadin-split-layout>
        `;
    }

We use Vaadin components and for people familiar with the Vaadin framework it will be easy to get started.

The View uses a Grid to display the persons and uses lazy loading with a DataProvider. Therefore we define a property size that will get the number of persons from the Endpoint:

this.gridSize = (await SamplePersonEndpoint.count(this.filter)) ?? 0;

And to load the data we have to use the GridDataProvider functionality that also uses the appropriate Endpoint method that provides paging:

    private async getGridData(
        params: GridDataProviderParams<SamplePerson>,
        callback: GridDataProviderCallback<SamplePerson | undefined>
    ) {
        const sort: Sort = {
            orders: params.sortOrders.map((order) => ({
                property: order.path,
                direction: order.direction == 'asc' ? Direction.ASC : Direction.DESC,
                ignoreCase: false,
            })),
        };
        const data = await SamplePersonEndpoint.list(this.filter, 
                                                {pageNumber: params.page, pageSize: params.pageSize, sort});
        callback(data);
    }

The Form

To edit the person data I’ve created a separate component. This makes the code more readable.

To bind the SamplePerson object to the form we use a binder. A binder controls all aspects of a single form. It is typically used to get and set the form value, access the form model, validate, reset, and submit the form.
The binder is typed by the generated interface and class based on the Java SamplePerson class.

@customElement('person-form')
export class PersonForm extends View {

    private binder = new Binder<SamplePerson, SamplePersonModel>(this, SamplePersonModel);

    constructor() {
        super();
        this.autorun(() => {
            if (personStore.selectedPerson) {
                this.binder.read(personStore.selectedPerson);
            } else {
                this.binder.clear();
            }
        });
    }

But how do we get the person that is selected in the Grid to the form?
This can be solved by a store that holds the currently selected person.

Hilla recommends MobX to manage frontend state. Read more about that in the documentation: https://hilla.dev/docs/application/state-management#using-a-store

Also in the form we use Vaadin components that allows us to bind the fields: ${field(this.binder.model.firstName)}

 render() {
        return html`
            <div class="editor-layout">
                <div class="editor">
                    <vaadin-form-layout>
                        <vaadin-text-field
                                label="First name"
                                id="firstName"
                                ${field(this.binder.model.firstName)}
                        ></vaadin-text-field>
...

Submitting the Form

The last thing we want to have a look at is how to save the person in the form.

We can use the binders submitTo method and pass the update method of the Endpoint.

 private async save() {
        try {
            await this.binder.submitTo(SamplePersonEndpoint.update);
            this.binder.clear();

            personStore.selectedPerson = null;

            this.dispatchEvent(new CustomEvent('contact-form-saved'));

            Notification.show(`SamplePerson details stored.`, {position: 'bottom-start'});
        } catch (error: any) {
            if (error instanceof EndpointError) {
                Notification.show(`Server error. ${error.message}`, {theme: 'error', position: 'bottom-start'});
            } else {
                throw error;
            }
        }
    }

After saving we clear the store and dispatch an event contact-form-saved. This event will be used in the grid to refresh the grid with the changed data.

<person-form @contact-form-saved=${this.contactFormSave}></person-form>

Conclusion

Of course, this was only a superficial introduction to Hilla. But I hope this article gave you an overview of how to easily develop a data-centric full-stack application with a lazy loading grid.

If you’re interested in how Hilla compares to Vaadin check out my video:

How to use jOOQ with Testcontainers and, Flyway

When using a database the question is how to manage the versions of the schema and how to use the database in testing. Container technologies became a defacto standard. Why not use containers for development and your database?

Testcontainers is a Java library that supports JUnit tests, providing lightweight, throwaway instances of common databases, Selenium web browsers, or anything else that can run in a Docker container.
(Source: www.testcontainers.org)

Sounds perfect, right? And what about database versioning?

Flyway. Version control for your database. Robust schema evolution across all environments. With ease, pleasure, and plain SQL. (Source: flywaydb.org)

Awesome! Now let’s have a look at how we can use these two technologies in combination with jOOQ to generate the Java code and test the application. The example project uses MariaDB but would work with any database. You’ll find the source code on GitHub: github.com/simasch/jooq-mariadb

The Build

The example uses Maven as the build tool. And the process is as follows

  1. Start MariaDB
    A Testcontainer with MariaDB is started using the Groovy plugin
  2. Execute Flyway migrations
    Flyway migrations are applied to the MariaDB
  3. Generate jOOQ Code
    jOOQ generates code from the MariaDB objects
  4. Compile
    Code and generated classes are compiled
  5. Run Tests
    For each test, Testcontainers starts a MariaDB container

Bellow, you can see how the Maven plugins are configured.

<!-- Start Testcontainer -->
<plugin>
    <groupId>org.codehaus.gmaven</groupId>
    <artifactId>groovy-maven-plugin</artifactId>
    <version>2.1.1</version>
    <executions>
        <execution>
            <phase>generate-sources</phase>
            <goals>
                <goal>execute</goal>
            </goals>
            <configuration>
                <source>
                    db = new org.testcontainers.containers.MariaDBContainer("mariadb:10.7.3")
                            .withUsername("${db.username}")
                            .withDatabaseName("${db.database}")
                            .withPassword("${db.password}")
                    db.start()
                    project.properties.setProperty('db.url', db.getJdbcUrl())
                </source>
            </configuration>
        </execution>
    </executions>
    <dependencies>
        <dependency>
            <groupId>org.testcontainers</groupId>
            <artifactId>mariadb</artifactId>
            <version>${testcontainers.version}</version>
        </dependency>
        <dependency>
            <groupId>org.mariadb.jdbc</groupId>
            <artifactId>mariadb-java-client</artifactId>
            <version>${mariadb.version}</version>
        </dependency>
    </dependencies>
</plugin>
<!-- Migrate schema -->
<plugin>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-maven-plugin</artifactId>
    <version>7.14.0</version>
    <executions>
        <execution>
            <phase>generate-sources</phase>
            <goals>
                <goal>migrate</goal>
            </goals>
            <configuration>
                <url>${db.url}</url>
                <user>${db.username}</user>
                <password>${db.password}</password>
                <locations>
                    <location>filesystem:src/main/resources/db/migration</location>
                </locations>
            </configuration>
        </execution>
    </executions>
</plugin>
<!-- Generate jOOQ code -->
<plugin>
    <groupId>org.jooq</groupId>
    <artifactId>jooq-codegen-maven</artifactId>
    <executions>
        <execution>
            <goals>
                <goal>generate</goal>
            </goals>
        </execution>
    </executions>
    <configuration>
        <jdbc>
            <driver>${db.driver}</driver>
            <url>${db.url}</url>
            <user>${db.username}</user>
            <password>${db.password}</password>
        </jdbc>
        <generator>
            <database>
                <inputSchema>sakila</inputSchema>
                <forcedTypes>
                    <forcedType>
                        <name>BOOLEAN</name>
                        <includeTypes>(?i:TINYINT\(1\))</includeTypes>
                    </forcedType>
                </forcedTypes>
            </database>
            <target>
                <packageName>ch.martinelli.sakila</packageName>
            </target>
            <generate>
                <pojos>true</pojos>
                <pojosToString>false</pojosToString>
                <daos>true</daos>
            </generate>
        </generator>
    </configuration>
</plugin>

That’s it. Easy, isn’t it?

If you are new to jOOQ don’t miss my introduction video:

Visualization of the Message Flow between Business Functions with Vaadin and Neo4j

The Project

Analyzing and visualizing the message flow between business functions was the goal of my current project. At first, we considered using a UML tool for this job, but we came to the conclusion that it might not be as flexible as we need it to be. Finally, I’ve got the assignment to create a custom web application.

Since business functions and messages are related to each other, it made sense to represent them as a graph. That’s why I chose Neo4j as the database. Now the question was how to manage and visualize the graph. As I’m experienced with the Vaadin framework I want to use it also in this project. 

Vaadin has a lot of great UI components but in my case, there was no match. Finally, I’ve found vis.js. The network diagram seemed appropriate for the visualization. Luckily Vaadin provides the Vaadin Directory, a place to publish 3rd party components. From the Vaadin directory, a component called vis-network-vaadin is available that provides a Java API on top of vis.js

The Graph

The graph below is a simplified model of what my client wants to manged in the application. A business function can send many messages and a message can be received by many business functions.

The Implementation

First I created a Vaadin project on start.vaadin.com and added the vis-network-vaadin dependency for the visualization. As Vaadin uses Spring Boot by default I could just add spring-boot-starter-data-neo4j for the data access.

Data Access

Spring Data Neo4j provides easy access to Neo4j. As I already know Spring Data JPA and the programming model are very similar it was easy to get started. First I’ve mapped the nodes and defined the relationships using the Neo4j annotations.

@Node
public class BusinessFunction {

    @Id
    @GeneratedValue
    private Long id;

    private String nameDE;
    private String actorsDE;
    private String descriptionDE;
}
@Node
public class Message {

    @Id
    @GeneratedValue
    private Long id;

    private String nameDE;
    private String descriptionDE;

    @Relationship(type = "SENDS", direction = Relationship.Direction.INCOMING)
    private Set<BusinessFunction> senders = new HashSet<>();

    @Relationship(type = "RECEIVES")
    private Set<BusinessFunction> receivers = new HashSet<>();
}

To read and write the data you can use repositories and make use of interface methods that will be used to generate the queries for you. Remark: I didn’t care about the performance so the generated queries were good enough in the first phase.

public interface BusinessFunctionRepository extends Neo4jRepository<BusinessFunction, Long> {

    Optional<BusinessFunction> findByNameDE(String name);

    List<BusinessFunction> findAllByNameDELike(String name, Pageable pageable);
}
public interface MessageRepository extends Neo4jRepository<Message, Long> {

    Optional<Message> findByNameDE(String name);

    List<Message> findAllByNameDELike(String name, Pageable pageable);
}

Diagram

Finally, I had to visualize the graph with a network diagram. Using the vis-network-vaadin API made it quite simple. I just had to map BusinessFunction and Message to nodes and create edges from the relationships.

var networkDiagram = new NetworkDiagram(Options.builder().build());
networkDiagram.setSizeFull();

var businessFunctionNodes = businessFunctionRepository.findAll().stream()
        .map(businessFunction -&gt; createNode("b-", businessFunction.getId(), businessFunction.getNameDE(), "DodgerBlue"))
        .toList();
var nodes = new ArrayList<>(businessFunctionNodes);

var messages = messageRepository.findAll();
var messageNodes = messages.stream()
        .map(message -&gt; createNode("m-", message.getId(), message.getNameDE(), "Orange"))
        .toList();

nodes.addAll(messageNodes);

var dataProvider = new ListDataProvider<>(nodes);
networkDiagram.setNodesDataProvider(dataProvider);

var edges = new ArrayList<Edge>();
for (Message message : messages) {
    for (BusinessFunction sender : message.getSenders()) {
        edges.add(createEdge("b-", sender.getId(), "m-" + message.getId().toString(), getTranslation("sends")));
    }
    for (BusinessFunction receiver : message.getReceivers()) {
        edges.add(createEdge("m-", message.getId(), "b-" + receiver.getId(), getTranslation("receives")));
    }
}

networkDiagram.setEdges(edges);

These are the helper methods to create nodes and edges:

private Edge createEdge(String prefix, Long id, String name, String label) {
    var edge = new Edge(prefix + id.toString(), name);
    edge.setColor("black");
    edge.setArrows(new Arrows(new ArrowHead(1, Arrows.Type.arrow)));
    edge.setLength(300);
    return edge;
}

private Node createNode(String prefix, Long id, String name, String color) {
    var node = new Node(prefix + id, name);
    node.setShape(Shape.circle);
    node.setColor(color);
    node.setFont(Font.builder().withColor("white").build());
    node.setWidthConstraint(new WidthConstraint(100, 100));
    return node;
}

Finally, the graph is displayed in the application.

Conclusion

The application is still in an early stage. The graph will be extended and the diagram must be improved. Especially the behavior when dragging around the edges seems to be quite tricky and vis.js provides a lot of configuration.

As a Java developer creating UIs with Vaadin makes it very efficient. There are even 3rd party libraries that wrap components in a Java API. On the other side, I was impressed by how easy it is to start with Neo4j and to integrate it into a Spring Boot application.

Btw. If you want to learn more about Spring Boot check my video below.

Vaadin and jOOQ: Match Made in Heaven

Have you ever had to write an application that didn’t do much other than display and change data? And did you use a Single Page Application framework like Angular, a REST API, and Hibernate?

How about if that was a lot easier?

Introducing Vaadin and jOOQ

Vaadin exists already for 20 years but was completely overhauled three years ago. Vaadin Flow is a web framework that allows the development of a web application in Java. It’s based on web components and the state is synchronized between browser and server.

What makes Vaadin a perfect match for data-centric applications are the components. We will see how simple it is to display data in a grid.

The second framework we will use is jOOQ for database access. jOOQ consists of two parts: a code generator and a DSL.

With the code generator, you can generate Java code for all database objects including tables, views, procedures, user-defined types, and many more. The DSL allows you to write type-safe, compile-time-checked SQL in Java using the generated Java classes.

Displaying a List of Data

Data Providers

To fetch data from a backend Vaadin provides data providers. Data providers are used when a component displays a collection of data. For example, in Grids or ComboBoxes.

There are two types of data providers: InMemoryDataProvider and CallbackDataProvider. An InMemoryDataProvider is used if you only have a handful of records to display whereas the CallBackDataProvider provides lazy loading and paging.

Creating the Grid

We want to create a grid to display the revenue of customers for that we use a Java Record:

public record CustomerInfo(Integer id, String lastname, String firstname, double revenue) {
}

This Java Record can then be used to define the type of the grid and add the columns. The addColumn is an overloaded method and we use to pass a method reference that returns the value that must be displayed in the grid cell.

Grid<CustomerInfo> grid = new Grid<>();
grid.addColumn(CustomerInfo::id).setHeader("ID");
grid.addColumn(CustomerInfo::firstname).setHeader("First Name");
grid.addColumn(CustomerInfo::lastname).setHeader("Last Name");
grid.addColumn(CustomerInfo::revenue).setHeader("Revenue");

Fetching the Data

As we have many customers we want to use paging. The Grid has an overloaded method setItems where we only have to pass a callback that is used to fetch the data. Internally a CallbackDataProvider is created.

grid.setItems(query -> dsl
	.select(CUSTOMER.ID, CUSTOMER.LASTNAME, CUSTOMER.FIRSTNAME, DSL.sum(PRODUCT.PRICE))
	.from(CUSTOMER)
	.join(PURCHASE_ORDER).on(PURCHASE_ORDER.CUSTOMER_ID.eq(CUSTOMER.ID))
	.join(ORDER_ITEM).on((ORDER_ITEM.ORDER_ID.eq(PURCHASE_ORDER.ID)))
	.join(PRODUCT).on((PRODUCT.ID.eq(ORDER_ITEM.PRODUCT_ID)))
	.groupBy(CUSTOMER.ID)
	.orderBy(CUSTOMER.LASTNAME, CUSTOMER.FIRSTNAME)
	.offset(query.getOffset())
	.limit(query.getLimit())
	.fetchStreamInto(CustomerInfo.class)

To fetch the data we use the jOOQ DSL with the generated Java code. There are Java classes for the tables (CUSTOMER, PURCHASE_ORDER, ORDER_ITEM, and PRODUCT) that contain the columns (CUSTOMER.ID, CUSTOMER.LASTNAME, etc). Finally, we fetch the result into the CustomerInfo record.

We use offset and limit from the query object that is passed to the fetch callback method from Vaadin so the grid will have infinite scrolling and will call the callback method to fetch data when needed.

As you see in the example the code looks like SQL but due to the generated code, it is fully type-safe and is checked at compile time. If you have breaking database changes your code may no longer compile after regenerating the Java code.

And finally, this is what the grid looks like. There are many more features in grid-like filtering and sorting. You can find more information about these features and also about all other Vaadin components in the components directory.

Conclusion

This introduction covered only a small part of the feature set of both frameworks but I hope the example gives you an impression of how easy it is to create data-centric web applications with the combination of Vaadin and jOOQ.

If you’d like to know more check out the jOOQ documentation and tutorials and watch my Vaadin quick start tutorial to learn more about Vaadin:

Java 16 Records with JPA and jOOQ

The new Java version 16 includes a new feature: Records

https://openjdk.java.net/jeps/395 “Enhance the Java programming language with records, which are classes that act as transparent carriers for immutable data. Records can be thought of as nominal tuples.”

Let’s try Java records with JPA and jOOQ.

JPA Constructor Expression

One way to use projection in JPA queries is using the constructor expression. The name constructor expression implies that the constructor is called with the fields from the projection.

select new com.demo.dto.EmployeeDTO(e.name, e.department.name) from Employee e

In the example we have a DTO called EmployeeDTO and the constructor takes two Strings as parameters.

With Java before Java 16 we would create a class like this:

public final class EmployeeDTO {

    private final String employeeName;
    private final String departmentName;

    public EmployeeDTO(String employeeName, String departmentName) {
        this.employeeName = employeeName;
        this.departmentName = departmentName;
    }

    public String employeeName() {
        return employeeName;
    }

    public String departmentName() {
        return departmentName;
    }

    @Override
    public boolean equals(Object obj) {
        if (obj == this) return true;
        if (obj == null || obj.getClass() != this.getClass()) return false;
        var that = (EmployeeDTO) obj;
        return Objects.equals(this.employeeName, that.employeeName) &&
                Objects.equals(this.departmentName, that.departmentName);
    }

    @Override
    public int hashCode() {
        return Objects.hash(employeeName, departmentName);
    }

    @Override
    public String toString() {
        return "EmployeeDTO[" +
                "employeeName=" + employeeName + ", " +
                "departmentName=" + departmentName + ']';
    }
}

Thanks to Java 16 Records this is now much simpler:

public record EmployeeDTO(String employeeName, String departmentName) {
}

This Record will contain the required constructor and also the methods to get the employeeName and the departmentName so it’s a perfect fit for JPAs constructor expression!

jOOQ SQL Projection

Besides JPA there is another great solution for accessing relational database systems: jOOQ

With jOOQ, we can write type-safe SQL in Java. And very often we also want DTOs as a result. Also here Java Records shine:

List<EmployeeDTO> employees = dsl
   .select(EMPLOYEE.NAME, DEPARTMENT.NAME)
   .from(EMPLOYEE).join(DEPARTMENT).on(EMPLOYEE.DEPARTMENT_ID.eq(DEPARTMENT.ID))
   .fetchInto(EmployeeDTO.class);

Conclusion

Java Records are a great addition to the Java language and a great fit to use with persistence technologies like JPA or jOOQ.

If you want to try it on your own, please find the example code on GitHub: https://github.com/72services/java16-jpa-jooq

Use the Power of your Database: XML and JSON

Today databases have a lot of functionality that is often not used by software developers because they simply do not know that this exists. But knowing about these features can save a lot of time because you may write less code.

One of these hidden gems is the ability to produce XML or JSON data using a SQL SELECT statement.
In the examples, Oracle Database is used but these features are also available in other Databases like PostgreSQL or SQL Server.

Model

Let’s assume we have the following model and we want to have employees with their phones.

XML

In Oracle Database producing XML would look like this.

select xmlelement(
        name "employees",
        xmlagg(xmlelement(name "employee",
            xmlattributes(EMPLOYEE.ID, EMPLOYEE.NAME),
            xmlelement(name "phones", (select xmlagg(xmlelement(name "phone",
                                              xmlattributes(PHONE.PHONENUMBER, PHONE.TYPE)))
                                       from PHONE
                                       where PHONE.EMPLOYEE_ID = EMPLOYEE.ID)))))
from "EMPLOYEE"

The result of the query will be this XML:

<employees>
    <employee ID="1" NAME="Ursula Friedman">
        <phones>
            <phone PHONENUMBER="031 333 11 12" TYPE="WORK"/>
        </phones>
    </employee>
    <employee ID="2" NAME="Hans Boss">
        <phones>
            <phone PHONENUMBER="031 333 11 01" TYPE="HOME"/>
            <phone PHONENUMBER="032 311 43 12" TYPE="WORK"/>
        </phones>
    </employee>
</employees>

JSON

For sure this also works for JSON in Oracle Database.

SELECT
	json_arrayagg("employee".employee)
FROM (SELECT
		json_object(
			KEY 'id' value EMPLOYEE.ID,
			KEY 'name' value EMPLOYEE.NAME,
			KEY 'phones' value json_arrayagg(json_object(KEY 'number' value PHONE.PHONENUMBER, KEY 'type' value PHONE.TYPE))
		) employee
	FROM EMPLOYEE JOIN PHONE ON PHONE.EMPLOYEE_ID = EMPLOYEE.ID
	GROUP BY EMPLOYEE.ID, EMPLOYEE.NAME) "employee"

The query produces this result:

[
  {
    "id": 1,
    "name": "Ursula Friedman",
    "phones": [
      {
        "number": "031 333 11 12",
        "type": "WORK"
      }
    ]
  },
  {
    "id": 2,
    "name": "Hans Boss",
    "phones": [
      {
        "number": "031 333 11 01",
        "type": "HOME"
      },
      {
        "number": "032 311 43 12",
        "type": "WORK"
      }
    ]
  }
]

Conclusion

Knowing the features of your database can save you time.
Start reading the manual of your database today. Have fun!

Vaadin Tip: Lazy Loading and Item Identity

When using grids, trees, or any other of the multi-valued component with Vaadin you often want to display data from a database table, and typically you have more than a few rows in the database.
In this case loading, thousands or even millions of records don’t make sense and would be a huge performance problem. For this use case, Vaadin provides lazy loading using a CallbackDataProvider.

To create a CallBackDataProvider you must implement a CountCallback and a FetchCallback.
The CountCallback is used to provide the total number of records. And the FetchCallback is used for paging. Both methods receive an Query object that contains filter, sorting, offset and limit.

In this example, you can see how to use offset and limit.

DataProvider<Employee, Void> dataProvider = new CallbackDataProvider<>(
                query -> employeeRepository.findAll(query.getOffset(), query.getLimit()),
                query -> employeeRepository.count()
        );

Item Identity

In a Grid or the DataProvider there are methods that are using an item:

grid.select(employee);
dataProvider.refreshItem(employee);

Ever wondered how Vaadin is finding the right item in the underlying data structure? No surprise – it uses equals().
But what if you can’t control how equals() is implemented? For example, is the Class that you use in the Grid is generated directly from the database tables like jOOQ does?

No worries! Vaadin provides another constructor to create a CallbackDataProvivder

As a third parameter, you pass a ValueProvider that is responsible to return a unique identifier. In the example, this is the ID of the Employee.

DataProvider<Employee, Void> dataProvider = new CallbackDataProvider<>(
                query -> employeeRepository.findAll(query.getOffset(), query.getLimit()),
                query -> employeeRepository.count(),
                Employee::getId
        );

What’s next?

Ever heard of Vaadin? Stay tuned there will be a Vaadin introduction coming soon!

Type Safe SQL in Java

No matter if you are using frameworks like JPA, MyBatis or Spring Data JDBC you always end up declaring the SQL statements as a Java String.

Strings, String, Strings

No matter if you are using frameworks like JPAMyBatis or Spring Data JDBC you always end up declaring the SQL statements as a Java String.
The problem with this approach is that you have to write tests for every statement to make sure that it is even valid SQL. There is no compile time guarantee that the SQL statement will execute.

Get rid of the Strings!

Embedded SQL

I started professional software development in 1995 on IBM mainframe computers programming in COBOL. To access the database we used something called “Embedded SQL”:

EXEC SQL
SELECT lastname, firstname
INTO :lastname, :firstname
FROM employee
WHERE id = :id

The cool thing about Embedded SQL was that a pre-compiler was checking every SQL statement and only if it was valid the code compiled.
Bellow you can see the compile steps. (Source: http://www.redbooks.ibm.com/redbooks/pdfs/sg246435.pdf)

SQLJ

When I first met Java and JDBC in 2000 I was confused that nothing similar existed. I found out that there was an initiative called SQLJ started in 1997 but never took off. I don’t have an idea why, maybe because this was hard to integrate for IDE vendors and pre-compilers where not very common for Java. At least the compile steps are similar to Embedded SQL:

When comparing JDBC and SQLJ we can see that there is not much difference from the amount of code you have to write but everything after #sql is type safe because the pre-compiler checks the syntax where as with JDBC there is a String that could contain any error and the error will happen late in production.

And then I found jOOQ!

Ten years ago Lukas Eder release the first version of jOOQ. According to the website is jOOQ “The easiest way to write SQL in Java”

Let’s try to write the same query as above with jOOQ:

List<EmployeeDTO> records = create
         .select(EMPLOYEE.LASTNAME, EMPLOYEE.FIRSTNAME, EMPLOYEE.SALARY)
         .from(EMPLOYEE)
         .where(EMPLOYEE.SALARY.between(80000, 100000))
         .fetchInto(EmployeeDTO.class);

Pretty cool, isn’t it? Yes – but how does it work?

1. Code Generator

jOOQ uses a code generator to generate Java classes from database objects.

For example this is an extract of the class generated by jOOQ for the table EMPLOYEE:

public class Employee extends TableImpl<EmployeeRecord> {

    public static final Employee EMPLOYEE = new Employee();

    public final TableField<EmployeeRecord, Integer> ID = createField("ID", org.jooq.impl.SQLDataType.INTEGER.nullable(false).identity(true), this, "");
    public final TableField<EmployeeRecord, String> LASTNAME = createField("LASTNAME", org.jooq.impl.SQLDataType.VARCHAR(50).nullable(false), this, "");
    public final TableField<EmployeeRecord, String> FIRSTNAME = createField("FIRSTNAME", org.jooq.impl.SQLDataType.VARCHAR(50).nullable(false), this, "");
    public final TableField<EmployeeRecord, Integer> SALARY = createField("SALARY", org.jooq.impl.SQLDataType.INTEGER, this, "");
    public final TableField<EmployeeRecord, Integer> DEPARTMENT_ID = createField("DEPARTMENT_ID", org.jooq.impl.SQLDataType.INTEGER.nullable(false), this, "");
    public final TableField<EmployeeRecord, Integer> MANAGER_ID = createField("MANAGER_ID", org.jooq.impl.SQLDataType.INTEGER, this, "");
}

There are constants for the table and all the columns. Thanks to these meta data classes it’s not possible to use a type in a SQL statement that does not exists in the database. And because you can generate the meta data every time, the database model changes your code will not compile if there are breaking changes.

How to configure the generator and what input formats for the generator are possible will be described in a future post. (Stay tuned)

2. Domain Specific Language

The second part of jOOQ is the DSL (Domain Specific Language) that allows to write SQL code in Java.
And in contrast to SQL in Strings the DSL forces me to write valid SQL!

Examples

So let’s see some more examples. The examples are based on this data model:

Insert
dsl.insertInto(DEPARTMENT)
   .columns(DEPARTMENT.NAME)
   .values("HR")
   .execute();
Select
dsl.select(DEPARTMENT.NAME)
    .from(DEPARTMENT)
    .where(DEPARTMENT.NAME.eq("IT"))
    .fetchOne();
Update
dsl.update(DEPARTMENT)
   .set(DEPARTMENT.NAME, "IT2")
   .where(DEPARTMENT.ID.eq(departmentId))
   .execute();
Delete
dsl.deleteFrom(EMPLOYEE)
   .where(EMPLOYEE.ID.eq(employeeId))
   .execute();

What’s next?

That was just a short introduction. In the next blog post we will have a deeper look at all the features jOOQ provides.

In the meanwhile you can checkout the code here: https://github.com/simasch/jooq-hr

Java Persistence Done Right

When it comes to accessing relational databases with Java, people usually think of two options:

1. SQL (Structured Query Language)
2. ORM (Object Relational Mapping)

Because the usage of SQL with the Java API JDBC (Java Database Connectivity) is painful and error-prone the first choice is usually an ORM like JPA/Hibernate.

ORM

Let’s have a look at the definition of ORM on Wikipedia:

Object-relational mapping (ORM, O/RM, and O/R mapping tool) in computer science is a programming technique for converting data between incompatible type systems using object-oriented programming languages. This creates, in effect, a “virtual object database” that can be used from within the programming language.

The idea behind an ORM framework is to hide the database access from the user. Another goal is to introduce the capability to the database access layer that does not exist in a relational database like inheritance. But this abstraction is leaky and leads to the so-called impedance mismatch:

The Impedance Mismatch

The object-relational impedance mismatch is a set of conceptual and technical difficulties that are often encountered when a relational database management system (RDBMS) is being served by an application program (or multiple application programs) written in an object-oriented programming language or style, particularly because objects or class definitions must be mapped to database tables defined by a relational schema. Source: Wikipedia

The problem with ORM is that the user by default does not have full control over the database access and this can cause several problems. The most common problem is poor performance caused by the fact that developers usually don’t deep dive into the details of a framework. This naive approach usually leads to too many SQL statements executed by the ORM framework.

The fact that it’s possible to define parent-child relationships in ORM raises the question when and how the children are loaded. By default, this is done in a lazy way. So let’s assume that we have a customer order with many items and we want to fetch the customer orders the generated SQL statements will look like this:

select * from customer_order;

The above query returns all customer orders (e.g. 1,2,3,4). If the program accesses the children the ORM framework will produce a query per customer order:

select * from item where customer_order_id = 1;
select * from item where customer_order_id = 2;
select * from item where customer_order_id = 3;
select * from item where customer_order_id = 4;

This problem is called n+1 select problem and happens in every application that uses ORM. The ORM usually provides techniques to overcome this problem but as initially said developers usually are not ORM experts.

What are the alternatives?

As you can see ORM may not be the silver bullet you’re looking for. But as initially mentioned using SQL can be very painful. Luckily there are two popular alternatives.

1. MyBatis (former iBatis)
2. jOOQ

MyBatis

MyBatis was first released in 2001 under the name iBatis and the idea behind this framework is to map SQL statements to Java objects. In contrast to ORM where the SQL statements are generated by the framework, you have full control over the SQL statements because you have to write them on your own.

The code examples show how you write the SQL statement in an annotation (it also supports XML) and that the method returns a Java object and not a JDBC ResultSet:

public interface DepartmentMapper {

    @Select("select id, name from department WHERE name = #{name}")
    Department findByName(String name);
}

The downside of MyBatis is that there is a lot of mapping work to do. To overcome this disadvantage there is a generator that can help with this task.
But the biggest disadvantage of MyBatis is the lack of type safety. SQL statements are written in Strings and also the mapping is just strings this may cause problems during runtime because the mapping and the SQL are not checked during compile time.

jOOQ

jOOQ is a framework that embraces SQL and makes SQL the primary language to speak to the database from Java in a typesafe and fluent way. jOOQ provides a domain-specific language (DSL). All the artifacts you use with this DSL are generated from the database meta-model.

The difference to MyBatis is that you don’t write SQL in plain text and therefore the compiler can check your SQL statements and you have full code completion in your IDE.

DepartmentDTO department = dsl
        .select(DEPARTMENT.ID, DEPARTMENT.NAME)
        .from(DEPARTMENT)
        .where(DEPARTMENT.NAME.eq("IT"))
        .fetchOneInto(DepartmentDTO.class);

As you can see in the above example you really write SQL! The types in capital letters are constants that are generated from the database meta-model and provide the type-safety with the DSL.

Should I still use ORM?

As usual, the answer is “it depends”. But because of the impedance mismatch and the fact you have to be an expert in ORM and SQL you really should think twice if it’s worth investing in this technology.
With jOOQ you will get a great alternative plus full control over the database access!

What’s Next?

In the next blog post, I will introduce jOOQ as the best way to use SQL in Java applications.
Stay tuned!

Introduction to Database Migrations with Spring Boot and Flyway

If you are working with Spring Boot there are several ways to initialize the database.

You could create a schema.sql, add it to the classpath, and set spring.datasource.initialization-mode=true. Spring will execute the SQL script on startup.

Or you could use the Hibernate built-in functionality and set spring.jpa.hibernate.ddl-auto to create or update.

But both approaches are very limited and if you have to deal with an evolving database you may look for something more sophisticated.
That’s where database migration tools fill the gap.

Database migration tools

In the Java environment, there are two open-source projects available: Flyway and Liquibase. The difference between these two is that Flyway uses SQL for the migrations and Liquibase supports additional abstractions in XML, YAML, or JSON.

Both tools are very well integrated with Spring Boot. You simply have to add the right dependency in your pom.xml or Gradle build file.
Read more about why you should care about database migrations here.

Flyway with Spring Boot

In my example, I use Flyway with Spring Boot. To use it simply add this dependency and Spring Boot will auto-configure Flyway right away.

<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-core</artifactId>
</dependency>

Now you’re ready to start adding your migration SQL files to your classpath in the path classpath:db/migration

Naming Conventions

To have Flyway execute your SQL scripts you have to follow some naming conventions.

  • Prefix (V, U or R)V = Versioned migrations that are executed only once
  • U = Undo migrations (only supported in the commercial version)
  • R = Repeatable migrations. Scripts that could run with every execution
  • Version
  • Separator __
  • Description
  • Suffix: (.sql)

Example: V1__Init.sql

Example Migration

V1__Init.sql could look like

CREATE TABLE address (
    id integer NOT NULL,
    city character varying(255),
    state character varying(255),
    street character varying(255),
    zip character varying(255),
    created_date timestamp,
    last_modified_date timestamp
);

How it Works

Flyway uses a table to store the version of the database. The default name of this table is flyway_schema_history and the content looks like this:

As you can see Flyway stores the actual version and is, therefore, able to know which migrations scripts have to be executed.

Further Reading

That was just a brief introduction. Flyway has way more to offer and you will find all you need to know in the official documentation on flywaydb.org