Mybatis 复杂环境搭建和处理

创建表

插入语句

CREATE TABLE `teacher` (
  `id` INT(10) NOT NULL,
  `name` VARCHAR(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO teacher(`id`, `name`) VALUES (1, '不知道取什么名字的老师'); 


CREATE TABLE `student` (
  `id` INT(10) NOT NULL,
  `name` VARCHAR(30) DEFAULT NULL,
  `tid` INT(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fktid` (`tid`),
  CONSTRAINT `fktid` FOREIGN KEY (`tid`) REFERENCES `teacher` (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('1', '小明', '1'); 
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('2', '小红', '1'); 
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('3', '小张', '1'); 
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('4', '小李', '1'); 
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('5', '小王', '1');

老师有ID和名字,学生有自己的ID和对应的老师ID和名字。

实现多对一查询问题处理

程序需要实现查询所有学生信息以及对应的老师信息

按照查询嵌套处理

定义老师,学生类

package pojo;

import lombok.Data;

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

package pojo;

import lombok.Data;

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

定义学生方法接口

package dao;
import pojo.Student;
import java.util.List;
public interface StudentMapper {
    //查询所有学生信息以及对应的老师信息
    public List<Student> findStudentTeacher();
}

配置mapper

<?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">
<!--namespace对应接口名字-->
<mapper namespace="dao.StudentMapper">
    <!--
    查询所有学生和对应的老师信息
    思路:
    1.查询所有学生的信息
    2.根据查询出来的学生tid,寻找对应的老师
    -->
    <select id="findStudentTeacher" resultMap="StudentTeacher">
        select * from student
    </select>

    <resultMap id="StudentTeacher" type="Student">
        <result property="id" column="id"/>
        <result property="name" column="name"/>
        <!--学生类中还有一个属性是Teacher类 在mybatis中使用association处理-->
        <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
    </resultMap>
    
    <select id="getTeacher" resultType="Teacher">
        select * from teacher where id=#{id}
    </select>
</mapper>

主程序:

import dao.StudentMapper;
import dao.TeacherMapper;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import pojo.Student;
import pojo.Teacher;
import utiles.MybatisUtiles;

import java.util.List;

public class Mytest {
    @Test
    public void test(){
        SqlSession sqlSession = MybatisUtiles.getSqlSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        List<Student> students = mapper.findStudentTeacher();
        for (Student student : students) {
            System.out.println(student);
        }
        sqlSession.close();
    }
}

按照结果嵌套处理(推荐)

将mapper文件改成如下:

<?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">
<!--namespace对应接口名字-->
<mapper namespace="dao.StudentMapper">
    <select id="findStudentTeacher2" resultMap="StudentTeacher2">
        select s.id sid,s.name sname,t.id tid,t.name tname
        from student s,teacher t
        where s.tid=t.id
    </select>
    <resultMap id="StudentTeacher2" type="Student">
        <result property="id"   column="sid"/>
        <result property="name" column="sname"/>
        <!--teacher对应student类中的属性teacher 由于是一个复杂类型需要javaType告诉mybatis类型-->
        <association property="teacher" javaType="Teacher">
            <result property="id" column="tid"/>
            <result property="name" column="tname"/>
        </association>
    </resultMap>
</mapper>

一对多处理

查询一个老师以及他对应的学生信息
将类定义修改为:

package pojo;

import lombok.Data;

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

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

@Data
public class Teacher {
    private int id;
    private String name;
    //老师拥有一群学生
    private List<Student> students;
}

定义接口和mapper文件

package dao;

import pojo.Teacher;

import java.util.List;

public interface TeacherMapper{
    public List<Teacher> findStudentTeacher();
}

<?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">
<!--namespace对应接口名字-->
<mapper namespace="dao.TeacherMapper">

    <select id="findStudentTeacher" resultMap="StudentTeacher">
        select t.id tid,t.name tname,s.id sid,s.name sname
        from teacher t,student s
        where t.id=s.tid;
    </select>
    <resultMap id="StudentTeacher" type="Teacher">
        <result property="id" column="tid"/>
        <result property="name" column="tname"/>
        <!--List这种的用ofType-->
        <collection property="students" ofType="Student">
            <result property="id" column="sid"/>
            <result property="name" column="sname"/>
            <result property="tid" column="tid"/>
        </collection>
    </resultMap>

</mapper>

主程序

import dao.StudentMapper;
import dao.TeacherMapper;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import pojo.Student;
import pojo.Teacher;
import utiles.MybatisUtiles;

import java.util.List;

public class Mytest {
    @Test
    public void test(){
        SqlSession sqlSession = MybatisUtiles.getSqlSession();
        TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
        List<Teacher> teachers = mapper.findStudentTeacher();
        for (Teacher teacher : teachers) {
            System.out.println(teacher.getId()+teacher.getName());
            List<Student> students = teacher.getStudents();
            for (Student student : students) {
                System.out.println(student);
            }
        }
        sqlSession.close();
    }
}