<!--头文件-->

<!DOCTYPE mapper

        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"

        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

 <!--namespace：

     命名空间：区分不同空间下的同名SQLID

     A： findlAll

     B:  findAll

    -->

<mapper namespace="cn.happy.dao.IStudentInfoDAO">

    <!--SQL标签

      id:唯一锁定到SQL标识

      paramenterType:SQL语句的入参  可以省略

      resultType：

      增删除操作：不能 写

      查询：单个实体的类型

    -->

    <sql id="columns">

        stuid,stuname,stuage,studate

    </sql>

    <resultMap id="studentMap" type="StudentInfo">

      <!--   <result column="stuname2" property="stuName"></result>-->

    </resultMap>

    <select id="findAll" resultMap="studentMap">

        /*SQL文：SQL语句*/

        select <include refid="columns"></include> from studentinfo

    </select>

    <!--按主键查询-->

    <select id="getStudentById" resultType="StudentInfo">

        select * from studentinfo WHERE stuid=#{stuId}

    </select>

 <!--添加学生-->

    <insert id="addStudent">

        insert into studentinfo( stuName,stuAge,stuDate) VALUES (#{stuName},#{stuAge},#{stuDate})

    </insert>

    <!--修改学生-->

    <update id="updateStudent">

       update studentinfo set  stuName= #{stuName} WHERE  stuId=#{stuId}

    </update>

    <!--删除学生-->

    <delete id="deleteStudent">

       delete from  studentinfo WHERE  stuId=#{stuId}

    </delete>

    <!--模糊查询-->

    <select id="findStudentListLike" resultType="StudentInfo">

        <!--select * from studentinfo where stuname like concat('%',#{stuName},'%') and stuAge>#{stuAge}-->

            select * from studentinfo where stuname like '%${stuName}%' and stuAge>#{stuAge}

    </select>

    <!--多条件查询-->

    <select id="findStudentsByCondition" resultType="StudentInfo">

         select * from  studentinfo where stuname like '%' #{stuName} '%' and stuAge>#{stuAge}

    </select>

    <!--多条件查询使用索引-->

    <select id="findStudentsByConditionMutliArgs" resultType="StudentInfo">

        select * from  studentinfo where stuname like '%' #{0} '%' and stuAge>#{1}

    </select>

    <!--智能标签foreach List-->

    <select id="findByForeachListStudent" resultType="StudentInfo">

        select * from studentinfo

        <where>

            <if test="list.size>0">

                stuid in

                <foreach collection="list" open="(" close=")" separator="," item="stu">

                    #{stu.stuId}

                </foreach>

            </if>

        </where>

    </select>

    <!--智能标签foreach List-->

    <select id="findByForeachList" resultType="StudentInfo">

        select * from studentinfo

        <where>

            <if test="list.size>0">

                stuid in

                <foreach collection="list" open="(" close=")" separator="," item="stuno">

                    #{stuno}

                </foreach>

            </if>

        </where>

    </select>

 <!--智能标签foreach Array-->

    <select id="findByForeachArray" resultType="StudentInfo">

        select * from studentinfo

        <where>

            <if test="array.length>0">

                stuid in

                <foreach collection="array" open="(" close=")" separator="," item="stuno">

                    #{stuno}

                </foreach>

            </if>

        </where>

    </select>

    <!--智能标签choose-->

    <select id="findByChoose" resultType="StudentInfo">

        select * from studentinfo

        <where>

            <choose>

                <when test="stuName!=null">

                    and stuName like '%' #{stuName} '%'

                </when>

                <when test="stuAge!=null">

                    and stuAge>#{stuAge}

                </when>

                <otherwise>

                     and 1=2

                </otherwise>

            </choose>

        </where>

    </select>

    <!--智能标签if-->

    <select id="findByIf" resultType="StudentInfo">

        select * from studentinfo

        <where>

            <if test="stuName!=null"><!--用户录入的姓名字段-->

                 and stuName like '%' #{stuName} '%'

            </if>

            <if test="stuAge!=null">

                and stuAge>#{stuAge}

            </if>

        </where>

    </select>

</mapper>