<?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.heima.dao.UserDao">
    <resultMap id="userMap" type="com.heima.pojo.User">
        <!-- 如果pojo属性和数据库字段不一致，可以使用这种方式resultMap=‘userMap’,或者起别名
            如果用这种方式的话可以提高开发效率，但是如果是起别名可以提升执行效率-->
        <!-- 主键字段 -->
        <id property="userId" column="userId"></id>
        <result property="username" column="username"></result>
        <result property="password" column="password"></result>
        <result property="realName" column="realName"></result>
        <result property="sex" column="sex"></result>
        <result property="age" column="age"></result>
    </resultMap>
    
    <!-- 了解一下抽取重复的sql语句-->
    <sql id="defaultUser">
        select * from userinfo
    </sql>
    <!-- 查询所有 -->
    <select id="findAll" resultType="com.heima.pojo.User">
        <include refid="defaultUser"></include>
    </select>
    <!-- 添加 -->
    <insert id="insert" parameterType="com.heima.pojo.User">
        <!-- 获取添加时的id， keyProperty是实体类的属性，
        keyColumn是数据库的字段，order是执行语句前执行还是后执行AFTER为后执行-->
       <selectKey keyProperty="userId" keyColumn="userId" resultType="int" order="AFTER">
            select last_insert_id();
        </selectKey>
        insert into userinfo(username,password,realName,sex,age) value
        (#{username},#{password},#{realName},#{sex},#{age});
    </insert>
    <!-- 修改 -->
    <update id="update" parameterType="com.heima.pojo.User">
        update userinfo set username=#{username},password=#{password},realName=#{realName},
        sex=#{sex},age=#{age} where userId=#{userId};
    </update>
    <!-- 删除 -->
    <delete id="delete" parameterType="Integer">
        delete from userinfo where userId=#{id};
    </delete>
    <!-- 单个查询 -->
    <select id="findOne" parameterType="Integer" resultType="com.heima.pojo.User">
        select * from userinfo where userId=#{id};
    </select>
    <!-- 模糊查询-->
    <select id="findByName" parameterType="String" resultType="com.heima.pojo.User">
         <include refid="defaultUser"></include>
         <where>
             username like #{username};
         </where>
    </select>
    <!-- 查询总记录数 -->
    <select id="findTotal" resultType="Integer">
        select count(userId) from userinfo;
    </select>
    <!-- 使用实体类包装对象作为查询条件-->
    <select id="queryVo" parameterType="com.heima.pojo.QueryVo" resultType="com.heima.pojo.User">
        <include refid="defaultUser"></include>
        <where>
            username like #{user.username};
        </where>
    </select>
    <!-- 使用if标签判断数据查询 -->
    <select id="findUserByCondition" resultMap="userMap" parameterType="com.heima.pojo.User">
        <include refid="defaultUser"></include>
        <where>
            <if test="username != null">
                and username=#{username}
            </if>
            <if test="sex != null">
                and sex=#{sex}
            </if>
        </where>
    </select>
    <!-- 使用foreach标签来查询多个id的数据 -->
    <select id="findUserIds" resultMap="userMap" parameterType="com.heima.pojo.QueryVo">
        <include refid="defaultUser"></include>
        <where>
            <if test="ids != null and ids.size()>0">
                <foreach collection="ids" open="and userId in(" close=")" item="userId" separator=",">
                    #{userId}
                </foreach>
            </if>
        </where>
    </select>
</mapper>