Mybatis—⑥多对一、一对多处理

最新推荐文章于 2022-07-28 19:21:14 发布

原创最新推荐文章于 2022-07-28 19:21:14 发布 · 179 阅读

0 ·

CC 4.0 BY-SA版权

Java框架专栏收录该内容

23 篇文章

订阅专栏

本文详细介绍了在MyBatis框架中如何实现多对一和一对多的关联查询，包括实体类设计、DAO接口定义、Mapper文件配置、测试方法编写等关键步骤。通过具体案例展示了如何使用联表查询和面向对象思想来处理复杂的数据关系。

多对一

多个对象对应一个对象
首先，准备好环境，创建老师表和学生表

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


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

然后给分别插入信息
在这里插入图片描述

给老师和学生创建实体类
Teacher.java

package com.an.pojo;

public class Teacher {
    private int id;
    private String name;

    public Teacher() {
    }

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

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

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

Student.java

package com.an.pojo;

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 +
                '}';
    }
}

多对一处理方式1

使用数据库的思想处理：联表查询

1、定义dao口 StudentDao.java

public interface StudentDao {
    //查询学生的所有信息 方式1
    public abstract List<Student> getStudents();
}

2、对应的mapper编写 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.an.dao.StudentDao">
    <select id="getStudents" resultMap="StudentTeacher">
        select * from mybatis.student
    </select>
    <resultMap id="StudentTeacher" type="Student">
    <!--属性和字段对应  ， 类和表对应  ， 对象和记录
    关联一个字段
    需求：拿到老师这个类的属性
    association ： 关联，多对一
        column ： 数据库对应的列名
        property ： 对应属性名
        javaType ： 多对一字段对应的Java类型
        select ： 关联一个语句
    -->
        <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"></association>
    </resultMap>
    <select id="getTeacher" resultType="Teacher">
        select * from mybatis.teacher where id = #{id}
    </select>
</mapper>

3、编写工具类

public class MyBatisUtils {
    private static SqlSessionFactory sqlSessionFactory;
    static {
        try {
            String resource = "mybatis.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    public static SqlSessionFactory getSqlSessionFactory(){
        return sqlSessionFactory;
    }

}

4、在核心映射文件mybatis.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="database.properties"/>
    <settings>
        <setting name="logImpl" value="LOG4J"/>
    </settings>

    <!--为一个包里所有类取别名为类名,注意位置为固定的-->
   <typeAliases>
        <package name="com.an.pojo"/>
    </typeAliases>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <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 resource="com/an/dao/StudentMapper.xml"/>
    </mappers>
</configuration>

5、编写测试方法

	@Test
    public void getStudents() {
       SqlSessionFactory sqlSessionFactory = MyBatisUtils.getSqlSessionFactory();
       SqlSession sqlSession = sqlSessionFactory.openSession();
       StudentDao mapper = sqlSession.getMapper(StudentDao.class);
       List<Student> students = mapper.getStudents();
        for (Student student : students) {
            System.out.println(student);
        }
    }

多对一处理方式2

1、定义dao口 StudentDao.java

public interface StudentDao {
    public abstract List<Student> getStudents2();
}

2、对应的mapper编写 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.an.dao.StudentDao">
<!--一个resultMap解决 ， 模拟面向对象的思想-->
    <select id="getStudents2" resultMap="StudentTeacher2">
        select s.id,s.name,t.id as tid,t.name as tname from mybatis.student as s,mybatis.teacher as t
        where s.tid = t.id
    </select>
   
<!--设置结果集映射ResultMap -->
    <resultMap id="StudentTeacher2" type="Student">
        <id column="id" property="id"></id>
        <result column="name" property="name"></result>
        <!--直接关联一个老师-->
        <association property="teacher" javaType="Teacher">
            <id column="tid" property="id"/>
            <result column="tname" property="name"></result>
        </association>
    </resultMap>
</mapper>

3、4、工具类、在核心映射文件mybatis.xml中配置同上
5、编写测试方法

@Test
    public void getStudents2() {
        SqlSessionFactory sqlSessionFactory = MyBatisUtils.getSqlSessionFactory();
        SqlSession sqlSession = sqlSessionFactory.openSession();
        StudentDao mapper = sqlSession.getMapper(StudentDao.class);
        List<Student> students = mapper.getStudents2();
        for (Student student : students) {
            System.out.println(student);
        }
    }

一对多

一个对象对应多个对象
1、首先是学生和老师的实体类
Teacher.java

package com.an.pojo;

import java.util.List;

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 List<Student> getStudents() {
        return students;
    }

    public void setStudents(List<Student> students) {
        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;
    }

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

Student.java

package com.an.pojo;

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

    public Student() {
    }

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

    public Teacher getTeacher() {
        return teacher;
    }

    public void setTeacher(Teacher teacher) {
        this.teacher = teacher;
    }

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

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

2、编写接口TeacherDao，这里我们用两种查询方式

package com.an.dao;

import com.an.pojo.Teacher;

public interface TeacherDao {
    //根据id查询老师
    //方式1
    Teacher getTeacher(int id);
    //方式2
    Teacher getTeacher2(int id);
}

3、编写对应的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.an.dao.TeacherDao">
    <!--方式1-->
    <select id="getTeacher" resultMap="TeacherStudent">
        select t.id tid,t.name tname,s.id sid,s.name sname from mybatis.teacher as t,mybatis.student as s where s.tid = t.id and t.id=#{id}
    </select>
    <resultMap id="TeacherStudent" type="Teacher">
        <id column="tid" property="id"/>
        <result column="tname" property="name" />
        <collection property="students" ofType="Student">
            <id column="sid" property="id"/>
            <result column="sname" property="name"/>
        </collection>
    </resultMap>


    <!--方式2-->
    <select id="getTeacher2" resultMap="TeacherStudent2">
        select * from mybatis.teacher
    </select>
    <resultMap id="TeacherStudent2" type="teacher">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <!--属性和字段对应  ， 类和表对应  ， 对象和记录
    关联一个字段
    需求：拿到老师这个类的属性
    association ： 关联，多对一
        column ： 数据库对应的列名
        property ： 对应属性名
        javaType ： 多对一字段对应的Java类型
        select ： 关联一个语句
    -->
        <collection property="students" column="id" javaType="ArrayList" ofType="Student" select="getStudents"/>
    </resultMap>
    <select id="getStudents" resultType="Student">
        select * from mybatis.student where tid=#{id}
    </select>
</mapper>

4、在核心映射文件mybatis.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="database.properties"/>
    <settings>
        <setting name="logImpl" value="LOG4J"/>
    </settings>

    <!--为一个包里所有类取别名为类名,注意位置为固定的-->
   <typeAliases>
        <package name="com.an.pojo"/>
    </typeAliases>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <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 resource="com/an/dao/TeacherMapper.xml"/>
    </mappers>
</configuration>

5、测试

package com.an.dao;

import com.an.pojo.Teacher;
import com.an.utils.MyBatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.junit.Test;

public class TeacherMapperTest {
    @Test
    public void getTeacher(){
        SqlSessionFactory factory = MyBatisUtils.getSqlSessionFactory();
        SqlSession sqlSession = factory.openSession();
        TeacherDao mapper = sqlSession.getMapper(TeacherDao.class);
        Teacher teacher = mapper.getTeacher(1);
        System.out.println(teacher);
    }

    @Test
    public void getTeacher2(){
        SqlSessionFactory factory = MyBatisUtils.getSqlSessionFactory();
        SqlSession sqlSession = factory.openSession();
        TeacherDao mapper = sqlSession.getMapper(TeacherDao.class);
        Teacher teacher2 = mapper.getTeacher2(1);
        System.out.println(teacher2);
    }
}