mybatis foreach集合对象

迷南。 2022-12-10 12:26 321阅读 0赞

**数据库是mysql**  
入参  
TrainUserDistributionMonthForm

@Data
    public class TrainUserDistributionMonthForm extends BaseForm { 
        private static final long serialVersionUID = 3829583027163783711L;
        /** * 〈代理商维度〉 */
        private List<OrgServiceAgent> orgServiceAgents;
        /** * 〈月份〉 */
        private String month;
    }

**controller和service我就不写了 这不是重点**

**mapper接口**

List<TrainUserDistribution> personnelDistributionTrainingThisMonth(TrainUserDistributionMonthForm form);

mapper.xml文件如下

SELECT
            COUNT( a.user_id ) AS sumUsers
            FROM
            t_train_user a
            <where>
            a.`status` IN ( '03', '10', '11' )
            <if test="orgServiceAgents != null">
                <foreach collection="orgServiceAgents" item="orgServiceAgent" index="index" open="and (" separator="or" close=")">
                    <if test="orgServiceAgent.trainType != null and orgServiceAgent.trainType!= ''">
                        a.train_type = #{ orgServiceAgent.trainType}
                    </if>
                    <if test="orgServiceAgent.industry != null and orgServiceAgent.industry!= ''">
                      and  a.industry = #{ orgServiceAgent.industry}
                    </if>
                    <if test="orgServiceAgent.areaCode != null and orgServiceAgent.areaCode!= ''">
                        and  a.area_code = #{ orgServiceAgent.areaCode}
                    </if>
                    <if test="orgServiceAgent.catId != null and orgServiceAgent.catId!= ''">
                        and  a.cat_id = #{ orgServiceAgent.catId}
                    </if>
                </foreach>
            </if>
            <if test="month != null and month!=''">
                AND DATE_FORMAT( a.start_time, '%Y-%m' ) <= #{ month} AND DATE_FORMAT( a.end_time, '%Y-%m' ) >= #{ month}
            </if>
            </where>

主要就是 注意的两个点:  
**一个是foreach标签里面open=“and (” separator="or"**  
  
第二个就是foreach里面的**第一个if标签**

<if test="orgServiceAgent.trainType != null and orgServiceAgent.trainType!= ''">
                a.train_type = #{ orgServiceAgent.trainType}
     </if>

**属性前面的and 不要写**

执行接口之后控制台打印的SQL语句

SELECT
    	COUNT( a.user_id ) AS sumUsers 
    FROM
    	t_train_user a 
    WHERE
    	a.`status` IN ( '03', '10', '11' ) 
    	AND (
    		a.train_type = ? 
    		AND a.industry = ? 
    		AND a.area_code = ? 
    		AND a.cat_id = ? 
    		OR a.train_type = ? 
    		AND a.industry = ? 
    		AND a.area_code = ? 
    		AND a.cat_id = ? 
    		OR a.train_type = ? 
    		AND a.industry = ? 
    		AND a.area_code = ? 
    		AND a.cat_id = ? 
    		OR a.train_type = ? 
    		AND a.industry = ? 
    		AND a.area_code = ? 
    		AND a.cat_id = ? 
    		OR a.train_type = ? 
    		AND a.industry = ? 
    		AND a.area_code = ? 
    		AND a.cat_id = ? 
    	) 
    	AND DATE_FORMAT( a.start_time, '%Y-%m' ) <= ? AND DATE_FORMAT( a.end_time, '%Y-%m' ) >= ?