Try to get data from database using MyBatis in Micronaut + Kotlin project

Last time

Hello World with Micronaut + Kotlin Extend the previous project and try connecting to the database using MyBatis.

The repository actually created is here.

Prepare database

Prepare a PostgreSQL container with Docker. I created the following in the same hierarchy as the project created last time.

docker-compose.yml


version: '3'
services: 
  db:
    image: postgres
    restart: always
    container_name: postgres
    environment: 
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
      POSTGRES_DB: books
    volumes:
      - ./data/postgres/db-data:/var/lib/postgresql/data
      - ./database:/docker-entrypoint-initdb.d
    ports:
      - 5432:5432

database/init.sql


set client_encoding = 'UTF8';

CREATE TABLE book (
  id SERIAL,
  name VARCHAR(200) NOT NULL,
  publisher VARCHAR(200),
  publication_date DATE,
  created_at TIMESTAMP NOT NULL,
  updated_at TIMESTAMP NOT NULL,
  CONSTRAINT pk_book PRIMARY KEY (id)
);

--Initial data for confirmation
INSERT INTO book (id, name, publisher, publication_date, created_at, updated_at) VALUES (1, 'Test book 1', 'Test publisher A', null, current_timestamp, current_timestamp);
INSERT INTO book (id, name, publisher, publication_date, created_at, updated_at) VALUES (2, 'Test book 2', 'Test publisher A', null, current_timestamp, current_timestamp);

.gitignore


data

Go to the folder with docker-compose and start the container.

$ docker-compose up -d

Now the DB is ready.

Project refurbishment

Add library

Edit build.gradle and add the library.

server/build.gradle


dependencies {
    //abridgement
    implementation("org.mybatis:mybatis:3.4.6")
    implementation("io.micronaut.sql:micronaut-jdbc-hikari")
    runtimeOnly("org.postgresql:postgresql")
    //abridgement
}

Add connection settings

Edit application.yml.

server/src/main/resources/application.yml


micronaut:
  application:
    name: helloworld
#Add the following
datasources:
  default:
    url: jdbc:postgresql://localhost:5432/books
    username: postgres
    password: postgres
    driverClassName: org.postgresql.Driver
mybatis:
  #Specify the location to store the mapper xml file
  mapper-locations: classpath:example/mapper/*.xml
  configuration:
    map-underscore-to-camel-case: true
    default-fetch-size: 100
    default-statement-timeout: 30
    cache-enabled: false

Creating a model

Create a class to store the data obtained from DB.

server/src/main/kotlin/example/model/Book.kt


package example.model

import io.micronaut.core.annotation.Introspected
import java.sql.Date

/**
 *Book data class
 */
@Introspected
data class Book(
        var id: Int?,
        var name: String,
        var publisher: String?,
        var publicationDate: Date?,
)

Creating a SqlSessionFactory

Create a Factory class that prepares a SqlSession.

server/src/main/kotlin/example/MybatisFactory.kt


package example

import io.micronaut.context.annotation.Factory
import org.apache.ibatis.mapping.Environment
import org.apache.ibatis.session.Configuration
import org.apache.ibatis.session.SqlSessionFactory
import org.apache.ibatis.session.SqlSessionFactoryBuilder
import org.apache.ibatis.transaction.TransactionFactory
import org.apache.ibatis.transaction.jdbc.JdbcTransactionFactory
import javax.inject.Singleton
import javax.sql.DataSource

@Factory
class MybatisFactory(private val dataSource: DataSource) {

    @Singleton
    fun sqlSessionFactory(): SqlSessionFactory {
        val transactionFactory: TransactionFactory = JdbcTransactionFactory()

        val environment = Environment("dev", transactionFactory, dataSource)
        val configuration = Configuration(environment)
        //Specify the package to scan the mapper.
        configuration.addMappers("example")

        return SqlSessionFactoryBuilder().build(configuration)
    }
}

Creating a Mapper

Create a Mapper that will interface with SQL.

server/src/main/kotlin/example/mapper/BookMapper.kt


package example.mapper

import example.model.Book
import org.apache.ibatis.annotations.Mapper

@Mapper
interface BookMapper {
    fun findById(id: Int): Book
}

The SQL body to be executed is described in XML.

server/src/main/resources/example/mapper/BookMapper.xml


<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="example.mapper.BookMapper">

    <select id="findById" resultType="example.model.Book">
        SELECT
        id,
        name,
        publisher,
        publication_date
        FROM book
        <where>
            id = #{id}
        </where>
    </select>

</mapper>

Create a Service class as an implementation of the interface.

server/src/main/kotlin/example/service/BookService.kt


package example.service

import example.mapper.BookMapper
import example.model.Book
import org.apache.ibatis.session.SqlSessionFactory
import javax.inject.Singleton

@Singleton
class BookService(private val sqlSessionFactory: SqlSessionFactory) : BookMapper {

    override fun findById(id: Int): Book {
        sqlSessionFactory.openSession().use { session ->
            val bookMapper = session.getMapper(BookMapper::class.java)
            return bookMapper.findById(id)
        }
    }
}

Creating a Controller

Create an API to access the database.

server/src/main/kotlin/example/controller/BookController.kt


package example.controller

import example.mapper.BookMapper
import example.model.Book
import io.micronaut.http.HttpResponse
import io.micronaut.http.annotation.Controller
import io.micronaut.http.annotation.Get


@Controller()
class BookController(private val bookMapper: BookMapper) {

    /**
     *Acquisition of book information.
     *ID specification
     *
     * @param id Book ID
     *
     * @return HttpResponse
     */
    @Get("/book/{id}")
    fun readById(id: Int): HttpResponse<Book> {
        return HttpResponse.ok(bookMapper.findById(id))
    }
}

Completed for the time being

So far, if you access the endpoint of / book / {id}, you can get the data. I'll give it a try.

Run the server.

$ cd server 
$ ./gradlew run

Go to http: // localhost: 8080 / book / 1.

$ curl -X GET  http://localhost:8080/book/1
{"id":1,"name":"Test book 1","publisher":"Test publisher A"}

I was able to get the data from the database.

reference

Access a database with MyBatis

Recommended Posts

Try to get data from database using MyBatis in Micronaut + Kotlin project
[Java] Get data from DB using singleton service in Spring (Boot)
How to get and add data from Firebase Firestore in Ruby
How to get date data in Ruby
Try to solve Project Euler in Java
[Kotlin] 3 ways to get Class from KClass
[IOS] How to get data from DynamoDB
Try to issue or get a card from Jave to Trello using API
How to get the setting value (property value) from the database in Spring Framework
[Java] Get KFunction from Method / Constructor in Java [Kotlin]
Java code sample to acquire and display DBLINK source and destination data in Oracle Database using DBLINK
Get weather forecasts from Watson Weather Company Data in simple Java
[IOS14] How to get Data type image data directly from PHPickerViewController?
About smart cast in callback from Fragment to Activity in Kotlin
I tried to create an API to get data from a spreadsheet in Ruby (with service account)
Moved from iBATIS to MyBatis3
Try using RocksDB in Java
Try Spring Boot from 0 to 100.
[Java] Get KClass in Java [Kotlin]
Try using gRPC in Ruby
I can't log in to MySQL from Django when using docker-compose
How to delete only specific data from data created using Form object
Unable to get resources when using modules in Gradle and IntelliJ
How to get the id of PRIMAY KEY auto_incremented in MyBatis
Try passing values from Java Servlet to iPhone app using JSON
From "Create mvn project" to "Create remote repository in GitLab" to "git init"
[Android] Convert Map to JSON using GSON in Kotlin and Java
[Android] I want to get the listener from the button in ListView