<?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>
mybatis一对多关联查询示例
最新推荐文章于 2024-07-11 16:40:22 发布