表结构:
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>