Mybatis03_一对多查询

Mybatis03_一对多查询

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

student表结构：

student表内容：

classes表结构：

classes表内容：

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

<mapper resource="com/blu/mapper/StudentRepository.xml"></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：

<mapper resource="com/blu/mapper/ClassesRepository.xml"></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)])