Java配合mysql实时统计业务_mysql数据库 ，java 代码巧妙结合提升系统性能。

存储过程：

BEGIN

DECLARE

counts INTEGER;

-- 记录迁移条数

-- DECLARE

-- '2018-07-01' datetime;

-- 定义一个时间变量用来存放时间

DECLARE

result_code INTEGER;

-- 定义返回结果并赋初始值

DECLARE

CONTINUE HANDLER FOR SQLEXCEPTION

SET result_code = 1;

-- 在执行过程中出现任何异常result_code为1

-- SELECT

-- '2018-07-01' INTO '2018-07-01';

-- 获取今日零点三个月之前的时间

SELECT

count(1) INTO counts

FROM

(

SELECT

t.*

FROM

t_yh_transport_task t

WHERE

t.task_type = 1

AND t.`create_time` <= '2018-07-01'

UNION

SELECT

t.*

FROM

t_yh_transport_task t

LEFT JOIN t_yh_ordertask_extend e ON e.transport_task_id = t.transport_task_id

WHERE

t.task_type = 2

AND e.`order_time` <= '2018-07-01'

) a;

-- WHERE

-- a.task_status = '33'

-- OR a.task_status = '99';

START TRANSACTION;

-- 进行事物处理

-- 向历史表插入历史数据

INSERT INTO t_yh_transport_task_history SELECT

NULL,

a.transport_task_id,

a.user_id,

a.create_time,

a.task_Ftype,

a.task_Ctype,

a.task_content,

a.task_org,

a.transport_tool,

a.task_Departure,

a.task_Destination,

a.task_status,

a.task_startTime,

a.task_endTime,

a.exc_user_id,

a.task_detail,

a.affiliated_hospital,

a.owned_property,

a.task_type,

a.cancel_reason,

a.parent_task_id,

a.destination_org,

a.task_code,

a.task_from,

a.is_lock,

now(),

a.change_tool_time,

a.end_reason,

a.update_time,

a.plan_no,

a.wandaihao

FROM

(

SELECT

t.*

FROM

t_yh_transport_task t

WHERE

t.task_type = 1

AND t.`create_time` <= '2018-07-01'

UNION

SELECT

t.*

FROM

t_yh_transport_task t

LEFT JOIN t_yh_ordertask_extend e ON e.transport_task_id = t.transport_task_id

WHERE

t.task_type = 2

AND e.`order_time` <= '2018-07-01'

) a;

-- WHERE

-- a.task_status = '33'

-- OR a.task_status = '99';

-- 插入后删除三个月前的数据

DELETE

FROM

t_yh_transport_task

WHERE

t_yh_transport_task.transport_task_id IN (

SELECT

a.transport_task_id

FROM

(

SELECT

t.*

FROM

t_yh_transport_task t

WHERE

t.task_type = 1

AND t.`create_time` <= '2018-07-01'

UNION

SELECT

t.*

FROM

t_yh_transport_task t

LEFT JOIN t_yh_ordertask_extend e ON e.transport_task_id = t.transport_task_id

WHERE

t.task_type = 2

AND e.`order_time` <= '2018-07-01'

) a

-- WHERE

-- a.task_status = '33'

-- OR a.task_status = '99'

);

-- 插入日志

INSERT INTO t_yh_move_data_log (time, tablename, count)

VALUES

(

NOW(),

't_yh_transport_task',

counts

);

IF result_code = 1 THEN

-- 发生异常就回滚反之就提交

ROLLBACK;

ELSE

COMMIT;

END

IF;

END