【MyBatis学习笔记六】——MyBatis一对一，一对多，多对一，多对多

最新推荐文章于 2024-03-29 18:57:34 发布

码上云端

最新推荐文章于 2024-03-29 18:57:34 发布

阅读量135

点赞数

分类专栏： mybatis 文章标签： mybatis java orm

本文链接：https://blog.csdn.net/weixin_43817709/article/details/117537580

版权

mybatis 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

1.MybBatis一对一

一对一，一个学生对应一个老师就是一对一
创建老师表和学生表

#老师表
CREATE TABLE `teacher` (
  `id` int(10) NOT NULL,
  `name` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


#学生表
CREATE TABLE `student` (
  `id` int(10) NOT NULL,
  `name` varchar(30) DEFAULT NULL,
  `tid` int(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fktid` (`tid`),
  CONSTRAINT `fktid` FOREIGN KEY (`tid`) REFERENCES `teacher` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

创建老师实体类和学生实体类

//老师实体类
public class Teacher {
    private int id;
    private String name;
    private List<Student> studentList;
	
	//省略get(),set(),toString()
}


//学生实体类
public class Student {

    private int id;
    private String name;
    private Teacher teacher;
    
    //省略get(),set(),toString()
}

xml配置

<!--     一对一     -->
<!--嵌套查询：通过执行另外一个SQL映射语句来返回预期的复杂类型-->
<select id="getOne" resultMap="TeacherStudent3">
	select * from mybatis.student where id = #{id}
</select>

<resultMap id="TeacherStudent3" type="com.demo.entity.Student">
    <id property="id" column="id"></id>
    <result property="name" column="name"></result>
    <association property="teacher" column="tid" javaType="com.demo.entity.Teacher" select="getTeacherById"></association>
</resultMap>

<select id="getTeacherById" resultType="com.demo.entity.Teacher">
	select * from mybatis.teacher where id = #{tid}
</select>


<!--嵌套结果：使用嵌套结果映射来处理重复的联合结果的子集-->
<select id="getOne2" resultMap="TeacherStudent4">
    select s.id as sid,
            s.name as sname,
            t.id as tid,
            t.name as tname
            from mybatis.student as s join mybatis.teacher as t on s.tid = t.id
            where s.id = #{id}
</select>

<resultMap id="TeacherStudent4" type="com.demo.entity.Student">
    <id property="id" column="sid"></id>
    <result property="name" column="sname"></result>
    <association property="teacher" javaType="com.demo.entity.Teacher">
        <id property="id" column="tid"></id>
        <result property="name" column="tname"></result>
    </association>
</resultMap>

2.MybBatis一对多

一对多，一个老师对应多个学生就是一对多
xml配置

<!--   一对多   -->
<!--嵌套查询：通过执行另外一个SQL映射语句来返回预期的复杂类型-->
<select id="getOne2" resultMap="teacherStudent2">
	select * from mybatis.teacher where id = #{tid}
</select>

<resultMap id="teacherStudent2" type="com.demo.entity.Teacher">
    <!--<id property="id" column="id"></id>-->
    <!--<result property="name" column="name"></result>-->
    <collection property="studentList"  ofType="com.demo.entity.Student" select="getStudent" column="id"></collection>
</resultMap>

<select id="getStudent" resultType="com.demo.entity.Student">
	select * from mybatis.student where tid = #{tid}
</select>

<!--嵌套结果：使用嵌套结果映射来处理重复的联合结果的子集-->
<select id="getOne" resultMap="teacherStudent">
    select s.id as sid,
            s.name as sname,
            t.id as tid,
            t.name as tname
            from mybatis.student as s join mybatis.teacher as t on s.tid =   t.id
            where t.id = #{tid}
</select>

<resultMap id="teacherStudent" type="com.demo.entity.Teacher">
    <id property="id" column="tid"></id>
    <result property="name" column="tname"></result>
    <collection property="studentList"  ofType="com.demo.entity.Student">
        <id property="id" column="sid"></id>
        <result property="name" column="sname"></result>
    </collection>
</resultMap>

3.MybBatis多对一

多对一，多个学生对应一个老师就是多对一
xml配置

<!--嵌套查询：通过执行另外一个SQL映射语句来返回预期的复杂类型-->
<select id="getAll" resultMap="TeacherStudent">
	select * from mybatis.student
</select>

<resultMap id="TeacherStudent" type="com.demo.entity.Student">
    <id property="id" column="id"></id>
    <result property="name" column="name"></result>
    <association property="teacher" column="tid" javaType="com.demo.entity.Teacher" select="getTeacher"></association>
</resultMap>

<select id="getTeacher" resultType="com.demo.entity.Teacher">
	select * from mybatis.teacher
</select>


<!--嵌套结果：使用嵌套结果映射来处理重复的联合结果的子集-->
<select id="getAll2" resultMap="TeacherStudent2">
    select s.id as sid,
    s.name as sname,
    t.id as tid,
    t.name as tname
    from mybatis.student as s join mybatis.teacher as t on s.tid = t.id
</select>

<resultMap id="TeacherStudent2" type="com.demo.entity.Student">
    <id property="id" column="sid"></id>
    <result property="name" column="sname"></result>
    <association property="teacher" javaType="com.demo.entity.Teacher">
        <id property="id" column="tid"></id>
        <result property="name" column="tname"></result>
    </association>
</resultMap>

4.MybBatis多对多

多对多，多个用户对应多个组就多对多，要通过中间表来维护对多的关系
创建用户表，组表，用户和组的中间表

#用户表
CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL,
  `password` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

#组表
CREATE TABLE `groups` (
  `id` int(10) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

#用户和组的中间表
CREATE TABLE `user_groups` (
  `uid` int(10) unsigned NOT NULL,
  `gid` int(10) unsigned NOT NULL,
  PRIMARY KEY (`uid`,`gid`) USING BTREE,
  KEY `IX_uid` (`uid`) USING BTREE,
  KEY `FK_gid` (`gid`),
  CONSTRAINT `FK_gid` FOREIGN KEY (`gid`) REFERENCES `groups` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_uid` FOREIGN KEY (`uid`) REFERENCES `user` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;

创建用户实体类，组实体类，用户和组的中间表实体类

//用户实体类
public class User {

    private int id;
    private String username;
    private String password;
    private List<Groups> groupsList;
	
	//省略get(),set(),toString()
}

//组实体类
public class Groups {

    private int id;
    private String name;
    
	//省略get(),set(),toString()
}

//用户和组的中间表实体类
public class UserGroups {

    private int uid;
    private int gid;
    
    //省略get(),set(),toString()
}

xml配置

<!--嵌套结果：使用嵌套结果映射来处理重复的联合结果的子集-->
<select id="getAllUserAndGroups" resultMap="userGroups">
    select u.id as userId,
            u.username,
            u.password,
            g.id as groupsId,
            g.name
            from  mybatis.user as u join mybatis.user_groups as ug on u.id = ug.uid
            join mybatis.groups as g on ug.gid = g.id
</select>

<resultMap id="userGroups" type="com.demo.entity.User">
    <id column="userId" property="id"></id>
    <result column="username" property="username"></result>
    <result column="password" property="password"></result>
    <collection property="groupsList" ofType="com.demo.entity.Groups">
        <id column="groupsId" property="id"></id>
        <result column="name" property="name"></result>
    </collection>
</resultMap>


<!--嵌套查询：通过执行另外一个SQL映射语句来返回预期的复杂类型-->
<select id="getAllUserAndGroups2" resultMap="userGroups2">
	select id,username,password from mybatis.user
</select>

<resultMap id="userGroups2" type="com.demo.entity.User">
    <id column="id" property="id"></id>
    <result column="username" property="username"></result>
    <result column="password" property="password"></result>
    <collection property="groupsList" ofType="com.demo.entity.Groups" column="id" select="getGroups">
</collection>
</resultMap>

<select id="getGroups" resultType="com.demo.entity.Groups">
    select g.* from  mybatis.groups as g
    join mybatis.user_groups as ug on g.id = ug.gid
    where ug.uid = #{id}
</select>

5.总结

association 用于单个对象，javaType表示对象类型
collection用于集合，ofType表示集合包含的对象类型
在嵌套查询中，以上两个中的column=“id” 都可以将id作为参数传递到子查询

6.demo地址

https://download.csdn.net/download/weixin_43817709/19377262

码上云端

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
【MyBatis学习笔记六】——MyBatis一对一，一对多，多对一，多对多

1.MybBatis一对一一对一，一个学生对应一个老师就是一对一创建老师表和学生表#老师表CREATE TABLE `teacher` ( `id` int(10) NOT NULL, `name` varchar(30) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;#学生表CREATE TABLE `student` ( `id` int(10) NOT NULL, `n
复制链接

扫一扫