大家好，欢迎来到IT知识分享网。

建表语句

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 等等。 常用增删改查都可以使用这个网站生成的代码。