mybatis关系表

<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

 

 版权声明：本文为博主原创文章，未经博主允许不得转载 https://blog.csdn.net/weixin_37173488/article/details/61956875

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

1.  
   <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>则这样设定

1.  
   如果参数的类型是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>

    

2.  

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

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

 

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

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

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