开发微网站和小程序,wordpress使用教程书,兰州需要做推广的公司,wordpress 安装要求一、背景 在mysql上定义了存储过程#xff0c;然后我想每1分钟调用一次存储过程。于是我设置了一个event#xff0c;但是这个事件默认的运行周期为天#xff0c;我尝试修改成minute却不生效。所以我决定通过java代码来调用存储过程。 二、mysql存储过程
CREATE DEFINERroot…一、背景 在mysql上定义了存储过程然后我想每1分钟调用一次存储过程。于是我设置了一个event但是这个事件默认的运行周期为天我尝试修改成minute却不生效。所以我决定通过java代码来调用存储过程。 二、mysql存储过程
CREATE DEFINERroot% PROCEDURE apporder.ord_shopping()
begin
truncate table ord_shopping_procedure;
insert into ord_shopping_procedure
select * from apporder.ord_shopping_order
union all
select * from apporder.ord_shopping_order_2
union all
select * from apporder.ord_shopping_order_3
union all
select * from apporder.ord_shopping_order_4
union all
select * from apporder.ord_shopping_order_5
union all
select * from apporder.ord_shopping_order_6
union all
select * from apporder.ord_shopping_order_7
union all
select * from apporder.ord_shopping_order_8
union all
select * from apporder.ord_shopping_order_9
union all
select * from apporder.ord_shopping_order_10
union all
select * from apporder.ord_shopping_order_11
union all
select * from apporder.ord_shopping_order_12
union all
select * from apporder.ord_shopping_order_13
union all
select * from apporder.ord_shopping_order_14
union all
select * from apporder.ord_shopping_order_15
union all
select * from apporder.ord_shopping_order_16
union all
select * from apporder.ord_shopping_order_17
union all
select * from apporder.ord_shopping_order_18
union all
select * from apporder.ord_shopping_order_19
union all
select * from apporder.ord_shopping_order_20
union all
select * from apporder.ord_shopping_order_21
union all
select * from apporder.ord_shopping_order_22
union all
select * from apporder.ord_shopping_order_23
union all
select * from apporder.ord_shopping_order_24
union all
select * from apporder.ord_shopping_order_25
union all
select * from apporder.ord_shopping_order_26
union all
select * from apporder.ord_shopping_order_27
union all
select * from apporder.ord_shopping_order_28
union all
select * from apporder.ord_shopping_order_29
union all
select * from apporder.ord_shopping_order_30
union all
select * from apporder.ord_shopping_order_31
union all
select * from apporder.ord_shopping_order_32
union all
select * from apporder.ord_shopping_order_33
union all
select * from apporder.ord_shopping_order_34
union all
select * from apporder.ord_shopping_order_35
union all
select * from apporder.ord_shopping_order_36
union all
select * from apporder.ord_shopping_order_37
union all
select * from apporder.ord_shopping_order_38
union all
select * from apporder.ord_shopping_order_39
union all
select * from apporder.ord_shopping_order_40
union all
select * from apporder.ord_shopping_order_41
union all
select * from apporder.ord_shopping_order_42
union all
select * from apporder.ord_shopping_order_43
union all
select * from apporder.ord_shopping_order_44
union all
select * from apporder.ord_shopping_order_45
union all
select * from apporder.ord_shopping_order_46
union all
select * from apporder.ord_shopping_order_47
union all
select * from apporder.ord_shopping_order_48
union all
select * from apporder.ord_shopping_order_49
union all
select * from apporder.ord_shopping_order_50
union all
select * from apporder.ord_shopping_order_51
union all
select * from apporder.ord_shopping_order_52
union all
select * from apporder.ord_shopping_order_53
union all
select * from apporder.ord_shopping_order_54
union all
select * from apporder.ord_shopping_order_55
union all
select * from apporder.ord_shopping_order_56
union all
select * from apporder.ord_shopping_order_57
union all
select * from apporder.ord_shopping_order_58
union all
select * from apporder.ord_shopping_order_59
union all
select * from apporder.ord_shopping_order_60
union all
select * from apporder.ord_shopping_order_61
union all
select * from apporder.ord_shopping_order_62
union all
select * from apporder.ord_shopping_order_63;truncate table ord_shopping;
insert into ord_shopping
select t.*, JSON_EXTRACT(t.json, $.skuName) as sku_name from (
select id,order_type,order_mode,user_id,user_name,trade_no,state,total_amount, pay_amount, pay_time ,
ou_name ,mobile ,create_time, sale_amount, JSON_EXTRACT(item_abstract_json, $[0]) as json,store_ou_code,store_name
,mch_ou_code,mch_name,pay_mode
from ord_shopping_procedure ) t;truncate table ctg_h5_pay_order;
insert into ctg_h5_pay_order
select id as order_no ,
total_amount as total_price ,
pay_time,
trade_no,
trade_no as pay_no,
mobile as phone,
store_ou_code as store_code ,
store_name,
sku_name as product_id ,
user_id ,
case
when state 1 then 未支付
when state 2 then 支付中
when state 3 then 已支付
when state 7 then 废弃
when state 8 then 已失效
end
state,
create_time,
pay_mode,
case
when state 1 then 未支付
when state 2 then 支付中
when state 3 then 已支付
when state 7 then 废弃
when state 8 then 已失效
endtrade_status
from ord_shopping;END
我定义的event其他定时执行存储过程
CREATE EVENT ord_shopping_event
ON SCHEDULE EVERY 1 DAY
STARTS 2023-11-28 04:51:22.000
ON COMPLETION PRESERVE
ENABLE
DO call ord_shopping()
但是 ON SCHEDULE EVERY 1 DAY 我改为 ON SCHEDULE EVERY 1 MINUTE 却修改不了。
于是我使用java来调用存储过程
三、通过java来调用存储过程
在 接口中定义
Repository public interface TransactionMapper extends BaseMapperTransaction{ /** * 调用存储过程 */ Select(script call ord_shopping() /script) public void callOrdShopping();
} /*** 调用存储过程*/Select(script call ord_shopping() /script)public void callOrdShopping();
通过service调用 /** * 调用存储过程 */ public void callOrdShopping() { transactionMapper.callOrdShopping(); } /*** 调用存储过程*/public void callOrdShopping() {transactionMapper.callOrdShopping();}通过定时任务调用
Slf4j Service Transactional(rollbackFor Exception.class) public class ToolServiceThread implements ApplicationRunner { Autowired private TransactionService transactionService; /** * 交易信息上链定时任务 早上2点10分 */ // Scheduled(cron 0 10 2 * * ?) Scheduled(cron 0 0/1 * * * ?) public void callOrdShopping() throws Exception { log.info(调用交易信息存储过程); transactionService.callOrdShopping(); } } /*** 1分钟调用一次*/// Scheduled(cron 0 0/1 * * * ?)public void callOrdShopping() throws Exception {log.info(调用交易信息存储过程);transactionService.callOrdShopping();}
