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>