<?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.strtz3.blog.mapper.ArticleMapper">

    <!--插入，返回主键-->
    <insert id="save" parameterType="com.strtz3.blog.model.pojo.Article">
        <selectKey resultType="java.lang.Integer" order="AFTER" keyProperty="id">
            SELECT LAST_INSERT_ID()
        </selectKey>
        INSERT INTO tb_article VALUES
        (null,#{title},#{content},#{description},
        #{firstPicture},#{published},#{comment},#{views},
        #{category.id},#{createTime},#{updateTime})
    </insert>


    <!--多表查询-->
    <select id="findById" parameterType="integer" resultMap="articleMap">
        select *
        from tb_article
        where id = #{id}
    </select>

    <resultMap id="articleMap" type="com.strtz3.blog.model.pojo.Article">
        <id column="id" property="id"/>
        <result column="title" property="title"/>
        <result column="content" property="content"/>
        <result column="description" property="description"/>
        <result column="first_picture" property="firstPicture"/>
        <result column="published" property="published"/>
        <result column="comment" property="comment"/>
        <result column="views" property="views"/>
        <result column="create_time" property="createTime"/>
        <result column="update_time" property="updateTime"/>
        <!--category-->
        <association property="category" column="category_id" javaType="com.strtz3.blog.model.pojo.Category"
                     select="com.strtz3.blog.mapper.CategoryMapper.findByCid"/>
        <!--tags-->
        <collection property="tags" column="id" ofType="list"
                    select="com.strtz3.blog.mapper.TagMapper.findTagsByAid"/>
    </resultMap>

    <select id="findAll" resultMap="articleMap">
        select *
        from tb_article
        order by create_time DESC
    </select>
    <select id="findByCid" parameterType="integer" resultMap="articleMap">
        select *
        from tb_article
        where category_id = #{cid}
    </select>

    <select id="searchByDesc" parameterType="string" resultMap="articleMap">
        select *
        from tb_article
        where content like #{searchTxt}
           or description like #{searchTxt}
           or title like #{searchTxt}
    </select>

    <select id="orderByCreatTime" resultType="com.strtz3.blog.model.pojo.Article">
        select *
        from tb_article
        order by create_time desc
        LIMIT 0,3
    </select>
    <select id="orderByViews"  resultType="com.strtz3.blog.model.pojo.Article">
        SELECT *
        from tb_article
        ORDER BY views DESC
        <if test="size=null">
            LIMIT 0, #{size}
        </if>
    </select>
</mapper>