4.8.13. Integration with MyBatis

The platform includes MyBatis framework, which offers wider capabilities for running SQL and mapping query results to objects compared to ORM native query or QueryRunner.

The following beans must be added into spring.xml file of the core module to use MyBatis in the project:

<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
  <property name="dataSource" ref="dataSource"/>
  <property name="configLocation" value="cuba-mybatis.xml"/>
  <property name="mapperLocations" value="classpath*:com/sample/sales/core/sqlmap/*.xml"/>
</bean>

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

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

Below is the 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:

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