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

一、Mybatius左连接一对一查询

<--数据库字段与实体类对应关系--><--id方法名 resultMap数据库字段与实体类映射关系-->SELECTaa.*FROM`ap_article` aaLEFT JOIN ap_article_config aac ON aa.id = aac.article_idand aac.is_delete != 1and aac.is_down != 1and aa.publish_time  #{dto.minBehotTime}and aa.publish_time ]]> #{dto.maxBehotTime}and aa.channel_id = #{dto.tag}order by aa.publish_time desclimit #{dto.size}

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

@Select("select * from construction_project.subitem where pid = #{pid} and status = 1")
List 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("")
List 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 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 findItemsBySuid(String suid);

三、MyBatisPlus一对一查询

Page pageInfo=new Page<>(page,pageSize);Page dishDtoPage=new Page<>(page,pageSize);//条件构造器LambdaQueryWrapper 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 records = pageInfo.getRecords();//record,页面是上的所有数据，也就需要展现给用户的数据，获取到record，遍历record获取其中的categoryIdList 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();//获取出的分类名称赋值给dishDtodishDto.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 p = new Page();LambdaQueryWrapper 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 pageRecords = page.getRecords();//NewsNameDto需要将作者姓名和其他信息一起返回给前端，NewsNameDto前端需要的数据List 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 flavors = new ArrayList<>();private String categoryName;private Integer copies;
}

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

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！