association 用来串联两个sql查询
collection 一对多
实体类 UserInfo 对应数据表 USER_INFO @Data public class UserInfo { /** * id */ private String id; /** * 用户名 */ private String name; /** * 用户id */ private String userId; /** * 手机号 */ private String phone; /** * 文章 */ private List<ArticleInfo> articleInfos; }
实体类 articleInfo
对应数据表 ARTICLE_INFO
@Data public class ArticleInfo { /** * id */ private String id; /** * 作者id */ private String userId; /** * 文章名 */ private String title; /** * 正文 */ private String content; }
查询UserInfo关联Article
第一种查询 使用association
<resultMap id="userMap1" type="com.fzs.mydemo.entity.UserInfo"> <id property="id" column="ID"/> <result property="name" column="NAME"/> <result property="userId" column="USER_ID"/> <result property="phone" column="PHONE"/> <association property="articleInfos" column="USER_ID" javaType="java.util.List" select="com.fzs.mydemo.mapper.ArticleInfoMapper.selectArticlesByUserId"/> </resultMap> <select id="selectUserInfo" resultMap="userMap1"> SELECT <include refid="baseSql"/> FROM USER_INFO </select>
<select id="selectArticleById" resultMap="articleInfoMap"> SELECT <include refid="baseSql"/> FROM ARTICLE_INFO WHERE ID = #{id,jdbcType=VARCHAR} </select>
第二种查询 使用collection
<resultMap id="userMap2" type="com.fzs.mydemo.entity.UserInfo"> <id property="id" column="ID"/> <result property="name" column="NAME"/> <result property="userId" column="USER_ID"/> <result property="phone" column="PHONE"/> <collection property="articleInfos" ofType="com.fzs.mydemo.entity.ArticleInfo"> <id property="id" column="A_ID"/> <result property="userId" column="A_USER_ID"/> <result property="title" column="A_TITLE"/> <result property="content" column="A_CONTENT"/> </collection> </resultMap> <select id="selectUserInfo" resultMap="userMap2"> SELECT i.ID, i.NAME, i.USER_ID, i.PHONE, a.ID A_ID, a.USER_ID A_USER_ID, a.TITLE A_TITLE, a.CONTENT A_CONTENT FROM USER_INFO i,ARTICLE_INFO a WHERE i.USER_ID = a.USER_ID </select>
(查询结果中有相同字段名时,注意使用别名区分,否则结果映射有问题)
查询结果