1.一对一类型多表连接查询

表结构:
实体类代码:

public class Card implements Serializable {

  private int cid;
  private String cnum;
  private int pid;
  private Person person;
}

public class Person implements Serializable {

  private long pid;
  private String pname;
  private String adress;
}

//对应的get/set方法和toString方法这里不再书写

实现功能,根据身份证号码查询出这个人的信息.
具体步骤:
第一步:配置Maven项目环境,配置需要的jar包,这里不在过多解释.
第二步:创建实体类,这里我之前的博客有写idea快速构建,这里也不过多解释.
第三步:配置Mybatis配置文件,创建映射文件CardMapper.xml和PersonMapper.xml
第四步:创建接口,实现接口代理模式
第五步:书写sql语句,创建测试类测试

实现连接查询的方法一:

使用resultMap

<mapper namespace="com.offcn.mapper.CardMapper">
    <!--<resultMap id="baseMap" type="com.offcn.pojo.Card">
        <id property="cid" column="cid"></id>
        <result property="cnum" column="cnum"></result>
        <result property="pid" column="pid"></result>
    </resultMap>-->

    <resultMap id="baseMap" type="com.offcn.pojo.Card">
        <id property="cid" column="cid"></id>
        <result property="cnum" column="cnum"></result>
        <result property="pid" column="pid"></result>

        <!--一对一关联配置,使用标签association property是属性名-->
        <association property="person" javaType="com.offcn.pojo.Person">
            <id property="pid" column="pid"></id>
            <result property="pname" column="pname"></result>
            <result property="adress" column="adress"></result>
        </association>
    </resultMap>
    <select id="queryByCnum" parameterType="String" resultMap="baseMap">
        select c.*,p.* from card c,person p where c.pid=p.pid and c.cnum=#{cnum}
    </select>
</mapper>

测试代码:

@Test
    public void testQueryByCnum() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory build = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = build.openSession();
        CardMapper mapper = sqlSession.getMapper(CardMapper.class);
        Card card = mapper.queryByCnum("111111");
        System.out.println(card);
        sqlSession.close();
    }

使用外联resultMap

原因:因为我们第一种方法中在CardMapper映射文件里面涉及到了Person,我们应该是实现Card映射文件里面专门配置和Card有关的,因此我们去PersonMapper里面配置
步骤:我们去PersonMapper.xml中配置映射关系,然后调用
PersonMapper.xml中的代码:

<mapper namespace="com.offcn.mapper.PersonMapper">
    <resultMap id="pMap" type="com.offcn.pojo.Person">
        <id property="pid" column="pid"></id>
        <result property="pname" column="pname"></result>
        <result property="adress" column="adress"></result>
    </resultMap>
</mapper>

我们在CardMapper.xml中调用:

<mapper namespace="com.offcn.mapper.CardMapper">
    <!--<resultMap id="baseMap" type="com.offcn.pojo.Card">
        <id property="cid" column="cid"></id>
        <result property="cnum" column="cnum"></result>
        <result property="pid" column="pid"></result>
    </resultMap>-->

    <resultMap id="baseMap" type="com.offcn.pojo.Card">
        <id property="cid" column="cid"></id>
        <result property="cnum" column="cnum"></result>
        <result property="pid" column="pid"></result>

        <!--一对一关联配置,使用标签association property是属性名-->
        <association property="person" javaType="com.offcn.pojo.Person" resultMap="com.offcn.mapper.PersonMapper.pMap">
        </association>
    </resultMap>
    <select id="queryByCnum" parameterType="String" resultMap="baseMap">
        select c.*,p.* from card c,person p where c.pid=p.pid and c.cnum=#{cnum}
    </select>

测试代码和上面一样

第三种是最常用的方法:嵌套查询

思路:我们如果只在Card表中根据身份证号查询出来的只有cid,cnum,pid,但只有通过pid我们才可以在Person表中查询到数据,所以这里我们使用嵌套查询,将sql语句简单化.
首先我们在CardMapper和personMapper接口中分别定义方法:

public interface CardMapper {
    Card queryByCnum(String cnum);
}

public interface PersonMapper {
    Person queryBypid(int pid);
}

首先配置PersonMapper.xml:

<mapper namespace="com.offcn.mapper.PersonMapper">
    <resultMap id="pMap" type="com.offcn.pojo.Person">
        <id property="pid" column="pid"></id>
        <result property="pname" column="pname"></result>
        <result property="adress" column="adress"></result>
    </resultMap>

    <select id="queryBypid" parameterType="int" resultMap="pMap">
        select * from person where pid=#{pid};
    </select>
</mapper>

再配置CardMapper.xml:

<mapper namespace="com.offcn.mapper.CardMapper">
    <resultMap id="baseMap" type="com.offcn.pojo.Card">
        <id property="cid" column="cid"></id>
        <result property="cnum" column="cnum"></result>
        <result property="pid" column="pid"></result>
    </resultMap>

    <resultMap id="newMap" type="com.offcn.pojo.Card" extends="baseMap">
        <association property="person" javaType="com.offcn.pojo.Person" column="pid" select="com.offcn.mapper.PersonMapper.queryBypid">
        </association>
    </resultMap>
    <select id="queryByCnum" parameterType="String" resultMap="newMap">
       select * from card where cnum=#{cnum}
    </select>
</mapper>

测试代码一样

总结

第一点:我们使用resultMap时,一般是指定实体类中属性和sql查询出来字段的匹配,所以一般是是会把实体类属性全部写完的,但多表查询时会出现新的字段和属性,所以我们可以使用继承来实现继承基础的映射关系,上面中CardMapper.xml里面就有.
第二点:接口中的方法名和映射文件中association标签里面的select属性里面要写对方法

2.一对多类型多表连接查询

创建老师和学生表,一个老师对应多个学生.

在这里插入图片描述

建立实体类:

public class Teacher implements Serializable {

  private int id;
  private String tname;
  private List<Student> students;
}

public class Student implements Serializable {

  private int sid;
  private String sname;
  private int sage;
  private int tid;
}

书写接口,定义方法,创建映射文件,在Mybatis配置文件中加载新的映射文件

public interface TeacherMapper {
    Teacher queryByname(String tname);
}

public interface StudentMapper {
    List<Student> queryBytid(int tid);
}

配置TeacheMapper.xml和StudentMapper.xml,以下分别是对应的代码:

<mapper namespace="com.offcn.mapper.TeacherMapper">
        <resultMap id="baseMap" type="com.offcn.pojo.Teacher">
            <id property="id" column="id"></id>
            <result property="tname" column="tname"></result>
        </resultMap>

    <resultMap id="tMap" type="com.offcn.pojo.Teacher" extends="baseMap">
        <collection property="students" column="id" ofType="com.offcn.pojo.Student"
        select="com.offcn.mapper.StudentMapper.queryBytid">

        </collection>
    </resultMap>
    <select id="queryByname" parameterType="String" resultMap="tMap">
        select * from teacher where tname=#{tname}
    </select>
</mapper>



<mapper namespace="com.offcn.mapper.StudentMapper">
    <resultMap id="sMap" type="com.offcn.pojo.Student">
        <id property="sid" column="sid"></id>
        <result property="sname" column="sname"></result>
        <result property="sage" column="sage"></result>
        <result property="tid" column="tid"></result>
    </resultMap>

    <select id="queryBytid" resultMap="sMap" parameterType="int">
        select * from student where tid=#{tid}
    </select>
</mapper>

书写测试类:

    @Test
    public void testqueryByname() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory build = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = build.openSession();
        TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
        Teacher teacher = mapper.queryByname("sun");
        System.out.println(teacher);
        sqlSession.close();
        
    }

3.多对多类型多表连接查询

创建四张表实现多对多关系:用户表,订单表,商品表和订单商品表
创建实体类:

public class Customer implements Serializable {

  private int cid;
  private String cname;
  private String caddress;
  private List<Orders> orders;
 }


public class Orders implements Serializable {

  private int oid;
  private String onum;
  private String odetial;
  private int cfk;
  private List<ItemsOrders> itemsOrders;
  }



public class ItemsOrders implements Serializable {

  private int ioId;
  private int itemFk;
  private int ordersFk;
  private Items items;
  }

public class Items implements Serializable {

  private int itemId;
  private String itemName;
  private double itemPrice;
}

我们通过用户id来查询其订单和对应的商品信息,开始写映射文件:

<mapper namespace="com.offcn.mapper.CustomerMapper">

    <resultMap id="cMap" type="com.offcn.pojo.Customer">
        <id column="cid" property="cid"></id>
        <result column="cname" property="cname"></result>
        <result column="caddress" property="caddress"></result>
        <!--第一个collection查询用户的订单集合-->
        <collection property="orders" ofType="com.offcn.pojo.Orders">
            <id column="oid" property="oid"></id>
            <result column="onum" property="onum"></result>
            <result column="odetial" property="odetial"></result>
            <result column="cfk" property="cfk"></result>
            <!--第二个collection查询订单和商品对应关系的集合-->
            <collection property="itemsOrders" ofType="com.offcn.pojo.ItemsOrders">
                <id column="io_id" property="ioId"></id>
                <result column="item_fk" property="itemFk"></result>
                <result column="orders_fk" property="ordersFk"></result>
                <!--第三个属性是商品信息,因为此时属于多对一,因此只需要使用association标签即可-->
                <association property="items" javaType="com.offcn.pojo.Items">
                    <id column="item_id" property="itemId"></id>
                    <result column="item_name" property="itemName"></result>
                    <result column="item_price" property="itemPrice"></result>
                </association>
            </collection>
        </collection>

    </resultMap>

    <select id="queryByid" resultMap="cMap" parameterType="int">
        select c.*,o.*,io.*,i.*
        from customer c,orders o,items_orders io ,items i
        where c.cid=o.cfk
        and o.oid=io.orders_fk
        and io.item_fk=i.item_id
        and c.cid=#{cid}
    </select>
</mapper>

写测试代码:

@Test
    public void testqueryByid() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory build = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = build.openSession();
        CustomerMapper mapper = sqlSession.getMapper(CustomerMapper.class);
        Customer customer = mapper.queryByid(1);
        System.out.println(customer);
        sqlSession.close();

    }

结果:

总结

第一点:对于上面的一对一,一对多的区别就是嵌套查询的结果是一个集合了,所以我们使用的标签也不再是association,而是使用了collection,其他步骤没有太多变化,属性变成了ofType.

原文链接：https://blog.csdn.net/qq_43265564/article/details/112197147