Java之Mybatis多層嵌套查詢方式

更新時(shí)間：2022年03月11日 08:57:16 作者：蒼穹之躍

這篇文章主要介紹了Java之Mybatis多層嵌套查詢方式，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教

Mybatis多層嵌套查詢

三張表：user article blog

表的存儲(chǔ)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');

實(shí)體類(lèi)

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語(yǔ)句 -->
    <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多層嵌套查詢(多對(duì)多)

依賴(lài)

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

實(shí)體類(lèi)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;//套餐價(jià)格
    private String remark;
    private String attention;
    private String img;//套餐對(duì)應(yīng)圖片存儲(chǔ)路徑
    @TableField(exist = false)
    private List<CheckGroup> checkGroups;//體檢套餐對(duì)應(yīng)的檢查組，多對(duì)多關(guān)系
}

實(shí)體類(lèi)CheckGroup

@Data
@TableName("t_checkgroup")
public class CheckGroup {
    @TableId(type = IdType.AUTO)
    private Integer id;//主鍵
    private String code;//編碼
    private String name;//名稱(chēng)
    private String helpCode;//助記
    private String sex;//適用性別
    private String remark;//介紹
    private String attention;//注意事項(xiàng)
    @TableField(exist = false)
    private List<CheckItem> checkItems;//一個(gè)檢查組合包含多個(gè)檢查項(xiàng)
}

實(shí)體類(lèi)CheckItem

@Data
@TableName("t_checkitem")
public class CheckItem {
    @TableId(type = IdType.AUTO)
    private Integer id;//主鍵
    private String code;//項(xiàng)目編碼
    private String name;//項(xiàng)目名稱(chēng)
    private String sex;//適用性別
    private String age;//適用年齡（范圍），例如：20-50
    private Float price;//價(jià)格
    private String type;//檢查項(xiàng)類(lèi)型，分為檢查和檢驗(yàn)兩種類(lèi)型
    private String remark;//項(xiàng)目說(shuō)明
    private String attention;//注意事項(xiàng)
}

中間表t_setmeal_checkgroup

中間表t_checkgroup_checkitem

可以看出Setmeal里面包含多個(gè)CheckGroup，而CheckGroup包括多個(gè)CheckItem

mapper層

CheckItemMapper
/**
? ? ?* 根據(jù)檢查組得到檢查項(xiàng)
? ? ?* @param checkgroupId
? ? ?* @return
? ? ?*/
? ? List<CheckItem> findCheckItemById(@Param("checkgroupId") Integer checkgroupId);

CheckItemMapper.xml

<!--根據(jù)檢查組id查詢檢查項(xiàng)信息-->
? ? <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

/**
? ? ?* 根據(jù)體驗(yàn)套餐的id得到檢查項(xiàng)的分組
? ? ?* @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>
? ? <!--根據(jù)套餐id查詢檢查項(xiàng)信息-->
? ? <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"應(yīng)該是把CheckGroup的id當(dāng)作參數(shù)傳給findCheckGroupBySetmealId

SetmealMapper

/**
? ? ?* 根據(jù)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"應(yīng)該就是t_setmeal的id，然后傳過(guò)去-->
? ? <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>