一、多对一:多个学生对应一个老师。学生属性中关联老师(对象)
数据库:
- 学生表:(tid是老师id)
- 老师表:
实体类:
package com.domain;
import lombok.Data;
@Data
public class Student {
private int id;
private String name;
//学生关联一个老师
private Teacher teacher;
}
package com.domain;
import lombok.Data;
@Data
public class Teacher {
private int id;
private String name;
}
Mapper接口:
- StudentMapper:
package com.dao;
import com.domain.Student;
import java.util.List;
public interface StudentMapper {
//查询所有学生和对应的老师
List<Student> getStudent();
}
- TeacherMapper:(TeacherMapper这里我用的是注解,也可以写相应的xml文件)
package com.dao;
import com.domain.Teacher;
import org.apache.ibatis.annotations.Select;
public interface TeacherMapper {
@Select("select * from teacher")
Teacher getTeachers();
}
核心配置文件:
<?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>
<!-- <typeAlias type="com.domain.User" alias="user"/>-->
<package name="com.domain"/>
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<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>
<!--指定mapper文件-->
<mappers>
<mapper class="com.dao.StudentMapper"/>
<mapper class="com.dao.TeacherMapper"/>
</mappers>
</configuration>
如果按原来的查询方式,即 StudentMapper.xml 文件中的sql为:
<?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.dao.StudentMapper">
<select id="getStudent" resultType="student">
select * from student
</select>
</mapper>
这样得到的结果中,Teacher属性为null,结果如下:
所以为了得到学生的所有信息,包括对应的老师,需要对 StudentMapper.xml 文件中的语句进行相应的处理。处理方式有两种。
方式一:按照结果嵌套处理,即直接查询出结果,再进行结果集的映射
<?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.dao.StudentMapper">
<!--
多对一的处理
要把多个学生及对应的老师给显示出来
-->
<select id="getStudents2" resultMap="studentTeacher2">
select s.id sid,s.name sname,t.id id, t.name tname from student s join teacher t on s.tid = t.id
</select>
<resultMap id="studentTeacher2" type="Student">
<result property="id" column="sid"/>
<result property="name" column="sname" />
<!--property中是学生的属性,类型是Teacher类型-->
<association property="teacher" javaType="Teacher">
<!--property中是老师的属性name,column是数据库查出来的字段-->
<result property="id" column="id"/>
<result property="name" column="tname"/>
</association>
</resultMap>
note:association和collection都用来关联属性,但是association是对对象进行操作,而collection是对集合进行操作!
方式二:按查询嵌套处理
1. 先查询所有的学生信息
2. 再根据查询出来的学生的tid,寻找对应的老师(子查询)
<?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.dao.StudentMapper">
<select id="getStudents" resultMap="studentTeacher">
select * from student;
</select>
<resultMap id="studentTeacher" type="Student">
<!--student中的属性-->
<result column="id" property="id"/>
<result column="name" property="name"/>
<!--
对象:association 集合:collection
javaType:把sql语句查询出的结果集封装给某个类的对象(可以省略)
select: 下一条要执行的sql语句(或者说当前属性是调用select指定的方法查出的结果)
property:实体类中的属性
column:数据库中对应的属性
-->
<association property="teacher" column="tid" javaType="Teacher" select="getTeachers"/>
</resultMap>
<select id="getTeachers" resultType="teacher">
select * from teacher where id = #{id}
</select>
</mapper>
测试类:
package com.dao;
import com.domain.Student;
import com.domain.Teacher;
import com.utils.MyBatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.List;
public class Mytest {
@Test
public void test(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
List<Student> studentList = mapper.getStudent();
for (Student student : studentList) {
System.out.println(student);
}
}
}
这两种方式都能得到想要的结果:
总结:
这两种方式都用到了Map映射,方式一的sql语句复杂一点,但是只要能把sql语句写出来,再做一个map映射就可以。方式二sql语句简单,但是进行映射时比较复杂,而且还要写出来查询teacher的sql语句。
二、一对多:一个老师拥有多个学生,这就成了老师属性中关联一群学生(集合)
实体类:
package com.domain;
import lombok.Data;
import java.util.List;
@Data
public class Teacher {
private int id;
private String name;
//一个老师拥有多个学生
private List<Student> students;
}
package com.domain;
import lombok.Data;
@Data
public class Student {
private int id;
private String name;
private int tid;
}
Mapper接口:
- StudentMapper:
package com.dao;
import com.domain.Student;
import org.apache.ibatis.annotations.Select;
import java.util.List;
public interface StudentMapper {
@Select("select * from student")
List<Student> getStudents();
}
- TeacherMapper:
package com.dao;
import com.domain.Teacher;
import org.apache.ibatis.annotations.Param;
public interface TeacherMapper {
//获取老师
// Teacher getTeacher();
//获取指定老师下的所有学生及老师信息
Teacher getTeacher(@Param("tid") int id);
}
核心配置文件: 和多对一的相同
<?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>
<!-- <typeAlias type="com.domain.User" alias="user"/>-->
<package name="com.domain"/>
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<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>
<mapper class="com.dao.StudentMapper"/>
<mapper class="com.dao.TeacherMapper"/>
</mappers>
</configuration>
Mapper.xml文件:
- StudentMapper.xml:因为用的是注解,所以不用配置它的xml文件
- 和一对多类似,TeacherMapper.xml的配置也有两种方式。
方式一:按结果嵌套查询
<?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.dao.TeacherMapper">
<!--按结果嵌套查询-->
<select id="getTeacher" resultMap="TeacherStudent">
select t.id tid ,t.name tname, s.name sname, s.id sid from teacher t join student s on t.id = s.tid where t.id = #{tid}
</select>
<resultMap id="TeacherStudent" type="teacher">
<result property="id" column="tid"/>
<result property="name" column="tname"/>
<!--老师中的学生属性是集合,用collection,
javaType和ofType都是用来指定对象类型的
JavaType是用来指定实体类pojo中属性的类型
ofType指定的是映射到list集合属性中pojo的类型。
-->
<collection property="students" javaType="ArrayList" ofType="Student">
<result property="name" column="sname"/>
<result property="id" column="sid"/>
</collection>
</resultMap>
</mapper>
方式二:按子查询
<?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.dao.TeacherMapper">
<!--按按查询嵌套处理-->
<select id="getTeacher2" resultMap="TeacherStudents">
select * from teacher where id = #{tid};
</select>
<resultMap id="TeacherStudents" type="teacher">
<!--column是一对多的外键 , 写的是一的主键的列名-->
<collection property="students" column="id" javaType="ArrayList" ofType="Student" select="getStudents"/>
</resultMap>
<select id="getStudents" resultType="Student">
select * from student where tid = #{tid}
</select>
</mapper>
测试:
@Test
public void testTeacherMapper(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
Teacher teacher = mapper.getTeacher(1);
System.out.println(teacher);
}
结果:
Teacher(id=1, name=村长, students=[Student(id=1, name=美羊羊, tid=0),
Student(id=2, name=红太狼, tid=0), Student(id=3, name=喜羊羊, tid=0),
Student(id=4, name=懒羊羊, tid=0), Student(id=5, name=灰太狼, tid=0)])
总结:
其实不管是一对多还是多对一,主要考察的还是sql语句,和map映射,掌握好这两个就好。