mybatis的嵌套查询和嵌套结果的区别,mybatis嵌套子查询
目录
米巴提斯多层嵌套查询表的存储结构化查询语言文件实体类XMLMybatis多层嵌套查询(多对多)依赖实体类套餐实体类检查组实体类检查项目映射器层测试
Mybatis多层嵌套查询
三张表:用户文章博客
表的存储sql文件
/*Navicat MySQL数据传输源服务器:本地主机源服务器版本: 50620源主机:本地主机:3306来源源数据库: mybatisTarget服务器类型: MySQL目标服务器版本: 50620文件编码: 65001日期: 2014-10-19 1833:20-"用户"的表结构- 如果存在"用户",则删除表;CREATE TABLE ` user `(` id ` int(11)NOT NULL AUTO _ INCREMENT,` username ` varchar(50)DEFAULT NULL,` userAge` int(11) DEFAULT NULL,` user address ` varchar(200)DEFAULT NULL,PRIMARY KEY(` id `))ENGINE=InnoDB AUTO _ INCREMENT=6 DEFAULT CHARSET=utf8;- -用户记录- -插入`用户值( 1 ,夏季, 30 ,上海);插入"用户"值(“2”、“测试1”、“22”、“测试2”苏州");插入"用户"值("3"、"测试1"、"29"、"某处");插入到"用户"值中(《四大》、《鲁》、《二十八大》、《四大》某处");插入到"用户"值中("5"、"小勋"、"27"、"南京");-“文章”的表结构- 如果存在“文章”,则删除表;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;- -文章记录- -插入"文章"值( 1 , 1 ,测试标题_1 ,测试内容_1 , 1 );插入到文章值( 2 , 1 , test_title_2 , test_content_2 , 1 );插入到`文章值( 3 , 1 , test_title_3 , test_content_3 , 2 );插入到`文章值( 4 , 1 , test_title_4 , test_content_4 , 2 );插入到`文章`值( 5 , 2 ,测试_标题_5 ,测试_内容_5 , 2 );"博客"的表结构
ABLE 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
以上为个人经验,希望能给大家一个参考,也希望大家多多支持盛行IT。
郑重声明:本文由网友发布,不代表盛行IT的观点,版权归原作者所有,仅为传播更多信息之目的,如有侵权请联系,我们将第一时间修改或删除,多谢。