(2).处理问题一 在学生表查询的时候为Null
(1).按照查询嵌套处理(子查询)
思路: 1.先查询所有的学生信息.2.
按照查询出来的学生的tid,寻找对应的老师。
复杂的属性,我们需要单独使用 对象: association 集合: collection
配置文件
<?xml version="1.0" encoding="UTF8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "https://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="Com.Jsxs.Dao.StudentMapper"> <!-- select *from student s,teacher t where s.tid=t.id;--> <select id="getStudent_Teacher" resultMap="Student_Teacher"> select *from student ; </select> <resultMap id="Student_Teacher" type="Com.Jsxs.pojo.Student"> <result property="id" column="id"/> <result property="name" column="name"/> <!-- 复杂的属性,我们需要单独使用 对象: association 集合: collection--> <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/> </resultMap> <select id="getTeacher" resultType="Com.Jsxs.pojo.Teacher"> select *from teacher where id=#{tid}; </select> </mapper>
(2).按照结果进行查询
Sql
select *from student s,teacher t where s.tid=t.id;
<select id="getStudent_Teacher2" resultMap="getStudent_Teacher2"> select s.id sid, s.name sname,t.name tname from student s,teacher t where s.tid=t.id; </select> <resultMap id="getStudent_Teacher2" type="Com.Jsxs.pojo.Student"> <result property="id" column="sid"/> <result property="name" column="sname"/> <association property="teacher" javaType="Teacher"> <result property="name" column="tname"/> </association> </resultMap>
(十七)、一对多处理(集合)
1.基本设置
老师实体类的设置
package Com.Jsxs.pojo; import lombok.AllArgsConstructor; import lombok.Data; import lombok.NoArgsConstructor; import java.util.List; @Data @AllArgsConstructor @NoArgsConstructor public class Teacher { private int id; private String name; //一个老师拥有多个学生 private List<Student> students; }
学生实体类的设置
package Com.Jsxs.pojo; import lombok.AllArgsConstructor; import lombok.Data; import lombok.NoArgsConstructor; @Data @AllArgsConstructor @NoArgsConstructor public class Student { private int id; private String name; private int tid; }
2.问题出现:在老师表中查询对应的学生
接口设置
package Com.Jsxs.Dao; import Com.Jsxs.pojo.Teacher; import org.apache.ibatis.annotations.Param; import org.apache.ibatis.annotations.Select; import java.util.List; public interface TeacherMapper { // 获取指定老师下的所有学生 Teacher getTeacher(@Param("ttid") int id); }
配置设置
<?xml version="1.0" encoding="UTF8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "https://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="Com.Jsxs.Dao.TeacherMapper"> <!-- 按揭或--> <select id="getTeacher" resultType="Com.Jsxs.pojo.Teacher"> select s.id sid, s.name sname, t.name tname, t.id tid from student s,teacher t where s.id=t.id and t.id=#{ttid}; </select> </mapper>
测试
package Com.Jsxs.Dao; import Com.Jsxs.pojo.Teacher; import Com.Jsxs.utils.MybatisUtils; import org.apache.ibatis.session.SqlSession; import org.junit.Test; import java.util.List; public class TeacherMapperTest { @Test public void test_getTeacher(){ SqlSession sqlSession = MybatisUtils.getSqlSession(); TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class); Teacher teacher = mapper.getTeacher(1); System.out.println(teacher); sqlSession.close(); } }
结果为null
3.处理问题
(1).按结果查询
集合用 collection ; javaType="" 指定属性的类型; 集合中的泛型信息我们用ofType获取
SQL语句
select s.id sid, s.name sname, t.name tname, t.id tid from student s,teacher t where s.tid=t.id and t.id=#{ttid};
配置文件
<?xml version="1.0" encoding="UTF8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "https://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="Com.Jsxs.Dao.TeacherMapper"> <!-- 按结果进行查询--> <select id="getTeacher" resultMap="Teacher_student"> select s.id sid, s.name sname, t.name tname, t.id tid from student s,teacher t where s.tid=t.id and t.id=#{ttid}; </select> <resultMap id="Teacher_student" type="Com.Jsxs.pojo.Teacher"> <result property="id" column="tid"/> <result property="name" column="tname"/> <!-- 集合用 collection ; javaType="" 指定属性的类型; 集合中的泛型信息我们用ofType获取 --> <collection property="students" ofType="Student"> <result property="id" column="sid"/> <result property="name" column="sname"/> <result property="tid" column="tid"/> </collection> </resultMap> </mapper>
(2).按子查询进行处理
<?xml version="1.0" encoding="UTF8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "https://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="Com.Jsxs.Dao.TeacherMapper"> <!-- 按结果进行查询--> <select id="getTeacher" resultMap="Teacher_student"> select s.id sid, s.name sname, t.name tname, t.id tid from student s,teacher t where s.tid=t.id and t.id=#{ttid}; </select> <resultMap id="Teacher_student" type="Com.Jsxs.pojo.Teacher"> <result property="id" column="tid"/> <result property="name" column="tname"/> <!-- 集合用 collection ; javaType="" 指定属性的类型; 集合中的泛型信息我们用ofType获取 --> <collection property="students" ofType="Student"> <result property="id" column="sid"/> <result property="name" column="sname"/> <result property="tid" column="tid"/> </collection> </resultMap> <!-- =========================-======按照嵌套查询==--> <select id="getTeacher2" resultMap="Teacher_student2"> <!-- 通过指定的老师id进行查询--> select *from teacher where id=#{ttid}; </select> <resultMap id="Teacher_student2" type="Com.Jsxs.pojo.Teacher"> <!-- 传回来查询到的老师的id--> <collection property="students" column="id" ofType="Student" select="getStudent"/> </resultMap> <select id="getStudent" resultType="Com.Jsxs.pojo.Student"> <!-- 老师的id和外键进行对比--> select *from student where tid=#{ttid}; </select> </mapper>
4.小结
1.关联–association 【多对一】
2.集合-- collection 【一对多】
3.javaType : 用来指定实体类中属性的类型
4.ofType : 用来指定映射到List或则集合中实体的类型,泛型中的约束类型