springboot+mybatis-plus实现多表联查+分页
MyBatis-Plus是一款国产的框架,优化了许多操作。
本次主要记录一下,多表联查和分页的使用。
pom文件
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.0.7.1</version>
</dependency>
配置分页插件
import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import org.springframework.boot.autoconfigure.condition.ConditionalOnClass;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
@Configuration
@ConditionalOnClass(value = {PaginationInterceptor.class})
public class MybatisPlusConfig {
@Bean
public PaginationInterceptor paginationInterceptor() {
PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
return paginationInterceptor;
}
}
定义vo类
由于我的videos视频表需要多两个属性,分别是
private String faceImage;
private String nickname;
,而这个两个属性来自于用户users表,因此创建了一个videosVo类,把上面两个属性都放在里面,具体代码如下:
@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
@ApiModel(value="Videos对象", description="视频信息表")
public class VideosVo implements Serializable {
private static final long serialVersionUID=1L;
private String id;
private String userId;
private String audioId;
private String videoDesc;
private String videoPath;
private Float videoSeconds;
private Integer videoWidth;
private Integer videoHeight;
private String coverPath;
private Long likeCounts;
private Integer status;
private Date createTime;
private String faceImage;
private String nickname;
}
创建mapper+xml
参数为分页对象,暂时不用管,后面controller层构造该对象传进去就完事了。
public interface VideosMapperCustom extends BaseMapper<VideosVo> {
// 采用注解的方式也可以@Select("SELECT * FROM fy_user u LEFT JOIN fy_role r ON u.role = r.id")
// List<UserRoleVo> selectUserListPage(Page<UserRoleVo> pagination);
Page<VideosVo> queryAllVideos(Page<VideosVo> pagination);
}
<?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.wxapp.video.mapper.VideosMapperCustom">
<select id="queryAllVideos" resultType="com.wxapp.video.vo.VideosVo" >
select v.* ,u.face_image as face_image,u.nickname as nickname from videos as v
left join users as u
on u.id=v.user_id
where 1=1
and v.status=1
order by v.create_time DESC
</select>
</mapper>
测试类
@Autowired
private VideosMapperCustom videosMapperCustom;
//测试分页+ 多表
@Test
public void test4(){
Page<VideosVo> page = new Page<>(2, 5);
Page<VideosVo> results = videosMapperCustom.queryAllVideos(page);
List<VideosVo> resultList = results.getRecords();
for (VideosVo r:resultList
) {
System.out.println("一条记录:"+r);
}
System.out.println("============================");
results.hasNext();
System.out.println("是否有下一页:"+results.hasNext());
System.out.println("当前页:"+results.getCurrent());
System.out.println("总数:"+results.getTotal());
System.out.println("getPages():"+results.getPages());
System.out.println("getOrders():"+results.getOrders());
System.out.println("getSize():"+results.getSize());
}
结果
(数据部分,过多不赘述)
是否有下一页:true
当前页:2
总数:12
getPages():3
getOrders():[]
getSize():5
page参数说明
// private List<T> records; 对象列表
// private long total; 总记录
// private long size; 每页记录数
// private long current; 当前的页数
// private List<OrderItem> orders; //和数据库列有关
// private boolean optimizeCountSql; //是否记录优化
// private boolean isSearchCount; //是否搜索