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的调用改成