多对一的处理_多对一的修改语句sql-CSDN博客

例如：对于学生而言，多个学生关联一个老师【多对一】；对于老师而言，一个老师集合很多学生【一对多】。

在这里插入图片描述

SQL：

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 `ftid` (`tid`),
	CONSTRAINT `ftid` 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');

1、测试环境搭建

导入Lombokjar包；

<!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.18</version>
        </dependency>

新建实体类Student，Teacher；

package com.beyond.pojo;

import lombok.Data;

@Data
public class Student {
    private int id;
    private String name;

    //学生需要关联一个老师
    private Teacher teacher;
}

package com.beyond.pojo;

import lombok.Data;

@Data
public class Teacher {
    private int id;
    private String name;
}

建立Mapper接口；

package com.beyond.dao;

import com.beyond.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);
}

建立Mapper.xml文件【注意：在resource目录将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.beyond.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.beyond.dao.TeacherMapper">

</mapper>

在核心配置文件中绑定注册我们的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:mybatis核心配置文件-->
<configuration>
    <!--引入外部配置文件-->
    <properties resource="db.properties">
        <property name="username" value="root"/>
    </properties>
    
    <settings>
        <setting name="logImpl" value="STDOUT_LOGGING"/>
    </settings>

    <!--可以给实体类起别名-->
    <typeAliases>
        <typeAlias type="com.beyond.pojo.Student" alias="Student"/>
        <typeAlias type="com.beyond.pojo.Teacher" alias="Teacher"/>
<!--        <package name="com.beyond.pojo"/>-->
    </typeAliases>

    <!--environments：环境-->
    <environments default="development">
        <environment id="development">
            <!--transactionManager：事务-->
            <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="${password}"/>
            </dataSource>
        </environment>
        <environment id="test">
            <!--transactionManager：事务-->
            <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="${password}"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper class="com.beyond.dao.TeacherMapper"/>
        <mapper class="com.beyond.dao.StudentMapper"/>
    </mappers>

</configuration>

测试查询是否成功。

import com.beyond.dao.TeacherMapper;
import com.beyond.pojo.Teacher;
import com.beyond.utils.MyBatisUtil;
import org.apache.ibatis.session.SqlSession;

public class MyTest {
    public static void main(String[] args) {
        SqlSession sqlSession = MyBatisUtil.getSqlSession();
        TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
        Teacher teacher = mapper.getTeacher(1);
        System.out.println(teacher);
        sqlSession.close();
    }
}

2、按照查询嵌套处理

编写Mapper接口；

package com.beyond.dao;

import com.beyond.pojo.Student;

import java.util.List;

public interface StudentMapper {
    //查询所有学生及对应老师的信息
    public List<Student> getStudent();
}

编写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.beyond.dao.StudentMapper">
    <!--方法一（按照查询嵌套处理（类似于子查询））
    思路：
    1、查询所有的学生信息；
    2、根据查询出来的学生的tid，寻找对应的老师
    -->
    <select id="getStudent" resultMap="StudentTeacher">
        select * from student s
    </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>

测试查询是否成功。

import com.beyond.dao.StudentMapper;
import com.beyond.dao.TeacherMapper;
import com.beyond.pojo.Student;
import com.beyond.pojo.Teacher;
import com.beyond.utils.MyBatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;

public class MyTest {
    @Test
    public void getStudentTest(){
        SqlSession sqlSession = MyBatisUtil.getSqlSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        List<Student> studentList = mapper.getStudent();
        for (Student student:studentList) {
            System.out.println(student);
        }
        sqlSession.close();
    }
}

3、按照结果嵌套处理（推荐）

编写Mapper接口；

package com.beyond.dao;

import com.beyond.pojo.Student;

import java.util.List;

public interface StudentMapper {
    //查询所有学生及对应老师的信息
    public List<Student> getStudent2();
}

编写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.beyond.dao.StudentMapper">
    <!--方法二：按照结果嵌套处理（类似于联表查询）-->
    <select id="getStudent2" resultMap="StudentTeacher2">
        select s.id sid,s.name sname,t.name tname
        from student s,teacher 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>

</mapper>

测试查询是否成功。

import com.beyond.dao.StudentMapper;
import com.beyond.dao.TeacherMapper;
import com.beyond.pojo.Student;
import com.beyond.pojo.Teacher;
import com.beyond.utils.MyBatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;

public class MyTest {
    @Test
    public void getStudentTest2(){
        SqlSession sqlSession = MyBatisUtil.getSqlSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        List<Student> studentList = mapper.getStudent2();
        for (Student student:studentList) {
            System.out.println(student);
        }
        sqlSession.close();
    }
}