Java之Mybatis多层嵌套查询方式

目录

• Mybatis多层嵌套查询
• 表的存储sql文件
• 实体类
• XML
• Mybatis多层嵌套查询(多对多)
• 依赖
• 实体类Setmeal
• 实体类CheckGroup
• 实体类CheckItem
• mapper层
• 测试

Mybatis多层嵌套查询

三张表：user article blog

表的存储sql文件

/*
Navicat MySQL Data Transfer
Source Server         : localhost
Source Server Version : 50620
Source Host           : localhost:3306
Source Database       : mybatis
Target Server Type    : MYSQL
Target Server Version : 50620
File Encoding         : 65001
Date: 2014-10-19 18:27:31
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `user`
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userName` varchar(50) DEFAULT NULL,
  `userAge` int(11) DEFAULT NULL,
  `userAddress` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', 'summer', '30', 'shanghai');
INSERT INTO `user` VALUES ('2', 'test1', '22', 'suzhou');
INSERT INTO `user` VALUES ('3', 'test1', '29', 'some place');
INSERT INTO `user` VALUES ('4', 'lu', '28', 'some place');
INSERT INTO `user` VALUES ('5', 'xiaoxun', '27', 'nanjing');

-- ----------------------------
-- Table structure for `article`
-- ----------------------------
DROP TABLE IF EXISTS `article`;
CREATE TABLE `article` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userid` int(11) NOT NULL,
  `title` varchar(100) DEFAULT NULL,
  `content` text,
  `blogid` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of article
-- ----------------------------
INSERT INTO `article` VALUES ('1', '1', 'test_title_1', 'test_content_1', '1');
INSERT INTO `article` VALUES ('2', '1', 'test_title_2', 'test_content_2', '1');
INSERT INTO `article` VALUES ('3', '1', 'test_title_3', 'test_content_3', '2');
INSERT INTO `article` VALUES ('4', '1', 'test_title_4', 'test_content_4', '2');
INSERT INTO `article` VALUES ('5', '2', 'test_title_5', 'test_content_5', '2');

-- ----------------------------
-- Table structure for `blog`
-- ----------------------------
DROP TABLE IF EXISTS `blog`;
CREATE TABLE `blog` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of blog
-- ----------------------------
INSERT INTO `blog` VALUES ('1', 'xiaoxun_blog');
INSERT INTO `blog` VALUES ('2', 'zhang_blog');

实体类

package com.mybatis.test;
public class Article {
    private int id;
    private User user;
    private String title;
    private String content;

    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }

    public User getUser() {
        return user;
    }
    public void setUser(User user) {
        this.user = user;
    }

    public String getTitle() {
        return title;
    }
    public void setTitle(String title) {
        this.title = title;
    }

    public String getContent() {
        return content;
    }
    public void setContent(String content) {
        this.content = content;
    }
}

package com.mybatis.test;
import java.util.List;
public class Blog {
    private int id;
    private String title;
    private List<Article> articles;

    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }

    public String getTitle() {
        return title;
    }
    public void setTitle(String title) {
        this.title = title;
    }

    public List<Article> getArticles() {
        return articles;
    }
    public void setArticles(List<Article> articles) {
        this.articles = articles;
    }
}

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">
<mapper namespace="com.mybatis.test.IBlogOperation">
    <resultMap id="userResultMap" type="User">
        <id property="id" column="user_id"  />
        <result property="userName" column="user_userName"  />
        <result property="userAge" column="user_userAge"  />
        <result property="userAddress" column="user_userAddress"  />
    </resultMap>

    <resultMap id="articleResultMap" type="Article">
        <id property="id" column="article_id" />
        <result property="title" column="article_title" />
        <result property="content" column="article_content" />
        <association property="user" javaType="User" resultMap="userResultMap"/>
    </resultMap>

    <resultMap id="blogResultMap" type="Blog">
        <id property="id" column="blog_id" />
        <result property="title" column="blog_title" />
        <!-- 将article list属性映射到collection -->
        <collection property="articles" ofType="Article" resultMap="articleResultMap"/>
    </resultMap>

    <!-- select语句 -->
    <select id="getBlogByID" parameterType="int" resultMap="blogResultMap">
       select user.id user_id,user.userName user_userName,user.userAddress user_userAddress,
       article.id article_id,article.title article_title,article.content article_content,
       blog.id blog_id, blog.title blog_title
       from user,article,blog
       where user.id=article.userid and blog.id=article.blogid and blog.id=#{id}
    </select>
</mapper>

Mybatis多层嵌套查询(多对多)

依赖

			<dependency>
                <groupId>com.baomidou</groupId>
                <artifactId>mybatis-plus-boot-starter</artifactId>
                <version>3.4.3</version>
            </dependency>

实体类Setmeal

@Data
@TableName("t_setmeal")
public class Setmeal implements Serializable {
    @TableId(type = IdType.AUTO)
    private Integer id;
    private String name;
    private String code;
    private String helpCode;
    private String sex;//套餐适用性别：0不限 1男 2女
    private String age;//套餐适用年龄
    private Float price;//套餐价格
    private String remark;
    private String attention;
    private String img;//套餐对应图片存储路径
    @TableField(exist = false)
    private List<CheckGroup> checkGroups;//体检套餐对应的检查组，多对多关系
}

实体类CheckGroup

@Data
@TableName("t_checkgroup")
public class CheckGroup {
    @TableId(type = IdType.AUTO)
    private Integer id;//主键
    private String code;//编码
    private String name;//名称
    private String helpCode;//助记
    private String sex;//适用性别
    private String remark;//介绍
    private String attention;//注意事项
    @TableField(exist = false)
    private List<CheckItem> checkItems;//一个检查组合包含多个检查项
}

实体类CheckItem

@Data
@TableName("t_checkitem")
public class CheckItem {
    @TableId(type = IdType.AUTO)
    private Integer id;//主键
    private String code;//项目编码
    private String name;//项目名称
    private String sex;//适用性别
    private String age;//适用年龄（范围），例如：20-50
    private Float price;//价格
    private String type;//检查项类型，分为检查和检验两种类型
    private String remark;//项目说明
    private String attention;//注意事项
}

中间表t_setmeal_checkgroup

中间表t_checkgroup_checkitem

可以看出Setmeal里面包含多个CheckGroup，而CheckGroup包括多个CheckItem

mapper层

CheckItemMapper
/**
     * 根据检查组得到检查项
     * @param checkgroupId
     * @return
     */
    List<CheckItem> findCheckItemById(@Param("checkgroupId") Integer checkgroupId);

CheckItemMapper.xml

<!--根据检查组id查询检查项信息-->
    <select id="findCheckItemById" resultType="com.zhubayi.common.pojo.CheckItem">
        select * from t_checkitem
        where id
        in (select checkitem_id from t_checkgroup_checkitem where checkgroup_id=#{checkgroupId})
    </select>

CheckGroupMapper

/**
     * 根据体验套餐的id得到检查项的分组
     * @param setmealId
     * @return
     */
    List<CheckGroup> findCheckGroupBySetmealId(@Param("setmealId") Integer setmealId);

CheckGroupMapper.xml

    <resultMap type="com.zhubayi.common.pojo.CheckGroup" id="baseResultMap">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <result column="code" property="code"/>
        <result column="help_code" property="helpCode"/>
        <result column="sex" property="sex"/>
        <result column="remark" property="remark"/>
        <result column="attention" property="attention"/>
    </resultMap>
    
    <resultMap type="com.zhubayi.common.pojo.CheckGroup"
               id="findByIdResultMap"
               extends="baseResultMap">
        <collection property="checkItems"
                    javaType="ArrayList"
                    ofType="com.zhubayi.common.pojo.CheckItem"
                    column="id"
                    select="com.zhubayi.provider.mapper.CheckItemMapper.findCheckItemById">
        </collection>
    </resultMap>
    <!--根据套餐id查询检查项信息-->
    <select id="findCheckGroupBySetmealId" resultMap="findByIdResultMap">
        select * from t_checkgroup
        where id
                  in (select checkgroup_id from t_setmeal_checkgroup where setmeal_id=#{id})
    </select>

column="id"应该是把CheckGroup的id当作参数传给findCheckGroupBySetmealId

SetmealMapper

/**
     * 根据id查询套餐信息
     * @param id
     * @return
     */
    Setmeal findById(@Param("id") int id);

SetmealMapper.xml

    <resultMap type="com.zhubayi.common.pojo.Setmeal" id="baseResultMap">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <result column="code" property="code"/>
        <result column="help_code" property="helpCode"/>
        <result column="sex" property="sex"/>
        <result column="age" property="age"/>
        <result column="price" property="price"/>
        <result column="remark" property="remark"/>
        <result column="attention" property="attention"/>
        <result column="img" property="img"/>
    </resultMap>
    <!--column="id"应该就是t_setmeal的id，然后传过去-->
    <resultMap type="com.zhubayi.common.pojo.Setmeal"
               id="findByIdResultMap"
               extends="baseResultMap">
        <collection property="checkGroups"
                    javaType="ArrayList"
                    ofType="com.zhubayi.common.pojo.CheckGroup"
                    column="id"
                    select="com.zhubayi.provider.mapper.CheckGroupMapper.findCheckGroupBySetmealId">
        </collection>
    </resultMap>
    <select id="findById" resultMap="findByIdResultMap">
        select * from t_setmeal  where id=#{id}
    </select>

测试

一个setmeal里面有多个checkGroup，checkGroup里面有多个checkItems

以上为个人经验，希望能给大家一个参考，也希望大家多多支持我们。