多表操作

本文已参与「新人创作礼」活动，一起开启掘金创作之路

多对一

1. 创建用户、订单表，并建立用户与订单之间的关系

2. 创建用户表customer的domain类

@Setter@Getter@ToString
public class Customer {
    private Integer custId;//cust_id
     private String custName;//cust_name
    private String custProfession;//cust_profession
    private String custPhone;//cust_phone
    private String email;//email

}

3. 创建customer的mapping配置文件

<?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="demo2.mapper.CustomerMapper">
    <!--根据id查找用户-->
    <select id="queryCustomerById" parameterType="Integer" resultType="Customer">
        SELECT * FROM customer WHERE cust_id=#{cust_id}
    </select>
    <!--查询所有用户-->
    <select id="queryAll" resultType="Customer" >
        SELECT * FROM  customer;
    </select>
    <!--根据用户名模糊查询用户-->
    <select id="queryByName" resultType="demo2.domain.Customer" parameterType="String">
        SELECT * FROM customer WHERE cust_name LIKE #{cust_name}
    </select>
    <!--添加用户-->
    <insert id="addCustomer" parameterType="demo2.domain.Customer">
        <!--返回新增加记录的主键-->
        <selectKey keyColumn="cust_id" keyProperty="cust_id" order="AFTER" resultType="Integer">
            SELECT LAST_INSERT_ID()
        </selectKey>
        INSERT  INTO customer (cust_name,cust_profession,cust_phone,email) VALUES
        (#{cust_name},#{cust_profession},#{cust_phone},#{email})
    </insert>
    <!--更新用户-->
    <update id="updateeCustomerById" parameterType="demo2.domain.Customer">
        UPDATE customer SET cust_name=#{cust_name}where cust_id=#{cust_id}
    </update>
    <!--删除用户-->
    <delete id="deleteById" parameterType="Integer">
        DELETE FROM customer WHERE cust_id=#{cust_id}
    </delete>
    <!--根据名称和id查找用户-->
    <select id="queryCustomerByIdAndName"  resultType="demo2.domain.Customer">
        SELECT * FROM customer WHERE cust_id=#{cust_id} AND cust_name=#{cust_name}
    </select>
    <select id="Count" resultType="Integer">
        SELECT COUNT(*) FROM customer;
    </select>
</mapper>

4. 创建customer的mapping的接口类CustomerMapper

public interface CustomerMapper {
   public Customer queryCustomerById(Integer id);
   public List<Customer> queryAll();
   public Customer queryByName(String name);
   public void addCustomer(Customer customer);
   public void updateeCustomerById(Customer customer);
   public void deleteById(Integer id);
   public  Customer queryCustomerByIdAndName(Map<String,Object> map);
   public Integer Count();
}

5. 创建订单表orders的domain类

@Setter@Getter@ToString
public class Order {
    private Integer orderId;
    private Integer orderNum;
    private Date orderTime;
    private String orderNote;
    private Integer custId;
    private Customer customer;//自定义属性对象
}

6. 创建orders的mapping配置文件

<?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="demo2.mapper.OrderMapper">
    <resultMap id="order" type="Order">
        <result column="cust_id" property="custId"/>
        <association property="customer" javaType="Customer"
                     select="demo2.mapper.CustomerMapper.queryCustomerById"
                     column="cust_id">
        </association>
    </resultMap>
    <select id="queryOrder" resultMap="order">
        SELECT * FROM orders ;
    </select>
</mapper>

7. 创建orders的mapping的接口类OrderMapper

public interface OrderMapper {
    public List<Order> queryOrder();
}

8. 测试类的测试方法

@Test
public void test() throws IOException {
    this.init();
    //1、创建sqlSession对象
    SqlSession sqlSession = sqlSessionFactory.openSession();
    //2、加载Mapper接口
    OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);
    //3、调用Mapper中的方法执行sql
    List<Order> orders = mapper.queryOrder();
    for (Order order:orders) {
        System.out.println(order);
    }
    //4、关闭连接，释放资源
    sqlSession.close();
}

9. 查询

9.1分步查询

9.1.1 先查出所有的订单

9.1.2 根据id查出对应的用户

9.1.3 运行测试类结果

分步查询实际上在数据库中的语句相当于select * from customer where cust_id=(select cust_id from orders WHERE order_id=4)的执行过程，将第一步查询出来的结果作为第二步查询的参数。

9.2左连接查询

修改order的mapping配置文件如下

<?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="demo2.mapper.OrderMapper">

<resultMap id="order" type="Order">
    <!--order当中需要映射的字段，只要配置到result里才会被赋值，不会自动映射赋值-->
    <result property="orderId" column="order_id"/>
    <result property="orderNum" column="order_num"/>
    <result property="orderNote" column="order_note"/>
    <result property="custId" column="cust_id"/>
    <!--order内部关联的对象-->
    <association property="customer" javaType="Customer">
        <!--关联对象的键-->
        <result property="custId" column="cust_id"/>
        <result property="custName" column="cust_name"/>
        <result property="custProfession" column="cust_profession"/>
        <result property="custPhone" column="cust_phone"/>
        <result property="email" column="email"/>
    </association>
</resultMap>
    <select id="queryOrder" resultMap="order">
        SELECT * FROM orders AS o LEFT JOIN customer AS  c ON o.cust_id=c.cust_id;
    </select>
</mapper>

再次运行测试类，得到运行结果如下

由于orderTime没有做映射配置，所以没有附上值。其他的字段都有做映射，因此都有值。

10. 添加

10.1 添加用户

<?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="demo2.mapper.CustomerMapper">
    <!--根据id查找用户-->
    <select id="queryCustomerById" parameterType="Integer" resultType="Customer">
        SELECT * FROM customer WHERE cust_id=#{cust_id}
    </select>
    <!--添加用户-->
    <insert id="addCustomer" parameterType="demo2.domain.Customer" useGeneratedKeys="true" keyColumn="cust_id" keyProperty="custId">
        INSERT  INTO customer (cust_name,cust_profession,cust_phone,email) VALUES
        (#{custName},#{custProfession},#{custPhone},#{email})
    </insert>
    <!--查询所有用户-->
    <select id="queryAll" resultType="Customer" >
        SELECT * FROM  customer;
    </select>
    <!--根据用户名模糊查询用户-->
    <select id="queryByName" resultType="demo2.domain.Customer" parameterType="String">
        SELECT * FROM customer WHERE cust_name LIKE #{cust_name}
    </select>
    <!--更新用户-->
    <update id="updateeCustomerById" parameterType="demo2.domain.Customer">
        UPDATE customer SET cust_name=#{cust_name}where cust_id=#{cust_id}
    </update>
    <!--删除用户-->
    <delete id="deleteById" parameterType="Integer">
        DELETE FROM customer WHERE cust_id=#{cust_id}
    </delete>
    <!--根据名称和id查找用户-->
    <select id="queryCustomerByIdAndName"  resultType="demo2.domain.Customer">
        SELECT * FROM customer WHERE cust_id=#{cust_id} AND cust_name=#{cust_name}
    </select>
    <!--统计-->
    <select id="Count" resultType="Integer">
        SELECT COUNT(*) FROM customer;
    </select>
</mapper>

10.2 添加订单

<?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="demo2.mapper.OrderMapper">
    <resultMap id="order" type="Order">
        <!--order当中需要映射的字段，只要配置到result里才会被赋值，不会自动映射赋值-->
        <result property="orderId" column="order_id"/>
        <result property="orderNum" column="order_num"/>
        <result property="orderNote" column="order_note"/>
        <result property="custId" column="cust_id"/>
        <!--order内部关联的对象-->
        <association property="customer" javaType="Customer">
            <!--关联对象的键-->
            <result property="custId" column="cust_id"/>
            <result property="custName" column="cust_name"/>
            <result property="custProfession" column="cust_profession"/>
            <result property="custPhone" column="cust_phone"/>
            <result property="email" column="email"/>
        </association>
    </resultMap>
    <!--查询订单-->
    <select id="queryOrder" resultMap="order">
        SELECT * FROM orders AS o LEFT JOIN customer AS  c ON o.cust_id=c.cust_id;
    </select>
    <!--添加订单-->
    <insert id="addOrder" parameterType="Order">
        INSERT  INTO orders(order_num,order_time,order_note,cust_id) VALUES
        (#{orderNum},#{orderTime},#{orderNote},#{customer.custId})
    </insert>
</mapper>

10.3 设置关系(将用户表的主键添加到订单表的外键中)

10.4 测试类的测试方法

@Test
public void test1() throws IOException {
    this.init();
    //1、创建sqlSession对象
    SqlSession sqlSession = sqlSessionFactory.openSession();
    //2、加载Mapper接口
    OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class);
    CustomerMapper customerMapper = sqlSession.getMapper(CustomerMapper.class);
    //设置用户信息
    Customer customer = new Customer();
    customer.setCustName("张无忌");
    customer.setCustProfession("导师");
    customer.setCustPhone("13565175127");
    customer.setEmail("12365@qeoq.com");
    //先保存用户，生成用户cust_id
    //3、调用customerMapper中的方法执行sql
    customerMapper.addCustomer(customer);
    //设置订单信息
    Order order = new Order();
    order.setOrderNum(1005);
    Date date = new Date();
    order.setOrderTime(date);
    order.setOrderNote("订单5");
    order.setCustomer(customer);
    //4、调用orderMapper中的方法执行sql
    orderMapper.addOrder(order);
   //5、提交事务，更新数据库
    sqlSession.commit();
    //6、关闭连接，释放资源
    sqlSession.close();
}

10.5运行结果

两张表各添加了一条记录，如图所示

一对多

1. 查询

Customer表的domain类增加自定义属性

@Setter @Getter @ToString
public class Customer {
    private Integer custId;//cust_id
     private String custName;//cust_name
    private String custProfession;//cust_profession
    private String custPhone;//cust_phone
    private String email;//email
    private List<Order> order;//自定义对象属性
}

Customer的Mapping配置文件添加查询sql语句

测试类中的测试方法

@Test
public void test2() throws IOException {
    this.init();
    //1、创建sqlSession对象
    SqlSession sqlSession = sqlSessionFactory.openSession();
    //2、加载Mapper接口
    CustomerMapper mapper = sqlSession.getMapper(CustomerMapper.class);
    //3、调用Mapper中的方法执行sql
    List<Customer> customers = mapper.queryAll();
    for (Customer customer:customers) {
        System.out.println(customer);
    }
    //4、关闭连接，释放资源
    sqlSession.close();
}

运行结果

只有配置了映射的字段才有值

2. 添加

Customer表的domain类

@Setter @Getter @ToString
public class Customer {
    private Integer custId;//cust_id
    private String custName;//cust_name
    private String custProfession;//cust_profession
    private String custPhone;//cust_phone
    private String email;//email
    private List<Order> order=new ArrayList<>();//自定义对象属性
}

Customer的Mapping配置文件

<?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="demo2.mapper.CustomerMapper">
    <!--根据id查找用户-->
    <select id="queryCustomerById" parameterType="Integer" resultType="Customer">
        SELECT * FROM customer WHERE cust_id=#{cust_id}
    </select>
    <!--添加用户-->
    <insert id="addCustomer" parameterType="demo2.domain.Customer"
            useGeneratedKeys="true"
            keyColumn="cust_id"
            keyProperty="custId">
        INSERT  INTO customer (cust_name,cust_profession,cust_phone,email) VALUES
        (#{custName},#{custProfession},#{custPhone},#{email})
    </insert>
    <!--查询所有用户订单-->
    <resultMap id="customer" type="Customer">
        <!--配置映射-->
        <result column="cust_id" property="custId"/>
        <result column="cust_name" property="custName"/>
        <result column="cust_profession" property="custProfession"/>
        <collection property="order" javaType="list" ofType="Order">
            <!--配置主键，是关联order的唯一标识-->
            <id column="order_id" property="orderId"/>
            <result column="order_num" property="orderNum"/>
            <result column="order_note" property="orderNote"/>
        </collection>
    </resultMap>
    <select id="queryAll" resultMap="customer" >
        SELECT * FROM  customer AS c LEFT JOIN orders AS o ON c.cust_id=o.cust_id;
    </select>
    <!--根据用户名模糊查询用户-->
    <select id="queryByName" resultType="demo2.domain.Customer" parameterType="String">
        SELECT * FROM customer WHERE cust_name LIKE #{cust_name}
    </select>
    <!--更新用户-->
    <update id="updateeCustomerById" parameterType="demo2.domain.Customer">
        UPDATE customer SET cust_name=#{cust_name}where cust_id=#{cust_id}
    </update>
    <!--删除用户-->
    <delete id="deleteById" parameterType="Integer">
        DELETE FROM customer WHERE cust_id=#{cust_id}
    </delete>
    <!--根据名称和id查找用户-->
    <select id="queryCustomerByIdAndName"  resultType="demo2.domain.Customer">
        SELECT * FROM customer WHERE cust_id=#{cust_id} AND cust_name=#{cust_name}
    </select>
    <!--统计-->
    <select id="Count" resultType="Integer">
        SELECT COUNT(*) FROM customer;
    </select>
</mapper>

Customer的Mapper接口

public interface CustomerMapper {
   public Customer queryCustomerById(Integer id);
   public List<Customer> queryAll();
   public Customer queryByName(String name);
   public void addCustomer(Customer customer);
   public void updateeCustomerById(Customer customer);
   public void deleteById(Integer id);
   public  Customer queryCustomerByIdAndName(Map<String,Object> map);
   public Integer Count();
}

Order的mapping配置文件

<?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="demo2.mapper.OrderMapper">

    <resultMap id="order" type="Order">
        <!--order当中需要映射的字段，只要配置到result里才会被赋值，不会自动映射赋值-->
        <result property="orderId" column="order_id"/>
        <result property="orderNum" column="order_num"/>
        <result property="orderNote" column="order_note"/>
        <result property="custId" column="cust_id"/>
        <!--order内部关联的对象-->
        <association property="customer" javaType="Customer">
            <!--关联对象的键-->
            <result property="custId" column="cust_id"/>
            <result property="custName" column="cust_name"/>
            <result property="custProfession" column="cust_profession"/>
            <result property="custPhone" column="cust_phone"/>
            <result property="email" column="email"/>
        </association>
    </resultMap>
    <!--查询订单-->
    <select id="queryOrder" resultMap="order">
        SELECT * FROM orders AS o LEFT JOIN customer AS  c ON o.cust_id=c.cust_id;
    </select>
    <!--添加订单-->
    <insert id="addOrder" parameterType="Order" useGeneratedKeys="true" keyColumn="order_id" keyProperty="orderId">
        INSERT  INTO orders(order_num,order_time,order_note,cust_id) VALUES
        (#{orderNum},#{orderTime},#{orderNote},#{customer.custId})
    </insert>
    <!--更新外键-->
    <update id="updateKey">
        UPDATE orders SET cust_id =#{custId} WHERE order_id=#{orderId}
    </update>
</mapper>

Order的mapper接口

public interface OrderMapper {
    public List<Order> queryOrder();
    public void addOrder(Order order);
    public void updateKey(@Param("custId") Integer custId, @Param("orderId") Integer orderId);
}

测试类的测试方法

@Test
public void test3() throws IOException {
    this.init();
    //1、创建sqlSession对象
    SqlSession sqlSession = sqlSessionFactory.openSession();
    //2、加载Mapper接口
    OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class);
    CustomerMapper customerMapper = sqlSession.getMapper(CustomerMapper.class);
    //设置用户信息
    Customer customer = new Customer();
    customer.setCustName("张三丰");
    customer.setCustProfession("博士");
    customer.setCustPhone("1356517");
    customer.setEmail("165@qe.com");
    //设置订单信息
    Order order = new Order();
    order.setOrderNum(1005);
    order.setOrderNote("订单5");
    Order order1 = new Order();
    order1.setOrderNum(1007);
    order1.setOrderNote("订单7");
    //设置关系
    customer.getOrder().add(order);
    customer.getOrder().add(order1);
    //3、执行Mapper接口方法，添加用户和订单
    customerMapper.addCustomer(customer);
    orderMapper.addOrder(order);
    orderMapper.addOrder(order1);
    //4、执行Mapper接口方法，更新关系
    for(Order orders:customer.getOrder()){
        orderMapper.updateKey(customer.getCustId(),orders.getOrderId());
    }
    //4、提交事务，更新数据库
    sqlSession.commit();
    //5、关闭连接，释放资源
    sqlSession.close();
}

3. 删除

由于在数据库设置order表的外键时使用的是RESTRICT约束（拒绝删除或者更新父表）因此删除父表记录时一定要先打破关系（将父表需要删除的记录的主键在子表的外键值中设置为空）再做删除操作，如果只是删除子表的记录则不需要此操作

Customer的Mapping配置文件

<?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="demo2.mapper.CustomerMapper">
    <!--根据id查找用户-->
    <select id="queryCustomerById" parameterType="Integer" resultType="Customer">
        SELECT * FROM customer WHERE cust_id=#{cust_id}
    </select>
    <!--添加用户-->
    <insert id="addCustomer" parameterType="demo2.domain.Customer"
            useGeneratedKeys="true"
            keyColumn="cust_id"
            keyProperty="custId">
        INSERT  INTO customer (cust_name,cust_profession,cust_phone,email) VALUES
        (#{custName},#{custProfession},#{custPhone},#{email})
    </insert>
    <!--查询所有用户订单-->
    <resultMap id="customer" type="Customer">
        <!--配置映射-->
        <result column="cust_id" property="custId"/>
        <result column="cust_name" property="custName"/>
        <result column="cust_profession" property="custProfession"/>
        <collection property="order" javaType="list" ofType="Order">
            <!--配置主键，是关联order的唯一标识-->
            <id column="order_id" property="orderId"/>
            <result column="order_num" property="orderNum"/>
            <result column="order_note" property="orderNote"/>
        </collection>
    </resultMap>
    <select id="queryAll" resultMap="customer" >
        SELECT * FROM  customer AS c LEFT JOIN orders AS o ON c.cust_id=o.cust_id;
    </select>
    <!--根据用户名模糊查询用户-->
    <select id="queryByName" resultType="demo2.domain.Customer" parameterType="String">
        SELECT * FROM customer WHERE cust_name LIKE #{cust_name}
    </select>
    <!--更新用户-->
    <update id="updateeCustomerById" parameterType="demo2.domain.Customer">
        UPDATE customer SET cust_name=#{cust_name}where cust_id=#{cust_id}
    </update>
    <!--删除用户-->
    <delete id="deleteById" parameterType="Integer">
        DELETE FROM customer WHERE cust_id=#{cust_id}
    </delete>
    <!--根据名称和id查找用户-->
    <select id="queryCustomerByIdAndName"  resultType="demo2.domain.Customer">
        SELECT * FROM customer WHERE cust_id=#{cust_id} AND cust_name=#{cust_name}
    </select>
    <!--统计-->
    <select id="Count" resultType="Integer">
        SELECT COUNT(*) FROM customer;
    </select>
</mapper>

Customer的Mapper接口类

public interface CustomerMapper {
   public Customer queryCustomerById(Integer id);
   public List<Customer> queryAll();
   public Customer queryByName(String name);
   public void addCustomer(Customer customer);
   public void updateeCustomerById(Customer customer);
   public void deleteById(Integer id);
   public  Customer queryCustomerByIdAndName(Map<String,Object> map);
   public Integer Count();
}

Order的Mapping配置文件

<?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="demo2.mapper.OrderMapper">

    <resultMap id="order" type="Order">
        <!--order当中需要映射的字段，只要配置到result里才会被赋值，不会自动映射赋值-->
        <result property="orderId" column="order_id"/>
        <result property="orderNum" column="order_num"/>
        <result property="orderNote" column="order_note"/>
        <result property="custId" column="cust_id"/>
        <!--order内部关联的对象-->
        <association property="customer" javaType="Customer">
            <!--关联对象的键-->
            <result property="custId" column="cust_id"/>
            <result property="custName" column="cust_name"/>
            <result property="custProfession" column="cust_profession"/>
            <result property="custPhone" column="cust_phone"/>
            <result property="email" column="email"/>
        </association>
    </resultMap>
    <!--查询订单-->
    <select id="queryOrder" resultMap="order">
        SELECT * FROM orders AS o LEFT JOIN customer AS  c ON o.cust_id=c.cust_id;
    </select>
    <!--添加订单-->
    <insert id="addOrder" parameterType="Order" useGeneratedKeys="true" keyColumn="order_id" keyProperty="orderId">
        INSERT  INTO orders(order_num,order_time,order_note,cust_id) VALUES
        (#{orderNum},#{orderTime},#{orderNote},#{customer.custId})
    </insert>
    <!--更新外键-->
    <update id="updateKey">
        UPDATE orders SET cust_id =#{custId} WHERE order_id=#{orderId}
    </update>
    <!--删除订单-->
    <delete id="deleteOrder" parameterType="Integer">
        DELETE  FROM  orders  WHERE order_id=#{order_id}
    </delete>
    <!--删除前打破关系-->
    <update id="deleteRelationship" parameterType="Integer">
        UPDATE orders SET cust_id=NULL WHERE cust_id=#{cust_id}
    </update>
</mapper>

Order的Mapper接口类

public interface OrderMapper {
    public List<Order> queryOrder();
    public void addOrder(Order order);
    public void updateKey(@Param("custId") Integer custId, @Param("orderId") Integer orderId);
    public void deleteOrder(Integer id);
    public void deleteRelationship(Integer id);
}

测试类的测试方法

@Test
public void test4() throws IOException {
    this.init();
    //1、创建sqlSession对象
    SqlSession sqlSession = sqlSessionFactory.openSession();
    //2、加载Mapper接口
    OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class);
    CustomerMapper customerMapper = sqlSession.getMapper(CustomerMapper.class);
    //3、执行OrderMapper的接口方法打断关系，即将外键属性值设置为空，然后再执行CustomerMapper的接口方法执行删除用户表记录（父表）操作
    orderMapper.deleteRelationship(28);
    customerMapper.deleteById(28);
    //4、提交事务，更新数据库
    sqlSession.commit();
    //5、关闭连接，释放资源
    sqlSession.close();
}

多对多

1、创建多对多关系表

库表中的数据如下所示

2. student的domain类

@Setter@Getter@ToString
public class Student {
    private Integer stuId;
    private String stuName;
}

3. Student的mapping配置文件

<?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="demo3.mapper.StudentMapper">
    <select id="selectByTeacher"  resultType="Student">
        SELECT * FROM student where stu_id IN (SELECT  stu_id FROM stu_teach WHERE teach_id=#{id})
    </select>
</mapper>

4. Student的mapper接口类

public interface StudentMapper {
    public List<Student> selectByTeacher(Integer id);
}

5. Teacher的domain类

@Setter@Getter@ToString
public class Teacher {
    private Integer teachId;
    private String teachName;
    private List<Student> student=new ArrayList<>();
}

6. Teacher的mapping配置文件

<?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="demo3.mapper.TeacherMapper">
    <resultMap id="Teacher" type="Teacher">
        <result column="teach_id" property="teachId"/>
        <collection property="student" javaType="list" ofType="Student"
                    select="demo3.mapper.StudentMapper.selectByTeacher"
                    column="teach_id">
        </collection>
    </resultMap>
    <select id="selectById" parameterType="Integer" resultMap="Teacher">
        SELECT * FROM teacher WHERE teach_id=#{id}
    </select>
</mapper>

7. Teacher的mapper接口类

public interface TeacherMapper {
    public Teacher selectById(Integer id);
}

8. SqlMappingConfig配置文件

<?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>
    <!--用resource引入外部属性配置文件-->
    <properties resource="db.properties">
        <!--在properties内部用property定义属性-->
        <!--如果外部配置文件有该属性，则内部定义的属性会被外部属性覆盖-->
        <property name="jdbc.username" value="root"/>
        <property name="jdbc.password" value="1234"/>
    </properties>
    <!--用来配置Mybatis中的设置-->
    <settings>
        <!--开启驼峰映射，为自定义的SQL语句服务-->
        <!--设置启用数据库字段下划线映射到java对象的驼峰式命名属性，默认为false-->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>
    <typeAliases>
        <!--单个别名定义-->
        <typeAlias alias="Customer" type="demo2.domain.Customer"/>
        <typeAlias alias="Order" type="demo2.domain.Order"/>
        <typeAlias alias="Teacher" type="demo3.domain.Teacher"/>
        <typeAlias alias="Student" type="demo3.domain.Student"/>
        <!--批量别名定义，扫描整个包下的类，别名为类名（大小写不敏感）-->
        <!--注意：如果当前包类与子包类重名，则会报异常，我们可以在类上使用注解@Alias（"别名"）-->
        <!--<package name="demo2"/>-->
    </typeAliases>
    <environments default="development">
        <environment id="development">
            <!--使用jdbc事务管理-->
            <transactionManager type="JDBC"/>
            <!--配置数据库连接池-->
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>
    <!--加载映射文件-->
    <mappers>
        <!--<mapper resource="demo1/Mapping"/>-->
        <mapper resource="demo2/mapper/Mapping"/>
        <mapper resource="demo2/mapper/Mapping2"/>
        <mapper resource="demo3/mapper/StudentMapping"/>
        <mapper resource="demo3/mapper/TeacherMapping"/>
    </mappers>
</configuration>

9. db.properties属性文件

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/development
jdbc.username=root
jdbc.password=123456

10. 测试类

public class test {
    private SqlSessionFactory sqlSessionFactory;
    public void init() throws IOException {
        //1、创建SqlSessionFactoryBuilder对象
        SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
        //2、加载SqlMappingConfig配置文件
        InputStream inputStream = Resources.getResourceAsStream("SqlMappingConfig");
        //3、创建sqlSessionFactory对象
        sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream);
    }
    @Test
    public void test() throws IOException {
        this.init();
        SqlSession sqlSession = sqlSessionFactory.openSession();
        TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
        Teacher teacher = mapper.selectById(1);
        System.out.println(teacher);
    }
}

运行结果

分步查询：

1、查出指定的老师

 SELECT **** *FROM teacher WHERE teach_id=#{id}

1. 根据步骤一查询出来的结果查询中间表stu_teach得到指定老师关联的所有学生

 SELECT **** *FROM student where stu_id IN (SELECT  stu_id FROM stu_teach WHERE teach_id=#{id})

左连接查询

1. Teacher的mapping配置文件

<?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="demo3.mapper.TeacherMapper">
    <resultMap id="Teacher" type="Teacher">
        <result column="teach_id" property="teachId"/>
        <collection property="student" javaType="list" ofType="Student"
                    select="demo3.mapper.StudentMapper.selectByTeacher"
                    column="teach_id">
        </collection>
    </resultMap>
    <select id="selectById" parameterType="Integer" resultMap="Teacher">
        SELECT * FROM teacher WHERE teach_id=#{id}
    </select>

    <!--左连接查询-->
    <resultMap id="teacherMap" type="Teacher">
        <id column="teach_id" property="teachId"/>
        <result column="teach_name" property="teachName"/>
        <collection property="student" javaType="list" ofType="Student">
            <id column="stu_id" property="stuId"/>
            <result column="stu_name" property="stuName"/>
        </collection>
    </resultMap>
    <select id="getTeacher" resultMap="teacherMap">
        SELECT * FROM teacher AS  t
        LEFT JOIN stu_teach AS st ON t.teach_id=st.teach_id
        LEFT JOIN student AS s ON  s.stu_id=st.stu_id
        WHERE t.teach_id=#{id};
    </select>
</mapper>

1. Teacher的mapper接口类

public interface TeacherMapper {
    public Teacher selectById(Integer id);
    public List<Teacher> getTeacher(Integer id);
}

1. 测试类的测试方法

@Test
public void test1() throws IOException {
    this.init();
    SqlSession sqlSession = sqlSessionFactory.openSession();
    TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
    List<Teacher> teachers = mapper.getTeacher(1);
    for (Teacher teacher:teachers) {
        System.out.println(teacher);
    }
    sqlSession.close();
}

运行结果同分步查询