Mybatis多对一、一对多处理
前提环境搭建
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, '秦老师');
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');
此时我们的数据就是多个学生对应一个老师，一个老师对应多个学生
然后我们需要先创建一个Maven项目，可参考我之前编写的
2.导入相关依赖包
org.mybatis mybatis 3.5.7 junit junit 4.12 test mysql mysql-connector-java 5.1.3 org.projectlombok lombok 1.18.10
3.创建MyBatis的核心配置文件
先配置一个数据库配置文件db.properties，配置文件存放的位置是src/main/resources目录下
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://192.168.111.131:3306/mybatis?characterEncoding=UTF-8
username=root
password=123456
然后创建MyBatis核心配置文件，习惯上命名为mybatis-config.xml，核心配置文件存放的位置是src/main/resources目录下
<?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="mapUnderscoreToCamelCase" value="true"/> settings> <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 resource="mappers/StudentMapper.xml"/> <mapper resource="mappers/TeacherMapper.xml"/> mappers> configuration>
4.编写Mybatis工具类
public class MybatisUtils {
private static SqlSessionFactory sqlSessionFactory;
static {
try {
//读取MyBatis的核心配置文件
InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
//创建SqlSessionFactoryBuilder对象
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
//通过核心配置文件所对应的字节输入流创建工厂类SqlSessionFactory，生产SqlSession对象
sqlSessionFactory = sqlSessionFactoryBuilder.build(is);
} catch (IOException e) {
e.printStackTrace();
}
}
public static SqlSession getSqlSession(){
//参数true为设置自动提交
return sqlSessionFactory.openSession(true);
}
}
多对一
查询多个学生信息及对应老师信息
1.创建实体类
该地方我们使用的是Lombok配置实体类，可以通过对其进行了解
@Data public class Student { private int id; private String name; private Teacher teacher; }
@Data public class Teacher { private int id; private String name; }
2.创建Mapper接口
MyBatis中的mapper接口相当于以前的dao。但是区别在于，mapper仅仅是接口，我们不需要提供实现类。
public interface StudentMapper { //查询所有学生信息和对应老师信息 public ListgetStudent(); }
public interface TeacherMapper { //Teacher在此不需要定义方法 }
3.创建MyBatis的映射文件
注：mapper接口的全类名和映射文件中的命名空间（namespace）保持一致。
方式一：按照查询嵌套处理(子查询)
该方式比较麻烦的就是需要写两个sql语句，将表A查询sql的结果赋给表B的查询sql，理解下来就是类似于子查询
StudentMapper.xml
<?xml version="1.0" encoding="UTF-8" ?> DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.chen.mybatis.mapper.StudentMapper"> <select id="getStudent" resultMap="StudentTeacher"> select * from student select> <resultMap id="StudentTeacher" type="com.chen.mybatis.pojo.Student"> <result property="id" column="id" /> <result property="name" column="name" /> <association property="teacher" column="tid" javaType="com.chen.mybatis.pojo.Teacher" select="teacherInfo" /> resultMap> <select id="teacherInfo" resultType="com.chen.mybatis.pojo.Teacher"> select * from teacher where id = #{tid} select> mapper>
方式二：按照结果嵌套处理(联表查询)简单点
该方式虽然不用写多个sql语句，但是需要给查出来的字段都设置别名，个人感觉的话，可能方式二更容易写一点，当然，不用写多个sql的话其实就是联表查询了
StudentMapper.xml
<?xml version="1.0" encoding="UTF-8" ?> DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.chen.mybatis.mapper.StudentMapper"> <select id="getStudent" resultMap="StudentTeacher"> select s.id sid,s.name sname,t.name tname from student s,teacher t where s.tid = t.id select> <resultMap id="StudentTeacher" type="com.chen.mybatis.pojo.Student"> <result property="id" column="sid" /> <result property="name" column="sname" /> <association property="teacher" javaType="com.chen.mybatis.pojo.Teacher"> <result property="name" column="tname" /> association> resultMap> mapper>
4.测试
public class MybatisTest { @Test public void testMybatis() { SqlSession sqlSession = MybatisUtils.getSqlSession(); StudentMapper studentMapper= sqlSession.getMapper(StudentMapper.class); ListstudentList = studentMapper.getStudent(); for(Student student:studentList){ System.out.println(student); } sqlSession.close(); } }
一对多
查询一个老师及对应的多个学生信息
1.创建实体类
该地方我们使用的是Lombok配置实体类，可以通过对其进行了解
@Data public class Student { private int id; private String name; private int tid; }
@Data public class Teacher { private int id; private String name; private Liststudents; }
2.创建Mapper接口
MyBatis中的mapper接口相当于以前的dao。但是区别在于，mapper仅仅是接口，我们不需要提供实现类。
public interface StudentMapper { //Student在此不需要定义方法 }
public interface TeacherMapper { //获取指定老师的信息及对应的多个学生的信息 Teacher getTeacher(int id); }
3.创建MyBatis的映射文件
注：mapper接口的全类名和映射文件中的命名空间（namespace）保持一致。
方式一：按照查询嵌套处理(子查询)
该方式比较麻烦的就是需要写两个sql语句，将表A查询sql的结果赋给表B的查询sql，理解下来就是类似于子查询
TeacherMapper.xml
<?xml version="1.0" encoding="UTF-8" ?> DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.chen.mybatis.mapper.TeacherMapper"> <select id="getTeacher" parameterType="int" resultMap="TeacherStudent"> select * from teacher where id = #{id} select> <resultMap id="TeacherStudent" type="com.chen.mybatis.pojo.Teacher"> <collection property="students" column="id" ofType="com.chen.mybatis.pojo.Student" select="StudentInfo"/> resultMap> <select id="StudentInfo" resultType="com.chen.mybatis.pojo.Student"> select * from student where tid = #{id} select> mapper>
方式二：按照结果嵌套处理(联表查询)简单点
该方式虽然不用写多个sql语句，但是需要给查出来的字段都设置别名，个人感觉的话，可能方式二更容易写一点，当然，不用写多个sql的话其实就是联表查询了
TeacherMapper.xml
<?xml version="1.0" encoding="UTF-8" ?> DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.chen.mybatis.mapper.TeacherMapper"> <select id="getTeacher" parameterType="int" resultMap="TeacherStudent"> select t.id teid,t.name tename,s.id sid,s.name sname from teacher t,student s where t.id=s.tid and t.id = #{id}; select> <resultMap id="TeacherStudent" type="com.chen.mybatis.pojo.Teacher"> <result property="id" column="teid" /> <result property="name" column="tename" /> <collection property="students" ofType="com.chen.mybatis.pojo.Student"> <result property="id" column="sid" /> <result property="name" column="sname" /> collection> resultMap> mapper>
4.测试
public class MybatisTest {
@Test public void testMybatis() { SqlSession sqlSession = MybatisUtils.getSqlSession(); TeacherMapper teacherMapper = sqlSession.getMapper(TeacherMapper.class); Teacher teacher = teacherMapper.getTeacher(1); System.out.println(teacher); sqlSession.close(); }
}
小结
关联 - association 多对一
集合 - collection 一对多
javaType 用来指定我们实体类中属性的类型
ofType 用来指定集合中的类型
该文档来自狂神说Java
b站视频链接https://www.bilibili.com/video/BV1NE411Q7Nx?p=22&spm_id_from=pageDriver