Mybatis学习记录

最近在工作中有学习了一些平时没用过的Mybatis标签用法

<!--遇到需要多条件调用一个sql时可以使用 trim 将,变为 空 根据column使用if标签判断聚合条件-->
	order by
        <trim prefixOverrides="," suffixOverrides="">
            <if test="column == 'createTime' and order == 'desc'">
                 ,md.create_time desc
            </if>
            <if test="column == 'createTime' and order == 'asc'">
                 ,md.create_time
            </if>
            <if test="column == 'maintainStatus_dictText' and order == 'asc'">
                ,md.maintain_status
            </if>
            <if test="column == 'maintainStatus_dictText' and order == 'desc'">
                ,md.maintain_status desc
            </if>
            <if test="column == 'state' and order == 'asc'">
                ,md.state
            </if>
            <if test="column == 'state' and order == 'desc'">
                ,md.state desc
            </if>
        </trim>

<!--在遇到条件是list时，如果在Java中用for来不断的查询会增加开关数据库消耗，可以使用 where xx in (xx)-->
WHERE
date_format(statistic_day.create_time,'%Y-%m-%d') in
<foreach collection="timeList" index="index" item="createTime" open="(" separator="," close=")">
    #{createTime}
</foreach>
AND tbox_id in
<foreach collection="tboxList" index="index" item="tboxId" open="(" separator="," close=")">
    #{tboxId}
</foreach>

今天是2020-12-10我又来更新了

今天遇到了个之前遇到的问题没解决，今天终于找到了解决方案。
这就是，如果Mybatis子查询需要使用父查询中的条件时，该如何做？
总结虚拟字段:直接上代码

<select id="listDeviceCountSummary" parameterType="java.lang.String"
            resultMap="onlineCountByDeviceTypeMap">
        select t1.device_type        as deviceTypeCode,
               count(t1.device_type) as deviceCount,
               IF(#{miningAreaCode} is null, ' ', #{miningAreaCode}) as miningAreaCode
        from md_device t1
        where t1.section = #{miningAreaCode}
        group by deviceTypeCode;
    </select>
    <resultMap id="onlineCountByDeviceTypeMap" type="org.jeecg.modules.demo.api.bigscreen.entity.Device.DeviceCountSummary">
        <id column="deviceTypeCode" property="deviceTypeCode"/>
        <result property="deviceCount" column="deviceCount"/>
        <collection property="onlineDeviceCount" select="getOnlineCountByDeviceType" column="deviceTypeCode=deviceTypeCode,miningAreaCode=miningAreaCode"/>
    </resultMap>
    <select id="getOnlineCountByDeviceType" resultType="java.lang.Integer">
        select
        count(t1.device_type) as onlineDeviceCount
        from base_machinery_realtime t1
        where t1.section = #{miningAreaCode}
        and timestampdiff(SECOND ,t1.last_location_time,now()) &lt; 20
        and t1.device_type = #{deviceTypeCode}
    </select>

最好是使用if来判断传进来的值，使用虚拟字段，这样在resultMap中的collection->select->column中就可以使用多个字段形式
这样就可以查到了