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