How to Create Single Page Applications with PostgreSQL Data Type Jsonb, Play Framework and REST

• Download demo example - 1,04 MB

One of the most common problems these days is saving tree data structures of different nesting levels. This problem is particularly relevant when there is a need in data storage in modern web applications and when the main data format is Json.

Usually one of the problems for developers starting a new project is deciding what technologies to choose. As a rule, they rely on previous experience and choose approaches they are used to. But sometimes, you need to break stereotypes in order to start using new trendy technologies that can replace the existing ones. Today, we are going to focus on Single Page Web Application (SPA).

What is so special about SPA? First of all, the architecture, of course. Let’s compare two widely spread approaches: web application with template creation on server side and SPA.

As for me, there are several very annoying moments when working with JSF. For example, custom elements on the view always connect with backing bean on the server. Besides, defining custom logic for a JSF component is really a big deal.

On the other hand, with SPA you have to use some JavaScript template engine framework and REST service to request or store data instead of a server template engine.

At the moment, several JavaScript frameworks are available, including Angular, Ember, React, etc. The weakest (i.e. the slowest) part in HTTP protocol is that data is sent via network. If engine template is used on the server side, the data is always sent in html and sometimes the weight of html will exceed payloads. Besides, these frameworks provide services for data binding, and this significantly reduces time losses on development.

Additionally, a greater performance on the frontend is expected. REST API is essential for SPA. So we can use JAX-RS for example. Use JavaScript to call REST API method, send any Json and store it in a database on the server side. The main chain element in this communication is Json. In SPA, you need to create Json object at the client side and send it in the request to the server via REST API.

The main stumbling block is that Java and JavaScript have different models of data presentation. Java has classes while JavaScript has Json objects. Each time when dealing with REST API calls, serialization and deserialization is needed. But what if you try to store Json directly in a relational database or even in a data table column? How will the process of validation and selection be performed? How should the data be stored? Application performance depends on answers to these questions.

Let’s start building SPA with PostgreSQL data type Jsonb (since 9.4 version), Play Framework (version 2) and REST https://github.com/AlexSoftarex/javaOne_spa. This example application contains two different approaches to performing the same thing, i.e., storing data in Json structure.

First approach is based on relational database and refers to Client model. Start with node with ID creation, other nodes will refer to this ID. As a result, there are parent and children nodes.

The Code

public class Note extends UUIDEntity{

	@Column
	private String text;
	
	@OneToMany(mappedBy="parentNote", fetch = FetchType.LAZY, cascade = CascadeType.ALL)
	private Set<Note> children = new HashSet<>();
	
	@ManyToOne(cascade = CascadeType.ALL)
	@JoinColumn
	private Note parentNote;	

––	@ManyToOne(cascade = CascadeType.ALL)
	@JoinColumn
	private Client client;
	
	@Transient
	private Long parentId;

The second approach contains ClientwithJsonb model and assumes Jsonb storage directly into a data table column.

public class ClientWithJsonb extends UUIDEntity implements Validatable {
	
	@Type(type = "Jsonb")
	private JsonNode model;


	public JsonNode getModel() {
		return model;
	}


	public void setModel(JsonNode model) {
		this.model = model;
	}	
}

To create SPA Play Framework was chosen for several reasons. Among them are:

1. Hot Code Reloading
2. Netty (NIO)
3. Akka System
4. Easy to work with in comparison to similar tools

For example, look through the test example. Using Play Framework, check the configuration file. There are main application settings: database connection, log configuration, etc. Play API gives an easy opportunity to use the configuration file.

Pay attention to Global class in app folder. In this class, all basic configurations can be defined at the beginning of application. Next, check the controllers and rout file. Define URL pattern and Play Framework will call method. Check sbt file to add new dependencies. All you may need in your work is already in Play Framework. This framework has its own template engine based on Scala language, but it is not suited for SPA creation. That’s why Play would be used only for data management. Notice how easy the application is running in standalone mode. Of course, there is always an option to integrate application with other servers, for example NGINX. In this case, the second version of Angular.js is to be used.

For SPA, the basic data format will be Json. The fastest solution is to save Json directly in the database, but it is important to note that this approach does not fit any needs of your application. With relational approach, main targets are storing data without duplicates and to be able execute SQL query to get different data. That’s why storing Json data can be convenient only in some cases. And we have one of suitable decision: PostgreSQL allows using special Jsonb data type. Hibernate usage will decrease the development process time – all you need is to create custom Jsonb datatype. Using ClientWithJsonb model allows easy forms creation with dynamically changing fields or other columns. It is also possible to create indexes for Json nodes. PostgreSQL has JSON query language but it is important to admit that there are some cons for usage. For example, it is impossible to update any node, replacing column only. Also, PostgreSQL has limited set of functions you can use in your project.

@Transactional
public static Result createNote(String userEmail) throws Exception {
JsonNode note = request().body().asJson();
String sql = "select * from clientwithJsonb where model->>'email' = ?";
ClientWithJsonb clientWithJsonb =
    (ClientWithJsonb) JPA.em().createNativeQuery(sql, ClientWithJsonb.class)
    .setParameter(1, userEmail).getSingleResult();

JsonNode newNode = clientWithJsonb.getModel().deepCopy();
((ObjectNode) newNode).put("notes", note);
clientWithJsonb.setModel(newNode);
JPA.em().persist(clientWithJsonb);
return ok();

Pay attention to the transactional annotation. Play Framework has its own API dealing with data storage; just use this annotation to create transaction. If it is needed to store JSON in database, another question occurs: how to validate it before saving? First, it is important to define validation rules for Json.

Check this Json example for client validation:

{
    "properties": {
        "name": {
            "type": "string",
            "minLength": 3
        },
        "secondName": {
            "type": "string",
            "minLength": 3
        },
        "address": {
            "type": "string",
            "minLength": 3
        },
        "age": {
            "type": "integer",
            "minimum": 0,
            "maximum": 120
        },
        "email": {
            "type": "string",
            "format": "email"
        },
        "tasks": {
            "type": "array",
            "$ref": "file://localhost/C:/work/JAVA_ONE/SPA_GIT/app/model/Json/schema/task.Json"
        }
    },
    "required": [
        "name",
        "secondName",
        "address",
        "age",
        "email"
    ]
}

Property type, name and length restrictions can be easily defined. Also using build-in data format it is possible to set an email or define your own. In addition, it is easy to make references to other files or objects already defined in a file.

Open site Json-schema.org to see the “The home of Json Schema”. It is a powerful tool for validation of different programming languages. In the example, we used Json-schema-validator version 4. As a rule, before starting validation, it is necessary to make deserialization. Using Json schema unbelievably decreases the time for validation. Just set up the test and check the results.

Selecting Json data is not a trivial task. First, you need to split array elements in Json to separated Json objects and then make some selection. Also, there is a possibility to write own stored procedure to create some custom rules for selection. Besides, PostgresSQL possesses a set of tools for data management such as triggers, temporary tables, views and others. Pay attention to the transactional annotation with read only attribute.

Let’s analyze the selecting query – it contains a subquery and the main query.

String sql = "select * FROM (select Jsonb_array_elements(model -> 'notes') "
		+ "from clientwithJsonb WHERE model ->> 'email' = ?) _
		as item WHERE item.Jsonb_array_elements @> '{\"text\": \"" + qLevel + "\"}'";

First, the subquery selects array data from a row with a specific email, then Jsonb_array_elements function splits array into the array elements and specific operator arranges elements search by key value. Jsonb array elements is PostgreSQL function, and it has more useful features for Json processing.

Let’s open our tests. There are two tests for application. Each of them does the same thing: validation while signup, then creation of a large Json document and some data selection.

Play Framework provides a very convenient API for unit test creation. It is possible to simulate start of your application using fakeApplication mock, then create fake request with some data and call a particular method in controller with pre-defined data. Use asserts to check internal data response, response status, etc.

Let’s make a test with help of Eclipse IDE. There are two tests. The first is a common approach for Json object creation and storage in a relational database with key value columns. The second test uses Jsonb datatype and stores Json data. Both of them are validated while signing up. As a result, Jsonb approach performs same operations faster.

This step-by-step case shows how to optimize a web application using new technologies which can easily increase the performance.