mybatis的多表联合查询

1、首先使用sql语句编写数据库中表的信息

2、然后在idea中编写各大对象类

package com.qfedu.pojo;

import java.util.List;

/**
 * Created by asus on 2020/3/10.
 */
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;
    }
}

3、在resources中配置Order’Mapper.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="com.qfedu.pojo.orderMapper">
    <select id="getOrders" resultMap="OrderMap">
        select * from orders where oid=#{oid}
    </select>


    <resultMap id="OrderMap" type="com.qfedu.pojo.Order">

    <!--  id 代表主键，分别设置列和属性的关系   -->

        <id property="oid" column="oid"/>

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

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

          属性名叫做payType，字段名pay_type;
          例 ：<result column="pay_type" property="payType" />
      -->
        <result property="price" column="price"/>
        <result property="addr" column="addr"/>
        <result property="payType" column="payType"/>
        <!--
         association关联，只要是"对一"的关系都可以使用association，代表关联
         property代表Order类中的属性名u
         column代表Orders表中的uid字段
         select代表要使用该查询完成两表的联合查询得出user对象
     -->
        <association property="u" column="uid" select="com.qfedu.pojo.UserMapper.getUsers"/>
    </resultMap>

   <!-- <resultMap id="OrderMap" type="com.qfedu.pojo.Detail">
        <association property="details" select="com.qfedu.pojo.DetailsMapper.getDetail"/>
    </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="com.qfedu.pojo.UserMapper">
    <select id="getUsers" resultType="com.qfedu.pojo.User">
        select * from users where uid=#{uid};
    </select>
</mapper>

最后进行测试

package com.qfedu.test;

import com.qfedu.pojo.Detail;
import com.qfedu.pojo.Order;
import com.sun.org.apache.xpath.internal.operations.Or;
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 org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.io.IOException;
import java.util.List;

/**
 * Created by asus on 2020/3/10.
 */
public class mybatisTest {

    private SqlSessionFactory factory=null;
    private SqlSession session=null;

    @Before
    public void setUp(){
        try {
            factory=new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("mybatis.xml"));
            session = factory.openSession();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    @After
    public void tearDown(){
        if(session!=null){
            session.close();
            session=null;
        }
    }

    @Test
    public void test1(){
      Order o=session.selectOne("com.qfedu.pojo.orderMapper.getOrders","16c7fe4d62bd11ea8d61e447b5b67216");
        System.out.println(o);
    }

    @Test
    public void test2(){
       Detail dd=session.selectOne("com.qfedu.pojo.DetailsMapper.getDetail","5a2d37a062bc11ea8d61e447b5b67216");
        System.out.println(dd);
    }
}

PS：我们每次创建一个xml文件需要resources下的mybatis中导入这些xml文件，同时我们还可以使用标签进行打印输出的日志，例

<?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 数据库的连接信息 -->
    <properties resource="db.properties"/>

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

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <!--导入db.properties文件后，设置value 值是  使用  ${driver} 等进行获取数值-->
                <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/DetailsMapper.xml"/>
        <mapper resource="com/qfedu/mapper/ProductMapper.xml"/>
    </mappers>
</configuration>