Mybatis一对一,一对多,分步,封装map查询

实体类

public class Student {
	private Integer id;
	private String name;
	private String sex;
	private Integer teacherId;
}
public class Teacher {
	private Integer id;
	private String name;
	private Integer teachClassId;
	private List<Student> studentList;
}
public class TeachClass {
	private Integer id;
	private String className;
	private Teacher teacher;
}

表结构

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `sex` varchar(255) DEFAULT NULL,
  `teacher_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `teach_class_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Table structure for teach_class
-- ----------------------------
DROP TABLE IF EXISTS `teach_class`;
CREATE TABLE `teach_class` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `class_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;

将单个查询结果封装成map

public interface StudentMapper {
	// 根据id查询,返回将学生封装成map
	public Map<String, Object> selectStudentByIdToMap(@Param("id") Integer id);
}

<?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.daben.mapper.StudentMapper">
	<select id="selectStudentByIdToMap" resultType="map">
		SELECT id, name, sex, teacher_id
		FROM student
		WHERE id = #{id}
	</select>
</mapper>

// key是数据库字段名,value是数据库存储的值
{teacher_id=1, sex=0, name=刘邦, id=1}

将多个查询结果封装成map

public interface StudentMapper {
	// 根据性别查询,返回Map,通过@MapKey注解来指定Student对应的主键
	@MapKey("id")
	public Map<Integer, Student> selectStudentBySexToMap(@Param("sex") String sex);
}

<?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.daben.mapper.StudentMapper">
	<select id="selectStudentBySexToMap" resultType="com.daben.entity.Student">
		SELECT id, name, sex, teacher_id
		FROM student
		WHERE sex = #{sex}
	</select>
</mapper>

{
    2=Student [id=2, name=刘备, sex=1, teacherId=2], 
 	6=Student [id=6, name=赵云, sex=1, teacherId=2], 
 	10=Student [id=10, name=哪吒, sex=1, teacherId=1]
}

一对一查询

public interface TeachClassMapper {
	// 一对一查询
	public TeachClass selectTeachClassById(@Param("id") Integer id);
}

<?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.daben.mapper.TeachClassMapper">
	<select id="selectTeachClassById" resultMap="teachClass">
		SELECT c.id, c.class_name, t.id tid, t.name
		FROM teach_class c
		LEFT JOIN teacher t
		ON c.id = t.teach_class_id
		WHERE c.id = #{id}
	</select>
	<resultMap type="com.daben.entity.TeachClass" id="teachClass">
		<id property="id" column="id"/>
		<result property="className" column="class_name"/>
		<association property="teacher" javaType="com.daben.entity.Teacher">
			<id property="id" column="tid"/>
			<result property="name" column="name"/>
		</association>
	</resultMap>
</mapper>

一对多查询

public interface TeacherMapper {
	// 根据id来查询老师以及该老师管理的所有学生
	public Teacher selecTeacherById(@Param("id") Integer id);
}

<?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.daben.mapper.TeacherMapper">
	<select id="selecTeacherById" resultMap="teacherMap">
		SELECT t.id, t.name, t.teach_class_id, s.id sid, s.name sname, s.sex, s.teacher_id
		FROM teacher t 
		LEFT JOIN student s
		ON t.id = s.teacher_id
		WHERE t.id = #{id}
	</select>
	<resultMap type="com.daben.entity.Teacher" id="teacherMap">
		<id property="id" column="id"/>
		<result property="name" column="name"/>
		<result property="teachClassId" column="teach_class_id"/>
		<collection property="studentList" ofType="com.daben.entity.Student">
			<id property="id" column="sid"/>
			<result property="name" column="sname"/>
			<result property="sex" column="sex"/>
			<result property="teacherId" column="teacher_id"/>
		</collection>
	</resultMap>
</mapper>

一对一分步查询

public interface TeachClassMapper {
	// 一对一分步查询
	public TeachClass selectTeachClassAndTeacherInfo(@Param("id") Integer id);
}

<?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.daben.mapper.TeachClassMapper">
	<select id="selectTeachClassAndTeacherInfo" resultMap="teachClassAndTeacherInfo">
		SELECT * 
		FROM teach_class
		WHERE id = #{id}
	</select>
	<resultMap type="com.daben.entity.TeachClass" id="teachClassAndTeacherInfo">
		<id property="id" column="id"/>
		<result property="className" column="class_name"/>
		<!-- 第二步查询需要传入一个参数时,直接column="主表查询来的数据库字段",如果需要多个参数时column="{第二步查询参数名=主表查询来的数据库字段}" -->
		<association property="teacher" select="com.daben.mapper.TeacherMapper.selectTeacherByIdAndName" column="{teachClassId=id,name=class_name}"></association>
	</resultMap>
</mapper>

<?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.daben.mapper.TeacherMapper">
	<select id="selectTeacherByIdAndName" resultType="com.daben.entity.Teacher">
		SELECT *
		FROM teacher
		WHERE teach_class_id = #{teachClassId} 
		AND name = #{name}
	</select>
</mapper>

声明:
有一些博文是看的黑马程序员视频,然后跟着老师做的笔记
Spring是跟子路老师学的
特此感谢,写这些文章的目的是为了自己方便查阅