5.9.11. 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="cubaDataSource"/>
<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();
}