Mybatis 一对一 、一对多查询

<?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>