Mybatis处理一对多映射查询

忘是亡心i 2022-05-27 13:43 259阅读 0赞

# Mybatis处理一对多映射查询 #

Mybatis处理sql查询比较灵活，既可以使用orm创建实体类来保存结果集，也可以直接使用HashMap保存结果集．

## 数据库表结构 ##

use wt;
    
    SET FOREIGN_KEY_CHECKS=0;
    
    -- ----------------------------
    -- Table structure for user_info
    -- ----------------------------
    DROP TABLE IF EXISTS `user_info`;
    CREATE TABLE `user_info` ( `uid` varchar(32) COLLATE utf8_unicode_ci NOT NULL, `login_name` varchar(40) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `username` varchar(100) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `worker_id` varchar(16) COLLATE utf8_unicode_ci DEFAULT '', `password` varchar(40) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `position` varchar(16) COLLATE utf8_unicode_ci DEFAULT '', `department` varchar(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `email` varchar(255) COLLATE utf8_unicode_ci DEFAULT '', `status` int(11) NOT NULL, `is_admin` int(11) DEFAULT NULL, `reserv2` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL, `reserv1` int(11) DEFAULT NULL, `modifytime` datetime DEFAULT NULL, PRIMARY KEY (`uid`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
    create unique index idx_user_username on user_info(login_name);
    
    INSERT INTO `user_info` VALUES ('1', 'login_name1', 'username1', '000', '1', '0', '1', 'robin@email.com', 1, 1, '', 0, '2011-08-10 10:02:44');
    INSERT INTO `user_info` VALUES ('2', 'login_name2', 'username2', '001', '1', '0', '1', 'robin@email.com', 1, 1, '', 0, '2011-08-10 10:02:44');
    INSERT INTO `user_info` VALUES ('3', 'login_name3', 'username3', '002', '1', '0', '2', 'robin@email.com', 1, 1, '', 0, '2011-08-10 10:02:44');
    
    
    DROP TABLE IF EXISTS `department`;
    CREATE TABLE `department` ( `uid` varchar(32) COLLATE utf8_unicode_ci NOT NULL, `department_name` varchar(40) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `is_active` int(3) NOT NULL DEFAULT 0, `modifytime` datetime DEFAULT NULL, PRIMARY KEY (`uid`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
    
    insert into `department` values('1', '软件组', 1, '2018-04-12 12:12:12');
    insert into `department` values('2', '导航组', 1, '2018-04-12 12:12:12');
    insert into `department` values('3', '算法组', 1, '2018-04-12 12:12:12');
    insert into `department` values('4', '机械组', 1, '2018-04-12 12:12:12');

## 使用实体类保存结果 ##

### mapper.xml编写sql ###

mapper.xml需要定义resultMap，使用`collection`处理映射关系

<resultMap id="DepartmentResultMap" type="com.example.demo.model.Department2">
            <id column="uid" jdbcType="VARCHAR" property="uid" />
            <result column="department_name" jdbcType="VARCHAR" property="departmentName" />
            <result column="is_active" jdbcType="INTEGER" property="isActive" />
            <result column="modifytime" jdbcType="TIMESTAMP" property="modifytime" />
            <collection property="users" ofType="UserInfo2" column="department">
                <id property="uid" column="userId"/>
                <result column="login_name" property="loginName"/>            
                <result column="username" jdbcType="VARCHAR" property="username" />
                <result column="worker_id" jdbcType="VARCHAR" property="workerId" />
                <result column="password" jdbcType="VARCHAR" property="password" />
                <result column="position" jdbcType="VARCHAR" property="position" />
                
            </collection>
        </resultMap>
    
    <select id="selectDepartment2" parameterType="java.lang.String" resultMap="DepartmentResultMap">        
    select d.*,u.uid as userId ,u.* from user_info u, department d where u.department = d.uid and d.uid=#{uid}      
    </select>

### 实体类 ###

package com.example.demo.model;
    
    import java.util.Date;
    import java.util.List;
    
    public class Department2 { 
        private String uid;
    
        private String departmentName;
    
        private Integer isActive;
    
        private Date modifytime;
    
        private List<UserInfo2> users;
    
        public String getUid() {
            return uid;
        }
    
        public void setUid(String uid) {
            this.uid = uid == null ? null : uid.trim();
        }
    
        public String getDepartmentName() {
            return departmentName;
        }
    
        public void setDepartmentName(String departmentName) {
            this.departmentName = departmentName == null ? null : departmentName.trim();
        }
    
        public Integer getIsActive() {
            return isActive;
        }
    
        public void setIsActive(Integer isActive) {
            this.isActive = isActive;
        }
    
        public Date getModifytime() {
            return modifytime;
        }
    
        public void setModifytime(Date modifytime) {
            this.modifytime = modifytime;
        }
    
        /** * @return the users */
        public List<UserInfo2> getUsers() {
            return users;
        }
    
        /** * @param users the users to set */
        public void setUsers(List<UserInfo2> users) {
            this.users = users;
        }
    
        public String toString() {
            StringBuilder sb = new StringBuilder();
            sb.append(this.getDepartmentName());
            for (UserInfo2 u : users) {
                u.setDepartment(this);
                sb.append(u.toString());
            }
            return sb.toString();
        }
    }

### mapper接口 ###

接口中的方法名，需要和mapper.xml中标签中的id一致．

import com.example.demo.model.Department2;
    import com.example.demo.model.UserInfo2;
    
    public interface UserInfo2Mapper { 
    
        UserInfo2 selectUserInfo2(String uid);
    
        Department2 selectDepartment2(String uid);
    }

### 调用 ###

Department2 d = userInfo2Mapper.selectDepartment2("1");
        System.out.println("get department: " + d);

## 使用HashMap保存结果集 ##

使用上面的步骤，需要定义实体类，以及定义resultMap，比较麻烦．  
使用HashMap只需要增加sql语句，比较简单．

### mapper.xml定义sql ###

主要使用HashMap时，需要把resultMap修改为resultType属性．

<select id="queryDepartment" parameterType="java.lang.String" resultType="java.util.HashMap">
            select d.*,u.uid as userId ,u.* from user_info u, department d where u.department = d.uid and d.uid=#{uid}      
    </select>

### mapper接口 ###

接口中的方法名，需要和mapper.xml中标签中的id一致．

### 调用 ###

List<HashMap> list1 = userInfo2Mapper.queryDepartment("1");
        for (HashMap dep: list1) {
            System.out.println("get dep: " + dep);
        }

使用实体类保存结果集遇到一个问题，department.users是有数据的，但是department.users\[0\].department会是空数据，这里牵涉到嵌套填充数据，在mybatis查询到数据后，必须手动处理department.users\[i\].department对应的数据，比较麻烦．  
在实际使用中，多使用HashMap来保存结果集．