一、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 );
}