mybatis技巧

# 批量更新

<update id="batchUpdate">
    <foreach collection="list" item="item" index="index" open="begin" close=";end;" separator=";">
        update table
        <set>
            <if test="item.cloumn1 != null">
                cloumn1 = #{item.cloumn1},
            </if>
            cloumn2 = #{item.cloumn1}
        </set>
        where id = #{item.id}
    </foreach>
</update>

1
2
3
4
5
6
7
8
9
10
11
12

# 小于号处理

方式一：
	<![CDATA[ a < 1 ]]>
方式二：
	< 改为 &lt;

1
2
3
4

# in大于1000

 where (字段1 in
<foreach collection="codes" item="code"  open="(" close=")" index="index">
    <if test="index != 0">
        <choose>
            <when test="index % 1000 == 999">) OR user_code IN (</when>
            <otherwise>,</otherwise>
        </choose>
    </if>
	#{code}
</foreach>
)

1
2
3
4
5
6
7
8
9
10
11

# 字段/条件分装

    <sql id="Base_Column_List">
        id
    </sql>
	<select id="pageList" resultType="VO">
        SELECT
            <include refid="Base_Column_List"/>
        FROM a
        WHERE 1 = 1
	</select>

1
2
3
4
5
6
7
8
9

# 更新空报错问题

带上类型
#{code, jdbcType=VARCHAR}

1
2

# 调用存储过程

<![CDATA[
   {CALL P_XX(
  		#{code,jdbcType=VARCHAR,mode=IN},
         #{P_rest,jdbcType=CURSOR,mode=OUT,resultMap=vo,javaType=java.sql.ResultSet}
     )}
]]>
<resultMap type="VO" id="vo">
    <result property="code"  column="code" />
</resultMap>

1
2
3
4
5
6
7
8
9

# 行转列

# oracle 19G
LISTAGG(DISTINCT decode ( a.code,1, to_char(a.name), NULL ), ',' ) WITHIN GROUP (ORDER BY a.name) name
# oracle 11G
GROUP_CONCAT( DISTINCT IF ( sa.code = 1, a.name, NULL ) ) name,

1
2
3
4

# 分页

<!-- pagehelper 分页插件 -->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.2.11</version>
        </dependency>

public DataResult<DataPage<InventoryVo>> queryInventoryList(QueryInventoryDto dto) {
        PageHelper.startPage(dto.getPage(), dto.getPageSize());
        List<InventoryVo> list = basicsStockOutMapper.queryInventoryList(dto);
        PageInfo<InventoryVo> pageInfo = new PageInfo<>(list);
        DataPage<InventoryVo> dataPage = new DataPage<InventoryVo>();
        dataPage.setData(pageInfo.getList());
        dataPage.setTotal(pageInfo.getTotal());
        dataPage.setPages(pageInfo.getPages());
        dataPage.setPageSize(pageInfo.getPageSize());
        dataPage.setPage(pageInfo.getPageNum());
        return success(dataPage, ResultCodeEnum.QUERY_SUCCESS);
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

# mybatis打印执行sql

yml配置

# 第一种配置
mybatis-plus:
  mapper-locations: classpath:mapper/*Mapper.xml
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

# 打印SQL
logging:
  level:
    com.cloudwarehouse.impl.mapper: DEBUG

1
2
3
4
5
6
7
8
9
10

上次更新: 2022/11/16, 20:18:06

← 建表创建索引几分钟处理完30亿个数据→