mysql存储过程

mapper里原始sql

select        tss.channel_id as channelId,        tssr.risk_id as riskId,        count(tssr.risk_id) as riskCount        from  ${riskTableName} tssr        right JOIN ${salesmanTableName} tss        on tssr.salesman_id = tss.salesman_idWHEREtss.organization_id like CONCAT('',#{organCode,jdbcType=VARCHAR},'%')        <if test="channelId != null and channelId !='' ">        and tss.channel_id=#{channelId,jdbcType=VARCHAR}        </if>        group by tss.channel_id,tssr.risk_id        order by tss.channel_id,count(tssr.risk_id) DESC

写成存储过程后

CREATE PROCEDURE pieProcedure(IN organizationId VARCHAR(200),IN riskTableName VARCHAR(200),IN channelId VARCHAR(200))BEGINset @sqlStr=concat('select         tssr.channel_code as channelId,        tssr.risk_id as riskId,        count(tssr.risk_id) as riskCount        from  ',riskTableName,' tssr       WHEREinstr(organization_code,',organizationId,') ',channelId,'        group by tssr.channel_code,tssr.risk_id        order by tssr.channel_code,count(tssr.risk_id) DESC');PREPARE stmt from @sqlStr;EXECUTE stmt; end;

mapper.xml中改成

<select id="getFiveRiskByDay" resultType="com.sinosig.app.project.entity.TSlaqSalesmanRisk" parameterType="java.lang.String">    call pieProcedure(#{organCode,jdbcType=VARCHAR},#{riskTableName},#{channelId})    </select>

service中对mapper的调用改成