欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

SSM框架学习04——MyBatis关联数据查询

程序员文章站 2024-01-15 23:56:58
...

一、创建数据库表

1、创建教师表teacher

SSM框架学习04——MyBatis关联数据查询

CREATE TABLE `teacher` (
`t_id` int(11) NOT NULL AUTO_INCREMENT,
`t_name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`t_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
 
INSERT INTO `teacher` VALUES ('1', '刘晓云');
INSERT INTO `teacher` VALUES ('2', '郑同华');
INSERT INTO `teacher` VALUES ('3', '李明军');

2、创建班级表class

SSM框架学习04——MyBatis关联数据查询

CREATE TABLE `class` (
  `c_id` int(11) NOT NULL AUTO_INCREMENT,
  `c_name` varchar(20) DEFAULT NULL,
  `teacher_id` int(11) DEFAULT NULL,
PRIMARY KEY (`c_id`),
KEY `fk_teacher_id` (`teacher_id`),
CONSTRAINT `fk_teacher_id` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`t_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
 
INSERT INTO `class` VALUES ('1', '14软件1班', '3');
INSERT INTO `class` VALUES ('2', '13软件2班', '2');
INSERT INTO `class` VALUES ('3', '15软件1班', '1');

3、创建学生表student

SSM框架学习04——MyBatis关联数据查询

CREATE TABLE `student` (
  `s_id` int(11) NOT NULL AUTO_INCREMENT,
  `s_name` varchar(30) DEFAULT NULL,
  `s_gender` varchar(10) DEFAULT NULL,
  `s_age` int(11) DEFAULT NULL,
  `class_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`s_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
 
INSERT INTO `student` VALUES ('1', '佟大为', '男', '20', '1');
INSERT INTO `student` VALUES ('2', '李方玉', '女', '19', '1');
INSERT INTO `student` VALUES ('3', '郑大林', '男', '19', '2');
INSERT INTO `student` VALUES ('4', '温晓辉', '男', '18', '1');
INSERT INTO `student` VALUES ('5', '吴文静', '女', '19', '2');
INSERT INTO `student` VALUES ('6', '肖文艳', '女', '18', '3');
INSERT INTO `student` VALUES ('7', '杨文军', '男', '20', '3');
INSERT INTO `student` VALUES ('8', '唐雨涵', '女', '19', '2');
INSERT INTO `student` VALUES ('9', '金雨欣', '女', '20', '2');

二、创建实体类

1、教师实体类Teacher

SSM框架学习04——MyBatis关联数据查询

package net.cw.mybatis.bean;


public class Teacher {
    private int id;
    private String 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 + '\'' +
                '}';
    }
}

2、学生实体类Student

SSM框架学习04——MyBatis关联数据查询

package net.cw.mybatis.bean;

public class Student {
        private int id;
        private String name;
        private String gender;
        private int age;
        private Clazz clazz;

        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 String getGender() {
            return gender;
        }

        public void setGender(String gender) {
            this.gender = gender;
        }

        public int getAge() {
            return age;
        }

        public void setAge(int age) {
            this.age = age;
        }

        public Clazz getClazz() {
            return clazz;
        }

        public void setClazz(Clazz clazz) {
            this.clazz = clazz;
        }

        @Override
        public String toString() {
            return "Student{" +
                    "id=" + id +
                    ", name='" + name + '\'' +
                    ", gender='" + gender + '\'' +
                    ", age=" + age +
                    ", clazz=" + clazz.getName() +
                    '}';

    }
}

3、班级实体类Clazz

SSM框架学习04——MyBatis关联数据查询

package net.cw.mybatis.bean;


import java.util.List;

public class Clazz{
    private int id;
    private String name;
    private Teacher teacher;
    private List<Student> 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 Teacher getTeacher() {
        return teacher;
    }

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

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

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

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

三、配置和定义映射文件

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>
    <!--配置实体类别名-->
    <typeAliases>
        <typeAlias type="net.cw.mybatis.bean.User" alias="User"/>
        <typeAlias type="net.cw.mybatis.bean.Clazz" alias="Clazz"/>
        <typeAlias type="net.cw.mybatis.bean.Teacher" alias="Teacher"/>
        <typeAlias type="net.cw.mybatis.bean.Student" alias="Student"/>
    </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://localhost:3306/test_mybatis?characterEncoding=utf8"/>
                <property name="username" value="root"/>
                <property name="password" value="1"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper resource="mapper/UserMapper.xml"/>
        <mapper resource="mapper/ClazzMapper.xml"/>
    </mappers>
</configuration>

SSM框架学习04——MyBatis关联数据查询

2、定义班级映射文件ClazzMapper.xml

SSM框架学习04——MyBatis关联数据查询

<?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="net.cw.mybatis.mapper.ClazzMapper">
 
    <!--三表关联查询-->
    <select id="findById" parameterType="Clazz" resultMap="clazzResultMap">
        SELECT * FROM teacher INNER JOIN class ON class.teacher_id = teacher.t_id
        INNER JOIN student ON class.c_id = student.class_id WHERE c_id = #{id};
    </select>
 
    <resultMap id="clazzResultMap" type="Clazz">
        <result property="id" column="c_id"/>
        <result property="name" column="c_name"/>
        <!--一对一-->
        <association property="teacher" column="teacher_id" javaType="Teacher">
            <result property="id" column="t_id"/>
            <result property="name" column="t_name"/>
        </association>
        <!--一对多-->
        <collection property="students" ofType="Student">
            <result property="id" column="s_id"/>
            <result property="name" column="s_name"/>
            <result property="gender" column="s_gender"/>
            <result property="age" column="s_age"/>
            <association property="clazz" column="class_id" javaType="Clazz">
                <result property="name" column="c_name"/>
            </association>
        </collection>
    </resultMap>
 
    <!--###########################################################-->
 
    <!--通过嵌套查询实现一对一关联-->
    <select id="findAll" resultMap="clazzResultMap2">
        SELECT * FROM class;
    </select>
 
    <resultMap id="clazzResultMap2" type="Clazz">
        <result property="id" column="c_id"/>
        <result property="name" column="c_name"/>
        <association property="teacher" column="teacher_id" javaType="Teacher"
                     select="getTeacher"/>
    </resultMap>
 
    <select id="getTeacher" resultType="Teacher">
        SELECT t_id id, t_name name FROM teacher WHERE t_id = #{id};
    </select>
</mapper>

四、定义班级映射接口ClazzMapper

SSM框架学习04——MyBatis关联数据查询

SSM框架学习04——MyBatis关联数据查询

package net.cw.mybatis.mapper;

import net.cw.mybatis.bean.Clazz;

import java.util.List;

public interface ClazzMapper {
        Clazz findById(int id);
        List<Clazz> findAll();
}

五、创建测试程序TestClazzMapper

SSM框架学习04——MyBatis关联数据查询

package net.cw.mybatis.mapper;

import net.cw.mybatis.bean.Clazz;
import net.cw.mybatis.bean.Student;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.io.IOException;
import java.io.Reader;
import java.util.List;


public class TestClazzMapper {
        private SqlSession session;
        private ClazzMapper clazzMapper;

        @Before
        public void init() {
            try {
                Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
                SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader);
                session = factory.openSession();
                clazzMapper = session.getMapper(ClazzMapper.class);
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

        @Test
        public void testFindById() {
            Clazz clazz = clazzMapper.findById(1);
            System.out.println(clazz);

            List<Student> students = clazz.getStudents();
            for(Student student : students) {
                System.out.println(student);
            }

        }

        @Test
        public void testFindAll() {
            List<Clazz> clazzes = clazzMapper.findAll();
            for (Clazz clazz : clazzes) {
                System.out.println(clazz);
            }
        }

        @After
        public void destroy() {
            session.close();
    }
}

1、运行testFindById方法,查看结果

SSM框架学习04——MyBatis关联数据查询

2、运行testFindAll方法,查看结果

SSM框架学习04——MyBatis关联数据查询

相关标签: SSM框架学习