Mybatis复杂查询环境搭建以及联表查询

面向接口编程

在开发中一般使用面向接口编程，架构师在Mapper文件中定义接口，交给别人去实现，实现了定义和实现的分离，并且在大型网站上，定义的接口都是异步的
接口应该分为2个类
对一个个体的抽象，可对应一个抽象体(abstract class) 对一个个体某一方面的抽象，是一个抽象面(interface)

复杂查询环境搭建

创建新模块

1. 创建资源文件config，拷入mybatis-config.xml和db.properties文件
2. 拷入源码结构，删除Dao接口和pojo老式Java对象，保留工具类
3. 删除mybatis-config.xml里面的mapper标签
4. 在子工程pom.xml中导入lombok
   

多对一处理

编写实体类

package com.bkms.pojo;
import lombok.Data;

@Data
public class Student {
    private int id;
    private String name;
    // 学生需要关联一个老师
    private Teacher teacher;
}

package com.bkms.pojo;

import lombok.Data;

@Data
public class Teacher {
    private int id;;
    private String name;
}

编写Mapper接口和测试环境

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.bkms.dao.TeacherMapper">
</mapper>

package com.bkms.dao;

import com.bkms.pojo.Teacher;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

public interface TeacherMapper {
    @Select("select * from teacher where id = #{tid}")
    Teacher getTeacherList(@Param("tid") int id);
}

import com.bkms.dao.TeacherMapper;
import com.bkms.pojo.Teacher;
import com.bkms.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

public class MyTest {
    @Test
    public void getTeacherTest(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
        Teacher teacherList = mapper.getTeacherList(1);
        System.out.println(teacherList);
        sqlSession.close();
    }
}

联表查询

    <!-- method 2 -->
    <select id="getStudentList2" resultMap="ST2">
        select s.id sid,s.name sname,t.name tname,t.id tid
        from student s,teacher t
        where s.tid = t.id;
    </select>

    <resultMap id="ST2" type="Student">
        <result property="id" column="sid"/>
        <result property="name" column="sname"/>
        <association property="teacher" javaType="Teacher">
            <result property="id" column="tid"/>
            <result property="name" column="tname"/>
        </association>
    </resultMap>

	@Test
    public void getStudentListTest2() {
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        List<Student> studentList = mapper.getStudentList2();
        for (Student student : studentList) {
            System.out.println(student);
        }
        sqlSession.close();
    }

一对多处理

先创建新模块

• 导入依赖
• 导入config资源文件
• 导入源码
• 清理dao文件夹下的Mapper接口和xml文件
• 清理测试文件夹
• 修改实体类

	@Test
    public void getTeacherByIdTest() {
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
        Teacher teacher = mapper.getTeacherById(1);
        System.out.println(teacher);
        /*
         * Teacher
         * (id=1, name=秦老师, students=
         * [
         * Student(id=1, name=小明, tid=1), 
         * Student(id=2, name=小红, tid=1), 
         * Student(id=3, name=小张, tid=1), 
         * Student(id=4, name=小李, tid=1), 
         * Student(id=5, name=小王, tid=1)
         * ]
         * ) 
         */
        sqlSession.close();
    }

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.bkms.dao.TeacherMapper">
    <!-- 获取所有老师信息 -->
    <select id="getTeachersList" resultType="Teacher">
        select * from mydb.teacher
    </select>

    <!-- 获取指定老师下的所有学生及老师信息 -->
    <select id="getTeacherById" resultMap="TS">
        select s.id sid,s.name sname,t.id tid,t.name tname
        from student s,teacher t
        where s.tid = t.id and t.id = #{tid}
    </select>

    <resultMap id="TS" type="Teacher">
        <result column="tid" property="id" />
        <result column="tname" property="name" />
        <!-- students成员属性 Student集合泛型 -->
        <collection property="students" ofType="Student">
            <result column="sid" property="id" />
            <result column="sname" property="name" />
            <result column="tid" property="tid" />
        </collection>
    </resultMap>
</mapper>

package com.bkms.dao;

import com.bkms.pojo.Teacher;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface TeacherMapper {
    // 获取老师列表
    List<Teacher> getTeachersList();

    // 获取指定老师下的所有学生及老师信息
    Teacher getTeacherById(@Param("tid") int id);
}