一、分析
- 多个学生,对应一个老师
- 对于学生这边而言,关联(association ):多个学生关联一个老师【多对一】
- 对于老师而言,集合(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,"叶老师");
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_ciinsert 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);
三、搭配环境
1.导入lombok包
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.16</version>
</dependency>
2.建立实体类student、teacher
package ljg.com.pojo;
import lombok.*;
@Data
@AllArgsConstructor
@NoArgsConstructor
@Setter //注解在属性上。为属性提供 setting 方法。
@Getter
public class Student {
private int id;
private String name;
private int age;
}
package ljg.com.pojo;
import lombok.*;
import java.util.List;
@Data
@AllArgsConstructor
@NoArgsConstructor
@Setter
@Getter
public class Teacher {
private int id;
private String name;
}
3.建立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 {
// mybatis注解 @Select(sql语句):实现查询
@Select("select * from student where id = #{sid}")
//@Param是Mybatis中的一个常用注解,该注解标注在dao接口中的方法参数上,用来简化xml配置的时候(比如Mybatis的Mapper.xml中的sql参数注入),
// @Param注解的作用是给参数命名,参数命名后就可以通过 #{xxx} 的形式注入sql语句中(xxx为@Param给参数设置的名称)。@Param注解是为了dao接口
// 的方法参数和配置文件sql语句的参数保持变量名的一致性。
//简单来说就是:该注解用在dao层,用来给参数命名,在Mybatis的mapper中加上该注解,传递的参数与Sql中的字段名需要一致。
Student getStudent(@Param("sid") int id);
}
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);
}
4.建立mapper.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">
</mapper>
<?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">
</mapper>
5.在配置文件中加入我们的mapper接口
<?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="db.properties"/>
<!--标准的日志工厂的实现-->
<settings>
<setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
<typeAliases>
<package name="ljg.com.pojo"/>
</typeAliases>
<environments default="ljgdb">
<environment id="ljgdb">
<transactionManager type="JDBC"></transactionManager>
<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的配置-->
<mappers>
<!--<package name="ljg.com.mapper"/>-->
<mapper class="ljg.com.mapper.TeacherMapper"/>
<mapper class="ljg.com.mapper.StudentMapper"/>
</mappers>
</configuration>
driver=com.mysql.cj.jdbc.Driver url=jdbc:mysql://localhost:3306/xsgl2 username=root password=123456
6.搭配环境测试
//环境测试
@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);
}
四、多对一
1..接口
public interface StudentMapper {
// 查询所有学生的信息,以及对应的教师信息
List<Student> getStudents();
List<Student> getStudents2();
}
2.实体类中的属性类型
public class Student {
private int id;
private String name;
private int age;
//学生需要关联一个老师
private Teacher teacher;
}
3.在xml中写sql语句
<?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,s.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"/>
<!--javaType:用来指定实体类中属性的类型-->
<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"/>
<!--复杂的属性需要单独处理,对象(老师):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>
4.测试类
//方法一 查询嵌套处理
@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);
}
}
五、一对多
1.搭配环境
第一步接口
public interface TeacherMapper {
@Select("select * from teacher where id = #{tid}")
Teacher getTeacher(@Param("tid") int id);
//测试
List<Teacher> getTeacher3();
}
第二步在TeacherMapper.xml中
<select id="getTeacher3" resultType="Teacher">
select * from xsgl2.teacher;
</select>
第三步测试
//环境测试
@Test
public void getTeacher3(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
for (Teacher teacher :sqlSession.getMapper(TeacherMapper.class).getTeacher3()){
System.out.println(teacher);
}
sqlSession.close();
}
}
2.一对多的两种方法
public class Student { private int id; private String name; private int age; //一对多,一个老师拥有多个学生 //只有一个老师 private int tid; }
3.在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="getTeacher1" resultMap="TeacherStudent">
select s.id as sid, s.name as sname,s.age as sage,t.id as tid,t.name as 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"/>
<!-- 如果是集合 用 collection
<collection property="students" 集合属性(多个学生)
ofType="Student" 集合类型数据Student >
<result property="id" column="sid"/> 学生的ID
<result property="name" column="sname"/>
<result property="age" column="sage"/>
<result property="tid" column="tid"/>
</collection>
-->
<!--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>
4.测试
//查询一个老师对应多个学生
//结果嵌套处理
@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();
}
六、总结
在多对一中StudentMapper.xml中实行业务实现
1. 查询所有的学生
2. 用查收到学生的tid 查找老师,必须要在这有个查老师的方法
3. 讲两个查询连接起来,连接方法使用resultMap建立复杂的映射关系
复杂的属性用特殊处理
如果是对象 用
<association property="teacher" 学生类的属性是teacher 对象
javaType="Teacher"> 数据类型是Teacher
<result property="name" column="tname" />
教师的姓名是name 在查询中是tanme
</association>
如果是集合 用 <collection property="uid" column="uid"/>
在一对多中TeacherMapper.xml中实行业务实现
1. 查询 指定的教师
2. 使用resultMap建立复杂的映射关系
复杂的属性用特殊处理
如果是对象 用association
如果是集合 用 collection
<collection property="students" 集合属性(多个学生)
ofType="Student" 集合类型数据Student >
<result property="id" column="sid"/> 学生的ID
<result property="name" column="sname"/>
<result property="age" column="sage"/>
<result property="tid" column="tid"/>
</collection>
关联 ---association : 【多对一】 使用
集合---collection : 【一对多】使用
javaType 用来指定实体类中属性的类型
ofType 用来指定映射到List或集合中的pojo类型,就是范型中的约束类型数据。
吾日三省吾身:
你成功的速度一定要快于父母老去的速度,因为父母真的希望看到你成功所以你得努力。我们还年轻,还能扛得住。