mybatis常用技巧

「爱情、让人受尽委屈。」 2022-10-11 00:51 327阅读 0赞

### mybatis常用技巧 ###

*  list的for循环
 *  分页
 *  insert
 *  update
 *  \#\{\} 预处理
 *  $\{\} 字符串替换
 *  批量提交
 *  mybatis plus实用工具类
 *   *  TableInfoHelper
 *  mapper.xml文件常用标签
 *   *  select标签
     *  insert标签
     *  update标签
     *  delete标签
     *  if标签
     *  choose标签
     *  where标签
     *  set标签
     *  trim标签
     *  foreach

# list的for循环 #

mybatis-plus

IPage<Map<String, Object>> page = this.baseMapper.selectMyPage(new Page<Map<String,Object>>(pageIndex, pageSize), paramMap);

@Select({ "<script>", 
    		"select a.* from t_customer a where a.TABLE_ID in",
    		"<foreach item='item' collection='tableIdList' separator=',' open='(' close=')' >",
    		" #{item, jdbcType=VARCHAR}",
    		"</foreach>",
    		"</script>"})
    	List<Customer > findByTableIdList(@Param("tableIdList")List<String> tableIdList);

# 分页 #

service

paramMap.put("cust_name", "%公司");
    		paramMap.put("clue_level_code ", "6");
    		IPage<Map<String, Object>> page = this.baseMapper.selectMyPage(new Page<Map<String,Object>>(pageIndex, pageSize), paramMap);

mapper

@Select({ "<script>",  
    		" select * from t_customer ",
    		" where 1=1 ",
    		" <if test=\"param.cust_name != null and param.cust_name != '' \"> and cust_name like #{param.cust_name} </if> ",
    		" <if test=\"param.cust_code != null and param.cust_code != '' \"> and cust__code = #{param.cust_code} </if> ",
    		"</script>"})
        IPage<Map<String, Object>> selectMyPage(IPage<Map<String, Object>> page, @Param("param")Map<String, Object> param);

# insert #

void insertMapObject(@Param("param")Map<String, Object> param);

<insert id="insertMapObject" parameterType="Map">
    		insert into t_customer (
    			<if test="param.ID != null and param.ID != '' ">ID </if>
    			<if test="param.CUST_ID != null and param.CUST_ID != '' ">, CUST_ID </if>
    			<if test="param.CUST_NAME != null and param.CUST_NAME != '' ">, CUST_NAME </if>
    			) values (
    			<if test="param.ID != null and param.ID != '' ">#{param[${'ID'}]} </if>
    			<if test="param.CUST_ID != null and param.CUST_ID != '' ">, #{param[${'CUST_ID'}]} </if>
    			<if test="param.CUST_NAME != null and param.CUST_NAME != '' ">, #{param[${'CUST_NAME'}]} </if>
    			)
    	</insert>

写法二  
动态变量map参数，  
缺点：必须确保key就是字段名

@Select({ "<script>",
    		" insert into t_customer ",
    		" <foreach collection='param.keys' item='key' open='(' close=')' separator=',' >",
    		" ${key}",
    		" </foreach>",
    		" values",
    		" <foreach collection='param.keys' item='key' open='(' close=')' separator=',' >",
    		" #{param[${key}]}",
    		" </foreach>",
    		"</script>"})
    	void insertMapObject(@Param("param")Map<String, Object> param);

# update #

void updateMapObject(@Param("param")Map<String, Object> param, @Param("id")String id);

<update id="updateMapObject" parameterType="Map">
    		update t_customer 
    		set id = #{id}
    		<if test="param.ID != null and param.ID != '' ">, ID = #{param.ID} </if>
    		<if test="param.CUST_ID != null and param.CUST_ID != '' ">, CUST_ID = #{param.CUST_ID} </if>
    		<if test="param.CUST_NAME != null and param.CUST_NAME != '' ">, CUST_NAME = #{param.CUST_NAME} </if>
    		where id = #{id} 
    	</update>

写法二  
动态变量map参数，  
缺点：必须确保key就是字段名

@Select({ "<script>",
    		" update t_customer ",
    		" set",
    		" <foreach collection='param.keys' item='key' open='' close='' separator=',' >",
    		" ${key} = #{param[${key}]}",
    		" </foreach>",
    		" where id = #{id}",
    	"</script>"})
    	void updateMapObject(@Param("param")Map<String, Object> param, @Param("id")String id);

# \#\{\} 预处理 #

\#\{\}可以防止sql注入

# $\{\} 字符串替换 #

# 批量提交 #

# mybatis plus实用工具类 #

## TableInfoHelper ##

// 实体类反射表辅助类
    com.baomidou.mybatisplus.core.metadata.TableInfoHelper
    
    // 获取实体映射表信息
    public static TableInfo getTableInfo(Class<?> clazz)
    
    // 获取所有实体映射表信息
    public static List<TableInfo> getTableInfos()

public boolean saveOrUpdate(T entity) { 
            if (null != entity) { 
                Class<?> cls = entity.getClass();
                TableInfo tableInfo = TableInfoHelper.getTableInfo(cls);
                Assert.notNull(tableInfo, "error: can not execute. because can not find cache of TableInfo for entity!");
                String keyProperty = tableInfo.getKeyProperty();
                Assert.notEmpty(keyProperty, "error: can not execute. because can not find column for id from entity!");
                Object idVal = ReflectionKit.getMethodValue(cls, entity, tableInfo.getKeyProperty());
                return StringUtils.checkValNull(idVal) || Objects.isNull(getById((Serializable) idVal)) ? save(entity) : updateById(entity);
            }
            return false;
        }

# mapper.xml文件常用标签 #

## select标签 ##

<select id="selectByPage" parameterType="" resultType="" resultMap="" flushCache="" useCache="">
    	select * from t_table where id = #{param.id}
    </select>

*  `parameterType`： 参数类型，可选属性。  
    MyBatis 可以通过 TypeHandler推断出具体传入语句的参数，默认值为 unset
 *  `resultType`： 期望返回的数据类型的类的全限定名。
 *  `resultMap`： 结果集映射，不可以和 resultType 同时使用。一般如果返回数据类型是常用的类型  
    比如 String Map List 的时候，可以使用 resultType  
    如果返回的是简单 POJO 类的时候，也可以直接使用 resultType  
    如果是复杂的映射或者连级查询的时候就需要使用 resultMap
 *  `flushCache` ：将其设置为 true，任何时候只要语句被调用，都会导致本地缓存和二级缓存都会被清空，默认值：false。
 *  `useCache`： 将其设置为 true，将会导致本条语句的结果被二级缓存，默认值：对 select 元素为 true。

## insert标签 ##

## update标签 ##

## delete标签 ##

## if标签 ##

## choose标签 ##

## where标签 ##

`where`标签会除去多余的 `and` 和 `or`

<select id="selectByPage" resultType="Map">
    		select * from t_table
    		<where>
    			<if test="param.isEnable !=null and param.isEnable !=''"> and IS_ENABLE=#{param.isEnable}</if>
    			<if test="param.updateControlId !=null and param.updateControlId !=''"> and UPDATE_CONTROL_ID=#{param.updateControlId}</if>
    		</where>
    	</select>

## set标签 ##

`set`标签会除去多余的逗号 （`,` ）

<update id="update">
    		upate t_table
    		<set>
    			<if test="param.isEnable !=null and param.isEnable !=''"> IS_ENABLE=#{param.isEnable},</if>
    			<if test="param.updateControlId !=null and param.updateControlId !=''">UPDATE_CONTROL_ID=#{param.updateControlId},</if>
    		</set>
    		where id = #{param.id}
    	</update>

## trim标签 ##

去掉前缀`and`

<select id="selectByPage" resultType="Map">
    		select * from t_table
    		<trim prefix="where" prefixOverrides="and">
    			<if test="param.isEnable !=null and param.isEnable !=''"> and IS_ENABLE=#{param.isEnable}</if>
    			<if test="param.updateControlId !=null and param.updateControlId !=''"> and UPDATE_CONTROL_ID=#{param.updateControlId}</if>
    		</trim>
    	</select>

去掉后缀逗号`,`

<insert id="insert">
    		insert into t_table
    		<trim prefix="(" suffix=")" suffixOverrides=",">
    			<if test="param.isEnable !=null">IS_ENABLE,</if>
    			<if test="param.updateControlId !=null">UPDATE_CONTROL_ID,</if>
    		</trim>
    		<trim prefix="values(" suffix=")" suffixOverrides=",">
    			<if test="param.isEnable !=null">#{param.isEnable},</if>
    			<if test="param.updateControlId !=null">#{param.updateControlId},</if>
    		</trim>
    	</insert>

## foreach ##

<delete id="insert">
    		delete t_table
    		where id in
    		<foreach collection="param.ids" index="i" item="id" open="(" separator="," close=")">
    		#{id}
    		</foreach>
    	</delete>