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

北风toto

已于 2024-06-07 16:59:09 修改

阅读量6.3k

点赞数 21

分类专栏：次要的个人笔记文章标签： mybatis java mapper

于 2022-04-26 15:50:30 首次发布

本文链接：https://blog.youkuaiyun.com/m0_46085118/article/details/124423401

版权

次要的个人笔记专栏收录该内容

68 篇文章

订阅专栏

文章目录

1、sql 标签
2、select 标签
3、where标签
4、insert 标签
5、update 标签
6、delete 标签
7、set标签
8、include 标签
9、if标签
10、trim标签
11、foreach标签
12、choose标签
xml文件值比较处理

1、sql 标签

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

	<sql id="Base_Column_List">
        <!--@mbg.generated-->
        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>

2、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>

3、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>

where标签可以自动添加where，同时处理sql语句中第一个and关键字，如下面的sql语句，第一个AND会被优化剔除

<!-- 代码块 2-4  -->
<!-- 根据条件查询用户 -->
<select id="queryUserByWhere" parameterType="user" resultType="user">
   SELECT id , gender ,nickname ,mobile ,avatar From dts_user
<!-- where标签可以自动添加where，同时处理sql语句中第一个and关键字 -->
  <where>
    <if test="gender != null">
      AND gender = #{gender}
    </if>
    <if test="mobile != null and mobile != ''">
     AND mobile LIKE '%${mobile}%'
    </if>
  </where>
</select>

4、insert 标签

    int insert(NcdpFormDefine record);

    <insert id="insert" parameterType="com.iwhalecloud.bassc.ncdp.entity.form.NcdpFormDefine">
        <!--@mbg.generated-->
        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>

5、update 标签

    int updateByPrimaryKeySelective(LcdpAppGroupInfo record);

	<update id="updateByPrimaryKeySelective" parameterType="com.iwhalecloud.bassc.ncdp.entity.form.NcdpFormDefine">
        <!--@mbg.generated-->
        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>

6、delete 标签

表示这是一条删除语句

    int deleteByPrimaryKey(Long groupId);

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

7、set标签

感觉没啥用，很垃圾。目前没发现它的用处

    int updateByPrimaryKeySelective(LcdpAppGroupInfo record);

	<update id="updateByPrimaryKeySelective" parameterType="com.iwhalecloud.bassc.ncdp.entity.form.NcdpFormDefine">
        <!--@mbg.generated-->
        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>

8、include 标签

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

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

	<sql id="Base_Column_List">
        <!--@mbg.generated-->
        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>

9、if标签

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

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

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

<if test="list != null and list.size()!=0">

type 为 1 则为真

<if test="type == '1'.toString() ">

10、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>

11、foreach标签

collection 表示传入的对象名，默认为list，如果需要更改需要加注解@Param；如果传入的是数组 collection=“list” ，改为 collection=“array” 即可
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>

12、choose标签

在这个例子中，MyBatis会根据传入参数判断哪个条件满足，然后只执行相应的块里的SQL片段。如果没有条件满足，则执行块的内容。

<select id="getUserDetailsBasedOnCondition" parameterType="map" resultType="User">
    SELECT * FROM users
    <choose>
        <when test="username != null">
            WHERE username = #{username}
        </when>
        <when test="email != null">
            WHERE email = #{email}
        </when>
        <otherwise>
            <!-- 如果以上条件都不满足，可以定义一个默认行为或者干脆不做任何事情 -->
        </otherwise>
    </choose>
</select>