Maven项目集成Mybatis 一对多、多对一查询嵌套&结

作者: 乘风破浪的姐姐 | 来源:发表于2021-12-16 17:15 被阅读0次

Maven项目集成Mybatis 一对多、多对一查询嵌套&结
PageHelper插件一对多查询时的分页问题
SpringBoot+Spring JPA基础使用
mybatis一对多查询
【Mybatis】一对多查询
mybatis
SSM框架的常用注解整理
2018-07-09
09|第九课：一对一以及一对多关联查询详解
mybatis一对多、延迟查询

MyBatis在映射文件中加载关联对象的关系主要通过两种方式:查询嵌套、结果嵌套
查询嵌套:类似于子查询，它需要执行多条sql语句
结果嵌套:类似于联结查询，它只会执行一条复杂的sql语句

一、多对一查询
例如：会员和门店，一个商户的一个门店下可以有多个会员，每个会员对应一个门店
实例类pojo:
TdyMemberInfo.class

package com.sc.pojo;

import lombok.Data;

@Data
public class TdyMemberInfo {
    private Long id;
    private String memberNo;
    private String mobile;
    private String openId;
    private TdyShop shop;

}

TdyShop.class

package com.sc.pojo;

import lombok.Data;

@Data
public class TdyShop {
    private int shopInfoId;
    private String shopName;
    private Long shopId;
  
}

数据库中表字段与实体类的属性对应，t_dy_member_info表通过外键shop_id关联t_dy_shop表

TdyMemberInfoMapper.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.sc.dao.TdyMemberInfoMapper">
 
 <select id="getAllmemberInfo1" resultMap="memberShop">
         select m.member_id id, m.member_no memberNo,m.mobile,m.open_id openId,m.shop_id shopId  from t_dy_member_info m
         where  m.member_no is not null   and m.shop_id is not null
    </select>

    <resultMap id="memberShop" type="TdyMemberInfo">
        <result property="id"  column="id"/>
        <result property="memberNo" column="memberNo"/>
        <result property="openId" column="openId"/>
        <association property="shop" column="shop_id" javaType="TdyShop" select="getShop">
            <result property="shopInfoId" column="shop_info_id"/>
            <result property="shopId" column="shop_id"/>
            <result property="shopName" column="shopName"/>
        </association>
    </resultMap>

    <select id="getShop" resultType="TdyShop">
        select * shopName  from t_dy_shop where shop_id=#{shopId}
    </select>

结果嵌套(多对一)

  <resultMap id="memberResultMap" type="TdyMemberInfo">
        <result property="id"  column="member_id"/>
        <result property="memberNo" column="member_no"/>
        <result property="mobile" column="mobile"/>
        <result property="openId" column="open_id"/>

        <!--关联对象property 关联对象在TdyShop实体类中的属性   association: 一对一关联(has one)-->
        <association property="shop" javaType="TdyShop">
            <result property="shopInfoId" column="shop_info_id"/>
            <result property="shopId" column="shop_id"/>
            <result property="shopName" column="shop_name"/>
        </association>
    </resultMap>

    <select id="getAllmemberInfo2" resultMap="memberResultMap" >
       select m.*,s.*
       from t_dy_member_info m ,t_dy_shop s
       where m.shop_id = s.shop_id and m.member_no is not null
    </select>

接口：TdyMemberInfoDao.class

package com.sc.dao;

import com.sc.pojo.TdyMemberInfo;

import java.util.List;

public interface TdyMemberInfoDao {
    //多对一
    List<TdyMemberInfo> getAllmemberInfo1();
    List<TdyMemberInfo> getAllmemberInfo2();
}

测试类：MemberDaoTest.class

package com.sc.dao;

import com.sc.config.MyBatisUtils;
import com.sc.pojo.TdyMemberInfo;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.List;

public class MemberDaoTest {
    //多对一
    @Test
    public void selectAll1(){
        SqlSession sqlSession =  MyBatisUtils.getSqlSession();
        TdyMemberInfoDao mapper = sqlSession.getMapper(TdyMemberInfoDao.class);
        List<TdyMemberInfo> allMembers = mapper.getAllmemberInfo1();
        for(TdyMemberInfo member:allMembers){
            System.out.println(member);
        }
        sqlSession.close();
    }


    //多对一
    @Test
    public void selectAll2(){
        SqlSession sqlSession =  MyBatisUtils.getSqlSession();
        TdyMemberInfoDao mapper = sqlSession.getMapper(TdyMemberInfoDao.class);
        List<TdyMemberInfo> allMembers = mapper.getAllmemberInfo2();
        for(TdyMemberInfo member:allMembers){
            System.out.println(member);
        }
        sqlSession.close();
    }

输出结果：

image.png

二、一对多查询
例如：会员和门店，多个会员属于同一个门店
实例类pojo:
TdyMemberInfo.class

package com.sc.pojo;

import lombok.Data;
 

@Data
public class TdyMemberInfo {
    private Long memberId;
    private String memberNo;
    private String mobile;
    private String openId;
    private String shopId;

}

TdyShop.class

package com.sc.pojo;

import lombok.Data;
import java.util.List;

@Data
public class TdyShop {
    private Long shopId;
    private String shopCode;
    private String shopName;
    private List<TdyMemberInfo> memberList;

}

mapper配置文件
TdyShopMapper.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.sc.dao.TdyShopMapper">
    <select id="getShopMembers2" resultMap="shopResultMap">
        select * from t_dy_shop where shop_id=#{shopId}
    </select>
    <resultMap id="shopResultMap" type="TdyShop">
        <collection property="memberList" javaType="ArrayList" ofType="TdyMemberInfo" column="shop_id" select="getMembers"/>
    </resultMap>
    <select id="getMembers" resultType="TdyMemberInfo">
        select *
        from t_dy_member_info
        where member_no is not null
        and shop_id=#{shopId}
    </select>
</mapper>

结果嵌套(一对多)

 <resultMap id="memberMap" type="TdyShop">
        <result property="shopId" column="shop_id"/>
        <result property="shopCode" column="shop_code"/>
        <result property="shopName" column="shop_name"/>

        <!--关联对象property 关联对象在TdyShop实体类中的属性-->
        <!-- 
            collection定义关联的集合类型的属性的封装规则，一对多关联(has many)
            property="memberList":指定这是哪个集合属性，对应实例类中对应的属性  private List<TdyMemberInfo> memberList; 中的memberList
            ofType:指定集合内封装的JavaBean类型(集合内装的什么)，这里是会员信息，即为TdyMemberInfo类
      -->
        <collection property="memberList" ofType="TdyMemberInfo"   column="shop_id">
            <result property="memberId"   column="member_id"/>
            <result property="memberNo" column="member_no"/>
            <result property="mobile" column="mobile"/>
            <result property="openId" column="open_id"/>
            <result property="shopId" column="shop_id"/>
        </collection>
    </resultMap>

    <select id="getShopMembers" resultMap="memberMap" >
       select s.*,m.*
       from t_dy_shop s,t_dy_member_info m
       where s.shop_id = m.shop_id
       and m.member_no is not null
       and  s.shop_id = #{shopId}
    </select>

接口：TdyShopMapper.class

package com.sc.dao;

import com.sc.pojo.TdyShop;
import org.apache.ibatis.annotations.Param;

public interface TdyShopMapper {
    //一对多
     TdyShop getShopMembers(@Param("shopId") Long shopId);

    TdyShop getShopMembers2(@Param("shopId") Long shopId);
}

测试类：MemberDaoTest.class

package com.sc.dao;

import com.sc.config.MyBatisUtils;
import com.sc.pojo.TdyMemberInfo;
import com.sc.pojo.TdyShop;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.List;

public class MemberDaoTest {


    //一对多
    @Test
    public void getShopMembers(){
        SqlSession sqlSession =  MyBatisUtils.getSqlSession();
        TdyShopMapper mapper = sqlSession.getMapper(TdyShopMapper.class);
        TdyShop  shop = mapper.getShopMembers(new Long(4463798));
        System.out.println(shop);
        List<TdyMemberInfo> memberList = shop.getMemberList();
        for(TdyMemberInfo memberInfo:memberList){
            System.out.println(memberInfo);
        }
        sqlSession.close();
    }


    //一对多
    @Test
    public void getShopMembers2(){
        SqlSession sqlSession =  MyBatisUtils.getSqlSession();
        TdyShopMapper mapper = sqlSession.getMapper(TdyShopMapper.class);
        TdyShop  shop = mapper.getShopMembers2(new Long(4463798));
        System.out.println(shop);
        List<TdyMemberInfo> memberList = shop.getMemberList();
        for(TdyMemberInfo memberInfo:memberList){
            System.out.println(memberInfo);
        }
        sqlSession.close();
    }

}

输出结果：

image.png

Maven项目集成Mybatis 一对多、多对一查询嵌套&结
MyBatis在映射文件中加载关联对象的关系主要通过两种方式:查询嵌套、结果嵌套查询嵌套:类似于子查询，它需要执行...
PageHelper插件一对多查询时的分页问题
项目中经常会使用到一对多的查询场景，但是PageHelper对这种嵌套查询的支持不够，如果是一对多的列表查询，返回...
SpringBoot+Spring JPA基础使用
Spring JPA集成基础查询实例复杂查询+分页一对多查询及多对多查询 1. Spring JPA集成 1.1 ...
mybatis一对多查询
association联合，是一对一查询
【Mybatis】一对多查询
如果有顺序要求，千万不要用set 与Phoenix不同，在mysql中查询的结果不会按照id默认排序。所以如果页面...
mybatis
mybatis 高级映射一对一一对多 mybatis generator 的使用多个条件 or 连接查询但...
SSM框架的常用注解整理
一.mybatis 1 配置一对多查询和多对多查询的注解方式映射关系： @Results：声明映射关系的配置 Va...
2018-07-09
项目 1.天友负责用户、角色、权限模块高层模块（户型新建、户型导入、mybatis一对多查询）前后端分离项目...
09|第九课：一对一以及一对多关联查询详解
一、历史回顾（一）、关联查询 1、大多框架都分别讲解一对一一对多多对一多对多 2、Mybatis只有两种...
mybatis一对多、延迟查询
参考https://blog.csdn.net/ncepu_Chen/article/details/974248...