mybatis递归，一对多代码示例

今天需要做一个功能，根据专业，有不同的章节，章节下面有对应的习题，

由于只有这么两级，可以不用使用递归，直接查询父集，之后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在线工具

[
    {
        "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