
<select id="selectSingleQuestion" resultType="remarkPaper">

        select

        FrontTitle as  fontTitle,BackTitle as backTitle, Answer as answer

        from fillblankproblem

        where ID

        <foreach item="item" index="index" collection="list" open= " in (" separator="," close=")">

                  #{item.titleId}

        </foreach>

    </select>

多对一

package com.abc.beans;

public class Country {

    private Integer cid;

    private String cname;

    public Country() {

        super();

        // TODO Auto-generated constructor stub

    }

    public Country(String cname) {

        super();

        this.cname = cname;

    }

    public Integer getCid() {

        return cid;

    }

    public void setCid(Integer cid) {

        this.cid = cid;

    }

    public String getCname() {

        return cname;

    }

    public void setCname(String cname) {

        this.cname = cname;

    }

    @Override

    public String toString() {

        return "Country [cid=" + cid + ", cname=" + cname + "]";

    }

}

package com.abc.beans;

public class Minister {

    private Integer mid;

    private String mname;

    // 关联属性

    private Country country;

    public Minister() {

        super();

        // TODO Auto-generated constructor stub

    }

    public Minister(String mname) {

        super();

        this.mname = mname;

    }

    public Integer getMid() {

        return mid;

    }

    public void setMid(Integer mid) {

        this.mid = mid;

    }

    public String getMname() {

        return mname;

    }

    public void setMname(String mname) {

        this.mname = mname;

    }

    public Country getCountry() {

        return country;

    }

    public void setCountry(Country country) {

        this.country = country;

    }

    @Override

    public String toString() {

        return "Minister [mid=" + mid + ", mname=" + mname + ", country=" + country + "]";

    }

}

<!-- 多表连接查询 -->

    <!-- 定义结果映射关系 -->

    <resultMap type="Minister" id="ministerMap">

        <id column="mid" property="mid" />

        <result column="mname" property="mname" />

        <association property="country" javaType="Country">

            <id column="cid" property="cid" />

            <result column="cname" property="cname" />

        </association>

    </resultMap>

    <select id="selectMinisterById" resultMap="ministerMap">

        select mid,mname,cid,cname

        from minister, country

        where countryId=cid and mid=#{xxx}

    </select>

多对一（2）

package com.abc.beans;

public class Country {

    private Integer cid;

    private String cname;

    public Country() {

        super();

        // TODO Auto-generated constructor stub

    }

    public Country(String cname) {

        super();

        this.cname = cname;

    }

    public Integer getCid() {

        return cid;

    }

    public void setCid(Integer cid) {

        this.cid = cid;

    }

    public String getCname() {

        return cname;

    }

    public void setCname(String cname) {

        this.cname = cname;

    }

    @Override

    public String toString() {

        return "Country [cid=" + cid + ", cname=" + cname + "]";

    }

}

package com.abc.beans;

public class Minister {

    private Integer mid;

    private String mname;

    // 关联属性

    private Country country;

    public Minister() {

        super();

        // TODO Auto-generated constructor stub

    }

    public Minister(String mname) {

        super();

        this.mname = mname;

    }

    public Integer getMid() {

        return mid;

    }

    public void setMid(Integer mid) {

        this.mid = mid;

    }

    public String getMname() {

        return mname;

    }

    public void setMname(String mname) {

        this.mname = mname;

    }

    public Country getCountry() {

        return country;

    }

    public void setCountry(Country country) {

        this.country = country;

    }

    @Override

    public String toString() {

        return "Minister [mid=" + mid + ", mname=" + mname + ", country=" + country + "]";

    }

}

    <!-- 多表单独查询 -->

    <select id="selectCountryByMinister" resultType="Country">

        select cid,cname from country where cid=#{jjj}

    </select>

    <!-- 定义结果映射关系 -->

    <resultMap type="Minister" id="ministerMap">

        <id column="mid" property="mid" />

        <result column="mname" property="mname" />

        <association property="country"

                     javaType="Country"

                     select="selectCountryByMinister"

                     column="countryId"/>

    </resultMap>

    <select id="selectMinisterById" resultMap="ministerMap">

        select mid,mname,countryId from minister where mid=#{xxx}

    </select>

使用mybatis进行foreach遍历

2017年03月14日 00:26:09 IT成长之路. 阅读数：5604

在SQL开发过程中，我们会经常使用到in进行搜索，Mybatis中提供了foreach功能，它允许你指定一个集合，然后对集合中的数据进行迭代赋值，如何使用这一功能，很简单，主要是细节要注意。我们先看下foreach的语法

<foreach item="item" index="index" collection="list"

open="(" separator="," close=")">

#{item}

</foreach>

foreach元素的属性包含 item，index，collection，open，separator，close。
    item表示集合中每一个元素进行迭代时的别名，非限定词
    index指定一个名字，用于表示在迭代过程中，每次迭代到的位置，
    open表示该语句以什么开始，这边使用"("，则会在sql进行拼接加上"("，如where id in (
    separator表示在每次进行迭代之间以什么符号作为分隔符，如如where id in ( 1，
    close表示以什么结束，这边使用"("，则会在list遍历后进行拼接加上"）"，如where id in （1,2）

collection表示要遍历的集合

针对collection参数，我们需要考虑入参，而不能随便写，如果如参数List<String>则这样设定

如果参数的类型是List集合, 则在使用时，collection属性要必须指定为 list

<select id="findByIds" resultMap="User">

Select

id ,name

from t_a where id in

<foreach item="item" index="index" collection="list"

open="(" separator="," close=")">

#{item}

</foreach>

</select>

以上的查询，最终会转换成这样的sql，

select id ,name  from t_a where id in (1,2)

但是实际开发中，我们不能局限于list集合，也有可能是数组，数组的方式只要做一下修改即可

<foreach item="item" index="index" collection="array" ....


in最好写在open里面，写在外面有些时候有问题。

巴特西

mybatis关系表

使用mybatis进行foreach遍历

最新文章

热门文章