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="mapper.EmployeeMapper">

    <!-- select语句
        结果集的映射有以下两种方式：
        resultType="": 自动结果集映射，要求结果集列名与实体类的属性相同时可以用这种方式或通过取别名的方式变成一样的。
        resultMap="xxxResultMap"：（首选）结果集映射，通常用来解决结果集列名与实体类的属性　不相同时
        <resultMap id="xxxResultMap"> ... </resultMap>
     -->
    <resultMap id="employeeResultMap" type="Employee">
        <id column="empno" property="empno"/>
        <result column="ename" property="ename"/>
        <result column="job" property="job"/>
        <result column="hiredate" property="hiredate"/>
        <result column="mgr" property="mgr"/>
        <result column="sal" property="sal"/>
        <result column="comm" property="comm"/>
        <result column="deptno" property="deptno"/>
    </resultMap>


    <select id="selectAll" resultType="Employee">
        select * from employee
    </select>


    <select id="findAll" resultMap="employeeResultMap">
        select * from employee where 1=1
    </select>

    <!-- 按主键查找
        parameterType="int": 传递给该sql语句占位符参数值的类型
        #{expression}: 这种语法里的表达式其实是 OGNL 表达式
        所有参数值类型为简单类型（基本类型和包装类型、String）的，获取其值用特殊的 #{id} 表达式，mybatis在解析
        此语句时会将 #{id} 解析为占位符 “？”
    -->
    <select id="selectByPrimaryKey" parameterType="int" resultMap="employeeResultMap">
        select * from employee where empno=#{id}
    </select>


    <!-- 多条件查询
        mybatis 最强大就是它的动态SQL（SQL语名是通过一些 xml元素动态构建出来的）,动态构建SQL的元素有：
        <where>, <trim>, <if>, <foreach>, <choose><when><otherwise>, <set>等等
     -->
    <select id="selectByCondition" parameterType="employee" resultMap="employeeResultMap">
        SELECT * FROM EMPLOYEE
        <where> <!-- 会生成一个where关键字,并且<where>元素会为我们清除第一个and -->
            <if test="ename != null">
                AND ENAME=#{ename}
            </if>
            <!--
            <if test="job != null">
                AND JOB=#{job}
            </if>
            -->
            <if test="sal != null">
                AND SAL=#{sal}
            </if>
            <!-- 如果条件运算符是小于号要把这个条件放到CDATA元素内 -->
            <if test="hiredate != null">
                <![CDATA[ AND HIREDATE <= #{hiredate} ]]>
            </if>
            <if test="jobs.size() > 0">
                 AND
                <foreach item="job" collection="jobs" open="job in (" close=")" separator=",">
                    #{job}
                </foreach>
            </if>
        </where>
    </select>


    <!-- 插入
        对于mysql数据库可用
        keyProperty=""
        useGeneratedKeys="true"  10000 ==> setEmpno(10000);

        插入如果某字段的值可以为空时，那要让mybatis对此字段时行空值处理，这不特性不是mybatis需要，而是JDBC驱动所需
    -->
    <insert id="insert_1" parameterType="employee" keyProperty="empno" useGeneratedKeys="true">
        INSERT INTO EMPLOYEE(empno,ename,job,mgr,hiredate,sal,comm,deptno)
            VALUES(#{empno},#{ename},#{job,jdbcType=VARCHAR},#{mgr,jdbcType=INTEGER},#{hiredate,jdbcType=DATE},#{sal,jdbcType=DOUBLE},#{comm,jdbcType=VARCHAR},#{deptno,jdbcType=INTEGER})
    </insert>

    <!-- 插入方式2
        插入的列和插入的值都动态生成
     -->
    <sql id="insert_column">
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="ename != null">ENAME,</if>
            <if test="job != null">JOB,</if>
            <if test="mgr != null">MGR,</if>
            <if test="hiredate != null">HIREDATE,</if>
            <if test="sal != null">SAL,</if>
            <if test="comm != null">COMM,</if>
            <if test="deptno != null">DEPTNO,</if>
        </trim>
    </sql>

    <insert id="insert_2" parameterType="employee" keyProperty="empno" useGeneratedKeys="true">
        INSERT INTO EMPLOYEE <include refid="insert_column"/>
        <!--
        prefix="values(" : 生成的前缀字符
        suffix=")" : 生成的后缀字符
        suffixOverrides=",": 将后缀字符 “，” 删除
        -->
        <trim prefix="values(" suffix=")" suffixOverrides=",">
            <if test="ename != null">#{ename,jdbcType=VARCHAR},</if>
            <if test="job != null">#{job,jdbcType=VARCHAR},</if>
            <if test="mgr != null">#{mgr,jdbcType=INTEGER},</if>
            <if test="hiredate != null">#{hiredate,jdbcType=DATE},</if>
            <if test="sal != null">#{sal,jdbcType=DOUBLE},</if>
            <if test="comm != null">#{comm,jdbcType=DOUBLE},</if>
            <if test="deptno != null">#{deptno,jdbcType=INTEGER},</if>
        </trim>
    </insert>

    <!-- 更新 -->
    <update id="update_1" parameterType="employee">
        update employee
        <set> <!-- 产生set子句,并且会删除最后一个逗号 -->
            <if test="ename != null">ENAME=#{ename},</if>
            <if test="job != null">JOB=#{job},</if>
            <if test="mgr != null">MGR=#{mgr},</if>
            <if test="hiredate != null">HIREDATE=#{hiredate},</if>
            <if test="sal != null">SAL=#{sal},</if>
            <if test="comm != null">COMM=#{comm},</if>
            <if test="deptno != null">DEPTNO=#{deptno},</if>
        </set>
        where EMPNO=#{empno}
    </update>

    <update id="update_2" parameterType="employee">
        update employee
        <trim prefix="set" suffixOverrides=","> <!-- 产生set子句,并且会删除最后一个逗号 -->
            <if test="ename != null">ENAME=#{ename},</if>
            <if test="job != null and job.trim().length() > 0">JOB=#{job},</if>
            <if test="mgr != null">MGR=#{mgr},</if>
            <if test="hiredate != null">HIREDATE=#{hiredate},</if>
            <if test="sal != null">SAL=#{sal},</if>
            <if test="comm != null">COMM=#{comm},</if>
            <if test="deptno != null">DEPTNO=#{deptno},</if>
        </trim>
        where EMPNO=#{empno}
    </update>

    <delete id="delete" parameterType="int">
        DELETE FROM EMPLOYEE WHERE EMPNO=#{empno}
    </delete>



</mapper>