Mapper的xml文件基础语法笔记，增删改查，遍历

你的名字 2023-10-07 18:48 64阅读 0赞

#### 文章目录 ####

*  sql 标签
 *  select 标签
 *  where标签
 *  insert 标签
 *  update 标签
 *  delete 标签
 *  include 标签
 *  if标签
 *  trim标签
 *  foreach标签

## sql 标签 ##

*  表示单纯的一条sql语句，可以直接写一条可以执行的sql语句，也可以写一条给其他sql语句引用的sql语句

## select 标签 ##

*  表示这是一条查询语句

NcdpFormDefine selectByBusiObjectId(Long busiObjectId);  //mapper接口中对应的方法

<select id="selectByBusiObjectId" parameterType="java.lang.Long" resultMap="BaseResultMap">
            select *
            from ncdp_form_define
            where busi_object_id = #{busiObjectId,jdbcType=BIGINT}
    	</select>

## where标签 ##

*  如果where内如果没有任何语句，where则直接去除，比如下面这个例子如果if判定为false这时where里面没有语句，则不会生成where

<where>
            <if test="departmentChecks != null">
                bu.department_id in
                <foreach collection="departmentChecks" item="item" open="(" close=")" separator=",">
                    #{item}
                </foreach>
            </if>
            </where>

## insert 标签 ##

int insert(NcdpFormDefine record);

<insert id="insert" parameterType="com.iwhalecloud.bassc.ncdp.entity.form.NcdpFormDefine">
            
            insert into ncdp_form_define (id, busi_object_id, busi_object_code, app_id,
                                          title, page_id, form_data,
                                          page_data, creator_id, updator_id,
                                          created_time, updated_time)
            values (#{id,jdbcType=BIGINT}, #{busiObjectId,jdbcType=BIGINT}, #{busiObjectCode,jdbcType=VARCHAR},
                    #{appId,jdbcType=BIGINT},
                    #{title,jdbcType=VARCHAR}, #{pageId,jdbcType=VARCHAR}, #{formData,jdbcType=VARCHAR},
                    #{pageData,jdbcType=VARCHAR}, #{creatorId,jdbcType=BIGINT}, #{updatorId,jdbcType=BIGINT},
                    #{createdTime,jdbcType=TIMESTAMP}, #{updatedTime,jdbcType=TIMESTAMP})
        </insert>

## update 标签 ##

int updateByPrimaryKeySelective(LcdpAppGroupInfo record);

<update id="updateByPrimaryKeySelective" parameterType="com.iwhalecloud.bassc.ncdp.entity.form.NcdpFormDefine">
            
            update ncdp_form_define
            <set>
                <if test="busiObjectId != null">
                    busi_object_id = #{busiObjectId,jdbcType=BIGINT},
                </if>
                <if test="busiObjectCode != null">
                    busi_object_code = #{busiObjectCode,jdbcType=VARCHAR},
                </if>
                <if test="appId != null">
                    app_id = #{appId,jdbcType=BIGINT},
                </if>
                <if test="title != null">
                    title = #{title,jdbcType=VARCHAR},
                </if>
                <if test="pageId != null">
                    page_id = #{pageId,jdbcType=VARCHAR},
                </if>
                <if test="formData != null">
                    form_data = #{formData,jdbcType=VARCHAR},
                </if>
                <if test="pageData != null">
                    page_data = #{pageData,jdbcType=VARCHAR},
                </if>
                <if test="creatorId != null">
                    creator_id = #{creatorId,jdbcType=BIGINT},
                </if>
                <if test="updatorId != null">
                    updator_id = #{updatorId,jdbcType=BIGINT},
                </if>
                <if test="createdTime != null">
                    created_time = #{createdTime,jdbcType=TIMESTAMP},
                </if>
                <if test="updatedTime != null">
                    updated_time = #{updatedTime,jdbcType=TIMESTAMP},
                </if>
            </set>
            where id = #{id,jdbcType=BIGINT}
        </update>

## delete 标签 ##

*  表示这是一条删除语句

int deleteByPrimaryKey(Long groupId);

<delete id="deleteByPrimaryKey" parameterType="java.lang.Long">
            
            delete
            from ncdp_form_define
            where id = #{id,jdbcType=BIGINT}
        </delete>

## include 标签 ##

sql语句设置一个id（Base\_Column\_List），include通过这个id引用这个sql语句，引用过程为字符串拼接

NcdpFormDefine selectByBusiObjectId(Long busiObjectId);  //mapper接口中对应的方法

<sql id="Base_Column_List">
            
            id, busi_object_id, busi_object_code, app_id, title, page_id, form_data, page_data, creator_id, updator_id,
            created_time, updated_time
    	</sql>
    
    	<select id="selectByBusiObjectId" parameterType="java.lang.Long" resultMap="BaseResultMap">
            select
            <include refid="Base_Column_List"/>
            from ncdp_form_define
            where busi_object_id = #{busiObjectId,jdbcType=BIGINT}
    	</select>

## if标签 ##

*  表示updatedTime 不为null时，标签中的内容拼接进sql语句

<if test="updatedTime != null">
    		updated_time,
    	</if>

*  表示list不为空，同时不为 null

## trim标签 ##

实际上就是表示剪切的意思，对字符串进行稍微的处理

*  prefix 给sql语句拼接的前缀
 *  suffix 给sql语句拼接的后缀
 *  prefixOverrides 去除sql语句前面的关键字或者字符，该关键字或者字符由prefixOverrides属性指定，假设该属性指定为"AND"，当sql语句的开头为"AND"，trim标签将会去除该"AND"
 *  suffixOverrides 去除sql语句后面的关键字或者字符，该关键字或者字符由suffixOverrides属性指定

int insertSelective(LcdpAppGroupInfo record);

<insert id="insertSelective" keyColumn="group_id" keyProperty="groupId" parameterType="com.iwhalecloud.bassc.ncdp.entity.appManage.LcdpAppGroupInfo" useGeneratedKeys="true">
        insert into "ncdp_app_group_info"
        <trim prefix="(" suffix=")" suffixOverrides=",">
          <if test="orgId != null">
            org_id,
          </if>
          <if test="groupName != null">
            group_name,
          </if>
          <if test="sort != null">
            sort,
          </if>
          <if test="creatorId != null">
            creator_id,
          </if>
          <if test="createdTime != null">
            created_time,
          </if>
          <if test="updateId != null">
            update_id,
          </if>
          <if test="updateTime != null">
            update_time,
          </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
          <if test="orgId != null">
            #{orgId,jdbcType=BIGINT},
          </if>
          <if test="groupName != null">
            #{groupName,jdbcType=VARCHAR},
          </if>
          <if test="sort != null">
            #{sort,jdbcType=SMALLINT},
          </if>
          <if test="creatorId != null">
            #{creatorId,jdbcType=BIGINT},
          </if>
          <if test="createdTime != null">
            #{createdTime,jdbcType=TIMESTAMP},
          </if>
          <if test="updateId != null">
            #{updateId,jdbcType=BIGINT},
          </if>
          <if test="updateTime != null">
            #{updateTime,jdbcType=TIMESTAMP},
          </if>
        </trim>
      </insert>

## foreach标签 ##

*  collection 表示传入的对象名
 *  item 表示遍历的每个对象
 *  index表示索引
 *  open该语句以什么开始，
 *  close以什么结束
 *  separator在每次进行迭代之间以什么符号作为分隔符，

void updateSort(List<LcdpAppGroupInfo> list);

<update id="updateSort" parameterType="java.util.List">
        <foreach collection="list" item="item" index="index" open="" close="" separator=";">
          update ncdp_app_group_info
          <set>
            sort = #{item.sort}
          </set>
          where group_id = #{item.groupId}
        </foreach>
      </update>