Mybatis多表查询（一对多，多对一，多对多）

---

1、一对多

• 查询用户详情，同时查询到用户管理的所有订单

1. 查询用户详情（不含订单）
2. 查询指定用户的所有订单
3. 用户管理订单
   • 修改JavaBean
   • 映射文件

1.1、用户详情

功能接口

package com.czxy.ssm.mapper;

import com.czxy.ssm.domain.User;
import org.apache.ibatis.annotations.Param;

/**
 * @author sky
 */
public interface UserMapper {

    /**
     * 通过id查询详情（含所有的订单）
     * @param uid
     * @return
     */
    public User selectById(@Param("uid") String uid);
}

映射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.czxy.ssm.mapper.UserMapper">

    <resultMap id="userResultMap" type="user">
        <id property="uid" column="uid"></id>
        <result property="userName" column="user_name"></result>
    </resultMap>

    <select id="selectById" resultMap="userResultMap">
        select * from user where uid = #{uid}
    </select>

</mapper>

测试类

package com.czxy.ssm;

import com.czxy.ssm.domain.User;
import com.czxy.ssm.mapper.UserMapper;
import com.czxy.ssm.utils.MyBatisUtils;

import java.util.List;

/**
 * @author sky
 */
public class Test01_XML_SelectById {
    public static void main(String[] args) {
        //1 获得mapper
        UserMapper userMapper = MyBatisUtils.getMapper(UserMapper.class);

        //2 查询素有
        User user = userMapper.selectById("u001");
        System.out.println(user);


        //3 释放
        MyBatisUtils.commitAndclose();
    }
}

确认核心配置文件，添加映射文件

1.2、用户订单

功能接口

package com.czxy.ssm.mapper;

import com.czxy.ssm.domain.Order;
import org.apache.ibatis.annotations.Param;

import java.util.List;

/**
 * @author sky
 */
public interface OrderMapper {

    /**
     * 通过uid查询所有的订单
     * @param uid
     * @return
     */
    public List<Order> selectAllByUid(@Param("uid") String uid);
}

映射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.czxy.ssm.mapper.OrderMapper">


    <select id="selectAllByUid" resultType="order">
        SELECT * FROM orders WHERE uid = #{uid}
    </select>

</mapper>

测试类

package com.czxy.ssm;

import com.czxy.ssm.domain.Order;
import com.czxy.ssm.domain.User;
import com.czxy.ssm.mapper.OrderMapper;
import com.czxy.ssm.mapper.UserMapper;
import com.czxy.ssm.utils.MyBatisUtils;

import java.util.List;

/**
 * @author sky
 */
public class Test02_XML_SelectAllOrderByUid {
    public static void main(String[] args) {
        //1 获得mapper
        OrderMapper orderMapper = MyBatisUtils.getMapper(OrderMapper.class);

        //2 查询素有
        List<Order> list = orderMapper.selectAllByUid("u001");
        list.forEach(System.out::println);

        //3 释放
        MyBatisUtils.commitAndclose();
    }
}

检查核心配置文件，添加映射文件

1.3、 关联

编写Java

// 一对多关系：一个用户拥有多个订单
private List orderList = new ArrayList<>();

编写xml映射

<!-- 配置一对多 ，类似@Many  -->
<collection property="orderList" column="uid" select="com.czxy.ssm.mapper.OrderMapper.selectAllByUid"></collection>

2、多对一

查询订单时，同时查询所属用户

• 通过id查询订单详情
• 通过uid查询用户详情（已有）
• 关联

2.1通过id查询订单详情

功能接口

package com.czxy.ssm.mapper;

import com.czxy.ssm.domain.Order;
import org.apache.ibatis.annotations.Param;

import java.util.List;

/**
 * @author sky
 */
public interface OrderMapper {

    /**
     * 通过oid查询详情
     * @param oid
     * @return
     */
    public Order selectById(@Param("oid") String oid);
}

xml配置

 <resultMap id="orderResultMap" type="order">
        <id property="oid" column="oid"></id>
        <result property="ordertime" column="ordertime"></result>
        <result property="uid" column="uid"></result>
    </resultMap>

    <!-- 查询详情  -->
    <select id="selectById" resultMap="orderResultMap">
        select * from orders where oid = #{oid}
    </select>
    

测试类

package com.czxy.ssm;

import com.czxy.ssm.domain.Order;
import com.czxy.ssm.mapper.OrderMapper;
import com.czxy.ssm.utils.MyBatisUtils;

import java.util.List;

/**
 * @author sky
 */
public class Test03_XML_SelectOrderByid {
    public static void main(String[] args) {
        //1 获得mapper
        OrderMapper orderMapper = MyBatisUtils.getMapper(OrderMapper.class);

        //2 查询素有
        Order order = orderMapper.selectById("x001");
        System.out.println(order);

        //3 释放
        MyBatisUtils.commitAndclose();
    }
}

2.2 关联

编写JavaBean

// 多对一：多个订单属于一个用户
private User user;

编写XML

<!--  多对一 @One等效 -->
<association property="user" column="uid" select="com.czxy.ssm.mapper.UserMapper.selectById"></association>

3、多对多

查询所有学生，同时查询每一个学生对应的所有授课老师

1. 查询所有学生
2. 查询指定学生的所有授课老师
3. 关联

3.1、查询所有学生

功能接口

package com.czxy.ssm.mapper;

import com.czxy.ssm.domain.Student;

import java.util.List;

/**
 * @author sky
 */
public interface StudentMapper  {

    /**
     * 所有的学生
     * @return
     */
    public List<Student> selectAll();
}

XML映射配置，需要使用ResultMap （后面需要封装老师的数据）

<?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.czxy.ssm.mapper.StudentMapper">


    <resultMap id="studentResultMap" type="student">
        <id property="sid" column="sid"></id>
        <result property="name" column="name"></result>
    </resultMap>

    <!-- 查询详情  -->
    <select id="selectAll" resultMap="studentResultMap">
        select * from student
    </select>

</mapper>

核心配置文件，添加映射文件

测试类

package com.czxy.ssm;

import com.czxy.ssm.domain.Order;
import com.czxy.ssm.domain.Student;
import com.czxy.ssm.mapper.OrderMapper;
import com.czxy.ssm.mapper.StudentMapper;
import com.czxy.ssm.utils.MyBatisUtils;

import java.util.List;

/**
 * @author sky
 */
public class Test04_XML_SelectAllStudent {
    public static void main(String[] args) {
        //1 获得mapper
        StudentMapper studentMapper = MyBatisUtils.getMapper(StudentMapper.class);

        //2 查询素有
        List<Student> list = studentMapper.selectAll();
        list.forEach(System.out::println);

        //3 释放
        MyBatisUtils.commitAndclose();
    }
}

3.2、查询指定学生的所有授课老师

功能接口

package com.czxy.ssm.mapper;

import com.czxy.ssm.domain.Student;
import com.czxy.ssm.domain.Teacher;
import org.apache.ibatis.annotations.Param;

import java.util.List;

/**
 * @author sky
 */
public interface TeacherMapper {

    /**
     * 查询指定学生的所有授课老师
     * @return
     */
    public List<Teacher> selectAllBySid(@Param("sid") Integer sid);
}

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.czxy.ssm.mapper.TeacherMapper">


    <!-- 查询详情  -->
    <select id="selectAllBySid" resultType="teacher">
        select * from teacher t, teacher_student ts where t.tid = ts.teacher_id and ts.student_id = #{sid}
    </select>

</mapper>

核心配置文件，添加映射文件

测试类

package com.czxy.ssm;

import com.czxy.ssm.domain.Student;
import com.czxy.ssm.domain.Teacher;
import com.czxy.ssm.mapper.StudentMapper;
import com.czxy.ssm.mapper.TeacherMapper;
import com.czxy.ssm.utils.MyBatisUtils;

import java.util.List;

/**
 * @author sky
 */
public class Test05_XML_SelectAllTeacherBySid {
    public static void main(String[] args) {
        //1 获得mapper
        TeacherMapper teacherMapper = MyBatisUtils.getMapper(TeacherMapper.class);

        //2 查询素有
        List<Teacher> list = teacherMapper.selectAllBySid(1);
        list.forEach(System.out::println);

        //3 释放
        MyBatisUtils.commitAndclose();
    }
}

关联
编写JavaBean

// 多对多：不同的学生，可以上【不同老师】的课
private List teacherList = new ArrayList<>();

修改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.czxy.ssm.mapper.StudentMapper">


    <resultMap id="studentResultMap" type="student">
        <id property="sid" column="sid"></id>
        <result property="name" column="name"></result>
        <!--  多对多关系 -->
        <collection property="teacherList" column="sid" select="com.czxy.ssm.mapper.TeacherMapper.selectAllBySid" ></collection>
    </resultMap>

    <!-- 查询详情  -->
    <select id="selectAll" resultMap="studentResultMap">
        select * from student
    </select>

</mapper>