（二）MyBatis-增删改查以及分页

增删改查以及分页

增

<insert id="insert" parameterType="com.newNet.libra.rdbms.lottery.entity.UserVotesInfo" >
      insert into 
      USER_VOTES_INFO ( ID, ENTER_ID, USER_PHONE,CREATE_TIME, ACTIVITY_ID, VOTE_NUM, REMARKS )
      values  (  #{id,jdbcType=DECIMAL}, 
		 #{enterId,jdbcType=DECIMAL},
                 #{userPhone,jdbcType=VARCHAR}, 
		 #{createTime,jdbcType=TIMESTAMP},
                 #{activityId,jdbcType=DECIMAL}, 
		 #{voteNum,jdbcType=DECIMAL},
                 #{remarks,jdbcType=VARCHAR} 
	      )
</insert>

原生批量插入

INSERT into tb_red(id,price,date... ...) VALUES
(1000, 8001, '2016-10-10 10:59:59', 1, 8001, '测试用户1000', '红名单0', '男', '膜法学院', '被测'),
(1001, 8001, '2016-10-10 11:00:00', 2, 8001, '测试用户1001', '红名单1', '男', '膜法学院', '被测'),
(1002, 8001, '2016-10-10 11:00:01', 0, 8001, '测试用户1002', '红名单2', '男', '膜法学院', '被测'),
(1003, 8001, '2016-10-11 10:59:59', 1, 8001, '测试用户1003', '红名单3', '男', '膜法学院', '被测'), 
(1004, 8001, '2016-10-11 11:00:00', 2, 8001, '测试用户1004', '红名单4', '男', '膜法学院', '被测'),
(1005, 8001, '2016-10-11 11:00:01', 0, 8001, '测试用户1005', '红名单5', '男', '膜法学院', '被测');

MySql批量插入

<insert id="addDemoList" parameterType="ArrayList">  
    INSERT INTO `demo` ( userId, financingId`,smashed, bonusAmount)  
    VALUES  
    <foreach collection="list" item="item" index="index" separator=",">  
            ( #{item.userId}, #{item.financingId},#{item.smashed}, #{item.bonusAmount})  
    </foreach>  
</insert> 

ORACLE批量插入

<insert id="batchInsertCompanyDate" parameterType="java.util.List">
      insert into COMPANY (
          order_Id,merchant_Order,delivery_Number,supplier_Number,tele,cost_Amount,
          amount,sale_Amount,order_Time,completion_Time,operator,order_Status
      )
      <foreach collection="list" item="item" index="index" separator="union all" >
            (select 
                  #{item.orderId,jdbcType=VARCHAR},
                  #{item.merchantOrder,jdbcType=VARCHAR},
                  #{item.deliveryNumber,jdbcType=VARCHAR},
                  #{item.supplierNumber,jdbcType=VARCHAR},
                  #{item.tele},
                  #{item.costAmount},
                  #{item.amount},
                  #{item.saleAmount},
                  #{item.orderTime,jdbcType=VARCHAR},
                  #{item.completionTime,jdbcType=VARCHAR},
                  #{item.operator,jdbcType=VARCHAR},
                  #{item.orderStatus,jdbcType=VARCHAR}
             from dual)
      </foreach>
</insert>

parameterType:为 ArrayList<Company>  

删

 <delete id="deleteByPrimaryKey" parameterType="java.lang.Long" >
    delete from USER_VOTES_INFO
    where ID = #{id,jdbcType=DECIMAL}
 </delete>

批量删除

//批量删除员工信息
void batchDeleteEmps(int[] empno);

<!-- 批量删除员工信息 -->
<delete id="batchDeleteEmps" parameterType="int">
     delete from emp where empno in
     <foreach item="empnoItem" collection="array" open="(" separator="," close=")">
         #{empnoItem}
     </foreach>
</delete>

<!-- 参数说明 -->
<!-- 
    emp   : 表名
    empno : 字段名
    collection:表示类型,这里参数是数组,就写成array,如果是集合,就写成list
    item  : 是一个变量名,自己随便起名
-->

更新

<update id="updateStudent" parameterType="StudentEntity">  
        UPDATE STUDENT_TBL  
            SET STUDENT_TBL.STUDENT_NAME = #{studentName},   
                STUDENT_TBL.STUDENT_SEX = #{studentSex},  
                STUDENT_TBL.STUDENT_BIRTHDAY = #{studentBirthday},  
                STUDENT_TBL.CLASS_ID = #{classEntity.classID}  
         WHERE STUDENT_TBL.STUDENT_ID = #{studentID};     
</update> 

批量更新1

比较普通的写法，是通过循环，依次执行update语句。
<update id="updateBatch"  parameterType="java.util.List">  
    <foreach collection="list" item="item" index="index" open="" close="" separator=";">
        update course
        <set>
            name=${item.name}
        </set>
        where id = ${item.id}
    </foreach>      
</update>

批量更新2

原生sql
UPDATE course
    SET name = CASE id 
        WHEN 1 THEN 'name1'
        WHEN 2 THEN 'name2'
        WHEN 3 THEN 'name3'
    END, 
    title = CASE id 
        WHEN 1 THEN 'New Title 1'
        WHEN 2 THEN 'New Title 2'
        WHEN 3 THEN 'New Title 3'
    END
WHERE id IN (1,2,3)

批量更新3（原生sql的mybatis写法）

<update id="updateBatch" parameterType="list">
     update course

     <trim prefix="set" suffixOverrides=",">
           <trim prefix="peopleId =case" suffix="end,">
                 <foreach collection="list" item="i" index="index">
                      <if test="i.peopleId!=null">
                          when id=#{i.id} then #{i.peopleId}
                      </if>
                 </foreach>
            </trim>

            <trim prefix=" roadgridid =case" suffix="end,">
                 <foreach collection="list" item="i" index="index">
                      <if test="i.roadgridid!=null">
                          when id=#{i.id} then #{i.roadgridid}
                      </if>
                 </foreach>
            </trim>
              
            <trim prefix="type =case" suffix="end," >
                 <foreach collection="list" item="i" index="index">
                      <if test="i.type!=null">
                          when id=#{i.id} then #{i.type}
                      </if>
                 </foreach>
            </trim>
      </trim>

      where

      <foreach collection="list" separator="or" item="i" index="index" >
           id=#{i.id}
      </foreach>
</update>

查

    <select id="getManagers" resultType="com.atguigu.crm.entity.Contact">
            （起个别名让查出来列和属性对应上就不用写resultMap了；或者设置mapper配置列字段去掉中间下划线和属性名对应也可以不起别名）
        select c.id,c.name,c.customer_id  customerId 
        from contacts c
        where customer_id = #{id}
    </select>

    <select id="get" resultMap="CustomerMap">
        select c.id,c.no,c.name,c.region,c.satify,c.state,c.credit,c.customer_level as "level",
        c.address,c.zip,c.tel,c.fax,c.websit,c.manager_id as "managerId",
        c.licence_no,c.chief,c.bankroll,c.turnover,c.bank,c.bank_account,c.local_tax_no, c.national_tax_no,
        t.name as "t_name", t.id as "t_id",
        o.id as "o_id",o.no as "o_no"
        from  customers c
        left outer join contacts t
        on c.manager_id = t.id
        left outer join orders o
        on c.id = o.customer_id
        where c.id = #{id}
    </select>
    <resultMap type="com.atguigu.crm.entity.Customer" id="CustomerMap">
        <id column="id" property="id"/>
        <result column="no" property="no"/>
        <result column="name" property="name"/>
        <result column="region" property="region"/>
        <result column="satify" property="satify"/>
        <result column="state" property="state"/>
        <result column="credit" property="credit"/>
        <result column="level" property="level"/>
        <result column="address" property="address"/>
        <result column="zip" property="zip"/>
        <result column="tel" property="tel"/>
        <result column="fax" property="fax"/>
        <result column="websit" property="websit"/>
        <result column="licence_no" property="licenceNo"/>
        <result column="chief" property="chief"/>
        <result column="bankroll" property="bankroll"/>
        <result column="turnover" property="turnover"/>
        <result column="bank" property="bank"/>
        <result column="bank_account" property="bankAccount"/>
        <result column="local_tax_no" property="localTaxNo"/>
        <result column="national_tax_no" property="nationalTaxNo"/>
        <result column="managerId" property="manager.id"/>
        <collection property="contacts" javaType="com.atguigu.crm.entity.Contact">
            <id column="t_id" property="id"/>
            <result column="t_name" property="name"/>
            <result column="id" property="customer.id"/>
        </collection>
        <collection property="orders" javaType="com.atguigu.crm.entity.Order">
            <id column="o_id" property="id"/>
            <result column="o_no" property="no"/>
        </collection>
    </resultMap>

mysql分页查询

<select id="findAllByNameWithPage" parameterType="map" resultMap="studentMap">
    SELECT id,name,sal
    FROM student
    WHERE name LIKE #{pname}
    limit #{pstart},#{psize}
</select>

oracle分页查询

<select id="getContent" resultType="com.atguigu.crm.entity.CustomerDrain">
    select *
    from (
          select  rownum rn,d.id,d.last_order_date as "lastOrderDate",d.drain_date as "drainDate", d.reason
          from  customer_drains d
                left outer join customers c
                on d.customer_id = c.id
                left outer join contacts t
                on c.manager_id = t.id
          <where>
                d.status = #{status}
                <if test="customerName != null and customerName != ''">
                    and c.name like #{customerName}
                </if>
                <if test="customerManagerName != null and customerManagerName != ''">
                    and t.name like #{customerManagerName}
                </if>
           </where>
     )
     <![CDATA[
     where rn >= #{fromIndex} and rn < #{endIndex}
     ]]>
</select> 

<select id="getTotalElements" resultType="long">
        select count(c.id)
        from  customer_drains d
        left outer join customers c
        on  d.customer_id = c.id
        left outer join contacts t
        on c.manager_id = t.id
        <where>
            d.status = #{status}
            <if test="customerName != null and customerName != ''">
                  and c.name like #{customerName}
            </if>
            <if test="customerManagerName != null and customerManagerName != ''">
                  and t.name like #{customerManagerName}
            </if>
        </where>
</select>