Java之Mybatis多层嵌套查询方式

Java之Mybatis多层嵌套查询方式

目录Mybatis多层嵌套查询表的存储sql文件实体类XMLMybatis多层嵌套查询(多对多)依赖实体类Setmeal实体类CheckGroup实体类CheckItemmapper层测试

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

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

return articles;

}

public void setArticles(List

this.articles = articles;

}

}

XML

&pZASflt;!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

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}

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

依赖

com.baomidou

mybatis-plus-boot-starter

3.4.3

实体类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 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 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 findCheckItemById(@Param("checkgroupId") Integer checkgroupId);

CheckItemMapper.xml

select * from t_checkitem

where id

in (select checkitem_id from t_checkgroup_checkitem where checkgroup_id=#{checkgroupId})

CheckGroupMapper

/**

* 根据体验套餐的id得到检查项的分组

* @param setmealId

* @return

*/

List findCheckGroupBySetmealId(@Param("setmealId") Integer setmealId);

CheckGroupMapper.xml

id="findByIdResultMap"

extends="baseResultMap">

javaType="ArrayList"

ofType="com.zhubayi.common.pojo.CheckItem"

column="id"

select="com.zhubayi.provider.mapper.CheckItemMapper.findCheckItemById">

id="findByIdResultMap"

extends="baseResultMap">

javaType="ArrayList"

ofType="com.zhubayi.common.pojo.CheckItem"

column="id"

select="com.zhubayi.provider.mapper.CheckItemMapper.findCheckItemById">

javaType="ArrayList"

ofType="com.zhubayi.common.pojo.CheckItem"

column="id"

select="com.zhubayi.provider.mapper.CheckItemMapper.findCheckItemById">

select * from t_checkgroup

where id

in (select checkgroup_id from t_setmeal_checkgroup where setmeal_id=#{id})

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

SetmealMapper

/**

* 根据id查询套餐信息

* @param id

* @return

*/

Setmeal findById(@Param("id") int id);

SetmealMapper.xml

id="findByIdResultMap"

extends="baseResultMap">

javaType="ArrayList"

ofType="com.zhubayi.common.pojo.CheckGroup"

column="id"

select="com.zhubayi.provider.mapper.CheckGroupMapper.findCheckGroupBySetmealId">

id="findByIdResultMap"

extends="baseResultMap">

javaType="ArrayList"

ofType="com.zhubayi.common.pojo.CheckGroup"

column="id"

select="com.zhubayi.provider.mapper.CheckGroupMapper.findCheckGroupBySetmealId">

javaType="ArrayList"

ofType="com.zhubayi.common.pojo.CheckGroup"

column="id"

select="com.zhubayi.provider.mapper.CheckGroupMapper.findCheckGroupBySetmealId">

select * from t_setmeal where id=#{id}

测试

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

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。