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>