xxl-job2.1.2集成postgresql

admin模块改造

引入依赖

xxl-job-adminmodule中引入一下依赖

		<!-- 引入数据源 与数据库 -->
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>druid</artifactId>
			<version>1.1.12</version>
		</dependency>
		<dependency>
			<groupId>org.postgresql</groupId>
			<artifactId>postgresql</artifactId>
			<scope>runtime</scope>
		</dependency>

配置数据库

application.properties配置文件中添加数据库信息

### xxl-job, datasource
spring.datasource.url=jdbc:postgresql://localhost:5432/postgres
spring.datasource.username=postgres
spring.datasource.password=postgres
spring.datasource.driver-class-name=org.postgresql.Driver

Mapper调整

xxl-job-admin模块resources下mybatis-mapper里面mapper文件做如下调整:

  • 去掉转义符 `

  • XxlJobGroupMapper.xml关键字order添加双引号
    在这里插入图片描述

  • 分页查询修改为显示写法LIMIT #{pagesize} OFFSET #{offset},如果没有offset则写0。
    在这里插入图片描述

  • XxlJobLogMapper.xmlfindFailJobLogIds方法逻辑调整

	<select id="findFailJobLogIds" resultType="long" >
		SELECT id FROM xxl_job_log
		<!--WHERE !(-->
			<!--(trigger_code in (0, 200) and handle_code = 0)-->
			<!--OR-->
			<!--(handle_code = 200)-->
		<!--)-->
		WHERE
		(trigger_code !=0 AND handle_code !=0 )
		OR
		(trigger_code !=200 AND handle_code !=0 )
		OR
		(handle_code != 200)
		AND alarm_status = 0
		ORDER BY id ASC
		LIMIT #{pagesize} OFFSET #{offset}
	</select>
  • XxlJobRegistryMapper.xml文件中findAll以及findDead查询语句DATE_ADD(#{nowTime},INTERVAL -#{timeout} SECOND)修改为((select NOW())-INTERVAL ‘${timeout} S’ )
	<select id="findDead" parameterType="java.util.HashMap" resultType="java.lang.Integer" >
		SELECT t.id
		FROM xxl_job_registry AS t
		WHERE t.update_time <![CDATA[ < ]]> ((select NOW())-INTERVAL '${timeout} S')
	</select>

	<select id="findAll" parameterType="java.util.HashMap" resultMap="XxlJobRegistry">
		SELECT <include refid="Base_Column_List" />
		FROM xxl_job_registry AS t
		WHERE t.update_time <![CDATA[ > ]]> ((select NOW())-INTERVAL '${timeout} S')
	</select>

执行sql脚本

CREATE table if not exists xxl_job_info (
  id serial NOT NULL,
  job_group integer NOT NULL,
  job_cron varchar(128) NOT NULL ,
  job_desc varchar(255) NOT NULL,
  add_time timestamp  DEFAULT NULL,
  update_time timestamp  DEFAULT NULL,
  author varchar(64) DEFAULT NULL,
  alarm_email varchar(255) DEFAULT NULL ,
  executor_route_strategy varchar(50) DEFAULT NULL  ,
  executor_handler varchar(255) DEFAULT NULL,
  executor_param varchar(512) DEFAULT NULL,
  executor_block_strategy varchar(50) DEFAULT NULL ,
  executor_timeout integer  NOT NULL DEFAULT '0',
  executor_fail_retry_count integer NOT NULL DEFAULT '0',
  glue_type varchar(50) NOT NULL,
  glue_source text,
  glue_remark varchar(128) DEFAULT NULL,
  glue_updatetime timestamp  DEFAULT NULL,
  child_jobid varchar(255) DEFAULT NULL,
  trigger_status int NOT NULL DEFAULT '0',
  trigger_last_time bigint NOT NULL DEFAULT '0',
  trigger_next_time bigint NOT NULL DEFAULT '0',
  PRIMARY KEY (id)
);
comment on table xxl_job_info is '任务信息表';
comment on column xxl_job_info.id is '主键id';
comment on column xxl_job_info.job_group  is '执行器主键ID';
comment on column xxl_job_info.job_cron is '任务执行CRON';
comment on column xxl_job_info.job_desc  is '任务描述';
comment on column xxl_job_info.add_time  is '任务创建时间';
comment on column xxl_job_info.update_time  is '任务更新时间';
comment on column xxl_job_info.author is '作者';
comment on column xxl_job_info.alarm_email is '报警邮件';
comment on column xxl_job_info.executor_route_strategy is '执行器路由策略';
comment on column xxl_job_info.executor_handler is '执行器任务handler';
comment on column xxl_job_info.executor_param is '执行器任务参数';
comment on column xxl_job_info.executor_block_strategy is '阻塞处理策略';
comment on column xxl_job_info.executor_timeout is '任务执行超时时间,单位秒';
comment on column xxl_job_info.executor_fail_retry_count is '失败重试次数';
comment on column xxl_job_info.glue_type is 'GLUE类型';
comment on column xxl_job_info.glue_source is 'GLUE源代码';
comment on column xxl_job_info.glue_remark is 'GLUE备注';
comment on column xxl_job_info.glue_updatetime is 'GLUE更新时间';
comment on column xxl_job_info.child_jobid is '子任务ID,多个逗号分隔';
comment on column xxl_job_info.trigger_status is '调度状态:0-停止,1-运行';
comment on column xxl_job_info.trigger_last_time is '上次调度时间';
comment on column xxl_job_info.trigger_next_time  is '下次调度时间';




CREATE TABLE  if not exists xxl_job_log (
  id serial NOT NULL,
  job_group int NOT NULL,
  job_id int NOT NULL,
  executor_address varchar(255) DEFAULT NULL,
  executor_handler varchar(255) DEFAULT NULL,
  executor_param varchar(512) DEFAULT NULL,
  executor_sharding_param varchar(20) DEFAULT NULL,
  executor_fail_retry_count int NOT NULL DEFAULT '0',
  trigger_time timestamp  DEFAULT NULL,
  trigger_code int NOT NULL,
  trigger_msg text,
  handle_time timestamp  DEFAULT NULL,
  handle_code int NOT NULL,
  handle_msg text,
  alarm_status int NOT NULL DEFAULT '0',
  PRIMARY KEY (id)
);
do
$$
	begin 
		if to_regclass('i_trigger_time') is null
		then
			CREATE INDEX i_trigger_time ON xxl_job_log using BTREE(trigger_time);
		else
			raise notice 'index i_trigger_time already exists in xxl_job_log.';
		end if;
	end
$$;

do
$$
	begin 
		if to_regclass('i_handle_code') is null
		then
			CREATE INDEX i_handle_code ON xxl_job_log using BTREE(handle_code);
		else
			raise notice 'index i_handle_code already exists in xxl_job_log.';
		end if;
	end
$$;


comment on table xxl_job_log is '任务日志表';
comment on column xxl_job_log.id  is '主键';
comment on column xxl_job_log.job_group  is '执行器主键ID';
comment on column xxl_job_log.job_id  is '任务,主键ID';
comment on column xxl_job_log.executor_address  is '执行器地址,本次执行的地址';
comment on column xxl_job_log.executor_handler  is '执行器任务handler';
comment on column xxl_job_log.executor_param  is '执行器任务参数';
comment on column xxl_job_log.executor_sharding_param  is '执行器任务分片参数,格式如 1/2';
comment on column xxl_job_log.executor_fail_retry_count  is '失败重试次数';
comment on column xxl_job_log.trigger_time  is '调度-时间';
comment on column xxl_job_log.trigger_code  is '调度-结果';
comment on column xxl_job_log.trigger_msg  is '调度-日志';
comment on column xxl_job_log.handle_time  is '执行-时间';
comment on column xxl_job_log.handle_code  is '执行-状态';
comment on column xxl_job_log.handle_msg  is '执行-日志';
comment on column xxl_job_log.alarm_status  is '告警状态:0-默认、1-无需告警、2-告警成功、3-告警失败';



CREATE TABLE  if not exists xxl_job_log_report (
  id serial NOT NULL,
  trigger_day timestamp DEFAULT NULL ,
  running_count int NOT NULL DEFAULT '0' ,
  suc_count int NOT NULL DEFAULT '0' ,
  fail_count int NOT NULL DEFAULT '0',
  PRIMARY KEY (id)
);
do
$$
	begin 
		if to_regclass('i_trigger_day') is null
		then
			create unique index i_trigger_day on xxl_job_log_report using BTREE(trigger_day);
		else
			raise notice 'index i_trigger_day already exists in xxl_job_log_report.';
		end if;
	end
$$;
comment on table xxl_job_log_report is '日志统计表';
comment on column xxl_job_log_report.id is '主键';
comment on column xxl_job_log_report.trigger_day is '调度-时间';
comment on column xxl_job_log_report.running_count is '运行中-日志数量';
comment on column xxl_job_log_report.suc_count is '执行成功-日志数量';
comment on column xxl_job_log_report.fail_count is '执行失败-日志数量';


CREATE TABLE  if not exists xxl_job_logglue (
  id serial NOT NULL,
  job_id int NOT NULL,
  glue_type varchar(50) DEFAULT NULL,
  glue_source text,
  glue_remark varchar(128) NOT NULL,
  add_time timestamp DEFAULT NULL,
  update_time timestamp DEFAULT NULL,
  PRIMARY KEY (id)
);
comment on table xxl_job_logglue is '任务GLUE日志表';
comment on column xxl_job_logglue.id  is '主键';
comment on column xxl_job_logglue.job_id  is '任务,主键ID';
comment on column xxl_job_logglue.glue_type  is 'GLUE类型';
comment on column xxl_job_logglue.glue_source  is 'GLUE源代码';
comment on column xxl_job_logglue.glue_remark  is 'GLUE备注';
comment on column xxl_job_logglue.add_time  is '创建时间';
comment on column xxl_job_logglue.update_time  is '修改时间';




CREATE TABLE  if not exists xxl_job_registry (
  id serial NOT NULL,
  registry_group varchar(50) NOT NULL,
  registry_key varchar(255) NOT NULL,
  registry_value varchar(255) NOT NULL,
  update_time timestamp DEFAULT NULL,
  PRIMARY KEY (id)
);
do
$$
	begin 
		if to_regclass('i_g_k_v') is null
		then
			create index i_g_k_v on xxl_job_registry using BTREE(registry_group,registry_key,registry_value);
		else
			raise notice 'index i_g_k_v already exists in xxl_job_registry.';
		end if;
	end
$$;
comment on table xxl_job_registry is '任务注册表';
comment on column xxl_job_registry.id  is '主键';
comment on column xxl_job_registry.registry_group  is '注册分组';
comment on column xxl_job_registry.registry_key  is '注册键';
comment on column xxl_job_registry.registry_value  is '注册值';
comment on column xxl_job_registry.update_time  is '更新时间';



CREATE TABLE  if not exists xxl_job_group (
  id serial NOT NULL,
  app_name varchar(64) NOT NULL,
  title varchar(12) NOT NULL,
  "order" int NOT NULL DEFAULT '0',
  address_type int NOT NULL DEFAULT '0' ,
  address_list varchar(512) DEFAULT NULL,
  PRIMARY KEY (id)
);
comment on table xxl_job_group is '任务分组表';
comment on column xxl_job_group.id  is '主键';
comment on column xxl_job_group.app_name  is '执行器AppName';
comment on column xxl_job_group.title  is '执行器名称';
comment on column xxl_job_group.order  is '排序';
comment on column xxl_job_group.address_type  is '执行器地址类型:0=自动注册、1=手动录入';
comment on column xxl_job_group.address_list  is '执行器地址列表,多地址逗号分隔';



CREATE TABLE  if not exists xxl_job_user (
  id serial NOT NULL,
  username varchar(50) NOT NULL,
  password varchar(50) NOT NULL,
  role int NOT NULL ,
  permission varchar(255) DEFAULT NULL,
  PRIMARY KEY (id)
);
do
$$
	begin 
		if to_regclass('i_username') is null
		then
			create unique index i_username on xxl_job_user USING BTREE(username);
		else
			raise notice 'index i_username already exists in xxl_job_user.';
		end if;
	end
$$;
comment on table xxl_job_user is '任务用户表';
comment on column xxl_job_user.id  is '主键';
comment on column xxl_job_user.username  is '账号';
comment on column xxl_job_user.password  is '密码';
comment on column xxl_job_user.role  is '角色:0-普通用户、1-管理员';
comment on column xxl_job_user.permission  is '权限:执行器ID列表,多个逗号分割';


CREATE TABLE  if not exists xxl_job_lock (
  lock_name varchar(50) NOT NULL,
  PRIMARY KEY (lock_name)
);
comment on table xxl_job_lock is '任务锁表';
comment on column xxl_job_lock.lock_name  is '锁名称';

INSERT INTO xxl_job_group(id, app_name, title, "order", address_type, address_list) VALUES (1, 'xxl-job-executor-sample', '示例执行器', 1, 0, NULL) on conflict(id) do nothing;
INSERT INTO xxl_job_info(id, job_group, job_cron, job_desc, add_time, update_time, author, alarm_email, executor_route_strategy, executor_handler, executor_param, executor_block_strategy, executor_timeout, executor_fail_retry_count, glue_type, glue_source, glue_remark, glue_updatetime, child_jobid) VALUES (1, 1, '0 0 0 * * ? *', '测试任务1', '2018-11-03 22:21:31', '2018-11-03 22:21:31', 'XXL', '', 'FIRST', 'demoJobHandler', '', 'SERIAL_EXECUTION', 0, 0, 'BEAN', '', 'GLUE代码初始化', '2018-11-03 22:21:31', '')  on conflict(id) do nothing;
INSERT INTO xxl_job_user(id, username, password, role, permission) VALUES (1, 'admin', 'e10adc3949ba59abbe56e057f20f883e', 1, NULL)  on conflict(id) do nothing;
INSERT INTO xxl_job_lock (lock_name) VALUES ( 'schedule_lock')  on conflict(lock_name) do nothing;

控制台登录

登录后台,地址:http://localhost:8080/xxl-job-admin/toLogin
账户:admin
密码:123456

如果修改了地址,进行替换即可。
在这里插入图片描述
在这里插入图片描述

样例测试

运行测试样例模块,样例有多个版本,可以选择自己使用的版本进行测试,以SpringBoot项目为例:
在这里插入图片描述
需要注意的是,我们要和admin的地址保持一致。
在这里插入图片描述

执行器管理

执行器管理有一个默认的记录
在这里插入图片描述
他来自我们的springboot测试样例,相关的配置信息。
在这里插入图片描述
如果没有,我们可以手动创建,创建时可以手动注册,也可以自动注册:
在这里插入图片描述
完成后OnLine会出现对应的地址。

任务管理

前面我们创建了执行器,下面我们来到任务管理中。任务管理中默认存在一个任务,它来自自带的springboot样例测试中,
我们发现仅仅只是输出了一些日志。
在这里插入图片描述
在这里插入图片描述

    @XxlJob("demoJobHandler")
    public ReturnT<String> demoJobHandler(String param) throws Exception {
        XxlJobLogger.log("XXL-JOB, Hello World.");

        for (int i = 0; i < 5; i++) {
            XxlJobLogger.log("beat at:" + i);
            TimeUnit.SECONDS.sleep(2);
        }
        return ReturnT.SUCCESS;
    }

调度日志

我们执行刚刚的调度任务后,会在调度日志中有相应的记录。
在这里插入图片描述
查看执行日志:
在这里插入图片描述

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值