Interacting with MySQL database in a Spring Boot project using MyBatis

This article is going to walk through the process of utilizing a MySQL database in a Spring Boot project using MyBatis. From the initial setup, to calling the queries from our application.

Configuring MyBatis

MyBatis has provided mybatis-spring-boot-autoconfigure to help us quickly setup the service in our application.

As mentioned in the “QuickStart” section of the configuration page, we only need to do the following things to be able to use the database in our application:

Add mybatis-spring-boot-autoconfigure dependency
Have a MySQL datasource
Create a mapper

Using MySQL database in Spring Boot

If we already have a datasource defined in our application we can skip this step, and the MyBatis autoconfigure will detect the datasource and pass it to the SQL Session.

First, we will want to add the following lines to application.properties:

spring.datasource.url=jdbc:mySql://localhost:3306/my_db
spring.datasource.username=user
spring.datasource.password=pass

Then we will need an Entity to represent our data object. For example, we want to record data of Books in a library with status that can be updated when a book is borrowed or available.

import lombok.Data;

@Data
public class Book {
  String title;
  String author;
  String category;
  String status;
}

Creating Mapper

Create a mapper class to define CRUD operations for our Book entity.

import com.application.MyApplication.model.Book;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

@Mapper
public interface BookRepository {
  @Select("SELECT * FROM book WHERE id = #{id};")
  Book findById(@Param("id") String id);

  @Insert("INSERT INTO book(id, title, author, category, status)"
      + "VALUES(#{id}, #{title}, #{author}, #{category}, #{status});")
  void save(
      @Param("id") String id,
      @Param("title") String title,
      @Param("author") String author,
      @Param("category") String category,
      @Param("status") String status
  );

  @Update("UPDATE book SET status=#{status} WHERE id=#{id};")
  void updateStatus(@Param("status") String status, @Param("id") String id);

  @Delete("DELETE FROM book WHERE id=#{id};")
  void delete(@Param("id") String id);
}

Query Calls

When implementing the query calls it’s important to pay attention to the security of the data input and output we send to the database. When there is a data discrepancy, we should send output / logs to the client side of the application so that we know exactly what the cause of the problem is. For this reason, we will put the query call separate from the main class, inside a “Service” class.

To find a Book we can simply use the call from the repository, wrapped in a try-catch to handle connection failures when calling MySQL database.

public Book findById(String id) {
  try {
    return bookRepository.findById(id);
  } catch (Throwable e) {
    throw new RuntimeException(String.format("Failed during search of Book with Id: %s.", id), e);
  }
}

When saving a new object we need to validate the data according to our specification. For example, in Book all fields are required. So when a field is empty we can throw an exception:

public void add(String title, String author, String category) {
  try {
    if (StringUtils.isBlank(title) || StringUtils.isBlank(author) || StringUtils.isBlank(category)) {
      throw new RuntimeException(String.format("Book field to be saved cannot be blank.\n"
          + "Entry: Title: %s, Author: %s, Category: %s.", title, author, category));
    }
    String id = generateBookId(title, author);
    bookRepository.add(id, title, author, category, BookStatus.AVAILABLE.getValue());
  } catch (Throwable e) {
    throw new RuntimeException(String.format("Failed during adding new Book.\n"
        + "Entry: Title: %s, Author: %s, Category: %s.", title, author, category), e);
  }
}

private String generateBookId(String title, String author) {
    String inputDate = new SimpleDateFormat("yyyyMMdd").format(new java.util.Date());
    return inputDate + "-" + author + "-" + title;
  }

When a customer is borrowing a book, we need to make sure that the book is available in the library, and currently is not being borrowed. Same as when they are returning a book, the data needs to match to make sure that the book is actually being borrowed:

public Book borrowBook(String id) {
  Book book;
  try {
    book = bookRepository.findById(id);
    if (book != null && book.getStatus() == BookStatus.AVAILABLE) {
      bookRepository.updateStatus(BookStatus.BORROWED.getValue(), id);
    }
  } catch (Throwable e) {
    throw new RuntimeException(String.format("Failed during borrowing of Book with Id: %s.", id), e);
  }
  return book;
}

public Book returnBook(String id) {
  Book book;
  try {
    book = bookRepository.findById(id);
    if (book != null && book.getStatus() == BookStatus.BORROWED) {
      bookRepository.updateStatus(BookStatus.AVAILABLE.getValue(), id);
    }
  } catch (Throwable e) {
    throw new RuntimeException(String.format("Failed during returning of Book with Id: %s.", id), e);
  }
  return book;
}

Finally, in case where the Library needs to discard a Book (for example if the book will be transferred to another library) we can use the “delete” function. We also need to make sure the book we want to discard is not currently being borrowed:

public void delete(String id) {
  Book book;
  try {
    book = bookRepository.findById(id);
    if (book != null && book.getStatus() == BookStatus.AVAILABLE) {
      bookRepository.delete(id);
    } else {
      throw new RuntimeException(String.format("Book id %s is not available to be deleted.", id));
    }
  } catch (Throwable e) {
    throw new RuntimeException(String.format("Failed during borrowing of Book with Id: %s.", id), e);
  }
}

Conclusion

We have created a simple system to manage Books using our application. We can see how easy it is to setup MyBatis in Spring Boot with the help of MyBatis Autoconfigure.

Next action we probably want to take is to add a component-test to our project, to make sure the interaction with MySQL database is behaving correctly, and if the correct exceptions are thrown in the correct situation.

We can also add caching when doing READ operation with our database to increase performance in the case where we are repeating the same query calls.