Mybatis处理一对多映射查询

最新推荐文章于 2023-05-20 17:04:35 发布

robin912

最新推荐文章于 2023-05-20 17:04:35 发布

阅读量229

点赞数

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

本文链接：https://blog.csdn.net/robin912/article/details/80010115

版权

java 专栏收录该内容

41 篇文章 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" />
            <!-- <result column="department" jdbcType="VARCHAR" property="department" /> -->
        </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一致．

import com.example.demo.model.Department2;
import com.example.demo.model.UserInfo2;

public interface UserInfo2Mapper {

    UserInfo2 selectUserInfo2(String uid);

    Department2 selectDepartment2(String uid);

    List<HashMap> queryDepartment(String uid);
}

调用

    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来保存结果集．