Auto-Generate a REST API From a Database With Spring

if you have an existing database and you want to write a front-end to work with it, you often find yourself spending hours setting up the glue between the database and the front-end. it would be a much more efficient use of your time if you could simply press a button and generate the entire rest api directly.

speedment is a tool that uses code generation to produce a tailored domain model based on an existing database structure. queries can either be sent directly to the database or served from in-memory for better performance. in this article, we will use the official plugin called "spring-generator" for speedment free to generate a complete spring application to serve a simple rest api. we will add support for paging, remote filtering and sorting, without writing a single line of code.

the database

in the examples below, we are using the sakila database for mysql. sakila is an example database that models a movie rental store. it has tables called film, actor, category, and so on.

step 1: create the project

the first thing we need is to configure our pom.xml -file to use the latest speedment dependencies and maven plugin. the fastest way to do this is to generate a pom.xml -file using the speedment initializer that you can find here . if you select "spring" in the plugin list and press "download," you will get an entire project folder with a main.java -file generated automatically.

next, open a command line where you downloaded the file and enter the following:

mvn speedment:tool

this will launch the speedment tool and prompt you for a license key. simply select "start free" and you will get a free license! once you have registered, you can connect to the database and get started.

step 2: generate a controller

by default, the only thing the spring-generator plugin adds in addition to the regular speedment classes is a @configuration -bean. to actually generate controllers for your tables, you can select the table in the tree to the left and then check "generate @restcontroller" on the right side.

this will enable a number of options below it, but we will get to these in a minute. for now, this is all you need to do to enable the controller logic. click "generate" to generate the code.

in the ide, you can now see a number of classes. if you downloaded the .zip -file from the initializer, then you should already have a main.java -file. make sure it is in the correct folder, then build and run the application.

mvn clean install && java -jar target/example-app-1.0.0-snapshot.jar

you can try out the new rest api by calling:

curl localhost:8080/film

easy, huh?

step 3: using filters

a cool feature with the spring-generator plugin is that it supports remote filtering. it means that the front-end can send predicates encoded as json-objects to the server, and the server will respond with a filtered json response. speedment automatically parses the json filters into a sql select -statement. if you want to be able to serve thousands of requests per second, you can also enable the speedment datastore to serve queries directly from memory, in which case the json filters will be parsed to find an appropriate in-memory index.

the syntax for the json filters is easy. to get films with a length less than 60 minutes, you simply call:

curl -g localhost:8080/film --data-urlencode \
  'filter={"property":"length","operator":"lt","value":60}'

(the -g argument makes sure that the command is sent as a get request and not a post request)

you can add multiple filters by wrapping the object in a list.

curl -g localhost:8080/film --data-urlencode \
  'filter=[{"property":"length","operator":"lt","value":60},
  {"property":"length","operator":"ge","value":30}]'

this will return all films with a length between 30 and 60 minutes. by default, all the operators in the list are assumed to be separated with and-operators. all the conditions must apply for a row to pass the filter. to change this, you can use an explicit or-statement.

curl -g localhost:8080/film --data-urlencode \
  'filter={"or":[{"property":"length","operator":"lt","value":30},
  {"property":"length","operator":"ge","value":60}]}'

this will return all films that are either shorter than 30 minutes or longer than one hour.

step 4: using sorters

by default, speedment returns rows in the same order as the database returns them. if you add filters to the expression, however, the returned order is undefined. to make sure the order is well defined in the front-end, you could, therefore, send a defined order to the generated backend to tell it how to sort elements. if you use speedment datastore to serve elements from in-memory, then it will use in-memory indexes to resolve the correct order.

here is an example where the films are retrieved by their length.

curl -g localhost:8080/film --data-urlencode \
  'sort={"property":"length"}'

to get the films in the opposite order, you can specify a descending order like this:

curl -g localhost:8080/film --data-urlencode \
  'sort={"property":"length","direction":"desc"}'

you can also send multiple sorters to the backend to define the primary order, the secondary order and so on.

curl -g localhost:8080/film --data-urlencode \
  'sort=[{"property":"length","direction":"desc"},
  {"property":"title","direction":"asc"}]'

step 5: using paging

the last feature of the spring-generator plugin is the ability to page results to avoid sending unnecessary large objects to the browser. this is enabled by default, which is why you won't see more than 25 results when you query the backend. to skip a number of results (not pages), you can specify the ?start= parameter.

curl localhost:8080/film?start=25

this will skip the first 25 elements and begin at the 26th. you can change the default page size by adding the ?limit= parameter.

curl 'localhost:8080/film?start=25&limit=5'

this also begins at the 26th element, but only returns 5 elements instead of 25.

summary

in this article, you have learned how easy it is to get a full rest api up and running using speedment, spring, and the generate-spring plugin.