1 一对多查询

对 student 数据表和 classes 数据表进行级联查询。（通过学生查班级）

• 创建实体类 Classes 和 Student

package com.southwind.entity;

import lombok.Data;

import java.util.List;

@Data
public class Classes {
    private long id;
    private String name;
    private List<Student> students;
}

package com.southwind.entity;

import lombok.Data;

@Data
public class Student {
    private long id;
    private String name;
    private Classes classes;
}

• 创建 StudentRepository 接口

package com.southwind.repository;

import com.southwind.entity.Student;

public interface StudentRepository {
    public Student findById(long id);
}

• 创建 StudentRepository.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.southwind.repository.StudentRepository">
    <select id="findById" parameterType="long" resultType="com.southwind.entity.Student">
        select s.id,s.name,c.id,c.name from student s,classes c where s.id=#{id} and s.cid=c.id
    </select>
</mapper>

• 在 config.xml 中注册 StudentRepository.xml

<mapper resource="com/southwind/repository/StudentRepository.xml"></mapper>

• 调用接口的代理对象完成相关的业务操作

package com.southwind.test;

import com.southwind.repository.StudentRepository;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.InputStream;

public class Test3 {
    public static void main(String[] args) {
        InputStream inputStream = Test2.class.getClassLoader().getResourceAsStream("config.xml");
        SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
        SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream);
        SqlSession sqlsession = sqlSessionFactory.openSession();
        StudentRepository studentRepository = sqlsession.getMapper(StudentRepository.class);
        System.out.println(studentRepository.findById(1));
    }
}

• 运行结果
  

那么：为什么 classes = null ？？？
答：因为查出来的表格前两列与Student属性id和name相对应，故会将值赋给这两个属性，而由于后面表中没有列名为classes的列，故无法赋值，所以classes为null。

• 处理方法，修改 StudentRepository.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.southwind.repository.StudentRepository">
    <resultMap id="studentMap" type="com.southwind.entity.Student">
        <id column="id" property="id"></id><!-- 主键 -->
        <result column="name" property="name"></result>
        <association property="classes" javaType="com.southwind.entity.Classes">
            <id column="cid" property="id"></id>
            <result column="cname" property="name"></result>
        </association>
    </resultMap>
    <select id="findById" parameterType="long" resultMap="studentMap">
        select s.id,s.name,c.id as cid,c.name as cname from student s,classes c where s.id=#{id} and s.cid=c.id
    </select>
</mapper>

• 运行结果：
  
   

2 多对多查询

• 创建实体类：Customer、Goods

package com.southwind.entity;

import lombok.Data;
import java.util.List;

@Data
public class Customer {
    private long id;
    private String name;
    private List<Goods> goods;
}

package com.southwind.entity;

import lombok.Data;
import java.util.List;

@Data
public class Goods {
    private long id;
    private String name;
    private List<Customer> customers;
}

• 创建 CustomerRepository 接口，以及 CustomerRepository.xml

package com.southwind.repository;

import com.southwind.entity.Customer;

public interface CustomerRepotory {
    public Customer findById(long id);
}

<?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.southwind.repository.CustomerRepository">
    <resultMap id="customerMap" type="com.southwind.entity.Customer">
        <id column="cid" property="id"></id>
        <result column="cname" property="name"></result>
        <collection property="goods" ofType="com.southwind.entity.Goods">
            <id column="gid" property="id"/>
            <result column="gname" property="name"/>
        </collection>
    </resultMap>
    <select id="findById" parameterType="long" resultMap="customerMap">
        select c.id cid,c.name cname,g.id gid,g.name gname from customer c,goods
            g,customer_goods cg where c.id = #{id} and cg.cid = c.id and cg.gid = g.id
    </select>
</mapper>

• 创建 GoodsRepository 接口 和 GoodsRepository.xml

package com.southwind.repository;

import com.southwind.entity.Goods;

public interface GoodsRepository {
    public Goods findById(long id);
}

<?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.southwind.repository.GoodsRepository">
    <resultMap id="goodsMap" type="com.southwind.entity.Goods">
        <id column="gid" property="id"></id>
        <result column="gname" property="name"></result>
        <collection property="customers" ofType="com.southwind.entity.Customer">
            <id column="cid" property="id"/>
            <result column="cname" property="name"/>
        </collection>
    </resultMap>
    <select id="findById" parameterType="long" resultMap="goodsMap">
        select c.id cid,c.name cname,g.id gid,g.name gname from customer c,goods
            g,customer_goods cg where g.id = #{id} and cg.cid = c.id and cg.gid = g.id
    </select>
</mapper>