博客系统mapper.xml的分析(2)

BlogMapper.xml

博客映射器

  • mapper基本列sql(包括tb_blog表所有的列名)
<sql id="Base_Column_List">
    blog_id, blog_title, blog_sub_url, blog_cover_image, blog_category_id, blog_category_name, 
    blog_tags, blog_status, blog_views, enable_comment, is_deleted, create_time, update_time
  </sql>
  • 博客内容列的sql
<sql id="Blob_Column_List">
    blog_content
  </sql>
  • Blog selectByPrimaryKey(Long blogId);
<!--    按主键查询博客所有的内容-->
    <select id="selectByPrimaryKey" parameterType="java.lang.Long" resultMap="ResultMapWithBLOBs">
        select
        <include refid="Base_Column_List"/>
        ,
        <include refid="Blob_Column_List"/>
        from tb_blog
        where blog_id = #{blogId,jdbcType=BIGINT} and is_deleted = 0
    </select>
  • List< Blog > findBlogList(PageQueryUtil pageUtil);
  • 分页查询博客的列表,传入参数map 返回参数map
<!--    分页查询博客的列表-->
    <select id="findBlogList" parameterType="Map" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from tb_blog
        where is_deleted=0
        <if test="keyword!=null">
            AND (blog_title like CONCAT('%',#{keyword},'%' ) or blog_category_name like CONCAT('%',#{keyword},'%' ))
        </if>
        <if test="blogStatus!=null">
            AND blog_status = #{blogStatus}
        </if>
        <if test="blogCategoryId!=null">
            AND blog_category_id = #{blogCategoryId}
        </if>
        order by blog_id desc
        <if test="start!=null and limit!=null">
            limit #{start},#{limit}
        </if>
    </select>
  • int getTotalBlogs(PageQueryUtil pageUtil);
<!--获取全部博客-->
    <select id="getTotalBlogs" parameterType="Map" resultType="int">
        select count(*) from tb_blog
        where is_deleted=0
        <if test="keyword!=null">
            AND (blog_title like CONCAT('%',#{keyword},'%' ) or blog_category_name like CONCAT('%',#{keyword},'%' ))
        </if>
        <if test="blogStatus!=null">
            AND blog_status = #{blogStatus}
        </if>
        <if test="blogCategoryId!=null">
            AND blog_category_id = #{blogCategoryId}
        </if>
    </select>
  • List< Blog> findBlogListByType(@Param("type") int type, @Param("limit") int limit);
<!--  按类型查找博客列表  -->
    <select id="findBlogListByType" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from tb_blog
        where is_deleted=0 AND blog_status = 1<!-- 发布状态的文章 -->
        <if test="type!=null and type==0">
            order by blog_views desc
        </if>
        <if test="type!=null and type==1">
            order by blog_id desc
        </if>
        limit #{limit}
    </select>
  • List< Blog> getBlogsPageByTagId(PageQueryUtil pageUtil);
<!--按标签 ID 获取博客页面-->
    <select id="getBlogsPageByTagId" parameterType="Map" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from tb_blog
        where blog_id IN (SELECT blog_id FROM tb_blog_tag_relation WHERE tag_id = #{tagId})
        AND blog_status =1 AND is_deleted=0
        order by blog_id desc
        <if test="start!=null and limit!=null">
            limit #{start},#{limit}
        </if>
    </select>
  • int getTotalBlogsByTagId(PageQueryUtil pageUtil);
<!--按标签 ID 获取博客总数-->
    <select id="getTotalBlogsByTagId" parameterType="Map" resultType="int">
        select count(*)
        from tb_blog
        where  blog_id IN (SELECT blog_id FROM tb_blog_tag_relation WHERE tag_id = #{tagId})
        AND blog_status =1 AND is_deleted=0
    </select>
  • Blog selectBySubUrl(String subUrl);
<!--按子网址选择 博客子网址-->
    <select id="selectBySubUrl" parameterType="java.lang.String" resultMap="ResultMapWithBLOBs">
        select
        <include refid="Base_Column_List"/>
        ,
        <include refid="Blob_Column_List"/>
        from tb_blog
        where blog_sub_url = #{subUrl,jdbcType=VARCHAR} and is_deleted = 0 limit 1
    </select>
  • int deleteByPrimaryKey(Long blogId);
<!--按主键删除-->
    <update id="deleteByPrimaryKey" parameterType="java.lang.Long">
    UPDATE tb_blog SET is_deleted = 1
    where blog_id = #{blogId,jdbcType=BIGINT} and is_deleted = 0
  </update>
  • int deleteBatch(Integer[] ids);
<!--    批量删除-->
    <update id="deleteBatch">
        update tb_blog
        set is_deleted=1 where blog_id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </update>
  • int insert(Blog record);
<!--    插入 大概是写入博客的方法-->
    <insert id="insert" parameterType="com.site.blog.my.core.entity.Blog">
    insert into tb_blog (blog_id, blog_title, blog_sub_url, 
      blog_cover_image, blog_category_id, blog_category_name, 
      blog_tags, blog_status, blog_views, 
      enable_comment, is_deleted, create_time, 
      update_time, blog_content)
    values (#{blogId,jdbcType=BIGINT}, #{blogTitle,jdbcType=VARCHAR}, #{blogSubUrl,jdbcType=VARCHAR}, 
      #{blogCoverImage,jdbcType=VARCHAR}, #{blogCategoryId,jdbcType=INTEGER}, #{blogCategoryName,jdbcType=VARCHAR}, 
      #{blogTags,jdbcType=VARCHAR}, #{blogStatus,jdbcType=TINYINT}, #{blogViews,jdbcType=BIGINT}, 
      #{enableComment,jdbcType=TINYINT}, #{isDeleted,jdbcType=TINYINT}, #{createTime,jdbcType=TIMESTAMP}, 
      #{updateTime,jdbcType=TIMESTAMP}, #{blogContent,jdbcType=LONGVARCHAR})
  </insert>
  • int insertSelective(Blog record);
<!--    插入选择性-->
    <insert id="insertSelective" useGeneratedKeys="true" keyProperty="blogId"
            parameterType="com.site.blog.my.core.entity.Blog">
        insert into tb_blog
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="blogId != null">
                blog_id,
            </if>
            <if test="blogTitle != null">
                blog_title,
            </if>
            <if test="blogSubUrl != null">
                blog_sub_url,
            </if>
            <if test="blogCoverImage != null">
                blog_cover_image,
            </if>
            <if test="blogCategoryId != null">
                blog_category_id,
            </if>
            <if test="blogCategoryName != null">
                blog_category_name,
            </if>
            <if test="blogTags != null">
                blog_tags,
            </if>
            <if test="blogStatus != null">
                blog_status,
            </if>
            <if test="blogViews != null">
                blog_views,
            </if>
            <if test="enableComment != null">
                enable_comment,
            </if>
            <if test="isDeleted != null">
                is_deleted,
            </if>
            <if test="createTime != null">
                create_time,
            </if>
            <if test="updateTime != null">
                update_time,
            </if>
            <if test="blogContent != null">
                blog_content,
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="blogId != null">
                #{blogId,jdbcType=BIGINT},
            </if>
            <if test="blogTitle != null">
                #{blogTitle,jdbcType=VARCHAR},
            </if>
            <if test="blogSubUrl != null">
                #{blogSubUrl,jdbcType=VARCHAR},
            </if>
            <if test="blogCoverImage != null">
                #{blogCoverImage,jdbcType=VARCHAR},
            </if>
            <if test="blogCategoryId != null">
                #{blogCategoryId,jdbcType=INTEGER},
            </if>
            <if test="blogCategoryName != null">
                #{blogCategoryName,jdbcType=VARCHAR},
            </if>
            <if test="blogTags != null">
                #{blogTags,jdbcType=VARCHAR},
            </if>
            <if test="blogStatus != null">
                #{blogStatus,jdbcType=TINYINT},
            </if>
            <if test="blogViews != null">
                #{blogViews,jdbcType=BIGINT},
            </if>
            <if test="enableComment != null">
                #{enableComment,jdbcType=TINYINT},
            </if>
            <if test="isDeleted != null">
                #{isDeleted,jdbcType=TINYINT},
            </if>
            <if test="createTime != null">
                #{createTime,jdbcType=TIMESTAMP},
            </if>
            <if test="updateTime != null">
                #{updateTime,jdbcType=TIMESTAMP},
            </if>
            <if test="blogContent != null">
                #{blogContent,jdbcType=LONGVARCHAR},
            </if>
        </trim>
    </insert>
  • int updateByPrimaryKeySelective(Blog record);
<!--    按主键选择性更新-->
    <update id="updateByPrimaryKeySelective" parameterType="com.site.blog.my.core.entity.Blog">
        update tb_blog
        <set>
            <if test="blogTitle != null">
                blog_title = #{blogTitle,jdbcType=VARCHAR},
            </if>
            <if test="blogSubUrl != null">
                blog_sub_url = #{blogSubUrl,jdbcType=VARCHAR},
            </if>
            <if test="blogCoverImage != null">
                blog_cover_image = #{blogCoverImage,jdbcType=VARCHAR},
            </if>
            <if test="blogContent != null">
                blog_content = #{blogContent,jdbcType=LONGVARCHAR},
            </if>
            <if test="blogCategoryId != null">
                blog_category_id = #{blogCategoryId,jdbcType=INTEGER},
            </if>
            <if test="blogCategoryName != null">
                blog_category_name = #{blogCategoryName,jdbcType=VARCHAR},
            </if>
            <if test="blogTags != null">
                blog_tags = #{blogTags,jdbcType=VARCHAR},
            </if>
            <if test="blogStatus != null">
                blog_status = #{blogStatus,jdbcType=TINYINT},
            </if>
            <if test="blogViews != null">
                blog_views = #{blogViews,jdbcType=BIGINT},
            </if>
            <if test="enableComment != null">
                enable_comment = #{enableComment,jdbcType=TINYINT},
            </if>
            <if test="isDeleted != null">
                is_deleted = #{isDeleted,jdbcType=TINYINT},
            </if>
            <if test="createTime != null">
                create_time = #{createTime,jdbcType=TIMESTAMP},
            </if>
            <if test="updateTime != null">
                update_time = #{updateTime,jdbcType=TIMESTAMP},
            </if>
            <if test="blogContent != null">
                blog_content = #{blogContent,jdbcType=LONGVARCHAR},
            </if>
        </set>
        where blog_id = #{blogId,jdbcType=BIGINT}
    </update>
  • int updateByPrimaryKeyWithBLOBs(Blog record);
<!--    使用 BLO Bs 按主键更新-->
    <update id="updateByPrimaryKeyWithBLOBs" parameterType="com.site.blog.my.core.entity.Blog">
    update tb_blog
    set blog_title = #{blogTitle,jdbcType=VARCHAR},
      blog_sub_url = #{blogSubUrl,jdbcType=VARCHAR},
      blog_cover_image = #{blogCoverImage,jdbcType=VARCHAR},
      blog_category_id = #{blogCategoryId,jdbcType=INTEGER},
      blog_category_name = #{blogCategoryName,jdbcType=VARCHAR},
      blog_tags = #{blogTags,jdbcType=VARCHAR},
      blog_status = #{blogStatus,jdbcType=TINYINT},
      blog_views = #{blogViews,jdbcType=BIGINT},
      enable_comment = #{enableComment,jdbcType=TINYINT},
      is_deleted = #{isDeleted,jdbcType=TINYINT},
      create_time = #{createTime,jdbcType=TIMESTAMP},
      update_time = #{updateTime,jdbcType=TIMESTAMP},
      blog_content = #{blogContent,jdbcType=LONGVARCHAR}
    where blog_id = #{blogId,jdbcType=BIGINT}
  </update>
  • int updateByPrimaryKey(Blog record);
<!--    按主键更新-->
    <update id="updateByPrimaryKey" parameterType="com.site.blog.my.core.entity.Blog">
    update tb_blog
    set blog_title = #{blogTitle,jdbcType=VARCHAR},
      blog_sub_url = #{blogSubUrl,jdbcType=VARCHAR},
      blog_cover_image = #{blogCoverImage,jdbcType=VARCHAR},
      blog_category_id = #{blogCategoryId,jdbcType=INTEGER},
      blog_category_name = #{blogCategoryName,jdbcType=VARCHAR},
      blog_tags = #{blogTags,jdbcType=VARCHAR},
      blog_status = #{blogStatus,jdbcType=TINYINT},
      blog_views = #{blogViews,jdbcType=BIGINT},
      enable_comment = #{enableComment,jdbcType=TINYINT},
      is_deleted = #{isDeleted,jdbcType=TINYINT},
      create_time = #{createTime,jdbcType=TIMESTAMP},
      update_time = #{updateTime,jdbcType=TIMESTAMP}
    where blog_id = #{blogId,jdbcType=BIGINT}
  </update>
  • int updateBlogCategorys(@Param("categoryName") String categoryName, @Param("categoryId") Integer categoryId, @Param("ids")Integer[] ids);
<!--    更新博客类别-->
    <update id="updateBlogCategorys">
        update tb_blog
        set blog_category_id = #{categoryId,jdbcType=INTEGER},
        blog_category_name = #{categoryName,jdbcType=VARCHAR}
        where blog_category_id in
        <foreach item="id" collection="ids" open="(" separator="," close=")">
            #{id}
        </foreach>
        and is_deleted =0
    </update>
  • 博客实体类
public class Blog {
    private Long blogId;

    private String blogTitle;

    private String blogSubUrl;

    private String blogCoverImage;

    private Integer blogCategoryId;

    private String blogCategoryName;

    private String blogTags;

    private Byte blogStatus;

    private Long blogViews;

    private Byte enableComment;

    private Byte isDeleted;

    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    private Date createTime;

    private Date updateTime;

    private String blogContent;

    public Long getBlogId() {
        return blogId;
    }

    public void setBlogId(Long blogId) {
        this.blogId = blogId;
    }

    public String getBlogTitle() {
        return blogTitle;
    }

    public void setBlogTitle(String blogTitle) {
        this.blogTitle = blogTitle == null ? null : blogTitle.trim();
    }

    public String getBlogSubUrl() {
        return blogSubUrl;
    }

    public void setBlogSubUrl(String blogSubUrl) {
        this.blogSubUrl = blogSubUrl == null ? null : blogSubUrl.trim();
    }

    public String getBlogCoverImage() {
        return blogCoverImage;
    }

    public void setBlogCoverImage(String blogCoverImage) {
        this.blogCoverImage = blogCoverImage == null ? null : blogCoverImage.trim();
    }

    public Integer getBlogCategoryId() {
        return blogCategoryId;
    }

    public void setBlogCategoryId(Integer blogCategoryId) {
        this.blogCategoryId = blogCategoryId;
    }

    public String getBlogCategoryName() {
        return blogCategoryName;
    }

    public void setBlogCategoryName(String blogCategoryName) {
        this.blogCategoryName = blogCategoryName == null ? null : blogCategoryName.trim();
    }

    public String getBlogTags() {
        return blogTags;
    }

    public void setBlogTags(String blogTags) {
        this.blogTags = blogTags == null ? null : blogTags.trim();
    }

    public Byte getBlogStatus() {
        return blogStatus;
    }

    public void setBlogStatus(Byte blogStatus) {
        this.blogStatus = blogStatus;
    }

    public Long getBlogViews() {
        return blogViews;
    }

    public void setBlogViews(Long blogViews) {
        this.blogViews = blogViews;
    }

    public Byte getEnableComment() {
        return enableComment;
    }

    public void setEnableComment(Byte enableComment) {
        this.enableComment = enableComment;
    }

    public Byte getIsDeleted() {
        return isDeleted;
    }

    public void setIsDeleted(Byte isDeleted) {
        this.isDeleted = isDeleted;
    }

    public Date getCreateTime() {
        return createTime;
    }

    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }

    public Date getUpdateTime() {
        return updateTime;
    }

    public void setUpdateTime(Date updateTime) {
        this.updateTime = updateTime;
    }

    public String getBlogContent() {
        return blogContent;
    }

    public void setBlogContent(String blogContent) {
        this.blogContent = blogContent == null ? null : blogContent.trim();
    }

    @Override
    public String toString() {
        StringBuilder sb = new StringBuilder();
        sb.append(getClass().getSimpleName());
        sb.append(" [");
        sb.append("Hash = ").append(hashCode());
        sb.append(", blogId=").append(blogId);
        sb.append(", blogTitle=").append(blogTitle);
        sb.append(", blogSubUrl=").append(blogSubUrl);
        sb.append(", blogCoverImage=").append(blogCoverImage);
        sb.append(", blogCategoryId=").append(blogCategoryId);
        sb.append(", blogCategoryName=").append(blogCategoryName);
        sb.append(", blogTags=").append(blogTags);
        sb.append(", blogStatus=").append(blogStatus);
        sb.append(", blogViews=").append(blogViews);
        sb.append(", enableComment=").append(enableComment);
        sb.append(", isDeleted=").append(isDeleted);
        sb.append(", createTime=").append(createTime);
        sb.append(", updateTime=").append(updateTime);
        sb.append(", blogContent=").append(blogContent);
        sb.append("]");
        return sb.toString();
    }
}
  • 页面查询工具
public class PageQueryUtil extends LinkedHashMap<String, Object> {
    //当前页码
    private int page;
    //每页条数
    private int limit;

    public PageQueryUtil(Map<String, Object> params) {
        this.putAll(params);

        //分页参数
        this.page = Integer.parseInt(params.get("page").toString());
        this.limit = Integer.parseInt(params.get("limit").toString());
        this.put("start", (page - 1) * limit);
        this.put("page", page);
        this.put("limit", limit);
    }


    public int getPage() {
        return page;
    }

    public void setPage(int page) {
        this.page = page;
    }

    public int getLimit() {
        return limit;
    }

    public void setLimit(int limit) {
        this.limit = limit;
    }

    @Override
    public String toString() {
        return "PageUtil{" +
                "page=" + page +
                ", limit=" + limit +
                '}';
    }
}