Native sql executing a union query does not give the same result as mybatis executing a union query?

41 views
Skip to first unread message

Cute Animal TV

unread,
Jul 6, 2024, 6:02:13 AM7/6/24
to mybatis-user
The following data tables and data are available:

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for users
-- ----------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `user_id` varchar(64) NOT NULL COMMENT '用户id',
  `username` varchar(50) NOT NULL COMMENT '用户名',
  `password` varchar(128) NOT NULL COMMENT '用户密码',
  `user_alias` varchar(50) DEFAULT NULL COMMENT '用户别名',
  `is_enable` int(1) NOT NULL DEFAULT '1' COMMENT '账号是否可用(1:可用  0:禁用)',
  `sex` int(1) NOT NULL DEFAULT '1' COMMENT '性别(0:女 1:男)',
  `cell_phone_number` varchar(15) DEFAULT NULL COMMENT '手机号',
  `address` varchar(100) DEFAULT NULL COMMENT '住址',
  `entry_date` varchar(25) DEFAULT NULL COMMENT '登录日期',
  `last_login` varchar(25) DEFAULT NULL COMMENT '上次登录日期',
  `email` varchar(100) DEFAULT NULL COMMENT '电子邮件',
  `is_super` int(1) DEFAULT '0' COMMENT '是否超级管理员(0:否  1:是)',
  `create_time` varchar(25) DEFAULT NULL COMMENT '创建日期',
  `modify_time` varchar(25) DEFAULT NULL COMMENT '修改日期',
  `remarks` varchar(255) DEFAULT NULL COMMENT '备注信息',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表';

-- ----------------------------
-- Records of users
-- ----------------------------
INSERT INTO `users` VALUES ('00dc3d392604422a91850d6c4f9071ec', '司马光', 'd93ae65992caf6a8751e334d0a716ad8', '司马光', '1', '1', '15678905678', '广东省广州天河区棠下街18号', '2024-07-06 16:41:55', '2024-07-06 16:39:15', 'sima...@qq.com', '0', '2023-03-04 23:02:46', '2024-07-06 15:39:17', '总部采购部经理。');

-- ----------------------------
-- Table structure for organization
-- ----------------------------
DROP TABLE IF EXISTS `organization`;
CREATE TABLE `organization` (
  `org_id` varchar(64) NOT NULL COMMENT '组织ID',
  `org_name` varchar(100) NOT NULL COMMENT '组织名称',
  `org_type` varchar(20) NOT NULL COMMENT '组织类型(1:总公司 2:分公司 3:部门)',
  `org_code` varchar(20) DEFAULT NULL COMMENT '组织编码',
  `parent_id` varchar(64) NOT NULL COMMENT '上级组织ID',
  `position` int(3) DEFAULT NULL COMMENT '节点位置,越小越靠前',
  `icon` varchar(50) DEFAULT NULL COMMENT '字体图标',
  `is_super` int(2) DEFAULT '0' COMMENT '是否是超级管理员节点(1 是 0 不是)',
  PRIMARY KEY (`org_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='组织表(有上下级关系)';

-- ----------------------------
-- Records of organization
-- ----------------------------
INSERT INTO `organization` VALUES ('9ffc66b087a145348e52028a0d64bf3b', '玉兔公司总部', '1', '', '-1', '1', 'layui-icon layui-icon-home', '0');
INSERT INTO `organization` VALUES ('3e542424f7ee45d988aab05152400328', '采购部', '3', '', '9ffc66b087a145348e52028a0d64bf3b', '0', 'layui-icon layui-icon-home', '0');


SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for post
-- ----------------------------
DROP TABLE IF EXISTS `post`;
CREATE TABLE `post` (
  `post_id` varchar(64) NOT NULL COMMENT '职位ID',
  `post_name` varchar(20) NOT NULL COMMENT '职位名称',
  `post_code` varchar(64) DEFAULT NULL COMMENT '职位编码',
  `parent_id` varchar(64) NOT NULL COMMENT '上级职位ID',
  `org_id` varchar(64) NOT NULL COMMENT '所属组织ID',
  `icon` varchar(50) DEFAULT NULL COMMENT '字体图标',
  `position` int(3) DEFAULT NULL COMMENT '节点位置,越小越靠前',
  PRIMARY KEY (`post_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='职位表';

-- ----------------------------
-- Records of post
-- ----------------------------
INSERT INTO `post` VALUES ('ee4ac9b12a054088bb86687d50f8ddb7', '采购经理', '', '-1', '3e542424f7ee45d988aab05152400328', 'layui-icon layui-icon-user', '2');

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for user_post
-- ----------------------------
DROP TABLE IF EXISTS `user_post`;
CREATE TABLE `user_post` (
  `user_id` varchar(64) NOT NULL COMMENT '用户ID',
  `post_id` varchar(64) NOT NULL COMMENT '职位ID',
  PRIMARY KEY (`user_id`,`post_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户-职位关联表';

-- ----------------------------
-- Records of user_post
-- ----------------------------
INSERT INTO `user_post` VALUES ('00dc3d392604422a91850d6c4f9071ec', 'ee4ac9b12a054088bb86687d50f8ddb7');

The java entity class has:

User.java
public class User implements Serializable {

    private static final long serialVersionUID = 4513862602468058360L;
    private String userId;
    /**
     * 用户名
     */
    private String userName;
    /**
     * 用户别名
     */
    private String userAlias;
    /**
     * 用户性别 0:女 1:男
     */
    private Integer sex;
    /**
     * 用户手机号码
     */
    private String cellPhoneNumber;
    /**
     * 用户住址
     */
    private String address;
    /**
     * 用户登录时间
     */
    private String entryDate;
    /**
     * 用户上次登录时间
     */
    private String lastLogin;
    /**
     * 用户是否是超级管理员 1:是 0:否
     */
    private Integer isSuper;

    /**
     * 密码
     */
    private String password;
    /**
     * 邮箱地址
     */
    private String email;
    /**
     * 1:可用 0:不可用
     */
    private Integer isEnable;
    /***
     * 创建时间
     */
    private String createTime;
    /**
     * 修改时间
     */
    private String modifyTime;
    /**
     * 备注
     */
    private String remarks;

    private Post post;


    public String getUserId() {
return userId;
    }

    public void setUserId(String userId) {
this.userId = userId;
    }

    public String getUserName() {
return userName;
    }

    public void setUserName(String userName) {
this.userName = userName;
    }

    public String getPassword() {
return password;
    }

    public void setPassword(String password) {
this.password = password;
    }

    public String getEmail() {
return email;
    }

    public void setEmail(String email) {
this.email = email;
    }

    public String getRemarks() {
return remarks;
    }

    public void setRemarks(String remarks) {
this.remarks = remarks;
    }

    public String getCreateTime() {
return createTime;
    }

    public void setCreateTime(String createTime) {
this.createTime = createTime;
    }

    public String getModifyTime() {
return modifyTime;
    }

    public void setModifyTime(String modifyTime) {
this.modifyTime = modifyTime;
    }

    public String getUserAlias() {
return userAlias;
    }

    public void setUserAlias(String userAlias) {
this.userAlias = userAlias;
    }

    public String getCellPhoneNumber() {
return cellPhoneNumber;
    }

    public void setCellPhoneNumber(String cellPhoneNumber) {
this.cellPhoneNumber = cellPhoneNumber;
    }

    public String getAddress() {
return address;
    }

    public void setAddress(String address) {
this.address = address;
    }

    public String getEntryDate() {
return entryDate;
    }

    public void setEntryDate(String entryDate) {
this.entryDate = entryDate;
    }

    public String getLastLogin() {
return lastLogin;
    }

    public void setLastLogin(String lastLogin) {
this.lastLogin = lastLogin;
    }

    public Integer getSex() {
return sex;
    }

    public void setSex(Integer sex) {
this.sex = sex;
    }

    public Integer getIsSuper() {
return isSuper;
    }

    public void setIsSuper(Integer isSuper) {
this.isSuper = isSuper;
    }

    public Integer getIsEnable() {
return isEnable;
    }

    public void setIsEnable(Integer isEnable) {
this.isEnable = isEnable;
    }



    public Post getPost() {
return post;
    }

    public void setPost(Post post) {
this.post = post;
    }

}

 Post.java
public class Post implements Serializable {
    private static final long serialVersionUID = -1256403720282748631L;
    /**
     * 职位ID
     */
    private String postId;
    /**
     * 职位名称
     */
    private String postName;
    /**
     * 职位编码
     */
    private String postCode;
    /**
     * 上级职位ID
     */
    private String parentId;
    /**
     * 父节点名称
     */
    private String parentName;
    /**
     * 所属组织ID
     */
    private String orgId;
    /**
     * 职位字体图标
     */
    private String icon;
    /**
     * 节点位置,最小值1,越小越靠前
     */
    private Integer position;

    public String getPostId() {
return postId;
    }

    public void setPostId(String postId) {
this.postId = postId;
    }

    public String getPostName() {
return postName;
    }

    public void setPostName(String postName) {
this.postName = postName;
    }

    public String getPostCode() {
return postCode;
    }

    public void setPostCode(String postCode) {
this.postCode = postCode;
    }

    public String getParentId() {
return parentId;
    }

    public void setParentId(String parentId) {
this.parentId = parentId;
    }

    public String getOrgId() {
return orgId;
    }

    public void setOrgId(String orgId) {
this.orgId = orgId;
    }

    public String getIcon() {
return icon;
    }

    public void setIcon(String icon) {
this.icon = icon;
    }

    public Integer getPosition() {
return position;
    }

    public void setPosition(Integer position) {
this.position = position;
    }

    public String getParentName() {
return parentName;
    }

    public void setParentName(String parentName) {
this.parentName = parentName;
    }

}

UserMapper.java
public interface UserMapper {
     List<User> queryUserDetail(Map<String, String> map);
}

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="UserMapper">
<!-- <cache /> -->
<resultMap id="userMap" type="User">
<id property="userId" column="user_id" />
<result property="userName" column="username" />
<result property="password" column="password" />
<result property="userAlias" column="user_alias" />
<result property="sex" column="sex" />
<result property="cellPhoneNumber" column="cell_phone_number" />
<result property="address" column="address" />
<result property="entryDate" column="entry_date" />
<result property="lastLogin" column="last_login" />
<result property="email" column="email" />
<result property="isSuper" column="is_super" />
<result property="isEnable" column="is_enable" />
<result property="createTime" column="create_time" />
<result property="modifyTime" column="modify_time" />
<result property="remarks" column="remarks" />
<association property="post" javaType="com.ucan.entity.Post">
<result property="postName" column="post_name" />
</association>
</resultMap>
<select id="queryUserDetail" resultMap="userMap">
SELECT u.user_id user_id,u.username
username,u.email email,u.cell_phone_number
cell_phone_number
,u.address
address,u.user_alias
user_alias,concat(o.org_name,'-',p.post_name)
post_name,u.create_time
create_time,u.modify_time
modify_time,
u.entry_date
entry_date,u.last_login
last_login,u.is_enable is_enable,u.sex
sex,u.remarks remarks from
users u
JOIN
user_post
up on
u.user_id=up.user_id JOIN post p on
up.post_id=p.post_id
JOIN
organization o
ON
o.org_id=p.org_id
<where>
<choose>
<when test="userId != null and userId !=''"> u.user_id=#{userId}
</when>
<otherwise> u.username=#{userName}
</otherwise>
</choose>
</where>
union
SELECT u.user_id user_id,u.username
username,u.email
email,u.cell_phone_number
cell_phone_number ,u.address
address,u.user_alias
user_alias,'无'
post_name,u.create_time
create_time,u.modify_time modify_time,
u.entry_date
entry_date,u.last_login
last_login,u.is_enable
is_enable,u.sex
sex,u.remarks
remarks from users u
<where>
<choose>
<when test="userId != null and userId !=''"> u.user_id=#{userId}
</when>
<otherwise> u.username=#{userName}
</otherwise>
</choose>
</where>

</select>
</mapper>

Why does the following sql statement return two records when executed in a command line window, but only one record when executed in mybatis? But if the entire select statement does not query the u.user_id user_id attribute, i.e., something like SELECT u.user_id user_id,u.username username,u.email email..., the result of the query is correct.I looked at the mybatis source code, and I found that the storeObject(resultHandler, resultContext, rowValue, parentMapping, resultSet) method of the handleRowValuesForNestedResultMap method of DefaultResultSetHandler is executed only once, and the properties in the Post of the first User object in the ResultSet result set are overwritten by the Post properties of the subsequent parsed User objects. 
Is there no problem with the logic of the DefaultResultSetHandler#handleRowValuesForNestedResultMap method? Looking forward to mybatis developers to solve this problem, thanks!

The sql query statement is as follows:
SELECT u.user_id user_id,u.username username,u.email email,u.cell_phone_number cell_phone_number ,u.address address,
u.user_alias user_alias,concat(o.org_name,'-',p.post_name) post_name,u.create_time create_time,u.modify_time modify_time,
u.entry_date entry_date,u.last_login last_login,u.is_enable is_enable,u.sex sex,u.remarks remarks from users u JOIN
user_post up on u.user_id=up.user_id JOIN post p on up.post_id=p.post_id JOIN organization o ON o.org_id=p.org_id WHERE
u.user_id='00dc3d392604422a91850d6c4f9071ec'
 union
SELECT u.user_id user_id,u.username username,u.email email,u.cell_phone_number cell_phone_number ,u.address address,
u.user_alias user_alias,'无' post_name,u.create_time create_time,u.modify_time modify_time, u.entry_date entry_date,u.last_login last_login,
u.is_enable is_enable,u.sex sex,u.remarks remarks from users u WHERE u.user_id='00dc3d392604422a91850d6c4f9071ec'


Reply all
Reply to author
Forward
0 new messages