Mybatis-Study10-一对多

测试环境搭建

• 数据库

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);

• 包的结构
  

• dao层的StudentMapper和TeacherMapper接口

StudentMapper接口

public interface StudentMapper {
}

TeacherMapper接口

public interface TeacherMapper {
//测试数据库是否连接成功所用的方法
    @Select("select * from teacher where id=#{tid}")
    Teacher getteacher(@Param("tid") int id);
}

• pojo层Student和Teacher实体类
  Student类

public class Student {
    private int id;
    private String name;
    private int tid;


    public Student() {
    }

    public Student(int id, String name, int tid) {
        this.id = id;
        this.name = name;
        this.tid = tid;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getTid() {
        return tid;
    }

    public void setTid(int tid) {
        this.tid = tid;
    }

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", tid=" + tid +
                '}';
    }
}

Teacher类

public class Teacher {
    private int id;
    private String name;
    private List<Student> students;

    public Teacher() {
    }

    public Teacher(int id, String name, List<Student> students) {
        this.id = id;
        this.name = name;
        this.students = students;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public List<Student> getStudents() {
        return students;
    }

    public void setStudents(List<Student> students) {
        this.students = students;
    }

    @Override
    public String toString() {
        return "Teacher{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", students=" + students +
                '}';
    }
}

• utils层的MybatisUtils工具类

public class MybatisUtils {
    private static SqlSessionFactory sqlSessionFactory;
    static {
        try{
            String resource="mybatis-config.xml";
            InputStream inputStream= Resources.getResourceAsStream(resource);
            sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);

        }catch (Exception e){
            e.printStackTrace();
        }
    }
    public static SqlSession getSqlSession(){
        //设置为true目的是自动提交事务，不需要在设置commit
        return sqlSessionFactory.openSession(true);
    }
}

• resources目录下的配置文件

db.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mybatis?useSSL=false&;useUnicode=true&;characterEncoding=UTF-8&;serverTimezone=UTC
username=root
password=123456

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"></properties>
    <settings>
        <setting name="logImpl" value="STDOUT_LOGGING"/>
    </settings>

    <environments default="mytest">
        <environment id="mytest">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper class="com.wang.dao.TeacherMapper"></mapper>
        <mapper class="com.wang.dao.StudentMapper"></mapper>
    </mappers>

</configuration>

StudentMapper.xml文件

<?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.wang.dao.StudentMapper">

</mapper>

TeacherMapper.xml文件

<?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.wang.dao.TeacherMapper">
</mapper>

• 测试类

public class MyTest {
//测试数据库是否连接成功
    @Test
    public void test(){
        SqlSession sqlSession= MybatisUtils.getSqlSession();
        TeacherMapper mapper=sqlSession.getMapper(TeacherMapper.class);
        Teacher teacher=mapper.getteacher(1);
        System.out.println(teacher);
        sqlSession.close();
    }
}

一对多

方式一：按照查询嵌套处理

• Teacher接口

 //方式一：按结果嵌套查询
    Teacher getTeacher(@Param("tid") int id);

• TeacherMappeer.xml

  <!--第一种方式：按结果嵌套查询-->
    <select id="getTeacher" resultMap="st">
        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=#{tid}
    </select>

    <resultMap id="st"  type="Teacher">
        <result property="id" column="tid"/>
        <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>

• 测试类

 @Test
    public void test(){
        SqlSession sqlSession= MybatisUtils.getSqlSession();
        TeacherMapper teacherMapper=sqlSession.getMapper(TeacherMapper.class);
        Teacher teacher=teacherMapper.getTeacher(1);
        System.out.println(teacher);
        sqlSession.close();
    }

• 结果
  

方式二

• Teacher接口

//方式二
    Teacher getTeacher2(@Param("tid")int id);

TeacherMapper.xml

 <!--第二种-->
    <select id="getTeacher2" resultMap="st2" parameterType="int">
        select * from teacher where id=#{tid}
    </select>
    <resultMap id="st2" type="Teacher">
        <result property="id" column="id"/>
        <result property="name" column="name"/>
        <collection property="students" javaType="ArrayList" ofType="Student" select="getStudent" column="id">
            <!--这里的column就是将值通过property传递给getStudent这个select标签-->
        </collection>
    </resultMap>
    <select id="getStudent" resultType="Student" parameterType="int">
        select * from student where tid=#{tid}
    </select>

• 测试类

 @Test
    public void test2(){
        SqlSession sqlSession= MybatisUtils.getSqlSession();
        TeacherMapper teacherMapper=sqlSession.getMapper(TeacherMapper.class);
        Teacher teacher=teacherMapper.getTeacher2(1);
        System.out.println(teacher);
        sqlSession.close();
    }

• 结果

小结

对于resultMap，以我们的Teacher类为例子

<resultMap id="st"  type="Teacher">
    <result property="id" column="tid"/>
    <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>

通俗的说就是将column的值赋值给property 或传递给property
我们Teacher类中的属性有
id，name，List集合（约束：Studnet类型）
数据库中teacher表中的字段
id,name
数据库中student表中的字段
id，name，tid
我们使用resultMap目的是使Teacher类中的属性与我们数据库中的字段一一对应，在我们Teacher类中
List集合也要进行映射，也就是我们的Student类也需要与数据库中的字段进行对应
我们就要用collection标签
property=“students” 是Teacher类中的集合属性名
ofType=“Student” 指的是集合的约束类型(泛型的约束)
我们还需要对Student类中的属性进行映射，就是如下

   <collection property="students" ofType="Student">
            <result property="id" column="sid"/>
            <result property="name" column="sname"/>
            <result property="tid" column="tid"/>
        </collection>

总结来说就是层层递进式映射

对于javaType和ofType
javaType：一个 Java 类的完全限定名，或一个类型别名
ofType：泛型中的约束，例如List 中的Student

属性是一个对象使用association 是集合则使用collection