sql语句组织父子级带children

tech2022-08-18  128

model:

@Data @ToString @Entity @Table(name="teachplan") @GenericGenerator(name = "jpa-uuid", strategy = "uuid") public class Teachplan implements Serializable { private static final long serialVersionUID = -916357110051689485L; @Id @GeneratedValue(generator = "jpa-uuid") @Column(length = 32) private String id; private String pname; private String parentid; private String grade; private String ptype; private String description; private String courseid; private String status; private Integer orderby; private Double timelength; private String trylearn; } @Data @ToString public class TeachplanNode extends Teachplan { List<TeachplanNode> children; }

sql:

<?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.xuecheng.manage_course.dao.TeachplanMapper"> <resultMap id="teachplanMap" type="com.xuecheng.framework.domain.course.ext.TeachplanNode"> <id column="one_id" property="id"></id> <result column="one_pname" property="pname"></result> <collection property="children" ofType="com.xuecheng.framework.domain.course.ext.TeachplanNode"> <id column="two_id" property="id"></id> <result column="two_pname" property="pname"></result> <collection property="children" ofType="com.xuecheng.framework.domain.course.ext.TeachplanNode"> <id column="three_id" property="id"></id> <result column="three_pname" property="pname"></result> </collection> </collection> </resultMap> <select id="selectList" parameterType="java.lang.String" resultMap="teachplanMap"> SELECT a.id one_id, a.pname one_pname, b.id two_id, b.pname two_pname, c.id three_id, c.pname three_pname FROM teachplan a LEFT JOIN teachplan b ON b.parentid = a.id LEFT JOIN teachplan c ON c.parentid = b.id WHERE a.parentid = '0' <if test="_parameter !=null and _parameter!=''"> AND a.courseid = #{courseId} </if> ORDER BY a.orderby, b.orderby, c.orderby </select> </mapper>

 

最新回复(0)