Mybatis复杂查询的实现
目录
- 一、环境搭建
- 1.1 建表
- 1.2 导入依赖
- 1.3 创建实体类
- 1.3.1 Student 实体类
- 1.3.2 Teacher 实体类
- 1.4 创建Mapper接口
- 1.4.1 StudentMapper 接口
- 1.4.2 TeacherMapper 接口
- 1.5 创建Mapper.XML文件
- 1.5.1 namespace 绑定StudentMapper接口
- 1.5.2 namespace 绑定TeacherMapper接口
- 1.6 核心配置文件中注册Mapper.xml文件
- 1.7 测试
- 二、多对一复杂查询
- 2.1 按照查询嵌套处理
- 2.1.1 编写接口方法
- 2.1.2 编写sql标签
- 2.1.3 测试
- 2.1.4 测试结果
- 2.2 按照结果嵌套处理
- 2.2.1 编写接口方法
- 2.2.2 编写sql标签
- 2.2.3 测试
- 2.2.4 测试结果
- 三、一对多复杂查询
- 3.1 按照结果嵌套查询
- 3.1.1 学生实体类
- 3.1.2 教师实体类
- 3.1.3 编写接口
- 3.1.4 编写TeacherMapper1.xml文件
- 3.1.5 测试
- 3.1.6 测试结果
- 3.2 根据查询嵌套处理
- 3.2.1 编写接口
- 3.2.2 编写TeacherMapper1.xml文件
- 3.2.3 测试
- 3.2.4 测试结果
一、环境搭建
1.1 建表
CREATE TABLE `teacher` ( `id` INT(10) NOT NULL, `name` VARCHAR(30) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=INNODB DEFAULT CHARSET=utf8 INSERT INTO teacher(`id`, `name`) VALUES (1, '乔丹'); INSERT INTO teacher(`id`, `name`) VALUES (2, '科比'); INSERT INTO teacher(`id`, `name`) VALUES (1, '垫脚哥'); CREATE TABLE `student` ( `id` INT(10) NOT NULL, `name` VARCHAR(30) DEFAULT NULL, `tid` INT(10) DEFAULT NULL, PRIMARY KEY (`id`), KEY `fktid` (`tid`), CONSTRAINT `fktid` FOREIGN KEY (`tid`) REFERENCES `teacher` (`id`) ) ENGINE=INNODB DEFAULT CHARSET=utf8 INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('1', '杜兰特', '1'); INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('2', '德罗赞', '1'); INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('3', '詹姆斯', '1'); INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('4', '卡子哥', '1'); INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('5', '乔治', '1');
1.2 导入依赖
<dependencies> <dependency> <groupId>log4j</groupId> <artifactId>log4j</artifactId> <version>1.2.16</version> </dependency> <dependency> <groupId>org.myBATis</groupId> <artifactId>mybatis</artifactId> <version>3.5.2</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-Java</artifactId> <version>5.1.47</version> </dependency> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.12</version> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version>1.18.12</version> <scope>provided</scope> </dependency> </dependencies>
1.3 创建实体类
1.3.1 Student 实体类
@Data @NoArgsConstructor public class Student { private int id; private String name; //学生要关联一个老师 private Teacher teacher; }
1.3.2 Teacher 实体类
@Data public class Teacher { private int id; private String name; }
1.4 创建Mapper接口
1.4.1 StudentMapper 接口
public interface StudentMapper { }
1.4.2 TeacherMapper 接口
public interface TeacherMapper { @Select("select *from teacher where id=#{tid}") Teacher getTeacher(@Param("tid") int id); }
1.5 创建Mapper.xml文件
1.5.1 namespace 绑定StudentMapper接口
<?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="com.kuang.dao.StudentMapper"> </mapper>
1.5.2 namespace 绑定TeacherMapper接口
<?xml ver编程客栈sion="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="com.kuang.dao.TeacherMapper"> </mapper>
1.6 核心配置文件中注册Mapper.xml文件
<?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="com.kuang.pojo"/> <!-- <typeAlias type="com.kuang.pojo.User" alias="User"/>--> </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.kuang.dao.TeacherMapper"/> <mapper class="com.kuang.dao.StudentMapper"/> </mappers> </configuration>
1.7 测试
@Test public void getTeacher(){ SqlSession sqlSession = MybatisUtils.getSqlSession(); TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class); Teacher teacher = mapper.getTeacher(1); System.out.println(teacher); sqlSession.close(); }
测试结果
二、多对一复杂查询
2.1 按照查询嵌套处理
2.1.1 编写接口方法
public interface StudentMapper { //查询所有的学生信息,以及对应的老师信息 List<Student> getStudent(); }
2.1.2 编写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="com.kuang.dao.StudentMapper"> <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> </mapper>
2.1.3 测试
@Test public void testStudent(){ //第一步获取sqlsession对象 SqlSession sqlSession = MybatisUtils.getSqlSession(); StudentMapper mapper = sqlSession.getMapper(StudentMapper.class); List<Student> studentList = mapper.getStudent(); for (Student student : studentList) { System.out.println(student); } sqlSession.close(); }
2.1.4 测试结果
2.2 按照结果嵌套处理
2.2.1 编写接口方法
public interface StudentMapper { //查询所有的学生信息,以及对应的老师信息 List<Student> getStudent2(); }
2.2.2 编写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="com.kuang.dao.StudentMapper"> <!--按照结果嵌套查询--> <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> </mapper> android
2.2.3 测试
@Test public void testStudent2(){ SqlSession sqlSession = MybatisUtils.getSqlSession(); StudentMapper mapper = sqlSession.getMapper(StudentMapper.class); List<Student> studentList = mapper.getStudent2(); for (Student student : studentList) { System.out.println(student); } sqlSession.close(); }
2.2.4 测试结果
三、一对多复杂查询
3.1 按照结果嵌套查询
3.1.1 学生实体类
@Data public class Student1 { private int id; private String name; private int tid; }
3.1.2 教师实体类
@Data public class Teacher1 { private int id; private String name; //一个老师对应多个学生 private List<Student1> studentqwxdYifoPis; }
3.1.3 编写接口
public interface TeacherMapper1 { //获取指定老师下的所有学生及老师的信息 Teacher1 getTeacher2(@Param("tid") int id); }
3.1.4 编写TeacherMapper1.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="com.kuang.dao.TeacherMapper1"> <select id="getTeacher1" resultType="Teacher1"> select *from mybatis.teacher; </select> <!--按照结果嵌套查询--> <select id="getTeacher2" resultMap="TeacherStudent1"> 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 = #{tid}; </select> <resultMap id="TeacherStudent1" type="Teacher1"> <result property="id" column="tid"/> <result property="name" column="tname"/> <!--复杂的属性:我们要单独处理 对象:association 集合:collection --> <!--javaType="" 指定属性的类型 集合中的泛型信息我们使用ofType获取--> <collection property="student1s" ofType="Student1" javaType="java.util.List" > <result property="id" column="sid"/> <result property="name" column="sname"/> <result property="tid" column="tid"/> </collection> </resultMap> </mapper>
3.1.5 测试
@Test public void test2() { SqlSession sqlSession = MybatisUtils.getSqlSession(); TeacherMapper1 mapper = sqlSession.getMapper(TeacherMapper1.class); Teacher1 teacher = 编程mapper.getTeacher2(1); System.out.println(teacher); }
3.1.6 测试结果
3.2 根据查询嵌套处理
3.2.1 编写接口
public interface TeacherMapper1 { //获取指定老师下的所有学生及老师的信息 Teacher1 getTeacher3(@Param("tid") int id); }
3.2.2 编写TeacherMapper1.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="com.kuang.dao.TeacherMapper1"> <select id="getTeacher3" resultType="com.kuang.pojo.Teacher1" resultMap="TeacherStudent2"> select *from mybatis.teacher where id = #{tid}; </select> <resultMap id="TeacherStudent2" type="Teacher1"> <collection property="student1s" javaType="ArrayList" ofType="Student1" select="getStudentByTeacherId" column="id"/> </resultMap> <select id="getStudentByTeacherId" resultType="Student1"> select *from mybatis.student where id = #{tid} </select> </mapper>
3.2.3 测试
@Test public void test2() { SqlSession sqlSession = MybatisUtils.getSqlSession(); TeacherMapper1 mapper = sqlSession.getMapper(TeacherMapper1.class); Teacher1 teacher = mapper.getTeacher3(1); System.out.println(teacher); }
3.2.4 测试结果
到此这篇关于Mybatis复杂查询的实现的文章就介绍到这了,更多相关Mybatis复杂查询内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)!
精彩评论