多表联合查询

在mybatis添加mapper映射，需要按顺序，

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
        <configuration>

    <!--
        引入db的配置文件信息，后面用到的四个连接字符串就可以直接使用 ${}的方式来动态引入
    -->
    <properties resource="db.properties">
        <property name="pass" value="123456"></property>
    </properties>

    <!--
        给当前mybatis项目添加日志功能，该STDOUT_LOGGING值的好处是不用添加第三方jar包就可以有日志的输出
    -->
    <settings>
        <setting name="logImpl" value="STDOUT_LOGGING"/>
    </settings>

    <typeAliases>
        <!--<typeAlias type="com.qfedu.pojo.Order" alias="abc" />-->
        <package name="com.qfedu.pojo" />
    </typeAliases>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${user}"/>
                <property name="password" value="${pass}"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="com/qfedu/mapper/OrderMapper.xml"/>
        <mapper resource="com/qfedu/mapper/UserMapper.xml"/>
        <mapper resource="com/qfedu/mapper/DetailMapper.xml"/>
        <mapper resource="com/qfedu/mapper/ProductMapper.xml"/>
        <mapper resource="com/qfedu/mapper/TypeMapper.xml"/>
    </mappers>
</configuration>

association 是对一的关系，collection 是对多的关系

OrderMapper.xml

<?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，
    每个方法对应自己的sql语句，每个sql语句对应有一个id
    整个项目中所有的namespace.id必须是唯一的
-->
<mapper namespace="com.qfedu.pojo.OrderMapper">

    <select id="getOrderByOid" resultMap="orderMap">
      select * from orders where oid = #{oid}
    </select>
    
    <!--<resultMap id="orderMap" type="com.qfedu.pojo.Order">-->
    <resultMap id="orderMap" type="order">

        <!--
            id代表主键，分别设置列和属性的对应关系
        -->
        <id property="oid" column="oid" ></id>

        <!--
            result代表普通字段的映射，分别指定列与属性的对应

            如果字段名和属性名一致，可以省略

            属性名叫做payType，字段名pay_type;
            <result column="pay_type" property="payType" />
        -->
        <result column="price" property="price" />
        <result column="addr" property="addr" />
        <result column="payType" property="payType" />

        <!--
            association关联，只要是"对一"的关系都可以使用association，代表关联
            property代表Order类中的属性名u
            column代表Orders表与Users表之间的关联字段
            select代表要使用该查询完成两表的联合查询得出user对象
        -->
        <association property="u" column="uid" select="com.qfedu.pojo.UserMapper.getUserByUid"></association>

        <!--
            collection设置集合，只要是"对多"多关系，都可以使用collection，代表集合
            column代表orders表与details表之间的关联字段
        -->
        <!--<collection property="details" column="oid" select="com.qfedu.pojo.DetailMapper.getDetailsByOid" ofType="com.qfedu.pojo.Detail" />-->
        <collection property="details" column="oid" select="com.qfedu.pojo.DetailMapper.getDetailsByOid" ofType="detail" />
    </resultMap>
</mapper>

UserMapper.xml

<?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，
    每个方法对应自己的sql语句，每个sql语句对应有一个id
    整个项目中所有的namespace.id必须是唯一的
-->
<mapper namespace="com.qfedu.pojo.UserMapper">
    
    <!--<select id="getUserByUid" resultType="com.qfedu.pojo.User">-->
    <select id="getUserByUid" resultType="User">
      select * from users where uid = #{uid}
    </select>
</mapper>

DetailMapper.xml

<?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，
    每个方法对应自己的sql语句，每个sql语句对应有一个id
    整个项目中所有的namespace.id必须是唯一的
-->
<mapper namespace="com.qfedu.pojo.DetailMapper">
    
    <select id="getDetailsByOid" resultMap="detailMap">
      select * from details where oid = #{oid}
    </select>

    <resultMap id="detailMap" type="com.qfedu.pojo.Detail">
        <id column="did" property="did"></id>

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

        <association property="pro" column="pid" select="com.qfedu.pojo.ProductMapper.getProductByPid" />
    </resultMap>
</mapper>

ProductMapper.xml

<?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，
    每个方法对应自己的sql语句，每个sql语句对应有一个id
    整个项目中所有的namespace.id必须是唯一的
-->
<mapper namespace="com.qfedu.pojo.ProductMapper">
    
    <select id="getProductByPid" resultMap="productMap">
      select * from products where pid = #{uid}
    </select>

    <resultMap id="productMap" type="com.qfedu.pojo.Product">
        <id column="pid" property="pid"></id>

        <association property="t" column="tid" select="com.qfedu.pojo.TypeMapper.getTypesByTid" />
    </resultMap>
</mapper>

TypeMapper.xml

<?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，
    每个方法对应自己的sql语句，每个sql语句对应有一个id
    整个项目中所有的namespace.id必须是唯一的
-->
<mapper namespace="com.qfedu.pojo.TypeMapper">
    
    <select id="getTypesByTid" resultType="com.qfedu.pojo.Types">
      select * from types where tid = #{tid}
    </select>
</mapper>

Order.java

package com.qfedu.pojo;

import java.util.List;

public class Order {

    private String oid;
    private double price;
    private String addr;
    private String payType;
    private User u;
    private List<Detail> details;

    @Override
    public String toString() {
        return "Order{" +
                "oid='" + oid + '\'' +
                ", price=" + price +
                ", addr='" + addr + '\'' +
                ", payType='" + payType + '\'' +
                ", u=" + u +
                ", details=" + details +
                '}';
    }

    public String getOid() {
        return oid;
    }

    public void setOid(String oid) {
        this.oid = oid;
    }

    public double getPrice() {
        return price;
    }

    public void setPrice(double price) {
        this.price = price;
    }

    public String getAddr() {
        return addr;
    }

    public void setAddr(String addr) {
        this.addr = addr;
    }

    public String getPayType() {
        return payType;
    }

    public void setPayType(String payType) {
        this.payType = payType;
    }

    public User getU() {
        return u;
    }

    public void setU(User u) {
        this.u = u;
    }

    public List<Detail> getDetails() {
        return details;
    }

    public void setDetails(List<Detail> details) {
        this.details = details;
    }
}

User.java

package com.qfedu.pojo;

public class User {
    private int uid;
    private String name;
    private String pass;
    private String phone;

    @Override
    public String toString() {
        return "User{" +
                "uid=" + uid +
                ", name='" + name + '\'' +
                ", pass='" + pass + '\'' +
                ", phone='" + phone + '\'' +
                '}';
    }

    public int getUid() {
        return uid;
    }

    public void setUid(int uid) {
        this.uid = uid;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getPass() {
        return pass;
    }

    public void setPass(String pass) {
        this.pass = pass;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }
}

Detail.java

package com.qfedu.pojo;

public class Detail {

    private String did;
    private int count;
    private Product pro;

    @Override
    public String toString() {
        return "Detail{" +
                "did='" + did + '\'' +
                ", count=" + count +
                ", pro=" + pro +
                '}';
    }

    public String getDid() {
        return did;
    }

    public void setDid(String did) {
        this.did = did;
    }

    public int getCount() {
        return count;
    }

    public void setCount(int count) {
        this.count = count;
    }

    public Product getPro() {
        return pro;
    }

    public void setPro(Product pro) {
        this.pro = pro;
    }
}

Product.java

package com.qfedu.pojo;

public class Product {

    private String pid;
    private String name;
    private String img;
    private double price;
    private Types t;

    @Override
    public String toString() {
        return "Product{" +
                "pid='" + pid + '\'' +
                ", name='" + name + '\'' +
                ", img='" + img + '\'' +
                ", price=" + price +
                ", t=" + t +
                '}';
    }

    public String getPid() {
        return pid;
    }

    public void setPid(String pid) {
        this.pid = pid;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getImg() {
        return img;
    }

    public void setImg(String img) {
        this.img = img;
    }

    public double getPrice() {
        return price;
    }

    public void setPrice(double price) {
        this.price = price;
    }

    public Types getT() {
        return t;
    }

    public void setT(Types t) {
        this.t = t;
    }
}

Types.java

package com.qfedu.pojo;

public class Types {

    private String tid;
    private String name;

    @Override
    public String toString() {
        return "Types{" +
                "tid=" + tid +
                ", name='" + name + '\'' +
                '}';
    }

    public String getTid() {
        return tid;
    }

    public void setTid(String tid) {
        this.tid = tid;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}