Mybatis-Plus使用总结

1、忽略数据库字段注解

@TableField(exist = false)

2、Mybatis通过colliection属性递归获取菜单树

public class Department extends BaseEntity {
    private Long id;
    private String name;
    private Integer parentId;
    private List<Department> children;
}

<?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.tumake.user.mapper.DepartmentMapper">
    <cache-ref namespace="com.tumake.user.mapper.DepartmentMapper" />

    <resultMap id="deptTree" type="com.tumake.user.entity.Department">
        <id column="id" property="id" javaType="java.lang.Long" />
        <result column="name" property="name" javaType="java.lang.String" />
        <result column="parentId" property="parentId" javaType="java.lang.Integer" />
        <!--id作为参数递归调用getDeptTree，查询子节点-->
        <collection property="children" column="id" select="selectDeptTreeByParentId" ofType="com.tumake.user.entity.Department"></collection>
    </resultMap>

    <!--根据父id获取子节点-->
    <select id="selectDeptTreeByParentId" resultMap="deptTree">
        select id,`name`,parentId from Department where parentId=#{id}
    </select>
</mapper>

@CacheNamespace(implementation = MybatisPlusRedisCache.class, eviction = MybatisPlusRedisCache.class)
public interface DepartmentMapper extends BaseMapper<Department> {

    List<Department> selectDeptTreeByParentId(@Param("id") Integer parentId);
}

3、由于生成表采用的JPA，所以需要“关闭驼峰转换”和“表名注解”
不然提示错误Cause: java.sql.SQLSyntaxErrorException: Table 'tumake.department' doesn't exist

mybatis-plus: #Mybatis-plus配置
  configuration: #原生MyBatis所支持的配置
    map-underscore-to-camel-case: false #不启用驼峰命名规则

@TableName("Department")
@Entity
public class Department extends BaseEntity {

}

@Test
// 测试回滚数据，不做真实插入
@Transactional
void contextLoads(){
    Department department=new Department();
    department.setId(4L);
    department.setName("测试");
    department.setParentId(1);
    department.setStatus(true);
    departmentMapper.insert(department);
}

4、Mybatis-plus orderBy多字段排序，null值排最后

// 不能使用last拼接排序，有SQL注入风险
if (sortDirection.replace("ending", "").equals("desc")) {
    userQueryWrapper.orderByDesc(sortField);
    //userQueryWrapper.orderByAsc(sortField + " is NULL").orderByDesc(sortField);
} else {
    userQueryWrapper.orderByAsc(sortField + " is NULL," + sortField);
    //userQueryWrapper.orderBy(true, true, Arrays.asList(new String[]{sortField + " is NULL", sortField}));
}

5、根据部门ID查询该部门文章类别的父子节点

//Mapper接口
List<CategoryVO> selectCategoryTreeByDeptId(Map<String, Object> map);

//服务调用方法
public List<CategoryVO> selectCategoryTreeByDeptId(Long deptId) {
    Map<String,Object> map = new HashMap<>();
    map.put("deptId", deptId);
    return categoryMapper.selectCategoryTreeByDeptId(map);
}

<!--文章类别选择-->
<resultMap id="categoryTreeByDeptid" type="com.tumake.user.vo.CategoryVO">
    <id column="id" property="id" javaType="java.lang.Long" />
    <result column="name" property="name" javaType="java.lang.String" />
    <!--id作为参数递归调用selectCategoryTreeByDeptId，查询子节点，column为Map型-->
    <collection property="children" column="{id=id}" select="selectCategoryTreeByDeptId" ofType="com.tumake.user.vo.CategoryVO"></collection>
</resultMap>

<!--先根据部门ID查询类别父级节点，然后递归查询子节点，注意这里参数类型为Map-->
<select id="selectCategoryTreeByDeptId" resultMap="categoryTreeByDeptid" parameterType="java.util.Map">
    select id,name from Category  where 1=1
    <if test="deptId != null and deptId > 0">
        and departmentId=#{deptId}
    </if>
    <if test="id != null and id > 0">
        and parentId=#{id}
    </if>
    order by ISNULL(sortNumber),sortNumber asc,id desc
</select>

6、批量插入数据

// 只有继承了 MP 框架中的 IService 接口的实现方法才具有saveBatch方法
public interface UserService extends IService<User> {
}
@Service
public class UserServiceImpl extends ServiceImpl<UserMapper,User> implements UserService {
}

public boolean saveBatch() {
    List<User> list = new ArrayList<>();
    // 待添加（用户）数据
    for (int i = 0; i < 1000; i++) {
        User user = new User();
        user.setName("test:"+i);
        user.setPassword("123456");
        list.add(user);
    }
    // 批量插入
    return userService.saveBatch(list);
}