文章目录
复杂查询环境搭建
多对一处理(比如多个学生对应一个老师)
sql:
CREATE TABLE teacher(
id INT PRIMARY KEY,
NAME VARBINARY(20) DEFAULT NULL
)ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO teacher VALUES(1,'卓中');
CREATE TABLE student(
id INT PRIMARY KEY,
NAME VARBINARY(20) DEFAULT NULL,
tid INT,
CONSTRAINT ftid FOREIGN KEY(tid) REFERENCES teacher(id)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO student VALUES(1,'张三' ,1),(2,'李四' ,1),(3,'王五' ,1),(4,'田七' ,1);
测试环境搭建
-
导入lombok
-
实体类Teacher、Student
@Data @AllArgsConstructor @NoArgsConstructor public class Student { private int id; private String name; //注意这里是Teacher对象 private Teacher teacher; } @Data @AllArgsConstructor @NoArgsConstructor public class Teacher { private int id; private String name; }
-
接口TeacherMapper、StudentMapper
-
可以通过注解或者xml文件进行测试,看环境是否搭建成
按照查询嵌套处理
-
StudentMapper
public interface StudentMapper { //查询返回学生list List<Student> getStudentList(); }
-
StudentMapper.xml (记得在在核心配置中注册)
思路:
- 首先查询所有的学生
- 再根据所有的学生表中的tid查找对应的老师(子查询)
<?xml version="1.0" encoding="UTF8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.wjq.dao.StudentMapper"> <select id="getStudentList" resultMap="StudentTeacher"> select * from student </select> <resultMap id="StudentTeacher" type="Student"> <!-- 因为Student对象的第三个属性为Teacher类型,因此不能再用resultMap中的普通标签(result、id)--> <!-- 要用复杂的处理例如collection集合(一对多,一个老师对应多个学生) association对象(在多对一中使用,可以理解为多个学生关联一个老师)--> <!-- 对象中的字段 字段的类型 映射到数据库中的字段名 嵌套查询的id--> <association property="teacher" javaType="Teacher" column="tid" select="getTeacher"/> </resultMap> <select id="getTeacher" resultType="Teacher"> select * from teacher where id = #{tid}; </select> </mapper>
-
测试
@Test public void getStudentList(){ SqlSession sqlSession = MybatisUtils.getSqlSession(); StudentMapper mapper = sqlSession.getMapper(StudentMapper.class); List<Student> students = mapper.getStudentList(); for (Student student : students) { System.out.println(student); } sqlSession.close(); }
按照结果嵌套处理
-
StudentMapper
public interface StudentMapper { //查询返回学生list List<Student> getStudentList2(); }
-
StudentMapper.xml (记得在在核心配置中注册)
思路:
- 就直接连表查询
<?xml version="1.0" encoding="UTF8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.wjq.dao.StudentMapper"> <select id="getStudentList2" 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 javaType指定属性的类型--> <association property="teacher" javaType="Teacher"> <!--Teacher表中的属性--> <result property="name" column="tname"/> </association> </resultMap> </mapper>
-
测试
@Test public void getStudentList2(){ SqlSession sqlSession = MybatisUtils.getSqlSession(); StudentMapper mapper = sqlSession.getMapper(StudentMapper.class); List<Student> students = mapper.getStudentList2(); for (Student student : students) { System.out.println(student); } sqlSession.close(); }
如有不对的地方欢迎指出,共同进步!