mybatis语句(一)java枚举、遍历list、遍历map查询

1.java枚举对应字段查询

 public static String findRecordByUserIdAndBizType() {
            return "SELECT * FROM "+TABLE+" WHERE userId = #{userId} and bizType = #{coinBizType, typeHandler=" + EnumTypeHandler.class.getCanonicalName() + "} order by createdTime desc";
        }

2.遍历list

public static String findRecordByUserIdAndBizType() {
            return "<script>"
                    + "SELECT * FROM "+TABLE+" WHERE userId = #{userId}"
                    +"and bizType IN "
                    + "<foreach item='item' index='index' collection='coinBizTypes' open='(' separator=',' close=')'>"
                    + "#{item, typeHandler=" + EnumTypeHandler.class.getCanonicalName() + "}"
                    + "</foreach>"
                    + "</script>";
        }

public static String findByAppModuleAndSendEndTimeAfterNowItemIds() {
            return "<script>"
                    + "SELECT * FROM "+TABLE+" WHERE appName = #{appName} AND module = #{module} AND sendEndTime > #{now} and active = 1 "
                    +"and itemId IN "
                    + "<foreach item='item' index='index' collection='itemIds' open='(' separator=',' close=')'>"
                    + "#{item}"
                    + "</foreach>"
                    + "</script>";
        }

3.遍历map作为条件查询
参考：参考文章

@Override
    public List<ExPacketRecordV2> findByUserAndPeriodMap(String userId, String appName, Map<String, String> searchTypeTimeMap) {
        return mapper.findByUserAndPeriodMap(userId, appName, searchTypeTimeMap);
    }
    
 @ResultMap("SkuRecordResultMap")
        @SelectProvider(type = ExchangePacketSqlProvider.class, method = "findByUserAndPeriodMap")
        List<ExPacketRecordV2> findByUserAndPeriodMap( @Param("userId")String userId,@Param("appName") String appName,
                                                       @Param("searchMap") Map<String, String> searchTypeTimeMap);

sql：
/**
         * select userId,skuId,amount,coin,periodType from ex_packet_record_v2 where userId='2088312991026016' and appName='shell'  and active = 0
         * and  (( periodType='YEARLY' and createdTime>'2020-01-01' ) or ( periodType='DAILY' and createdTime>'2020-11-02' ) or ( periodType='MONTHLY' and createdTime>'2020-11-01' ) or ( periodType='GLOBALLY' and createdTime>'1978-08-23' ))
         * @return
         */
        public static String findByUserAndPeriodMap() {
            return "<script>"+
                    "select userId,skuId,amount,coin,periodType from " + TABLE_RECORD +
                    " where userId=#{userId} and appName=#{appName}  and active = 1 and " +
                    "<foreach collection='searchMap.keys' separator='or' item='key' index='index' open='(' close=')'> " +
                    " ( periodType=#{key} and createdTime>#{searchMap[${key}]} )" +
                    " </foreach> "
                    + "</script>";
        }