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 11+ 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:3.14.1:create \
    -DprojectGroupId=org.acme \
    -DprojectArtifactId=mybatis-quickstart \
    -Dextensions="resteasy-jackson,jdbc-mysql,io.quarkiverse.mybatis:quarkus-mybatis:2.2.4" \
    -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>2.2.4</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)
}

Get a user from the database.
Insert a user into the database. We should use the @Param to bind the parameters.
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 jakarta.inject.Inject;
import jakarta.ws.rs.Consumes;
import jakarta.ws.rs.DELETE;
import jakarta.ws.rs.FormParam;
import jakarta.ws.rs.GET;
import jakarta.ws.rs.POST;
import jakarta.ws.rs.Path;
import jakarta.ws.rs.PathParam;
import jakarta.ws.rs.Produces;
import jakarta.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);
    }
}

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)

The datasource used by the mybatis to connect the database.
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 QUARKUS 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
`quarkus.mybatis.xmlconfig.enable` enable mybatis xml configuration	boolean	`false`
`quarkus.mybatis.xmlconfig.path` xml configuration file	string	`mybatis-config.xml`
`quarkus.mybatis.environment` MyBatis environment id	string	`quarkus`
`quarkus.mybatis.transaction-factory` MyBatis transaction factory	string	`MANAGED`
`quarkus.mybatis.datasource` MyBatis data source	string
`quarkus.mybatis.initial-sql` MyBatis initial sql	string
`quarkus.mybatis.cache-enabled` MyBatis cacheEnabled	boolean	`true`
`quarkus.mybatis.lazy-loading-enabled` MyBatis lazyLoadingEnabled	boolean	`false`
`quarkus.mybatis.aggressive-lazy-loading` MyBatis aggressiveLazyLoading	boolean	`false`
`quarkus.mybatis.use-column-label` MyBatis useColumnLabel	boolean	`true`
`quarkus.mybatis.use-generated-keys` MyBatis useGeneratedKeys	boolean	`false`
`quarkus.mybatis.auto-mapping-behavior` MyBatis autoMappingBehavior	`none`, `partial`, `full`	`partial`
`quarkus.mybatis.auto-mapping-unknown-column-behavior` MyBatis autoMappingUnknownColumnBehavior	`none`, `warning`, `failing`	`none`
`quarkus.mybatis.default-executor-type` MyBatis defaultExecutorType	`simple`, `reuse`, `batch`	`simple`
`quarkus.mybatis.default-statement-timeout` MyBatis defaultStatementTimeout	int
`quarkus.mybatis.default-fetch-size` MyBatis defaultFetchSize	int
`quarkus.mybatis.default-result-set-type` MyBatis defaultResultSetType	`default`, `forward-only`, `scroll-insensitive`, `scroll-sensitive`
`quarkus.mybatis.safe-row-bounds-enabled` MyBatis safeRowBoundsEnabled	boolean	`false`
`quarkus.mybatis.safe-result-handler-enabled` MyBatis safeResultHandlerEnabled	boolean	`true`
`quarkus.mybatis.map-underscore-to-camel-case` MyBatis mapUnderscoreToCamelCase	boolean	`false`
`quarkus.mybatis.multiple-result-sets-enabled` MyBatis multipleResultSetsEnabled	boolean	`true`
`quarkus.mybatis.local-cache-scope` MyBatis localCacheScope	`session`, `statement`	`session`
`quarkus.mybatis.jdbc-type-for-null` 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`
`quarkus.mybatis.lazy-load-trigger-methods` MyBatis lazyLoadTriggerMethods	list of string	`equals,clone,hashCode,toString`
`quarkus.mybatis.default-scripting-language` MyBatis defaultScriptingLanguage	string	`org.apache.ibatis.scripting.xmltags.XMLLanguageDriver`
`quarkus.mybatis.default-enum-type-handler` MyBatis defaultEnumTypeHandler	string	`org.apache.ibatis.type.EnumTypeHandler`
`quarkus.mybatis.call-setters-on-nulls` MyBatis callSettersOnNulls	boolean	`false`
`quarkus.mybatis.return-instance-for-empty-row` MyBatis returnInstanceForEmptyRow	boolean	`false`
`quarkus.mybatis.log-prefix` MyBatis logPrefix	string
`quarkus.mybatis.log-impl` MyBatis logImpl	string
`quarkus.mybatis.proxy-factory` MyBatis proxyFactory	string	`JAVASSIST`
`quarkus.mybatis.vfs-impl` MyBatis vfsImpl	string
`quarkus.mybatis.use-actual-param-name` MyBatis useActualParamName	boolean	`true`
`quarkus.mybatis.configuration-factory` MyBatis configurationFactory	string
`quarkus.mybatis.shrink-whitespaces-in-sql` MyBatis shrinkWhitespacesInSql	boolean	`false`
`quarkus.mybatis.default-sql-provider-type` MyBatis defaultSqlProviderType	string
`quarkus.mybatis.mapper-locations` MyBatis mapperLocations	list of string
Data sources config	Type	Default
`quarkus.mybatis."data-source-name".environment` MyBatis environment id	string
`quarkus.mybatis."data-source-name".transaction-factory` MyBatis transaction factory	string
`quarkus.mybatis."data-source-name".initial-sql` MyBatis initial sql	string
`quarkus.mybatis."data-source-name".cache-enabled` MyBatis cacheEnabled	boolean
`quarkus.mybatis."data-source-name".lazy-loading-enabled` MyBatis lazyLoadingEnabled	boolean
`quarkus.mybatis."data-source-name".aggressive-lazy-loading` MyBatis aggressiveLazyLoading	boolean
`quarkus.mybatis."data-source-name".use-column-label` MyBatis useColumnLabel	boolean
`quarkus.mybatis."data-source-name".use-generated-keys` MyBatis useGeneratedKeys	boolean
`quarkus.mybatis."data-source-name".auto-mapping-behavior` MyBatis autoMappingBehavior	`none`, `partial`, `full`
`quarkus.mybatis."data-source-name".auto-mapping-unknown-column-behavior` MyBatis autoMappingUnknownColumnBehavior	`none`, `warning`, `failing`
`quarkus.mybatis."data-source-name".default-executor-type` MyBatis defaultExecutorType	`simple`, `reuse`, `batch`
`quarkus.mybatis."data-source-name".default-statement-timeout` MyBatis defaultStatementTimeout	int
`quarkus.mybatis."data-source-name".default-fetch-size` MyBatis defaultFetchSize	int
`quarkus.mybatis."data-source-name".default-result-set-type` MyBatis defaultResultSetType	`default`, `forward-only`, `scroll-insensitive`, `scroll-sensitive`
`quarkus.mybatis."data-source-name".safe-row-bounds-enabled` MyBatis safeRowBoundsEnabled	boolean
`quarkus.mybatis."data-source-name".safe-result-handler-enabled` MyBatis safeResultHandlerEnabled	boolean
`quarkus.mybatis."data-source-name".map-underscore-to-camel-case` MyBatis mapUnderscoreToCamelCase	boolean
`quarkus.mybatis."data-source-name".multiple-result-sets-enabled` MyBatis multipleResultSetsEnabled	boolean
`quarkus.mybatis."data-source-name".local-cache-scope` MyBatis localCacheScope	`session`, `statement`
`quarkus.mybatis."data-source-name".jdbc-type-for-null` 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`
`quarkus.mybatis."data-source-name".lazy-load-trigger-methods` MyBatis lazyLoadTriggerMethods	list of string
`quarkus.mybatis."data-source-name".default-scripting-language` MyBatis defaultScriptingLanguage	string
`quarkus.mybatis."data-source-name".default-enum-type-handler` MyBatis defaultEnumTypeHandler	string
`quarkus.mybatis."data-source-name".call-setters-on-nulls` MyBatis callSettersOnNulls	boolean
`quarkus.mybatis."data-source-name".return-instance-for-empty-row` MyBatis returnInstanceForEmptyRow	boolean
`quarkus.mybatis."data-source-name".log-prefix` MyBatis logPrefix	string
`quarkus.mybatis."data-source-name".log-impl` MyBatis logImpl	string
`quarkus.mybatis."data-source-name".proxy-factory` MyBatis proxyFactory	string
`quarkus.mybatis."data-source-name".vfs-impl` MyBatis vfsImpl	string
`quarkus.mybatis."data-source-name".use-actual-param-name` MyBatis useActualParamName	boolean
`quarkus.mybatis."data-source-name".configuration-factory` MyBatis configurationFactory	string
`quarkus.mybatis."data-source-name".shrink-whitespaces-in-sql` MyBatis shrinkWhitespacesInSql	boolean
`quarkus.mybatis."data-source-name".default-sql-provider-type` MyBatis defaultSqlProviderType	string

Configuration property

Type

Default

quarkus.mybatis.xmlconfig.enable

enable mybatis xml configuration

boolean

false

quarkus.mybatis.xmlconfig.path

xml configuration file

string

mybatis-config.xml

quarkus.mybatis.environment

MyBatis environment id

string

quarkus

quarkus.mybatis.transaction-factory

MyBatis transaction factory

string

MANAGED

quarkus.mybatis.datasource

MyBatis data source

string

quarkus.mybatis.initial-sql

MyBatis initial sql

string

quarkus.mybatis.cache-enabled

MyBatis cacheEnabled

boolean

true

quarkus.mybatis.lazy-loading-enabled

MyBatis lazyLoadingEnabled

boolean

false

quarkus.mybatis.aggressive-lazy-loading

MyBatis aggressiveLazyLoading

boolean

false

quarkus.mybatis.use-column-label

MyBatis useColumnLabel

boolean

true

quarkus.mybatis.use-generated-keys

MyBatis useGeneratedKeys

boolean

false

quarkus.mybatis.auto-mapping-behavior

MyBatis autoMappingBehavior

none, partial, full

partial

quarkus.mybatis.auto-mapping-unknown-column-behavior

MyBatis autoMappingUnknownColumnBehavior

none, warning, failing

none

quarkus.mybatis.default-executor-type

MyBatis defaultExecutorType

simple, reuse, batch

simple

quarkus.mybatis.default-statement-timeout

MyBatis defaultStatementTimeout

int

quarkus.mybatis.default-fetch-size

MyBatis defaultFetchSize

int

quarkus.mybatis.default-result-set-type

MyBatis defaultResultSetType

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

quarkus.mybatis.safe-row-bounds-enabled

MyBatis safeRowBoundsEnabled

boolean

false

quarkus.mybatis.safe-result-handler-enabled

MyBatis safeResultHandlerEnabled

boolean

true

quarkus.mybatis.map-underscore-to-camel-case

MyBatis mapUnderscoreToCamelCase

boolean

false

quarkus.mybatis.multiple-result-sets-enabled

MyBatis multipleResultSetsEnabled

boolean

true

quarkus.mybatis.local-cache-scope

MyBatis localCacheScope

session, statement

session

quarkus.mybatis.jdbc-type-for-null

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