In this post, we’ll implement a sample REST API that uses Spring Boot with MyBatis and PostgreSQL.

Prerequisites

This is the list of all the prerequisites:

• Spring Boot 2.6.5
• Gradle
• Java 17
• PostgreSQL

Overview

What is MyBatis?

MyBatis is a first-class persistence framework with support for custom SQL, stored procedures, and advanced mappings. MyBatis eliminates almost all of the JDBC code and manual setting of parameters and retrieval of results. MyBatis can use simple XML or Annotations for configuration and map primitives, Map interfaces, and Java POJOs (Plain Old Java Objects) to database records.

Why Mybatis?

MyBatis is a fork of iBATIS 3.0 and is maintained by a team that includes the original creators of iBATIS.

MyBatis comes with the following design philosophies −

• Open source− MyBatis is free and open-source software.
• Supports ORM − MyBatis supports many of the same features as an ORM tool, such as lazy loading, join fetching, caching, runtime code generation, and inheritance.
• Simplicity − MyBatis is widely regarded as one of the simplest persistence frameworks available today.
• Fast Development − MyBatis does all it can to facilitate hyper-fast development.
• Portability − MyBatis can be implemented for nearly any language or platform such as Java, Ruby, and C# for Microsoft .NET.
• Independent Interfaces − MyBatis provides database-independent interfaces, and APIs that help the rest of the application remain independent of any persistence-related resources.
• Stored procedures − MyBatis encapsulates SQL in the form of stored procedures so that business logic can be kept out of the database, and the application is more portable and easier to deploy and test.
• Inline SQL − No pre-compiler is needed, and you can have full access to all of the features of SQL.
• Dynamic SQL − MyBatis provides features for dynamic building SQL queries based on parameters.

Getting Started

We will start by creating a simple Spring Boot project from start.spring.io, with the following dependencies: Spring Boot, Liquibase, Mybatis, Lombok, Postgresql, and Validation.

Below is the build.gradle project:

lugins {
    id 'org.springframework.boot' version '2.7.0'
    id 'io.spring.dependency-management' version '1.0.11.RELEASE'
    id 'java'
}

group = 'com.example'
version = '0.0.1-SNAPSHOT'
sourceCompatibility = '17'

configurations {
    compileOnly {
        extendsFrom annotationProcessor
    }
}

repositories {
    mavenCentral()
}

dependencies {
    implementation 'org.springframework.boot:spring-boot-starter-validation'
    implementation 'org.springframework.boot:spring-boot-starter-web'
    implementation 'org.liquibase:liquibase-core'
    implementation 'org.mybatis.spring.boot:mybatis-spring-boot-starter:2.2.2'
    compileOnly 'org.projectlombok:lombok'
    runtimeOnly 'org.postgresql:postgresql'
    annotationProcessor 'org.projectlombok:lombok'
    testImplementation 'org.springframework.boot:spring-boot-starter-test'
}

tasks.named('test') {
    useJUnitPlatform()
}

Project Structure

Our project structure will look like this:

Database initialization

We added liquibase configurations to create the database schema and insert data before use by MyBatis. We will create the changelog versions in the /resources/db/changelog

Creating the book table (001_create_book_table.sql)

CREATE SEQUENCE book_id_seq;

CREATE TABLE book(
                     id bigint PRIMARY KEY DEFAULT nextval('book_id_seq'),
                     title VARCHAR(100) not null,
                     isbn VARCHAR(255),
                     description VARCHAR(255),
                     page int,
                     price numeric
);

ALTER SEQUENCE book_id_seq
    OWNED BY book.id;

We will create a new changelog YAML file (db.changelog-1.0.yaml) that runs SQL scripts.

databaseChangeLog:

  - changeSet:
      id: "1.0"
      author: boottech

  - include:
      file: book/001_create_book_table.sql
      relativeToChangelogFile: true

  - include:
      file: author/001_create_author_table.sql
      relativeToChangelogFile: true

  - changeSet:
      id: 202207011812-1-data
      author: boottech
      changes:
        - loadData:
            catalogName: cat
            columns:
              - column:
                  header: title
                  name: title
                  type: string
              - column:
                  header: page
                  name: page
                  type: numeric
              - column:
                  header: isbn
                  name: isbn
                  type: string
              - column:
                  header: description
                  name: description
                  type: string
              - column:
                  header: price
                  name: price
                  type: numeric
            commentLineStartsWith: //
            encoding: UTF-8
            file: book/book.csv
            quotchar: '""'
            relativeToChangelogFile: true
            schemaName: public
            separator: ','
            tableName: book
            usePreparedStatements: true

Spring MyBatis integration

After database initialization, we need to implement the codes that interact between Spring and MyBatis.

Create Book Model

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Book {

    private Long id;

    @NotBlank
    @Size(max = 100)
    private String title;

    private int page;

    private String isbn;

    private String description;

    private double price;
}

Create Book Repository

MyBatis-Spring-Boot-Starter will:

• Autodetect an existing DataSource
• Will create and register an instance of a SqlSessionFactory passing that DataSource as an input using the SqlSessionFactoryBean
• Will create and register an instance of a SqlSessionTemplate got out of the SqlSessionFactory
• Auto-scan your mappers, link them to the SqlSessionTemplate and register them to Spring context so they can be injected into your beans

The MyBatis-Spring-Boot-Starter will search, by default, for mappers marked with the @Mapper annotation.

@Mapper
@Repository
public interface BookRepository {

    String SELECT_FROM_BOOK_WHERE_ID = "SELECT * FROM book WHERE id = #{id}";
    String SELECT_FROM_BOOK = "select * from book";

    @Select(SELECT_FROM_BOOK)
    List<Book> findAll();

    @Select(SELECT_FROM_BOOK_WHERE_ID)
    Book findById(long id);

    @Select("SELECT * FROM book WHERE title = #{title}")
    Book findByTitle(String title);

    @Delete("DELETE FROM book WHERE id = #{id}")
    boolean deleteById(long id);

    @Insert("INSERT INTO book(title, isbn, description, page, price) " +
            " VALUES (#{title}, #{isbn}, #{description}, #{page}, #{price})")
    void insert(Book book);

    @Update("Update book set title=#{title}, " +
            " isbn=#{isbn}, description=#{description}, page=#{page}, price=#{price} where id=#{id}")
    int update(Book book);
}

Service Layer

The service layer implementation class (BookServiceImpl) will be injected by the BookRepository mapper.

@RequiredArgsConstructor
@Service
public class BookServiceImpl implements BookService {

    private final BookRepository repository;

    @Override
    public Book create(Book book) {
        Book bookById = getByTitle(book.getTitle());
        if(!ObjectUtils.isEmpty(bookById)){
          throw new DuplicateException(MessageFormat.format("Book {0} already exists in the system", book.getTitle()));
        }
        repository.insert(book);
        return getByTitle(book.getTitle());
    }

    @Override
    public List<Book> getAll(){
        return repository.findAll();
    }

    @Override
    public Book getOne(long id) {
        Book book = repository.findById(id);
        if(ObjectUtils.isEmpty(book)){
           throw new DataNotFoundException(MessageFormat.format("Book id {0} not found", String.valueOf(id)));
        }
        return book;
    }

    @Override
    public void deleteById(long id) {
        boolean isDeleted = repository.deleteById(id);
        if(!isDeleted){
            throw new BadRequestException("Delete error, please check ID and try again");
        }
    }

    @Override
    public Book getByTitle(String title) {
        return repository.findByTitle(title);
    }
}

Create Book Controller

@RequiredArgsConstructor
@RestController
@RequestMapping("/api/book")
public class BookController {

    private final BookService bookService;

     @PostMapping()
     public ResponseEntity<SuccessResponse> create(@RequestBody @Valid Book book) {
         if (!ObjectUtils.isEmpty(book.getId())) {
             throw new BadRequestException("A new data cannot already have an ID");
         }

         return new ResponseEntity<>(
                 new SuccessResponse(bookService.create(book), "Successful registration"),
                 HttpStatus.CREATED);
     }

    @GetMapping
    public ResponseEntity<SuccessResponse> getAll() {
        List<Book> books = bookService.getAll();

        return new ResponseEntity<>(new SuccessResponse(books, MessageFormat.format("{0} Results found", books.size())), HttpStatus.OK);
    }

    @GetMapping("/{id}")
    public ResponseEntity<SuccessResponse> getOne(@PathVariable("id") Long id) {
        Book book = bookService.getOne(id);
        return new ResponseEntity<>(
                new SuccessResponse(book, "Result found"), HttpStatus.OK);
    }

    @DeleteMapping("/{id}")
    public ResponseEntity<SuccessResponse> delete(@PathVariable("id") Long id) {
        bookService.deleteById(id);
        return new ResponseEntity<>(
                new SuccessResponse(null, "Deletion completed successfully"), HttpStatus.OK);
    }
    
}

Test the application

Now we can run our application and test it.

• Create Book

• Get All Book

• Get By book Id

• Delete By book Id

Conclusion

Well done !!. In this story, we implemented a REST API application that uses Spring Boot with MyBatis and PostgreSQL.

It’s an alternative to JDBC and Hibernate

The complete source code is available on GitHub.

Thanks for reading!

References

• https://mybatis.org/mybatis-3/
• https://www.perfomatix.com/hibernate-vs-mybatis/
• https://blog.mybatis.org/p/products.html

👉 Link to Medium blog