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(); } }