多对一和一对多的关系说明：以多个学生对一位老师为例
1. 对学生这方面说 ： 使用关键"关联" association 就是多个学生关联一个老师 -----------多对一
2. 对老师来说： 使用关键字“集合” collection， 就是 一个老师有很多学生 （集合）---------一对多
一、创建数据库
create table teacher(
id int not null primary key ,
name varchar(50) not null
)ENGINE =INNODB DEFAULT CHARSET=utf8;
insert into teacher(id,name) values (1001,"刘老师"),(1002,"叶老师")，(1003,"张老师");
create table student(
id int not null primary key,
name varchar(50) not null,
age int not null ,
tid int default null,
constraint `fk` foreign key (`tid`) references `teacher`(`id`)
)ENGINE =INNODB DEFAULT CHARSET=utf8 collate utf8_general_ci
insert into student(id,name,age,tid) values (1201,"王冬雪",20,1001),
(1202,"好平平",19,1001),
(1203,"刘美玲",18,1001),
(1204,"王海华",32,1001),
(1205,"张李",34,1001),
(1206,"江风",45,1001),
(1207,"任丽",67,1001);
insert into student(id,name,age,tid) values (1201,"王冬雪",20,1001),
(1202,"好平平",19,1001),
(1203,"刘美玲",18,1001),
(1204,"王海华",32,1001),
(1205,"张李",34,1001),
(1206,"江风",45,1001),
(1207,"任丽",67,1001);
insert into student(id,name,age,tid) values (1301,"李小龙",20,1002),
(1302,"王美玲",19,1002),
(1303,"和小",18,1002),
(1304,"王丹丹",32,1002),
(1305,"厚天天",34,1002),
(1306,"李大明",45,1002),
(1307,"李铁",43,1002);
insert into student(id,name,age,tid) values (1401,"王小妹",20,1003),
(1402,"李大牛",19,1003),
(1403,"杨杨",18,1003),
(1404,"王二小",32,1003),
(1405,"李芬丽",34,1003),
(1406,"牛小同",45,1003),
(1407,"和小明",23,1003);二、多对一和一对多
第一步：导包
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.16</version>
</dependency>第二步：创建实体类Teacher、Student
Teacher
package ljg.com.pojo;
import lombok.*;
import java.util.List;
@Data
@AllArgsConstructor
@NoArgsConstructor
@Setter
@Getter
public class Teacher {
private int id;
private String name;
// 一个老师拥有多个学生
private List<Student> students;
}
Student
package ljg.com.pojo;
import lombok.*;
@Data//提高代码的简洁，可以省去实体类中大量的get()、set()、toString()等方法
@AllArgsConstructor//注解版全参
@NoArgsConstructor//注解版无参
@Setter
@Getter
public class Student {
private int id;
private String name;
private int age;
//学生需要关联一个老师 多对一
private Teacher teacher;
//一对多，一个老师拥有多个学生
//只有一个老师
private int tid;
}
第三步：建立mapper接口
package ljg.com.mapper;
import ljg.com.pojo.Student;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import java.util.List;
public interface StudentMapper {
//@Select mybatis注解 用于映射sql语句
@Select("select * from student where id = #{sid}")
// @Param的作用就是给参数命名，
// 比如在mapper里面某方法A（int id），
// 当添加注解后A（@Param("sid") int id），
// 也就是说外部想要取出传入的id值，
// 只需要取它的参数名sid就可以了。
// 将参数值传如SQL语句中，通过#{sid}进行取值给SQL的参数赋值。
Student getStudent(@Param("sid") int id);
List<Student> getStudents();
List<Student> getStudents2();
}
package ljg.com.mapper;
import ljg.com.pojo.Teacher;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import java.util.List;
public interface TeacherMapper {
@Select("select * from teacher where id = #{tid}")
Teacher getTeacher(@Param("tid") int id);
//测试
List<Teacher> getTeacher3();
// 获取一个老师 获取指定老师下的所有及老师的信息
Teacher getTeacher1(@Param("tid") int id);
// 子查询
Teacher getTeacher2(@Param("tid") int id);
}
第四步：建立Mapper.xml文件
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="ljg.com.mapper.StudentMapper">
<!--按照结果嵌套处理 联表查询-->
<select id="getStudents2" resultMap="StudentTeacher2" >
select s.id as sid,s.name as sname,age as sage,t.name as tname
from student s,teacher t
where s.tid=t.id
</select>
<resultMap id="StudentTeacher2" type="Student">
<!--学生的属性id对应 sid 一一对应-->
<result property="id" column="sid"/>
<result property="name" column="sname"/>
<result property="age" column="sage"/>
<association property="teacher" javaType="Teacher">
<!--老师是个复杂类型 结果处理完了 再去映射老师里面对应的字段-->
<result property="name" column="tname"/>
</association>
</resultMap>
<!--/
1.查询所有的学生信息
2.根据查询出来的学生的tid，寻找对应的老师
-->
<!-- 按照查询嵌套处理-->
<select id="getStudents" resultMap="StudentTeacher">
select * from student
</select>
<!--结果集映射将两个关联起来，解决属性名和字段名不一致的问题-->
<resultMap id="StudentTeacher" type="Student">
<!--主键，一一对应-->
<result column="id" property="id"/>
<result column="name" property="name"/>
<result column="age" property="age"/>
<!-- //property参数类型名
//column字段名
//javaType实体类中属性的类型
//select 嵌套语句-->
<!--复杂的属性需要单独处理，对象（老师）：association javaType：用来指定实体类中属性的类型，select在进行嵌套查询-->
<association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
</resultMap>
<!--tid 下面id没必要一一对应，mybatis会自行去推断-->
<select id="getTeacher" resultType="Teacher">
select * from Teacher where id=#{tid}
</select>
</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="ljg.com.mapper.TeacherMapper">
<select id="getTeacher3" resultType="Teacher">
select * from xsgl2.teacher;
</select>
<!--按照结果嵌套-->
<select id="getTeacher1" resultMap="TeacherStudent">
select s.id sid, s.name sname,s.age sage,t.id tid,t.name tname
from teacher t ,student s
where t.id=s.tid and t.id=#{tid}
</select>
<resultMap id="TeacherStudent" type="Teacher">
<result property="id" column="tid"/>
<result property="name" column="tname"/>
<!--javaType时属性的类型，集合中的泛型信息，我们使用ofType获取-->
<!--不用写javaType因为直接取出来的每一个值，然后去一一对应-->
<collection property="students" ofType="Student">
<result property="id" column="sid"/>
<result property="name" column="sname"/>
<result property="age" column="sage"/>
<result property="tid" column="tid"/>
</collection>
</resultMap>
<!--查询嵌套处理-->
<select id="getTeacher2" resultMap="TeacherStuden2">
select * from xsgl2.teacher where id =#{tid}
</select>
<resultMap id="TeacherStuden2" type="Teacher">
<!--
private List<Student> students;
list 是Java类型 用 javaType="ArrayList"
<Student> 是范型数据 用 ofType="Student
select="getByTidOfStudents" 根据老师的id查询对应的学生
tid=#{tid} 这个结果就是老师的ID
-->
<!--本来就是个集合 集合对象和泛型信息写出来-->
<collection property="students" javaType="ArrayList" ofType="Student"
select="getByTidOfStudents" column="id"/>
</resultMap>
<!--两个语句查询再通过子查询去查一次getByTidOfStudents-->
<select id="getByTidOfStudents" resultType="Student">
select * from xsgl2.student where tid=#{tid}
</select>
</mapper>说明：上述代码详细说明了多对一的两种方法，分别是查询嵌套处理和结果嵌套处理。
第五步：测试类
package ljg.com.test;
import ljg.com.mapper.StudentMapper;
import ljg.com.mapper.TeacherMapper;
import ljg.com.pojo.Student;
import ljg.com.pojo.Teacher;
import ljg.com.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.jupiter.api.Test;
import java.util.List;
public class YouTest {
//搭配环境
@Test
public void getteacher(){
SqlSession salSession = MybatisUtils.getSqlSession();
StudentMapper mapper = salSession.getMapper(StudentMapper.class);
Student student = mapper.getStudent(1201);
System.out.println(student);
TeacherMapper mapper1 = salSession.getMapper(TeacherMapper.class);
Teacher teacher = mapper1.getTeacher(1001);
System.out.println(teacher);
}
//多对一
// 第一种方法 查询嵌套处理
@Test
public void getstudent(){
SqlSession salSession = MybatisUtils.getSqlSession();
StudentMapper mapper = salSession.getMapper(StudentMapper.class);
List<Student> students = mapper.getStudents();
for (Student student : students) {
System.out.println(student);
}
}
//第二种方法 结果嵌套查询
@Test
public void getstudent1(){
SqlSession salSession = MybatisUtils.getSqlSession();
StudentMapper mapper = salSession.getMapper(StudentMapper.class);
List<Student> students = mapper.getStudents2();
for (Student student : students) {
System.out.println(student);
}
}
@Test
public void getTeacher1(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
Teacher teacher = mapper.getTeacher1(1002);
System.out.println(teacher);
sqlSession.close();
}
@Test
public void getTeacher2(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
Teacher teacher = mapper.getTeacher2(1002);
System.out.println(teacher);
sqlSession.close();
}
@Test
public void getTeacher3(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
for (Teacher teacher :sqlSession.getMapper(TeacherMapper.class).getTeacher3()){
System.out.println(teacher);
}
sqlSession.close();
}
}
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
