MyBatis 关联查询的实现：多对多

 

2个实体：订单、商品，一个订单可以包含多种商品，同时一种商品可以属于多个订单，即多对多。

 

商品表goods_tb：

 

 

订单表order_tb：

no是订单编号，user_id与用户表的id关联。

 

 

需要新建一张中间表order_item_tb，引入2个“多”的主键作为外键，把这2个“多”联系起来：

purchase_amount是该种商品的购买数量。

 

 

---

 

 

使用嵌套结果实现一对多

（一）编写pojo类

package com.chy.pojo;

public class goods {
    private integer id;  //商品id
    private string goodsname;  //商品名称
    private float goodsprice;  //商品单价
    private integer purchaseamount;  //购买数量

    public integer getid() {
        return id;
    }

    public void setid(integer id) {
        this.id = id;
    }

    public string getgoodsname() {
        return goodsname;
    }

    public void setgoodsname(string goodsname) {
        this.goodsname = goodsname;
    }

    public float getgoodsprice() {
        return goodsprice;
    }

    public void setgoodsprice(float goodsprice) {
        this.goodsprice = goodsprice;
    }

    public integer getpurchaseamount() {
        return purchaseamount;
    }

    public void setpurchaseamount(integer purchaseamount) {
        this.purchaseamount = purchaseamount;
    }

    @override
    public string tostring() {
        return "goods{" +
                "id=" + id +
                ", goodsname='" + goodsname + '\'' +
                ", goodsprice=" + goodsprice +
                ", purchaseamount=" + purchaseamount +
                '}';
    }
}

 

package com.chy.pojo;

import java.util.list;

public class order {
    private integer no;
    private integer userid;
    private list<goods> goodslist;  //包含的商品

    public integer getno() {
        return no;
    }

    public void setno(integer no) {
        this.no = no;
    }

    public integer getuserid() {
        return userid;
    }

    public void setuserid(integer userid) {
        this.userid = userid;
    }

    public list<goods> getgoodslist() {
        return goodslist;
    }

    public void setgoodslist(list<goods> goodslist) {
        this.goodslist = goodslist;
    }

    @override
    public string tostring() {
        return "order{" +
                "no=" + no +
                ", userid=" + userid +
                ", goodslist=" + goodslist +
                '}';
    }
}

在哪个pojo中使用list来关联其他实体，需要根据业务需求来确定。

 

 

（二）编写mapper接口、映射文件

package com.chy.mapper;

import com.chy.pojo.order;

public interface ordermapper {
    //根据orderid查询订单信息
    public order queryorderbyorderno(integer orderno);
}

 

<?xml version="1.0" encoding="utf-8" ?>
<!doctype mapper  public
        "-//mybatis.org//dtd mapper 3.0//en"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.chy.mapper.ordermapper">
    <select id="queryorderbyorderno" parametertype="integer" resultmap="orderresultwithgoods">
        select order_tb.*,goods_tb.*,order_item_tb.purchase_amount
        from order_tb,goods_tb,order_item_tb
        where order_tb.no=#{no} and order_item_tb.order_no=order_tb.no and goods_tb.id=order_item_tb.goods_id
    </select>
    <resultmap id="orderresultwithgoods" type="order">
        <id property="no" column="no"/>
        <result property="userid" column="user_id"/>
        <collection property="goodslist" oftype="goods">
            <id property="id" column="id"/>
            <result property="goodsname" column="goods_name"/>
            <result property="goodsprice" column="goods_price"/>
            <result property="purchaseamount" column="purchase_amount"/>
        </collection>
    </resultmap>
</mapper>

三表联合查询，sql语句很长，如果觉得表名、字段名不好写，可以用as设置表名、字段名的别名。

不是每个pojo类都要写对应的mapper接口、映射文件，写哪些，看业务需求。

 

 

（三）使用

package com.chy.utils;

import org.apache.ibatis.io.resources;
import org.apache.ibatis.session.sqlsession;
import org.apache.ibatis.session.sqlsessionfactory;
import org.apache.ibatis.session.sqlsessionfactorybuilder;
import java.io.ioexception;
import java.io.inputstream;

public class mybatisutils {
    private static sqlsessionfactory sqlsessionfactory;

    static {
        try {
            inputstream inputstream = resources.getresourceasstream("mybatis-config.xml");
            sqlsessionfactory = new sqlsessionfactorybuilder().build(inputstream);
        } catch (ioexception e) {
            e.printstacktrace();
        }
    }

    public static sqlsession getsqlsession(){
        return sqlsessionfactory.opensession();
    }
}

 

package com.chy.test;

import com.chy.mapper.ordermapper;
import com.chy.pojo.order;
import com.chy.utils.mybatisutils;
import org.apache.ibatis.session.*;

public class test {
    public static void main(string[] args) {
        sqlsession sqlsession = mybatisutils.getsqlsession();
        ordermapper mapper = sqlsession.getmapper(ordermapper.class);
        order order = mapper.queryorderbyorderno(1);
        system.out.println(order);
        sqlsession.close();
    }
}

 

 

结果：

order{no=1, userid=1, goodslist=[goods{id=1, goodsname='抽纸', goodsprice=6.5, purchaseamount=1}, goods{id=2, goodsname='中华', goodsprice=80.0, purchaseamount=2}]}

 

 

也可以使用嵌套查询来实现多对多，但嵌套查询缺点较多，不推荐。

mybatis让开发人员专注于数据库的设计、sql语句的编写，而不需要花费过多精力在jdbc的底层操作上。