mybatis sql mapper 用map的写法

最新推荐文章于 2024-12-29 17:20:42 发布

随波足流

最新推荐文章于 2024-12-29 17:20:42 发布

阅读量4.9k

点赞数 3

CC 4.0 BY-SA版权

分类专栏：数据库 mybatis 文章标签： mybatis sql

本文链接：https://blog.youkuaiyun.com/chenaini119/article/details/51966862

数据库同时被 2 个专栏收录

9 篇文章

订阅专栏

mybatis

7 篇文章

订阅专栏

本文详细解释了MySQL中用于计算字符串长度的两个函数：LENGTH() 和 CHAR_LENGTH() 的区别及应用场景，并通过具体示例展示了如何在mapper层使用map类型参数进行SQL查询。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

CHAR_LENGTH(str)
返回值为字符串str 的长度，长度的单位为字符。一个多字节字符算作一个单字符。对于一个包含五个二字节字符集, LENGTH()返回值为 10,而CHAR_LENGTH()的返回值为5。
CHARACTER_LENGTH(str)
CHARACTER_LENGTH()是CHAR_LENGTH()的同义词。
BIT_LENGTH(str)
返回2进制长度.
简单的总结来说，mysql中获取字符串长度的有两个函数：
length:返回字符串所占的字节数，是计算字段的长度一个汉字是算三个字符,一个数字或字母算一个字符
char_length:返回字符串所占的字符数，不管汉字还是数字或者是字母都算是一个字符

有时候写mapper的时候用到map类型的传参，写法如下

// 获取方法查询入参
        public Map<String, Object> getCommonParams(CaseRecordsInfoModel caseRecordsInfoModel, Pageable pageable) {
            Map<String, Object> params = new HashMap<String, Object>();
            if (caseRecordsInfoModel.getRecord() != null) {
                if (StringUtils.isNotBlank(caseRecordsInfoModel.getRecord().getUserId())) {
                    params.put("userId", caseRecordsInfoModel.getRecord().getUserId());
                }
            if (pageable != null) {
                params.put("offset", pageable.getOffset());
                params.put("pageSize", pageable.getPageSize());
            }
            return params;
        }

service 层

    @Override
    public int selectPageByParamsCount(Map<String, Object> params, Pageable pageable) {
        return caseRecordsInfoRepo.selectPageByParamsCount(params);
    }

mapper层

    <select id="selectPageByParams" resultMap="BaseResultMap" parameterType="Map">
        select
        <include refid="Base_Column_Lists" />
        from case_records_info a , case_records record  where record.if_out=0
        <if test="_parameter.containsKey('userId')" >
            and record.user_id not in
            <foreach item="item" index="index" collection="items"
                     open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="_parameter.containsKey('ifAboveM7') and ifAboveM7 =='false'.toString()" >
            and record.collection_type in 
            <foreach item="item" index="index" collection="types"
                     open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="_parameter.containsKey('ifAboveM7') and ifAboveM7=='true'.toString()" >
            and (record.collection_type > 'M7' or LENGTH(record.collection_type)>2 or record.collection_type in 
            <foreach item="item" index="index" collection="types"
                     open="(" separator="," close=")">
                #{item}
            </foreach>)
        </if>
        <if test="_parameter.containsKey('sendAreaName') and ifNoAdr=='true'.toString()">
           and a.send_area_name = ''
        </if>
         <if test="_parameter.containsKey('sendAreaName') and ifNoAdr=='false'.toString()">
           and a.send_area_name like CONCAT(#{sendAreaName},'%' ) 
        </if>
        and a.case_id=record.id and a.is_deleted=0 and
        record.is_deleted=0  ORDER  BY record.user_id ASC
        <if  test="_parameter.containsKey('offset')">
            limit #{offset},#{pageSize}
        </if>
    </select>