MyBatis关联查询（xml方式和注解方式都有）

本博客将用简单的demo来展示MyBatis关联查询的全部写法，如果写的不好还望评论区的小伙伴指点和补充！

前置环境和数据库的表

• 本项目所需要的jar包：
  
• 数据库的表：
  student表：
  
  card表：
  
  studentclass表
  
• MyBatis核心配置文件config.xml：（主要配置数据库信息和mapper接口位置）

<?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>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <!-- 配置数据库信息 -->
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/quehuimin"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <!-- 加载映射文件 -->
        <mapper class="mapper.StudentMapper"/>  <!--注解版本用这个-->
<!--        <mapper resource="mapper/studentMapper.xml"/>--><!--xml版本用这个-->
    </mappers>
</configuration>

根据数据库的表编写实体类

public class Student {
    private Integer id;
    private String name;
    private Integer age;
    private Integer cardid;
    private Integer classid;
    private Card card;
    public Student(Integer id, String name, Integer age, Integer cardid,Integer classid) {//无需写card 属性
        this.id = id;
        this.name = name;
        this.age = age;
        this.cardid = cardid;
        this.classid = classid;
    }
//省略setter,getter,toString

public class Card {
    private Integer cardid;
    private String Info;
    public Card(Integer cardid, String info) {
        this.cardid = cardid;
        Info = info;
    }
    //省略setter,getter,toString

public class StudentClass {
    private Integer classid;
    private String classinfo;
    private List<Student> students;

    public StudentClass(Integer classid, String classinfo) {//无需 students;
        this.classid = classid;
        this.classinfo = classinfo;
    }
        //省略setter,getter,toString

！！！！！1.注解方式的Mapper接口写法：！！！！！

1.一对一查询：
根据id查询学生student的全部信息，利用信息中查出来的cardid去关联查询出该学生的学生卡card的信息

public interface StudentMapper {
    // 1 to 1

    @Select("select *from card where cardid = #{cardid}")
    public Card getCardByCardid(int cardid);


    @Select("select * from student where id=#{id}")
    @Results({
            @Result(column = "cardid",property = "card",one=@One(select
                    = "mapper.StudentMapper.getCardByCardid"))
    })
    public Student getStudent(int id);
}

测试方法：

public static void T() throws IOException {
        String resource = "config.xml";
        Reader reader = Resources.getResourceAsReader(resource);
        SqlSessionFactory sessionFactory
                = new SqlSessionFactoryBuilder().build(reader);
        SqlSession session = sessionFactory.openSession( );

        StudentMapper studentMapper = session.getMapper(StudentMapper.class);//接口
        Student student = studentMapper.getStudent(2);
        System.out.println(student);

        session.close();
    }

2.一对多查询（级联）：
根据classid查出班级studentclass的信息，在利用classid关联查询所有该班级的所有学生，再利用每个学生的cardid，关联查询出每个学生学生卡Card的信息

public interface StudentMapper {

	@Select("select *from card where cardid = #{cardid}")
    public Card getCardByCardid(int cardid);

    @Select("select * from student where classid=#{classid}")
    @Results({
            @Result(column = "cardid",property = "card",one=@One(select
                    = "mapper.StudentMapper.getCardByCardid"))
    })
    public List<Student> getStudent2(int classid);



    @Select("select *from studentclass where classid=#{classid}")
    @Results({
            @Result(id = true,column = "classid",property = "classid"),
            @Result(column = "classinfo",property = "classinfo"),
            @Result(column = "classid",property = "students",many = @Many(select =
            "mapper.StudentMapper.getStudent2"))
    })
    public StudentClass getStudentClass(int classid);
}

测试方法：

public static void T2() throws IOException {
        String resource = "config.xml";
        Reader reader = Resources.getResourceAsReader(resource);
        SqlSessionFactory sessionFactory
                = new SqlSessionFactoryBuilder().build(reader);
        SqlSession session = sessionFactory.openSession( );

        StudentMapper studentMapper = session.getMapper(StudentMapper.class);//接口
        StudentClass studentClass = studentMapper.getStudentClass(1);
        System.out.println(studentClass);

        session.close();
    }

！！！！！2.xml方式的配置：！！！！！

1.一对一关联查询
根据id查询学生student的全部信息，利用信息中查出来的cardid去关联查询出该学生的学生卡card的信息

• 1.先在接口中写抽象方法：

public interface StudentMapper {
	public Student getStudent3(int id); //xml方式的 的一对一查询
}

• 2.在mapper.xml中使用resultMap+association

<?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="mapper.StudentMapper">
    <select id="getCardByCardid2" resultType="pojo.Card" parameterType="int"><!--如果id爆红不需要管-->
        select *from card where cardid = #{cardid}
    </select>

    <select id="getStudent3" resultMap="aa" parameterType="int">
        select * from student where id = #{id}
    </select>
    <resultMap id="aa" type="pojo.Student">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="age" column="age"/>
        <result property="cardid" column="cardid"/>
        <result property="classid" column="classid"/>
        <association property="card" column="cardid" select="mapper.StudentMapper.getCardByCardid2" javaType="pojo.Card"/>
    </resultMap>
</mapper>

测试方法：

public static void T3() throws IOException {
        String resource = "config.xml";
        Reader reader = Resources.getResourceAsReader(resource);
        SqlSessionFactory sessionFactory
                = new SqlSessionFactoryBuilder().build(reader);
        SqlSession session = sessionFactory.openSession( );

        StudentMapper studentMapper = session.getMapper(StudentMapper.class);//接口
        Student student = studentMapper.getStudent3(2);
        System.out.println(student);

        session.close();
    }

2.一对多查询
根据classid查出班级studentclass的信息，在利用classid关联查询所有该班级的所有学生，再利用每个学生的cardid，关联查询出每个学生学生卡Card的信息

• 先在接口中写抽象方法

public interface StudentMapper {
	public StudentClass getStudentClass2(int classid);//xml格式的一对多查询
}

• 2.在mapper.xml中使用resultMap+collection

<?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="mapper.StudentMapper">
    <select id="getStudentByClassid" resultMap="aa" parameterType="int"><!--如果id爆红不需要管-->
        select *from student where classid=#{classid}
    </select>

    <select id="getStudentClass2" resultMap="bb">
        select *from studentclass where classid=#{classid}
    </select>
    <resultMap id="bb" type="pojo.StudentClass">
        <id property="classid" column="classid"/>
        <result property="classinfo" column="classinfo"/>
        <collection property="students" column="classid" ofType="pojo.Student" select="mapper.StudentMapper.getStudentByClassid"/>
    </resultMap>
</mapper>

测试方法：

 public static void T4() throws IOException {
        String resource = "config.xml";
        Reader reader = Resources.getResourceAsReader(resource);
        SqlSessionFactory sessionFactory
                = new SqlSessionFactoryBuilder().build(reader);
        SqlSession session = sessionFactory.openSession( );

        StudentMapper studentMapper = session.getMapper(StudentMapper.class);//接口
        StudentClass studentClass = studentMapper.getStudentClass2(1);
        System.out.println(studentClass);

        session.close();
    }