MyBatis常用功能介绍

为了更加方便开发，这里介绍MyBatis常见的使用说明

• 类型转换
• 主键自增
• 模糊查询
• 分页处理
• 排序

xml特殊字符转义

• & -> &
• < -> <
• > -> >
• " -> "
• ' -> '

日志中输出sql执行日志

在每个数据源上配置isLog

wueasy:
  datasource :
    demo :
      isLog: true

类型转换

通过指定typeHandler来进行参数值类型转换

支持以下几种类型转换：

• LongTypeHandler
• DoubleTypeHandler
• FloatTypeHandler
• IntegerTypeHandler
• ByteTypeHandler
• ShortTypeHandler
• BigDecimalTypeHandler

<update id="updateState">
  update sys_user
  set state = #{state}
  where user_id = #{userId,typeHandler=LongTypeHandler}
</update>

主键自增

<insert id="insert" useGeneratedKeys="true" keyProperty="id">
</insert>

模糊查询

<select id="queryPage" resultMap="BaseResultMap">

  select * from sys_user t where 1=1
   <if test="username != null and username != ''">
        <bind name="pattern_username" value="'%' + _parameter.get('username') + '%'" />
       and username  LIKE #{pattern_username}
   </if>
   order by user_id desc
</select>

分页功能

接口实现分页处理

@Service("DemoService")
public class DemoServiceImpl implements DemoService {

    @Autowired
    private DemoMapper demoMapper;

    @Override
    public Page<Demo> queryPage(DataMap paramMap) {

        //启用分页
        PageHelper.startPage(PageHelper.getPageNum(paramMap), PageHelper.getPageSize(paramMap));
        //查询数据
        List<Demo> list = demoMapper.query(paramMap);

        //获取page对象
        return PageHelper.getPage(list);
    }
}

批量插入

<insert id="insertBatch">
  insert into demo (name)
  values 
  <foreach collection ="list" item="item" separator =",">
    (#{item.name})
  </foreach>
</insert>

批量删除

<delete id="deleteMultiple">
  delete from demo
  where
  <bind name="ids" value="_parameter.get('id').split(',')" />

  <foreach collection="ids" item="item" index="index" open="" close="" separator="OR">
    id = #{item,typeHandler=LongTypeHandler}
  </foreach>
</delete>

sql排序

• sortField：需要排序的字段
• sortSort：排序方式，asc或desc

<select id="queryPage" resultMap="BaseResultMap">
   select * from demo
   <choose>
       <when test="sortField != null and sortField != '' and sortSort != null and sortSort != ''">
           order by ${sortField} ${sortSort}
       </when>
       <otherwise>
           order by id desc
       </otherwise>
   </choose>
</select>