今天需要做一个功能, 根据专业, 有不同的章节, 章节下面有对应的习题,
由于只有这么两级, 可以不用使用递归, 直接查询父集, 之后 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,'111',NULL,NULL,NULL),(2,'spring',1,'222',NULL,NULL,NULL),(3,'springmvc',1,'333',NULL,NULL,NULL),(4,'struts',1,'444',NULL,NULL,NULL),(5,'jdbc',0,'555',NULL,NULL,NULL),(6,'hibernate',5,'666',NULL,NULL,NULL),(7,'mybatis',5,'777',NULL,NULL,NULL),(8,'jdbctemplate',5,'888',NULL,NULL,NULL),(9,'beanfactory',3,'999',NULL,NULL,NULL),(10,'factorybean',3,'000',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 在线工具 https://www.sojson.com/
- [
- {
- "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 地址
https://github.com/chywx/MavenProject6oneToMany
来源: https://www.cnblogs.com/c-h-y/p/9434126.html