Mybatis多对一一对多查询

一、环境搭建

数据库表：

CREATE TABLE `teacher` ( `id` int(10) NOT NULL, `name` varchar(30) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ------------------------------ Records of teacher-- ----------------------------INSERT INTO `teacher` 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;-- ------------------------------ Records of student-- ----------------------------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');

1.导入lombok

<?xml version="1.0" encoding="UTF-8"?> 4.0.0 org.leo mybatis_demo pom 1.0-SNAPSHOT mybatis_01 mysql mysql-connector-java 8.0.27 org.mybatis mybatis 3.5.9 junit junit 4.12 org.projectlombok lombok 1.18.22 src/main/resources ***.xml true src/main/java ***.xml true 8 8

2.新建实体类Student、Mapper

package com.leo.pojo;import lombok.Data;@Datapublic class Student { private int id; private String name; private Teacher teacher;}

package com.leo.pojo;import lombok.Data;@Datapublic class Teacher { private int id; private String name;}

3.建立Mapper接口

package com.leo.dao;public interface StudentMapper {}

package com.leo.dao;import com.leo.pojo.Teacher;import org.apache.ibatis.annotations.Param;import org.apache.ibatis.annotations.Select;public interface TeacherMapper { @Select("select * from teacher where id = #{tid}") Teacher getTeacher(@Param("tid") int id);}

4.建立Mapper.xml

<?xml version="1.0" encoding="UTF-8" ?>

<?xml version="1.0" encoding="UTF-8" ?>

5.在核心配置文件中绑定注册Mapper接口或文件

<?xml version="1.0" encoding="UTF-8" ?>

6.测试查询是否成功

（1）工具类

package com.leo.utils;import org.apache.ibatis.io.Resources;import org.apache.ibatis.session.SqlSession;import org.apache.ibatis.session.SqlSessionFactory;import org.apache.ibatis.session.SqlSessionFactoryBuilder;import java.io.IOException;import java.io.InputStream;// mybatis工具类public class MybatisUtils { private static SqlSessionFactory sqlSessionFactory; static { String resource = "mybatis-config.xml"; try { InputStream inputStream = Resources.getResourceAsStream(resource); sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); } catch (IOException e) { e.printStackTrace(); } } // 既然有了 SqlSessionFactory，顾名思义，我们可以从中获得 SqlSession 的实例。SqlSession 提供了在数据库执行SQL命令所需的所有方法。 // 你可以通过 SqlSession 实例来直接执行已映射的 SQL 语句。例如： public static SqlSession getSqlSession() { return sqlSessionFactory.openSession(); }}

（2）测试类

package com.leo.dao;import com.leo.pojo.Teacher;import com.leo.utils.MybatisUtils;import org.apache.ibatis.annotations.Mapper;import org.apache.ibatis.session.SqlSession;import org.junit.Test;public class TeacherMapperTest { @Test public void test() { SqlSession sqlSession = MybatisUtils.getSqlSession(); TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class); Teacher teacher = mapper.getTeacher(1); System.out.println(teacher); sqlSession.close(); }}

（3）执行结果

二、多对一查询

1.按照查询嵌套处理（子查询）

（1）mapper接口

package com.leo.dao;import com.leo.pojo.Student;import java.util.List;public interface StudentMapper { public List getStudent();}

（2） mapper xml

<?xml version="1.0" encoding="UTF-8" ?> select * from student select * from teacher where id = #{id}

（3） 测试代码

package com.leo.dao;import com.leo.pojo.Student;import com.leo.pojo.Teacher;import com.leo.utils.MybatisUtils;import org.apache.ibatis.session.SqlSession;import org.junit.Test;import java.util.List;public class StudentMapperTest { @Test public void getStudent() { SqlSession sqlSession = MybatisUtils.getSqlSession(); StudentMapper mapper = sqlSession.getMapper(StudentMapper.class); List studentList = mapper.getStudent(); for (Student student : studentList) { System.out.println(student); } sqlSession.close(); }}

（4）执行

 

2.按照结果嵌套处理（连表查询）

（1）mapper接口

package com.leo.dao;import com.leo.pojo.Student;import java.util.List;public interface StudentMapper { // 按照查询嵌套处理 public List getStudent(); // 按照结果嵌套处理 public List getStudent2();}

（2） mapper xml

<?xml version="1.0" encoding="UTF-8" ?>

select * from student select * from teacher where id = #{id} select s.id sid, s.name sname, t.name tname from student s,teacher t where s.tid = t.id

（3） 测试代码

package com.leo.dao;import com.leo.pojo.Student;import com.leo.pojo.Teacher;import com.leo.utils.MybatisUtils;import org.apache.ibatis.session.SqlSession;import org.junit.Test;import java.util.List;public class StudentMapperTest { @Test public void getStudent() { SqlSession sqlSession = MybatisUtils.getSqlSession(); StudentMapper mapper = sqlSession.getMapper(StudentMapper.class); List studentList = mapper.getStudent(); for (Student student : studentList) { System.out.println(student); } sqlSession.close(); } @Test public void getStudent2() { SqlSession sqlSession = MybatisUtils.getSqlSession(); StudentMapper mapper = sqlSession.getMapper(StudentMapper.class); List studentList = mapper.getStudent2(); for (Student student : studentList) { System.out.println(student); } sqlSession.close(); }}

（4）执行

三、一对多查询

1.实体pojo

package com.leo.pojo;import lombok.Data;@Datapublic class Student { private int id; private String name; private int tid;}

package com.leo.pojo;import lombok.Data;import java.util.List;@Datapublic class Teacher { private int id; private String name; // 一个老师拥有多个学生 private List students;}

2.dao

package com.leo.dao;import com.leo.pojo.Teacher;import org.apache.ibatis.annotations.Param;public interface TeacherMapper { // 获取指定老师下的所有学生及老师的信息（嵌套查询） Teacher getTeacher(@Param("tid") int id); // 获取指定老师下的所有学生及老师的信息（子查询） Teacher getTeacher2(@Param("tid") int id);}

package com.leo.dao;public interface StudentMapper {}

3.mapper.xml

<?xml version="1.0" encoding="UTF-8" ?>

<?xml version="1.0" encoding="UTF-8" ?> 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 * from teacher where id = #{tid} select * from mybatis.student where tid = #{tid}

4.测试代码

package com.leo.dao;import com.leo.pojo.Teacher;import com.leo.utils.MybatisUtils;import org.apache.ibatis.session.SqlSession;import org.junit.Test;public class TeacherMapperTest { @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(); }}

5.执行