今天需要做一个功能，根据专业，有不同的章节，章节下面有对应的习题，
由于只有这么两级，可以不用使用递归，直接查询父集，之后foreach查询子集放入对应的list集合。
虽然实现了，感觉毕竟，太low。
有同事跟我说可以使用mybatis的递归实现，就学习了下。
对应的bean里面需要有对应的list<bean> lists的引用。
直接上代码
对应的sql语句
CREATE TABLE `goods_category` (
`goodscateid` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`parentid` int(11) DEFAULT NULL,
`description` varchar(255) DEFAULT NULL,
`displayorder` int(11) DEFAULT NULL,
`commissionrate` double DEFAULT NULL,
`enabled` int(11) DEFAULT NULL,
PRIMARY KEY (`goodscateid`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8; /*Data for the table `goods_category` */
insert into `goods_category`(`goodscateid`,`name`,`parentid`,`description`,`displayorder`,`commissionrate`,`enabled`) values (1,'java',0,'',NULL,NULL,NULL),(2,'spring',1,'',NULL,NULL,NULL),(3,'springmvc',1,'',NULL,NULL,NULL),(4,'struts',1,'',NULL,NULL,NULL),(5,'jdbc',0,'',NULL,NULL,NULL),(6,'hibernate',5,'',NULL,NULL,NULL),(7,'mybatis',5,'',NULL,NULL,NULL),(8,'jdbctemplate',5,'',NULL,NULL,NULL),(9,'beanfactory',3,'',NULL,NULL,NULL),(10,'factorybean',3,'',NULL,NULL,NULL);
实体类
@JsonIgnoreProperties({"displayorder","commissionrate","enabled"})
public class GoodsCategoryVo {
private Integer goodscateid;
private String name;
private Integer parentid;
private String description;
private Integer displayorder;
private Double commissionrate;
private Integer enabled;
private List<GoodsCategoryVo> catelist;
get 。。。 set。。。 tostring。。。
dao层
public interface GoodsMapper {
List<GoodsCategoryVo> getCategory(Integer pid);
}
mapper.xml
<resultMap id="getSelf" type="com.bscc.beans.GoodsCategoryVo">
<id column="goodscateid" property="goodscateid"></id>
<result column="name" property="name"></result>
<collection property="catelist" select="getCategory"
column="goodscateid"></collection>
<!--查到的cid作为下次的pid -->
</resultMap> <select id="getCategory" resultMap="getSelf">
select * from goods_category where parentid=#{pid}
ORDER BY displayorder,goodscateid
</select>
之后直接访问对应的方法，即可查询出来
@RequestMapping("/getGoodsList")
@ResponseBody
public List<GoodsCategoryVo> getGoodsList(){
// pid指定为0
List<GoodsCategoryVo> list = goodsMapper.getCategory(0);
return list;
}
结果，可以使用json在线工具
[
{
"goodscateid": 1,
"name": "java",
"parentid": 0,
"description": "111",
"catelist": [
{
"goodscateid": 2,
"name": "spring",
"parentid": 1,
"description": "222",
"catelist": []
},
{
"goodscateid": 3,
"name": "springmvc",
"parentid": 1,
"description": "333",
"catelist": [
{
"goodscateid": 9,
"name": "beanfactory",
"parentid": 3,
"description": "999",
"catelist": []
},
{
"goodscateid": 10,
"name": "factorybean",
"parentid": 3,
"description": "000",
"catelist": []
}
]
},
{
"goodscateid": 4,
"name": "struts",
"parentid": 1,
"description": "444",
"catelist": []
}
]
},
{
"goodscateid": 5,
"name": "jdbc",
"parentid": 0,
"description": "555",
"catelist": [
{
"goodscateid": 6,
"name": "hibernate",
"parentid": 5,
"description": "666",
"catelist": []
},
{
"goodscateid": 7,
"name": "mybatis",
"parentid": 5,
"description": "777",
"catelist": []
},
{
"goodscateid": 8,
"name": "jdbctemplate",
"parentid": 5,
"description": "888",
"catelist": []
}
]
}
]
mybatis递归就是这么的简单。
说下mybatis一对多实现
对应的bean
public class Dept {
private Integer id;
private String deptName;
private String locAdd;
private List<Emp> emps
@JsonIgnoreProperties("dept")
public class Emp {
private Integer id;
private String name;
private Dept dept;
dao层
public interface DeptMapper {
public Dept getDeptById(Integer id);
}
public interface EmpMapper {
public Emp getEmpByDeptId(Integer deptId);
}
mapper.xml文件
<mapper namespace="com.bscc.mapper.DeptMapper">
<resultMap id="DeptResultMap" type="com.bscc.beans.Dept">
<id property="id" column="id"/>
<result property="deptName" column="deptName"/>
<result property="locAdd" column="locAdd"/>
<!-- private List<Emp> emps; column="id"写被集合对象主键，select按照外键键查询，通过deptid查出emp给dept-->
<collection property="emps" column="id" ofType="Emp" select="com.bscc.mapper.EmpMapper.getEmpByDeptId"/>
</resultMap>
<select id="getDeptById" parameterType="Integer" resultMap="DeptResultMap">
select * from tbl_dept where id=#{id}
</select>
</mapper>
<mapper namespace="com.bscc.mapper.EmpMapper">
<resultMap id="EmpResultMap" type="com.bscc.beans.Emp">
<id property="id" column="id"/>
<result property="name" column="name"/>
</resultMap>
<select id="getEmpByDeptId" parameterType="Integer" resultMap="EmpResultMap">
select * from tbl_emp where deptId=#{deptId}
</select>
</mapper>
对应的controller方法
@RequestMapping("/getDeptById")
@ResponseBody
public Dept getDeptById() {
Dept deptById = deptMapper.getDeptById(1);
return deptById;
}
无非就是比简单查询复杂一些罢了。
代码目录
OK！！！
对应的github地址