MyBatis-多对一，一对多

整体

数据库

DROP TABLE IF EXISTS `student`;
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;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', '小明', '1');
INSERT INTO `student` VALUES ('2', '小红', '1');
INSERT INTO `student` VALUES ('3', '小张', '1');
INSERT INTO `student` VALUES ('4', '小李', '1');
INSERT INTO `student` VALUES ('5', '小王', '1');

DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `id` int(10) NOT NULL,
  `name` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('1', '秦老师');

 

 

mybatis-config.xml

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

    <properties resource="db.properties"/>

    <!--标准日志实现-->
    <!--<settings>-->
        <!--<setting name="logImpl" value="STDOUT_LOGGING"/>-->
    <!--</settings>-->

    <!--通过使用Log4j，我们可以控制日志信息输送的目的地：控制台，文本，GUI组件....-->
    <settings>
        <setting name="logImpl" value="STDOUT_LOGGING"/>
    </settings>

    <!--配置别名,注意顺序-->
    <typeAliases>
        <typeAlias type="com.yao.pojo.Student" alias="Student"/>
        <typeAlias type="com.yao.pojo.Teacher" alias="Teacher"/>
    </typeAliases>


    <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://192.168.182.153:3306/mybatis?useSSL=false&amp;useUnicode=true&amp;characterEncoding=utf8"/>
                <!--<property name="url" value="jdbc:mysql://192.168.182.153:3306/mybatis?useSSL=true&amp;useUnicode=true&amp;characterEncoding=utf8"/>-->
                <property name="username" value="${username}"/>
                <property name="password" value="jarvis123456"/>
            </dataSource>
        </environment>
    </environments>


    <mappers>
        <mapper resource="com/yao/dao/StudentMapper.xml"/>
        <mapper resource="com/yao/dao/TeacherMapper.xml"/>
    </mappers>


</configuration>

StudentMapper.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.yao.dao.StudentMapper">
    <!--
      需求：获取所有学生及对应老师的信息
      思路：
          1. 获取所有学生的信息
          2. 根据获取的学生信息的老师ID->获取该老师的信息
          3. 思考问题，这样学生的结果集中应该包含老师，该如何处理呢，数据库中我们一般使用关联查询？
              1. 做一个结果集映射：StudentTeacher
              2. StudentTeacher结果集的类型为 Student
              3. 学生中老师的属性为teacher，对应数据库中为tid。
                 多个 [1,...）学生关联一个老师=> 一对一，一对多
              4. 查看官网找到：association – 一个复杂类型的关联；使用它来处理关联查询
      -->
    <select id="getStudents" resultMap="StudentTeacher">
    select * from student
   </select>
    <resultMap id="StudentTeacher" type="Student">
        <!--association关联属性 property属性名 javaType属性类型 column在多的一方的表中的列名-->
        <association property="teacher"  column="tid" javaType="Teacher" select="getTeacher"/>
    </resultMap>
    <!--
    这里传递过来的id，只有一个属性的时候，下面可以写任何值
    association中column多参数配置：
        column="{key=value,key=value}"
        其实就是键值对的形式，key是传给下个sql的取值名称，value是片段一中sql查询的字段名。
    -->
    <select id="getTeacher" resultType="teacher">
      select * from teacher where id = #{id}
   </select>

</mapper>

TeacherMapper.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.yao.dao.TeacherMapper">
    <!--
      思路:
          1. 从学生表和老师表中查出学生id，学生姓名，老师姓名
          2. 对查询出来的操作做结果集映射
              1. 集合的话，使用collection！
                  JavaType和ofType都是用来指定对象类型的
                  JavaType是用来指定pojo中属性的类型
                  ofType指定的是映射到list集合属性中pojo的类型。
      -->
    <select id="getTeacher2"  resultMap="TeacherStudent">
      select s.id sid, s.name sname , t.name tname, t.id tid
      from student s,teacher t
      where s.tid = t.id and t.id=#{id}
   </select>

    <resultMap id="TeacherStudent" type="Teacher">
        <result  property="name" column="tname"/>
        <collection property="students" ofType="Student">
            <result property="id" column="sid" />
            <result property="name" column="sname" />
            <result property="tid" column="tid" />
        </collection>
    </resultMap>
</mapper>

Student

package com.yao.pojo;
import lombok.Data;
@Data
public class Student {
    private int id;
    private String name;
    private int tid;
    //多个学生可以是同一个老师，即多对一
    private Teacher teacher;
}

Teacher

package com.yao.pojo;
import lombok.Data;
import java.util.List;
@Data //GET,SET,ToString，有参，无参构造
public class Teacher {
    private int id;
    private String name;
    private List<Student> students;
}

 

StudentMapper

package com.yao.dao;
import com.yao.pojo.Student;
import java.util.List;
    public interface StudentMapper {
    //获取所有学生及对应老师的信息
    public List<Student> getStudents();

}

TeacherMapper

package com.yao.dao;
import com.yao.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 getTeacher(@Param("tid") int id);

    //获取指定老师，及老师下的所有学生
    public Teacher getTeacher2(int id);
}

 

Test

import com.yao.dao.StudentMapper;
import com.yao.dao.TeacherMapper;
import com.yao.pojo.Student;
import com.yao.pojo.Teacher;
import com.yao.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.List;

//多对一
public class MyTest {
    public static void main(String[] args) {
        SqlSession session = MybatisUtils.getSession();
        StudentMapper mapper = session.getMapper(StudentMapper.class);
//        TeacherMapper mapper = session.getMapper(TeacherMapper.class);
//        Teacher teacher=mapper.getTeacher(1);
//        System.out.println("teacher = " + teacher);
        List<Student> students = mapper.getStudents();
        for(Student student: students)
        {
            System.out.println("学生名:"+ student.getName() +"\t老师:"+student.getTeacher().getName());
        }
        session.close();
    }




//    一对多
    @Test
    public void testGetTeacher(){
        SqlSession session = MybatisUtils.getSession();
        TeacherMapper mapper = session.getMapper(TeacherMapper.class);
        Teacher teacher = mapper.getTeacher2(1);
        System.out.println(teacher.getName());
        System.out.println(teacher.getStudents());
    }


}