Mybatis关联查询 -- 一对多、多对一查询详解

最新推荐文章于 2025-11-06 18:24:12 发布

原创最新推荐文章于 2025-11-06 18:24:12 发布 · 665 阅读

0 ·

CC 4.0 BY-SA版权

MyBatis 专栏收录该内容

1 篇文章

订阅专栏

本文详细探讨Mybatis中的一对多和多对一关联查询。通过实例解析XML配置，展示了MESSAGE_MANAGE和MESSAGE_RECEIVER两个表的关联操作。文章旨在帮助开发者理解并熟练运用Mybatis的关联查询功能。

总述：基于平时在开发过程中dao、bean和XML文件都是自动生成的，但很多仅限于单表，今天记录一下mybatis表与表之间的关联查询，本文主要讲述一对多和多对一的情况。

为了讲解的需要，笔者创建了两个表MESSAGE_MANAGE和MESSAGE_RECEIVER，并创建JavaBean实体，如下所示：

MessageManage1.class

public class MessageManage1 {

    private String fdObjectid;
	
	private String content;
	
	private String sendState;
	
	public List<MessageReceiver1> messageReceiver1s;

	public String getFdObjectid() {
		return fdObjectid;
	}

	public void setFdObjectid(String fdObjectid) {
		this.fdObjectid = fdObjectid;
	}

	public String getContent() {
		return content;
	}

	public void setContent(String content) {
		this.content = content;
	}

	public String getSendState() {
		return sendState;
	}

	public void setSendState(String sendState) {
		this.sendState = sendState;
	}

	public List<MessageReceiver1> getMessageReceiver1s() {
		return messageReceiver1s;
	}

	public void setMessageReceiver1s(List<MessageReceiver1> messageReceiver1s) {
		this.messageReceiver1s = messageReceiver1s;
	}

	@Override
	public String toString() {
		return "MessageManage1 [fdObjectid=" + fdObjectid + ", content="
				+ content + ", sendState=" + sendState + ", messageReceiver1s="
				+ messageReceiver1s + "]";
	}
	
}

MessageReceiver1.class

public class MessageReceiver1 {
	
	private String fdObjectid;
	
	private String messageId;
	
	private String userId;
	
	private String realName;
	
	private String mobile;
	
	private String replyContent;
	
	private String isDel;
	
	private String createTime;

	private MessageManage1 messageManage1;

	public String getFdObjectid() {
		return fdObjectid;
	}

	public void setFdObjectid(String fdObjectid) {
		this.fdObjectid = fdObjectid;
	}

	public String getMessageId() {
		return messageId;
	}

	public void setMessageId(String messageId) {
		this.messageId = messageId;
	}

	public String getUserId() {
		return userId;
	}

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

	public String getRealName() {
		return realName;
	}

	public void setRealName(String realName) {
		this.realName = realName;
	}

	public String getMobile() {
		return mobile;
	}

	public void setMobile(String mobile) {
		this.mobile = mobile;
	}

	public String getReplyContent() {
		return replyContent;
	}

	public void setReplyContent(String replyContent) {
		this.replyContent = replyContent;
	}

	public String getIsDel() {
		return isDel;
	}

	public void setIsDel(String isDel) {
		this.isDel = isDel;
	}

	public String getCreateTime() {
		return createTime;
	}

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

	public MessageManage1 getMessageManage1() {
		return messageManage1;
	}

	public void setMessageManage1(MessageManage1 messageManage1) {
		this.messageManage1 = messageManage1;
	}

	@Override
	public String toString() {
		return "MessageReceiver1 [fdObjectid=" + fdObjectid + ", messageId="
				+ messageId + ", userId=" + userId + ", realName=" + realName
				+ ", mobile=" + mobile + ", replyContent=" + replyContent
				+ ", isDel=" + isDel + ", createTime=" + createTime
				+ ", messageManage1=" + messageManage1 + "]";
	}
	
}

下面我们进入这次讲解的主题，有关于MyBatis的关联查询(详解请看相关注释说明)：

一、MyBatis关联查询的一对多案例，相关XML文件配置如下：

<!-- 高级查询、一对多(第一种写法) -->
    <resultMap type="MessageManage1" id="MessageManageResultMap">
    	<id     property="fdObjectid"         column="fd_objectid"       />
		<result property="content"   column="content" />
		<result property="sendState"   column="send_state" />
		
		<!-- JavaType和ofType都是用来指定对象类型的，
		但是JavaType是用来指定pojo中属性的类型，
		而ofType指定的是映射到list集合属性中pojo的类型  -->
		
		<!-- collection中的column是子表中与父表关联的外键 -->
		<collection property="messageReceiver1s" ofType="MessageReceiver1" column="message_id">
			<!-- 这里的column对应的是下面查询的别名，而不是表字段名.property对应JavaBean中的属性名 -->
			<id column="child_id" property="fdObjectid"/>
			<result column="message_id" property="messageId"/>
			<result column="user_id" property="userId"/>
			<result column="real_name" property="realName"/>
			<result column="mobile" property="mobile"/>
			<result property="replyContent"   column="reply_content" />
		    <result property="isDel"   column="is_del" />
		    <result property="createTime"   column="create_time" />
		</collection>
    </resultMap>
    
    <!-- 查询短信下的接收人信息 -->
    <select id="selectReceiverOfMessage" parameterType="MessageManage1" resultMap="MessageManageResultMap">
    	SELECT
    	        a.FD_OBJECTID,
		        a.CONTENT   ,
		        a.SEND_STATE,
		        b.FD_OBJECTID AS CHILD_ID,
		        b.MESSAGE_ID,
		        b.USER_ID   ,
		        b.REAL_NAME ,
		        b.MOBILE,
		        b.REPLY_CONTENT,
		        b.IS_DEL,
		        b.CREATE_TIME
		FROM
		        MESSAGE_MANAGE a
		LEFT JOIN MESSAGE_RECEIVER b
		ON
		        b.MESSAGE_ID = a.FD_OBJECTID
		WHERE
		        b.IS_DEL = 0
		    AND a.IS_DEL = 0
    </select>

<!-- 高级查询、一对多(第二种写法) -->
    <resultMap type="MessageManage1" id="MessageManageResultMap1">
    	<id     property="fdObjectid"         column="fd_objectid"       />
		<result property="content"   column="content" />
		<result property="sendState"   column="send_state" />
		
		<!-- collection里的column为select中子查询的条件列名,在这里由父表的FD_OBJECTID和字表的MESSAGE_ID关联查询 -->
		<collection property="messageReceiver1s" ofType="MessageReceiver1" column="fd_objectid" select="selectReceiverById">
		</collection>
    </resultMap>
    
    <select id="selectMessageManage" parameterType="MessageManage1" resultMap="MessageManageResultMap1">
    	SELECT 
    	        a.FD_OBJECTID,
		        a.CONTENT   ,
		        a.SEND_STATE
		FROM
		        MESSAGE_MANAGE a
		WHERE
		        a.IS_DEL = 0
    </select>
    
    <!-- 注意这里返回是实体,要用resultType -->
    <select id="selectReceiverById" parameterType="String" resultType="MessageReceiver1">
        SELECT
                b.FD_OBJECTID,
    	        b.MESSAGE_ID,
		        b.USER_ID   ,
		        b.REAL_NAME ,
		        b.MOBILE,
		        b.REPLY_CONTENT,
		        b.IS_DEL,
		        b.CREATE_TIME
        FROM
        		MESSAGE_RECEIVER b
   		WHERE
   		        b.IS_DEL = 0
   		    AND b.MESSAGE_ID = #{fdObjectid}  		
    </select>

二、MyBatis关联查询的多对一案例，相关XML文件配置如下：

<!-- 高级查询、多对一 -->
	<resultMap type="MessageReceiver1" id="MessageReceiverResultMap">
		<id column="fdobject_id" property="fdObjectid"/>
		<result column="message_id" property="messageId"/>
		<result column="user_id" property="userId"/>
		<result column="real_name" property="realName"/>
		<result column="mobile" property="mobile"/>
		<result property="replyContent"   column="reply_content" />
	    <result property="isDel"   column="is_del" />
	    <result property="createTime"   column="create_time" />
	    <!-- 这里要注意的是column对应的是子表中的外键，而且需是表字段名 -->
	    <association property="messageManage1" javaType="MessageManage1" column="message_id" select="selectMessageManageById">
	    </association>
	</resultMap>
	
	<select id="selectMessageReceiver" resultMap="MessageReceiverResultMap">
		SELECT
				a.FD_OBJECTID,
    	        a.MESSAGE_ID,
		        a.USER_ID   ,
		        a.REAL_NAME ,
		        a.MOBILE,
		        a.REPLY_CONTENT,
		        a.IS_DEL,
		        a.CREATE_TIME
        FROM
        		MESSAGE_RECEIVER a
   		WHERE
   		        a.IS_DEL = 0
	</select>
	
	<select id="selectMessageManageById" parameterType="String" resultType="MessageManage1">
		SELECT 
    	        b.FD_OBJECTID,
		        b.CONTENT   ,
		        b.SEND_STATE
		FROM
		        MESSAGE_MANAGE b
		WHERE
		        b.IS_DEL = 0
		    AND b.FD_OBJECTID = #{messageId}
	</select>