java调用mysql存储过程_java调用mysql存储过程代码-CSDN博客

本文链接：https://blog.csdn.net/dongjing991/article/details/134735842

一、背景

在mysql上定义了存储过程，然后我想每1分钟调用一次存储过程。于是我设置了一个event，但是这个事件默认的运行周期为天，我尝试修改成minute却不生效。所以我决定通过java代码来调用存储过程。

二、mysql存储过程

CREATE DEFINER=`root`@`%` 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 '已失效'
end
 trade_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 BaseMapper<Transaction>{

   /**
   * 调用存储过程
   */
   @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();
	}