Mybatis 一对一 、一对多查询

bco

<?xml version="1.0" encoding="UTF-8" ?>  
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"  
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">  
<!-- 这里的namespace名字必须为执行该sql的dao地址  -->  
<mapper namespace="cn.tramp.iblog.dao.IUserMapper">  
<resultMap type="cn.tramp.iblog.domain.User" id="userResultMap">
<id property="user_id" column="user_id"/>
<result property="role_id" column="role_id"/>
<result property="user_name" column="user_name"/>   
<result property="password" column="password"/>   
<result property="user_nickname" column="user_nickname"/>  
<result property="gender" column="gender"/>  
<result property="user_email" column="user_email"/>  
<result property="user_birthday" column="user_birthday"/>  
<result property="photo_path" column="photo_path"/>  
<result property="is_show_birthday" column="is_show_birthday"/>
<result property="marriage_state" column="marriage_state"/>
<result property="occupation" column="occupation"/>
<result property="live_place" column="live_place"/>
<result property="native_place" column="native_place"/>
<result property="personal_intro" column="personal_intro"/>
<result property="is_lock" column="is_lock"/>
</resultMap>
<!-- one to many 嵌套查询-->
<resultMap type="cn.tramp.iblog.domain.User" id="userBlogResultMap">
<id property="user_id" column="user_id"/>
<result property="role_id" column="role_id"/>
<result property="user_name" column="user_name"/>   
<result property="password" column="password"/>   
<result property="user_nickname" column="user_nickname"/>  
<result property="gender" column="gender"/>  
<result property="user_email" column="user_email"/>  
<result property="user_birthday" column="user_birthday"/>  
<result property="photo_path" column="photo_path"/>  
<result property="is_show_birthday" column="is_show_birthday"/>
<result property="marriage_state" column="marriage_state"/>
<result property="occupation" column="occupation"/>
<result property="live_place" column="live_place"/>
<result property="native_place" column="native_place"/>
<result property="personal_intro" column="personal_intro"/>
<result property="is_lock" column="is_lock"/>
<!-- ofType 指定集合中元素的类型 -->
<collection property="blogList" column="user_id" ofType="cn.tramp.iblog.domain.Blog">
<id property="blog_id" column="blog_id"/>
<result property="user_id" column="user_id"/>
<result property="blog_type_id" column="blog_type_id"/>   
<result property="blog_title" column="blog_title"/>   
<result property="key_words" column="key_words"/>  
<result property="blog_content" column="blog_content"/>  
<result property="post_datetime" column="post_datetime"/>  
<result property="edit_datetime" column="edit_datetime"/>  
<result property="read_times" column="read_times"/>  
<result property="comment_times" column="comment_times"/>
</collection>
</resultMap>
<!-- one to many 多条SQL-->
<resultMap type="cn.tramp.iblog.domain.User" id="userBlogResultMap1">
<id property="user_id" column="user_id"/>
<result property="role_id" column="role_id"/>
<result property="user_name" column="user_name"/>   
<result property="password" column="password"/>   
<result property="user_nickname" column="user_nickname"/>  
<result property="gender" column="gender"/>  
<result property="user_email" column="user_email"/>  
<result property="user_birthday" column="user_birthday"/>  
<result property="photo_path" column="photo_path"/>  
<result property="is_show_birthday" column="is_show_birthday"/>
<result property="marriage_state" column="marriage_state"/>
<result property="occupation" column="occupation"/>
<result property="live_place" column="live_place"/>
<result property="native_place" column="native_place"/>
<result property="personal_intro" column="personal_intro"/>
<result property="is_lock" column="is_lock"/>
<!-- ofType 指定集合中元素的类型 -->
<collection property="blogList" column="user_id" ofType="cn.tramp.iblog.domain.Blog"
select="cn.tramp.iblog.dao.IBlogMapper.queryForBlogByUserID" />
</resultMap>
<sql id="userColumns">
<![CDATA[
role_id, user_name, password
]]>
</sql>
<select id="queryForUserBlogList" parameterType="int" resultMap="userBlogResultMap">
SELECT * FROM iblog_user, iblog_blog WHERE
iblog_user.user_id=iblog_blog.user_id AND iblog_user.user_id=#{user_id}
</select>
<!-- seGeneratedKeys设置 为"true"表明要MyBatis获取由数据库自动生成的主   键；keyProperty="id"指定把获取到的主键值注入  
到User的user_id属性 -->
<insert id="insert" parameterType="cn.tramp.iblog.domain.User" useGeneratedKeys="true" keyProperty="user_id">  
INSERT INTO iblog_user(role_id,user_name,password,user_nickname,gender,user_email,
user_birthday, photo_path, is_show_birthday, marriage_state, occupation, live_place,
native_place, personal_intro, is_lock)  
VALUES(  
#{role_id},  
#{user_name},  
#{password},  
#{user_nickname},  
#{gender},  
#{user_email},  
#{user_birthday},  
#{photo_path},  
#{is_show_birthday},  
#{marriage_state},
#{occupation},  
#{live_place},  
#{native_place},
#{personal_intro},
#{is_lock}
)  
</insert>
<update id="update" parameterType="cn.tramp.iblog.domain.User">
UPDATE iblog_user SET
role_id = #{role_id},
user_name = #{user_name},
password = #{password},
user_nickname = #{user_nickname},
gender = #{gender},
user_email = #{user_email},
user_birthday = #{user_birthday},
photo_path = #{photo_path},
is_show_birthday = #{is_show_birthday},
marriage_state = #{marriage_state},
occupation = #{occupation},
live_place = #{live_place},
native_place = #{native_place},
personal_intro = #{personal_intro},
is_lock = #{is_lock}
WHERE user_id = #{user_id}
</update>
<select id="queryForObject" parameterType="int" resultType="cn.tramp.iblog.domain.User" resultMap="userBlogResultMap1">   
<![CDATA[  
SELECT * FROM iblog_user
WHERE user_id =  #{user_id}
]]>     
</select>
<select id="queryUserByEmail" parameterType="string" resultType="cn.tramp.iblog.domain.User" resultMap="userResultMap">   
<![CDATA[  
SELECT * FROM iblog_user
WHERE user_email =  #{user_email}
]]>     
</select>  
<select id="queryUserByName" parameterType="string" resultType="hashmap">
SELECT <include refid="userColumns"/>
FROM iblog_user
WHERE user_name = #{user_name}
</select>
<select id="getTotalCount" parameterType="string" resultType="int">
SELECT count(*) FROM iblog_user
<if test="columnName != null">
WHERE #{columnName} LIKE %#{value}%
</if>
</select>
<select id="queryUserByPage" parameterType="cn.tramp.iblog.utils.Page" resultType="list" resultMap="userResultMap">
SELECT * FROM iblog_user
ORDER BY user_id
LIMIT #{offset}, #{pageSize}
</select>
<delete id="delete" parameterType="int">
DELETE FROM iblog_user WHERE user_id = #{user_id}
</delete>
</mapper>  


<?xml version="1.0" encoding="UTF-8" ?>  
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"  
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">  
<!-- 这里的namespace名字必须为执行该sql的dao地址  -->  
<mapper namespace="cn.tramp.iblog.dao.IBlogMapper">  
<resultMap type="cn.tramp.iblog.domain.Blog" id="blogResultMap0">
<id property="blog_id" column="blog_id"/>
<result property="user_id" column="user_id"/>
<result property="blog_type_id" column="blog_type_id"/>   
<result property="blog_title" column="blog_title"/>   
<result property="key_words" column="key_words"/>  
<result property="blog_content" column="blog_content"/>  
<result property="post_datetime" column="post_datetime"/>  
<result property="edit_datetime" column="edit_datetime"/>  
<result property="read_times" column="read_times"/>  
<result property="comment_times" column="comment_times"/>
</resultMap>
<!-- 嵌套查询 -->
<resultMap type="cn.tramp.iblog.domain.Blog" id="blogResultMap">
<id property="blog_id" column="blog_id"/>
<result property="user_id" column="user_id"/>
<result property="blog_type_id" column="blog_type_id"/>   
<result property="blog_title" column="blog_title"/>   
<result property="key_words" column="key_words"/>  
<result property="blog_content" column="blog_content"/>  
<result property="post_datetime" column="post_datetime"/>  
<result property="edit_datetime" column="edit_datetime"/>  
<result property="read_times" column="read_times"/>  
<result property="comment_times" column="comment_times"/>
<association property="user" column="user_id" javaType="cn.tramp.iblog.domain.User"
select="cn.tramp.iblog.dao.IUserMapper.queryForObject"/>
</resultMap>
<!-- one to one relation  结果集映射-->
<resultMap type="cn.tramp.iblog.domain.Blog" id="blogUserResultMap">
<id property="blog_id" column="blog_id"/>
<result property="user_id" column="user_id"/>
<result property="blog_type_id" column="blog_type_id"/>   
<result property="blog_title" column="blog_title"/>   
<result property="key_words" column="key_words"/>  
<result property="blog_content" column="blog_content"/>  
<result property="post_datetime" column="post_datetime"/>  
<result property="edit_datetime" column="edit_datetime"/>  
<result property="read_times" column="read_times"/>  
<result property="comment_times" column="comment_times"/>
<association property="user" javaType="cn.tramp.iblog.domain.User" column="user_id">
<id property="user_id" column="user_id"/>
<result property="role_id" column="role_id"/>
<result property="user_name" column="user_name"/>   
<result property="password" column="password"/>   
<result property="user_nickname" column="user_nickname"/>  
<result property="gender" column="gender"/>  
<result property="user_email" column="user_email"/>  
<result property="user_birthday" column="user_birthday"/>  
<result property="photo_path" column="photo_path"/>  
<result property="is_show_birthday" column="is_show_birthday"/>
<result property="marriage_state" column="marriage_state"/>
<result property="occupation" column="occupation"/>
<result property="live_place" column="live_place"/>
<result property="native_place" column="native_place"/>
<result property="personal_intro" column="personal_intro"/>
<result property="is_lock" column="is_lock"/>
</association>
</resultMap>
<sql id="userColumns">
<![CDATA[
role_id, user_name, password
]]>
</sql>
<select id="queryForBlogByID" parameterType="int" resultType="cn.tramp.iblog.domain.Blog" resultMap="blogUserResultMap">
SELECT * FROM iblog_blog , iblog_user WHERE iblog_blog.user_id = iblog_user.user_id AND iblog_blog.blog_id = #{blog_id}  
</select>
<!-- seGeneratedKeys设置 为"true"表明要MyBatis获取由数据库自动生成的主   键；keyProperty="id"指定把获取到的主键值注入  
到User的user_id属性 -->
<insert id="insert" parameterType="cn.tramp.iblog.domain.Blog" useGeneratedKeys="true" keyProperty="blog_id">  
INSERT INTO iblog_blog(user_id,blog_type_id,blog_title,key_words,blog_content,post_datetime,
edit_datetime, read_times, comment_times)  
VALUES(  
#{user_id},  
#{blog_type_id},  
#{blog_title},  
#{key_words},  
#{blog_content},  
#{post_datetime},  
#{edit_datetime},  
#{read_times},  
#{comment_times}  
)  
</insert>
<update id="update" parameterType="cn.tramp.iblog.domain.Blog">
UPDATE iblog_blog SET
user_id = #{user_id},
blog_type_id = #{blog_type_id},
blog_title = #{blog_title},
key_words = #{key_words},
blog_content = #{blog_content},
post_datetime = #{post_datetime},
read_times = #{read_times},
comment_times = #{comment_times}
WHERE blog_id = #{blog_id}
</update>
<select id="queryForBlog" parameterType="int" resultType="cn.tramp.iblog.domain.Blog" resultMap="blogResultMap0">   
<![CDATA[  
SELECT * FROM iblog_blog
WHERE blog_id =  #{blog_id}
]]>     
</select>
<select id="queryForBlogByUserID" parameterType="int" resultType="list" resultMap="blogResultMap0">   
<![CDATA[  
SELECT * FROM iblog_blog
WHERE user_id =  #{user_id}
]]>     
</select>
<select id="getTotalCount" parameterType="string" resultType="int">
SELECT count(*) FROM iblog_blog
<if test="params != null">
WHERE #{params} LIKE %#{params}%
</if>
</select>
<select id="queryBlogByPage" parameterType="cn.tramp.iblog.utils.Page"  resultType="list" resultMap="blogResultMap">
SELECT * FROM iblog_blog
ORDER BY user_id
LIMIT #{offset}, #{pageSize}
</select>
<delete id="delete" parameterType="int">
DELETE FROM iblog_blog WHERE blog_id = #{blog_id}
</delete>
</mapper>