<!-- 高级结果映射 --> <!-- 一对一关联 嵌套查询 应用select属性 1)id="selectBlogAndRelatedAuthor" 中的 blog_author_id 对应到 <association property="author" column="blog_author_id"/>中的column="blog_author_id" 2)我们执行selectBlogAndRelatedAuthor 1次之后,所产生的每条记录都要再进行一个查询来获取author信息(N), 这就是N+1问题,应该使用延迟加载的方式,否则这有可能产生致命的后果。 3)对于一对一关联,我设置了 <settings> <setting name="lazyLoadingEnabled" value="true"/> </settings> 却没有对Author进行延迟加载,不知为何。。。 --> <resultMap id="blogResult" type="Blog" > <association property="author" column="blog_author_id" javaType="Author" select="selectAuthor" /> </resultMap> <select id="selectBlogAndRelatedAuthor" parameterType="int" resultMap="blogResult" > SELECT id,title,content,blog_author_id FROM blog WHERE id = #{id} </select> <select id="selectAuthor" parameterType="int" resultType="Author"> SELECT id,author_name as "name" FROM author WHERE id = #{id} </select> <!-- 一对一关联 嵌套结果 --> <resultMap id="blogResult2" type="Blog"> <id property="id" column="id" /> <result property="title" column="title"/> <association property="author" column="blog_author_id" javaType="Author"> <id property="id" column="author_id"/> <result property="name" column="author_name"/> </association> </resultMap> <select id="selectBlogAndRelatedAuthor2" parameterType="int" resultMap="blogResult2" > SELECT t.ID, t.TITLE, t.CONTENT, a.id as "author_id", a.author_name FROM blog t INNER JOIN author a ON t.BLOG_AUTHOR_ID = a.ID AND t.ID = #{id} </select> <!-- 一对多关联 嵌套查询,应用select属性 <collection property="posts" column="id" javaType="ArrayList" ofType="Post" select="selectPosts"/>中的column指得是Post所对应表中的引用的主表中的主键id, 注意:这个column指的是主表(Blog)中的id,我在这犯了个错,写为Post所对应表中的外键id,这是不对的, 应为所引用主表的主键id; property="posts" javaType="ArrayList" ofType="Post" 指属性posts为元素为Post的ArrayList类型 同样没有进行延迟加载,不知为何。。。 --> <resultMap type="Blog" id="blogResult3" > <id property="id" column="id"/> <result property="title" column="title"/> <result property="content" column="content"/> <collection property="posts" column="id" javaType="ArrayList" ofType="Post" select="selectPosts"/> </resultMap> <select id="selectBlogAndRelatedPosts" parameterType="int" resultMap="blogResult3" > SELECT id, title, content FROM blog WHERE id = #{id} </select> <select id="selectPosts" parameterType="int" resultType="Post" > SELECT p.id,p.subject,p.body as "postContent" FROM posts p WHERE p.blog_id =#{id} </select> <!-- 一对多关联 嵌套结果,在使用这种方式时,sql语句应该使用别名以保证不重复,如果不这样,可能出现结果不正确的现象,比如以下的post_id别名 --> <resultMap type="Blog" id="blogResultVersion" > <id property="id" column="id" /> <result property="title" column="title"/> <result property="content" column="content"/> <collection property="posts" javaType="ArrayList" ofType="Post" column="id"> <id property="id" column="post_id"/> <result property="subject" column="subject"/> <result property="postContent" column="postContent"/> </collection> </resultMap> <select id="selectBlogAndRelatedPosts2" parameterType="int" resultMap="blogResultVersion"> SELECT t.id, t.title, t.content, p.id as "post_id", p.subject, p.BODY as "postContent" FROM blog t left outer JOIN posts p ON t.id = p.blog_id WHERE t.ID = #{id} </select> |