[MyBatis和MyBatis-Plus] 嵌套查询（一对一、一对多）

表结构：

CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_ame` varchar(50) DEFAULT NULL, `user_age` int(11) DEFAULT NULL, `user_address` varchar(200) DEFAULT NULL, PRIMARY KEY (`id`) );

CREATE TABLE `article` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL, `title` varchar(100) DEFAULT NULL, `content` text, `blog_id` int(11) NOT NULL, PRIMARY KEY (`id`) );

CREATE TABLE `blog` ( `id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(200) DEFAULT NULL, PRIMARY KEY (`id`) );

mybatis-cfg.xml：

<configuration> <!-- mybatis别名定义 --> <typeAliases> <typeAlias alias="User" type="com.mybatis.test.User"/> <typeAlias alias="Article" type="com.mybatis.test.Article"/> <typeAlias alias="Blog" type="com.mybatis.test.Blog"/> </typeAliases> <environments default="development"> <environment id="development"> <transactionManager type="JDBC"/> <dataSource type="POOLED"> <property name="driver" value="com.mysql.jdbc.Driver"/> <property name="url" value="jdbc:mysql://127.0.0.1:3306/mybatis" /> <property name="username" value="root"/> <property name="password" value="admin"/> </dataSource> </environment> </environments> <!-- mybatis的mapper文件，每个xml配置文件对应一个接口 --> <mappers> <mapper resource="com/mybatis/test/User.xml"/> <mapper resource="com/mybatis/test/Article.xml"/> <mapper resource="com/mybatis/test/Blog.xml"/> </mappers> </configuration>

 

Article实体类：

public class Article { private int id; private User user; //Article类中有一个User类。 private String title; private String content;

…… //getter、setter

}

Article.xml：

<mapper namespace="com.mybatis.test.IArticleOperation">

<resultMap type="User" id="userResultMap">

<!-- 属性名和数据库列名映射 -->

<id property="id" column="user_id" />

<result property="userName" column="user_userName" />

<result property="userAge" column="user_userAge" />

<result property="userAddress" column="user_userAddress" />

</resultMap>

<!-- User join Article进行联合查询 (一对一)-->

<resultMap id="articleResultMap" type="Article">

<id property="id" column="article_id" />

<result property="title" column="article_title" />

<result property="content" column="article_content" />

<!-- 将article的user属性映射到userResultMap -->

<association property="user" javaType="User" resultMap="userResultMap"/>

</resultMap>

<!-- 使用别名来映射匹配 -->

<select id="getUserArticles" parameterType="int" resultMap="articleResultMap">

select user.id user_id,user.userName user_userName,user.userAddress user_userAddress,

article.id article_id,article.title article_title,article.content article_content

from user,article

where user.id=article.userid and user.id=#{id}

</select>

</mapper>

Blog实体类：

public class Blog { private int id; private String title; private List<Article> articles; //Blog类中有一个List<Article>

…… //getter、setter

}

Blog.xml：

<mapper namespace="com.mybatis.test.IBlogOperation">

<resultMap id="userResultMap" type="User">

<id property="id" column="user_id" />

<result property="userName" column="user_userName" />

<result property="userAge" column="user_userAge" />

<result property="userAddress" column="user_userAddress" />

</resultMap>

<resultMap id="articleResultMap" type="Article">

<id property="id" column="article_id" />

<result property="title" column="article_title" />

<result property="content" column="article_content" />

<association property="user" javaType="User" resultMap="userResultMap"/>

</resultMap>

<resultMap id="blogResultMap" type="Blog">

<id property="id" column="blog_id" />

<result property="title" column="blog_title" />

<!-- 将article list属性映射到collection -->

<collection property="articles" ofType="Article" resultMap="articleResultMap"/>

</resultMap>

<!-- select语句 -->

<select id="getBlogByID" parameterType="int" resultMap="blogResultMap">

select user.id user_id,user.userName user_userName,user.userAddress user_userAddress,

article.id article_id,article.title article_title,article.content article_content,

blog.id blog_id, blog.title blog_title

from user,article,blog

where user.id=article.userid and blog.id=article.blogid and blog.id=#{id}

</select>

</mapper>

 

注意：如果是一对多的分页，不能在最后limit，需要在主表先分页。

主表分页的时候，有可能从表没有数据，分页数量可能还是不对，可添加条件来处理。

一对多分页处理示例：

<!--获取产品列表 -->

<select id="getProductList" parameterType="java.util.Map" resultMap="InsuranceProductVo">

SELECT

<include refid="Vo_Column_List"></include>

FROM

insurance_product pro

LEFT JOIN insurance_clause cla ON pro.id = cla.product_id

<where>

<!--先按主表分页，不然返回给前端的每页数量不是pageSize数量-->

pro.id IN (SELECT ip.id FROM (

SELECT id FROM insurance_product WHERE 1 = 1

AND id IN (SELECT DISTINCT product_id FROM `insurance_clause`) <!-这个条件处理从表没有造成分页数量不对的情况-->

<if test="merchantId != null">

and merchant_id = #{merchantId}

</if>

<if test="start !=null">

LIMIT ${start},${pageSize}

</if>

) AS ip

)

and cla.is_delete = 0

ORDER BY (CASE

WHEN pro.platform = 2 THEN 1 <!--象保保2排最前面-->

WHEN pro.platform = 3 THEN 2 <!--象保保&大象保险3排第二-->

WHEN pro.platform = 1 THEN 3 <!--大象保险1排第三-->

ELSE 4 END) <!--中保协4排最后-->

</where>