SET @batch_date = '20230222'; set @partition_name=concat('p',@batch_date); set @stat=concat('alter table tb_bond_price_comparision_reuslt truncate partition ',@partition_name); prepare stmt from @stat; execute stmt; insert into tb_bond_price_comparision_reuslt select distinct org.ISIN, mul.Broker_Yield, mul.Broker_Price, org.MARKET_PRICE, case when org.MARKET_PRICE > mul.Broker_Price then 'Broker Price' else 'BVAL' end as MARKET_PRICE_MIN, org.TRADE_REF, case when org.MARKET_PRICE > mul.Broker_Price then mul.Broker_Price else org.MARKET_PRICE end as MARKET_PRICE_MIN, case when org.MARKET_PRICE > mul.Broker_Price then mul.Broker_Price * org.NOMINAL_AMT else org.MARKET_PRICE * org.NOMINAL_AMT end as MARKET_VALUE_MIN, org.dt from tb_trade_blotter_op_sec org left join tb_bond_price_comparision_trade_blotter_op_sec_mul mul on org.TRADE_REF = mul.TRADE_REF and org.ISIN = mul.ISIN and org.dt = mul.dt where org.dt = @batch_date转化为mybatis语句，并且service层如何设计

Mapper文件：

<select id="getBondPriceComparisonResult" parameterType="java.util.Map" resultMap="BondPriceComparisonResult">
    SET @batch_date = #{batchDate};
    SET @partition_name=concat('p',@batch_date);
    SET @stat=concat('alter table tb_bond_price_comparision_reuslt truncate partition ',@partition_name);
    PREPARE stmt from @stat;
    EXECUTE stmt;
    insert into tb_bond_price_comparision_reuslt select distinct org.ISIN, mul.Broker_Yield, mul.Broker_Price, org.MARKET_PRICE, case when org.MARKET_PRICE > mul.Broker_Price then 'Broker Price' else 'BVAL' end as MARKET_PRICE_MIN, org.TRADE_REF, case when org.MARKET_PRICE > mul.Broker_Price then mul.Broker_Price else org.MARKET_PRICE end as MARKET_PRICE_MIN, case when org.MARKET_PRICE > mul.Broker_Price then mul.Broker_Price * org.NOMINAL_AMT else org.MARKET_PRICE * org.NOMINAL_AMT end as MARKET_VALUE_MIN, org.dt from tb_trade_blotter_op_sec org left join tb_bond_price_comparision_trade_blotter_op_sec_mul mul on org.TRADE_REF = mul.TRADE_REF and org.ISIN = mul.ISIN and org.dt = mul.dt where org.dt = @batch_date
</select>

Service层：

public interface BondPriceComparisonService {
    List<BondPriceComparisonResult> getBondPriceComparisonResult(String batchDate);
}

实现类：

@Service
public class BondPriceComparisonServiceImpl implements BondPriceComparisonService {

    @Autowired
    private BondPriceComparisonMapper bondPriceComparisonMapper;

    @Override
    public List<BondPriceComparisonResult> getBondPriceComparisonResult(String batchDate) {
        Map<String, Object> params = new HashMap<>();
        params.put("batchDate", batchDate);
        return bondPriceComparisonMapper.getBondPriceComparisonResult(params);
    }
}