动态sql拼接

有时候的需求很繁琐，如果单纯的跟着写；

可能会一个service使用五六个sql的statment;

如果拼接动态sql的话就可能一个mapper里 方法就可以了，

相对应的，拼接的时候有些可能会拼错。

select tt.a,tt.b, #{c} as cc,tt.d,
        ylt.e as ylt 
        from
        (
        select
        <if test="adv != '999999'.toString()">
            ga.adm,
            <choose>
                <when test="adv == null or adv=='' or adv =='000000'.toString() ">
                    substr( tg.av,0, 2 )
                </when>
                <when test="adv.substring(2)=='0000'.toString() ">
                    substr( tg.adv, 0, 4 )
                </when>
                <when test="adv.substring(4) =='00'.toString() ">
                    tg.adv
                </when>
            </choose>
            AS adv,
        </if>
        <if test="adv == '999999'.toString()">
            '全国' as dv, '999999' as adv,
        </if>
        count(1) as total
        from tta tg
        <if test="adv != '999999'.toString()">
            inner JOIN ta ga
            <choose>
                <when test="dav == null or adv=='' or adv=='000000'.toString() ">
                    ON ga.adv = substr( tg.adv, 0, 2 ) || '0000'
                </when>
                <when test="adv.substring(2)=='0000'.toString() ">
                    ON ga.adv = substr( tg.adv, 0, 4 ) || '00'
                </when>
                <when test="adv.substring(4) =='00'.toString() ">
                    ON ga.av = tg.adv
                </when>
                <otherwise>
                    ON ga.adv = tg.adv
                </otherwise>
            </choose>
        </if>
        inner join (
        select st from tb so
        <where>sp=0
            <if test="f != null and f != ''">
                and so.f =#{f}
            </if>
        </where>
        ) bo on bo.st= tg.st
        inner JOIN td td ON td.st = g.st
        INNER JOIN tta tta ON tta.st = a.st  

可以看到，sql的拼接中大量运用 <choose><when>标签，

根据进入的条件，拼接不同的sql语句，

标签既会出现在from 之前，也可能出现在 join on 之后的链接条件中，

当然where之后也可以使用，亲测；

有了 choose when 妈妈再也不用担心，sql特别杂碎了。