Mybatis中使用association及collection进行自关联示例（含XML版与注解版）

XML版本：

实体类：

@Data
@ToString
@NoArgsConstructor
public class Dept {
    private Integer id;
    private String name;
    private List<Dept> children = new ArrayList<Dept>();
   private Dept parent;

    public Dept(Integer id) {
        this.id = id;
    }

    public Dept(String name) {
        this.name = name;
    }

    public Dept(String name, Integer parentId) {
        this.name = name;
        this.parent = new Dept(parentId);
    }

    public String toLazyString() {
        return "Dept:{id: " + this.id + " ; name: " + this.name + "}";
    }
}

 

Mapper接口：

public interface DeptMapper {
    public Dept selectById(Integer id);
    public int insertDept(Dept dept);
    public int updateDept(Dept dept);
    public int deleteDept(Dept dept);
    public List<Dept> selectByParentId(Integer parentId);
}

 

Mapper映射文件：

<?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.sunwii.mybatis.mapper.DeptMapper">
    <resultMap type="Dept" id="DeptMap_basic">
        <id property="id" column="did" />
        <result property="name" column="name" />
    </resultMap>
    <resultMap type="Dept" id="DeptMap" extends="DeptMap_basic">
        <!-- 多对一关联：使用select引用方式 。association配置先于collection-->
        <association property="parent" column="parent_id" javaType="Dept" select="selectById"></association>
        
         <!-- 一对多关联：使用select引用方式 -->
        <collection property="children" column="did" ofType="Dept" select="selectByParentId" fetchType="lazy">
        </collection>
        
    </resultMap>
    
    <select id="selectById" parameterType="Integer"
        resultMap="DeptMap">
        select id as did, name,parent_id from t_dept d where d.id=#{id}
    </select>
    <select id="selectByParentId" parameterType="Integer"
        resultMap="DeptMap">
        select id as did, name,parent_id from t_dept d where d.parent_id=#{parentId}
    </select>

    <insert id="insertDept" parameterType="Dept" keyColumn="id"
        keyProperty="id" useGeneratedKeys="true">
        insert into t_dept(name,parent_id)
        values(#{name},
        <if test="parent==null">
        0
        </if>
        <if test="parent!=null">
        #{parent.id}
        </if>
        )
    </insert>


    <update id="updateDept" parameterType="Dept">
        update t_dept set
        name=#{name},parent_id=
        <if test="parent==null">
        0
        </if>
        <if test="parent!=null">
        #{parent.id}
        </if>
        where id=#{id}
    </update>

    <delete id="deleteDept" parameterType="Dept">
        delete from t_dept
        where
        id=#{id}
    </delete>
</mapper>

 

Service实现类：

@Service
public class DeptServiceImpl implements DeptService {
    @Autowired
    private DeptMapper deptMapper;

    @Override
    public Dept getDept(Integer id) {
        return deptMapper.selectById(id);
    }

    @Override
    public List<Dept> getDeptByParentId(Integer parentId) {
        return deptMapper.selectByParentId(parentId);
    }
    
    @Override
    @Transactional
    public void insertDept(Dept dept) {
        deptMapper.insertDept(dept);
    }

    @Override
    @Transactional
    public void updateDept(Dept dept) {
        deptMapper.updateDept(dept);
    }

    @Override
    @Transactional
    public void deleteDept(Dept dept) {
        List<Dept> children = this.getDeptByParentId(dept.getId());
        if(children!=null && children.size()>0) {
            for(Dept d : children) {
                //删除所有下级
                deleteDept(d);
            }
        }
        deptMapper.deleteDept(dept);
        
        //测试事务回滚
        //new Integer(0/0);
    }

}

 

测试类：

public class TestSelf2Self {
    private ApplicationContext context = SpringUtil.getContext();
    private DeptService deptService = (DeptService) context.getBean(DeptService.class);

    /**
     * -添加部门
     */
    @Test
    public void testInsert() {
        deptService.insertDept(new Dept("dept-6"));
    }

    /**
     * -添加部门
     */
    @Test
    public void testInsert2() {
        deptService.insertDept(new Dept("dept-7", 2));

    }

    /**
     * -查询指定部门
     */
    @Test
    public void testSelect() {
        int id = 1;
        Dept dept = deptService.getDept(id);
        String trees = dept.getName() + "(" + (dept.getParent() == null ? 0 : dept.getParent().getId()) + "-"
                + dept.getId() + ")";
        List<Dept> children = dept.getChildren();
        trees += "\n" + treeLevel(children, "\t");
        System.out.println(trees);

        /*
        //结果：
        部门-1(0-1)
        部门-2(1-2)
        部门-3(1-3)
            部门-4(3-4)
                部门-5(4-5)
                
        */
    }

    // 子树
    private String treeLevel(List<Dept> children, String levelChar) {
        String trees = "";
        for (Dept dept : children) {
            trees += levelChar + dept.getName() + "(" + (dept.getParent() == null ? 0 : dept.getParent().getId()) + "-"
                    + dept.getId() + ")\n";
            List<Dept> subChildren = dept.getChildren();
            if (subChildren != null && subChildren.size() > 0) {
                levelChar = "\t" + levelChar;
                trees = trees + treeLevel(subChildren, levelChar);
            }
        }
        return trees;
    }

    /**
     * 查询所有下级部门（由于已经配置了一对多的关联，并且有延迟加载方案，其实没有必要再进行下级部门查询，直接用getChildren()就可以的啦，会自动进行查询）
     */
    @Test
    public void testSelectByParent() {
        int parentId = 1;
        //List<Dept> children = deptService.getDeptByParentId(parentId);
        
        Dept dept = deptService.getDept(parentId); //实际中，要查询下级的当前部门是已经存在的，只是由于延迟加载，没有加载子级
        List<Dept> children = dept.getChildren(); //触发加载，执行SQL
        
        String trees = treeLevel(children, "\t");
        System.out.println(trees);
        
        /*
        //结果：
        部门-2(1-2)
        部门-3(1-3)
            部门-4(3-4)
                部门-5(4-5)
        */
    }

    /**
     * 查询所有上级部门（由于已经配置了一对多的关联（可能设置有延迟加载），其实没有必要再进行上级部门的查询，直接用getParent()就可以的啦，会自动进行查询）
     */
    @Test
    public void testSelectParents() {
        int id = 4;
        Dept dept = deptService.getDept(id);
        List<Dept> parents = new ArrayList<Dept>();
        parents.add(dept);
        while (dept.getParent() != null && dept.getParent().getId() > 0) {
            parents.add(dept.getParent());
            dept = dept.getParent();
        }

        String trees = "";
        String LevelChar = "\t";
        for (int i = parents.size() - 1; i >= 0; i--) {
            trees += LevelChar + parents.get(i).getName() + "(" + parents.get(i).getId() + ")" + "\n";
            LevelChar += "\t";
        }

        System.out.println(trees);
        
        //结果：
        /*
        部门-1(1)
            部门-3(3)
                部门-4(4)
        */
    }

    /**
     * 更新部门
     */
    @Test
    public void testUpdate() {
        int id = 6;
        Dept dept = deptService.getDept(id);
        dept.setName("dept-six");
        dept.setParent(new Dept(3));
        deptService.updateDept(dept);
    }

    /**
     * 删除部门（级联删除所有下级部门）
     */
    @Test
    public void testDelete() {
        int id = 3;
        deptService.deleteDept(new Dept(3));
    }
}

 

注解版：

注解版本只是将Mapper映射文件去掉，将映射注解到Mapper接口中（并使用了动态sql提供器），其它东西不变。

Mapper接口（注解版）：

 

public interface DeptMapper {
    @Select("select id as did, name, parent_id from t_dept d where d.id=#{id}")
    @Results(id="DeptMap", value= {
            @Result(property = "id", column = "did"),
            @Result(property = "name", column = "name"),
            @Result(property = "parent", column = "parent_id", one=@One(
                    select = "selectById",
                    fetchType = FetchType.LAZY
            )),
            @Result(property = "children", column = "did", many=@Many(
                    select = "selectByParentId",
                    fetchType = FetchType.LAZY
            ))
    })
    public Dept selectById(Integer id);

    @InsertProvider(type = DeptProvider.class, method = "insert")
    @Options(keyColumn = "id", keyProperty = "id", useGeneratedKeys = true)
    public int insertDept(Dept dept);

    @UpdateProvider(type = DeptProvider.class, method = "update")
    public int updateDept(Dept dept);

    @Delete("delete from t_dept where id=#{id}")
    public int deleteDept(Dept dept);

    @Select("select id as did, name, parent_id from t_dept d where d.parent_id=#{parentId}")
    @ResultMap("DeptMap")
    public List<Dept> selectByParentId(Integer parentId);
}

 

动态SQL提供器：

public class DeptProvider {
    public String insert(Dept dept) {
        return new SQL() {
            {
                INSERT_INTO("t_dept");
                VALUES("name", "#{name}");
                if (dept.getParent() != null) {
                    VALUES("parent_id", "#{parent.id}");
                } else {
                    VALUES("parent_id", "0");
                }
            }
        }.toString();
    }

    public String update(Dept dept) {
        return new SQL() {
            {
                UPDATE("t_dept");
                SET("name=#{name}");
                if (dept.getParent() != null) {
                    SET("parent_id=#{parent.id}");
                } else {
                    SET("parent_id=0");
                }
                WHERE("id=#{id}");
            }
        }.toString();
    }
}