3.10.11. Integration with MyBatis

MyBatis framework offers wider capabilities for running SQL and mapping query results to objects than ORM native query or QueryRunner.

Follow the steps below to set up integration with MyBatis in a CUBA project.

  1. Create UUID type handler class in the root package of the core module.

    import com.haulmont.cuba.core.global.UuidProvider;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.TypeHandler;

import java.sql.*;

public class UUIDTypeHandler implements TypeHandler {

    @Override
    public void setParameter(PreparedStatement ps, int i, Object parameter, JdbcType jdbcType) throws SQLException {
        ps.setObject(i, parameter, Types.OTHER);
    }

    @Override
    public Object getResult(ResultSet rs, String columnName) throws SQLException {
        String val = rs.getString(columnName);
        if (val != null) {
            return UuidProvider.fromString(val);
        } else {
            return null;
        }
    }

    @Override
    public Object getResult(ResultSet rs, int columnIndex) throws SQLException {
        String val = rs.getString(columnIndex);
        if (val != null) {
            return UuidProvider.fromString(val);
        } else {
            return null;
        }
    }

    @Override
    public Object getResult(CallableStatement cs, int columnIndex) throws SQLException {
        String val = cs.getString(columnIndex);
        if (val != null) {
            return UuidProvider.fromString(val);
        } else {
            return null;
        }
    }
}

  2. Create mybatis.xml configuration file in the core module next to spring.xml with a correct reference to UUIDTypeHandler:

    <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <settings>
        <setting name="lazyLoadingEnabled" value="false"/>
    </settings>
    <typeHandlers>
        <typeHandler javaType="java.util.UUID"
                     handler="com.company.demo.core.UUIDTypeHandler"/>
    </typeHandlers>
</configuration>

  3. Add the following beans into spring.xml file to use MyBatis in the project:

    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
    <property name="dataSource" ref="cubaDataSource"/>
    <property name="configLocation" value="com/company/demo/mybatis.xml"/>
    <property name="mapperLocations" value="com/company/demo/core/sqlmap/*.xml"/>
</bean>

<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
    <property name="basePackage" value="com/company/demo.core.dao"/>
    <property name="sqlSessionFactory" ref="sqlSessionFactory"/>
</bean>

<bean id="sqlSession" class="org.mybatis.spring.SqlSessionTemplate">
    <constructor-arg index="0" ref="sqlSessionFactory" />
</bean>

    The sqlSessionFactory bean provides the reference to the created mybatis.xml.

    The MapperLocations parameter defines a path to mapperLocations mapping files (according to the rules of ResourceLoader Spring interface).

  4. Finally, add MyBatis dependencies to the core module in build.gradle:

    compile('org.mybatis:mybatis:3.2.8')
compile('org.mybatis:mybatis-spring:1.2.5')

Below is an example of a mapping file for loading an instance of Order together with a related Customer and a collection of Order items:

<?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="com.sample.sales">

    <select id="selectOrder" resultMap="orderResultMap">
        select
        o.ID as order_id,
        o.DATE as order_date,
        o.AMOUNT as order_amount,
        c.ID as customer_id,
        c.NAME as customer_name,
        c.EMAIL as customer_email,
        i.ID as item_id,
        i.QUANTITY as item_quantity,
        p.ID as product_id,
        p.NAME as product_name
        from
        SALES_ORDER o
        left join SALES_CUSTOMER c on c.ID = o.CUSTOMER_ID
        left join SALES_ITEM i on i.ORDER_ID = o.id and i.DELETE_TS is null
        left join SALES_PRODUCT p on p.ID = i.PRODUCT_ID
        where
        c.id = #{id}
    </select>

    <resultMap id="orderResultMap" type="com.sample.sales.entity.Order">
        <id property="id" column="order_id"/>
        <result property="date" column="order_date"/>
        <result property="amount" column="order_amount"/>

        <association property="customer" column="customer_id" javaType="com.sample.sales.entity.Customer">
            <id property="id" column="customer_id"/>
            <result property="name" column="customer_name"/>
            <result property="email" column="customer_email"/>
        </association>

        <collection property="items" ofType="com.sample.sales.entity.Item">
            <id property="id" column="item_id"/>
            <result property="quantity" column="item_quantity"/>
            <association property="product" column="product_id" javaType="com.sample.sales.entity.Product">
                <id property="id" column="product_id"/>
                <result property="name" column="product_name"/>
            </association>
        </collection>
    </resultMap>

</mapper>

The following code can be used to retrieve query results from the example above:

try (Transaction tx = persistence.createTransaction()) {
    SqlSession sqlSession = AppBeans.get("sqlSession");
    Order order = (Order) sqlSession.selectOne("com.sample.sales.selectOrder", orderId);
    tx.commit();
}