Mybatis增删改查样例 and mybatis之trim

 

Mybatis增删改查样例     

 

查询

<sql id="columns">id,code,keyword,readingNum,commentsNum,title,page,author,publishTime,lastUpdateTime,content,contentUrl,taskId,createTime</sql>

<select id="getById" parameterType="Integer" resultType="com.springboot.splider.model.EastMoney">
     SELECT <include refid="columns"/> FROM shares_eastmoney_jmzy WHERE id=#{id}
</select>

<select id="selectEastMoneyListByCode" parameterType="String" resultType="com.springboot.splider.model.EastMoney">
     SELECT
          <include refid="columns"/>
     FROM shares_eastmoney_jmzy WHERE code=#{code} and ISNULL(content) and title is not null and loopFlag='0'  order by createTime asc
</select>

<select id="getCountByIdAndPublishTime" parameterType="String" resultType="int">
     SELECT count(1) FROM shares_eastmoney_jmzy WHERE id=#{id} and publishTime is not null
</select>

 

<select id="getRecruitInfoList" parameterType="String" resultType="com.springboot.splider.model.qichacha.RecruitInfo">
     SELECT * from t_recruit_info order by companyName
</select>

 

<select id="getCompanyInfoList" parameterType="String" resultType="com.springboot.splider.model.qichacha.CompanyInfo">
     SELECT * FROM t_company_info
</select>

<select id="getRecordIndexByType" parameterType="String" resultType="com.springboot.splider.model.qichacha.RecordIndex">
     SELECT * FROM t_record_index where `type`=#{type,jdbcType=VARCHAR}
</select>

 

新增

<insert id="insertCompanyInfoSelective" parameterType="com.springboot.splider.model.qichacha.CompanyInfo">
        insert into t_company_info
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null">
                id,
            </if>
            <if test="companyName != null">
                `companyName`,
            </if>
            <if test="companyUnique != null">
                companyUnique,
            </if>
            <if test="legalPerson != null">
                legalPerson,
            </if>
            <if test="registCapital != null">
                registCapital,
            </if>
            <if test="establishmentTime != null">
                establishmentTime,
            </if>
            <if test="companyetAddress != null">
                companyetAddress,
            </if>
            <if test="createTime != null and createTime != ''">
                createTime
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="id != null">
                #{id,jdbcType=VARCHAR},
            </if>
            <if test="companyName != null">
                #{companyName,jdbcType=VARCHAR},
            </if>
            <if test="companyUnique != null">
                #{companyUnique,jdbcType=VARCHAR},
            </if>
            <if test="legalPerson != null">
                #{legalPerson,jdbcType=VARCHAR},
            </if>
            <if test="registCapital != null">
                #{registCapital,jdbcType=VARCHAR},
            </if>
            <if test="establishmentTime != null">
                #{establishmentTime,jdbcType=VARCHAR},
            </if>
            <if test="companyetAddress != null">
                #{companyetAddress,jdbcType=VARCHAR},
            </if>
            <if test="createTime != null and createTime != ''">
                #{createTime,jdbcType=VARCHAR}
            </if>
        </trim>
</insert>

 

批量insert

<insert id="insertTbSelectiveBatch" parameterType="java.util.List">
        insert into t_bktb
        (
        id,
        companyName,
        title,
        author,
        publishTime,
        lastUpdateTime,
        content,
        articleUrl,
        createTime,
        mark
        )
        values
        <foreach collection="list" item="item" index="index" separator=",">
            (
            #{item.id,jdbcType=VARCHAR},
            #{item.companyName,jdbcType=VARCHAR},
            #{item.title,jdbcType=VARCHAR},
            #{item.author,jdbcType=VARCHAR},
            #{item.publishTime,jdbcType=VARCHAR},
            #{item.lastUpdateTime,jdbcType=VARCHAR},
            #{item.content,jdbcType=VARCHAR},
            #{item.articleUrl,jdbcType=VARCHAR},
            #{item.createTime,jdbcType=VARCHAR},
            #{item.mark,jdbcType=VARCHAR}
            )
        </foreach>
</insert>

 

oracle语句

<insert id="insertCdUser" parameterType="map">
		<selectKey resultType="Integer" keyProperty="id" order="BEFORE">SELECT seq_cd_user_id.NEXTVAL FROM DUAL</selectKey>
		INSERT INTO CD_USER
		<trim prefix="(" suffix=")" suffixOverrides=",">
			<if test="id != null and id !=''">
				id,
			</if>
			<if test="userName != null and userName !=''">
				username,
			</if>
			<if test="password != null and password !=''">
				password,
			</if>
			<if test="organization != null and organization !=''">
				organization,
			</if>
			<if test="policenumber != null and policenumber !=''">
				policenumber,
			</if>
			<if test="auditstatus != null and auditstatus !=''">
				auditstatus,
			</if>
			<if test="creattime != null and creattime !=''">
				creattime,
			</if>
			<if test="isadmin != null and isadmin !=''">
				isadmin,
			</if>
			<if test="usermobile != null and usermobile !=''">
				usermobile,
			</if>
			<if test="idcard != null and idcard !=''">
				idcard,
			</if>
			<if test="isfirst != null and isfirst !=''">
				isfirst,
			</if>
			<if test="usertype != null and usertype !=''">
				usertype,
			</if>
			<if test="provincecode != null and provincecode !=''">
				provincecode,
			</if>
			<if test="provincename != null and provincename !=''">
				provincename,
			</if>
			<if test="citycode != null and citycode !=''">
				citycode,
			</if>
			<if test="cityname != null and cityname !=''">
				cityname,
			</if>
			<if test="districtcode != null and districtcode !=''">
				districtcode,
			</if>
			<if test="districtname != null and districtname !=''">
				districtname,
			</if>
		</trim>
		VALUES
		<foreach collection="list" item="item" index="index" separator=",">
			<trim prefix="(" suffix=")" suffixOverrides=",">
				<if test="id != null and id !=''">
					#{id},
				</if>
				<if test="userName != null and userName !=''">
					#{userName},
				</if>
				<if test="password != null and password !=''">
					#{password},
				</if>
				<if test="organization != null and organization !=''">
					#{organization},
				</if>
				<if test="policenumber != null and policenumber !=''">
					#{policenumber},
				</if>
				<if test="auditstatus != null and auditstatus !=''">
					#{auditstatus},
				</if>
				<if test="creattime != null and creattime !=''">
					#{creattime},
				</if>
				<if test="isadmin != null and isadmin !=''">
					#{isadmin},
				</if>
				<if test="usermobile != null and usermobile !=''">
					#{usermobile},
				</if>
				<if test="idcard != null and idcard !=''">
					#{idcard},
				</if>
				<if test="isfirst != null and isfirst !=''">
					#{isfirst},
				</if>
				<if test="usertype != null and usertype !=''">
					#{usertype},
				</if>
				<if test="provincecode != null and provincecode !=''">
					#{provincecode},
				</if>
				<if test="provincename != null and provincename !=''">
					#{provincename},
				</if>
				<if test="citycode != null and citycode !=''">
					#{citycode},
				</if>
				<if test="cityname != null and cityname !=''">
					#{cityname},
				</if>
				<if test="districtcode != null and districtcode !=''">
					#{districtcode},
				</if>
				<if test="districtname != null and districtname !=''">
					#{districtname},
				</if>
			</trim>
		</foreach>
</insert>

 

修改

<update id="updateSelective" parameterType="com.springboot.splider.model.EastMoney">
  	UPDATE shares_eastmoney_jmzy
  	SET
  		publishTime=#{publishTime},
  		lastUpdateTime=#{lastUpdateTime},
  		content=#{content},
  		contentFileUrl=#{contentFileUrl}
  	WHERE id=#{id}
</update>

<update id="updateLoopFlag" parameterType="String">
    UPDATE shares_eastmoney_jmzy SET loopFlag='1' WHERE id=#{id}
</update>

 

批量update

<update id="updateRecordIndex" parameterType="com.springboot.splider.model.qichacha.RecordIndex">
    update t_record_index
      <set>
          <if test="companyName != null">companyName=#{companyName},</if>
          <if test="companyUnique != null">companyUnique=#{companyUnique},</if>
          <if test="companyLoopIndex != null">companyLoopIndex=#{companyLoopIndex},</if>
          <if test="pageLoopIndex != null">pageLoopIndex=#{pageLoopIndex},</if>
          <if test="type != null">`type`=#{type},</if>
          <if test="updateTime != null">`updateTime`=#{updateTime},</if>
          <if test="createTime != null">createTime=#{createTime}</if>
       </set>
     where id=#{id}
</update>

 

 

 

mybatis之trim

<trim prefix="" suffix="" suffixOverrides="" prefixOverrides=""></trim>

prefix:在trim标签内sql语句加上前缀。

suffix:在trim标签内sql语句加上后缀。

suffixOverrides:指定去除多余的后缀内容，如：suffixOverrides=","，去除trim标签内sql语句多余的后缀","。

prefixOverrides:指定去除多余的前缀内容

 

<insert id="insert" parameterType="com.tortuousroad.groupon.cart.entity.Cart">  
        insert into cart  
        <trim prefix="(" suffix=")" suffixOverrides=",">  
            <if test="id != null">  
                id,  
            </if>  
            <if test="userId != null">  
                user_id,  
            </if>  
            <if test="dealId != null">  
                deal_id,  
            </if>  
            <if test="dealSkuId != null">  
                deal_sku_id,  
            </if>  
            <if test="count != null">  
                count,  
            </if>  
            <if test="createTime != null">  
                create_time,  
            </if>  
            <if test="updateTime != null">  
                update_time,  
            </if>  
        </trim>  
        <trim prefix="values (" suffix=")" suffixOverrides=",">  
            <if test="id != null">  
                #{id,jdbcType=BIGINT},  
            </if>  
            <if test="userId != null">  
                #{userId,jdbcType=BIGINT},  
            </if>  
            <if test="dealId != null">  
                #{dealId,jdbcType=BIGINT},  
            </if>  
            <if test="dealSkuId != null">  
                #{dealSkuId,jdbcType=BIGINT},  
            </if>  
            <if test="count != null">  
                #{count,jdbcType=INTEGER},  
            </if>  
            <if test="createTime != null">  
                #{createTime,jdbcType=TIMESTAMP},  
            </if>  
            <if test="updateTime != null">  
                #{updateTime,jdbcType=TIMESTAMP},  
            </if>  
        </trim>  
    </insert>  

 

假设没有指定

suffixOverrides=","  

 

执行的sql语句也许是这样的：insert into cart (id,user_id,deal_id,) values(1,2,1,);显然是错误的

指定之后语句就会变成insert into cart (id,user_id,deal_id) values(1,2,1);这样就将“，”去掉了。

前缀也是一个道理这里就不说了。