博客项目（三）——Mapper接口与其映射

最新推荐文章于 2023-11-17 22:43:36 发布

WYFVV

最新推荐文章于 2023-11-17 22:43:36 发布

阅读量737

点赞数

本文链接：https://blog.csdn.net/WYFVV/article/details/105865436

版权

一.接口与映射文件

未学习MyBatis之前，连接数据库是使用JDBC，执行SQL语句都是使用Statement 和 PreparedStatement这两个对象。在学习了MyBatis之后，学会接口对应映射文件这种方法。

1.1Article

1.1.1 接口

package com.test.ssm.blog.mapper;

import com.test.ssm.blog.entity.Article;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

import java.util.HashMap;
import java.util.List;

@Mapper
public interface ArticleMapper {

    //根据id删除
    Integer deleteById(Integer articleId);


    //添加文章
    Integer insert(Article article);

    //更新文章
    Integer update(Article article);

    //获得所有文章
    List<Article> findAll(HashMap<String,Object> criteria);

    //文章归档
    List<Article> listAllNotWithContent();

    //获取文章总数
    Integer countArticle(@Param(value = "status") Integer status);

    //获取留言总数
    Integer  countArticleComment();

    //获取浏览量总数
    Integer countArticleView();

    //获取所有文章（文章归档）
    List<Article> listArticle();

    //根据id查询用户信息
    Article getArticleByStatusAndId(@Param(value = "status") Integer status,@Param(value = "id")Integer id);

    //根据ID查找
    Article getArticleById(@Param(value = "id")Integer id);

    /**分页操作（这个需要学习）
     * status 状态
     * pageIndex 从第几页开始
     * pageSize 数量
     */
    //不鼓励使用
    @Deprecated
     List<Article> pageArticle(@Param(value = "status") Integer status,
                               @Param(value = "pageIndex") Integer pageIndex,
                               @Param(value = "pageSize") Integer pageSize);

    /**
     * 获得访问最多的文章(猜你喜欢)
     * limit 查询数量
     */
    List<Article> listArticleByViewCount(@Param(value = "limit") Integer limit);

    //获得上一篇文章
    Article getAfterArticle(@Param(value = "id") Integer id);

    //获得下一篇文章
    Article getPreArticle(@Param(value = "id") Integer id);

    /**获得随机文章
     * @param limit 查询数量
     * @return
     */

    List<Article> listRandomArticle(@Param(value = "limit") Integer limit);

    //热评文章
    List<Article> listArticleByCommentCount(@Param(value = "limit") Integer limit);

    //更新文章评论数
    void updateCommentCount(@Param(value = "articleId")Integer articleId);

    //获得最后更新的记录
    Article getLastUpdateArticle();

    //用户的文章数
    Integer countArticleByUser(@Param(value = "id")Integer id);

    //根据分类ID
    List<Article> findArticleByCategoryId(@Param(value = "categoryId")Integer categoryId,
                                         @Param("limit") Integer limit );

    //根据分类ID集合
    List<Article> findArticleByCategoryIds(@Param(value = "categoryIds")List<Integer> categoryIds,
                                          @Param("limit") Integer limit );


    //获得最新文章
    List<Article> listArticleByLimit(Integer limit);

    /**批量删除文章
     *  @param ids 文章Id列表
     */
    Integer deleteBatch(@Param(value = "ids")List<Integer> ids);


    //根据用户查询文章列表ID
    List<Integer> selectArticleIdByUserId(Integer articleUserId);


}

1.1.2 SQL文件

<?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.test.ssm.blog.mapper.ArticleMapper">
    <resultMap id="BaseResultMap" type="com.test.ssm.blog.entity.Article">
        <id column="article_id" property="articleId" jdbcType="INTEGER"/>
        <result column="article_user_id" property="articleUserId" jdbcType="INTEGER"/>
        <result column="article_title" property="articleTitle" jdbcType="VARCHAR"/>
        <result column="article_content" property="articleContent" jdbcType="LONGVARCHAR"/>
        <result column="article_summary" property="articleSummary" jdbcType="VARCHAR"/>
        <result column="article_view_count" property="articleViewCount" jdbcType="INTEGER"/>
        <result column="article_comment_count" property="articleCommentCount" jdbcType="INTEGER"/>
        <result column="article_like_count" property="articleLikeCount" jdbcType="INTEGER"/>
        <result column="article_is_comment" property="articleIsComment" jdbcType="INTEGER"/>
        <result column="article_order" property="articleOrder" jdbcType="INTEGER"/>
        <result column="article_create_time" property="articleCreateTime" jdbcType="TIMESTAMP"/>
        <result column="article_update_time" property="articleUpdateTime" jdbcType="TIMESTAMP"/>
        <result column="article_status" property="articleStatus" jdbcType="INTEGER"/>
    </resultMap>

    <sql id="tb">article</sql>

    <sql id="Base_Column_List">
    article_id, article_user_id, article_title, article_content,article_summary, article_view_count, article_comment_count, article_like_count, article_create_time,
    article_update_time, article_is_comment, article_status, article_order
    </sql>

    <!--查询所有文章，包括文章id，作者id，标题，创建时间-->
    <select id="listAllNotWithContent" resultType="com.test.ssm.blog.entity.Article">
        SELECT
        article_id, article_user_id, article_title, article_create_time
        FROM
        <include refid="tb"/>
        WHERE article_status = 1
        ORDER BY article_id DESC
    </select>

    <select id="findAll" resultMap="BaseResultMap">
        SELECT
        article.*
        FROM
        article
        <where>
            <if test="status != null">
                article.article_status = #{status} AND
            </if>
            <if test="keywords != null">
                article.article_title LIKE concat(concat('%',#{keywords}),'%') AND
            </if>
            <if test="userId != null">
                article.article_user_id = #{userId} AND
            </if>
            <if test="categoryId != null">
                article.article_id IN (
                SELECT article_category_ref.article_id FROM article_category_ref
                WHERE article_category_ref.category_id = #{categoryId}
                ) AND
            </if>
            <if test="tagId != null">
                article.article_id IN (
                SELECT article_tag_ref.article_id FROM article_tag_ref
                WHERE article_tag_ref.tag_id = #{tagId}
                ) AND
            </if>
            1 = 1
        </where>
        ORDER BY `article`.`article_order` DESC, `article`.`article_id` DESC
    </select>

    <!--删除-->
    <delete id="deleteById" parameterType="java.lang.Integer">
        delete from
        <include refid="tb"></include>
        where article_id=#{articleId,jdbcType=INTEGER}
    </delete>




    <!--批量删除-->
    <delete id="deleteBatch" parameterType="list">
        DELETE FROM
        <include refid="tb"></include>
        <if test="ids!=null">
            where article_id IN
            <foreach collection="ids" open="(" close=")" separator="," item="id">
                #{id}
            </foreach>
        </if>
    </delete>

    <insert id="insert" parameterType="com.test.ssm.blog.entity.Article" useGeneratedKeys="true"
            keyProperty="articleId">
        insert into
        <include refid="tb"/>
        (article_user_id, article_title,
        article_view_count, article_comment_count,
        article_like_count, article_create_time, article_update_time,
        article_is_comment, article_status, article_order,
        article_content, article_summary)
        values (#{articleUserId,jdbcType=INTEGER}, #{articleTitle,jdbcType=VARCHAR},
        #{articleViewCount,jdbcType=INTEGER},
        #{articleCommentCount,jdbcType=INTEGER},
        #{articleLikeCount,jdbcType=INTEGER}, #{articleCreateTime,jdbcType=TIMESTAMP},
        #{articleUpdateTime,jdbcType=TIMESTAMP},
        #{articleIsComment,jdbcType=INTEGER}, #{articleStatus,jdbcType=INTEGER}, #{articleOrder,jdbcType=INTEGER},
        #{articleContent,jdbcType=LONGVARCHAR}, #{articleSummary,jdbcType=VARCHAR})
    </insert>

    <!--更新-->
    <update id="update" parameterType="com.test.ssm.blog.entity.Article">
        update
        <include refid="tb"/>
        <set>
            <if test="articleUserId != null">article_user_id = #{articleUserId,jdbcType=INTEGER},</if>
            <if test="articleTitle != null">article_title = #{articleTitle,jdbcType=VARCHAR},</if>
            <if test="articleViewCount != null">article_view_count = #{articleViewCount,jdbcType=INTEGER},</if>
            <if test="articleCommentCount != null">article_comment_count = #{articleCommentCount,jdbcType=INTEGER},</if>
            <if test="articleLikeCount != null">article_like_count = #{articleLikeCount,jdbcType=INTEGER},</if>
            <if test="articleCreateTime != null">article_create_time = #{articleCreateTime,jdbcType=TIMESTAMP},</if>
            <if test="articleUpdateTime != null">article_update_time = #{articleUpdateTime,jdbcType=TIMESTAMP},</if>
            <if test="articleIsComment != null">article_is_comment = #{articleIsComment,jdbcType=INTEGER},</if>
            <if test="articleStatus != null">article_status = #{articleStatus,jdbcType=INTEGER},</if>
            <if test="articleOrder != null">article_order = #{articleOrder,jdbcType=INTEGER},</if>
            <if test="articleContent != null">article_content = #{articleContent,jdbcType=LONGVARCHAR},</if>
            <if test="articleSummary != null">article_summary = #{articleSummary,jdbcType=VARCHAR},</if>
        </set>
        WHERE article_id = #{articleId,jdbcType=INTEGER}
    </update>

    <!--获取文章数量-->
    <select id="countArticle" resultType="Integer">
        SELECT COUNT (*) FROM
        <include refid="tb"></include>
        WHERE article_status=1
    </select>

    <!--获得文章留言总数-->
    <select id="countArticleComment" resultType="Integer">
        SELECT SUM (article_comment_count)
        FROM
        <include refid="tb"></include>
        WHERE article_status=1
    </select>

    <!--统计文章总访问量-->
    <select id="countArticleView" resultType="Integer">
        SELECT SUM (article_view_count) FROM
        <include refid="tb"></include>
        WHERE article_status=1
    </select>

    <!--获得所有文章-->
    <select id="listArticle" resultType="com.test.ssm.blog.entity.Article">
        SELECT
        <include refid="Base_Column_List"/>
        FROM
        <include refid="tb"/>
        WHERE article_status = 1
        ORDER BY article_status ASC, article_order DESC, article_id DESC
    </select>

    <!--根据状态和ID获得文章-->
    <select id="getArticleByStatusAndId" resultType="com.test.ssm.blog.entity.Article">
        SELECT
        <include refid="Base_Column_List"/>
        FROM
        <include refid="tb"/>
        <where>
            <if test="status != null">
                article_status = #{status} AND
            </if>
            article_id = #{id}
        </where>
    </select>


    <select id="getArticleById" resultType="com.test.ssm.blog.entity.Article">
        SELECT
        <include refid="Base_Column_List"/>
        FROM
        <include refid="tb"/>
        <where>
            article_id = #{id}
        </where>
    </select>

    <!--通过分页查询文章-->
    <select id="pageArticle" resultType="com.test.ssm.blog.entity.Article">
        SELECT
        <include refid="Base_Column_List"/>
        FROM
        <include refid="tb"/>
        <where>
            <if test="status!=null">
                article_status=#{status}
            </if>
        </where>
        ORDER BY article_status ASC, article_order DESC, article_id DESC
        limit #{pageIndex},#{pageSize}
    </select>

    <!--获得访问量较多的文章-->
    <select id="listArticleByViewCount" resultType="com.test.ssm.blog.entity.Article">
        SELECT
        <include refid="Base_Column_List"></include>
        FROM
        <include refid="tb"></include>
        WHERE article_status=1
        ORDER BY article_view_count DESC,article_order DESC,article_id DESC
        limit #{limit}
    </select>

    <!--获得下一篇文章-->
    <select id="getAfterArticle" resultType="com.test.ssm.blog.entity.Article">
        SELECT
        <include refid="Base_Column_List"></include>
        FROM
        <include refid="tb"></include>
        <where>
            article_id>#{id} AND article_status=1
        </where>
        ORDER BY article_id
        limit 1
    </select>

    <!--获得上一篇的文章-->
    <select id="getPreArticle" resultType="com.test.ssm.blog.entity.Article">
        SELECT
        <include refid="Base_Column_List"/>
        FROM
        <include refid="tb"/>
        <where>
            article_id &lt; #{id} AND article_status = 1
        </where>
        ORDER BY article_id
        limit 1
    </select>

    <!--获得随机文章-->
    <select id="listRandomArticle" resultType="com.test.ssm.blog.entity.Article">
        SELECT
        <include refid="Base_Column_List"></include>
        FROM
        <include refid="tb"></include>
        WHERE article_status=1
        ORDER BY
        RAND()
        limit #{limit}
    </select>

    <!--获得评论数较多的文章列表-->
    <select id="listArticleByCommentCount" resultType="com.test.ssm.blog.entity.Article">
        SELECT
        <include refid="Base_Column_List"></include>
        FROM
        <include refid="tb"></include>
        WHERE article_status=1
        ORDER BY
        article_comment_count DESC,article_order DESC ,article_id DESC
        limit #{limit}
    </select>

  <!--更新与文章相关的评论-->
    <update id="updateCommentCount" parameterType="Integer">
        UPDATE
        <include refid="tb"/>
        SET article_comment_count =
        (
        SELECT count(*) FROM `comment`
        WHERE article.article_id=comment.comment_article_id
        )
        WHERE article_id=#{articleId}
    </update>

    <!--获得最后更新的记录-->
   <select id="getLastUpdateArticle" resultType="com.test.ssm.blog.entity.Article">
       SELECT
       <include refid="Base_Column_List"></include>
       FROM
       <include refid="tb"></include>
       WHERE
       article_status=1 AND article_update_time=(
       SELECT max(article_update_time) FROM article
       )
   </select>

    <!--获得一个用户的文章数量-->
    <select id="countArticleByUser" parameterType="Integer" resultType="Integer">
        SELECT COUNT(*)
        FROM
        <include refid="tb"></include>
        where article_user_id=#{id} AND article_status=1
    </select>

    <!--根据分类获得文章-->
    <select id="findArticleByCategoryId" resultType="com.test.ssm.blog.entity.Article">
        SELECT
        article.article_id, article.article_user_id, article.article_title,
        article.article_view_count, article.article_comment_count,
        article.article_like_count, article.article_create_time, article.article_update_time,
        article.article_is_comment, article.article_status, article.article_order,
         article.article_summary
        FROM article, article_category_ref
        WHERE
        article.article_status = 1 AND
        article.article_id = article_category_ref.article_id AND
        article_category_ref.category_id = #{catgeoyrId}
        LIMIT #{limit}
    </select>

    <!--根据分类列表查询文章-->
    <select id="findArticleByCategoryIds" resultType="com.test.ssm.blog.entity.Article">
        SELECT
        article.article_id, article.article_user_id, article.article_title,
        article.article_view_count, article.article_comment_count,
        article.article_like_count, article.article_create_time, article.article_update_time,
        article.article_is_comment, article.article_status, article.article_order,
        article.article_summary
        FROM article, article_category_ref
        <where>
            article.article_status = 1 AND
            article.article_id = article_category_ref.article_id AND
            article_category_ref.category_id
            <if test="categoryIds != null">
                IN
                <foreach collection="categoryIds" open="(" close=")" separator="," item="id">
                    #{id}
                </foreach>
            </if>
        </where>
        LIMIT #{limit}
    </select>

    <select id="listArticleByLimit" resultType="com.test.ssm.blog.entity.Article">
        SELECT
        article_id, article_user_id, article_title, article_view_count, article_comment_count, article_like_count,
        article_create_time,
        article_update_time, article_is_comment, article_status, article_order
        FROM
        <include refid="tb"/>
        ORDER BY article_id DESC
        LIMIT #{value}
    </select>

    <select id="selectArticleIdByUserId" resultType="java.lang.Integer">
        select article_id from
        <include refid="tb"></include>
        where article_user_id=#{value}
    </select>




</mapper>

1.2 Category

1.2.1 接口

package com.test.ssm.blog.mapper;

import com.test.ssm.blog.entity.Category;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

import java.util.List;

@Mapper
public interface CategoryMapper {

    //添加分类
    int insert(Category category);

    //更新
    int update(Category category);

    //根据分类ID获得分类信息
    Category getCategoryById(Integer id);

    //删除分类
    int deleteCategory(Integer id);

    //查询分类总数
    Integer countCategory();

    //获得分类列表
    List<Category> listCategory();

    //根据用户ID获得分类列表
    List<Category> listCategoryByUserId(Integer categoryUserId);

    //根据父分类查找子分类
    List<Category> findChildCategory(@Param(value = "id")Integer id);

    //根据标签名获取标签
    Category getCategoryByName(String name);
}

1.2.2 SQL文件

<?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.test.ssm.blog.mapper.CategoryMapper">
    <resultMap id="BaseResultMap" type="com.test.ssm.blog.entity.Category">
        <id column="category_id" property="categoryId" jdbcType="INTEGER"/>
        <result column="category_pid" property="categoryPid" jdbcType="INTEGER"/>
        <result column="category_name" property="categoryName" jdbcType="VARCHAR"/>
        <result column="category_description" property="categoryDescription" jdbcType="VARCHAR"/>
        <result column="category_order" property="categoryOrder" jdbcType="INTEGER"/>
        <result column="category_icon" property="categoryIcon" jdbcType="VARCHAR"/>
        <result column="category_user_id" property="categoryUserId" jdbcType="INTEGER"/>
    </resultMap>

    <sql id="tb">category</sql>
    <sql id="Base_Column_List">
        category_id,category_pid,category_name,category_description,category_order,category_icon,category_user_id
    </sql>

    <!--插入分类-->
    <insert id="insert" parameterType="com.test.ssm.blog.entity.Category" useGeneratedKeys="true" keyProperty="categoryId">
        insert into
        <include refid="tb"></include>
        (category_pid,category_name,category_description,category_order,category_icon,category_user_id)
        VALUES (#{categoryPid,jdbcType=INTEGER},#{categoryName,jdbcType=VARCHAR},#{categoryDescription,jdbcType=VARCHAR},#{categoryOrder,jdbcType=INTEGER},
                #{categoryIcon,jdbcType=VARCHAR},#{categoryUserId,jdbcType=INTEGER})
    </insert>

    <!--不可以更改是哪个用户创建的-->
    <update id="update" parameterType="com.test.ssm.blog.entity.Category">
        update
        <include refid="tb"></include>
        <set>
            <if test="categoryPid!=null">category_pid=#{categoryPid,jdbcType=INTEGER},</if>
            <if test="categoryName!=null">category_name=#{categoryName,jdbcType=VARCHAR},</if>
            <if test="categoryDescription">category_description=#{categoryDescription,jdbcType=VARCHAR},</if>
            <if test="categoryOrder!=null">category_order=#{categoryOrder,jdbcType=INTEGER},</if>
            <if test="categoryIcon!=null">category_icon=#{categoryIcon,jdbcType=VARCHAR}</if>
        </set>
        where category_id=#{categoryId,jdbcType=INTEGER}
    </update>

    <!--获得分类总数-->
    <select id="countCategory" resultType="Integer">
        SELECT COUNT(*) FROM
        <include refid="tb"></include>
    </select>

    <!--分类列表-->
     <select id="listCategory" resultType="com.test.ssm.blog.entity.Category">
         select
         <include refid="Base_Column_List"></include>
         FROM
         <include refid="tb"></include>
         ORDER BY category_order DESC,category_id ASC
     </select>


    <!--分类列表-->
    <select id="listCategoryByUserId" parameterType="Integer" resultType="com.test.ssm.blog.entity.Category">
        select
        <include refid="Base_Column_List"></include>
        FROM
        <include refid="tb"></include>
        <where>
            category_user_id=#{categoryUserId}
        </where>
        ORDER BY category_order DESC,category_id ASC
    </select>

    <!--根据ID获得分类-->
    <select id="getCategoryById" parameterType="Integer" resultType="com.test.ssm.blog.entity.Category">
        select
        <include refid="Base_Column_List"></include>
        FROM
        <include refid="tb"></include>
        where
        category.category_id=#{value}
    </select>

    <!--根据id删除分类-->
    <delete id="deleteCategory" parameterType="Integer">
        DELETE FROM
        <include refid="tb"></include>
        where category_id=#{value}
    </delete>

    <!--根据父分类找子分类-->
    <select id="findChildCategory" parameterType="Integer" resultType="com.test.ssm.blog.entity.Category">
        SELECT
        <include refid="Base_Column_List"></include>
        FROM
        `category`
        WHERE
        category_pid=#{id}
    </select>

    <!--根据分类名查找分类-->
    <select id="getCategoryByName" parameterType="String" resultType="com.test.ssm.blog.entity.Category">
        SELECT
        <include refid="Base_Column_List"/>
        FROM
        <include refid="tb"/>
        WHERE category_name=#{value}
        limit 1
    </select>
</mapper>

1.3 Tag

1.3.1 接口

package com.test.ssm.blog.mapper;

import com.test.ssm.blog.entity.Tag;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;

@Mapper
public interface TagMapper {

    //根据ID删除
    int deleteById(Integer tagId);

    //添加
    int insert(Tag tag);

    //根据ID查询
    Tag getTagById(Integer tagId);

    //更新
    int update(Tag tag);

    //获得标签总数
    int countTag();

    //获得标签列表
    List<Tag> listTag();

    //根据用户ID获得标签列表
    List<Tag> listTagByUserId(Integer tagUserId);

    //根据标签名获取标签
    Tag getTagByName(String name);
}

1.3.2 SQL 文件

<?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.test.ssm.blog.mapper.TagMapper" >
    <resultMap id="BaseResultMap" type="com.test.ssm.blog.entity.Tag" >
        <id column="tag_id" property="tagId" jdbcType="INTEGER" />
        <result column="tag_name" property="tagName" jdbcType="VARCHAR" />
        <result column="tag_description" property="tagDescription" jdbcType="VARCHAR" />
        <result column="tag_user_id" property="tagUserId" jdbcType="INTEGER"/>
    </resultMap>
    <sql id="Base_Column_List" >
    tag_id, tag_name, tag_description,tag_user_id
  </sql>

    <sql id="tb">tag</sql>

    <select id="getTagById" resultMap="BaseResultMap" parameterType="java.lang.Integer" >
        select
        <include refid="Base_Column_List" />
        from <include refid="tb"/>
        where tag_id = #{tagId,jdbcType=INTEGER}
    </select>

    <delete id="deleteById" parameterType="java.lang.Integer" >
        delete from <include refid="tb"/>
        where tag_id = #{tagId,jdbcType=INTEGER}
    </delete>

    <insert id="insert" parameterType="com.test.ssm.blog.entity.Tag" useGeneratedKeys="true" keyProperty="tagId">
        insert into <include refid="tb"/>
        (tag_name, tag_description,tag_user_id)
        values (#{tagName,jdbcType=VARCHAR}, #{tagDescription,jdbcType=VARCHAR},#{tagUserId,jdbcType=INTEGER})
    </insert>


    <update id="update" parameterType="com.test.ssm.blog.entity.Tag" >
        update <include refid="tb"/>
        <set >
            <if test="tagName != null" >
                tag_name = #{tagName,jdbcType=VARCHAR},
            </if>
            <if test="tagDescription != null" >
                tag_description = #{tagDescription,jdbcType=VARCHAR}
            </if>
        </set>
        where tag_id = #{tagId,jdbcType=INTEGER}
    </update>

    <!--获得标签列表-->
    <select id="listTag"  resultType="com.test.ssm.blog.entity.Tag">
        SELECT
        <include refid="Base_Column_List"/>
        FROM <include refid="tb"/>
    </select>


    <select id="listTagByUserId"  parameterType="Integer" resultType="com.test.ssm.blog.entity.Tag">
        SELECT
        <include refid="Base_Column_List"/>
        FROM <include refid="tb"/>
        where
          tag_user_id=#{tagUserId,jdbcType=INTEGER}
    </select>

    <!--获得标签总数-->
    <select id="countTag" resultType="Integer">
        SELECT COUNT(*) FROM <include refid="tb"/>
    </select>

    <!--根据标签名获取标签-->
    <select id="getTagByName" parameterType="String" resultType="com.test.ssm.blog.entity.Tag">
        SELECT
        <include refid="Base_Column_List"/>
        FROM
        <include refid="tb"/>
        WHERE tag_name=#{value}
        limit 1
    </select>
</mapper>

1.4 ArticleCategoryRef

1.4.1 接口

package com.test.ssm.blog.mapper;

import com.test.ssm.blog.entity.ArticleCategoryRef;
import com.test.ssm.blog.entity.Category;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;

@Mapper
public interface ArticleCategoryRefMapper {

    //添加文章和分类关联记录
    int insert(ArticleCategoryRef record);

    //根据分类ID删除记录
    int deleteByCategoryId(Integer categoryId);

    //根据文章ID删除记录
    int deleteByArticleId(Integer articleId);

    //根据分类ID统计文章数
    int countArticleByCategoryId(Integer categoryId);

    //根据文章ID查询分类ID
    List<Integer> selectCategoryIdByArticleId(Integer articleId);

    //根据分类ID查询文章ID
    List<Integer> selectArticleIdByCategoryId(Integer categoryId);

    //根据文章ID获得分类列表
    List<Category> listCategoryByArticleId(Integer articleId);

}

1.4.2 SQL文件

<?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.test.ssm.blog.mapper.ArticleCategoryRefMapper">
    <resultMap id="BaseResultMap" type="com.test.ssm.blog.entity.ArticleCategoryRef">
        <result column="article_id" jdbcType="INTEGER" property="articleId"/>
        <result column="category_id" jdbcType="INTEGER" property="categoryId"/>
    </resultMap>
    
    <sql id="tb">article_category_ref</sql>
    
    <insert id="insert" parameterType="java.lang.Integer">
        insert into
        <include refid="tb"/>
        (article_id, category_id)
        values (#{articleId}, #{categoryId})
    </insert>
    
    <delete id="deleteByCategoryId" parameterType="java.lang.Integer">
        delete from 
        <include refid="tb"></include>
        where 
        category_id=#{value}
    </delete>
    
    <delete id="deleteByArticleId" parameterType="java.lang.Integer">
        delete from 
        <include refid="tb"></include>
        where article_id=#{value}
    </delete>

    <select id="countArticleByCategoryId" parameterType="java.lang.Integer" resultType="java.lang.Integer">
       select count(*) from
       <include refid="tb"></include>
       where
        category_id=#{value}
    </select>

    <select id="selectCategoryIdByArticleId" resultType="java.lang.Integer">
        select category_id
        from
        <include refid="tb"></include>
        where article_id=#{value}
    </select>

    <select id="selectArticleIdByCategoryId" resultType="java.lang.Integer">
        select article_id
        from
        <include refid="tb"></include>
        where
        category_id=#{value}
    </select>

    <!--根据文章ID获得分类（目前没有加用户信息）-->
    <select id="listCategoryByArticleId" parameterType="java.lang.Integer" resultType="com.test.ssm.blog.entity.Category">
         SELECT
        category.category_id, category.category_pid, category.category_name
        FROM category, article_category_ref
        WHERE article_category_ref.article_id = #{value} AND
        article_category_ref.category_id = category.category_id
        ORDER BY category.category_pid asc
    </select>


</mapper>

1.5 ArticleTagRef

1.5.1 接口

package com.test.ssm.blog.mapper;

import com.test.ssm.blog.entity.ArticleTagRef;
import com.test.ssm.blog.entity.Tag;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;

@Mapper
public interface ArticleTagRefMapper {

    //添加文章和标签关联记录
    int insert(ArticleTagRef record);

    //根据标签ID删除记录
    int deleteByTagId(Integer tagId);

    //根据文章ID删除记录
    int deleteByArticleId(Integer articleId);

    //根据标签ID统计文章数
    int countArticleByTagId(Integer tagId);

    //根据文章ID获得标签列表
    List<Tag> listTagByArticleId(Integer articleId);


}

1.5.2 SQL文件

<?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.test.ssm.blog.mapper.ArticleTagRefMapper">
    <resultMap id="BaseResultMap" type="com.test.ssm.blog.entity.ArticleTagRef">
        <result column="article_id" jdbcType="INTEGER" property="articleId"/>
        <result column="tag_id" jdbcType="INTEGER" property="tagId"/>
    </resultMap>

    <sql id="tb">article_tag_ref</sql>

    <insert id="insert" parameterType="java.lang.Integer">
        insert into
        <include refid="tb"></include>
        (article_id,tag_id)
        values (#{articleId},#{tagId})
    </insert>

    <delete id="deleteByTagId" parameterType="java.lang.Integer">
        delete from
        <include refid="tb"></include>
        where
        tag_id=#{value}
    </delete>

    <delete id="deleteByArticleId" parameterType="java.lang.Integer">
        delete from
        <include refid="tb"></include>
        where article_id=#{value}
    </delete>

    <select id="countArticleByTagId" parameterType="java.lang.Integer" resultType="java.lang.Integer">
        select count(*) from
        <include refid="tb"></include>
        where tag_id=#{value}
    </select>

    <select id="listTagByArticleId" parameterType="java.lang.Integer" resultType="com.test.ssm.blog.entity.Tag">
       SELECT tag.* FROM tag, article_tag_ref
    WHERE article_tag_ref.article_id = #{value} AND
    article_tag_ref.tag_id = tag.tag_id
    </select>
</mapper>

1.6 Comment

1.6.1 接口

package com.test.ssm.blog.mapper;

import com.test.ssm.blog.entity.Article;
import com.test.ssm.blog.entity.Comment;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

import java.util.List;

@Mapper
public interface CommentMapper {

    //根据ID删除
    int deleteById(Integer commentId);

    //添加
    int insert(Comment comment);

    //根据ID查询
    Comment getCommentById(Integer commentId);

    //更新
    int update(Comment comment);

    //根据文章ID获取评论列表
    List<Comment> listCommentByArticleId(@Param(value = "id")Integer id);

    //获得评论列表
    List<Comment> listComment();

    //根据文章列表ID获得评论列表
    List<Comment> listCommentByArticleIds(@Param(value = "articleIds")List<Integer> articleIds);

    //统计评论数
    Integer countComment();

    /**获得最新评论
     * @param limit 查询数量
     * @return
     */
    List<Comment> listRecentComment(@Param(value = "limit")Integer limit);

    //获得评论的子评论
    List<Comment> listChildComment(@Param(value="id")Integer id);
}

1.6.2 SQL文件

<?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.test.ssm.blog.mapper.CommentMapper">
    <resultMap id="BaseResultMap" type="com.test.ssm.blog.entity.Comment">
        <id column="comment_id" property="commentId" jdbcType="INTEGER"/>
        <result column="comment_pid" property="commentPid" jdbcType="INTEGER"/>
        <result column="comment_pname" property="commentPname" jdbcType="VARCHAR"/>
        <result column="comment_article_id" property="commentArticleId" jdbcType="INTEGER"/>
        <result column="comment_author_name" property="commentAuthorName" jdbcType="VARCHAR"/>
        <result column="comment_author_email" property="commentAuthorEmail" jdbcType="VARCHAR"/>
        <result column="comment_author_url" property="commentAuthorUrl" jdbcType="VARCHAR"/>
        <result column="comment_author_avatar" property="commentAuthorAvatar" jdbcType="VARCHAR"/>
        <result column="comment_content" property="commentContent" jdbcType="VARCHAR"/>
        <result column="comment_agent" property="commentAgent" jdbcType="VARCHAR"/>
        <result column="comment_ip" property="commentIp" jdbcType="VARCHAR"/>
        <result column="comment_create_time" property="commentCreateTime" jdbcType="TIMESTAMP"/>
        <result column="comment_role" property="commentRole" jdbcType="INTEGER"/>
    </resultMap>
    <sql id="tb">comment</sql>

    <sql id="Base_Column_List">
    comment_id, comment_pid, comment_pname, comment_article_id, comment_author_name,
    comment_author_email, comment_author_url, comment_author_avatar, comment_content, comment_agent,
     comment_ip,comment_create_time, comment_role
  </sql>

    <select id="getCommentById" resultMap="BaseResultMap" parameterType="java.lang.Integer">
        select 
        <include refid="Base_Column_List"></include>
        from 
        <include refid="tb"></include>
        where comment_id = #{commentId,jdbcType=INTEGER}
    </select>

    <delete id="deleteById" parameterType="java.lang.Integer">
        delete from
        <include refid="tb"></include>
        where comment_id = #{commentId,jdbcType=INTEGER}
    </delete>

    <insert id="insert" parameterType="com.test.ssm.blog.entity.Comment" useGeneratedKeys="true" keyProperty="commentId">
        insert into
        <include refid="tb"/>
        (comment_pid, comment_pname, comment_article_id,
        comment_author_name, comment_author_email,
        comment_author_url, comment_author_avatar, comment_content, comment_agent,
        comment_ip, comment_create_time, comment_role)
        values (#{commentPid,jdbcType=INTEGER}, #{commentPname,jdbcType=VARCHAR}, #{commentArticleId,jdbcType=INTEGER},
        #{commentAuthorName,jdbcType=VARCHAR}, #{commentAuthorEmail,jdbcType=VARCHAR},
        #{commentAuthorUrl,jdbcType=VARCHAR},#{commentAuthorAvatar}, #{commentContent,jdbcType=VARCHAR},
        #{commentAgent,jdbcType=VARCHAR},
        #{commentIp,jdbcType=VARCHAR}, #{commentCreateTime,jdbcType=TIMESTAMP}, #{commentRole,jdbcType=INTEGER})
    </insert>
    <update id="update" parameterType="com.test.ssm.blog.entity.Comment">
        update
        <include refid="tb"/>
        <set>
            <if test="commentPid != null">comment_pid = #{commentPid,jdbcType=INTEGER},</if>
            <if test="commentPname != null">comment_pname = #{commentPname,jdbcType=VARCHAR},</if>
            <if test="commentArticleId != null">comment_article_id = #{commentArticleId,jdbcType=INTEGER},</if>
            <if test="commentAuthorName != null">comment_author_name = #{commentAuthorName,jdbcType=VARCHAR},</if>
            <if test="commentAuthorEmail != null">comment_author_email = #{commentAuthorEmail,jdbcType=VARCHAR},</if>
            <if test="commentAuthorUrl != null">comment_author_url = #{commentAuthorUrl,jdbcType=VARCHAR},</if>
            <if test="commentContent != null">comment_content = #{commentContent,jdbcType=VARCHAR},</if>
            <if test="commentAgent != null">comment_agent = #{commentAgent,jdbcType=VARCHAR},</if>
            <if test="commentIp != null">comment_ip = #{commentIp,jdbcType=VARCHAR},</if>
            <if test="commentCreateTime != null">comment_create_time = #{commentCreateTime,jdbcType=TIMESTAMP},</if>
            <if test="commentAuthorAvatar != null">comment_author_avatar = #{commentAuthorAvatar},</if>
            <if test="commentRole != null">comment_role = #{commentRole,jdbcType=INTEGER},</if>
        </set>
        where comment_id = #{commentId,jdbcType=INTEGER}
    </update>

    <select id="listCommentByArticleId" resultType="com.test.ssm.blog.entity.Comment">
        SELECT
        <include refid="Base_Column_List"/>
        FROM
        <include refid="tb"/>
        WHERE
        comment_article_id = #{id}
        ORDER BY comment_id ASC
    </select>

    <select id="listCommentByArticleIds" resultType="com.test.ssm.blog.entity.Comment">
        select
        <include refid="Base_Column_List"></include>
        FROM 
        <include refid="tb"></include>
        <where>
            comment_article_id
            <if test="articleIds!=null">
                IN 
                <foreach collection="articleIds" open="(" close=")" separator="," item="articleId">
                    #{articleId}
                </foreach>
            </if>
        </where>
    </select>


    <!--获得评论列表-->
    <select id="listComment" resultType="com.test.ssm.blog.entity.Comment">
        SELECT
        <include refid="Base_Column_List"></include>
        FROM
        <include refid="tb"></include>
        ORDER BY comment_id DESC
    </select>

    <!--统计评论数-->
    <select id="countComment" parameterType="Integer" resultType="Integer">
        SELECT COUNT(*)
        FROM
        <include refid="tb"></include>
    </select>

    <!--获得最新评论，访客的评论-->
    <select id="listRecentComment" parameterType="Integer" resultType="com.test.ssm.blog.entity.Comment">
       SELECT
       <include refid="Base_Column_List"></include>
       FROM
       <include refid="tb"></include>
       where comment_role=0
       LIMIT #{limit}
    </select>

    <!--获取评论的子评论-->
    <select id="listChildComment" resultType="com.test.ssm.blog.entity.Comment">
        SELECT
        <include refid="Base_Column_List"></include>
        FROM
        <include refid="tb"></include>
        WHERE
        comment_pid=#{id}
    </select>

</mapper>

1.7 Page

1.7.1 接口

package com.test.ssm.blog.mapper;

import com.test.ssm.blog.entity.Page;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

import java.util.List;

@Mapper
public interface PageMapper {

    //根据ID删除
    int deleteById(Integer pageId);

    //添加
    int insert(Page page);

    //根据ID查询
    Page getPageById(Integer pageId);

    //更新
    int update(Page page);

    //获得页面列表
    List<Page> listPage(@Param(value = "status")Integer status);

    //根据key获得页面
    Page getPageByKey(@Param(value = "status")Integer status,
                      @Param(value = "key")String key);
}

1.7.2 SQL文件

<?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.test.ssm.blog.mapper.PageMapper" >
    <resultMap id="BaseResultMap" type="com.test.ssm.blog.entity.Page" >
        <id column="page_id" property="pageId" jdbcType="INTEGER" />
        <result column="page_key" property="pageKey" jdbcType="VARCHAR" />
        <result column="page_title" property="pageTitle" jdbcType="VARCHAR" />
        <result column="page_content" property="pageContent" jdbcType="VARCHAR" />
        <result column="page_create_time" property="pageCreateTime" jdbcType="TIMESTAMP" />
        <result column="page_update_time" property="pageUpdateTime" jdbcType="TIMESTAMP" />
        <result column="page_view_count" property="pageViewCount" jdbcType="INTEGER" />
        <result column="page_comment_count" property="pageCommentCount" jdbcType="INTEGER" />
        <result column="page_status" property="pageStatus" jdbcType="INTEGER" />
    </resultMap>
    <sql id="Base_Column_List" >
    page_id, page_key, page_title, page_content, page_create_time, page_update_time,
    page_view_count, page_comment_count, page_status
  </sql>

    <sql id="tb">page</sql>

    <select id="getPageById" resultMap="BaseResultMap" parameterType="java.lang.Integer" >
        select
        <include refid="Base_Column_List" />
        from <include refid="tb"/>
        where page_id = #{pageId,jdbcType=INTEGER}
    </select>

    <delete id="deleteById" parameterType="java.lang.Integer" >
        delete from <include refid="tb"/>
        where page_id = #{pageId,jdbcType=INTEGER}
    </delete>

    <insert id="insert" parameterType="com.test.ssm.blog.entity.Page" useGeneratedKeys="true" keyProperty="pageId">
        insert into <include refid="tb"/>
        (page_id, page_key, page_title,
        page_content, page_create_time, page_update_time,
        page_view_count, page_comment_count, page_status
        )
        values (#{pageId,jdbcType=INTEGER}, #{pageKey,jdbcType=VARCHAR}, #{pageTitle,jdbcType=VARCHAR},
        #{pageContent,jdbcType=VARCHAR}, #{pageCreateTime,jdbcType=TIMESTAMP}, #{pageUpdateTime,jdbcType=TIMESTAMP},
        #{pageViewCount,jdbcType=INTEGER}, #{pageCommentCount,jdbcType=INTEGER}, #{pageStatus,jdbcType=INTEGER}
        )
    </insert>


    <update id="update" parameterType="com.test.ssm.blog.entity.Page" >
        update <include refid="tb"/>
        <set >
            <if test="pageKey != null" > page_key = #{pageKey,jdbcType=VARCHAR},</if>
            <if test="pageTitle != null" >page_title = #{pageTitle,jdbcType=VARCHAR},</if>
            <if test="pageContent != null" >page_content = #{pageContent,jdbcType=VARCHAR},</if>
            <if test="pageCreateTime != null" >page_create_time = #{pageCreateTime,jdbcType=TIMESTAMP},</if>
            <if test="pageUpdateTime != null" >page_update_time = #{pageUpdateTime,jdbcType=TIMESTAMP},</if>
            <if test="pageViewCount != null" >page_view_count = #{pageViewCount,jdbcType=INTEGER},</if>
            <if test="pageCommentCount != null" >page_comment_count = #{pageCommentCount,jdbcType=INTEGER},</if>
            <if test="pageStatus != null" >page_status = #{pageStatus,jdbcType=INTEGER}</if>
        </set>
        where page_id = #{pageId,jdbcType=INTEGER}
    </update>

    <!--获得页面列表-->
    <select id="listPage" resultType="com.test.ssm.blog.entity.Page">
        SELECT
        <include refid="Base_Column_List"/>
        FROM <include refid="tb"/>
        <where>
            <if test="status!=null">
                page_status=#{status}
            </if>
        </where>
    </select>

    <!--根据key获得-->
    <select id="getPageByKey" resultType="com.test.ssm.blog.entity.Page">
        SELECT
        <include refid="Base_Column_List"/>
        FROM
        <include refid="tb"/>
        <where>
            <if test="status!=null">
                page_status=#{status} AND
            </if>
            page_key=#{key}
        </where>
    </select>
</mapper>

1.8 Notice

1.8.1 接口

package com.test.ssm.blog.mapper;

import com.test.ssm.blog.entity.Notice;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

import java.util.List;

@Mapper
public interface NoticeMapper {

    //根据ID删除
    int deleteById(Integer noticeId);

    //添加
    int insert(Notice notice);

    //根据ID查询

    Notice getNoticeById(Integer noticeId);

    //更新
    int update(Notice notice);

    //根据主键更新公告
    int updateByPrimaryKey(Notice notice);

    //获得公告总数
    Integer countNotice(@Param(value = "status")Integer status);

    //获得公告列表
    List<Notice> listNotice(@Param(value = "status")Integer status);


}

1.8.2 SQL文件

<?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.test.ssm.blog.mapper.NoticeMapper" >
    <resultMap id="BaseResultMap" type="com.test.ssm.blog.entity.Notice" >
        <id column="notice_id" property="noticeId" jdbcType="INTEGER" />
        <result column="notice_title" property="noticeTitle" jdbcType="VARCHAR" />
        <result column="notice_content" property="noticeContent" jdbcType="VARCHAR" />
        <result column="notice_create_time" property="noticeCreateTime" jdbcType="TIMESTAMP" />
        <result column="notice_update_time" property="noticeUpdateTime" jdbcType="TIMESTAMP" />
        <result column="notice_status" property="noticeStatus" jdbcType="INTEGER" />
        <result column="notice_order" property="noticeOrder" jdbcType="INTEGER" />
    </resultMap>
    <sql id="Base_Column_List" >
    notice_id, notice_title, notice_content, notice_create_time, notice_update_time,
    notice_status, notice_order
  </sql>

    <sql id="tb">notice</sql>

    <select id="getNoticeById" resultMap="BaseResultMap" parameterType="java.lang.Integer">
        select
        <include refid="Base_Column_List"></include>
        from
        <include refid="tb"></include>
        where notice_id = #{noticeId,jdbcType=INTEGER}
    </select>

    <delete id="deleteById" parameterType="java.lang.Integer">
        delete from <include refid="tb"></include>
        where notice_id = #{noticeId,jdbcType=INTEGER}
    </delete>

    <insert id="insert" parameterType="com.test.ssm.blog.entity.Notice" useGeneratedKeys="true" keyProperty="noticeId">
        insert into <include refid="tb"/>
        (notice_id, notice_title, notice_content,
        notice_create_time, notice_update_time,
        notice_status, notice_order)
        values (#{noticeId,jdbcType=INTEGER}, #{noticeTitle,jdbcType=VARCHAR}, #{noticeContent,jdbcType=VARCHAR},
        #{noticeCreateTime,jdbcType=TIMESTAMP}, #{noticeUpdateTime,jdbcType=TIMESTAMP},
        #{noticeStatus,jdbcType=INTEGER}, #{noticeOrder,jdbcType=INTEGER})
    </insert>

    <update id="update" parameterType="com.test.ssm.blog.entity.Notice" >
        update <include refid="tb"/>
        <set >
            <if test="noticeTitle != null" > notice_title = #{noticeTitle,jdbcType=VARCHAR},</if>
            <if test="noticeContent != null" >notice_content = #{noticeContent,jdbcType=VARCHAR},</if>
            <if test="noticeCreateTime != null" >notice_create_time = #{noticeCreateTime,jdbcType=TIMESTAMP},</if>
            <if test="noticeUpdateTime != null" >notice_update_time = #{noticeUpdateTime,jdbcType=TIMESTAMP},</if>
            <if test="noticeStatus != null" >notice_status = #{noticeStatus,jdbcType=INTEGER},</if>
            <if test="noticeOrder != null" >notice_order = #{noticeOrder,jdbcType=INTEGER},</if>
        </set>
        where notice_id = #{noticeId,jdbcType=INTEGER}
    </update>

    <update id="updateByPrimaryKey" parameterType="com.test.ssm.blog.entity.Notice" >
        update <include refid="tb"/>
        set notice_title = #{noticeTitle,jdbcType=VARCHAR},
        notice_content = #{noticeContent,jdbcType=VARCHAR},
        notice_create_time = #{noticeCreateTime,jdbcType=TIMESTAMP},
        notice_update_time = #{noticeUpdateTime,jdbcType=TIMESTAMP},
        notice_status = #{noticeStatus,jdbcType=INTEGER},
        notice_order = #{noticeOrder,jdbcType=INTEGER}
        where notice_id = #{noticeId,jdbcType=INTEGER}
    </update>

    <select id="countNotice" resultType="Integer">
        SELECT COUNT (*)
        FROM
        <include refid="tb"></include>
        where
        <if test="status!=null">
            notice_status=#{status}
        </if>
    </select>

    <select id="listNotice" resultType="com.test.ssm.blog.entity.Notice">
        SELECT
        <include refid="Base_Column_List"></include>
        FROM
        <include refid="tb"></include>
        <where>
            <if test="status!=null">
                notice_status=#{status}
            </if>
        </where>
        ORDER BY notice_status ASC ,notice_order DESC ,notice_id ASC
    </select>

</mapper>

1.9 Link

1.9.1 接口

package com.test.ssm.blog.mapper;

import com.test.ssm.blog.entity.Link;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

import java.util.List;

@Mapper
public interface LinkMapper {

    //删除
    int deleteById(Integer linkId);

    //添加
    int insert(Link link);

    //根据ID查询
    Link getLinkById(Integer linkId);

    //更新
    int update(Link link);

    //获得链接总数
    Integer countLink(@Param(value = "status")Integer status);

    //获得链表列表
    List<Link> listLink(@Param(value = "status")Integer status);
}

1.9.2 SQL文件

<?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.test.ssm.blog.mapper.LinkMapper" >
    <resultMap id="BaseResultMap" type="com.test.ssm.blog.entity.Link" >
        <id column="link_id" property="linkId" jdbcType="INTEGER" />
        <result column="link_url" property="linkUrl" jdbcType="VARCHAR" />
        <result column="link_name" property="linkName" jdbcType="VARCHAR" />
        <result column="link_image" property="linkImage" jdbcType="VARCHAR" />
        <result column="link_description" property="linkDescription" jdbcType="VARCHAR" />
        <result column="link_owner_nickname" property="linkOwnerNickname" jdbcType="VARCHAR" />
        <result column="link_owner_contact" property="linkOwnerContact" jdbcType="VARCHAR" />
        <result column="link_update_time" property="linkUpdateTime" jdbcType="TIMESTAMP" />
        <result column="link_create_time" property="linkCreateTime" jdbcType="TIMESTAMP" />
        <result column="link_order" property="linkOrder" jdbcType="INTEGER" />
        <result column="link_status" property="linkStatus" jdbcType="INTEGER" />
    </resultMap>


    <sql id="Base_Column_List" >
    link_id, link_url, link_name, link_image, link_description, link_owner_nickname,
    link_owner_contact, link_update_time, link_create_time, link_order, link_status
  </sql>

    <sql id="tb">link</sql>


    <select id="getLinkById" resultMap="BaseResultMap" parameterType="java.lang.Integer">
        select
        <include refid="Base_Column_List"></include>
        from
        <include refid="tb"></include>
        where link_id=#{linkId,jdbcType=INTEGER}
    </select>

    <delete id="deleteById" parameterType="java.lang.Integer" >
    delete from link
    where link_id = #{linkId,jdbcType=INTEGER}
  </delete>

    <insert id="insert" parameterType="com.test.ssm.blog.entity.Link" useGeneratedKeys="true" keyProperty="linkId">
    insert into link (link_id, link_url, link_name,
      link_image, link_description, link_owner_nickname,
      link_owner_contact, link_update_time, link_create_time,
      link_order, link_status)
    values (#{linkId,jdbcType=INTEGER}, #{linkUrl,jdbcType=VARCHAR}, #{linkName,jdbcType=VARCHAR},
      #{linkImage,jdbcType=VARCHAR}, #{linkDescription,jdbcType=VARCHAR}, #{linkOwnerNickname,jdbcType=VARCHAR},
      #{linkOwnerContact,jdbcType=VARCHAR}, #{linkUpdateTime,jdbcType=TIMESTAMP}, #{linkCreateTime,jdbcType=TIMESTAMP},
      #{linkOrder,jdbcType=INTEGER}, #{linkStatus,jdbcType=INTEGER})
  </insert>

    <update id="update" parameterType="com.test.ssm.blog.entity.Link" >
        update link
        <set >
            <if test="linkUrl != null" > link_url = #{linkUrl,jdbcType=VARCHAR},</if>
            <if test="linkName != null" >link_name = #{linkName,jdbcType=VARCHAR},</if>
            <if test="linkImage != null" >link_image = #{linkImage,jdbcType=VARCHAR},</if>
            <if test="linkDescription != null" >link_description = #{linkDescription,jdbcType=VARCHAR},</if>
            <if test="linkOwnerNickname != null" >link_owner_nickname = #{linkOwnerNickname,jdbcType=VARCHAR},</if>
            <if test="linkOwnerContact != null" >link_owner_contact = #{linkOwnerContact,jdbcType=VARCHAR},</if>
            <if test="linkUpdateTime != null" >link_update_time = #{linkUpdateTime,jdbcType=TIMESTAMP},</if>
            <if test="linkCreateTime != null" >link_create_time = #{linkCreateTime,jdbcType=TIMESTAMP},</if>
            <if test="linkOrder != null" >link_order = #{linkOrder,jdbcType=INTEGER}, </if>
            <if test="linkStatus != null" >link_status = #{linkStatus,jdbcType=INTEGER},</if>
        </set>
        where link_id = #{linkId,jdbcType=INTEGER}
    </update>

    <select id="countLink" resultType="Integer">
        select COUNT (*) FROM <include refid="tb"></include>
        where
        <if test="status!=null">
            link_status=#{status}
        </if>
    </select>

    <!--获得链接列表-->
    <select id="listLink" resultType="com.test.ssm.blog.entity.Link">
        select
        <include refid="Base_Column_List"></include>
        FROM
        <include refid="tb"></include>
        <where>
            <if test="status!=null">
                link_status=#{status}
            </if>
        </where>
        ORDER BY link_status ASC,link_order DESC ,link_id ASC
    </select>

</mapper>

1.10 Options

1.10.1 接口

package com.test.ssm.blog.mapper;

import com.test.ssm.blog.entity.Options;
import org.apache.ibatis.annotations.Mapper;

@Mapper
public interface OptionsMapper {
    //根据ID删除
    int deleteById(Integer optionId);

    //添加
    int insert(Options options);

    //根据ID查询
    Options getOptionsById(Integer optionId);

    //更新
    int update(Options options);

    //获得记录
    Options getOptions();
}

1.10.2 SQL文件

<?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.test.ssm.blog.mapper.OptionsMapper">
    <resultMap id="BaseResultMap" type="com.test.ssm.blog.entity.Options">
        <id column="option_id" property="optionId" jdbcType="INTEGER"/>
        <result column="option_site_title" property="optionSiteTitle" jdbcType="VARCHAR"/>
        <result column="option_site_descrption" property="optionSiteDescrption" jdbcType="VARCHAR"/>
        <result column="option_meta_descrption" property="optionMetaDescrption" jdbcType="VARCHAR"/>
        <result column="option_meta_keyword" property="optionMetaKeyword" jdbcType="VARCHAR"/>
        <result column="option_aboutsite_avatar" property="optionAboutsiteAvatar" jdbcType="VARCHAR"/>
        <result column="option_aboutsite_title" property="optionAboutsiteTitle" jdbcType="VARCHAR"/>
        <result column="option_aboutsite_content" property="optionAboutsiteContent" jdbcType="VARCHAR"/>
        <result column="option_aboutsite_wechat" property="optionAboutsiteWechat" jdbcType="VARCHAR"/>
        <result column="option_aboutsite_qq" property="optionAboutsiteQq" jdbcType="VARCHAR"/>
        <result column="option_aboutsite_github" property="optionAboutsiteGithub" jdbcType="VARCHAR"/>
        <result column="option_aboutsite_weibo" property="optionAboutsiteWeibo" jdbcType="VARCHAR"/>
        <result column="option_tongji" property="optionTongji" jdbcType="VARCHAR"/>
        <result column="option_status" property="optionStatus" jdbcType="INTEGER"/>
    </resultMap>

    <sql id="Base_Column_List">
    option_id, option_site_title, option_site_descrption, option_meta_descrption, option_meta_keyword,
    option_aboutsite_avatar, option_aboutsite_title, option_aboutsite_content, option_aboutsite_wechat,
    option_aboutsite_qq, option_aboutsite_github, option_aboutsite_weibo, option_tongji,
    option_status
  </sql>

    <sql id="tb">options</sql>

    <select id="getOptionsById" resultMap="BaseResultMap" parameterType="java.lang.Integer">
        select
        <include refid="Base_Column_List"/>
        from
        <include refid="tb"/>
        where option_id = #{optionId,jdbcType=INTEGER}
    </select>

    <delete id="deleteById" parameterType="java.lang.Integer">
        delete from
        <include refid="tb"/>
        where option_id = #{optionId,jdbcType=INTEGER}
    </delete>

    <insert id="insert" parameterType="com.test.ssm.blog.entity.Options">
        insert into
        <include refid="tb"/>
        (option_id, option_site_title, option_site_descrption,
        option_meta_descrption, option_meta_keyword,
        option_aboutsite_avatar, option_aboutsite_title,
        option_aboutsite_content, option_aboutsite_wechat,
        option_aboutsite_qq, option_aboutsite_github,
        option_aboutsite_weibo, option_tongji, option_status
        )
        values (#{optionId,jdbcType=INTEGER}, #{optionSiteTitle,jdbcType=VARCHAR},
        #{optionSiteDescrption,jdbcType=VARCHAR},
        #{optionMetaDescrption,jdbcType=VARCHAR}, #{optionMetaKeyword,jdbcType=VARCHAR},
        #{optionAboutsiteAvatar,jdbcType=VARCHAR}, #{optionAboutsiteTitle,jdbcType=VARCHAR},
        #{optionAboutsiteContent,jdbcType=VARCHAR}, #{optionAboutsiteWechat,jdbcType=VARCHAR},
        #{optionAboutsiteQq,jdbcType=VARCHAR}, #{optionAboutsiteGithub,jdbcType=VARCHAR},
        #{optionAboutsiteWeibo,jdbcType=VARCHAR}, #{optionTongji,jdbcType=VARCHAR}, #{optionStatus,jdbcType=INTEGER}
        )
    </insert>

    <update id="update" parameterType="com.test.ssm.blog.entity.Options">
        update
        <include refid="tb"/>
        <set>
            <if test="optionSiteTitle != null">option_site_title = #{optionSiteTitle,jdbcType=VARCHAR},</if>
            <if test="optionSiteDescrption != null">option_site_descrption = #{optionSiteDescrption,jdbcType=VARCHAR},
            </if>
            <if test="optionMetaDescrption != null">option_meta_descrption = #{optionMetaDescrption,jdbcType=VARCHAR},
            </if>
            <if test="optionMetaKeyword != null">option_meta_keyword = #{optionMetaKeyword,jdbcType=VARCHAR},</if>
            <if test="optionAboutsiteAvatar != null">option_aboutsite_avatar =
                #{optionAboutsiteAvatar,jdbcType=VARCHAR},
            </if>
            <if test="optionAboutsiteTitle != null">option_aboutsite_title = #{optionAboutsiteTitle,jdbcType=VARCHAR},
            </if>
            <if test="optionAboutsiteContent != null">option_aboutsite_content =
                #{optionAboutsiteContent,jdbcType=VARCHAR},
            </if>
            <if test="optionAboutsiteWechat != null">option_aboutsite_wechat =
                #{optionAboutsiteWechat,jdbcType=VARCHAR},
            </if>
            <if test="optionAboutsiteQq != null">option_aboutsite_qq = #{optionAboutsiteQq,jdbcType=VARCHAR},</if>
            <if test="optionAboutsiteGithub != null">option_aboutsite_github =
                #{optionAboutsiteGithub,jdbcType=VARCHAR},
            </if>
            <if test="optionAboutsiteWeibo != null">option_aboutsite_weibo = #{optionAboutsiteWeibo,jdbcType=VARCHAR},
            </if>
            <if test="optionTongji != null">option_tongji = #{optionTongji,jdbcType=VARCHAR},</if>
            <if test="optionStatus != null">option_status = #{optionStatus,jdbcType=INTEGER}</if>
        </set>
        where option_id = #{optionId,jdbcType=INTEGER}
    </update>


    <select id="getOptions" resultType="com.test.ssm.blog.entity.Options">
        SELECT * FROM
        <include refid="tb"/>
        limit 1
    </select>

</mapper>

1.11 Menu

1.11.1 接口

package com.test.ssm.blog.mapper;

import com.test.ssm.blog.entity.Menu;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;


@Mapper
public interface MenuMapper {

    //删除
    int deleteById(Integer menuId);

    //添加
    int insert(Menu menu);

    //根据ID查询
    Menu getMenuById(Integer menuId);

    //更新
    int update(Menu menu);

    //获得菜单列表
    List<Menu> listMenu();
}

1.11.2 SQL文件

<?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.test.ssm.blog.mapper.MenuMapper" >
    <resultMap id="BaseResultMap" type="com.test.ssm.blog.entity.Menu" >
        <id column="menu_id" property="menuId" jdbcType="INTEGER" />
        <result column="menu_name" property="menuName" jdbcType="VARCHAR" />
        <result column="menu_url" property="menuUrl" jdbcType="VARCHAR" />
        <result column="menu_level" property="menuLevel" jdbcType="INTEGER" />
        <result column="menu_icon" property="menuIcon" jdbcType="VARCHAR" />
        <result column="menu_order" property="menuOrder" jdbcType="INTEGER" />
    </resultMap>
    <sql id="Base_Column_List" >
    menu_id, menu_name, menu_url, menu_level, menu_icon, menu_order
  </sql>

    <sql id="tb">menu</sql>

    <select id="getMenuById" resultMap="BaseResultMap" parameterType="java.lang.Integer" >
        select
        <include refid="Base_Column_List" />
        from <include refid="tb"/>
        where menu_id = #{menuId,jdbcType=INTEGER}
    </select>
    <delete id="deleteById" parameterType="java.lang.Integer" >
        delete from <include refid="tb"/>
        where menu_id = #{menuId,jdbcType=INTEGER}
    </delete>
    <insert id="insert" parameterType="com.test.ssm.blog.entity.Menu" useGeneratedKeys="true" keyProperty="menuId">
        insert into <include refid="tb"/>
        (menu_id, menu_name, menu_url,
        menu_level, menu_icon, menu_order)
        values (#{menuId,jdbcType=INTEGER}, #{menuName,jdbcType=VARCHAR}, #{menuUrl,jdbcType=VARCHAR},
        #{menuLevel,jdbcType=INTEGER}, #{menuIcon,jdbcType=VARCHAR}, #{menuOrder,jdbcType=INTEGER})
    </insert>

    <update id="update" parameterType="com.test.ssm.blog.entity.Menu" >
        update <include refid="tb"/>
        <set >
            <if test="menuName != null" > menu_name = #{menuName,jdbcType=VARCHAR},</if>
            <if test="menuUrl != null" >menu_url = #{menuUrl,jdbcType=VARCHAR},</if>
            <if test="menuLevel != null" >menu_level = #{menuLevel,jdbcType=INTEGER},</if>
            <if test="menuIcon != null" >menu_icon = #{menuIcon,jdbcType=VARCHAR},</if>
            <if test="menuOrder != null" >menu_order = #{menuOrder,jdbcType=INTEGER},</if>
        </set>
        where menu_id = #{menuId,jdbcType=INTEGER}
    </update>



    <!--获得菜单列表-->
    <select id="listMenu"  resultType="com.test.ssm.blog.entity.Menu">
        SELECT
        <include refid="Base_Column_List"/>
        FROM <include refid="tb"/>
        ORDER BY menu_order DESC, menu_id ASC
    </select>
</mapper>

1.12 User

1.12.1 接口

package com.test.ssm.blog.mapper;


import com.test.ssm.blog.entity.User;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;

@Mapper
public interface UserMapper {

    /**
     * 根据用户ID删除
     */
    int deleteById(Integer userId);

    /**
     * 添加用户
     */
    int insert(User user);

    /**
     * 根据ID查询
     */
    User getUserById(Integer userId);

    /**
     * 更新用户信息
     */
    int update(User user);

    /**
     * 获得用户列表
     */
    List<User> listUser();

    /**
     * 根据用户名或Email获得用户
     */
    User getUserByNameOrEmail(String str) ;

    /**
     *根据用户名查询
     */
    User getUserByName(String name);

    /**
     * 根据Email查询
     */

    User getUserByEmail(String email);
    
}

1.12.2 SQL文件

<?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.test.ssm.blog.mapper.UserMapper">
    <!--property	需要映射到JavaBean 的属性名称。
    column	数据表的列名或者标签别名。
    javaType	一个完整的类名，或者是一个类型别名。如果你匹配的是一个JavaBean，那MyBatis 通常会自行检测到。然后，如果你是要映射到一个HashMap，那你需要指定javaType 要达到的目的。
    jdbcType	数据表支持的类型列表。
    typeHandler	使用这个属性可以覆写类型处理器。-->
    <resultMap id="BaseResultMap" type="com.test.ssm.blog.entity.User">
        <id column="user_id" property="userId" jdbcType="INTEGER"/>
        <result column="user_name" property="userName" jdbcType="VARCHAR"/>
        <result column="user_pass" property="userPass" jdbcType="VARCHAR"/>
        <result column="user_nickname" property="userNickname" jdbcType="VARCHAR"/>
        <result column="user_email" property="userEmail" jdbcType="VARCHAR"/>
        <result column="user_url" property="userUrl" jdbcType="VARCHAR"/>
        <result column="user_avatar" property="userAvatar" jdbcType="VARCHAR"/>
        <result column="user_last_login_ip" property="userLastLoginIp" jdbcType="VARCHAR"/>
        <result column="user_register_time" property="userRegisterTime" jdbcType="TIMESTAMP"/>
        <result column="user_last_login_time" property="userLastLoginTime" jdbcType="TIMESTAMP"/>
        <result column="user_status" property="userStatus" jdbcType="INTEGER"/>
        <result column="user_access" property="userAccess" jdbcType="INTEGER"/>
    </resultMap>

 <!--首先定义一个id，然后通过refid通过id将之前定义的内容进行引用，这样适用于同一字符串被大量引用的时候应用，-->
    <sql id="tb">user</sql>

    <sql id="Base_Column_List">
    user_id, user_name, user_pass, user_nickname, user_email, user_url, user_avatar,
    user_last_login_ip, user_register_time, user_last_login_time, user_status,user_access
  </sql>

    <!--根据用户ID查找-->
    <select id="getUserById" resultMap="BaseResultMap" parameterType="java.lang.Integer">
        select
        <include refid="Base_Column_List"/>
        from <include refid="tb"/>
        where user_id=#{userId,jdbcType=INTEGER}
    </select>

    <!--根据用户ID删除-->
    <delete id="deleteById" parameterType="java.lang.Integer">
        delete from
        <include refid="tb"></include>
        where user_id=#{userId,jdbcType=INTEGER}
    </delete>

    <!--添加用户-->
    <insert id="insert" parameterType="com.test.ssm.blog.entity.User" useGeneratedKeys="true" keyProperty="userId">
        insert into
        <include refid="tb"/>
        (user_id, user_name, user_pass,
        user_nickname, user_email, user_url,
        user_avatar, user_last_login_ip, user_register_time,
        user_last_login_time, user_status)
        values (#{userId,jdbcType=INTEGER}, #{userName,jdbcType=VARCHAR}, #{userPass,jdbcType=VARCHAR},
        #{userNickname,jdbcType=VARCHAR}, #{userEmail,jdbcType=VARCHAR}, #{userUrl,jdbcType=VARCHAR},
        #{userAvatar,jdbcType=VARCHAR}, #{userLastLoginIp,jdbcType=VARCHAR}, #{userRegisterTime,jdbcType=TIMESTAMP},
        #{userLastLoginTime,jdbcType=TIMESTAMP}, #{userStatus,jdbcType=INTEGER})
    </insert>


    <update id="update" parameterType="com.test.ssm.blog.entity.User">
        update
        <include refid="tb"/>
        <set>
            <if test="userName != null">user_name = #{userName,jdbcType=VARCHAR},</if>
            <if test="userPass != null">user_pass = #{userPass,jdbcType=VARCHAR},</if>
            <if test="userNickname != null">user_nickname = #{userNickname,jdbcType=VARCHAR},</if>
            <if test="userEmail != null">user_email = #{userEmail,jdbcType=VARCHAR},</if>
            <if test="userUrl != null">user_url = #{userUrl,jdbcType=VARCHAR},</if>
            <if test="userAvatar != null">user_avatar = #{userAvatar,jdbcType=VARCHAR},</if>
            <if test="userLastLoginIp != null">user_last_login_ip = #{userLastLoginIp,jdbcType=VARCHAR},</if>
            <if test="userRegisterTime != null">user_register_time = #{userRegisterTime,jdbcType=TIMESTAMP},</if>
            <if test="userLastLoginTime != null">user_last_login_time = #{userLastLoginTime,jdbcType=TIMESTAMP},</if>
            <if test="userStatus != null">user_status = #{userStatus,jdbcType=INTEGER},</if>
            <if test="userAccess!=null">user_access=#{userAccess,jdbcType=INTEGER}</if>
        </set>
        where user_id = #{userId,jdbcType=INTEGER}
    </update>

    <!--用户列表查找-->
    <select id="listUser" parameterType="com.test.ssm.blog.entity.User" resultType="com.test.ssm.blog.entity.User">
        SELECT
        <include refid="Base_Column_List"></include>
        FROM
        <include refid="tb"></include>
        ORDER BY `user_status` ASC
    </select>

    <!--根据用户名或邮箱获得用户-->
    <select id="getUserByNameOrEmail" parameterType="String" resultType="com.test.ssm.blog.entity.User">
        SELECT
        <include refid="Base_Column_List"/>
        FROM
        <include refid="tb"/>
        <where>
            user_name=#{value} OR user_email=#{value}
        </where>
        AND user_status>0
        limit 1
    </select>

    <!--根据用户名查用户-->
    <select id="getUserByName" parameterType="String" resultType="com.test.ssm.blog.entity.User">
        SELECT
        <include refid="Base_Column_List"/>
        FROM
        <include refid="tb"/>
        <where>
            user_name=#{value}
        </where>
        limit 1
    </select>

    <!--根据Email查用户-->
    <select id="getUserByEmail" parameterType="String" resultType="com.test.ssm.blog.entity.User">
        SELECT
        <include refid="Base_Column_List"/>
        FROM
        <include refid="tb"/>
        <where>
            user_email=#{value}
        </where>
        limit 1
    </select>

</mapper>

WYFVV

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
博客项目（三）——Mapper接口与其映射

一.接口与映射文件未学习MyBatis之前，连接数据库是使用JDBC，执行SQL语句都是使用Statement 和 PreparedStatement这两个对象。在学习了MyBatis之后，学会接口对应映射文件这种方法。1.1Article1.1.1接口package com.test.ssm.blog.mapper;import com.test.ssm.blog.ent...
复制链接

扫一扫