3.9.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.
-
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; } } }
-
Create
mybatis.xml
configuration file in the core module next to spring.xml with a correct reference toUUIDTypeHandler
:<!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>
-
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 createdmybatis.xml
.The
MapperLocations
parameter defines a path tomapperLocations
mapping files (according to the rules ofResourceLoader
Spring interface). -
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();
}