I’ve been testing a ton of frameworks lately – good and otherwise. One of them, the Java micro framework Spark, really amazed me with its simplicity and rapid development capabilities. In this article, we’ll examine an example of Spark’s usefulness by creating a REST API.

So, without further ado, let’s see how to store, retrieve, update and delete some information in a PostgreSQL database using jOOQ ORM over a RESTful API in a simple To-do app.

What is a REST API?

In an application that follows a REST architectural style, we use the same url and different HTTP methods (GET, POST, PUT, DELETE, etc.) to convey different actions.

Below is a table explaining the behavior and function of all the routes that will be created.

BEHAVIOR ROUTE(URL) HTTP METHOD DESCRIPTION Hello page /hello GET The server returns ‘Hello world’ in response List all To-do items /tasks GET The server retrieves all To-do items from a database and returns a list of them. Create a new To-do item /tasks POST The server accepts the data enclosed in the body of the request (name of category, task title and description). A new task is created in the database. Update an existing To-do item /tasks/:id PUT The server accepts the new data enclosed in the body request (title and task description) for the particular task. The id of updated task is included in the url as :id . jOOQ updates the task. Delete an existing To-do item /tasks/:id DELETE The server deletes the task item that matches the id send as a named parameter in the url.

The architecture of the application will look something like the illustration below. At the lowest level, we have our Postgres database. Our app will communicate with the database via ORM , which maps relational model elements (tables, relations, etc.) into Java objects. Among many implementations, I chose jOOQ because its fluent API allows to easily generate sufficient classes and write SQL queries.

Because the application instance can’t deliver data to a web browser on its own, we’ll use the Jetty web server that is already built into Spark. Jetty processes requests via HTTP and puts the app into communication with the browser. Put it all together, and a top-down view would look like this:

Making Spark Say Hello

Now that we know how communication is taking place from our database to our browser, let’s get started by creating a Maven project and adding the Spark dependency to pom.xml in the dependencies section:

com.sparkjava spark-core 2.2

With Spark it’s very easy to create a RESTful endpoint and return some values – in our case a browser saying hello to the world.

To create such an endpoint we’ll use a Spark route that is made up of three simple pieces:

verb (get)

path (/hello)

callback (request, response) -> { }

public static void main(String[] args) throws Exception { get("/hello", new Route() { @Override public Object handle(Request request, Response response) { return "Hello world"; } }); }

Since Spark supports the lambda expressions introduced in Java 8, which facilitates functional programming and simplifies development, we can perform some refactoring within this example.

So, the lambda expression is characterized by following syntax:

parameter -> expression body

This way, the above routing can be replaced by:

Running the class starts up the build in the Jetty server:

[Thread-0] INFO spark.webserver.SparkServer - == Spark has ignited ... [Thread-0] INFO spark.webserver.SparkServer - >> Listening on 0.0.0.0:4567 [Thread-0] INFO org.eclipse.jetty.server.Server - jetty-9.0.2.v20130417 [Thread-0] INFO org.eclipse.jetty.server.ServerConnector - Started ServerConnector@1fbbde{HTTP/1.1}{0.0.0.0:4567}

Then, we can open a browser and type: http://localhost:4567/hello in the address bar. We will see the greeting:

All right. We’ve dipped our toes into the waters of Spark; let’s see how we can use Spark to build our simple app, starting with the lowest level: the database.

Creating a Database in PostgreSQL

We’ll be using PostgreSQL to store our data. Once you have Postgres installed, create a database and name it (for example, sparktodoapp) for use as a local database.

To create your database, you can use pgadmin, a PostgreSQL administration and management tool or do it by way of the command line interface.

Switch to the superuser account sudo su - postgres Run PostgreSQL command line client. psql Create a database instance create database sparktodoapp with owner xxx

Designing a Database

I don’t know anyone who enjoys writing a ddl. It’s repetitive and quite boring. Personally, I use Vertabelo to visually design my database and download the SQL. Moreover, the Vertabelo-jOOQ integration allows me to generate jOOQ classes directly from downloaded Vertabelo XML, which also accelerates development.

A very simple sample of a PostgreSQL database might look like this:

This model is pretty straightforward: a single table that represents a task item and associates it to a category . Each task has an id that has a serial type (auto-incrementing integer), title , description , a boolean is_done , and the appropriate category_id .

To create this table in a database, I need a ddl script. Additionally, I’ll need an xml file, which will be used to generate jOOQ classes. Those files can be accessed in two button clicks, as the following image shows:

Get Dependencies with Maven

Maven is a build automation tool that will download our required dependencies, execute SQL script in a database, and generate jOOQ classes. You can find pom.xml here. So, we need to do the following tasks:

Add jOOQ and driver for PostgreSQL to the dependencies section in pom.xml ... 3.6.2 ... ... ... org.jooq jooq ${org.jooq.version} org.postgresql postgresql 9.3-1101-jdbc41

Configure the sql-maven plugin and jooq’s codegen-maven plugin.

plugin and jooq’s plugin. The sql-maven plugin will execute downloaded SQL script, while

plugin will execute downloaded SQL script, while the jooq-codegen-maven plugin will generate jOOQ classes from the downloaded XML file. (To learn more about generating jOOQ classes, see this link.)

The configuration of the sql-maven plugin follows this pattern:

Add sql-maven-plugin to the plugin section

Create an execution that will execute SQL script into the configured database

Configure the PostgreSQL driver, database url (PostgreSQL listens on 5432 port by default), username, and password

Set the location of the SQL file that creates the database structures.

Add to dependencies section of the plugin driver for PostgreSQL org.codehaus.mojo sql-maven-plugin 1.5 create-database-postgres generate-sources execute org.postgresql.Driver jdbc:postgresql://localhost:5432/sparktodoapp xxx xxx true src/main/resources/task.sql postgresql postgresql 9.1-901-1.jdbc4

The configuration of jooq-codegen-maven plugin looks like this:

Add jooq-codegen-maven plugin to the plugin section

plugin to the plugin section Add jooq-meta-extensions as a dependency of the plugin

as a dependency of the plugin Create an execution which runs the generate goal of the jOOQ-codegen-maven plugin during the generate-sources Maven lifecycle phase.

Configure the plugin:

In database section:

Add org.jooq.util.vertabelo.VertabeloXMLDatabase as a name. (This class will be used to parse the downloaded Vertabelo XML file.)

as a name. (This class will be used to parse the downloaded Vertabelo XML file.) Provide two properties: a database dialect (in this case, POSTGRES) and the path for xml file .

(in this case, POSTGRES) and the . In the target section, provide the destination package and the destination directory for the generated code.

Here’s an example:

org.jooq jooq-codegen-maven ${org.jooq.version} org.jooq jooq-meta-extensions ${org.jooq.version} generate-postgres generate-sources generate org.jooq.util.DefaultGenerator org.jooq.util.vertabelo.VertabeloXMLDatabase dialect POSTGRES xml-file src/main/resources/task.xml com.example.db target/generated-sources/jooq-postgres

Run the following Maven command:

mvn clean install

Using these steps, we have:

Created a Task table in the sparktodoapp database

Generated jOOQ classes: Keys.java contains UNIQUE, PRIMARY KEY and FOREIGN KEY definitions. Task.java and Category.java are table classes that describe the structure of a single table. TaskRecord.java and Category.java are record classes . They contain information on a single table row.



Next, we’ll create a single class called TodoCRUD . For simplicity we’ll store the database configuration and CRUD functions in one class. Let’s start by adding a Java database connection pool library in the dependencies section of pom.xml, like so…

commons-dbcp commons-dbcp 1.2.2

… and place the database configuration into the standard main function in TodoCRUD class:

public class TodoCRUD { public static void main(String[] args) throws Exception { final BasicDataSource ds = new BasicDataSource(); // configure the connection to database ds.setDriverClassName("org.postgresql.Driver"); ds.setUrl("jdbc:postgresql://localhost:5432/sparktodoapp"); ds.setUsername("xxx"); ds.setPassword("xxx"); } }

We can configure the jOOQ DSLContext for creating SQL statements by setting the previously-configured data source and SQL dialect of database like so:

DSLContext dsl = DSL.using(ds, SQLDialect.POSTGRES);

Building a RESTful To-Do App

As mentioned at the beginning, we will follow a REST approach to building a web To-do app. This means that in our app we will use the same url and different HTTP methods to create, retrieve, delete, and update tasks.

1. Create A Task

When we want to create a new To-do item, we tell the browser to make a POST request, which sends information to change something on the server. To add a single To-do item, we select the name of a category and a description for our new task. The code for this is:

//CREATE post("/tasks", (request, response) -> { CategoryRecord category = dsl .selectFrom(Category.CATEGORY) .where(Category.CATEGORY.NAME.equal(request.queryParams("name"))) .fetchOne(); String title = request.queryParams("title"); // select title from the form String description = request.queryParams("description"); // select description from the form //validation omitted for sake of clarity TaskRecord taskRecord = dsl.newRecord(Task.TASK); taskRecord.setTitle(title); taskRecord.setDescription(description); taskRecord.setIsDone(false); taskRecord.setCategoryId(category.getId()); taskRecord.store(); return taskRecord; });

We can use Postman to test our app performance as it allows the creation of http requests. Once again, we start with a POST request that specifies all requested parameters and sends the request. The created item is returned as so:

2. Read a To-do Item.

When we want to retrieve To-do items from the browser, we are actually telling the browser to make a GET request. This method retrieves information without changing anything on the server.

In order to retrieve all To-do items, we call the select method of the DSLContext interface and then specify that we want to select attributes from the Task table. To get a list of TaskRecord objects, we’d tell the browser to call the fetchInto method of the ResultQuery interface. It would look like this:

// READ get("/tasks", (request, response) -> { List todoList = dsl.select(Task.TASK.ID, Task.TASK.TITLE, Task.TASK.DESCRIPTION, Task.TASK.IS_DONE, Task.TASK.CATEGORY_ID) .from(Task.TASK) .fetchInto(TaskRecord.class); return todoList; });

We can test this method with Postman by creating an HTTP GET request and sending it. A list of all To-do items is returned:

3. Update Stored Information

When we want to update information stored in database, we tell the browser to send a PUT request. In Spark’s PUT method, we can add a variable part to the URL (for example ‘ :id ’) . This variable part is then passed as a keyword argument to the function.

If we want to update a task with a particular id, we start with the following:

Fetch the To-do item that equals the requested id.

Create a select statement by calling the selectFrom method of DSLContext Interface

method of Interface Specify which item we want to fetch within the where method

method Call a fetchOne method in order to retrieve the record

To actually change the record, we need to:

Get the requested title and description

Set new values by using generated methods of setTitle() and setDescription()

and Update the record by calling an update method

The code for this would look like:

//UPDATE put("/tasks/:id", (request, response) -> { TaskRecord task = dsl .selectFrom(Task.TASK) .where(Task.TASK.ID.equal(Task.TASK.ID.getDataType().convert(request.params(":id")))) .fetchOne(); //if found if (task != null) { //retrieve new data for todolist String newTitle = request.queryParams("title"); String newDescription = request.queryParams("description"); task.setTitle(newTitle); task.setDescription(newDescription); task.update(); } else { response.status(404); } return "Example updated"; });

To test the PUT request, we’d create a query string by specifying the id of To-do item that we want to update and then setting new values for that item.

4. Delete an Item

When we want to delete a To-do item, we instruct the browser to make a DELETE request. The recipe is simple: create a delete statement by calling the deleteFrom method of the DSLContext interface. In the where method, we specify that we want to delete the item that matches the requested id.

//DELETE delete("/tasks/:id", (request, response) -> { TaskRecord task = dsl .deleteFrom(Task.TASK) .where(Task.TASK.ID.equal(Task.TASK.ID.getDataType().convert(request.params(":id")))) .returning() .fetchOne(); });

The appropriate DELETE request follows. Notice that we specify the id of the item we want to delete.

And there you have it – a simple app built on Spark and jOOQ.

Of course, the actual amount of written code used to build an app will vary based on the language you use. Some frameworks also make it harder by insisting on boilerplate code (or at least making it harder to avoid) and a more complicated configuration. Frameworks like Spark and jOOQ are very helpful in keeping code simple. You can test it for yourself, or see our example on this Github link.