Building a CRUD Application with ag-Grid — Part 1

One of the most common scenarios in an Enterprise application is the Creation, Retrieval, Updating and Deletion (CRUD!) of records in some backend somewhere.

Although there are many ways to implement the backend in a system like this, I describe a simple setup here. The idea is that I want to convey how simple this can be. Once you’re happy with the overall concepts it should be a trivial exercise to swap out the backend for your preferred one. You will then be able to maintain and reuse most of the middle and front tiers of the application described.

Series Chapters

• Part 1: Introduction & Initial Setup: Maven, Spring and JPA/Backend (Database)
• Part 2: Middle Tier: Exposing our data with a REST Service
• Part 3: Front End — Initial Implementation
• Part 4: Front End — Grid Features & CRUD (Creation, Updates and Deletion)

The links above will become active as we publish the the following part of the blog series.

Introduction

Our application will use the following technologies:

• Backend: H2 for the Database (Oracle in a later, optional, chapter)
• Middle tier: Java, Spring, Hibernate
• Front end: ag-Grid, Angular 4.x

Our Application

The completed code for this blog series can be found here (once the series is complete), with this particular section being under Part 1.

Two parts of this entry of the series have been updated as the result of a bug I found later on.

Look for the Updated! note (like this one) - one change is in the Data Model section, while the other is in the Bootstrapping section.

The lesson here? Write more tests folks!

Our application will be a reporting tool that looks at past Olympics results. Users will be able to see historic results for the Olympic Games from 2000 to 2012 (7 games in all) and be able to see who won the most medals for a given year, who won the most often, which country is — on average — the most successful, and so on.

In order to focus on the concepts being discussed, the application is deliberately simple — we’ll implement this using a traditional 3 tier model:

Or alternatively, you can break it down as follows:

Initial Setup

Prerequisites

In order to follow through this series, a number of tools are required. Please see their documentation on how to download and install it for your particular Operating System.

Subsequent sections will assume these tools have been installed and are available for use.

• Java 8
• Maven 5
• NodeJS 6 (includes npm)
• Git

Spring Initializr

For the Backend and Middle Tier, we’ll be making use of both Maven and Spring (covered more later in the Series). In order to get up and running quickly, we’ll make use of Spring Initializr.

Spring Initializr provides a mechanism to get a Maven setup with specified key dependencies up and running with very little effort.

First, let’s navigate to http://start.spring.io/ to get started.

Once there, ensure you fill in the fields as follows:

The Group and Artifact can be anything you like. For Spring Boot I've chosen 2.0.0 (SNAPSHOT) as this is the latest available version for Spring 5 at the time of writing - if you have a newer version available that will be fine too.

In the Search for dependencies field search for web, jpa and finally h2, selecting each as the auto-complete presents a match.

Alternatively you can click on the Switch to the full version link and select the above dependencies manually.

Once done, click on Generate Project - you'll get a dialog to download the generated bundle. Save this to your chosen project location and once downloaded unzip it to a sub-directory called backend.

You should see the following project structures:

└── backend
   ├── LICENSE
   ├── mvnw
   ├── mvnw.cmd
   ├── pom.xml
   └── src
   ├── main
   │   ├── java
   │   │   └── com
   │   │   └── aggrid
   │   │   └── crudapp
   │   │   └── CrudAppApplication.java
   │   └── resources
   │   ├── application.properties
   │   ├── static
   │   └── templates
   └── test
   └── java
   └── com
   └── aggrid
   └── crudapp
      └── CrudAppApplicationTests.java

Maven provides a standard project structure, including a default Application file (CrudAppApplication.java) and default, starting, test file (CrudAppApplicationTests.java).

We have unzipped the generated project into a directory called backend as I prefer to keep the back and front end separate. Each tier can in effect be considered an application in its own right - this is especially true as your application grows in code and complexity.

Navigate to the backend directory; let's download the dependencies and do a quick sanity check:

mvn test

You should see a long output while mvn downloads all dependencies — this might take a little time depending on your internet speed. The above command will install all dependencies, compile the code and then run tests.

As it stands the test doesn’t do anything so you should see a message letting you know that the build was successful.

Running the Application

You can launch the application in one of 2 ways:

In an IDE

If you’re using an IDE you can run src/main/java/com/aggrid/crudapp/CrudAppApplication.java - this will launch the application.

Via Maven

mvn spring-boot:run

In either case the application will launch, but not actually do much at this stage.

Data Model

This section has been updated to simplify the model — there is now no join table
(it was unnecessary — not sure why I added it!).

The Athlete and Result classes have been updated to reflect this change.

Our database model is pretty simple — we have 4 entities here:

AthleteThe main entity - from this we can get the Results and Country associated to the AthleteSportSports completed in - static dataCountryAll Countries that completed - static dataResultJoins the tables above and captures each medal won, by which athlete in which sport, as well as the year, date and age of the athlete at the time of the event

This information could be modelled better, but it’s based on the data available (i.e. if we had the Athletes date of birth, we wouldn’t need the age row in Result - it could be computed).

Converting this to our middle tier, we’ll need the following four JPA Entities: Athlete, Sport, Medal and Country

Sport and Country are simple entities as these are primarily there as static data. Result and Athlete are the most interesting of these entities.

Result has a OneToOne mapping to Sport, and has a ManyToOne mapping to Athlete. Athlete in turn has a OneToOne mapping to Country, and a OneToMany mapping to Result.

This reflects the ERD (entity relationship diagram) model above.

We define each of these as JPA Entities by annotating the class with @Entity. we also let Spring know that we want the id field to be the ID by annotating the field with @Id and finally annotate the field with @GeneratedValue(strategy = GenerationType.AUTO) to have Spring/H2 auto-generate IDs for us.

There is obvious code duplication here which we could refactor, but for simplicities sake we’ve left the duplication in place.

With the above configuration Hibernate will create the following physical model for us:

Repositories

So far we have entities and their mapping — this is great, but to be useful we need a facility to be able to perform read/writes/deletions of data. Spring makes this easy for us by providing the CrudRepository interface which we can extend. With very little effort we get a great deal of functionality - this is a real time saver.

Below are the 4 Repositories we'll use in this application - one for each of the entities described above:

H2 Console

We can take a look at the generated model for us in H2 but opening up the H2 Console. This is disabled by default - we need to update /src/main/resources/application.properties to enable it:

spring.h2.console.enabled=true

We can then navigate to http://localhost:8080/h2-console/ and take a look at what we have.

Be sure that the JDBC URL field is set to jdbc:h2:mem:testdb. H2 will cache any previous URLs you might have used, but the in memory H2 one we're using here will be jdbc:h2:mem:testdb.

Test Data — Bootstrapping

This section has been updated when I discovered I was incorrectly inserting duplicate Athlete with it.

Write tests before you write code, and write them often!

In order to get the data we have (src/main/resources/olympicWinners.csv) into the H2 database, we'll make use of Spring's ApplicationListener<ContextRefreshedEvent> facility to load the test data and save it to the database.

We’ll make use of a third-party library called Jackson to convert the CSV to Java POJOs, so add this to your Maven pom.xml file, within the dependencies block:

<dependency>
    <groupId>com.fasterxml.jackson.dataformat</groupId>
    <artifactId>jackson-dataformat-csv</artifactId>
    <version>2.9.1</version>
    <scope>test</scope>
</dependency>

Note that we’re only using this library for local testing, so it has the test scope.

On startup the data in src/main/resources/olympicWinners.csv will be loaded, parsed and inserted in the database, ready for us to query.

Let’s start the application and then fire up the H2 console to test what we have. If we run the following query in the H2 console:

select a.name, c.name, r.age, c.name, r.year, r.date,s.name,r.gold,r.silver,r.bronze
from athlete a, country c, athlete_result ar, result r, sport s
where a.country_id = c.id
and ar.athlete_id = a.id
and ar.result_id = r.id
and r.sport_id = s.id

Optional — Spring DevTools

Spring Boot offers a development utility called Spring DevTools. This is an optional dependency, but makes local development much easier.

Let’s add the DevTools to our Maven pom.xml file:

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-devtools</artifactId>
    <optional>true</optional>
</dependency>

With this in place if you’re running your application and find you need to make a change you can do so, rebuild the project, and refresh the browser/end point without needing to restart your entire application.

This can be a real time saver, so adding this is recommended. Please refer to the Spring Boot DevTools documentation for more details about this.

Summary

This first part of the series is by necessity a bit by the numbers. It’s worth noting however if you simply run the commands above (and created the Java classes listed) you’d be up and running with a fully fledged CRUD system — all within no more than 30 minutes, which is pretty amazing.

In order to access the CRUD features we’ve just implemented we need to provide this to the front end — we’ll start covering that in the next part of this series!

Learn more about AG Grid — high performance JavaScript Data Grid.