Last Updated on June 5, 2019 by Simanta

When developing enterprise applications, Spring programmers typically prefer writing data-centric code against a lightweight in-memory database, such as H2 rather than running an enterprise database server such as Oracle, or MySQL. Out of the box, Spring Boot is very easy to use with the H2 Database.

In-memory databases are useful in the early development stages in local environments, but they have lots of restrictions. As the development progresses, you would most probably require an RDBMS to develop and test your application before deploying it to use a production database server. I have written a series of posts on integrating Spring Boot for Oracle, MySQL, and PostgreSQL.

If you are wondering about the complexities of switching between databases, it’s actually quite simple. When you’re using Spring Data JPA with an ORM technology like Hibernate, the persistence layer is fairly well decoupled, which allows you to easily run your code against multiple databases. The level of decoupling even allows you to easily switch between an RDBMS and a NoSQL database, such as MongoDB. My previous post on Integrating Spring Boot for MongoDB covers that.

In this post, I will discuss Spring Boot integration for MariaDB. MariaDB started off as an offshoot of MySQL due to concerns of Oracle’s acquisition of MySQL. Led by the original developers of MySQL, MariaDB has become one of the fastest growing open source databases.

MariaDB Configuration

For this post, I’m using MariaDB installed locally on my laptop. You’ll need to have a database defined for your use.
Use the following command to log into MariaDB:
mysql -u root
Once you are logged in, use the following command to create a database.
create database springbootdb;

MariaDB Dependencies

First, we need to add the MariaDB database driver, mariadb-java-client, as a dependency to our project. The Maven POM file is this.

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>guru.springframework</groupId>
  <artifactId>blogposts</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <packaging>jar</packaging>
  <name>Blog Posts</name>
  <description>Misc Blog Posts</description>
  <parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>1.4.4.RELEASE</version>
    <relativePath/> <!-- lookup parent from repository -->
  </parent>
  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <start-class>guru.springframework.blog.BlogPostsApplication</start-class>
    <java.version>1.8</java.version>
  </properties>
  <dependencies>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter</artifactId>
    </dependency>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>
    <dependency>
      <groupId>org.mariadb.jdbc</groupId>
      <artifactId>mariadb-java-client</artifactId>
      <version>1.5.7</version>
    </dependency>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-test</artifactId>
      <scope>test</scope>
    </dependency>
  </dependencies>
  <build>
    <plugins>
      <plugin>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-maven-plugin</artifactId>
      </plugin>
    </plugins>
  </build>
</project>

Spring Boot Properties

We need to override the H2 database properties being set by default in Spring Boot. The nice part is, Spring Boot sets default database properties only when you don’t. So, when we configure MariaDB for use, Spring Boot won’t set up the H2 database anymore.

The following properties are required to configure MariaDB with Spring Boot. You can see these are pretty standard Java data source properties.

spring.datasource.url=jdbc:mariadb://localhost:3306/springbootdb
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driver-class-name=org.mariadb.jdbc.Driver
spring.jpa.hibernate.ddl-auto=create-drop

As we are using JPA, we need to configure Hibernate for MariaDB too. Line 4 tells Hibernate to recreate the database on startup. This is definitely not the behavior we want if this was actually a production database You can set this property to the following values: none, validate, update, create-drop. For a production database, you probably want to use validate.

JPA Entity

In our example application, we will perform CRUD operations on a user. For that, we will write a simple JPA entity, User for our application. I have written a post to use Spring Data JPA in a Spring Boot Web application, and so won’t go into JPA here.

User.java

package guru.springframework.blog.domain;

import javax.persistence.*;

@Entity
@Table(name = "user_tbl")
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Integer id;
    private String name;
    private int age;
    public User() {
    }

    public User(String name, int age) {
        this.name = name;
        this.age = age;
    }

    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }

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

    public int getAge() {
        return age;
    }
    public void setAge(int age) {
        this.age = age;
    }
    @Override
    public String toString() {
        return "User{" +
                ", name='" + name + '\'' +
                ", Age=" + age +
                '}';
    }
}

JPA Repository

Spring Data JPA CRUD Repository is a feature of Spring Data JPA that I extensively use. Using it, you can just define an interface that extends CrudRepository to manage entities for most common operations, such as saving an entity, updating it, deleting it, or finding it by id. Spring Data JPA uses generics and reflection to generate the concrete implementation of the interface we define.

For our User domain class, we can define a Spring Data JPA repository as follows.

UserRepository.java

package guru.springframework.blog.repositories;

import guru.springframework.blog.domain.User;
import org.springframework.data.repository.CrudRepository;

public interface UserRepository extends CrudRepository<User, Integer> {
    User findByName(String name);
}

That’s all we need to setup Spring Boot to use MariaDB. We will write some test code for this setup.

UserRepositoryTest.java

package guru.springframework.blog.repositories;

import guru.springframework.blog.domain.User;
import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import static org.junit.Assert.*;
@RunWith(SpringRunner.class)
@SpringBootTest
public class UserRepositoryTest {
    @Autowired
    private UserRepository userRepository;
    @Before
    public void setUp() throws Exception {
        User user1= new User("Alice", 23);
        User user2= new User("Bob", 38);
        //save user, verify has ID value after save
        assertNull(user1.getId());
        assertNull(user2.getId());//null before save
        this.userRepository.save(user1);
        this.userRepository.save(user2);
        assertNotNull(user1.getId());
        assertNotNull(user2.getId());
    }

    @Test
    public void testFetchData(){
        /*Test data retrieval*/
        User userA = userRepository.findByName("Bob");
        assertNotNull(userA);
        assertEquals(38, userA.getAge());
        /*Get all products, list should only have two*/
        Iterable<User> users = userRepository.findAll();
        int count = 0;
        for(User p : users){
            count++;
        }
        assertEquals(count, 2);
    }
}

For the test, I have used JUnit. To know more about JUnit, you can refer my series on JUnit Testing.
The result of the JUnit test is this.

Conclusion

As you can see, it is very easy to configure Spring Boot for MariaDB. As usual, Spring Boot will auto-configure sensible defaults for you. And as needed, you can override the default Spring Boot properties for your specific application.