myBaits 插入多条数据 foreach 查询数据

foreach元素的属性主要有 item，index，collection，open，separator，close。
collection：要循环的集合
item表示集合中每一个元素进行迭代时的别名.
index指 定一个名字，用于表示在迭代过程中，每次迭代到的位置.
open表示该语句以什么开始，separator表示在每次进行迭代之间以什么符号作为分隔 符.
close表示以什么结束.

froeach使用中 传入参数可以为Map、list、String[] aArray 、对象

foreach : 用的地方为：保存数据； 根据Id批量删除 ; in 集合中（(”,”)） 等

1、使用的model

public class EmployeeRelationsModel{
    private String id;

    private String pid;

    private String cid;

    private String adminId;

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id == null ? null : id.trim();
    }

    public String getPid() {
        return pid;
    }

    public void setPid(String pid) {
        this.pid = pid == null ? null : pid.trim();
    }

    public String getCid() {
        return cid;
    }

    public void setCid(String cid) {
        this.cid = cid == null ? null : cid.trim();
    }


    public String getAdminId() {
        return adminId;
    }

    public void setAdminId(String adminId) {
        this.adminId = adminId;
    }


}

2、组装数据 list,调用myBatis 执行

public void saveEmployeeRelations(EmployeeRelationsModel employeeRelationsmodel) {
        List<EmployeeRelationsModel> list = new ArrayList<EmployeeRelationsModel>();
        String cIds[] = employeeRelationsmodel.getCid().split(",");
        for (int i = 0 ,length = cIds.length; i < length; i++) {
            EmployeeRelationsModel model= new EmployeeRelationsModel();
            model.setId(PKGenarator.getId());
            model.setPid(employeeRelationsmodel.getPid());
            model.setCid(cIds[i]);
            list.add(model);
        }
        this.sqlSession.insert("saveEmployeeRelations", list);
    }

3、sql代码

<resultMap id="BaseResultMap" type="com.fbd.core.app.user.model.EmployeeRelationsModel" >
    <id column="id" property="id" jdbcType="VARCHAR" />
    <result column="pid" property="pid" jdbcType="VARCHAR" />
    <result column="cid" property="cid" jdbcType="VARCHAR" />
    <result column="admin_id" property="adminId" jdbcType="VARCHAR" />
  </resultMap>

//插入多条数据

<insert id="saveEmployeeRelations" parameterType="java.util.List">
        insert into employee_relations (id, pid, cid)
        values
        <foreach collection="list" item="item" index="index" separator=",">
            (#{item.id},#{item.pid},#{item.cid})
        </foreach>
  </insert>

二 查询数据

1、单纯传入list 查询数据

<select id="selectEmployeeListTest" resultMap="BaseResultMap" parameterType="java.util.List" >
    select 
    *
    from employee_relations
    where pid in  

    <foreach item="item" index="index" collection="list"  open="(" separator="," close=")">  
           #{item}  
    </foreach> 
  </select>

2、model中包含list
①使用model

public class EmployeeLogsModel extends BaseModel{
    private String id;

    private String employeeNo;

    private List<Object> listObject = new  ArrayList<Object>();


    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id == null ? null : id.trim();
    }

    public String getEmployeeNo() {
        return employeeNo;
    }

    public void setEmployeeNo(String employeeNo) {
        this.employeeNo = employeeNo == null ? null : employeeNo.trim();
    }
    public List<Object> getListObject() {
        return listObject;
    }

    public void setListObject(List<Object> listObject) {
        this.listObject = listObject;
    }

②java代码组装数据

public void test(){
    EmployeeLogsModel model = new EmployeeLogsModel ();
    List<Object> listObject = new ArrayList<Object>();
    list.add("1");
    list.add("2");
    model.setListObject(listObject);
    this.sqlSession.selectList("EmployeeLogsModelMapper.selectList",model );
}

③sql查询数据

select * from employee_logs 
<include refid="base_where"></include>
order by create_time desc
<if test="endPage>0" >
    LIMIT #{startPage},#{endPage}
</if>

<sql id="base_where">
    <where>
        <if test="employeeNo != null and employeeNo != ''" >
           and employee_no = #{employeeNo,jdbcType=VARCHAR}
        </if>

        <if test="listObject !=null and listObject.size!=0">
                AND employee_no in
                <foreach collection="listObject" item="item"
                    index="index" open="(" close=")" separator=",">
                    #{item}
                </foreach>
       </if>
    </where>  
  </sql>