数据连表表查询的几种方式_querywrapper连表查询

一、Mybatius左连接一对一查询

<?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.bigmi.article.mapper.ArticleMapper">
        
    <resultMap id="resultMap" type="com.bigmi.model.article.pojo.ApArticle">
     <--数据库字段与实体类对应关系-->
        <id column="id" property="id"/>
        <result column="title" property="title"/>
        <result column="author_id" property="authorId"/>
        <result column="author_name" property="authorName"/>
        <result column="channel_id" property="channelId"/>
        <result column="channel_name" property="channelName"/>
        <result column="layout" property="layout"/>
        <result column="flag" property="flag"/>
        <result column="images" property="images"/>
        <result column="labels" property="labels"/>
        <result column="likes" property="likes"/>
        <result column="collection" property="collection"/>
        <result column="comment" property="comment"/>
        <result column="views" property="views"/>
        <result column="province_id" property="provinceId"/>
        <result column="city_id" property="cityId"/>
        <result column="county_id" property="countyId"/>
        <result column="created_time" property="createdTime"/>
        <result column="publish_time" property="publishTime"/>
        <result column="sync_status" property="syncStatus"/>
        <result column="static_url" property="staticUrl"/>
    </resultMap>
    <--id方法名 resultMap数据库字段与实体类映射关系-->
    <select id="articleList" resultMap="resultMap">
        SELECT
        aa.*
        FROM
        `ap_article` aa
        LEFT JOIN ap_article_config aac ON aa.id = aac.article_id
        <where>
            and aac.is_delete != 1
            and aac.is_down != 1
            <!-- loadmore -->
            <if test="type != null and type == 1">
                and aa.publish_time <![CDATA[<]]> #{dto.minBehotTime}
            </if>
            <if test="type != null and type == 2">
                and aa.publish_time <![CDATA[>]]> #{dto.maxBehotTime}
            </if>
            <if test="dto.tag != '__all__'">
                and aa.channel_id = #{dto.tag}
            </if>
        </where>
        order by aa.publish_time desc
        limit #{dto.size}
    </select>

二、级联查询（注解开发）一对一查询和一对多

@Select("select * from construction_project.subitem where pid = #{pid} and status = 1")
List<Subitem> findSubitemsByPid(String pid);
@Results(id = "subMap",value = {
<--数据库与实体类对应字段-->
        @Result(column = "suid",property = "suid"),
        @Result(column = "sname",property = "sname"),
        @Result(column = "starttime",property = "starttime"),
        @Result(column = "endtime",property = "endtime"),
        @Result(column = "pid",property = "pid"),
        @Result(column = "updatetime",property = "updatetime"),
        @Result(column = "status",property = "status"),
        <--一对一查询 project是一对一对应的实体类 在suid在的实体类中创建属性 property为实体类中对应的属性名-->
        @Result(column = "pid",property = "project",javaType = Project.class,one =
        <--对一对一需要的条件路径，在相应的mapper中创建条件-->
                @One(select = "com.ioc.mapper.ProjectMapper.findProjectByPid",fetchType = FetchType.EAGER)),
          <--一对多查询suid是当前实体类id，需要在当前实体类中创建出一个list集合 property为实体类中对应的list集合名-->
        @Result(column = "suid",property = "items",many =
                @Many(select = "com.ioc.mapper.ItemMapper.findItemsBySuid",fetchType = FetchType.EAGER)),
})
<--查询对应的sql条件语句-->
@Select("<script> select * from construction_project.subitem " +
        "<where> status = 1 " +
        "<if test=\"queryString != null and queryString != ''\">" +
        "and sname like concat('%',#{queryString},'%')" +
        "or suid = #{queryString}"+
        "</if>" +
        "</where>" +
        " limit #{currentPage},#{pageSize}"+
        "</script>")
List<Subitem> findAllSubitems(@Param("currentPage") int currentPage,@Param("pageSize") int pageSize,@Param("queryString") String queryString);

public class Subitem {
//数据库中对应字段
  private String suid;
  private String sname;
  private Date starttime;
  private Date endtime;
  private long pid;
  private Date updatetime;
  private long status;
 
//一对一用到的对应实体类字段
  private Project project;
 //一对多需要的集合，集合中存放需要对应的数据，泛型为实体类
  private List<Item> items;
}

一对一查询对应子表条件
    com.ioc.mapper.ProjectMapper.findProjectByPid
@Select("select * from construction_project.project where pid =#{pid} and status=1")
Project findProjectByPid(int pid);

一对多查询对应子表条件
    com.ioc.mapper.ItemMapper.findItemsBySuid
@Select("select * from construction_project.item where suid = #{suid} and status = 1")
List<Item> findItemsBySuid(String suid);

三、MyBatisPlus一对一查询

Page<Dish> pageInfo=new Page<>(page,pageSize);
    Page<DishDto> dishDtoPage=new Page<>(page,pageSize);
    //条件构造器
    LambdaQueryWrapper<Dish> queryWrapper=new LambdaQueryWrapper<>();
    queryWrapper.like( name!=null,Dish::getName,name );
    //添加排序条件(根据更新时间降序排列)
    queryWrapper.orderByDesc( Dish::getUpdateTime );
    //执行查询
    dishService.page( pageInfo,queryWrapper );
    //对象拷贝(忽略record)
    BeanUtils.copyProperties( pageInfo,dishDtoPage,"records" );
    List<Dish> records = pageInfo.getRecords();
    //record,页面是上的所有数据，也就需要展现给用户的数据，获取到record，遍历record获取其中的categoryId
    List<DishDto> list= records.stream().map( (item)->{
    //DishDto就是需要返回给前端的数据,进行下方对数据的处理
        DishDto dishDto=new DishDto();
        //将item拷贝到dishDto中
        BeanUtils.copyProperties( item,dishDto );
        Long categoryId = item.getCategoryId();//分类id
        //根据id查询分类对象
        Category category = categoryService.getById( categoryId );
        if ( category!=null ){
            //获取分类名称
            String categoryName = category.getName();
            //获取出的分类名称赋值给dishDto
            dishDto.setCategoryName( categoryName );
        }
        return dishDto;
    } ).collect( Collectors.toList() );
    dishDtoPage.setRecords( list );
    return R.success( dishDtoPage );
}

if (newsAuthDto ==null){
        return ResponseResult.errorResult(AppHttpCodeEnum.DATA_NOT_EXIST);
    }
    //分页查询
    IPage page =new Page(newsAuthDto.getPage(),newsAuthDto.getSize());
    Page<NewsNameDto> p = new Page();
    LambdaQueryWrapper<WmNews> queryWrapper =new LambdaQueryWrapper<>();
    //模糊查询
    if (StringUtils.isNotBlank(newsAuthDto.getTitle())){
        queryWrapper.like(WmNews::getTitle,newsAuthDto.getTitle());
    }
    //条件查询全部
    if (newsAuthDto.getStatus() != null){
        queryWrapper.eq(WmNews::getStatus,newsAuthDto.getStatus());
    }
    queryWrapper.orderByDesc(WmNews::getSubmitedTime);
    //查询作者
    page =page(page,queryWrapper);
    BeanUtils.copyProperties(page,p,"records");
    List<WmNews> pageRecords = page.getRecords();
    //NewsNameDto需要将作者姓名和其他信息一起返回给前端，NewsNameDto前端需要的数据
    List<NewsNameDto> newsNameDtoList = pageRecords.stream().map((item) ->{
        NewsNameDto newsNameDto = new NewsNameDto();
        //将数据库实体类WmNews复制到前端需要的数据类中
        BeanUtils.copyProperties(item,newsNameDto,"userId");
        //查寻需要的数据，进行处理
        WmUser wmUser = wmUserMapper.selectById(item.getUserId());
        newsNameDto.setAuthorName(wmUser.getName());
        return newsNameDto;
    }).collect(Collectors.toList());
    PageResponseResult responseResult = new PageResponseResult(newsAuthDto.getPage(), newsAuthDto.getSize(), (int) page.getTotal());
    responseResult.setData(newsNameDtoList);
    return responseResult;
}

四、MyBatisPlus一对多查询

对需要进行子表的数据封装至list集合中，泛型为实体类
public class DishDto extends Dish {
    //菜品对应的口味数据
    private List<DishFlavor> flavors = new ArrayList<>();
 
    private String categoryName;
 
    private Integer copies;
}

if(dishDtoList != null){
        //如果存在，直接返回，无需查询数据库
        return R.success(dishDtoList);
    }
    //构造查询条件
    LambdaQueryWrapper<Dish> queryWrapper=new LambdaQueryWrapper<>();
    queryWrapper.eq( dish.getCategoryId()!=null,Dish::getCategoryId,dish.getCategoryId() );
    queryWrapper.eq( Dish::getStatus,1 );
    //添加排序条件
    queryWrapper.orderByAsc( Dish::getSort ).orderByDesc( Dish::getUpdateTime );
    List<Dish> list = dishService.list( queryWrapper );
     dishDtoList= list.stream().map( (item)->{
         //给前端返回dishDto中信息
        DishDto dishDto=new DishDto();
        BeanUtils.copyProperties( item,dishDto );
        //获取分类id
        Long categoryId = item.getCategoryId();
        //查询相应的分类
        Category category = categoryService.getById( categoryId );
        if ( category!=null ){
            String categoryName = category.getName();
            dishDto.setCategoryName( categoryName );
        }
        Long dishId = item.getId();
        LambdaQueryWrapper<DishFlavor> wrapper=new LambdaQueryWrapper<>();
        //更具餐品id获取到口味
        wrapper.eq( DishFlavor::getDishId,dishId );
        //将查到的数据存入实体类集合中
        List<DishFlavor> dishFlavors = dishFlavorService.list( wrapper );
        dishDto.setFlavors( dishFlavors );
        return dishDto;
    } ).collect( Collectors.toList() );
    
    return R.success( dishDtoList );
}