用mybatis返回树结构数据
最终结果:
-----------------------------------------------------------------------------------------------
目录结构:
-----------------------------------------------------------------------------------------------
班级实体:
public class ScClass extends DataEntity<ScClass> {
private static final long serialVersionUID = 1L;
private String className; //varchar(10) comment '班级名称',
private String classTeacher; //varchar(10) comment '班主任',
private List<ScStudent> studentsList = new ArrayList<ScStudent>();
public ScClass() {
super();
}
public ScClass(String id){
super(id);
}
public String getClassName() {
return className;
}
public void setClassName(String className) {
this.className = className;
}
public String getClassTeacher() {
return classTeacher;
}
public void setClassTeacher(String classTeacher) {
this.classTeacher = classTeacher;
}
public List<ScStudent> getStudentsList() {
return studentsList;
}
public void setStudentsList(List<ScStudent> studentsList) {
this.studentsList = studentsList;
}
}
-----------------------------------------------------------------------------------------------
学生实体:
public class ScStudent extends DataEntity<ScStudent> {
private static final long serialVersionUID = 1L;
private String classId; //varchar(64) comment '所属班级',
private String num; //varchar(3) comment '学号',
private String stuName; //varchar(10) comment '学生姓名',
private int age; //int(3) comment '年龄',
private String sex; //char(1) comment '性别',
public ScStudent() {
super();
}
public ScStudent(String id){
super(id);
}
public String getClassId() {
return classId;
}
public void setClassId(String classId) {
this.classId = classId;
}
public String getNum() {
return num;
}
public void setNum(String num) {
this.num = num;
}
public String getStuName() {
return stuName;
}
public void setStuName(String stuName) {
this.stuName = stuName;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
}
-----------------------------------------------------------------------------------------------
班级VO:
public class ScClassVO extends SerializableVO{
private String scClass;
private String scBzr;
private List<ScStudentVO> userList;
public String getScClass() {
return scClass;
}
public void setScClass(String scClass) {
this.scClass = scClass;
}
public String getScBzr() {
return scBzr;
}
public void setScBzr(String scBzr) {
this.scBzr = scBzr;
}
public List<ScStudentVO> getUserList() {
if (null == userList) {
return Lists.newArrayList();
}
return userList;
}
public void setUserList(List<ScStudentVO> userList) {
this.userList = userList;
}
@Override
public String toString() {
return super.toString();
}
}
-----------------------------------------------------------------------------------------------
学生VO:
public class ScStudentVO extends SerializableVO{
private String stuName;
private String age;
private String num;
private String sex;
public String getStuName() {
return stuName;
}
public void setStuName(String stuName) {
this.stuName = stuName;
}
public String getAge() {
return age;
}
public void setAge(String age) {
this.age = age;
}
public String getNum() {
return num;
}
public void setNum(String num) {
this.num = num;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
@Override
public String toString() {
return super.toString();
}
}
-----------------------------------------------------------------------------------------------
mybatis:
<?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.changbei.jeesite.modules.school.dao.ScClassDao">
<sql id="TABLE_NAME">
sc_class
</sql>
<sql id="JOIN_COLUMNS">
</sql>
<sql id="SELECT_COLUMNS">
a.id AS "id",
a.class_name AS "className",
a.class_teacher AS "classTeacher",
a.create_by AS "createBy.id",
a.create_date AS "createDate",
a.update_by AS "updateBy.id",
a.update_date AS "updateDate",
a.remarks AS "remarks",
a.del_flag AS "delFlag"
</sql>
<sql id="UPDATE_COLUMNS">
class_name = #{className},
class_teacher = #{classTeacher},
update_by = #{updateBy.id},
update_date = #{updateDate},
remarks = #{remarks},
del_flag = #{delFlag}
</sql>
<sql id="INSERT_COLUMNS">
id,
class_name,
class_teacher,
create_by,
create_date,
update_by,
update_date,
remarks,
del_flag
</sql>
<sql id="INSERT_VALUES">
#{id},
#{className},
#{classTeacher},
#{createBy.id},
#{createDate},
#{updateBy.id},
#{updateDate},
#{remarks},
#{delFlag}
</sql>
<select id="get" resultType="ScUser">
SELECT
<include refid="SELECT_COLUMNS"/>
FROM <include refid="TABLE_NAME"/> a
<include refid="JOIN_COLUMNS"/>
<where>
a.id = #{id}
</where>
</select>
<select id="findList" resultType="ScUser">
SELECT
<include refid="SELECT_COLUMNS"/>
FROM <include refid="TABLE_NAME"/> a
<include refid="JOIN_COLUMNS"/>
<where>
a.del_flag = #{DEL_FLAG_NORMAL}
<!--<if test="projectInfo.id != null and projectInfo.id != ''">-->
<!--AND a.prj_detail_id = #{projectDetail.id}-->
<!--</if>-->
</where>
<choose>
<when test="page !=null and page.orderBy != null and page.orderBy != ''">
ORDER BY a.${page.orderBy}
</when>
<otherwise>
ORDER BY a.update_date DESC
</otherwise>
</choose>
</select>
<insert id="insert">
INSERT INTO <include refid="TABLE_NAME"/> (
<include refid="INSERT_COLUMNS"/>
) VALUES (
<include refid="INSERT_VALUES"/>
)
</insert>
<update id="update">
UPDATE <include refid="TABLE_NAME"/> SET
<include refid="UPDATE_COLUMNS"/>
WHERE id = #{id}
</update>
<update id="delete">
UPDATE <include refid="TABLE_NAME"/> SET
del_flag = #{DEL_FLAG_DELETE}
WHERE id = #{id}
</update>
<!--查询树形结构班级学生-->
<!--3,查询部门对应的人员放到第2条里的userList里去 还没做-->
<select id="findScUser" resultType="com.changbei.jeesite.modules.school.vo.ScStudentVO">
SELECT id AS "id" ,stu_name AS "stuName",age AS "age",num AS "num",sex AS "sex"
FROM sc_student
<where>
del_flag='0'
AND class_id = #{classId}
</where>
order by num
</select>
<!--2, 返回的结果;注意type=对应的entiyVO-->
<resultMap id="companyUserDept" type="com.changbei.jeesite.modules.school.vo.ScClassVO">
<id property="id" column="id"/><!--VO对应的id -->
<result property="scClass" column="className"/>
<result property="scBzr" column="classTeacher"/>
<collection property="userList" ofType="ScStudentVO" column="{classId=id}" select="findScUser"/>
<!--VO对应的集合,因为这里的集合要通过子查询set进去,所以select="findUserVODept"连接到上面的子查询语句里去,注意column="{officeId=id}" officeId是用于传递到上面的变量,=id是把下面的结果的id赋给这个变量,这是个查询条件-->
</resultMap>
<!--1,根据当前登陆用户查询当前公司所有部门 resultMap="companyUserDept"是指向上面第2步子查询,注意type="com.changbei.jeesite.modules.v3.vo.DeptVO",必须指向对应的返回vo-->
<select id="findAllLists" resultMap="companyUserDept">
SELECT id as "id",a.class_name AS "scClass",a.class_teacher AS "scBzr"
FROM sc_class a
<where>
a.del_flag='0'
<!--<if test="null != officeId and officeId !=''">
AND o.parent_id=#{officeId}
</if>-->
</where>
order by create_date
</select>
</mapper>
-----------------------------------------------------------------------------------------------
Jsp页面:
<%@ page contentType="text/html;charset=UTF-8" %>
<%@ include file="/WEB-INF/views/include/taglib.jsp" %>
<html>
<head>
<title>sc首页</title>
<meta name="decorator" content="default"/>
<style>
.content {
/*margin-top: 200px;*/
}
</style>
<script type="text/javascript">
$(function () {
$('#check').click(function () {
$.ajax({
url:"/sc/find",
type:"post",
data:{},
success:function (data) {
//循环list中的list
var Temp = ''//先定义一个变量为空
$.each(data, function (i, item) {//对第一层list进行循环
Temp += "<h4> " + item.scClass + " " + item.scBzr + "</h4>\n" +//拿到第一层循环的对象值,循环放入Temp变量中,下面直到循环结束前都是固定内容
" <ol>\n" +
" <table class=\"table table-bordered\">\n" +
" <thead>\n" +
" <tr>\n" +
" <th>姓名</th>\n" +
" <th>学号</th>\n" +
" <th>性别</th>\n" +
" <th>年龄</th>\n" +
" </tr>\n" +
" </thead>\n" +
" <tbody>\n"
$.each(item.userList, function (i, item) {//进行list中包含的list循环
Temp += " <tr>\n" +//拿到第二层循环对象的值以及固定html代码循环放入Temp变量中
" <td>" + item.stuName + "</td>\n" +//拿到list中的list的对象值,进行赋值
" <td>" + item.num + "</td>\n" +
" <td>" + item.sex + "</td>\n" +
" <td>" + item.age + "</td>\n" +
" </tr>\n"
})
Temp += " </tbody>\n" +
" </table>\n" +
" </ol>"
});
$("#search").append(Temp)//在id为search的后面加入上面设置好的代码
$('#check').off("click");//得到数据后不再允许点击事件
console.log(data);
},
error:function (e) {
alert("错误！！");
}
});
});
$('#addStu').click(function () {
$.ajax({
url:"/sc/stu/add",
type:"post",
data:{stuName:$('#name').val(),num:$('#num').val(),age:$('#age').val(),classId:$('#classId').val(),sex:$("input[name='sex']:checked").val()},
success:function (res) {
alert(res.toString());
$('#formAdd')[0].reset();//成功后,清除所有表单内容
console.log(res);
},
error:function (e) {
alert("错误！！");
}
});
})
});
</script>
</head>
<body>
<div class="content">
<div style="text-align: center;width: 600px;margin: 0 auto;" id="search">
<h3>欢迎你登录,${user.loginName}</h3>
<br><br>
<a type="button" class="btn btn-info" id="check">查看所有class及user信息</a>
</div>
<br><br>
<br><br>
<div style="margin: 0 auto;width: 400px;">
<form role="form" action="" method="post" id="formAdd">
<div class="form-group" style="margin-bottom: 10px;">
<label for="name">class</label>：
<select class="form-control" name="classId" id="classId">
<option value="4ee241c3c27143ebbed9c10d004b035c">51c</option>
<option value="3712cef7bc664ee5859424ef74df35dc">52c</option>
</select>
</div>
<div class="form-group">
<label for="name">姓名</label>：
<input type="text" class="form-control" id="name" name="stuName" placeholder="请输入">
</div>
<div class="form-group">
<label for="name">学号</label>：
<input type="text" class="form-control" id="num" name="num" placeholder="请输入">
</div>
<div class="form-group">
<label for="name">年龄</label>：
<input type="text" class="form-control" id="age" name="age" placeholder="请输入">
</div>
<label class="radio-inline">
<input class="sex" type="radio" name="sex" id="optionsRadios3" value="男"> 男
<input class="sex" type="radio" name="sex" id="optionsRadios4" value="女"> 女
</label>
<br><br>
<button type="button" class="btn btn-primary" id="addStu" style="margin-left: 100px;">Add</button>
</form>
</div>
</div>
</body>
</html>
