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

有2个实体：用户、订单，一个用户可以拥有多个订单，同时这多个订单属于一个用户，即一对多。

 

 

user_tb：

 

 

order_tb：

在“多”的一方（order）添加“一”的一方（user）的主键（user_id）作为外键。

 

 

 

---

 

 

 

使用嵌套结果实现一对多

（1）给2个实体都编写pojo类，需要在“一”的一方写个list来关联“多”的一方

package com.chy.pojo;

public class order {
    private integer no;
    private integer goods_id;
    private integer goods_amount;

    public integer getno() {
        return no;
    }

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

    public integer getgoods_id() {
        return goods_id;
    }

    public void setgoods_id(integer goods_id) {
        this.goods_id = goods_id;
    }

    public integer getgoods_amount() {
        return goods_amount;
    }

    public void setgoods_amount(integer goods_amount) {
        this.goods_amount = goods_amount;
    }

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

 

 

package com.chy.pojo;

import java.util.list;

public class user {
    private integer id;
    private string username;
    private string password;
    private string tel;
    private string address;
    private list<order> orderlist;

    public integer getid() {
        return id;
    }

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

    public string getusername() {
        return username;
    }

    public void setusername(string username) {
        this.username = username;
    }

    public string getpassword() {
        return password;
    }

    public void setpassword(string password) {
        this.password = password;
    }

    public string gettel() {
        return tel;
    }

    public void settel(string tel) {
        this.tel = tel;
    }

    public string getaddress() {
        return address;
    }

    public void setaddress(string address) {
        this.address = address;
    }

    public list<order> getorderlist() {
        return orderlist;
    }

    public void setorderlist(list<order> orderlist) {
        this.orderlist = orderlist;
    }

    @override
    public string tostring() {
        return "user{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                ", tel='" + tel + '\'' +
                ", address='" + address + '\'' +
                ", orderlist=" + orderlist +
                '}';
    }
}

 

tostring()只是为了方便测试、调试，后期可以去掉。

 

 

（2）给“一”的一方写mapper接口、xml映射文件

package com.chy.mapper;

import com.chy.pojo.user;

public interface usermapper {
    public user queryuserbyid(integer id);
}

 

<?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.usermapper">
    <select id="queryuserbyid" parametertype="integer" resultmap="userresultwithorder">
        select user_tb.*,order_tb.* from user_tb,order_tb where user_tb.id=#{id} and order_tb.user_id=user_tb.id
     </select>
    <resultmap id="userresultwithorder" type="user">
        <id property="id" column="id"/>
        <result property="username" column="username"/>
        <result property="password" column="password"/>
        <result property="tel" column="tel"/>
        <result property="address" column="address"/>
        <collection property="orderlist" oftype="order">
            <id property="no" column="no"/>
            <result property="goods_id" column="goods_id" />
            <result property="goods_amount" column="goods_amount" />
        </collection>
    </resultmap>
</mapper>

因为关联的是集合（list），所以用<collection>，property指定表示多的一方（list）的属性名，oftype指定多的一方的数据类型（pojo类）。

<collection>里用<id>、<result>配置多的一方的映射。

 

所谓关联查询，是查询结果中同时包含双方（2张表）的多个字段，

如果只查询一张表的字段，比如说只查询某个用户的订单，不查询此用户本身的信息，直接根据user_id查order_tb表，不需要查user_tb，不必使用关联查询，写个ordermapper接口、ordermapper.xml就ok。

 

 

（3）使用

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.usermapper;
import com.chy.pojo.user;
import com.chy.utils.mybatisutils;
import org.apache.ibatis.session.*;

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

 

 

也可以使用嵌套查询来实现一对多（不推荐）。