Mybatis foreach 简单用法

如果List里面是对象的话去查询

dao接口:

/**
   * 查询导购和导购的片区
   */
  List<GuideNameAndRegionIdListDTO> queryGuideNameAndRegionIdListByGuideId(@Param("list") ArrayList<UploadGDNaturalSalesDTO> list);

xml文件:

<!--查询导购和导购的片区 -->
    <select id="queryGuideNameAndRegionIdListByGuideId" resultType="com.feihe.dto.sales.GuideNameAndRegionIdListDTO">
        SELECT tugi.pk_user_guide_id as pkUserGuideId,
               tugi.master_code       AS guideId,
               tugi.name              AS guideName,
               traor.fk_org_region_id AS regionId
        FROM t_user_guide_info tugi
                 LEFT JOIN t_relation_account_organization_region traor
                           ON tugi.fk_account_id = traor.fk_account_id
                               AND traor.ds = 0
        WHERE 1 = 1
        AND tugi.ds = 0
        AND tugi.master_code in
        <foreach collection="list" open="(" close=")" item="item" separator=",">
            #{item.guideId}
        </foreach>

    </select>

如果是Array数组里面都是字符串的话去查询

业务代码:

String[] names = new String[]{"lison", "james"};
    List<TUser> users = mapper.selectForeach4In(names);

dao层:

List<TUser> selectForeach4In(String[] names);

mapper.xml

<!--查询姓名在userName集合里面
        下面意思是 从array数组里面获取东西, 把内容用 逗号分隔 ,
        同时前面和后面分别加上左括号和右括号.
     -->
    <select id="selectForeach4In" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from t_user a
        where a.userName in
        <foreach collection="array" open="(" close=")" item="userName" separator=",">
            #{userName}
        </foreach>
    </select>

如果Set里面成字符串的话去查询

dao接口

List<ProductCodeAndIdDTO> selectProductCodeAndIdMapping(@Param("set") HashSet<String> productCodeSet);

mapper.xmp

<select id="selectProductCodeAndIdMapping" resultType="com.feihe.dto.sales.ProductCodeAndIdDTO">
            SELECT code          AS productCode,
                   pk_product_id AS productId
            FROM t_common_product_info
            WHERE code  in
            <foreach collection="set" open="(" close=")" item="productCode" separator=",">
                #{productCode}
            </foreach>
            and is_delete = 0

    </select>

批量插入功能

业务代码:

TUser user1 = new TUser();
    user1.setUserName("king");
    user1.setRealName("李小京");
    user1.setEmail("[e-mail]");
    user1.setMobile("18754548787");
    user1.setNote("king's note");
    user1.setSex((byte) 1);
    TUser user2 = new TUser();
    user2.setUserName("deer");
    user2.setRealName("陈大林");
    user2.setEmail("[e-mail]");
    user2.setMobile("18723138787");
    user2.setNote("deer's note");
    user2.setSex((byte) 1);

      //批量插入
    int i = mapper.insertForeach4Batch(Arrays.asList(user1, user2));
    System.out.println("------批量更新获取主键的方式与单条insert完全相同--------");
    /*获取批量插入的id*/
    System.out.println(user1.getId());
    /*获取批量插入的id*/
    System.out.println(user2.getId());

dao接口:

int insertForeach4Batch(List<TUser> users);

<!--批量insert 插入  如果想获取批量插入的id也是能获取到的-->
    <insert id="insertForeach4Batch" useGeneratedKeys="true" keyProperty="id">
        insert into t_user (userName, realName,
        sex, mobile,email,note,
        position_id)
        values
        <foreach collection="list" separator="," item="user">
            (
            #{user.userName,jdbcType=VARCHAR},
            #{user.realName,jdbcType=VARCHAR},
            #{user.sex,jdbcType=TINYINT},
            #{user.mobile,jdbcType=VARCHAR},
            #{user.email,jdbcType=VARCHAR},
            #{user.note,jdbcType=VARCHAR},
            #{user.position.id,jdbcType=INTEGER}
            )
        </foreach>


    </insert>

List集合里面是基本数据类型

list只有基本数据类型的情况下:

dao接口

List<Long> selectInstanceId(@Param("fkDefinitionId") Long fkDefinitionId,
                @Param("departList") List<Long> departList,
                @Param("fkDoRole") long fkDoRole);

daomapper .xml:

<select id="selectInstanceId" resultType="java.lang.Long">
            SELECT fk_instance_id
            FROM t_approval_do a
            WHERE a.fk_definition_id = #{fkDefinitionId}
            AND a.fk_do_depart IN
            <foreach collection="departList" open="(" close=")" item="fkDoDepart" separator=",">
                #{fkDoDepart}
            </foreach>
            AND a.fk_do_role = #{fkDoRole}
            AND a.task_state = 0
            AND a.is_delete = 0
    </select>

说明: