Mybatis一对多查询，多对一查询

最新推荐文章于 2022-10-07 15:29:09 发布

小白葫芦

最新推荐文章于 2022-10-07 15:29:09 发布

阅读量221

点赞数

本文链接：https://blog.csdn.net/qq_40791843/article/details/97135541

版权

多对一

比如说，我们很多个学生对应一个老师，这就是多对一。
要想实现多对一，首先得有两张表，如何在其中一张表中添加另外一章表的外键。
mysql提供的语句：

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

首先建立实体类

package com.baidu.pojo;

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

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

    public Student() {
    }

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

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

package com.baidu.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 + '\'' +
                '}';
    }
}

添加dao层代码

package com.baidu.dao;

import com.baidu.pojo.Student;

import java.util.List;

public interface StudentDao {
    //获得全部学生的信息，以及对应的老师
    List<Student> getStudents();

    List<Student> getStudents2();
}

添加映射文件

<?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.baidu.dao.StudentDao">
<!--方法一-->
    <select id="getStudents" resultMap="StudentTeacher">
        select * from mybatis.student
    </select>
    <resultMap id="StudentTeacher" type="Student">
        <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"></association>
    </resultMap>
    <select id="getTeacher" resultType="Teacher">
        select * from mybatis.teacher where id = #{id}
    </select>


    <!--解决方式二-->
    <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 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>

添加测试文件

package com.baidu.dao;

import com.baidu.pojo.Student;
import com.baidu.utils.MyBatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;

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

一对多

配置pojo层代码

package com.baidu.pojo;

import java.util.List;

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

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

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

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

配置dao层代码

Teacher getTeacher(int id);

配置映射文件

<?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.baidu.dao.TeacherDao">
    <select id="getTeacher" resultMap="TeacherStudent">
        select s.id as sid,t.id as tid,s.name as sname,t.name as tname
        from mybatis.student as s,mybatis.teacher as t
        where s.tid = t.id and t.id = #{id}
    </select>
    <resultMap id="TeacherStudent" type="Teacher">
        <id column="tid" property="id"></id>
        <result column="tname" property="name"></result>
        <!--一对多-->
        <collection property="students" ofType="Student">
            <id column="sid" property="id"></id>
            <result column="sname" property="name"></result>
        </collection>
    </resultMap>


</mapper>

测试

package com.baidu;

import com.baidu.dao.TeacherDao;
import com.baidu.pojo.Teacher;
import com.baidu.utils.MyBatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;


public class TeacherDaoTest {
    @Test
    public  void getTeacher(){
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        TeacherDao mapper = sqlSession.getMapper(TeacherDao.class);
        Teacher teacher = mapper.getTeacher(1);
        System.out.println(teacher);
    }
}

在这里插入图片描述
第二种方法

配置dao层文件

Teacher getTeacherTwo(int id);

配置映射文件

<select id="getTeacherTwo" resultMap="TeacherStudent2">
        select * from mybatis.teacher where id = #{id}
    </select>
    <resultMap id="TeacherStudent2" type="Teacher">
        <collection property="students" javaType="ArrayList" column="id" ofType="Student" select="T2"></collection>
    </resultMap>
    <select id="T2" resultType="Student">
        select * from mybatis.student where tid = #{id}
    </select>

测试文件

@Test
    public  void getTeacher2(){
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        TeacherDao mapper = sqlSession.getMapper(TeacherDao.class);
        Teacher teacher = mapper.getTeacherTwo(1);
        System.out.println(teacher);
    }

在这里插入图片描述

动态SQL查询

<delete id="removeRoleById" parameterType="Role">
        delete from smbms.smbms_role
        <where>
            <if test="id != ''">
                id = #{id}
            </if>
        </where>
    </delete>

    <update id="updateRole" parameterType="role">
        update smbms.smbms_role
        <trim prefix="set" suffixOverrides="," suffix="where id = #{id}">
            <if test="roleCode != ''">roleCode = #{roleCode},</if>
            <if test="roleName != ''">roleName = #{roleName},</if>
            <if test="createdBy != ''">createdBy = #{createdBy},</if>
            <if test="creationDate != ''">creationDate = #{creationDate},</if>
            <if test="modifyBy != ''">modifyBy = #{modifyBy},</if>
            <if test="modifyDate != ''">modifyDate = #{modifyDate},</if>
        </trim>
    </update>

小白葫芦

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Mybatis一对多查询，多对一查询

多对一比如说，我们很多个学生对应一个老师，这就是多对一。要想实现多对一，首先得有两张表，如何在其中一张表中添加另外一章表的外键。mysql提供的语句：CREATE TABLE `teacher` ( `id` INT(10) NOT NULL, `name` VARCHAR(30) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=INNODB...
复制链接

扫一扫