mybatis中的基本sql书写

小鱼儿 2022-05-27 03:20 114阅读 0赞

--------------------

### 查询语句 ###

<resultMap id="BaseResultMap" type="com.rongdu.cashloan.rule.domain.RuleInfo">
            <id column="id" property="id" jdbcType="BIGINT" />
            <result column="tb_nid" property="tbNid" jdbcType="VARCHAR" />
            <result column="tb_name" property="tbName" jdbcType="VARCHAR" />
            <result column="detail" property="detail" jdbcType="VARCHAR" />
            <result column="state" property="state" jdbcType="VARCHAR" />
            <result column="req_ext" property="reqExt" jdbcType="VARCHAR" />
            <result column="add_time" property="addTime" jdbcType="TIMESTAMP" />
        </resultMap>
    
    
     
        <sql id="Base_Column_List">
            id,tb_nid,tb_name,detail,state,req_ext,add_time 
        </sql>
    
    
    
        <sql id="searchBy">
            <trim prefix="where" prefixOverrides="and|or">
                <if test="id !='' and id !=null">
                    id  = #{id,jdbcType=BIGINT}
                </if>
                <if test="tbNid !='' and tbNid !=null">
                    and tb_nid like CONCAT("%",#{tbNid,jdbcType=VARCHAR},"%")
                </if>
                <if test="tbName !='' and tbName !=null">
                    and tb_name like CONCAT("%",#{tbName,jdbcType=VARCHAR},"%") 
                </if>
                <if test="detail !='' and detail !=null">
                    and detail = #{detail,jdbcType=VARCHAR}
                </if>
                <if test="state !='' and state !=null">
                    and state = #{state,jdbcType=VARCHAR}
                </if>
                <if test="reqExt !='' and reqExt !=null">
                    and req_ext = #{reqExt,jdbcType=VARCHAR}
                </if>
                <if test="addTime !=null">
                    and add_time = #{addTime,jdbcType=TIMESTAMP}
                </if>
            </trim>
        </sql>
    
        
          <select id="listSelective" resultMap="BaseResultMap" parameterType="java.util.HashMap">
            select
            <include refid="Base_Column_List" />
            from arc_rule_info
            <include refid="searchBy"/> order  by  state  asc,add_time desc
        </select>

### 插入语句 ###

<insert id="save" parameterType="com.rongdu.cashloan.rule.domain.RuleInfo">
            insert into arc_rule_info(tb_nid,tb_name,detail,state,req_ext,add_time )values(#{
       tbNid,jdbcType=VARCHAR},#{
       tbName,jdbcType=VARCHAR},#{
       detail,jdbcType=VARCHAR},#{
       state,jdbcType=VARCHAR},#{
       reqExt,jdbcType=VARCHAR}, #{
       addTime,jdbcType=TIMESTAMP})
        </insert>

### 修改语句 ###

<update id="update" parameterType="com.rongdu.cashloan.rule.domain.RuleInfo">
            update arc_rule_info set 
                tb_nid = #{
       tbNid,jdbcType=VARCHAR},
                tb_name = #{
       tbName,jdbcType=VARCHAR},
                detail = #{
       detail,jdbcType=VARCHAR},
                state = #{
       state,jdbcType=VARCHAR},
                req_ext = #{
       reqExt,jdbcType=VARCHAR},
                add_time = #{
       addTime,jdbcType=TIMESTAMP}
            where id = #{
       id ,jdbcType=BIGINT}
        </update>

### 删除语句 ###

<delete id="delInfoById"  parameterType="java.util.HashMap">
            delete   from arc_rule_info  where id = #{id,jdbcType=BIGINT}
        </delete>