MyBatisPlus多表自定义分页查询
问题:
我需要对一个文章信息+类型名称,它们分别属于不同的表,所以需要进行多表关联,再其次我需要查询的数据进行分页,所以我们还需要加入分页的相关条件。
两个实体类:
@Data@EqualsAndHashCode(callSuper = false)@ApiModel(value="ArticleInfo对象", description="文章信息")public class ArticleInfo implements Serializable { private static final long serialVersionUID=1L; @ApiModelProperty(value = "主键ID") @TableId(value = "id", type = IdType.AUTO) private Integer id; @ApiModelProperty(value = "文章类型ID") private Integer categoryId; @ApiModelProperty(value = "文章标题") private String title; @ApiModelProperty(value = "文章简介,默认100个汉字以内") private String summary; @ApiModelProperty(value = "文章是否置顶,0为否,1为是") private Integer isTop; @ApiModelProperty(value = "图片地址") private String pictureUrl; @ApiModelProperty(value = "文章访问量") private Integer traffic; @ApiModelProperty(value = "创建时间") @JsonFormat(pattern = "yyyy-MM-dd",timezone = "Asia/Shanghai") private Date createdDate; @ApiModelProperty(value = "修改日期") @JsonFormat(pattern = "yyyy-MM-dd",timezone = "Asia/Shanghai") private Date updatedDate; @TableField(select = false) private Category category;}
@Data@EqualsAndHashCode(callSuper = false)@ApiModel(value="Category对象", description="类型")public class Category implements Serializable { private static final long serialVersionUID=1L; @ApiModelProperty(value = "主键id") @TableId(value = "id", type = IdType.AUTO) private Integer id; @ApiModelProperty(value = "分类名称") private String name; @ApiModelProperty(value = "该分类下的文章数量") private Integer num; @ApiModelProperty(value = "默认类型图片地址") private String prticleUrl; @ApiModelProperty(value = "分类排序") private Integer orderNum; @ApiModelProperty(value = "是否有效,默认为1为有效,0为无效") private Boolean isEffective; @ApiModelProperty(value = "分类创建时间") @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone = "GMT+8") private Date createdDate; @ApiModelProperty(value = "分类修改时间") @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone = "GMT+8") private Date updatedDate; }
xml的结果集定义:
<!-- 结果集 --> <resultMap id="BaseResultMap" type="com.jz.blog.entity.ArticleInfo"> <id column="id" property="id" jdbcType="INTEGER"/> <result column="category_id" property="categoryId" jdbcType="INTEGER"/> <result column="title" property="title" jdbcType="VARCHAR"/> <result column="summary" property="summary" jdbcType="VARCHAR"/> <result column="is_top" property="isTop" jdbcType="INTEGER"/> <result column="picture_url" property="pictureUrl" jdbcType="VARCHAR"/> <result column="traffic" property="traffic" jdbcType="INTEGER"/> <result column="created_date" property="createdDate" jdbcType="TIMESTAMP"/> <result column="updated_date" property="updatedDate" jdbcType="TIMESTAMP"/> </resultMap> <!-- 关联结果集 --> <resultMap id="AllArticleInfoWithCategory" type="com.jz.blog.entity.ArticleInfo" extends="BaseResultMap"> <association property="category" javaType="com.jz.blog.entity.Category"> <id column="cid" property="id"/> <result column="cname" property="name" jdbcType="VARCHAR"/> </association> </resultMap>
思路一:
MyBatisPlus对于单表提供了很多方法,但是如果想要进行多表关联查询,那么我们就要去进行自定义的查询。
参考selectPage方法
image-20210128184743430.png
我们开始写一下我们自己的方法
image-20210128184949102.png
对应的xml信息
<select id="selectPage2" resultMap="AllArticleInfoWithCategory"> SELECT a.*, c.id AS cid, c.name AS cname FROM article_info a, category c WHERE a.category_id = c.id <if test="ew.sqlSegment != null and ew.sqlSegment != ''"> AND ${ew.sqlSegment} </if> </select>
接下来开始进行测试
思路是,文章信息类作为实体参数,配个分页信息进行查询
@Test public void myPageQueryTest2(){ //参数信息 ArticleInfo articleInfo = new ArticleInfo(); articleInfo.setCategoryId(1); articleInfo.setSummary("摘要1"); QueryWrapper<ArticleInfo> queryWrapper = new QueryWrapper<ArticleInfo>(articleInfo); //分页信息 Page<ArticleInfo> page = new Page<ArticleInfo>(1,10); IPage<ArticleInfo> iPage = articleInfoMapper.selectPage2(page,queryWrapper); System.out.println(iPage.getRecords().size()); List<ArticleInfo> lists = iPage.getRecords(); lists.forEach(list ->{ System.out.println(list.toString()); }); }
按照预想,实体内参数应该可以自动生成条件语句,${ew.sqlSegment}拼接在SQL中。
但是结果是,并没有如预想中拼接在后面:
image-20210128185947434.png
我开始以为我自己写错了,然后我替换成selectPage,MyBatisPlus自带的分页查询,开始实验。
结果是可行的,下面图片中就只有查询的方法改变了
image-20210128190145621.png
然后呢,是不是我自定义写错了,我不传入实体类,传入表达式。
结果是可行的
image-20210128190433352.png
那么问题来了?为什么传入实体类作为参数,自定义的方法中为何不能拼接成sql呢?
这个有待研究~
总结:思路一暂时行不通
思路二:
纯mybatis的形势,咱们不使用mybatis的分页插件,自将分页的参数、条件对象一一对应去写。
(这里的page 需要进行 page = (page-1)*size 转换)
接口:
image-20210128191855997.png
xml:
<select id="mySelectPage" resultMap="AllArticleInfoWithCategory"> SELECT a.*, c.id AS cid, c.name AS cname FROM article_info a, category c Where a.category_id = c.id <if test="art.title != null and art.title != ''"> and a.title like concat('%',#{art.title},'%') </if> <if test="art.summary != null and art.summary != ''"> and a.summary like concat('%',#{art.summary},'%') </if> <if test="art.categoryId != null and art.categoryId != ''"> and a.category_id = #{art.categoryId} </if> <if test="page !=null and size!=null"> limit #{page},#{size} </if> </select>
测试代码:
@Test public void myPageQueryTest3(){ ArticleInfo articleInfo = new ArticleInfo(); articleInfo.setCategoryId(1); articleInfo.setSummary("摘要1"); List<ArticleInfo> lists = articleInfoMapper.mySelectPage(1,10,articleInfo); System.out.println(lists.size()); lists.forEach(list ->{ System.out.println(list.toString()); }); }
测试结果是可行的
==> Preparing: SELECT a.*, c.id AS cid, c.name AS cname FROM article_info a, category c Where a.category_id = c.id and a.summary like concat('%',?,'%') and a.category_id = ? limit ?,? ==> Parameters: 摘要1(String), 1(Integer), 1(Integer), 10(Integer)
但是这个有一点欠缺的是,返回的是list合集,而且是本次查询的结果合集,我们分页查询肯定少不了总数,单纯用mybatis的话,咱们要再写一个查询总数的方法,进行查询。我们使用MyBatisp的分页插件的时候,已经直接进行了总数查询,所以我们就直接二者结合来用。
思路三:
MyBatisPlus的分页插件+实体传参的形式
接口:
image-20210128192431106.png
xml:
<select id="selectPageWithCategory" resultMap="AllArticleInfoWithCategory"> SELECT a.*, c.id AS cid, c.name AS cname FROM article_info a, category c Where a.category_id = c.id <if test="art.title != null and art.title != ''"> and a.title like concat('%',#{art.title},'%') </if> <if test="art.summary != null and art.summary != ''"> and a.summary like concat('%',#{art.summary},'%') </if> <if test="art.categoryId != null and art.categoryId != ''"> and a.category_id = #{art.categoryId} </if> order by a.id desc </select>
测试代码:
@Test public void myPageQueryTest(){ ArticleInfo articleInfo = new ArticleInfo(); articleInfo.setCategoryId(1); articleInfo.setSummary("摘要1"); Page<ArticleInfo> page = new Page<ArticleInfo>(1,10); IPage<ArticleInfo> iPage = articleInfoMapper.selectPageWithCategory(page,articleInfo); System.out.println(iPage.getRecords().size()); List<ArticleInfo> lists = iPage.getRecords(); lists.forEach(list ->{ System.out.println(list.getCategory().getName()); }); }
测试结果可行,进行了两次查询,一次总数一次结果,且包含了我需要的分类表数据
image-20210128192658073.png
总结
思路一参考MyBatisPlus的selectPage方法,但是目前暂时不知道为啥实体参数带不出来,后面有时间研究一下。
思路二是纯mybatis,一一对应还是很容易明白的
思路三是偷懒的方法,省了一次查询总数的方法
作者:阿库西斯教长
链接:https://www.jianshu.com/p/97500d484b1f