Mybatis03_一对多查询

偏执的太偏执、 2023-03-13 12:25 212阅读 0赞

**Mybatis03\_一对多查询**

##### 一对多级联查询（查询学生带班级） #####

student表结构：  
![在这里插入图片描述][20200513181259760.png]  
student表内容：  
![在这里插入图片描述][20200513181340956.png]  
classes表结构：  
![在这里插入图片描述][20200513181428934.png]  
classes表内容：  
![在这里插入图片描述][20200513181457867.png]  
Student实体：

package com.blu.entity;
    import lombok.Data;
    
    @Data
    public class Student { 
    	private long id;
    	private String name;
    	private Classes classes;
    }

Classes实体：

package com.blu.entity;
    import java.util.List;
    import lombok.Data;
    
    @Data
    public class Classes { 
    	private long id;
    	private String name;
    	private List<Student> students;
    }

StudentRepository接口：

package com.blu.repository;
    import com.blu.entity.Student;
    
    public interface StudentRepository { 
    
    	public Student findById(long id);
    	
    }

StudentRepository.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.blu.repository.StudentRepository">
    	<resultMap type="com.blu.entity.Student" id="studentMap">
    		<id column="id" property="id"></id>
    		<result column="name" property="name"></result>
    		<association property="classes" javaType="com.blu.entity.Classes">
    			<id column="cid" property="id"></id>
    			<result column="cname" property="name"></result>
    		</association>
    	</resultMap>
    
    	<select id="findById" parameterType="long" resultMap="studentMap">
    		select s.id, s.name, c.id as cid,c.name as cname
    		from student s, classes c
    		where s.id=#{id} and s.cid=c.id
    	</select>
    </mapper>

resultMap标签写法：

type标签指定返回值类型，相当于resultType
    子标签id用于标识主键，result标识其他字段
    子子标签column标识查询结果集中的字段，property用于标识实体类中的属性
    association标签用于标识对象属性，javaType指定该对象所属类名

注册Mapper

测试方法：

public class Test { 
    	public static void main(String[] args) { 
    		InputStream inputStream = Test.class.getClassLoader().getResourceAsStream("config.xml");
    		SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
    		SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream);
    		SqlSession sqlSession = sqlSessionFactory.openSession();
    		StudentRepository StudentRepository = sqlSession.getMapper(StudentRepository.class);
    		Student student = StudentRepository.findById(1L);
    		System.out.println(student);
    		sqlSession.close();
    	}
    }

运行结果：

Student(id=1, name=张三, classes=Classes(id=2, name=2班, students=null))

##### 反向一对多级联查询（查班级带学生） #####

ClassesRepository接口：

package com.blu.repository;
    
    import com.blu.entity.Classes;
    
    public interface ClassesRepository { 
    	public Classes findById(long id);
    }

ClassesRepository.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.blu.repository.ClassesRepository">
    	<resultMap type="com.blu.entity.Classes" id="classesMap">
    		<id column="cid" property="id"></id>
    		<result column="cname" property="name"></result>
    		<collection property="students" ofType="com.blu.entity.Student">
    			<id column="id" property="id"></id>
    			<result column="name" property="name"></result>
    		</collection>
    	</resultMap>
    
    	<select id="findById" parameterType="long" resultMap="classesMap">
    		select s.id,s.name,c.id as cid,c.name as cname
    		from student s,classes c
    		where c.id = #{id} and s.cid = c.id
    	</select>
    </mapper>

resultMap标签写法：

collection标签用于标识集合属性，ofType指定集合的泛型

注册mapper：

测试方法：

public class Test3 { 
    	public static void main(String[] args) { 
    		InputStream inputStream = Test.class.getClassLoader().getResourceAsStream("config.xml");
    		SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
    		SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream);
    		SqlSession sqlSession = sqlSessionFactory.openSession();
    		ClassesRepository classesRepository = sqlSession.getMapper(ClassesRepository.class);
    		Classes classes = classesRepository.findById(2L);
    		System.out.println(classes);
    		sqlSession.close();
    	}
    }

结果：

Classes(id=2, name=2班, students=[Student(id=1, name=张三, classes=null), Student(id=2, name=李四, classes=null), Student(id=3, name=王五, classes=null)])

[20200513181259760.png]: /images/20230312/ab99f3c62c504b098a561d46f1fa5611.png
[20200513181340956.png]: /images/20230312/3f2219075e4844f9b28f2b645004d15f.png
[20200513181428934.png]: https://img-blog.csdnimg.cn/20200513181428934.png
[20200513181457867.png]: https://img-blog.csdnimg.cn/20200513181457867.png