Mybatis-plus如何在xml中传入自定义的SQL语句_Java

项目场景

项目需求需要在一个框架上二开，原框架有权限校验会返回对应的权限sql语句。

例如：

在获取用户当前组织及下级组织后拼接生成的sql：

and (
    settlement_company_id in ( 
        '06eb1b4ce4d24e368319188bb4fc6390', 
        '9ff034739e984f188612c671219ddfed', 
        'afe69288c0b84828bd2abcb1c5eec28a' 
    ) 
)

因为有使用到关联查询，无法直接使用querywrapper的apply来拼接语句，只能在xml中拼接。

问题描述

查询传入实体：

@data
public class yygkbankaccountqueryform {
        /**
         * 数据权限sql
         */
        @apimodelproperty(value = "数据权限sql")
        private string extrasql;
}

mapper类：

public interface yygkbankaccountmapper extends basemapper<yygkbankaccountentity> {
    /**
     * 分页查询银行账户
     *
     * @param ipage                 分页数据
     * @param yygkbankinfoqueryform 查询参数
     * @return ipage<yygkbankaccountentity>
     */
    ipage<yygkbankaccountentity> querypage(ipage<yygkbankaccountentity> ipage, @param("query") yygkbankaccountqueryform yygkbankinfoqueryform);
}

xml的查询sql语句：

<select id="querypage" resultmap="basemap">
    select
    <include refid="yygkbankaccountsql"/>
    from yygk_bank_account ba
    left join yygk_currency_info ci on ba.currency_id = ci.id
    <where>
        ba.delete_mark != 1
        <if test="query.extrasql != null and query.extrasql != ''">
            and #{query.extrasql}
        </if>
    </where>
    order by ba.sort desc
</select>

query.extrasql为我需要拼接的sql语句。

执行查询的打印日志：

2022-06-17 18:59:55.981 debug 16768 --- [io-30601-exec-1] c.g.y.m.y.querypage_mpcount : ==> preparing: select count(1) from yygk_bank_account ba where ba.delete_mark != 1 and ?
2022-06-17 18:59:56.088 debug 16768 --- [io-30601-exec-1] c.g.y.m.y.querypage_mpcount : ==> parameters: (settlement_company_id in( '06eb1b4ce4d24e368319188bb4fc6390','9ff034739e984f188612c671219ddfed','afe69288c0b84828bd2abcb1c5eec28a' ) )(string)
2022-06-17 18:59:56.174 debug 16768 --- [io-30601-exec-1] c.g.y.m.y.querypage_mpcount : <== total: 1

可以看到在执行了mybatis-plus的count查询后并没有再执行查询数据

原因分析

怀疑是要用sql注入才能拼接sql语句

解决方案

xml的sql语句改为使用sql注入：

<select id="querypage" resultmap="basemap">
    select
    <include refid="yygkbankaccountsql"/>
    from yygk_bank_account ba
    left join yygk_currency_info ci on ba.currency_id = ci.id
    <where>
        ba.delete_mark != 1
        <if test="query.extrasql != null and query.extrasql != ''">
            and ${query.extrasql}
        </if>
    </where>
    order by ba.sort desc
</select>

将#{}替换成${}

2022-06-17 19:21:05.437 debug 9008 --- [io-30601-exec-2] c.g.y.m.y.querypage_mpcount : ==> preparing: select count(1) from yygk_bank_account ba where ba.delete_mark != 1 and (settlement_company_id in ('06eb1b4ce4d24e368319188bb4fc6390', '9ff034739e984f188612c671219ddfed', 'afe69288c0b84828bd2abcb1c5eec28a'))
2022-06-17 19:21:05.523 debug 9008 --- [io-30601-exec-2] c.g.y.m.y.querypage_mpcount : ==> parameters:
2022-06-17 19:21:05.598 debug 9008 --- [io-30601-exec-2] c.g.y.m.y.querypage_mpcount : <== total: 1
2022-06-17 19:21:05.630 debug 9008 --- [io-30601-exec-2] c.g.y.m.yygkbankaccountmapper.querypage : ==> preparing: select ci.currency_code as currency_code, ba.id, ba.bank_id, ba.bank_name, ba.bank_short_name, ba.bank_english_name, ba.swift_code, ba.account_name, ba.sort, ba.account_number, ba.receipts_payment_type, ba.internal_account, ba.settlement_type, ba.account_type, ba.deposit_type, ba.settlement_company_id, ba.settlement_company_name, ba.default_mark, ba.currency_id, ba.currency_code, ba.account_identification, ba.opening_bank_identification, ba.billing_account, ba.remark, ba.creator_time, ba.creator_user_id, ba.creator_user, ba.last_modify_time, ba.last_modify_user_id, ba.last_modify_user, ba.enable_mark, ba.delete_time, ba.delete_user_id, ba.delete_mark from yygk_bank_account ba left join yygk_currency_info ci on ba.currency_id = ci.id where ba.delete_mark != 1 and (settlement_company_id in( '06eb1b4ce4d24e368319188bb4fc6390','9ff034739e984f188612c671219ddfed','afe69288c0b84828bd2abcb1c5eec28a' ) ) order by ba.sort desc limit ?
2022-06-17 19:21:05.635 debug 9008 --- [io-30601-exec-2] c.g.y.m.yygkbankaccountmapper.querypage : ==> parameters: 20(long)
2022-06-17 19:21:05.651 debug 9008 --- [io-30601-exec-2] c.g.y.m.yygkbankaccountmapper.querypage : <== total: 2

可以看到在执行了mybatis-plus的count查询后，继续执行了查询数据语句。

虽然问题解决，但我还是不理解为何将#{}换成${}就能成功。。。