复杂查询：一对多，多对一

创建数据库：

//教师表 CREATE TABLE `teacher`( `id` INT(10) NOT NULL, `name` VARCHAR(30) DEFAULT NULL, PRIMARY KEY(`id`) )ENGINE=INNODB DEFAULT CHARSET=utf8 INSERT INTO `teacher` VALUES(1,'秦老师') //学生表 CREATE TABLE `student`( `id` INT(10) NOT NULL, `name` VARCHAR(30) DEFAULT NULL, `tid` INT(10) NOT NULL, PRIMARY KEY (`id`), CONSTRAINT `fktid` FOREIGN KEY (`tid`) REFERENCES `teacher` (`id`) )ENGINE=INNODB DEFAULT CHARSET=utf8 INSERT INTO `student` VALUES (1,'小明',1); INSERT INTO `student` VALUES (2,'小红',1); INSERT INTO `student` VALUES (3,'小张',1); INSERT INTO `student` VALUES (4,'小李',1); INSERT INTO `student` VALUES (5,'小王',1);

lombok

依赖：

<!-- https://mvnrepository.com/artifact/org.projectlombok/lombok --> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version>1.18.12</version> <!--<scope>provided</scope>--> </dependency> @Data：无参构造，get、set、tostring、hashcod、equals@AllArgsConstructor, ：有参构造@NoArgsConstructor：无参构造

@Getter and @Setter @FieldNameConstants @ToString @EqualsAndHashCode @RequiredArgsConstructor @Log, @Log4j, @Log4j2, @Slf4j, @XSlf4j, @CommonsLog, @JBossLog, @Flogger, @CustomLog

@Builder @SuperBuilder @Singular @Delegate @Value @Accessors @Wither @With @SneakyThrows @val @var experimental @var @UtilityClass Lombok config system

映射：

<mappers> <mapper resource="com/kuang/dao/UserMapper.xml"/> <mapper resource="com/kuang/dao/StudentMapper.xml"/> <mapper resource="com/kuang/dao/TeacherMapper.xml"/> </mappers>

注解查询：

public interface TeacherMapper { //@Param("tid")与#{tid}绑定，必须保证里面的名字一致。此是 int id只负责传参，参数名字无关联 @Select("select * from teacher where id =#{tid}") Teacher getTeacher(@Param("tid") int id); } public class MyTest { public static void main(String[] args) { SqlSession sqlSession = MybatisDao.getSqlSession(); TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class); Teacher teacher = mapper.getTeacher(1); System.out.println(teacher); sqlSession.close(); } }

多对一

teacher为null

//StudentMapper.xml <select id="getStudent" resultType="Student" > select s.id,s.name,t.id,t.name from student s,teacher t </select> //测试类： public class MyTest { public static void main(String[] args) { SqlSession sqlSession = MybatisDao.getSqlSession(); StudentMapper mapper = sqlSession.getMapper(StudentMapper.class); List<Student> studentList = mapper.getStudent(); for (Student student : studentList) { System.out.println(student); } sqlSession.close(); } } //查询结果：教师查不出来，为null /*Student(id=1, name=小明, teacher=null) Student(id=2, name=小红, teacher=null) Student(id=3, name=小张, teacher=null) Student(id=4, name=小李, teacher=null) Student(id=5, name=小王, teacher=null)*/

resultMap：结果集映射

resultType：结果类型（一个类）

ofType：集合中的泛型（泛型）

property实体类中的属性

column数据库中的字段

1.按照查询嵌套处理：

pojo

@Data public class Teacher { private int id; private String name; } @Data public class Student { private int id; private String name; private Teacher teacher; } <!--思路： 1.查询所有的学生信息 2.根据查询出来的学生tid，寻找对应的老师！ --> <select id="getStudent" resultMap="StudentTeacher" > select * from student </select> <resultMap id="StudentTeacher" type="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="Teacher"> select * from teacher where id =#{id} </select>

公用：测试类

public class MyTest { public static void main(String[] args) { SqlSession sqlSession = MybatisDao.getSqlSession(); StudentMapper mapper = sqlSession.getMapper(StudentMapper.class); List<Student> studentList = mapper.getStudent(); for (Student student : studentList) { System.out.println(student); } sqlSession.close(); } }

2.按照结果嵌套处理：

<select id="getStudent2" resultMap="StudentTeacher2"> select s.id sid,s.name sname,t.name tname from student s,teacher t where s.tid=t.id; </select> <resultMap id="StudentTeacher2" type="Student"> <result property="id" column="sid"/> <result property="name" column="sname"/> <association property="teacher" javaType="Teacher"> <result property="name" column="tname"/> </association> </resultMap> @Test public void test02(){ SqlSession sqlSession = MybatisDao.getSqlSession(); StudentMapper mapper = sqlSession.getMapper(StudentMapper.class); List<Student> studentList = mapper.getStudent2(); for (Student student : studentList) { System.out.println(student); } sqlSession.close(); }

一对多

pojo

@Data public class Student { private int id; private String name; private int tid; } @Data public class Teacher { private int id; private String name; //一个老是拥有多个学生 private List<Student> student; }

配置文件：

<!--方式一：--> <select id="getTeacher" parameterType="int" resultMap="TeacherStudent"> select s.id sid,s.name sname,t.name tname, t.id tid from teacher t,student s where tid=s.tid and tid =#{id} </select> <resultMap id="TeacherStudent" type="Teacher"> <result property="id" column="tid"/> <result property="name" column="tname"/> <collection property="student" ofType="Student"> <result property="id" column="sid"/> <result property="name" column="sname"/> <result property="tid" column="tid"/> </collection> </resultMap> <!--===============================================================================--> <!--方式二--> <select id="getTeacher2" parameterType="int" resultMap="TeacherStudent2"> select * from teacher where id=#{tid} </select> <resultMap id="TeacherStudent2" type="Teacher"> <result property="name" column="name"/> <collection property="student" column="id" javaType="ArrayList" ofType="Student" select="getStudentByTeacherId"/> </resultMap> <select id="getStudentByTeacherId" resultType="Student"> select * from student where tid=#{id} </select>

测试类：

@Test public void test03(){ SqlSession sqlSession = MybatisDao.getSqlSession(); TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class); Teacher teacher = mapper.getTeacher(1); System.out.println(teacher); sqlSession.close(); } @Test public void test04(){ SqlSession sqlSession = MybatisDao.getSqlSession(); TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class); Teacher teacher = mapper.getTeacher2(1); System.out.println(teacher); sqlSession.close(); }

小结

​ 1.关联-association【多对一】 ​ 2.集合-collection【一对多】 ​ 3.javaType&ofType：

1.JavaType用来指定实体类中属性的类型2.ofType用来指定映射到List或者集合中的pojo类型，泛型中的约束类型！

注意点：

保证SQL的可读性，尽量保证通俗易懂

注意一对多和多对一中，属性名和字段的问题！

如果问题不好排查错误，可以使用日志，建议使用Log4j

面试高频

Mysql引擎InnoDB底层原理素引索引优化！