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"> 
<mapper namespace="com.casic.smart.model.ItemTheme">
	<resultMap id="itemNews" type="com.casic.smart.model.ItemNews">
		<id property="id" column="news_id" jdbcType="NUMERIC"/>
		<result property="title" column="news_title" jdbcType="VARCHAR"/>
		<result property="author" column="news_author" jdbcType="VARCHAR"/>
		<result property="content" column="news_content" jdbcType="VARCHAR"/>
		<result property="publishTime" column="publish_time" jdbcType="DATE"/>
		<result property="relateItem" column="relate_item" jdbcType="VARCHAR"/>
		<result property="relateSubject" column="relate_subject" jdbcType="VARCHAR"/>
		<result property="advice" column="advice" jdbcType="VARCHAR"/>
		<result property="auditor" column="auditor" jdbcType="VARCHAR"/>
		<result property="state" column="state" jdbcType="VARCHAR"/>
		<result property="category1" column="category1" jdbcType="INTEGER"/>
		<result property="category2" column="category2" jdbcType="INTEGER"/>
	</resultMap>
	
	<resultMap id="itemTheme" type="com.casic.smart.model.ItemTheme">
		<id property="id" column="theme_id" jdbcType="NUMERIC"/>
		<result property="name" column="theme_name" jdbcType="VARCHAR"/>
		<result property="intro" column="theme_intro" jdbcType="VARCHAR"/>
		<result property="category" column="theme_category" jdbcType="VARCHAR"/>
		
		<!-- 专区项目与项目新闻的     一对多关系  数据库表上没有涉及到外键关联关系 -->
	    <collection property="newsList" select="getItemNewsList" column="theme_name" ofType="com.casic.smart.model.ItemNews"></collection> 
	</resultMap>
	
	<select id="getItemNewsList" parameterType="java.lang.String"  resultMap="itemNews">
		SELECT distinct *
		FROM zh_item_news 
		WHERE
		#{name} IN ( relate_item,relate_subject )
</select>
	<sql id="columns">
		theme_id,theme_name,theme_intro,theme_category
	</sql>
	
	<sql id="dynamicWhere">
		<where>
			<if test="category != null and category == '项目专区'">AND t.theme_name=n.relate_item  </if>
			<if test="category != null and category == '活动专区'">AND t.theme_name=n.relate_subject </if>		
			<if test="@Ognl@isNotEmpty(id)"> AND t.theme_id  =#{id} </if>
			<if test="@Ognl@isNotEmpty(name)"> AND t.theme_name  =#{name} </if>
			<if test="@Ognl@isNotEmpty(category)"> AND t.theme_category  =#{category} </if>

		</where>
	</sql>

	<insert id="add" parameterType="com.casic.smart.model.ItemTheme">
		INSERT INTO zh_item_theme
		(theme_id,theme_name,theme_intro,theme_category)
		VALUES
		(#{id,jdbcType=NUMERIC}, #{name,jdbcType=VARCHAR},#{intro,jdbcType=VARCHAR},#{category,jdbcType=VARCHAR})
	</insert>
	
	<delete id="delById" parameterType="java.lang.Long">
		DELETE FROM zh_item_theme 
		WHERE
		theme_id=#{id}
	</delete>
	
	<update id="update" parameterType="com.casic.smart.model.ItemTheme">
		UPDATE zh_item_theme SET
		theme_name=#{name,jdbcType=VARCHAR},
		theme_intro=#{intro,jdbcType=VARCHAR},
		theme_category=#{category,jdbcType=VARCHAR}
		WHERE
		theme_id=#{id}
	</update>
		    
	<select id="getById" parameterType="java.lang.Long" resultMap="itemTheme">
		select <include refid="columns"/>
		FROM zh_item_theme 
		WHERE
		theme_id=#{id}
	</select>
	<select id="getAll" resultMap="itemTheme">
		select <include refid="columns"/>
		FROM zh_item_theme 
		<where>	
			<if test="@Ognl@isNotEmpty(id)"> AND theme_id  =#{id} </if>
			<if test="@Ognl@isNotEmpty(name)"> AND theme_name  =#{name} </if>
			<if test="@Ognl@isNotEmpty(category)"> AND theme_category  =#{category} </if>
		</where>
	</select>
	<select id="getItemThemeList" resultMap="itemTheme">
		select distinct t.*,n.*
		FROM zh_item_theme t,zh_item_news n 
		<include refid="dynamicWhere" />
	</select>
</mapper>