mybatis常见的增删改查语句和条件查询

<?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.sky.mapper.EmpDao">

    <!--    查所有-->
    <select id="SelectAllEmp" resultType="HashMap">
        select * from emp,dept where emp.deptno = dept.deptno
    </select>

    <!--    新增-->
    <insert id="addEmp" parameterType="com.sky.pojo.Emp" >
        insert into emp VALUES (#{empno},#{ename},#{job},#{salary},#{mgr},#{bonus},#{hiredate},#{deptno})
    </insert>


<!--    分页  -->
    <select id="getEmplimits" parameterType="map" resultType="HashMap">
        select * from emp limit #{startIndex},#{pageSize}
    </select>


<!--    登录查询 -->
    <select id="selectEmpByIdAndPwd" parameterType="map" resultType="HashMap">
        select username,password from user where username = #{username} and password = #{password}
    </select>

<!--    条件查询-->
    <select id="GetUserByIdAndAge" resultType="HashMap">
        select * from user
        <where>
        <if test="sex !=null and sex !=''">
            and sex = #{sex}
        </if>
        <if test="name !=null and name !=''">
            and name like concat('%',#{name},'%')
        </if>
        <if test="age != null and age !=''">
            and age > #{age}
        </if>
        </where>
    </select>

<!--    模糊条件查询-->
    <select id="getUserByNameAndAge" resultType="HashMap">
        select * from user where sex='男'
        <choose>
            <when test="name != null and name != ''">
                and name like concat('%',#{name},'%')
            </when>
            <when test="age != null and age != '' and age != 0">
                and age = #{age}
            </when>
        <otherwise>
            and age &lt; 18
        </otherwise>
        </choose>
    </select>

<!--    批量删除 -->
    <delete id="deleteEmp">
        delete from emp where empno in
        <foreach collection="ids" separator="," open="(" close=")" item="id">
            #{id}
        </foreach>
    </delete>

<!--    修改信息 -->
    <update id="updateEmp" parameterType="com.sky.pojo.Emp">
        UPDATE `kg16`.`emp`
        <set>
            <if test="ename != null and ename != ''">
                ename= #{ename},
            </if>
            <if test="job != null and job != ''">
                job= #{job},
            </if>
            <if test="salary != null and salary != ''">
                salary= #{salary},
            </if>
            <if test="mgr != null and mgr != ''">
                mgr= #{mgr},
            </if>
            <if test="bonus != null and bonus != ''">
                bonus= #{bonus},
            </if>
            <if test="deptno != null and deptno != ''">
                deptno= #{deptno},
            </if>
        </set>
        WHERE
            `empno` = #{empno};
    </update>

    <!--    根据员工id查看他的信息和他的部门信息  *用对象封装* -->
    <resultMap id="kkk" type="com.sky.pojo.Emp" autoMapping="true">
<!--        <id column="empno" property="empno"/>-->
<!--        <result property="deptno" column="deptno"/>-->
        <association property="dept" javaType="com.sky.pojo.Dept">
<!--            <id property="deptno" column="deptno"/>-->
<!--            <result column="dlocation" property="dlocation"/>-->
<!--            <result column="dname" property="dname"/>-->
        </association>
    </resultMap>
    <select id="getEmpById" resultMap="kkk">
        select * from emp left join dept d on emp.deptno = d.deptno where empno=#{id}
    </select>



<!--    解决表面列表不一致-->
    <resultMap id="ccc" type="com.sky.pojo.Dept" autoMapping="true">
<!--                <id column="deptno" property="deptno"/>-->
<!--                <result property="dname" column="dname"/>-->
<!--                <result property="dlocation" column="dlocation"/>-->
        <collection property="emps" javaType="List" ofType="Emp" autoMapping="true">
<!--            <id property="empno" column="empno"/>-->
<!--            <result property="ename" column="ename"/>-->
        </collection>
    </resultMap>
    <select id="getEmpBydeptno" resultMap="ccc">
        SELECT * FROM dept LEFT JOIN emp e on dept.deptno = e.deptno where e.deptno = #{deptno}
    </select>

</mapper>