多对一和一对多

多对一和一对多

多个学生,对应一个老师
对于学生而言,关联: 多个学生,关联一个老师 [多对一]
对于老师而言,集合: 一个老师,有很多学生 [一对多]

1. 复杂表的构建

CREATE TABLE `teacher` (

`id` INT(10) NOT NULL,

`name` VARCHAR(30) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO teacher(`id`, `name`) VALUES (1, '秦老师');

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

INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('1', '小明', '1');

INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('2', '小红', '1');

INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('3', '小张', '1');

INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('4', '小李', '1');

INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('5', '小王', '1');

2. 测试环境搭建

1. 导入Lombok

<dependencies>

    <!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->

    <dependency>

        <groupId>org.projectlombok</groupId>

        <artifactId>lombok</artifactId>

        <version>1.18.12</version>

    </dependency>

</dependencies>

2. 新建实体类

package com.wang.pojo;

import lombok.AllArgsConstructor;

import lombok.Data;

import lombok.NoArgsConstructor;

@Data

@AllArgsConstructor

@NoArgsConstructor

public class Student {

    private int id;

    private String name;

    //学生需要关联一个老师(物理外键)

    private Teacher teacher;

}

package com.wang.pojo;

import lombok.AllArgsConstructor;

import lombok.Data;

import lombok.NoArgsConstructor;

@Data

@NoArgsConstructor

@AllArgsConstructor

public class Teacher {

    private int id;

    private String name;

}

3. 建立Mapper接口

package com.wang.dao;

import com.wang.pojo.Student;

import java.util.List;

public interface StudentMapper {

    //查询所有的学生信息,以及对应的老师的信息

    List<Student> getStudentInfo();

}

package com.wang.dao;

import com.wang.pojo.Teacher;

import org.apache.ibatis.annotations.Param;

import org.apache.ibatis.annotations.Select;

public interface TeacherMapper {

    @Select("select * from teacher where id = #{tid}")

    Teacher getTeacher(@Param("tid") int id);

}

4. 建立Mapper.xml文件

<?xml version="1.0" encoding="UTF-8" ?>

<!DOCTYPE mapper

        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"

        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.wang.dao.StudentMapper">

</mapper>

<?xml version="1.0" encoding="UTF-8" ?>

<!DOCTYPE mapper

        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"

        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.wang.dao.TeacherMapper">

</mapper>

5. 在核心配置文件汇总绑定Mapper接口或者文件

<?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 核心配置文件-->

<configuration>

    <!--引入外部配置文件,由于此处都在resource目录下,直接写文件名-->

    <properties resource="db.properties"/>

    <!--标准的日志工厂实现-->

    <settings>

        <setting name="logImpl" value="STDOUT_LOGGING"/>

    </settings>

    <!--可以给实体类起别名-->

    <typeAliases>

        <typeAlias type="com.wang.pojo.Teacher" alias="Teacher"/>

    </typeAliases>

    <environments default="development">

        <environment id="development">

            <transactionManager type="JDBC"/>

            <dataSource type="POOLED">

                <property name="driver" value="${driver}"/>

                <property name="url" value="${url}"/>

                <property name="username" value="${username}"/>

                <property name="password" value="${pwd}"/>

            </dataSource>

        </environment>

    </environments>

    <mappers>

        <mapper resource="com/wang/dao/StudentMapper.xml"/>

        <!--由于使用了注解,Teacher要用class关联接口!-->

        <mapper class="com.wang.dao.TeacherMapper"/>

    </mappers>

</configuration>

6. 测试查询是否成功

import com.wang.dao.TeacherMapper;

import com.wang.pojo.Teacher;

import com.wang.utils.MybatisUtils;

import org.apache.ibatis.session.SqlSession;

import org.junit.Test;

public class MyTest {

    @Test

    public void TestGetTeacher() {

        SqlSession sqlSession = MybatisUtils.getSqlSession();

        TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);

        Teacher teacher = mapper.getTeacher(1);

        System.out.println(teacher);

        sqlSession.close();

    }

}

3. 多对一处理

1. 按照查询嵌套处理(子查询)

<?xml version="1.0" encoding="UTF-8" ?>

<!DOCTYPE mapper

        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"

        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.wang.dao.StudentMapper">

    <!--

    思路:

        1.查询所有的学生信息

        2.根据查询出来的学生的tid,寻找对应的老师

    -->

    <select id="getStudentInfo" resultMap="StudentTeacher">

        select * from student;

    </select>

    <resultMap id="StudentTeacher" type="Student">

        <result property="id" column="id"/>

        <result property="name" column="name"/>

        <!--复杂的属性,我们需要单独处理

            对象:association

            集合:collection

        -->

        <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>

    </resultMap>

    <select id="getTeacher" resultType="Teacher">

        select * from teacher where id = #{id};

    </select>

</mapper>

2. 按照结果嵌套处理(连表查询)

<!--===========================================================-->

<!--按照结果嵌套处理-->

<select id="getStudentInfo2" resultMap="StudentTeacher2">

    select s.id as sid, s.name as sname, t.name as tname

    from student as s, teacher as t

    where s.tid = t.id;

</select>

<resultMap id="StudentTeacher2" type="Student">

    <result property="id" column="sid"/>

    <result property="name" column="sname"/>

    <association property="teacher" javaType="Teacher">

        <result property="name" column="tname"/>

    </association>

</resultMap>

4. 一对多处理

1. 环境搭建,修改实体类

package com.wang.pojo;

import lombok.AllArgsConstructor;

import lombok.Data;

import lombok.NoArgsConstructor;

@Data

@AllArgsConstructor

@NoArgsConstructor

public class Student {

    private int id;

    private String name;

    private int tid;

}

package com.wang.pojo;

import lombok.AllArgsConstructor;

import lombok.Data;

import lombok.NoArgsConstructor;

import java.util.List;

@Data

@NoArgsConstructor

@AllArgsConstructor

public class Teacher {

    private int id;

    private String name;

    //一个老师拥有多个学生

    private List<Student> students;

}

2. 按照结果嵌套处理

<!--按结果嵌套查询-->

<select id="getTeacherById" resultMap="TeacherStudent">

    select s.id as sid, s.name as sname, t.name as tname, t.id as tid

    from student as s

    inner join teacher as t

    on s.tid = t.id and tid = #{tid};

</select>

<!--复杂的属性,我们需要单独处理

    对象: association     集合: collection

    JavaType="" 指定属性的类型!

    对于集合中的泛型信息,我们使用ofType获取!

-->

<resultMap id="TeacherStudent" type="Teacher">

    <result property="id" column="tid"/>

    <result property="name" column="tname"/>

    <collection property="students" ofType="Student">

        <result property="id" column="sid"/>

        <result property="name" column="sname"/>

        <result property="tid" column="tid"/>

    </collection>

</resultMap>

3. 按照查询嵌套处理

<select id="getTeacherById2" resultMap="TeacherStudent2">

    select *

    from teacher

    where id = #{tid};

</select>

<resultMap id="TeacherStudent2" type="Teacher">

    <collection property="students" javaType="ArrayLi st" ofType="Student" select="getStudentByTeacherId" column="id"/>

</resultMap>

<select id="getStudentByTeacherId" resultType="Student">

    select *

    from student

    where tid = #{tid}

</select>

此种方式,用于查询的字段必须显示指定才能正确显示!

两种显式指定的方式:

在SQL语句中指定
在结果集映射中使用result标签通过property和column指定

5. 小结

association 关联多对一
collection 集合一对多
javaType & ofType
1. javaType 用来指定实体类中属性的类型
2. ofType 用来指定映射到List或者集合中的pojo类型,泛型中的约束类型

6. 注意点

保证SQL的可读性,尽量保证通俗易懂
注意一对多和多对一中,属性名和字段的问题!
如果问题不好排查错误,可以使用日志,建议使用Log4j

巴特西

Mybatis-多对一和一对多

多对一和一对多

1. 复杂表的构建

2. 测试环境搭建

1. 导入Lombok

2. 新建实体类

3. 建立Mapper接口

4. 建立Mapper.xml文件

5. 在核心配置文件汇总绑定Mapper接口或者文件

6. 测试查询是否成功

3. 多对一处理

1. 按照查询嵌套处理(子查询)

2. 按照结果嵌套处理(连表查询)

4. 一对多处理

1. 环境搭建,修改实体类

2. 按照结果嵌套处理

3. 按照查询嵌套处理

5. 小结

6. 注意点

最新文章

热门文章