• Spring Boot (11) mybatis 关联映射


    一对多

      查询category中的某一条数据,同时查询该分类下的所有Product。

    Category.java

    public class Category {
        private Integer id;
        private String name;
        private List<Product> product;
     ....

    product.java

    public class Product {
        private Integer id;
        private String name;
        private String price;
        private Integer categoryId;
    ...

    dao层

    @Mapper
    public interface MyBatisDao {
    
        @Select("select * from product where categoryId = #{categoryId}")
        List<Product> findProductByCategoryId(Integer categoryId);
    
        @Results({
                @Result(property = "id" , column = "id"),
                //products映射<List> Product,many=@Many是调用关联的方法,"id"是关联的条件, FetchType.LAZY是延迟加载
                @Result(property = "products", column = "id", many=@Many(select = "com.david.dao.MyBatisDao.findProductByCategoryId",fetchType = FetchType.LAZY))
        })
        @Select("select * from category where id = #{id}")
        Category findCategoryById(Integer id);
    
    }

    service层

    @Service
    @Transactional
    public class MyBatisService {
    
        @Resource
        private MyBatisDao myBatisDao;
    
        public Category findCategoryById(){
            Category category = myBatisDao.findCategoryById(1);return category;
        }
    
    }

    测试:

    一对一:把上面的@many=@Many换成one=@One即可

      修改Product.java 增加属性

    public class Product {
        private Integer id;
        private String name;
        private String price;
        private Integer categoryId;
        private Category category;

      dao

        @Results({
                @Result(property = "id", column = "id"),
                @Result(property = "category", column = "categoryId",one=@One(select = "com.david.dao.MyBatisDao.findCategoryById",fetchType = FetchType.LAZY))
        })
        @Select("select * from Product where id = #{id}")
        Product findProductCategoryById(Integer id);

    多对多:把多个字段映射成many=@Many即可

    多对一:把上面dao方法的返回值从Category改成List<Category>

    JAVA注解的局限性

    返回多条Category

        @Results({
                @Result(property = "id" , column = "id"),
                //products映射<List> Product,many=@Many是调用关联的方法,"id"是关联的条件, FetchType.LAZY是延迟加载
                @Result(property = "products", column = "id", many=@Many(select = "com.david.dao.MyBatisDao.findProductByCategoryId",fetchType = FetchType.LAZY))
        })
        @Select("select * from category")
        List<Category> findAllCategory();

      category现在有三条记录,我要查出所有category及其对应的product,最终得到一个List<Category> 查询过程是这样的:

     一共执行了四次查询,一次查category表,因为有三条记录,所以查了三次product表,以此来填充三个List<Product> products属性。如果category中又几百条数据,而且还有上十个表进行关联查询,结果无法想象。在传统的xml配置中,是可以用一条sql语句查出来的,不过mybatis官方做出了一个说明,由于java注解的局限性,不支持那种映射方式。所以,如果只想用一条sql语句查出关联映射,必须借助xml。

    xml无限层嵌套映射

      这里以三层嵌套为例,实现前端的三级菜单树。这是一个tree表,pid是其上级菜单的id。

     

    要得到查询结果Tree对象,这个对象是可以无限递归的

    public class TreeMenu {
        private Integer Id;
        private String MenuName;
        private List<TreeMenu> child;

    dao

    @Mapper
    public interface TreeMenuDao {
        @ResultMap("treeMenu")
        @Select("select p1.id,p1.menuName,p2.id id2,p2.menuName menuName2,p3.id id3,p3.menuName menuName3"+
        "from treemenu p1, treemenu p2,treemenu p3 where p1.id = p2.pid and p2.id = p3.pid")
        List findTreeMenu();
    }

    这个sql语句在数据库中查询结果是这样的,可以发现前四个字段是一样的,而且都是冗余数据,如果用java注解的关联查询是不会这样的

    @ResultMap("treeMenu"):映射结果集id是treeMenu,这个映射要在xml中配置

    application.yml中添加配置

    mybatis:
      mapper-locations: classpath:mapper/*

    在resources/mapper/TreeMenuMapper.xml

    <?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">
    <!-- 映射dao -->
    <mapper namespace="com.david.dao.TreeMenuDao">
        <!-- 结果集类型 -->
        <resultMap id="treeMenu" type="com.david.bean.TreeMenu">
            <!-- 映射字段 -->
            <result column="Id"  property="Id" />
            <result column="MenuName"  property="MenuName" />
            <!-- 嵌套第二张表 -->
            <collection  property="child" ofType="com.david.bean.TreeMenu" >
                <id column="Id2" property="Id" />
                <result column="MenuName2" property="MenuName" />
                <!-- 嵌套第三张表 -->
                <collection  property="child" ofType="com.david.bean.TreeMenu" >
                    <id column="Id3" property="Id" />
                    <result column="MenuName3" property="MenuName" />
                </collection>
            </collection>
        </resultMap>
    </mapper>

    这里只是配置一个嵌套映射,在dao方法中通过@ResultMap("treeMenu")使用这个映射。最终查询结果会映射成一个TreeMenu对象,通过spring mvc转换为json结果如下,在一些前端框架中,实现属性菜单就是需要用这种结构的json数据值。

    [
        {
            "child": [
                {
                    "child": [
                        {
                            "child": null,
                            "id": 4,
                            "menuName": "三级菜单-1-1"
                        },
                        {
                            "child": null,
                            "id": 5,
                            "menuName": "三级菜单-1-2"
                        }
                    ],
                    "id": 2,
                    "menuName": "二级菜单-1"
                },
                {
                    "child": [
                        {
                            "child": null,
                            "id": 6,
                            "menuName": "三级菜单-2-1"
                        }
                    ],
                    "id": 3,
                    "menuName": "二级菜单-2"
                }
            ],
            "id": 1,
            "menuName": "一级菜单"
        }
    ]
  • 相关阅读:
    [ZJOI2011]营救皮卡丘
    TJOI2018Party
    HEOI2013SAO
    [BJOI2017]树的难题
    [HNOI2016]序列
    [SHOI2007]善意的投票
    CF802C Heidi and Library (hard)
    SPOJ DIVCNT2
    LOJ子序列
    BZOJ2882工艺
  • 原文地址:https://www.cnblogs.com/baidawei/p/9107829.html
Copyright © 2020-2023  润新知