21、(知识篇)Mybatis02使用（嵌套结果/嵌套查询）

/**

* MyBatis的嵌套结果/嵌套查询（网上有说这是叫延迟加载）

* 

* 1、一对一：

* 1.1 一对一嵌套结果：实际就是将两个表join起来（参考userMapper.xml的selectUserById方法）

* 得出多行数据，其中，需要用resultMap返回，

* 配置resultMap

* <resultMap type="User" id="selectUserMap" autoMapping="true"></resultMap>

* type：返回的类型的全类名/配置别名 

* autoMapping：自动对应字段，如果不写，则需要手动映射起来

* 方法是加入<id column="xxx" property="xxx" />/<result column="xxx" property="xxx"/>

* 

* 1.2 通过 <association> 标签，配置返回的一对一的bean（入User中对应一个Teacher），association中则需要配置Teacher的class

* 例如：<association property="teacher" javaType="Teacher" autoMapping="true"><id column="teacherId" property="id"/><association/>

* 

* 

* 1.3一对一嵌套查询：实际是做两次查询（参考userMapper.xml的selectSingleUserById）

* 和嵌套结果类似：只不过配置association时，需要传入外键，和填写select的语句的id（参考selectSingleUser方法）

* 例如：<association javaType="Teacher" property="teacher" column="teacherId" select="selectSingleUser"  autoMapping="true"></association>

* 其中需要提及一下的是column 为表的外键的列名

* 

* 

* 2、一对多

* 2.1 一对多嵌套结果：跟一对一嵌套结果类似，区别在于（参考teacherMapper.xml的getTeacherByJoin方法）：

* 一对一嵌套结果使用<association>，一对多中使用<collection>标签

* 一对一中使用javaType指定类型，一堆多中使用ofType指定类型；

* resultMap中必须指定id 的标签，否则selectone中返回结果会报错

* 2.2一对多嵌套查询：同样跟一对一嵌套结果类似

* 注意一下collection标签、ofType、指定id标签对应的column

* 

* 

* @param args

*/

测试类：

package com.mybatis.test;import java.io.InputStream;import org.apache.ibatis.session.SqlSession;import org.apache.ibatis.session.SqlSessionFactory;import org.apache.ibatis.session.SqlSessionFactoryBuilder;import com.mybatis.vo.Teacher;import com.mybatis.vo.User;public class Test {/** * MyBatis的嵌套结果/嵌套查询（网上有说这是叫延迟加载） *  * 1、一对一： * 1.1 一对一嵌套结果：实际就是将两个表join起来（参考userMapper.xml的selectUserById方法） * 得出多行数据，其中，需要用resultMap返回， * 配置resultMap * <resultMap type="User" id="selectUserMap" autoMapping="true"></resultMap> * type：返回的类型的全类名/配置别名  * autoMapping：自动对应字段，如果不写，则需要手动映射起来 * 方法是加入<id column="xxx" property="xxx" />/<result column="xxx" property="xxx"/> *  * 1.2 通过 <association> 标签，配置返回的一对一的bean（入User中对应一个Teacher），association中则需要配置Teacher的class * 例如：<association property="teacher" javaType="Teacher" autoMapping="true"><id column="teacherId" property="id"/><association/> *  *  * 1.3一对一嵌套查询：实际是做两次查询（参考userMapper.xml的selectSingleUserById） * 和嵌套结果类似：只不过配置association时，需要传入外键，和填写select的语句的id（参考selectSingleUser方法） * 例如：<association javaType="Teacher" property="teacher" column="teacherId" select="selectSingleUser"  autoMapping="true"></association> * 其中需要提及一下的是column 为表的外键的列名 *  *  * 2、一对多 *2.1 一对多嵌套结果：跟一对一嵌套结果类似，区别在于（参考teacherMapper.xml的getTeacherByJoin方法）： *一对一嵌套结果使用<association>，一对多中使用<collection>标签 * 一对一中使用javaType指定类型，一堆多中使用ofType指定类型； * resultMap中必须指定id 的标签，否则selectone中返回结果会报错 * 2.2一对多嵌套查询：同样跟一对一嵌套结果类似 * 注意一下collection标签、ofType、指定id标签对应的column *  *  * @param args */public static void main(String[] args) {InputStream is = Test.class.getClassLoader().getResourceAsStream("mybatis-config.xml");System.out.println(is);SqlSessionFactory sessionFactory =  new SqlSessionFactoryBuilder().build(is);System.out.println(sessionFactory);SqlSession session = sessionFactory.openSession();System.out.println(session);String userNameSpace = "com.mybatis.vo.User";/*一对一嵌套结果*/String selectOneByJoin = userNameSpace+".selectUserById";User user = session.selectOne(selectOneByJoin,3);System.out.println(user);/*一对一嵌套查询*/String selectOneBySelect = userNameSpace+".selectSingleUserById";User user2 = session.selectOne(selectOneBySelect,1);System.out.println(user2);String teacherNameSpace = "com.mybatis.vo.Teacher";/*一对多嵌套结果*/String getTeacherByJoin = teacherNameSpace+".getTeacherByJoin";Teacher teacher = session.selectOne(getTeacherByJoin,1);System.out.println(teacher);/*一对多嵌套查询*/String getTeacherById = teacherNameSpace+".getTeacherById";Teacher teacher2 = session.selectOne(getTeacherById,2);System.out.println(teacher2);}}

User类：

package com.mybatis.vo;public class User {private int id;private String userName;private int sex;private Teacher teacher;public User() {super();// TODO Auto-generated constructor stub}public User(int id, String userName, int sex, Teacher teacher) {super();this.id = id;this.userName = userName;this.sex = sex;this.teacher = teacher;}public int getId() {return id;}public void setId(int id) {this.id = id;}public String getUserName() {return userName;}public void setUserName(String userName) {this.userName = userName;}public int getSex() {return sex;}public void setSex(int sex) {this.sex = sex;}public Teacher getTeacher() {return teacher;}public void setTeacher(Teacher teacher) {this.teacher = teacher;}@Overridepublic String toString() {return "User [id=" + id + ", userName=" + userName + ", sex=" + sex + ", teacher=" + teacher + "]";}}

userMapper.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.mybatis.vo.User"><!-- 一对一嵌套结果 --><select id="selectUserById" parameterType="int" resultMap="selectUserMap">SELECT user.id,userName,sex,teacher.id AS teacherId,teacherName FROM USERLEFT OUTER JOIN Teacher ON Teacher.id = user.teacherId  where user.id = #{id}</select><resultMap type="User" id="selectUserMap" autoMapping="true"><!-- <id property="id" column="id"/><result property="userName" column="userName"/><result property="sex" column="sex"/> --><association property="teacher" javaType="Teacher" autoMapping="true"><id column="teacherId" property="id"/><!-- <result column="teacherName" property="teacherName"/> --></association></resultMap><!-- 一对一嵌套查询 --><select id="selectSingleUserById" parameterType="int" resultMap="selectSingleUserMap" >select * from User where id = #{id}</select><select id="selectSingleUser" parameterType="int" resultType="Teacher">select * from Teacher where id = #{id}</select><resultMap type="User" id="selectSingleUserMap" autoMapping="true"><association javaType="Teacher" property="teacher" column="teacherId" select="selectSingleUser"  autoMapping="true"></association></resultMap></mapper>

Teacher类：

package com.mybatis.vo;import java.util.List;public class Teacher {private int id;private String teacherName;private List<User> users;public Teacher() {super();// TODO Auto-generated constructor stub}public Teacher(int id, String teacherName, List<User> users) {super();this.id = id;this.teacherName = teacherName;this.users = users;}public int getId() {return id;}public void setId(int id) {this.id = id;}public String getTeacherName() {return teacherName;}public void setTeacherName(String teacherName) {this.teacherName = teacherName;}public List<User> getUsers() {return users;}public void setUsers(List<User> users) {this.users = users;}@Overridepublic String toString() {return "Teacher [id=" + id + ", teacherName=" + teacherName + ", users=" + users + "]";}}

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.mybatis.vo.Teacher"><!-- 嵌套结果 --><select id="getTeacherByJoin" parameterType="int" resultMap="getTeacherByJoinMap">SELECT teacher.*,user.id AS userId,user.UserName,user.Sex FROM TeacherLEFT OUTER JOIN USER ON user.TeacherID = teacher.id where teacher.id = #{id}</select><resultMap type="Teacher" id="getTeacherByJoinMap" autoMapping="true"><id column="id" property="id"/><collection property="users" ofType="User" autoMapping="true"><id property="id" column="userId"/></collection></resultMap><!-- 一对多嵌套查询 --><select id="getTeacherById" parameterType="int" resultMap="getTeacherByIdMap">select * from Teacher where id = #{id}</select><select id="getUserByTeacherId" parameterType="int" resultType="User">select * from User where teacherId = #{teacherId}</select><resultMap type="Teacher" id="getTeacherByIdMap" autoMapping="true"><id column="id" property="id"/><collection property="users" column="id"  select="getUserByTeacherId" autoMapping="true"></collection></resultMap></mapper>

mybatis-config.xml

<?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><typeAliases><typeAlias type="com.mybatis.vo.User" alias="User" /><typeAlias type="com.mybatis.vo.Teacher" alias="Teacher" /></typeAliases><environments default="development"><environment id="development"><transactionManager type="JDBC" /><dataSource type="POOLED"><property name="driver" value="com.mysql.jdbc.Driver" /><property name="url" value="jdbc:mysql://192.168.1.92:3306/test" /><property name="username" value="root" /><property name="password" value="root" /></dataSource></environment></environments><mappers><!-- <mapper resource="org/mybatis/example/BlogMapper.xml" /> --><mapper resource="com/mybatis/vo/userMapper.xml" /><mapper resource="com/mybatis/vo/teacherMapper.xml" /></mappers></configuration>