MySql，MyBatis（常用总结）

需求图片，查询几天内过期的数据

 DATE_ADD(CURDATE(), INTERVAL 时间间隔 DAY)  >= 到期时间
 相反的函数 DATE_SUB()

不确定修改字段（必须修改一个，否则会报错）
<update id="changeStatusTerminEquipByIds" parameterType="pd">
        update
        <include refid="tableName"></include>
        <trim prefix="SET" suffixOverrides=",">
            <if test="STATUS !=null and STATUS !=''">
                STATUS =#{STATUS },
            </if>
            <if test="RID !=null and RID !=''">
                RID =#{RID },
            </if>
        </trim>
        where TERMINALEQUIPMENTS_ID in (${ids})
    </update>

使用IFNULL()处理空数据

    <select id="getSimpleEquByOrgId" parameterType="page" resultType="pd">
        SELECT
        IFNULL(tequ.`ORGSTRUCTURES_ID`,0) A1,
        IFNULL(tequ.`ORGSTRUCTURENAME`,"默认组") A2,
        IFNULL(tequ.`EQUIPMENTNAME`,tequ.`TERMINALEQUIPMENTNUM`) A3,
        tequ.`PMFINTERPRETERID` A4,
        tequ.`TERMINALEQUIPMENTSTATUS` A6,
        CASE tequ.`TERMINALEQUIPMENTSTATUS`
        WHEN 0 THEN '使用中'
        WHEN 1 THEN '未启用'
        WHEN 2 THEN '已到期'
        WHEN 10 THEN '测试'
        END  AS A7,
        tequ.`TERMINALEQUIPMENTNUM` IMEI
        FROM `bd_terminalequipments` tequ``
    </select>

使用GRUOP_CONCAT()以及CONCAT函数，将查询出来TERMINALEQUIPMENTS_ID字段的所有值拼成：“1”，“2”，“3”带引号的数据

<!--查询已过期设备 状态为使用中的 条数或者服务时间到期-->
    <select id="queryOverdueTerminEquip" resultType="String">
        SELECT GROUP_CONCAT(concat('"',TERMINALEQUIPMENTS_ID,'"') separator ',')
            from bd_terminalequipments
            where TERMINALEQUIPMENTSTATUS=0
            and (NOW()>=SERVICELIFEBYTIME
            or  HAVELOCATENUMBER>=SERVICELIFEBYCOUNT)
    </select>

使用date_format（）转换格式，查询本月的数据
    <!--根据组织id,查询激活时间在本月内且状态在使用中0，的终端设备数量-->
    <select id="queryTerminEquipWeekCount" parameterType="pd" resultType="Integer">
        select count(1)
        from
        bd_terminalequipments
        where
        date_format(TERMINALEQUIPMENTACTIVATETIME,'%Y-%m')=date_format(now(),'%Y-%m')
        and TERMINALEQUIPMENTSTATUS=0
        <if test="orgId!=null and orgId!= ''">
            and  ORGENTERPRISEID=#{orgId}
        </if>
    </select>

批量操作格式：

<!--批量修改设备-->
    <update id="distributeEquip" parameterType="pd">
        update
        <include refid="tableName"></include>
        set ORGENTERPRISEID=#{ORGENTERPRISEID}
        where TERMINALEQUIPMENTS_ID in
        <foreach item="item" open="(" close=")" separator="," index="index" collection="ids">
            #{item}
        </foreach>
    </update>

<!-- 批量删除 -->
    <delete id="deleteAll" parameterType="String">
        delete from
        <include refid="tableName"></include>
        where TERMINALEQUIPMENTS_ID in
        <foreach item="item" index="index" collection="array" open="(" separator="," close=")">
                 #{item}
        </foreach>
    </delete>

<insert id="addTrainRecordBatch" useGeneratedKeys="true" parameterType="java.util.List">  
    <selectKey resultType="long" keyProperty="id" order="AFTER">  
        SELECT  
        LAST_INSERT_ID()  
    </selectKey>  
    insert into t_train_record (add_time,emp_id,activity_id,flag)   
    values  
    <foreach collection="list" item="item" index="index" separator="," >  
        (#{item.addTime},#{item.empId},#{item.activityId},#{item.flag})  
    </foreach>  
</insert>