• 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 += "
    " + treeLevel(children, "	");
            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() + ")
    ";
                List<Dept> subChildren = dept.getChildren();
                if (subChildren != null && subChildren.size() > 0) {
                    levelChar = "	" + 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, "	");
            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 = "	";
            for (int i = parents.size() - 1; i >= 0; i--) {
                trees += LevelChar + parents.get(i).getName() + "(" + parents.get(i).getId() + ")" + "
    ";
                LevelChar += "	";
            }
    
            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();
        }
    }
  • 相关阅读:
    新闻列表中标题和日期的左右分别对齐的几种处理方法
    BFC
    css清除浮动float的几种方法
    git 恢复单个文件
    Git ignore
    198. House Robber(动态规划)
    121. Best Time to Buy and Sell Stock(股票最大收益)
    120. Triangle(动态规划 三角形最小路径 难 想)
    91. Decode Ways(动态规划 26个字母解码个数)
    53. Maximum Subarray(动态规划 求最大子数组)
  • 原文地址:https://www.cnblogs.com/dreamyoung/p/11810921.html
Copyright © 2020-2023  润新知