条件:文章(Article)和素材(Material)是多对多关系
一:ArticleMapper.xml内容如下
<?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.rdkl.cnfm.mapper.tb.TbArticleMapper">
<sql id="Base_Column_List"> articleid ,title, abstract, content, createtime, createuser, updateuser, updatetime, state, publicuser, publictime, url, templateid </sql>
<select id="selectByTemplateid" parameterType="int"> select <include refid="Base_Column_List" /> from tb_article where templateid=#{templateid} </select> <select id="listPageArticle" parameterType="map" resultType="TbArticle"> select <include refid="Base_Column_List" /> from tb_article <trim prefix="where" prefixOverrides="and|or" > <if test="templateid != null and templateid!=''"> <![CDATA[ and templateid = #{templateid} ]]> </if> </trim> </select> <select id="getArticle" parameterType="Integer" resultType="TbArticle"> select <include refid="Base_Column_List"/> from tb_article where articleid=#{articleid} </select> <insert id="insertArticle" parameterType="TbArticle" keyProperty="articleid" useGeneratedKeys="true"> insert into tb_article(title,content,createtime,createuser,state,templateid) values(#{title},#{content},#{createtime},#{createuser},#{state},#{templateid}) </insert> <insert id="insertArticleMaterial" parameterType="int"> insert into tb_article_material(articleid,materialid) values(#{0},#{1}) </insert> <update id="updateArticle" parameterType="TbArticle" > update tb_article set title=#{title},content=#{content},updatetime=#{updatetime},updateuser=#{updateuser} where articleid=#{articleid} </update> <update id="publishArticle" parameterType="TbArticle" > update tb_article set state=#{state},publicuser=#{publicuser},publictime=#{publictime} where articleid=#{articleid} </update> <delete id="deleteArticle" parameterType="Integer" > delete from tb_article where articleid=#{articleid} </delete> <delete id="deleteArticleMaterialByArticleid" parameterType="Integer"> delete from tb_article_material where articleid=#{articleid} </delete>
</mapper>
二:MaterialMapper.xml内容如下
<?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.rdkl.cnfm.mapper.tb.TbMaterialMapper" > <resultMap id="BaseResultMap" type="com.rdkl.cnfm.entity.tb.TbMaterial" > <id column="materialid" property="materialid" jdbcType="INTEGER" /> <result column="name" property="name" jdbcType="VARCHAR" /> <result column="createtime" property="createtime" jdbcType="TIMESTAMP" /> <result column="createuser" property="createuser" jdbcType="INTEGER" /> <result column="updateuser" property="updateuser" jdbcType="INTEGER" /> <result column="updatetime" property="updatetime" jdbcType="TIMESTAMP" /> <result column="url" property="url" jdbcType="VARCHAR" /> <result column="publicuser" property="publicuser" jdbcType="INTEGER" /> <result column="publictime" property="publictime" jdbcType="TIMESTAMP" /> <result column="state" property="state" jdbcType="INTEGER" /> <result column="content" property="content" jdbcType="VARCHAR" /> </resultMap> <!-- 分页信息 --> <select id="listPageMaterial" parameterType="map" resultType="com.rdkl.cnfm.entity.tb.TbMaterial"> select <include refid="Base_Column_List" /> from tb_material order by createtime desc </select> <sql id="Base_Column_List" > materialid, name, createtime, createuser, updateuser, updatetime, url, publicuser, publictime, state, content,puburl </sql> <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Integer" > select <include refid="Base_Column_List" /> from tb_material where materialid = #{materialid,jdbcType=INTEGER} </select> <update id="updatePuburl"> update tb_material set puburl=#{puburl} where materialid=#{materialid} </update> <update id="publishMaterial" parameterType="TbMaterial"> update tb_material set state=#{state},publicuser=#{publicuser},publictime=#{publictime} where materialid=#{materialid} </update> <update id="unpublishMaterial"> update tb_material set state=#{state} where materialid=#{materialid} </update> <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer" > delete from tb_material where materialid = #{materialid,jdbcType=INTEGER} </delete> <insert id="insert" parameterType="com.rdkl.cnfm.entity.tb.TbMaterial" > insert into tb_material (materialid, name, createtime, createuser, updateuser, updatetime, url, publicuser, publictime, state, content) values (#{materialid,jdbcType=INTEGER}, #{name,jdbcType=VARCHAR}, #{createtime,jdbcType=TIMESTAMP}, #{createuser,jdbcType=INTEGER}, #{updateuser,jdbcType=INTEGER}, #{updatetime,jdbcType=TIMESTAMP}, #{url,jdbcType=VARCHAR}, #{publicuser,jdbcType=INTEGER}, #{publictime,jdbcType=TIMESTAMP}, #{state,jdbcType=INTEGER}, #{content,jdbcType=VARCHAR}) </insert> <insert id="insertSelective" parameterType="com.rdkl.cnfm.entity.tb.TbMaterial" > insert into tb_material <trim prefix="(" suffix=")" suffixOverrides="," > <if test="materialid != null" > materialid, </if> <if test="name != null" > name, </if> <if test="createtime != null" > createtime, </if> <if test="createuser != null" > createuser, </if> <if test="updateuser != null" > updateuser, </if> <if test="updatetime != null" > updatetime, </if> <if test="url != null" > url, </if> <if test="publicuser != null" > publicuser, </if> <if test="publictime != null" > publictime, </if> <if test="state != null" > state, </if> <if test="content != null" > content, </if> </trim> <trim prefix="values (" suffix=")" suffixOverrides="," > <if test="materialid != null" > #{materialid,jdbcType=INTEGER}, </if> <if test="name != null" > #{name,jdbcType=VARCHAR}, </if> <if test="createtime != null" > #{createtime,jdbcType=TIMESTAMP}, </if> <if test="createuser != null" > #{createuser,jdbcType=INTEGER}, </if> <if test="updateuser != null" > #{updateuser,jdbcType=INTEGER}, </if> <if test="updatetime != null" > #{updatetime,jdbcType=TIMESTAMP}, </if> <if test="url != null" > #{url,jdbcType=VARCHAR}, </if> <if test="publicuser != null" > #{publicuser,jdbcType=INTEGER}, </if> <if test="publictime != null" > #{publictime,jdbcType=TIMESTAMP}, </if> <if test="state != null" > #{state,jdbcType=INTEGER}, </if> <if test="content != null" > #{content,jdbcType=VARCHAR}, </if> </trim> </insert> <update id="updateByPrimaryKeySelective" parameterType="com.rdkl.cnfm.entity.tb.TbMaterial" > update tb_material <set > <if test="name != null" > name = #{name,jdbcType=VARCHAR}, </if> <if test="createtime != null" > createtime = #{createtime,jdbcType=TIMESTAMP}, </if> <if test="createuser != null" > createuser = #{createuser,jdbcType=INTEGER}, </if> <if test="updateuser != null" > updateuser = #{updateuser,jdbcType=INTEGER}, </if> <if test="updatetime != null" > updatetime = #{updatetime,jdbcType=TIMESTAMP}, </if> <if test="url != null" > url = #{url,jdbcType=VARCHAR}, </if> <if test="publicuser != null" > publicuser = #{publicuser,jdbcType=INTEGER}, </if> <if test="publictime != null" > publictime = #{publictime,jdbcType=TIMESTAMP}, </if> <if test="state != null" > state = #{state,jdbcType=INTEGER}, </if> <if test="content != null" > content = #{content,jdbcType=VARCHAR}, </if> </set> where materialid = #{materialid,jdbcType=INTEGER} </update> <update id="updateByPrimaryKey" parameterType="com.rdkl.cnfm.entity.tb.TbMaterial" > update tb_material set name = #{name,jdbcType=VARCHAR}, createtime = #{createtime,jdbcType=TIMESTAMP}, createuser = #{createuser,jdbcType=INTEGER}, updateuser = #{updateuser,jdbcType=INTEGER}, updatetime = #{updatetime,jdbcType=TIMESTAMP}, url = #{url,jdbcType=VARCHAR}, publicuser = #{publicuser,jdbcType=INTEGER}, publictime = #{publictime,jdbcType=TIMESTAMP}, state = #{state,jdbcType=INTEGER}, content = #{content,jdbcType=VARCHAR} where materialid = #{materialid,jdbcType=INTEGER} </update></mapper>