mybatis-xml写法汇总

原创

伍六七AI编程

修改于 2020-12-28 18:05:28

1.1K0

修改于 2020-12-28 18:05:28

文章被收录于专栏：preparedprepared

建表语句

CREATE TABLE `tuser` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `id_card` varchar(32) COLLATE utf8mb4_bin DEFAULT NULL,
  `name` varchar(32) COLLATE utf8mb4_bin DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `ismale` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id_card` (`id_card`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

新增-insert获取id

注意点：

1、需要设置数据表主键为int或者bigint，且自增。

2、插入数据后在实体类中获取主键id

userMapper.insert(tuser);
id = tuser.getId();

<!--保存处方单-->
    <insert id="create" useGeneratedKeys="true" keyColumn="id" keyProperty="id"
            parameterType="com.prepared.entiry.TUser">
        <selectKey keyColumn="id" keyProperty="id" order="AFTER" resultType="java.lang.Long">
            select last_insert_id()
        </selectKey>
        INSERT INTO tuser
        <trim prefix="(" suffix=")" suffixOverrides=",">
                        <if test ='null != idCard'>
                        id_card,
                        </if>
                        <if test ='null != name'>
                        name,
                        </if>
                        <if test ='null != age'>
                        age,
                        </if>
                        <if test ='null != ismale'>
                        ismale
                        </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
                        <if test ='null != idCard'>
                        #{idCard},
                        </if>
                        <if test ='null != name'>
                        #{name},
                        </if>
                        <if test ='null != age'>
                        #{age},
                        </if>
                        <if test ='null != ismale'>
                        #{ismale}
                        </if>
        </trim>
    </insert>

新增-批量插入

需要设置主键为 int/bigint 且自增。

  <!--批量新增关联信息-->
  <insert id="batchInsert" parameterType="java.util.List">
    INSERT INTO tuser
    (name, age)
    VALUES
    <foreach collection ="list" item="item" index= "index" separator =",">
      (
      #{item.name},
      #{item.age}
      )
    </foreach >
  </insert>

新增-去掉首尾逗号

首尾增加trim标签

<trim prefix="(" suffix=")" suffixOverrides=",">
</trim>

例子

 <insert id="insert" useGeneratedKeys="true" keyColumn="id" keyProperty="id" parameterType="com.softdev.system.entity.TuserEntity">
        INSERT INTO tuser
        <trim prefix="(" suffix=")" suffixOverrides=",">
                        <if test ='null != idCard'>
                        id_card,
                        </if>
                        <if test ='null != name'>
                        name,
                        </if>
                        <if test ='null != age'>
                        age,
                        </if>
                        <if test ='null != ismale'>
                        ismale
                        </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
                        <if test ='null != idCard'>
                        #{idCard},
                        </if>
                        <if test ='null != name'>
                        #{name},
                        </if>
                        <if test ='null != age'>
                        #{age},
                        </if>
                        <if test ='null != ismale'>
                        #{ismale}
                        </if>
        </trim>
    </insert>

删除-根据id删除

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

修改-批量修改-case-when

1、一定要写where条件，否则会将除了when的其他数据修改为null，而mybatis会报错；

2、最好写else兜底

3、速度是for循环执行的N倍；

4、要限制修改数据的数量；

<update id="updateIsMaleById">
    update tuser
    set ismale =
    <foreach collection="idMaleList" item="item" index="index" separator=" " open="(case id" close="end)">
        when #{item.id,jdbcType=INTEGER} then #{item.ismale,jdbcType=INTEGER}
    </foreach>
    where id in
    <foreach collection="idMaleList" index="index" item="item" open="(" close=")" separator=",">
        #{item.id}
    </foreach>
</update>

查询-in查询

  <select id="selectCountByIds"
          resultType="com.prepared.UserCountDO">
    select id as id,count(1) as count from tuser where  id in
    <foreach collection="list" item="id" open="(" close=")" separator=",">
      #{id}
    </foreach>
    GROUP BY id
  </select>

查询-Mybatis 返回 List

<select id="queryUserTerminalList" resultMap="BaseResultMap">
    select
    <include refid="selectColumn"/>
    from T_MPS_SYS_USER_TERMINAL ut
    where 1=1
    <if test="userIds != null and userIds.size() > 0">
        and ut.USERID in
        <foreach collection="userIds" item="userId" open="(" separator="'" close=")">
            #{userId}
        </foreach>
    </if>
    <if test="type!=null and type!=''">
        and LOGIN_TYPE = #{type,jdbcType=VARCHAR}
    </if>
</select>

查询-模糊查询

模糊查询

  <!--根据title查询碎片列表-->
  <select id="queryByName" resultMap="BaseResultMap">
    select id, name
    from tuser
    where 1=1
    <if test="keyword !=null and keyword != ''">
      and name like concat(concat('%',#{keyword}),'%')
    </if>
  </select>

最后推荐一个在线生成代码的网站：

https://java.bejson.com/generator/

只需要复制建表语句，就可以生成各种代码，包括：mapper.xml, mapper.java, entity实体（包括lomlok，getter/setter风格）, json, service.java, controller.java 等等。

常用增删改查都可以使用这个网站生成的代码。

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

云数据库 SQL Server