mybatis中orderBy(排序字段)和sort(排序方式)引起的bug及解决

引言

记录一个mybatis实现动态字段的排序和动态的升降序问题

实现效果如下:

问题叙述

在这里无论使用postman是否传递sort的值， 都不生效，

postman

执行的sql日志

==>  Preparing: select sum(acd.read_view_count) as read_view_count,sum(acd.read_person_count) as read_person_count, sum(acd.like_count) as like_count,sum(acd.comment_count) as comment_count, sum(acd.collect_count) as collect_count,sum(acd.transmit_count) as transmit_count, avg(acd.avg_stay_time) as avg_stay_time,avg(acd.visit_count) as visit_count, acd.article_id, acd.article_title from article_content_data acd left join cplatfORM cp on cp.cid = acd.platform_id WHERE 1=1 group by acd.article_id desc order by transmit_count 
==> Parameters: 
<==    Columns: read_view_count, read_person_count, like_count, comment_count, collect_count, transmit_count, avg_stay_time, visit_count, article_id, article_title
<==        Row: 900, 900, 960, 390, 12592, 423, 1548.3333, 1.0000, 3, 这是一个正经的文章标题3
<==        Row: 1500, 1500, 1560, 390, 1497, 423, 5294.6667, 1.0000, 5, 这是一个正经的文章标题5
<==        Row: 400, 440, 480, 520, 526, 546, 419.0000, 1.0000, 1, 震惊, 百分之99的人在喝了睡之后, 100年之内会死
<==        Row: 800, 800, 880, 520, 2587, 777, 426.2500, 1.0000, 2, 这是一个正经的文章标题2
<==        Row: 1200, 1200, 1260, 390, 386, 816, 121.0000, 1.0000, 4, 这是一个正经的文章标题4
<==      Total: 5

**很明显, order by之后只拼了排序字段, 而没有拼上排序方式 **

下面尝试采用第二种方式

将排序sql语句替换为以下这个

                order by
                ${orderBy}
                <choose>
                    <when test="sort!=null">
                        ${sort}
                    </when>
                    <otherwise>
                        asc
                    </otherwise>
                </choose>

完整代码如下

    <select id="listArticleContentDataDTO" resultType="com.etouch.pojo.dto.data.ArticleContentDataDTO">
        select
        sum(acd.read_view_count) as read_view_count,sum(acd.read_person_count) as read_person_count,
        sum(acd.like_count) as like_count,sum(acd.comment_count) as comment_count,
        sum(acd.collect_count) as collect_count,sum(acd.transmit_count) as transmit_count,
        avg(acd.avg_stay_time) as avg_stay_time,avg(acd.visit_count) as visit_count,
        acd.article_id, acd.article_title
        from article_content_data acd
        left join cplatform cp on cp.cid = acd.platform_id
        <include refid="searchArticleData"/>
        group by acd.article_id
        order by
        ${orderBy}
        <choose>
            <when test="sort!=null">
                ${sort}
            </when>
            <otherwise>
                asc
            </otherwise>
        </choose>
    </select>

执行查询,结果如下

==>  Preparing: SELECT sum(acd.read_view_count) AS read_view_count, sum(acd.read_person_count) AS read_person_count, sum(acd.like_count) AS like_count, sum(acd.comment_count) AS comment_count, sum(acd.collect_count) AS collect_count, sum(acd.transmit_count) AS transmit_count, avg(acd.avg_stay_time) AS avg_stay_time, avg(acd.visit_count) AS visit_count, acd.article_id, acd.article_title FROM article_content_data acd LEFT JOIN cplatform cp ON cp.cid = acd.platform_id WHERE 1 = 1 GROUP BY acd.article_id order by transmit_count 
==> Parameters: 
<==    Columns: read_view_count, read_person_count, like_count, comment_count, collect_count, transmit_count, avg_stay_time, visit_count, article_id, article_title
<==        Row: 900, 900, 960, 390, 12592, 423, 1548.3333, 1.0000, 3, 这是一个正经的文章标题3
<==        Row: 1500, 1500, 1560, 390, 1497, 423, 5294.6667, 1.0000, 5, 这是一个正经的文章标题5
<==        Row: 400, 440, 480, 520, 526, 546, 419.0000, 1.0000, 1, 震惊, 百分之99的人在喝了睡之后, 100年之内会死
<==        Row: 800, 800, 880, 520, 2587, 777, 426.2500, 1.0000, 2, 这是一个正经的文章标题2
<==        Row: 1200, 1200, 1260, 390, 386, 816, 121.0000, 1.0000, 4, 这是一个正经的文章标题4
<==      Total: 5

同样是没有实现排序,此时应该考虑,mybatis是否无法同时接受orderBy和sort都是变量的情况, 带着这个疑问, 将order by的排序字段设置为固定值, sort设置为前端传递过来的值, 代码如下

        order by
        acd.like_count
        <choose>
            <when test="sort!=null">
                ${sort}
            </when>
            <otherwise>
                asc
            </otherwise>
        </choose>

执行sql日志如下

==>  Preparing: select sum(acd.read_view_count) as read_view_count,sum(acd.read_person_count) as read_person_count, sum(acd.like_count) as like_count,sum(acd.comment_count) as comment_count, sum(acd.collect_count) as collect_count,sum(acd.transmit_count) as transmit_count, avg(acd.avg_stay_time) as avg_stay_time,avg(acd.visit_count) as visit_count, acd.article_id, acd.article_title from article_content_data acd left join cplatform cp on cp.cid = acd.platform_id WHERE 1=1 group by acd.article_id order by acd.like_count asc 
==> Parameters: 
<==    Columns: read_view_count, read_person_count, like_count, comment_count, collect_count, transmit_count, avg_stay_time, visit_count, article_id, article_title
<==        Row: 400, 440, 480, 520, 526, 546, 419.0000, 1.0000, 1, 震惊, 百分之99的人在喝了睡之后, 100年之内会死
<==        Row: 800, 800, 880, 520, 2587, 777, 426.2500, 1.0000, 2, 这是一个正经的文章标题2
<==        Row: 900, 900, 960, 390, 12592, 423, 1548.3333, 1.0000, 3, 这是一个正经的文章标题3
<==        Row: 1200, 1200, 1260, 390, 386, 816, 121.0000, 1.0000, 4, 这是一个正经的文章标题4
<==        Row: 1500, 1500, 1560, 390, 1497, 423, 5294.6667, 1.0000, 5, 这是一个正经的文章标题5
<==      Total: 5
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@32112885]

这里发现实现了升降序, 但是这样不能满足原型上的要求, 动态的字段排序和升降序

这里不妨换种方式思考, 假如将排序字段和排序方式拼的一起, 存放到一个字段里呢? 带着这个猜想, 进行第三种方式

第三种方式

代码

 <choose>
            <when test="orderBy!=null and orderBy!=''">
                order by ${orderBy}
            </when>
            <otherwise>
                order by acd.create_time asc
            </otherwise>
        </choose>

3.1 首先是什么都不传

直接sql结果如下

==>  Preparing: select sum(acd.read_view_count) as read_view_count,sum(acd.read_person_count) as read_person_count, sum(acd.like_count) as like_count,sum(acd.comment_count) as comment_count, sum(acd.collect_count) as collect_count,sum(acd.transmit_count) as transmit_count, avg(acd.avg_stay_time) as avg_stay_time,avg(acd.visit_count) as visit_count, acd.article_id, acd.article_title from article_content_data acd left join cplatform cp on cp.cid = acd.platform_id WHERE 1=1 group by acd.article_id order by acd.create_time asc 
==> Parameters: 
<==    Columns: read_view_count, read_person_count, like_count, comment_count, collect_count, transmit_count, avg_stay_time, visit_count, article_id, article_title
<==        Row: 400, 440, 480, 520, 526, 546, 419.0000, 1.0000, 1, 震惊, 百分之99的人在喝了睡之后, 100年之内会死
<==        Row: 800, 800, 880, 520, 2587, 777, 426.2500, 1.0000, 2, 这是一个正经的文章标题2
<==        Row: 900, 900, 960, 390, 12592, 423, 1548.3333, 1.0000, 3, 这是一个正经的文章标题3
<==        Row: 1200, 1200, 1260, 390, 386, 816, 121.0000, 1.0000, 4, 这是一个正经的文章标题4
<==        Row: 1500, 1500, 1560, 390, 1497, 423, 5294.6667, 1.0000, 5, 这是一个正经的文章标题5
<==      Total: 5

默认采用创建日期排序, 避免前端不传排序字段导致报错

3.2 传入排序字段oderBy

这里将排序字段和排序方式用空格隔开, 存入orderBy中

查看sql执行结果

==>  Preparing: SELECT sum(acd.read_view_count) AS read_view_count, sum(acd.read_person_count) AS read_person_count, sum(acd.like_count) AS like_count, sum(acd.comment_count) AS comment_count, sum(acd.collect_count) AS collect_count, sum(acd.transmit_count) AS transmit_count, avg(acd.avg_stay_time) AS avg_stay_time, avg(acd.visit_count) AS visit_count, acd.article_id, acd.article_title FROM article_content_data acd LEFT JOIN cplatform cp ON cp.cid = acd.platform_id WHERE 1 = 1 GROUP BY acd.article_id order by like_count desc 
==> Parameters: 
<==    Columns: read_view_count, read_person_count, like_count, comment_count, collect_count, transmit_count, avg_stay_time, visit_count, article_id, article_title
<==        Row: 1500, 1500, 1560, 390, 1497, 423, 5294.6667, 1.0000, 5, 这是一个正经的文章标题5
<==        Row: 1200, 1200, 1260, 390, 386, 816, 121.0000, 1.0000, 4, 这是一个正经的文章标题4
<==        Row: 900, 900, 960, 390, 12592, 423, 1548.3333, 1.0000, 3, 这是一个正经的文章标题3
<==        Row: 800, 800, 880, 520, 2587, 777, 426.2500, 1.0000, 2, 这是一个正经的文章标题2
<==        Row: 400, 440, 480, 520, 526, 546, 419.0000, 1.0000, 1, 震惊, 百分之99的人在喝了睡之后, 100年之内会死
<==      Total: 5

很明显, 实现了动态排序, 大功告成, 接下来我会继续研究,到底是为什么myabtis不能同时接受orderBy和sort同时为变量的情况, 欢迎评论区补充

ps：order by或者group by后面不可以使用#{变量}的方式, 因为mybatis会把#{变量}进行预编译, 这也是为了防止sql注入, 也就是编译成了order by ‘字段名’ , 也就会导致排序失败, 这里只能采用${变量}的方式, ${}中的内容会不经过编译直接拼到order by后面, 但是不能避免sql注入, 可以再后台通过限制order by变量长度的方式, 来避免sql注入, 是麻烦了点, 但是鱼和熊掌不可兼得

以上为个人经验，希望能给大家一个参考，也希望大家多多支持编程网。