Integration with MyBatis #1249

Support
#1

Hi
I need to work with a data store and came acrcoss myBatis that looks good to meet what I need.
I have updated spring.xml file as recommented. The next recommended step is to use MapperLocations parameters. Do you have any sample app for this or if you please explain how I can use the following section (from documentation) in my project that will be really appreciated.


<?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();
}
1 Like
#2

Hello Mortoza. I am also using MyBatis in my project. You can have a look at mybatis – MyBatis 3 | Introduction. They have a very comprehensive documentation.

With the mapper.xml files you map the database sql command to CUBA entities. I think, the best approach is to create intefaces beside this xml file (e.g. OrderMapper.xml) that would look for your example like this:


public interface OrderMapper {
    com.sample.sales.entity.Order selectOrder(int orderId);
}

In spring.xml you need to register the sqlSessionFactory bean as described in the CUBA documentation. I needed to also add my datasource (not sure if there is another way to get the CUBA dataSource). In my case it looks like this:


<bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="com.microsoft.sqlserver.jdbc.SQLServerDriver" ></property>
        <property name="url" value="jdbc:sqlserver://localhost:1433;databaseName=XXX" ></property>
        <property name="username" value="XXX" ></property>
        <property name="password" value="XXX" ></property>
        <property name="initialSize" value="10" ></property>
        <property name="maxTotal" value="50" ></property>
    </bean>

    <bean id="myDbSqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource"></property>
        <property name="configLocation" value="classpath:com/XXX/core/dbimport/mybatis-config.xml"></property>
        <property name="mapperLocations" value="classpath*:com/XXX/core/dbimport/mybatismapper/*.xml"></property>
    </bean>
	
	<bean id="myDbSqlSession" class="org.mybatis.spring.SqlSessionTemplate">
        <constructor-arg index="0" ref="myDbSqlSessionFactory" ></constructor>
    </bean>

Now you can get your entities like this:


@Inject
SqlSession myDbSqlSession;
	
protected void loadData()
{
	...
	OrderMapper orderMapper = myDbSqlSession.getMapper(OrderMapper.class);
	com.sample.sales.entity.Order order = orderMapper.selectOrder(id);
	...
}