一、Mybatius左连接一对一查询

<--数据库字段与实体类对应关系-->

<--id方法名 resultMap数据库字段与实体类映射关系-->

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

@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获取其中的categoryId

List 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 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 );

//获取分类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 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 );

}

参考文章

评论可见，请评论后查看内容，谢谢！！！
 您阅读本篇文章共花了：