2022.5.15 多对一处理与一对多处理

10、多对一处理

 1  CREATE TABLE `teacher` (
 2    `id` INT(10) NOT NULL,
 3    `name` VARCHAR(30) DEFAULT NULL,
 4    PRIMARY KEY (`id`)
 5  ) ENGINE=INNODB DEFAULT CHARSET=utf8
 6  
 7  INSERT INTO teacher(`id`, `name`) VALUES (1, '秦老师'); 
 8  
 9  CREATE TABLE `student` (
10    `id` INT(10) NOT NULL,
11    `name` VARCHAR(30) DEFAULT NULL,
12    `tid` INT(10) DEFAULT NULL,
13    PRIMARY KEY (`id`),
14      -- 索引tid列
15    KEY `fktid` (`tid`),
16      -- CONSTRAINT：约束重命名  将FOREIGN KEY (`tid`) REFERENCES `teacher` (`id`)这个约束命名为`fktid`
17    CONSTRAINT `fktid` FOREIGN KEY (`tid`) REFERENCES `teacher` (`id`)
18  ) ENGINE=INNODB DEFAULT CHARSET=utf8
19  INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('1', '小明', '1'); 
20  INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('2', '小红', '1'); 
21  INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('3', '小张', '1'); 
22  INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('4', '小李', '1'); 
23  INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('5', '小王', '1');

mybatis-config.xml

 1  <?xml version="1.0" encoding="UTF-8" ?>
 2  <!DOCTYPE configuration
 3          PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
 4          "http://mybatis.org/dtd/mybatis-3-config.dtd">
 5  
 6  <configuration>
 7  
 8      <!--引入外部配置文件-->
 9      <properties resource="db.properties">
10          <property name="username" value="root"/>
11          <property name="pwd" value="123456"/>
12      </properties>
13  <!--    设置日志 value 日志类型-->
14      <settings>
15          <setting name="logImpl" value="STDOUT_LOGGING"/>
16      </settings>
17  <!--    可以给实体类起别名-->
18      <typeAliases>
19          <package name="com.xing.pojo"/>
20      </typeAliases>
21  
22      <environments default="development">
23          <environment id="development">
24              <transactionManager type="JDBC"/>
25              <dataSource type="POOLED">
26                  <property name="driver" value="${driver}"/>
27                  <property name="url" value="${url}"/>
28                  <property name="username" value="${username}"/>
29                  <property name="password" value="${pwd}"/>
30              </dataSource>
31          </environment>
32  
33      </environments>
34  
35      <mappers>
36          <mapper class="com.xing.dao.TeacherMapper"/>
37          <mapper class="com.xing.dao.StudentMapper"/>
38      </mappers>
39  
40  
41  </configuration>

db.properties

1  driver=com.mysql.jdbc.Driver
2  url=jdbc:mysql://localhost:3306/mybatis?useSSL=true&useUnicode=true&characterEncoding=UTF-8

MybatisUtils

 1  package com.xing.utils;
 2  
 3  import org.apache.ibatis.io.Resources;
 4  import org.apache.ibatis.session.SqlSession;
 5  import org.apache.ibatis.session.SqlSessionFactory;
 6  import org.apache.ibatis.session.SqlSessionFactoryBuilder;
 7  
 8  import java.io.IOException;
 9  import java.io.InputStream;
10  
11  public class MybatisUtils {
12  
13      private static SqlSessionFactory sqlSessionFactory;
14  
15      static{
16          try {
17              String resource = "mybatis-config.xml";
18              InputStream inputStream = Resources.getResourceAsStream(resource);//转变成流
19              sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
20          } catch (IOException e) {
21              e.printStackTrace();
22          }
23      }
24  
25      public static SqlSession getSqlSession() {
26          //参数为true设置自动提交
27          return sqlSessionFactory.openSession(true);
28  
29      }
30  
31  
32  }

Student

 1  package com.xing.pojo;
 2  
 3  import lombok.Data;
 4  
 5  @Data
 6  public class Student {
 7      private int id;
 8      private String name;
 9      //学生关联一个老师
10      private Teacher teacher;
11  }

Teacher

 1  package com.xing.pojo;
 2  
 3  import lombok.Data;
 4  
 5  @Data
 6  public class Teacher {
 7      private int id;
 8      private String name;
 9  }
10

按照查询嵌套处理

多个学生对应一个老师

StudentMapper

 1  package com.xing.dao;
 2  
 3  import com.xing.pojo.Student;
 4  
 5  import java.util.List;
 6  
 7  public interface StudentMapper {
 8      //查询所有的学生以及对应的老师信息
 9      List<Student> getStudent();
10  }

TeacherMapper

 1  package com.xing.dao;
 2  
 3  import com.xing.pojo.Teacher;
 4  import org.apache.ibatis.annotations.Param;
 5  import org.apache.ibatis.annotations.Select;
 6  
 7  public interface TeacherMapper {
 8      @Select("select * from teacher where id=#{tid}")
 9      Teacher getTeacher(@Param("tid") int id);
10  }

StudentMapper.xml

 1  <?xml version="1.0" encoding="UTF-8" ?>
 2  <!DOCTYPE mapper
 3          PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
 4          "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
 5  
 6  <mapper namespace="com.xing.dao.StudentMapper">
 7  
 8      <!--思路:
 9      1.查询所有的学生信息
10      2．根据查询出来的学生的tid，寻找对应的老师!
11      -->
12  
13      <select id="getStudent" resultMap="st">
14          select * from mybatis.student;
15      </select>
16  
17      <resultMap id="st" type="Student">
18          <result property="id" column="id"/>
19          <result property="name" column="name"/>
20          <!--复杂的属性，我们需要单独处理  对象:association  集合:colLection
21          javaType:property参数对应的对象类型
22          select：对应select的id 将此id对应方法返回的值,传入tid列
23          -->
24          <association property="teacher" column="tid" javaType="Teacher" select="getT"/>
25      </resultMap>
26  
27      <select id="getT" resultType="Teacher">
28          select * from mybatis.teacher where id=#{tid};
29      </select> 
30  
31  </mapper>

TeacherMapper.xml

1  <?xml version="1.0" encoding="UTF-8" ?>
2  <!DOCTYPE mapper
3          PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
4          "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
5  
6  <mapper namespace="com.xing.dao.TeacherMapper">
7  
8  </mapper>

MyTest

 1 import com.xing.dao.StudentMapper;
 2  import com.xing.dao.TeacherMapper;
 3  import com.xing.pojo.Student;
 4  import com.xing.pojo.Teacher;
 5  import com.xing.utils.MybatisUtils;
 6  import org.apache.ibatis.session.SqlSession;
 7  import org.junit.Test;
 8  
 9  import java.util.List;
10  
11  public class MyTest {
12  
13      @Test
14      public void test1() {
15          SqlSession sqlSession = MybatisUtils.getSqlSession();
16          StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
17  
18          List<Student> student = mapper.getStudent();
19          for (Student student1 : student) {
20              System.out.println(student1);
21          }
22  
23          sqlSession.close();
24      }
25  
26  }

按照结果嵌套查询

一个老师对应多个学生

StudentMapper.xml

 1  <?xml version="1.0" encoding="UTF-8" ?>
 2  <!DOCTYPE mapper
 3          PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
 4          "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
 5  
 6  <mapper namespace="com.xing.dao.StudentMapper">
 7  
 8      <select id="getStudent2" resultMap="st2">
 9          #                         重命名
10          select s.id sid,s.name sname,t.name tname
11          from mybatis.student s,mybatis.teacher t
12          where s.tid = t.id;
13      </select>
14  
15      <resultMap id="st2" type="Student">
16          <result property="id" column="sid"/>
17          <result property="name" column="sname"/>
18          <association property="teacher" javaType="Teacher">
19              <!--Teacher类中的匹配   tname对应Teacher类-->
20              <result property="name" column="tname"/>
21          </association>
22      </resultMap>
23      
24  </mapper>

MyTest

 1 import com.xing.dao.StudentMapper;
 2  import com.xing.dao.TeacherMapper;
 3  import com.xing.pojo.Student;
 4  import com.xing.pojo.Teacher;
 5  import com.xing.utils.MybatisUtils;
 6  import org.apache.ibatis.session.SqlSession;
 7  import org.junit.Test;
 8  
 9  import java.util.List;
10  
11  public class MyTest {
12  
13      @Test
14      public void test2() {
15          SqlSession sqlSession = MybatisUtils.getSqlSession();
16          StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
17  
18          List<Student> student = mapper.getStudent2();
19          for (Student student1 : student) {
20              System.out.println(student1);
21          }
22  
23          sqlSession.close();
24      }
25  
26  }
27

回顾Mysql多对一查询方式:

子查询（嵌套查询）：按照查询嵌套处理
联表查询：按照结果嵌套查询

11、一对多处理

Student

 1  package com.xing.pojo;
 2  
 3  import lombok.Data;
 4  
 5  @Data
 6  public class Student {
 7      private int id;
 8      private String name;
 9      private int tid;
10  }

Teacher

 1  package com.xing.pojo;
 2  
 3  import lombok.Data;
 4  
 5  import java.util.List;
 6  
 7  @Data
 8  public class Teacher {
 9      private int id;
10      private String name;
11      
12      //一个老师拥有多个学生
13      private List<Student> students; 
14  }

TeacherMapper

 1  package com.xing.dao;
 2  
 3  import com.xing.pojo.Teacher;
 4  import org.apache.ibatis.annotations.Param;
 5  import org.apache.ibatis.annotations.Select;
 6  
 7  import java.util.List;
 8  
 9  public interface TeacherMapper {
10  
11      //获取指定老师下的所有学生及老师的信息
12      Teacher getTeacher(@Param("tercherid") int id);
13  }

TeacherMapper.xml

 1  <?xml version="1.0" encoding="UTF-8" ?>
 2  <!DOCTYPE mapper
 3          PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
 4          "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
 5  
 6  <mapper namespace="com.xing.dao.TeacherMapper">
 7  <!--    按结果嵌套查询-->
 8      <select id="getTeacher" resultMap="ts">
 9          select s.id sid,s.name sname,t.name tname,t.id tid
10          from mybatis.student s,mybatis.teacher t
11          where s.tid=t.id and t.id=#{tercherid}
12      </select>
13  
14      <resultMap id="ts" type="Teacher">
15          <result property="id" column="tid"/>
16          <result property="name" column="tname"/>
17          <!--复杂的属性，我们需要单独处理  对象:association 集合: collection
18          javaType="" 指定属性的类型!
19          集合中的泛型信息，我们使用ofType获取
20          -->
21  
22          <collection property="students" ofType="Student">
23              <result property="id" column="sid"/>
24              <result property="tid" column="tid"/>
25              <result property="name" column="sname"/>
26          </collection>
27      </resultMap>
28  </mapper>

test

 1  import com.xing.dao.StudentMapper;
 2  import com.xing.dao.TeacherMapper;
 3  import com.xing.pojo.Student;
 4  import com.xing.pojo.Teacher;
 5  import com.xing.utils.MybatisUtils;
 6  import org.apache.ibatis.session.SqlSession;
 7  import org.junit.Test;
 8  
 9  import java.util.List;
10  
11  public class MyTest {
12      @Test
13      public void test() {
14          SqlSession sqlSession = MybatisUtils.getSqlSession();
15          TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
16          Teacher teacher = mapper.getTeacher(1);
17  
18          System.out.println(teacher);
19  
20  
21          sqlSession.close();
22      }
23  }

法二：按照查询嵌套处理

TeacherMapper.xml

 1  <?xml version="1.0" encoding="UTF-8" ?>
 2  <!DOCTYPE mapper
 3          PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
 4          "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
 5  
 6  <mapper namespace="com.xing.dao.TeacherMapper">
 7  
 8  <!--    查询老师-->
 9      <select id="getTeacher" resultMap="ts2">
10          select * from mybatis.teacher where id = #{tercherid}
11      </select>
12  
13      <resultMap id="ts2" type="Teacher">
14          <collection property="students" javaType="ArrayList" ofType="Student" select="getStudentByTeacherid" column="id"/>
15      </resultMap>
16  <!--    根据老师的id查询学生-->
17      <select id="getStudentByTeacherid" resultType="Student">
18          select * from mybatis.student where tid = #{teacherid}
19      </select>
20  
21  </mapper>

小结

1.关联- association【多对一】

2.集合- collection【一对多】

javaType & ofType
- JavaType 用来指定实体类中属性的类型
- ofType 用来指定映射到List或者集合中的pojo类型，泛型中的约束类型!

面试高频

Mysql引擎

InnoDB底层原理

索引

索引优化!

posted @ 2022-05-19 00:45 暴躁C语言阅读(16) 评论(0) 收藏举报

刷新页面返回顶部

暴躁C语言

2022.5.15 多对一处理 与 一对多处理

10、多对一处理

按照查询嵌套处理

按照结果嵌套查询

11、一对多处理

小结

面试高频

公告

2022.5.15 多对一处理与一对多处理