How to use ActiveJDBC with Spring Boot

ActiveJDBC is a Java library implements Active record design pattern. It was inspired by ActiveRecord ORM from Ruby on Rails. ActiveJDBC can be used as an alternative to Hibernate in many projects. As long as there is little to no business logic involved. In this article, I demonstrate a comprehensive example of how to use ActiveJDBC with the conjunction of Spring Boot and MySQL 8.

The example of my choice is a music streaming platform where users can create a Playlist of Songs they like. For that, we will create a set of APIs to perform basic CRUD operations on the User, Song, and Platlist resources. And to communicate with the database, we will be using ActiveJDBC.

Keep in mind that we will not have the conventional service layer. Instead we follow the Active record pattern and add most of the business logic inside of the models.

Without further ado, let’s get started.

Adding ActiveJDBC dependencies

First thing first, we need to add ActiveJDBC dependency to our project and then do some configurations to make it running.

<dependencies>
    <dependency>
        <groupId>org.javalite</groupId>
        <artifactId>activejdbc</artifactId>
        <version>2.2</version>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.16</version>
    </dependency>
</dependencies>

<build>
    <plugins>
        <plugin>
            <groupId>org.javalite</groupId>
            <artifactId>activejdbc-instrumentation</artifactId>
            <version>2.2</version>
            <executions>
                <execution>
                    <phase>process-classes</phase>
                    <goals>
                        <goal>instrument</goal>
                    </goals>
                </execution>
            </executions>
        </plugin>
        <plugin>
            <groupId>org.javalite</groupId>
            <artifactId>db-migrator-maven-plugin</artifactId>
            <version>2.2</version>
            <configuration>
                <configFile>${project.basedir}/src/main/resources/database.properties</configFile>
                <environments>development</environments>
            </configuration>
            <executions>
                <execution>
                    <id>dev_migrations</id>
                    <phase>validate</phase>
                    <goals>
                        <goal>migrate</goal>
                    </goals>
                </execution>
            </executions>
            <dependencies>
                <dependency>
                    <groupId>mysql</groupId>
                    <artifactId>mysql-connector-java</artifactId>
                    <version>8.0.16</version>
                </dependency>
            </dependencies>
        </plugin>
    </plugins>
</build>
</project>

The first paragraph of the above snippet is rather simple. We just added ActiveJDBC and MySQL connector as the project dependency. But most of the magic comes in the second part of the code where we need to have two maven plugins.

The first plugin generates models and adds them in the classpath. This is necessary, otherwise, the application throws run time exception if does not find the instruments. To get that working, we need to run a maven goal process-classes every time before starting the application which is a bit hassle. But don’t worry about it, for now, I have a fix for it which is explained later.

And the second one handles the database migration or evolution which I configured what environment, development, to run and where to find the database configuration. This plugin offers two goals for the initial table creation and gradual migration. Very similar to Liquibase. The plugin keeps track of all executes scripts in schema_version table.

Unlike the previous plugin, database plugin is optional and can be omitted. But then all the database tables and changes should be done manually.

Database schema

For the music streaming example, we need to create at least four tables, users, songs, playlists, and playlist_song. The last table is required in order to have many to many relationship between song and playlists. As one song can appear in many playlists and one playlist can have many songs.

The final database schema is look like below:

CREATE TABLE IF NOT EXISTS users (
  id INTEGER DEFAULT NULL AUTO_INCREMENT PRIMARY KEY,
  email_address VARCHAR(512) NOT NULL,
  first_name VARCHAR(512) NOT NULL,
  last_name VARCHAR(512) NOT NULL,
  CONSTRAINT uc_user_email_address UNIQUE(email_address),
  INDEX idx_user_email_address (email_address)
) engine=InnoDB;

CREATE TABLE IF NOT EXISTS songs (
  id INTEGER AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(512) NOT NULL,
  artist VARCHAR(512) NOT NULL,
  user_id INTEGER NOT NULL,
  FOREIGN KEY (user_id)
        REFERENCES users(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
) engine=InnoDB;

CREATE TABLE IF NOT EXISTS playlists (
  id INTEGER AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(512) NOT NULL,
  user_id INTEGER NOT NULL,

  CONSTRAINT uc_playlist_name UNIQUE(name),
  FOREIGN KEY (user_id)
        REFERENCES users(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
) engine=InnoDB;

CREATE TABLE IF NOT EXISTS playlist_song (
  id INTEGER AUTO_INCREMENT PRIMARY KEY,
  playlist_id INTEGER NOT NULL,
  song_id INTEGER NOT NULL,

  CONSTRAINT fk_playlist_song_playlist_id FOREIGN KEY (playlist_id) REFERENCES playlists (id),
  CONSTRAINT fk_playlist_song_song_id FOREIGN KEY (song_id) REFERENCES songs (id)
) engine=InnoDB;

To utilize ActiveJDBC database migrator, we need to place the SQL file inside of the migrations directory under src folder. This is a predefined path.

In order for ActiveJDBC migrator to pick up the script, we need to follow a certain file naming convention such as 20190503131110_create_tables.sql. The format is Year Month Day Hour Minute Second_any description. I’ve tried other format and none has worked. Hence, I assume this is the only format to follow.

Integrating ActiveJDBC with Spring Boot ecosystem

Before starting to implement the code, we need to do one last configuration. And that’s integrating ActiveJDBC with Spring Boot. All we need to do is to create a Servlet filter that opens the database connection before hitting any endpoint and closes after that. This essentially is an optional requirement to reduce code complications. Otherwise, we need to open and close the connection each time manually.

The filter is as follows:

package com.madadipouya.example.activejdbc.activejdbcexample.filters;

import org.javalite.activejdbc.Base;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;

import javax.servlet.*;
import java.io.IOException;

@Component
public class ActiveJdbcFilter implements Filter {

    private static final Logger logger = LoggerFactory.getLogger(ActiveJdbcFilter.class);

    @Override
    public void init(FilterConfig config) {
    }

    @Override
    public void doFilter(ServletRequest req, ServletResponse resp, FilterChain chain) throws IOException, ServletException {
        long before = System.currentTimeMillis();
        try {
            Base.open();
            Base.openTransaction();
            chain.doFilter(req, resp);
            Base.commitTransaction();
        } catch (IOException | ServletException e) {
            Base.rollbackTransaction();
            throw e;
        } finally {
            Base.close();
        }
        logger.info("Processing took: {} milliseconds", System.currentTimeMillis() - before);
    }

    @Override
    public void destroy() {
    }
}

Keep in mind the Servlet filter is not the only option. The same results can be achieved by defining aspects or implementing HandlerInterceptor.

Creating models

Now that we have done with the configurations, it’s time to create models for our database tables. Unlike Hibernate, we don’t need to define any property in the models. Solely, extending activejdbc.Model suffices to have a functioning entity.

Keep in mind all the validations need to be done in the model under static block. ActiveJDBC provides some basic predefined validations as well as allowing to declare and inject yours.

We define our music streaming table model as below:

package com.madadipouya.example.activejdbc.activejdbcexample.model;

import org.javalite.activejdbc.Model;
import org.javalite.activejdbc.annotations.Table;

@Table("users")
public class User extends Model {

    private static final String FIRST_NAME_FIELD = "first_name";

    private static final String LAST_NAME_FIELD = "last_name";

    private static final String EMAIL_ADDRESS_FIELD = "email_address";

    static {
        validatePresenceOf(FIRST_NAME_FIELD, LAST_NAME_FIELD, EMAIL_ADDRESS_FIELD);
        validateEmailOf(EMAIL_ADDRESS_FIELD);

    }

    public User() {

    }

    public User(String firstName, String lastName, String emailAddress) {
        set(FIRST_NAME_FIELD, firstName, LAST_NAME_FIELD, lastName, EMAIL_ADDRESS_FIELD, emailAddress);
    }

    public User merge(String firstName, String lastName, String emailAddress) {
        return set(FIRST_NAME_FIELD, firstName, LAST_NAME_FIELD, lastName, EMAIL_ADDRESS_FIELD, emailAddress);
    }
}

package com.madadipouya.example.activejdbc.activejdbcexample.model;

import org.javalite.activejdbc.Model;
import org.javalite.activejdbc.annotations.Many2Many;
import org.javalite.activejdbc.annotations.Table;

@Many2Many(other = Playlist.class, join = "playlist_song", sourceFKName = "song_id", targetFKName = "playlist_id")
@Table("songs")
public class Song extends Model {

    private static final String TITLE_FIELD = "title";

    private static final String ARTIST_FIELD = "artist";

    static {
        validatePresenceOf(TITLE_FIELD, ARTIST_FIELD);
    }

    public Song() {

    }

    public Song(String title, String artist) {
        set(TITLE_FIELD, title, ARTIST_FIELD, artist);
    }

    public Song merge(String title, String artist) {
        return set(TITLE_FIELD, title, ARTIST_FIELD, artist);
    }
}

package com.madadipouya.example.activejdbc.activejdbcexample.model;

import org.javalite.activejdbc.Model;
import org.javalite.activejdbc.annotations.Table;

@Table("playlists")
public class Playlist extends Model {

    private static final String PLAYLIST_NAME_FIELD = "name";

    static {
        validatePresenceOf(PLAYLIST_NAME_FIELD);
    }

    public Playlist() {

    }

    public Playlist(String name) {
        set(PLAYLIST_NAME_FIELD, name);
    }

    public Playlist merge(String name) {
        return set(PLAYLIST_NAME_FIELD, name);
    }
}

As described before, all the validations are done in static block. Additionally, I defined some column names as the static final variable just for convenience. And also allowing to prepare the object for persistence by accepting parameters in the constructor and setting them inside of the model internally.

The interesting method here is the merge method which I coded for enabling flawless updates which in reality is no difference than create.

It is important to know that for Many to Many relationship, we need to create a separate model. And if we don’t follow the naming convention of ActiveJDBC, like this example, we have to define the relationship via annotations in one side of the relation. For our example, we did that in the Song model by annotating the class with:

@Many2Many(other = Playlist.class, join = "playlist_song", sourceFKName = "song_id", targetFKName = "playlist_id")

ActiveJDBC has a naming convention for Many to Many table which is table name_table name. However, in our example, we don’t follow that. We simply have playlist_song which according to the convention should be playlists_songs. And that is why we override the relationship.

Implementing the Spring Boots controllers

The next which is the last step is to implement the endpoints. These endpoints are not different than normal controllers just they might have some additional logic. One shortcoming of ActiveJDBC is by default it didn’t ship with many validators. One way to compensate that is to use Javax validators in DTOs to disallow end users entering undesirable inputs.

The following is the implementation of our music stream controllers:

package com.madadipouya.example.activejdbc.activejdbcexample.controller;

import com.google.common.collect.ImmutableMap;
import com.madadipouya.example.activejdbc.activejdbcexample.controller.exceptions.UserNotFoundException;
import com.madadipouya.example.activejdbc.activejdbcexample.model.Playlist;
import com.madadipouya.example.activejdbc.activejdbcexample.model.Song;
import com.madadipouya.example.activejdbc.activejdbcexample.model.User;
import org.springframework.http.HttpStatus;
import org.springframework.web.bind.annotation.*;

import javax.validation.Valid;
import javax.validation.constraints.Email;
import javax.validation.constraints.NotBlank;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

@RequestMapping("/v1/users")
@RestController
public class UserController {

    @GetMapping
    @ResponseStatus(HttpStatus.OK)
    @ResponseBody
    public List<Map<String, Object>> getUsers() {
        return User.findAll().toMaps();
    }

    @GetMapping("/details")
    @ResponseStatus(HttpStatus.OK)
    @ResponseBody
    public List<Map<?, ?>> getUsersWithDetails() {
        return User.findAll().stream().map(user -> ImmutableMap.builder().putAll(user.toMap())
                .put("songs", user.getAll(Song.class).toMaps()).put("playlists", user.getAll(Playlist.class).toMaps())
                .build()).collect(Collectors.toList());
    }

    @ResponseStatus(HttpStatus.CREATED)
    @PostMapping
    public Map<String, Object> createUser(@Valid @RequestBody UserDto userDto) {
        User user = new User(userDto.firstName, userDto.lastName, userDto.emailAddress);
        if (user.saveIt()) {
            return user.toMap();
        }
        return null;
    }

    @ResponseStatus(HttpStatus.NO_CONTENT)
    @DeleteMapping(value = "/{userId}")
    public void deleteUser(@PathVariable int userId) throws UserNotFoundException {
        User user = User.findById(userId);
        if (user != null) {
            user.deleteCascade();
        } else {
            throw new UserNotFoundException(String.format("There is no user associated with id: %s", userId));
        }
    }

    @PutMapping(value = "/{userId}")
    @ResponseStatus(HttpStatus.OK)
    public Map<String, Object> updateUser(@PathVariable int userId, @RequestBody UserDto userDto) throws UserNotFoundException {
        User user = User.findById(userId);
        if (user != null) {
            user.merge(userDto.firstName, userDto.lastName, userDto.emailAddress);
            if (user.saveIt()) {
                return user.toMap();
            }
            return null;
        }
        throw new UserNotFoundException(String.format("There is no user associated with id: %s", userId));
    }

    public static class UserDto {
        @Email
        @NotBlank
        private String emailAddress;

        @NotBlank
        private String firstName;

        @NotBlank
        private String lastName;

        public String getEmailAddress() {
            return emailAddress;
        }

        public void setEmailAddress(String emailAddress) {
            this.emailAddress = emailAddress;
        }

        public String getFirstName() {
            return firstName;
        }

        public void setFirstName(String firstName) {
            this.firstName = firstName;
        }

        public String getLastName() {
            return lastName;
        }

        public void setLastName(String lastName) {
            this.lastName = lastName;
        }
    }
}

package com.madadipouya.example.activejdbc.activejdbcexample.controller;

import com.madadipouya.example.activejdbc.activejdbcexample.controller.exceptions.SongNotFoundException;
import com.madadipouya.example.activejdbc.activejdbcexample.controller.exceptions.UserNotFoundException;
import com.madadipouya.example.activejdbc.activejdbcexample.model.Song;
import com.madadipouya.example.activejdbc.activejdbcexample.model.User;
import org.springframework.http.HttpStatus;
import org.springframework.web.bind.annotation.*;

import javax.validation.Valid;
import javax.validation.constraints.NotBlank;
import java.util.List;
import java.util.Map;

@RequestMapping("/v1/songs")
@RestController
public class SongController {

    @GetMapping(value = "/{userId}")
    @ResponseStatus(HttpStatus.OK)
    @ResponseBody
    public List<Map<String, Object>> getSongs(@PathVariable int userId) throws UserNotFoundException {
        User user = User.findById(userId);
        if (user != null) {
            return user.getAll(Song.class).toMaps();
        }
        throw new UserNotFoundException(String.format("There is no user associated with id: %s", userId));
    }

    @ResponseStatus(HttpStatus.CREATED)
    @PostMapping(value = "/{userId}")
    public Map<String, Object> createSong(@PathVariable int userId, @Valid @RequestBody SongDto songDto) throws UserNotFoundException {
        User user = User.findById(userId);
        if (user != null) {
            Song song = new Song(songDto.title, songDto.artist);
            user.add(song);
            return song.toMap();
        }
        throw new UserNotFoundException(String.format("There is no user associated with id: %s", userId));
    }

    @ResponseStatus(HttpStatus.OK)
    @PutMapping(value = "/{songId}")
    public Map<String, Object> updateSong(@PathVariable int songId, @Valid @RequestBody SongDto songDto) throws SongNotFoundException {
        Song song = Song.findById(songId);
        if (song != null) {
            song.merge(songDto.title, songDto.artist);
            if (song.saveIt()) {
                return song.toMap();
            }
            return null;
        }
        throw new SongNotFoundException(String.format("There is no song associated with id: %s", songId));
    }

    @ResponseStatus(HttpStatus.NO_CONTENT)
    @DeleteMapping(value = "/{songId}")
    public void deleteSong(@PathVariable int songId) throws SongNotFoundException {
        Song song = Song.findById(songId);
        if (song != null) {
            song.deleteCascadeShallow();
        } else {
            throw new SongNotFoundException(String.format("There is no song associated with id: %s", songId));
        }
    }

    public static class SongDto {
        @NotBlank
        private String title;

        @NotBlank
        private String artist;

        public String getTitle() {
            return title;
        }

        public void setTitle(String title) {
            this.title = title;
        }

        public String getArtist() {
            return artist;
        }

        public void setArtist(String artist) {
            this.artist = artist;
        }
    }
}

package com.madadipouya.example.activejdbc.activejdbcexample.controller;

import com.google.common.collect.ImmutableMap;
import com.madadipouya.example.activejdbc.activejdbcexample.controller.exceptions.PlaylistNotFoundException;
import com.madadipouya.example.activejdbc.activejdbcexample.controller.exceptions.UserNotFoundException;
import com.madadipouya.example.activejdbc.activejdbcexample.model.Playlist;
import com.madadipouya.example.activejdbc.activejdbcexample.model.Song;
import com.madadipouya.example.activejdbc.activejdbcexample.model.User;
import org.springframework.http.HttpStatus;
import org.springframework.web.bind.annotation.*;

import javax.validation.Valid;
import javax.validation.constraints.NotBlank;
import javax.validation.constraints.NotNull;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

@RequestMapping("/v1/playlists")
@RestController
public class PlaylistController {

    @GetMapping(value = "/{userId}")
    @ResponseStatus(HttpStatus.OK)
    @ResponseBody
    public List<Map<String, Object>> getPlaylists(@PathVariable int userId) throws UserNotFoundException {
        User user = User.findById(userId);
        if (user != null) {
            return user.getAll(Playlist.class).toMaps();
        }
        throw new UserNotFoundException(String.format("There is no user associated with id: %s", userId));
    }

    @GetMapping(value = "/{userId}/details")
    @ResponseStatus(HttpStatus.OK)
    @ResponseBody
    public List<Map<?, Object>> getPlaylistWithSongs(@PathVariable int userId) throws UserNotFoundException {
        User user = User.findById(userId);
        if (user != null) {
            return createPlaylistWithSongsResponse(user);
        }
        throw new UserNotFoundException(String.format("There is no user associated with id: %s", userId));
    }

    @ResponseStatus(HttpStatus.CREATED)
    @PostMapping(value = "/{userId}")
    public Map<String, Object> createPlaylist(@PathVariable int userId, @Valid @RequestBody PlaylistDto playlistDto) throws UserNotFoundException {
        User user = User.findById(userId);
        if (user != null) {
            Playlist playlist = new Playlist(playlistDto.name);
            user.add(playlist);
            addSongsToPlaylist(playlist, filterInvalidSongs(user, playlistDto));
            return playlist.toMap();
        }
        throw new UserNotFoundException(String.format("There is no user associated with id: %s", userId));
    }

    @ResponseStatus(HttpStatus.OK)
    @PutMapping(value = "/{playlistId}")
    public List<Map<?, Object>> updatePlaylist(@PathVariable int playlistId, @Valid @RequestBody PlaylistDto playlistDto) throws PlaylistNotFoundException {
        Playlist playlist = Playlist.findById(playlistId);
        if (playlist != null) {
            User user = User.findById(playlist.get("user_id"));
            playlist.merge(playlistDto.name);
            if (playlist.saveIt()) {
                updatePlaylist(playlist, filterInvalidSongs(user, playlistDto));
            }
            return createPlaylistWithSongsResponse(user);
        }
        throw new PlaylistNotFoundException(String.format("There is no playlist associated with id: %s", playlistId));
    }

    @ResponseStatus(HttpStatus.NO_CONTENT)
    @DeleteMapping(value = "/{playlistId}")
    public void deletePlaylist(@PathVariable int playlistId) throws PlaylistNotFoundException {
        Playlist playlist = Playlist.findById(playlistId);
        if (playlist != null) {
            playlist.deleteCascadeShallow();
        } else {
            throw new PlaylistNotFoundException(String.format("There is no playlist associated with id: %s", playlistId));
        }
    }

    private void addSongsToPlaylist(Playlist playlist, List<Song> songs) {
        songs.forEach(playlist::add);
    }

    private void updatePlaylist(Playlist playlist, List<Song> songs) {
        songs.forEach(playlist::remove);
        addSongsToPlaylist(playlist, songs);
    }

    private List<Song> filterInvalidSongs(User user, PlaylistDto playlistDto) {
        return user.getAll(Song.class).stream().filter(song -> playlistDto.songIds.contains(song.getId()))
                .collect(Collectors.toList());
    }

    private List<Map<?, Object>> createPlaylistWithSongsResponse(User user) {
        return user.getAll(Playlist.class).stream().map(playlist -> ImmutableMap.builder().putAll(playlist.toMap())
                .put("songs", playlist.getAll(Song.class).toMaps()).build()).collect(Collectors.toList());
    }

    public static class PlaylistDto {
        @NotBlank
        private String name;

        @NotNull
        private List<Integer> songIds;

        public String getName() {
            return name;
        }

        public void setName(String name) {
            this.name = name;
        }

        public List<Integer> getSongIds() {
            return songIds;
        }

        public void setSongIds(List<Integer> songIds) {
            this.songIds = songIds;
        }
    }
}

Before finishing this post, I’d like to touch down on delete methods in ActiveJDBC. In our example we used three types of delete and each has a different functionality as below:

• delete: deletes the sole row of the table only. Such as deleting a user or a song.
• deleteCascade: deletes the row with all its associations entirely. For example, deleting a user row results in removing all the user’s songs and playlists.
• deleteCascadeShallow: deletes the row and its associated foreign keys. For example, deleting a song results in deleting the foreign key of the song from playlist_song table as well. But keeps the rest of playlist untouched.

The example source code is available at GitHub at this link: https://github.com/kasramp/active-jdbc-example
All the instructions on how to run the project are provided in the readme file.

Well, that’s all about the ActiveJDBC. Of course, the example presented here was a simple one. You can do much more with ActiveJDBC and use it as a full-blown ORM. In the next article, I’ll discuss the Active Record pattern.

If you are interested about high performance data insertion with Spring Boot and Hibernete have a look at my previous tutorial at below link:
https://www.geekyhacker.com/high-performance-data-fetching-using-spring-data-jpa-stream/