Quarkus - Using MyBatis

This guide demonstrates how your Quarkus application can use MyBatis to support custom SQL, stored procedures and advanced mappings.

Prerequisites

To complete this guide, you need:

  • less than 15 minutes

  • an IDE

  • JDK 1.8+ installed with JAVA_HOME configured appropriately

  • Apache Maven

  • A running Mysql Database server

  • GraalVM, or Docker, installed if you want to run in native mode.

Architecture

The application built in this guide is quite simple: the user can get, add and remove a record through the RESTful API by using the MyBatis Mapper.

Solution

We recommend that you follow the instructions in the next sections and create the application step by step.

Creating the Maven Project

First, we need a new project. Create a new project with the following command:

mvn io.quarkus:quarkus-maven-plugin:2.16.12.Final:create \
    -DprojectGroupId=org.acme \
    -DprojectArtifactId=mybatis-quickstart \
    -Dextensions="resteasy-jackson,jdbc-mysql,io.quarkiverse.mybatis:quarkus-mybatis:1.0.9" \
    -DnoExamples
cd mybatis-quickstart

Maven 3.6.2+ is required for this to work.

This command generates a Maven project, with its pom.xml importing the quarkus-mybatis extension.

If you already have your Quarkus project configured, you can add the quarkus-mybatis extension to your project by adding the following dependency in your pom.xml:

<dependency>
    <groupId>io.quarkiverse.mybatis</groupId>
    <artifactId>quarkus-mybatis</artifactId>
    <version>1.0.9</version>
</dependency>

Creating the User POJO

We are going to create a User POJO to access to the data in the backend mysql server. Create the src/main/java/org/acme/mybatis/User.java file, with the following content:

package org.acme.mybatis;

public class User {
    private Integer id;
    private String name;

    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;
    }
}

Creating the User Mapper

We are going to create a UserMapper class which will use the MyBatis annotations to inject the SQL. Create the src/main/java/org/acme/mybatis/UserMapper.java file, with the following content:

package org.acme.mybatis;

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;

@Mapper
public interface UserMapper {

    @Select("SELECT * FROM USERS WHERE id = #{id}")
    User getUser(Integer id); (1)

    @Insert("INSERT INTO USERS (id, name) VALUES (#{id}, #{name})")
    Integer createUser(@Param("id") Integer id, @Param("name") String name); (2)

    @Delete("DELETE FROM USERS WHERE id = #{id}")
    Integer removeUser(Integer id); (3)
}
  1. Get a user from the database.

  2. Insert a user into the database. We should use the @Param to bind the parameters.

  3. Delete a user from the databse.

Creating the MyBatisResource to handle the requests

We are going to create a MyBatisResource class which will handle all the requests to create, query or remove the data from the database.

package org.acme.mybatis;

import javax.inject.Inject;
import javax.ws.rs.Consumes;
import javax.ws.rs.DELETE;
import javax.ws.rs.FormParam;
import javax.ws.rs.GET;
import javax.ws.rs.POST;
import javax.ws.rs.Path;
import javax.ws.rs.PathParam;
import javax.ws.rs.Produces;
import javax.ws.rs.core.MediaType;

@Path("/mybatis")
public class MyBatisResource {

    @Inject
    UserMapper userMapper; (1)

    @Path("/user/{id}")
    @GET
    @Produces(MediaType.APPLICATION_JSON)
    public User getUser(@PathParam("id") Integer id) {
        return userMapper.getUser(id);
    }

    @Path("/user")
    @POST
    @Produces(MediaType.TEXT_PLAIN)
    @Consumes(MediaType.APPLICATION_FORM_URLENCODED)
    public Integer createUser(@FormParam("id") Integer id, @FormParam("name") String name) {
        return userMapper.createUser(id, name);
    }

    @Path("/user/{id}")
    @DELETE
    @Produces(MediaType.TEXT_PLAIN)
    public Integer removeUser(@PathParam("id") Integer id) {
        return userMapper.removeUser(id);
    }
}
  1. It uses the UserMapper which should be injected by the Quarkus to access the database.

Configure the properties

We need to config the datasource used to connect to the database and the mybatis will choose the default one. Also you can use quarkus.mybatis.datasource for the specific database.

quarkus.datasource.db-kind=mysql
quarkus.datasource.username=<db user>

quarkus.datasource.jdbc.url=jdbc:mysql://localhost/test  (1)
quarkus.mybatis.initial-sql=insert.sql  (2)
  1. The datasource used by the mybatis to connect the database.

  2. The SQL file which should be executed just after the application is started.

We could keep the following content in the insert.sql to add some data:

DROP TABLE IF EXISTS USERS;

CREATE TABLE USERS (
    id integer not null primary key,
    name varchar(80) not null
);

INSERT INTO USERS (id, name) values(1, 'Test User1');
INSERT INTO USERS (id, name) values(2, 'Test User2');
INSERT INTO USERS (id, name) values(3, 'Test User3');

Running with the JVM mode

At first, you should make sure the Mysql Server is running and the test database has been created. Then, you just need to run:

./mvnw compile quarkus:dev

You can get the user by using the following command:

curl http://localhost:8080/mybatis/user/1

Or create a new user:

curl -X POST http://localhost:8080/mybatis/user -d 'id=4&name=test'

Or remove a user:

curl -X DELETE http://localhost:8080/mybatis/user/1

Running Native

You can build the native executable with:

./mvnw package -Pnative

and then run with:

./target/mybatis-quickstart-1.0-SNAPSHOT-runner

Support for multiple data sources

You can choose a specific data source for MyBatis mapper using the annotation @MapperDataSource. Query in the next example will be running against data source with name user. If there is no such annotation on the mapper, the default data source will be used.

@Mapper
@MapperDataSource("user")
public interface UserMapper {
  @Select("SELECT * FROM USERS WHERE id = #{id}")
  User getUser(Integer id);
}

CDI integration

Injects default session factory:

@Inject
SqlSessionFactory sqlSessionFactory;

Injects session factory for data source user:

@Named("user")
SqlSessionFactory sqlSessionFactory;

XML Configuration Support

You need to set quarkus.mybatis.xmlconfig.enable to true and set quarkus.mybatis.xmlconfig.path to the mybatis xml configuration which the default value is mybatis-config.xml. Aslo quarkus.mybatis.environment is relevant to the element of environment in the xml configuration file.

# H2
quarkus.datasource.h2.db-kind=h2
quarkus.datasource.h2.username=sa
quarkus.datasource.h2.password=sa
quarkus.datasource.h2.jdbc.url=jdbc:h2:tcp://localhost/mem:default

# MyBatis
quarkus.mybatis.xmlconfig.enable=true
quarkus.mybatis.xmlconfig.path=mybatis-config.xml
quarkus.mybatis.environment=development
quarkus.mybatis.h2.initial-sql=insert-h2.sql
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">

<configuration>
    <typeHandlers>
        <package name="io.quarkiverse.it.mybatis"/>
    </typeHandlers>

    <!-- setup environment with Quarkus data source -->
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="QUARKUS">
                <property name="db" value="h2"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <package name="io.quarkiverse.it.mybatis"/>
    </mappers>
</configuration>

The dataSource type must be QARKUS and the property db could be specified to the datasource name in the quarkus configuration.

Configuration References

Configuration property fixed at build time - All other configuration properties are overridable at runtime

Configuration property

Type

Default

enable mybatis xml configuration

boolean

false

xml configuration file

string

mybatis-config.xml

MyBatis environment id

string

quarkus

MyBatis transaction factory

string

MANAGED

MyBatis data source

string

MyBatis initial sql

string

MyBatis cacheEnabled

boolean

true

MyBatis lazyLoadingEnabled

boolean

false

MyBatis aggressiveLazyLoading

boolean

false

MyBatis useColumnLabel

boolean

true

MyBatis useGeneratedKeys

boolean

false

MyBatis autoMappingBehavior

none, partial, full

partial

MyBatis autoMappingUnknownColumnBehavior

none, warning, failing

none

MyBatis defaultExecutorType

simple, reuse, batch

simple

MyBatis defaultStatementTimeout

int

MyBatis defaultFetchSize

int

MyBatis defaultResultSetType

default, forward-only, scroll-insensitive, scroll-sensitive

MyBatis safeRowBoundsEnabled

boolean

false

MyBatis safeResultHandlerEnabled

boolean

true

MyBatis mapUnderscoreToCamelCase

boolean

false

MyBatis multipleResultSetsEnabled

boolean

true

MyBatis localCacheScope

session, statement

session

MyBatis jdbcTypeForNull

array, bit, tinyint, smallint, integer, bigint, float, real, double, numeric, decimal, char, varchar, longvarchar, date, time, timestamp, binary, varbinary, longvarbinary, null, other, blob, clob, boolean, cursor, undefined, nvarchar, nchar, nclob, struct, java-object, distinct, ref, datalink, rowid, longnvarchar, sqlxml, datetimeoffset, time-with-timezone, timestamp-with-timezone

other

MyBatis lazyLoadTriggerMethods

list of string

equals,clone,hashCode,toString

MyBatis defaultScriptingLanguage

string

org.apache.ibatis.scripting.xmltags.XMLLanguageDriver

MyBatis defaultEnumTypeHandler

string

org.apache.ibatis.type.EnumTypeHandler

MyBatis callSettersOnNulls

boolean

false

MyBatis returnInstanceForEmptyRow

boolean

false

MyBatis logPrefix

string

MyBatis logImpl

string

MyBatis proxyFactory

string

JAVASSIST

MyBatis vfsImpl

string

MyBatis useActualParamName

boolean

true

MyBatis configurationFactory

string

MyBatis shrinkWhitespacesInSql

boolean

false

MyBatis defaultSqlProviderType

string

MyBatis mapperLocations

list of string

Data sources config

Type

Default

string

string

string

boolean

boolean

boolean

boolean

boolean

none, partial, full

none, warning, failing

simple, reuse, batch

int

int

default, forward-only, scroll-insensitive, scroll-sensitive

boolean

boolean

boolean

boolean

session, statement

array, bit, tinyint, smallint, integer, bigint, float, real, double, numeric, decimal, char, varchar, longvarchar, date, time, timestamp, binary, varbinary, longvarbinary, null, other, blob, clob, boolean, cursor, undefined, nvarchar, nchar, nclob, struct, java-object, distinct, ref, datalink, rowid, longnvarchar, sqlxml, datetimeoffset, time-with-timezone, timestamp-with-timezone

list of string

string

string

boolean

boolean

string

string

string

string

boolean

string

boolean

string