Getting Started with MariaDB using Docker, Java Spring and JDBC

Since being forked from MySQL back in 2009, MariaDB has become one of the most popular databases of choice for developers over the past decade. While many technologists have likely gravitated to it as a solution due to its open source roots and that it's rooted in the relational database world, that really only begins to scratch the surface of what MariaDB has to offer.

Over the years MariaDB has diverged from MySQL by adding many features and functionality, much of which we won't be able to dive into within the context of this article. However, one thing, above all else, has remained the same; providing a modern, open-source, high quality database solution that developers can use to power their innovation.

But before you can dive into MariaDB and check out all it has to offer you'll need to answer the most fundamental of questions; how do you get started using MariaDB?

MariaDB and Java Database Connectivity (JDBC)

It likely comes as no surprise that Java, and ultimately the Java Virtual Machine (JVM) environment as a whole, has been an extremely popular option used by developers to create applications.

With that in mind, I've written this short walkthrough to take you through the steps for getting started with MariaDB (Community Server) using a Docker image, Java and the MariaDB JDBC client.

In this walkthrough you'll be utilizing MariaDB and JDBC to create a simple (Maven based) "To do" application that, using Java Spring, will expose a variety of endpoints to use to perform basic CRUD (create-read-update-delete) operations on a MariaDB database instance.

Let's get started!

Requirements

Before jumping into code, you're going to need to make sure you have a few things on your machine:

• MariaDB client
• Docker
• Java (v. 8+)
• Curl (for testing the API endpoints)

Creating a new MariaDB instance using Docker

One of the simplest ways to get started with MariaDB, regardless of which operating system you're using, is to pull the MariaDB Server Docker image, from Docker Hub, and use it create a new container.

To do this simply open a terminal window and run the following:

$ docker run -p 3306:3306 -d --name mariadb -eMARIADB_ROOT_PASSWORD=Password123! mariadb/server:10.4 

That's it. You should now have a running instance of MariaDB. Pretty painless, right?

You can confirm the instance within a Docker container by running the following:

$ docker ps

And you should see your container within the output.

Connecting to MariaDB

Now that you've got a running MariaDB Server instance within a new Docker container the next step will be to connect to and communicate with the database instance using the MariaDB client.

There are many SQL clients available out in the wild. For simplicity's sake, I've chosen to demonstrate how to use the official MariaDB Client, but certainly feel free to use whatever client you prefer.

Connect to your MariaDB instance by executing the following:

$ mariadb --host 127.0.0.1 -P 3306 --user root -pPassword123!

You should see something like the following, which means you've successfully connected to the MariaDB instance!

Once you're connected you can create a new database.

CREATE DATABASE todo;

Then create a new table.

CREATE TABLE todo.tasks (
  id INT(11) unsigned NOT NULL AUTO_INCREMENT,
  description VARCHAR(500) NOT NULL,
  completed BOOLEAN NOT NULL DEFAULT 0,
  PRIMARY KEY (id)
);

Getting Started with Java Spring, JDBC and MariaDB

With a database instance spun up and schema created you're ready to create a new Java application.

Create a Maven project

Start by navigating to https://start.spring.io, which will enable you to create a new Spring-based Maven project. For this project you can enter the following criteria.

Next, add the following dependencies:

• Lombok: A library that will eliminate having to create boiler plate code (e.g. getters, setters) within the domain objects.
• Spring Web: A library to create and expose RESTful API endpoints.
• Spring Data JPA: A library that provides abstractions to help eliminate boiler plate code for connecting to and communicating with databases.

Finally, click the "GENERATE" button to create and download the project (contained within a .zip file) to a desired location on your machine.

Add the MariaDB R2DBC connector

Navigate to the location where you downloaded the new Maven project (.zip file) to, and unzip.  Then use a code editor to open the project, and open pom.xml.

Add a new dependency for MariaDB's JDBC connector to the collection of dependencies.

<dependency>             
   <groupId>org.mariadb.jdbc</groupId>           
   <artifactId>mariadb-java-client</artifactId>
   <version>2.6.0</version>
</dependency>

Preparing the data integration

Now that you've created a project that contains all of the dependencies you'll need, it's to jump into the Java code. Typically, I like to start by creating the entity classes.

Navigate to /src/main/java/com/mariadb/todo , create a new folder called "domain", and create a new file within it named "Task.java".

Open "Task.java" and add the following code.

package com.mariadb.todo.domain;

import javax.persistence.*;

import lombok.Data;

@Data
@Entity
@Table(name = "tasks")
public class Task {
    @Id 
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    private String description;
    private Boolean completed = false;
}

Next, create a new folder called "repositories" in /src/main/java/com/mariadb/todo, and create a new filed within it named "TaskRepository.java".

Open "TaskRepository.java" and add the following code.

package com.mariadb.todo.repositories;

import com.mariadb.todo.domain.Task;

import org.springframework.data.repository.CrudRepository;

public interface TaskRepository extends CrudRepository<Task, Integer> {
}

Like the name suggests the CrudRepository interface provides basic CRUD operations on a repository for a generic type (your Task class in this case) and the primary key type for that generic type.

Create a data service

Services can be used to manage the business logic of your application. The only service, TaskService, in this application is used for validating a Task object and integrating with the TaskRepository.

Create a new folder called "services" in /src/main/java/com/mariadb/todo, and create a new filed within it named "TaskService.java".

Open "TaskService.java" and add the following code.

package com.mariadb.todo.services;

import java.util.Optional;

import com.mariadb.todo.domain.Task;
import com.mariadb.todo.repositories.TaskRepository;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

// Registered as a Spring Service (Component)
@Service
public class TaskService {

    // Automatically instantiate (via Spring IoC) 
    @Autowired
    private TaskRepository repository;

    // 
    public Boolean isValid(final Task task) {
        if (task != null && !task.getDescription().isEmpty()) {
            return true;
        }
        return false;
    }

    // Get all records from the tasks table
    public Iterable<Task> getAllTasks() {
        return this.repository.findAll();
    }

    // Save a new task record
    public Task createTask(final Task task) {
        return this.repository.save(task);
    }

    // Update an existing task record
    @Transactional
    public Task updateTask(final Task task) {
        Optional<Task> ot = this.repository.findById(task.getId());
        Task t = ot.get();
        t.setDescription(task.getDescription());
        t.setCompleted(task.getCompleted());
        return this.repository.save(t);
    }

    // Delete the task record by specified id
    @Transactional
    public void deleteTask(final int id){
        Optional<Task> ot = this.repository.findById(id);
        Task t = ot.get();
        this.repository.delete(t);
    }
}

Expose API endpoints

Finally, you'll need to create a controller to expose four endpoints that can be used to perform the basic CRUD operations on your Tasks.

Create a new folder called "controllers" in /src/main/java/com/mariadb/todo, and create a new filed within it named "TaskController.java".

Open "TaskController.java" and add the following code.

package com.mariadb.todo.controllers;

import com.mariadb.todo.domain.Task;
import com.mariadb.todo.services.TaskService;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.DeleteMapping;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.PutMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

@RestController
@RequestMapping("/api/tasks")
public class TaskController {

    @Autowired
    private TaskService service;

    // Get all tasks
    @GetMapping()
    public ResponseEntity<Iterable<Task>> get() {
        return ResponseEntity.ok(this.service.getAllTasks());
    }

    // Create a new task
    @PostMapping()
    public ResponseEntity<Task> post(@RequestBody Task task) {
        if (service.isValid(task)) {
            return ResponseEntity.ok(this.service.createTask(task));
        }
        return ResponseEntity.status(HttpStatus.I_AM_A_TEAPOT).build();
    }

    // Update a task
    @PutMapping()
    public ResponseEntity<Task> put(@RequestBody Task task) {
        if (service.isValid(task)) {
            return ResponseEntity.ok(this.service.updateTask(task));
        }
        return ResponseEntity.status(HttpStatus.I_AM_A_TEAPOT).build();
    }

    // Delete a task
    @DeleteMapping()
    public ResponseEntity<Void> delete(@RequestParam int id) {
        if (id > 0) {
            this.service.deleteTask(id);
            return ResponseEntity.ok().build();
        }
        return ResponseEntity.status(HttpStatus.I_AM_A_TEAPOT).build();
    }
}

Testing it out

Now that everything has been constructed, it's time to test it out!

First, build the application.

$ mvn package

And then run it.

$ mvn spring-boot:run

First, start by adding a new task to your to do list.

$ curl --header "Content-Type: application/json" \
  --request POST \
  --data '{"description":"A New Task"}' \
  http://localhost:8080/api/tasks

While you can certainly query the database directly to confirm that a new task record has been added, where's the fun in that? Back to the API!

$ curl https://localhost:8080/api/tasks

If all goes well you should receive the following JSON response:

{ "id": 1, "description": "A New Task", "completed": false }

Voilà, a fully reactive Java Spring application using JDBC and MariaDB! To view this code in its entirety check out the source here. And if you're wondering "it'd sure be nice to see an implementation with a user interface", you're in luck! You can find a fully fleshed out implementation of a TODO application using React.js and your choice of multiple API projects
(R2DBC, JDBC, Node.js and Python) that integrate directly with MariaDB here!

Just getting started

Now that you've successfully created a new Maven project using Spring Data JPA, JDBC and MariaDB, you have all the tools you need to get started creating fully reactive applications, utilizing the power of MariaDB, of your own!If you have any questions, suggestions or concerns with this blog post please let me know here or reach out to me directly on Twitter at @probablyrealrob!Thanks for taking the time to read this and happy coding!