引言

記錄一個(gè)mybatis實(shí)現(xiàn)動(dòng)態(tài)字段的排序和動(dòng)態(tài)的升降序問(wèn)題

實(shí)現(xiàn)效果如下:

問(wèn)題敘述

在這里無(wú)論使用postman是否傳遞sort的值， 都不生效，

postman

執(zhí)行的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, 這是一個(gè)正經(jīng)的文章標(biāo)題3
<==        Row: 1500, 1500, 1560, 390, 1497, 423, 5294.6667, 1.0000, 5, 這是一個(gè)正經(jīng)的文章標(biāo)題5
<==        Row: 400, 440, 480, 520, 526, 546, 419.0000, 1.0000, 1, 震驚, 百分之99的人在喝了睡之后, 100年之內(nèi)會(huì)死
<==        Row: 800, 800, 880, 520, 2587, 777, 426.2500, 1.0000, 2, 這是一個(gè)正經(jīng)的文章標(biāo)題2
<==        Row: 1200, 1200, 1260, 390, 386, 816, 121.0000, 1.0000, 4, 這是一個(gè)正經(jīng)的文章標(biāo)題4
<==      Total: 5

**很明顯, order by之后只拼了排序字段, 而沒(méi)有拼上排序方式 **

下面嘗試采用第二種方式

將排序sql語(yǔ)句替換為以下這個(gè)

                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>

執(zhí)行查詢(xún),結(jié)果如下

==>  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, 這是一個(gè)正經(jīng)的文章標(biāo)題3
<==        Row: 1500, 1500, 1560, 390, 1497, 423, 5294.6667, 1.0000, 5, 這是一個(gè)正經(jīng)的文章標(biāo)題5
<==        Row: 400, 440, 480, 520, 526, 546, 419.0000, 1.0000, 1, 震驚, 百分之99的人在喝了睡之后, 100年之內(nèi)會(huì)死
<==        Row: 800, 800, 880, 520, 2587, 777, 426.2500, 1.0000, 2, 這是一個(gè)正經(jīng)的文章標(biāo)題2
<==        Row: 1200, 1200, 1260, 390, 386, 816, 121.0000, 1.0000, 4, 這是一個(gè)正經(jīng)的文章標(biāo)題4
<==      Total: 5

同樣是沒(méi)有實(shí)現(xiàn)排序,此時(shí)應(yīng)該考慮,mybatis是否無(wú)法同時(shí)接受orderBy和sort都是變量的情況, 帶著這個(gè)疑問(wèn), 將order by的排序字段設(shè)置為固定值, sort設(shè)置為前端傳遞過(guò)來(lái)的值, 代碼如下

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

執(zhí)行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年之內(nèi)會(huì)死
<==        Row: 800, 800, 880, 520, 2587, 777, 426.2500, 1.0000, 2, 這是一個(gè)正經(jīng)的文章標(biāo)題2
<==        Row: 900, 900, 960, 390, 12592, 423, 1548.3333, 1.0000, 3, 這是一個(gè)正經(jīng)的文章標(biāo)題3
<==        Row: 1200, 1200, 1260, 390, 386, 816, 121.0000, 1.0000, 4, 這是一個(gè)正經(jīng)的文章標(biāo)題4
<==        Row: 1500, 1500, 1560, 390, 1497, 423, 5294.6667, 1.0000, 5, 這是一個(gè)正經(jīng)的文章標(biāo)題5
<==      Total: 5
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@32112885]

這里發(fā)現(xiàn)實(shí)現(xiàn)了升降序, 但是這樣不能滿(mǎn)足原型上的要求, 動(dòng)態(tài)的字段排序和升降序

這里不妨換種方式思考, 假如將排序字段和排序方式拼的一起, 存放到一個(gè)字段里呢? 帶著這個(gè)猜想, 進(jìn)行第三種方式

第三種方式

代碼

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

3.1 首先是什么都不傳

直接sql結(jié)果如下

==>  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年之內(nèi)會(huì)死
<==        Row: 800, 800, 880, 520, 2587, 777, 426.2500, 1.0000, 2, 這是一個(gè)正經(jīng)的文章標(biāo)題2
<==        Row: 900, 900, 960, 390, 12592, 423, 1548.3333, 1.0000, 3, 這是一個(gè)正經(jīng)的文章標(biāo)題3
<==        Row: 1200, 1200, 1260, 390, 386, 816, 121.0000, 1.0000, 4, 這是一個(gè)正經(jīng)的文章標(biāo)題4
<==        Row: 1500, 1500, 1560, 390, 1497, 423, 5294.6667, 1.0000, 5, 這是一個(gè)正經(jīng)的文章標(biāo)題5
<==      Total: 5

默認(rèn)采用創(chuàng)建日期排序, 避免前端不傳排序字段導(dǎo)致報(bào)錯(cuò)

3.2 傳入排序字段oderBy

這里將排序字段和排序方式用空格隔開(kāi), 存入orderBy中

查看sql執(zhí)行結(jié)果

==>  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, 這是一個(gè)正經(jīng)的文章標(biāo)題5
<==        Row: 1200, 1200, 1260, 390, 386, 816, 121.0000, 1.0000, 4, 這是一個(gè)正經(jīng)的文章標(biāo)題4
<==        Row: 900, 900, 960, 390, 12592, 423, 1548.3333, 1.0000, 3, 這是一個(gè)正經(jīng)的文章標(biāo)題3
<==        Row: 800, 800, 880, 520, 2587, 777, 426.2500, 1.0000, 2, 這是一個(gè)正經(jīng)的文章標(biāo)題2
<==        Row: 400, 440, 480, 520, 526, 546, 419.0000, 1.0000, 1, 震驚, 百分之99的人在喝了睡之后, 100年之內(nèi)會(huì)死
<==      Total: 5

很明顯, 實(shí)現(xiàn)了動(dòng)態(tài)排序, 大功告成, 接下來(lái)我會(huì)繼續(xù)研究,到底是為什么myabtis不能同時(shí)接受orderBy和sort同時(shí)為變量的情況, 歡迎評(píng)論區(qū)補(bǔ)充

ps：order by或者group by后面不可以使用#{變量}的方式, 因?yàn)閙ybatis會(huì)把#{變量}進(jìn)行預(yù)編譯, 這也是為了防止sql注入, 也就是編譯成了order by ‘字段名’ , 也就會(huì)導(dǎo)致排序失敗, 這里只能采用${變量}的方式, ${}中的內(nèi)容會(huì)不經(jīng)過(guò)編譯直接拼到order by后面, 但是不能避免sql注入, 可以再后臺(tái)通過(guò)限制order by變量長(zhǎng)度的方式, 來(lái)避免sql注入, 是麻煩了點(diǎn), 但是魚(yú)和熊掌不可兼得

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

最新評(píng)論