How to Create a Spark REST API With jOOQ

Patrycja Dybka, Vertabelo

by
Patrycja Dybka
Community Manager at Vertabelo

Posted: October 22, 2015

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.

BEHAVIORROUTE(URL)HTTP METHODDESCRIPTION
Hello page/helloGETThe server returns ‘Hello world’ in response
List all To-do items/tasksGETThe server retrieves all To-do items from a database and returns a list of them.
Create a new To-do item/tasksPOSTThe 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/:idPUTThe 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/:idDELETEThe 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:

Spark to do app

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:

<dependency>
   <groupId>com.sparkjava</groupId>
   <artifactId>spark-core</artifactId>
   <version>2.2</version>
</dependency>

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:

Spark route lambda

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:

Spark Hello world

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.

  1. Switch to the superuser account

    sudo su - postgres

  2. Run PostgreSQL command line client.

    psql

  3. 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:

Download sql xml from Vertabelo

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

    <properties>
    ...
       <org.jooq.version>3.6.2</org.jooq.version>
    </properties>
    ...
    ...
    ...
       <dependency>
       <groupId>org.jooq</groupId>
       <artifactId>jooq</artifactId>
       <version>${org.jooq.version}</version>
    </dependency>
       <dependency>
       <groupId>org.postgresql</groupId>
       <artifactId>postgresql</artifactId>
       <version>9.3-1101-jdbc41</version>
    </dependency>
    

  • Configure the sql-maven plugin and jooq’s codegen-maven plugin.
  • The sql-maven 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.)

pom.xml in Spark todoapp

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

    <build>
       <plugins>
           <!-- Configure sql-maven-plugin -->
           <plugin>
               <groupId>org.codehaus.mojo</groupId>
               <artifactId>sql-maven-plugin</artifactId>
               <version>1.5</version>
    
               <executions>
                   <execution>
                       <id>create-database-postgres</id>
                       <phase>generate-sources</phase>
                       <goals>
                           <goal>execute</goal>
                       </goals>
                       <configuration>
                           <driver>org.postgresql.Driver</driver>
                           <url>jdbc:postgresql://localhost:5432/sparktodoapp</url>
                           <username>xxx</username>
                           <password>xxx</password>
    
                           <autocommit>true</autocommit>
                           <srcFiles>
                               <srcFile>src/main/resources/task.sql</srcFile>
                           </srcFiles>
                       </configuration>
                   </execution>
               </executions>
    
               <dependencies>
                   <dependency>
                       <groupId>postgresql</groupId>
                       <artifactId>postgresql</artifactId>
                       <version>9.1-901-1.jdbc4</version>
                   </dependency>
               </dependencies>
           </plugin>
    

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

  • Add jooq-codegen-maven plugin to the plugin section
  • Add jooq-meta-extensions 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.)
  • Provide two properties: a database dialect (in this case, POSTGRES) and the path for xml file.
  • In the target section, provide the destination package and the destination directory for the generated code.

Here’s an example:

       <!-- Configuration of codegen-maven plugin-->
       <plugin>
           <groupId>org.jooq</groupId>
           <artifactId>jooq-codegen-maven</artifactId>
           <version>${org.jooq.version}</version>

           <dependencies>
               <dependency>
                   <groupId>org.jooq</groupId>
                   <artifactId>jooq-meta-extensions</artifactId>
                   <version>${org.jooq.version}</version>
               </dependency>
           </dependencies>

           <executions>
               <execution>
                   <id>generate-postgres</id>
                   <phase>generate-sources</phase>
                   <goals>
                       <goal>generate</goal>
                   </goals>
                   <configuration>
                       <generator>
                           <name>org.jooq.util.DefaultGenerator</name>
                           <database>
                               <name>org.jooq.util.vertabelo.VertabeloXMLDatabase</name>
                               <properties>
                                   <property>
                                       <key>dialect</key>
                                       <value>POSTGRES</value>
                                   </property>
                                   <property>
                                       <key>xml-file</key>
                                       <value>src/main/resources/task.xml</value>
                                   </property>
                               </properties>
                           </database>
                           <target>
                               <packageName>com.example.db</packageName>
                               <directory>target/generated-sources/jooq-postgres</directory>
                           </target>
                       </generator>
                   </configuration>
               </execution>
           </executions>
       </plugin>
   </plugins>
</build>

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.

Generated jOOQ classes

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…

<dependency>
	<groupId>commons-dbcp</groupId>
	<artifactId>commons-dbcp</artifactId>
	<version>1.2.2</version>
</dependency>

… 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:

Postman POST

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<TaskRecord> 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:

Postman GET

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
  • Specify which item we want to fetch within the where 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()
  • 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.

Postman PUT

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.

Postman 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.

 
 
 

Try our online database modeler. No registration. No commitments.

 
 
Tags
 
Subscribe to our newsletter

If you find this article useful, join our weekly newsletter to be notified about the latest posts.

 
 
 
New SQL Course! Find out how to identify all the factors of a successful visualization: think like your audience, find the right chart type, and take out the meaning of the data. View course Discover our other courses: