問(wèn)題：

我需要對(duì)一個(gè)文章信息+類型名稱，它們分別屬于不同的表，所以需要進(jìn)行多表關(guān)聯(lián)，再其次我需要查詢的數(shù)據(jù)進(jìn)行分頁(yè)，所以我們還需要加入分頁(yè)的相關(guān)條件。

兩個(gè)實(shí)體類：

@Data
@EqualsAndHashCode(callSuper = false)
@ApiModel(value="ArticleInfo對(duì)象", 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 = "文章標(biāo)題")
    private String title;

    @ApiModelProperty(value = "文章簡(jiǎn)介，默認(rèn)100個(gè)漢字以內(nèi)")
    private String summary;

    @ApiModelProperty(value = "文章是否置頂，0為否，1為是")
    private Integer isTop;

    @ApiModelProperty(value = "圖片地址")
    private String pictureUrl;

    @ApiModelProperty(value = "文章訪問(wèn)量")
    private Integer traffic;

    @ApiModelProperty(value = "創(chuàng)建時(shí)間")
    @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對(duì)象", 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 = "該分類下的文章數(shù)量")
    private Integer num;

    @ApiModelProperty(value = "默認(rèn)類型圖片地址")
    private String prticleUrl;

    @ApiModelProperty(value = "分類排序")
    private Integer orderNum;

    @ApiModelProperty(value = "是否有效，默認(rèn)為1為有效，0為無(wú)效")
    private Boolean isEffective;

    @ApiModelProperty(value = "分類創(chuàng)建時(shí)間")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone = "GMT+8")
    private Date createdDate;

    @ApiModelProperty(value = "分類修改時(shí)間")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone = "GMT+8")
    private Date updatedDate;
    
}

xml的結(jié)果集定義：

<!-- 結(jié)果集 -->
    <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>
    <!-- 關(guān)聯(lián)結(jié)果集 -->
    <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對(duì)于單表提供了很多方法，但是如果想要進(jìn)行多表關(guān)聯(lián)查詢，那么我們就要去進(jìn)行自定義的查詢。

參考selectPage方法

image-20210128184743430.png

我們開(kāi)始寫一下我們自己的方法

image-20210128184949102.png

<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>

接下來(lái)開(kāi)始進(jìn)行測(cè)試

思路是，文章信息類作為實(shí)體參數(shù)，配個(gè)分頁(yè)信息進(jìn)行查詢

 @Test
    public void myPageQueryTest2(){
        //參數(shù)信息
        ArticleInfo articleInfo = new ArticleInfo();
        articleInfo.setCategoryId(1);
        articleInfo.setSummary("摘要1");
        QueryWrapper<ArticleInfo> queryWrapper = new QueryWrapper<ArticleInfo>(articleInfo);
        //分頁(yè)信息
        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());
        });
    }

按照預(yù)想，實(shí)體內(nèi)參數(shù)應(yīng)該可以自動(dòng)生成條件語(yǔ)句，${ew.sqlSegment}拼接在SQL中。

但是結(jié)果是，并沒(méi)有如預(yù)想中拼接在后面：

image-20210128185947434.png

我開(kāi)始以為我自己寫錯(cuò)了，然后我替換成selectPage，MyBatisPlus自帶的分頁(yè)查詢，開(kāi)始實(shí)驗(yàn)。

結(jié)果是可行的，下面圖片中就只有查詢的方法改變了

image-20210128190145621.png

然后呢，是不是我自定義寫錯(cuò)了，我不傳入實(shí)體類，傳入表達(dá)式。

結(jié)果是可行的

image-20210128190433352.png

那么問(wèn)題來(lái)了？為什么傳入實(shí)體類作為參數(shù)，自定義的方法中為何不能拼接成sql呢？

這個(gè)有待研究~

總結(jié)：思路一暫時(shí)行不通

思路二：

純mybatis的形勢(shì)，咱們不使用mybatis的分頁(yè)插件，自將分頁(yè)的參數(shù)、條件對(duì)象一一對(duì)應(yīng)去寫。
（這里的page 需要進(jìn)行 page = (page-1)*size 轉(zhuǎn)換）
接口：

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>

測(cè)試代碼：

@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());
        });
    }

測(cè)試結(jié)果是可行的

==>  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)

但是這個(gè)有一點(diǎn)欠缺的是，返回的是list合集，而且是本次查詢的結(jié)果合集，我們分頁(yè)查詢肯定少不了總數(shù)，單純用mybatis的話，咱們要再寫一個(gè)查詢總數(shù)的方法，進(jìn)行查詢。我們使用MyBatisp的分頁(yè)插件的時(shí)候，已經(jīng)直接進(jìn)行了總數(shù)查詢，所以我們就直接二者結(jié)合來(lái)用。

思路三：

MyBatisPlus的分頁(yè)插件+實(shí)體傳參的形式

接口：

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>

測(cè)試代碼：

 @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());
        });
    }

測(cè)試結(jié)果可行，進(jìn)行了兩次查詢，一次總數(shù)一次結(jié)果，且包含了我需要的分類表數(shù)據(jù)

image-20210128192658073.png

總結(jié)

思路一參考MyBatisPlus的selectPage方法，但是目前暫時(shí)不知道為啥實(shí)體參數(shù)帶不出來(lái)，后面有時(shí)間研究一下。

思路二是純mybatis，一一對(duì)應(yīng)還是很容易明白的

思路三是偷懶的方法，省了一次查詢總數(shù)的方法