【SSM - MyBatis篇07】一对多关联查询实现

淡淡的烟草味﹌ 2022-12-10 14:51 135阅读 0赞

### 文章目录 ###

*   *  案例描述
     *   *  1. 创建users表(一)和task表(多)
         *  2. 创建javabean对象
         *  3. 创建dao层接口
         *  4. 创建连接数据库的属性文件db.properties(键值对形式)
         *  5. spring整合MyBatis，核心配置文件applicationContext.xml
         *  6. 创建映射文件mapper.xml
         *  7. 创建测试类
         *   *  测试结果

## 案例描述 ##

一个任务由一个人完成，一个人可以完成多个任务。人和任务之间的关系是**一对多**的关联关系。创建user人物表和task任务表，通过resultMap映射的**一对多关联映射collection**实现。（这里设计表users和表task，task表有外键userId映射关联user表主键id)。

### 1. 创建users表(一)和task表(多) ###

-- ----------------------------
    -- users表，主键id自动增长
    -- ----------------------------
    DROP TABLE IF EXISTS `users`;
    CREATE TABLE `users` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `username` varchar(30) NOT NULL,
      `password` varchar(30) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=100050 DEFAULT CHARSET=utf8;
    
    INSERT INTO `users` VALUES ('100001', '用户100001', '123456');
    INSERT INTO `users` VALUES ('100002', '用户100002', '123679');
    INSERT INTO `users` VALUES ('100003', '用户100003', '8888888');
    INSERT INTO `users` VALUES ('100004', '张三', '8888888');
    INSERT INTO `users` VALUES ('100005', '李四', '8888888');
    INSERT INTO `users` VALUES ('100007', '王五', '8888888');
    INSERT INTO `users` VALUES ('100008', '赵六', '8888888');
    
    
    -- ----------------------------
    -- task表，主键自动增长，外键userId关联映射users表的主键id
    -- ----------------------------
    DROP TABLE IF EXISTS `task`;
    CREATE TABLE `task` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `taskName` varchar(100) DEFAULT NULL,
      `userId` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `FK_users_task` (`userId`),
      CONSTRAINT `FK_users_task` FOREIGN KEY (`userId`) REFERENCES `users` (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Records of task
    -- ----------------------------
    INSERT INTO `task` VALUES ('1', '软件开发', '100001');
    INSERT INTO `task` VALUES ('2', '软件测试', '100002');
    INSERT INTO `task` VALUES ('3', '项目经理', '100003');
    INSERT INTO `task` VALUES ('4', '集成开发', '100001');

### 2. 创建javabean对象 ###

user和task是一对多的关联关系，在User类中要有一个Task集合来存储(taskList)

public class Task { 
        private Integer id;
        private String taskName;
    	//省略getter/setter等方法
    }
    
    // 一个user有多个Task
    public class User { 
    
        private Integer id;
        private String username;
        private String password;
    // 一的一方保留多的一方信息
        private List<Task> taskList;
        //省略gettser/setter等方法
    }

### 3. 创建dao层接口 ###

@Repository
    public interface TaskMapper { 
    
        //通过useId查询当前user的task列表
        public List<Task> getTaskListByUid(Integer uid);
    }
    
    @Repository
    public interface UserMapper { 
    
        //1.一条sql查询(通过内连接、外连接)
        User getUserTaskById(int id);
        //2.嵌套查询，分布查询加载【推荐】
        User getUserTaskById2(int id);
    }

### 4. 创建连接数据库的属性文件db.properties(键值对形式) ###

jdbc.driver = com.mysql.jdbc.Driver
    jdbc.url = jdbc:mysql://localhost:3306/mybatis?useUnicode=true&characterEncoding=UTF-8
    jdbc.username = root
    jdbc.password = 861221293

### 5. spring整合MyBatis，核心配置文件applicationContext.xml ###

<?xml version="1.0" encoding="UTF-8"?>
    <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context" xmlns:mybatis="http://mybatis.org/schema/mybatis-spring" xmlns:tx="http://www.springframework.org/schema/tx" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd http://mybatis.org/schema/mybatis-spring http://mybatis.org/schema/mybatis-spring.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd">
    
    
        
        <context:property-placeholder location="classpath*:db.properties" />
    
        
        <context:component-scan base-package="com.xgf.correlation.one_to_many"/>
    
        
        <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
            <property name="username" value="${jdbc.username}"/>
            <property name="password" value="${jdbc.password}"/>
            <property name="url" value="${jdbc.url}"/>
            <property name="driverClassName" value="${jdbc.driver}"/>
        </bean>
    
        
        
        <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
            <property name="dataSource" ref="dataSource" />
        </bean>
    
        
        <mybatis:scan base-package="com.xgf.correlation.one_to_many"/>
    
    </beans>

### 6. 创建映射文件mapper.xml ###

1.  单条sql语句，一次查询出
2.  嵌套查询（这个才需要TaskMapper.xml映射）

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.xgf.correlation.one_to_many.dao.UserMapper">
    
        
       <resultMap id="userTaskMap1" type="com.xgf.correlation.one_to_many.bean.User" autoMapping="true">
            <id column="id" property="id"/>
            <result property="username" column="username"/>
    
            <collection property="taskList" ofType="com.xgf.correlation.one_to_many.bean.Task" >
                <id column="tid" property="id"/>
                <result column="taskName" property="taskName"/>
            </collection>
        </resultMap>
    
        <select id="getUserTaskById" resultMap="userTaskMap1">
            SELECT u.id,u.username,u.password,t.id tid,t.taskName
            from users u inner join task t on u.id = t.userId
            where u.id=#{id}
        </select>
    
        
        
        
        <resultMap id="userTaskMap2" type="com.xgf.correlation.one_to_many.bean.User" autoMapping="true">
             <id column="id" property="id"/>
            <result column="username" property="username"/>
            <result column="password" property="password"/>
            <collection property="taskList" ofType="com.xgf.correlation.one_to_many.bean.Task" column="id" foreignColumn="userId" select="com.xgf.correlation.one_to_many.dao.TaskMapper.getTaskListByUid" fetchType="lazy"/>
    
        </resultMap>
    
        <select id="getUserTaskById2" parameterType="int" resultMap="userTaskMap2">
            select id,username,password
            from users
            where id = #{id}
        </select>
    
    </mapper>

TaskMapper.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.xgf.correlation.one_to_many.dao.TaskMapper">
    
        <select id="getTaskListByUid" parameterType="int" resultType="com.xgf.correlation.one_to_many.bean.Task">
    
            select id,taskName,userId from task where userId=#{userId}
        </select>
    
    </mapper>

### 7. 创建测试类 ###

//测试类 
    public class TestOneToMany { 
    
        private static ApplicationContext applicationContext = null;
        private static UserMapper userMapper = null;
    
        //静态代码块 只加载一次
        static { 
            //加载配置文件
            applicationContext = new ClassPathXmlApplicationContext("com/xgf/correlation/one_to_many/config/applicationContext.xml");
            //获取bean的两种方式
            // 1.类名首字母小写
    // studentMapper = (StudentMapper) applicationContext.getBean("userMapper");
            // 2.类.class
            userMapper = (UserMapper) applicationContext.getBean(UserMapper.class);
        }
    
        //第一种(getUserTaskById)直接通过id一条SQL语句查询
        @Test
        public void test01(){ 
            System.out.println("===一条SQL语句查询 查找id为100001的user和他的task列表：===");
            User user = userMapper.getUserTaskById(100001);
            System.out.println(user);
        }
    
        //第二种(getUserTaskById2)通过嵌套查询实现
        @Test
        public void test02(){ 
            System.out.println("===嵌套查询 查找id为100002的user和他的task列表===");
            User user = userMapper.getUserTaskById2(100002);
            System.out.println(user);
        }
    }

#### 测试结果 ####

> test01测试getUserTaskById(一条sql查询)
> 
> > User\{id=100001, username=‘用户100001’, password=‘123456’, taskList=\[Task\{编号=1, 任务名=‘软件开发’\}, Task\{编号=4, 任务名=‘集成开发’\}\]\}
> 
> test02测试getUserTaskById2(嵌套查询)
> 
> > User\{id=100002, username=‘用户100002’, password=‘123679’, taskList=\[Task\{编号=2, 任务名=‘软件测试’\}\]\}