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

最新推荐文章于 2024-12-02 19:13:51 发布

转载最新推荐文章于 2024-12-02 19:13:51 发布 · 1.2k 阅读

mybatis 专栏收录该内容

32 篇文章

订阅专栏

本文介绍MyBatis中的嵌套查询与嵌套结果两种关联查询方式，并通过具体示例详细展示了如何实现一对一双向关联及一对多双向关联查询。

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

2016年12月25日 18:20:44

阅读数：3289

/**

* 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;
}
@Override
public 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 USER
LEFT 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"/>

</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;
}
@Override
public 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 Teacher
LEFT 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="com/mybatis/vo/userMapper.xml" />
<mapper resource="com/mybatis/vo/teacherMapper.xml" />
</mappers>
</configuration>