mybaties记录

1. in

<select id="getInfos4Map" resultMap="BaseResultMap" parameterType="map">
        select
        <include refid="Base_Column_List"/>
        from goods_marketsite_mapping
        <where>1=1
            <if test="goodCodes != null ">
                and goods_code in
                <foreach collection="goodCodes" item="item" index="index" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
        </where>
</select>
public List<GoodsMarketsiteMapping> getInfos(Set goodCodes) {
        Map<String, Object> map = new HashMap<>();
        map.put("goodCodes", goodCodes);
        return goodsMarketsiteMappingMapper.getInfos4Map(map);
}


<select id="getInfosByAgentCodeAndOperateTimeBetween" resultMap="BaseResultMap" parameterType="java.util.List">
        SELECT s.*,g.GOOD_ALIAS AS product_name
        FROM shoppingcart s
        LEFT JOIN goods_goods g ON s.goods_codes=g.goods_code
        LEFT JOIN agent_agent a ON s.AGENT_CODE=a.agent_code
        WHERE s.OPERATE_TIME >=a.apply_date
        AND DATE_FORMAT(s.OPERATE_TIME,'%Y-%m-%d %H:%i:%s') &lt;= DATE_FORMAT(DATE_ADD(a.apply_date,INTERVAL 20
        MINUTE),'%Y-%m-%d %H:%i:%s')
        AND s.agent_code IN
        <foreach collection="list" item="agentCode" index="index" open="(" separator="," close=")">
            #{agentCode}
        </foreach>
    </select>
public List<ShopCart> getInfosByAgentCodeAndOperateTimeBetween(List<String> agentCodes) {
        return shopCartMapper.getInfosByAgentCodeAndOperateTimeBetween(agentCodes);
}

2.date

//转义<![CDATA[ *** ]]>
 <if test="startApplyTime != null">
  		and apply_time <![CDATA[ >= ]]> #{startApplyTime}
  </if>
  <if test="endApplyTime != null">
    	and apply_time <![CDATA[ <= ]]> #{endApplyTime}
  </if>
//时间计算作为条件
<select id="getNeedDeleteSite" resultMap="BaseResultMap" parameterType="java.lang.Integer">
        select
        <include refid="Base_Column_List"/>
        from service_marketsite
        <![CDATA[
    where
    service_state = '03'
    and
    DATE_ADD(end_date,INTERVAL #{renewalDay} DAY) <  DATE_FORMAT(NOW(),'%Y-%c-%e 00:00:00')
    ]]>
</select>

//以Map为入参
<select id="getAgentUserInfos4Crm" resultType="java.util.HashMap" parameterType="map" >
  	select *
	    from agent_agent a
	    left join agent_userinfo u on a.adm_user_code=u.user_code
	    left join agent_userinfo_auth au on au.agent_code=a.agent_code
	    left join party p on p.id=a.organ_code
	    left join agent_register_type art on art.code=a.register_type and art.status ='01' 
	    left join shoppingcart s on s.agent_code=a.agent_code
	    and DATE_FORMAT(s.operate_time,'%Y-%m-%d %H:%i:%s') &gt;= DATE_FORMAT(a.apply_date,'%Y-%m-%d %H:%i:%s')
	    and DATE_FORMAT(s.operate_time,'%Y-%m-%d %H:%i:%s') &lt;= DATE_FORMAT(DATE_ADD(a.apply_date,INTERVAL 20 MINUTE),'%Y-%m-%d %H:%i:%s')
	    <where>
	      <if test="applyDateStartDate != null and '' != applyDateStartDate">
	        and DATE_FORMAT(a.apply_date,'%Y-%m-%d') &gt;=  #{applyDateStartDate}
	      </if>
	      <if test="applyDateEndDate != null and '' != applyDateEndDate">
	        and DATE_FORMAT(a.apply_date,'%Y-%m-%d') &lt;=  #{applyDateEndDate}
	      </if>
	    </where>
	    ) as a
</select>
 Map<String, Object> map = new HashMap<String, Object>();
        Calendar c = Calendar.getInstance();
        c.add(Calendar.DATE, 30);
        SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
        map.put("applyDateStartDate", null == startDate ? formatter.format(c.getTime()) : formatter.format(startDate));
        map.put("applyDateEndDate", null == endDate ? formatter.format(new Date()) : formatter.format(endDate));
        map.put("agentCode", agentCode);
        map.put("shoppingCartIsEmpty", shoppingCartIsEmpty);
        return userInfoService.getAgentUserInfos4Crm(map);
}