多表聯(lián)合查詢

多表聯(lián)合查詢

在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的配置文件信息，后面用到的四個連接字符串就可以直接使用 ${}的方式來動態(tài)引入
    -->
    <properties resource="db.properties">
        <property name="pass" value="123456"></property>
    </properties>

    <!--
        給當(dāng)前mybatis項目添加日志功能，該STDOUT_LOGGING值的好處是不用添加第三方j(luò)ar包就可以有日志的輸出
    -->
    <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 是對一的關(guān)系，collection 是對多的關(guān)系

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，
    每個方法對應(yīng)自己的sql語句，每個sql語句對應(yīng)有一個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代表主鍵，分別設(shè)置列和屬性的對應(yīng)關(guān)系
        -->
        <id property="oid" column="oid" ></id>

        <!--
            result代表普通字段的映射，分別指定列與屬性的對應(yīng)

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

            屬性名叫做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關(guān)聯(lián)，只要是"對一"的關(guān)系都可以使用association，代表關(guān)聯(lián)
            property代表Order類中的屬性名u
            column代表Orders表與Users表之間的關(guān)聯(lián)字段
            select代表要使用該查詢完成兩表的聯(lián)合查詢得出user對象
        -->
        <association property="u" column="uid" select="com.qfedu.pojo.UserMapper.getUserByUid"></association>

        <!--
            collection設(shè)置集合，只要是"對多"多關(guān)系，都可以使用collection，代表集合
            column代表orders表與details表之間的關(guān)聯(lián)字段
        -->
        <!--<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，
    每個方法對應(yīng)自己的sql語句，每個sql語句對應(yīng)有一個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，
    每個方法對應(yīng)自己的sql語句，每個sql語句對應(yīng)有一個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，
    每個方法對應(yīng)自己的sql語句，每個sql語句對應(yīng)有一個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，
    每個方法對應(yīng)自己的sql語句，每個sql語句對應(yīng)有一個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;
    }
}
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容