基于spring jdbc 动态创建表+定时任务管理

柔光的暖阳◎ 2024-04-18 17:18 22阅读 0赞

今天业务需求：需要创建业务功能模块依赖的公共模块的定时任务管理工具包。

功能要求：动态创建定时任务数据表和定时任务新增、停止、移除和初始化功能。

今天首先实现：动态建表功能：

第一：数据库连接配置对象：

import org.springframework.beans.factory.annotation.Value;
    import org.springframework.stereotype.Component;
    
    @Component
    public class CronTaskConfigEntity {
    	// cron 数据库url 地址
    	@Value("${com.digipower.ucas.cron.database.url}")
    	private String url;
    	// cron 数据库用户名称
    	@Value("${com.digipower.ucas.cron.database.username}")
    	private String username;
    	// cron 数据库密码
    	@Value("${com.digipower.ucas.cron.database.password}")
    	private String password;
    	// cron 数据库驱动
    	@Value("${com.digipower.ucas.cron.database.driverClassName}")
    	private String driverClassName;
    	// set 和   get 方法
    	public String getUrl() {
    		return url;
    	}
    	public void setUrl(String url) {
    		this.url = url;
    	}
    	public String getUsername() {
    		return username;
    	}
    	public void setUsername(String username) {
    		this.username = username;
    	}
    	public String getPassword() {
    		return password;
    	}
    	public void setPassword(String password) {
    		this.password = password;
    	}
    	public String getDriverClassName() {
    		return driverClassName;
    	}
    	public void setDriverClassName(String driverClassName) {
    		this.driverClassName = driverClassName;
    	}
    	
    	
    }

第二步：定时任务实体对象

import java.io.Serializable;
    import java.util.Date;
    
    @SuppressWarnings("serial")
    public class CronTask implements Serializable{
    	
    	private Long id;
    	
    	private String task_name;
    	
    	private String cron;
    	
    	private String class_name;
    	
    	private String method_name;
    	
    	private Integer type_;
    	
    	private Integer state_;
    	
    	private String remark_;
    	
    	private String created_by;
    	
    	private Date created_dt;
    	
    	// set 和   get 方法
    
    	public Long getId() {
    		return id;
    	}
    
    	public void setId(Long id) {
    		this.id = id;
    	}
    
    	public String getTask_name() {
    		return task_name;
    	}
    
    	public void setTask_name(String task_name) {
    		this.task_name = task_name;
    	}
    
    	public String getCron() {
    		return cron;
    	}
    
    	public void setCron(String cron) {
    		this.cron = cron;
    	}
    
    	public String getClass_name() {
    		return class_name;
    	}
    
    	public void setClass_name(String class_name) {
    		this.class_name = class_name;
    	}
    
    	public String getMethod_name() {
    		return method_name;
    	}
    
    	public void setMethod_name(String method_name) {
    		this.method_name = method_name;
    	}
    
    	public Integer getType_() {
    		return type_;
    	}
    
    	public void setType_(Integer type_) {
    		this.type_ = type_;
    	}
    
    	public Integer getState_() {
    		return state_;
    	}
    
    	public void setState_(Integer state_) {
    		this.state_ = state_;
    	}
    
    	public String getRemark_() {
    		return remark_;
    	}
    
    	public void setRemark_(String remark_) {
    		this.remark_ = remark_;
    	}
    
    	public String getCreated_by() {
    		return created_by;
    	}
    
    	public void setCreated_by(String created_by) {
    		this.created_by = created_by;
    	}
    
    	public Date getCreated_dt() {
    		return created_dt;
    	}
    
    	public void setCreated_dt(Date created_dt) {
    		this.created_dt = created_dt;
    	}
    	
    	
    	
    	
    
    }

第三步：JdbcTemplate 查询结果与实体对象转换封住：

import java.sql.ResultSet;
    import java.sql.SQLException;
    import org.springframework.jdbc.core.RowMapper;
    import com.digipower.ucas.cron.domain.CronTask;
    
    public class CronTaskMapper implements RowMapper<CronTask> {
    
    	@Override
    	public CronTask mapRow(ResultSet rs, int rowNum) throws SQLException {
    		// TODO Auto-generated method stub
    		CronTask object = new CronTask();
    		object.setId(rs.getLong("id"));
    		object.setClass_name(rs.getString("class_name"));
    		object.setCron(rs.getString("cron"));
    		object.setMethod_name(rs.getString("method_name"));
    		object.setRemark_(rs.getString("remark_"));
    		object.setState_(rs.getInt("state_"));
    		object.setTask_name(rs.getString("task_name"));
    		object.setType_(rs.getInt("type_"));
    		return object;
    	}
    
    }

第四步：动态建表工具类

package com.digipower.ucas.cron.util;
    
    import java.sql.Connection;
    import java.sql.DatabaseMetaData;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.util.List;
    import org.springframework.jdbc.core.JdbcTemplate;
    import com.digipower.ucas.cron.domain.CronTask;
    import com.digipower.ucas.cron.mapper.CronTaskMapper;
    
    /**
     * cron 工具类封装
     * 
     * @author zzg
     *
     */
    public class CronTaskUtil {
    	private JdbcTemplate template;
    
    	// set 和 get 方法
    	public JdbcTemplate getTemplate() {
    		return template;
    	}
    
    	public void setTemplate(JdbcTemplate template) {
    		this.template = template;
    	}
    
    	// 表sql是否存在
    	String tableName = "sys_cron";
    	// 建表sql
    	String create = "create table sys_cron (" + " id bigint(15) not null comment '主键',"
    			+ " task_name varchar(128) comment '任务名称'," + " cron varchar(64) comment '时间规则',"
    			+ " class_name varchar(64) comment '类名'," + " method_name varchar(64) comment '方法名称',"
    			+ " type_ tinyint(4) comment '任务类型:1:系统初始化 2:自定义'," + " state_ tinyint(4) comment '状态: 1:启用,2:禁用',"
    			+ " remark_ varchar(64) comment '备注'," + " created_by varchar(64) comment '创建人',"
    			+ " created_dt datetime comment '创建时间'," + " primary key (id)" + " )";
    	// 新增sql
    	String insert = "insert into sys_cron(id, task_name, cron, class_name, method_name, type_, state_, remark_) values (?,?,?,?,?,?,?,?)";
    
    	// 删除sql
    	String delete = "delete from sys_cron where id = ?";
    
    	// 更新sql
    	String update = "update sys_cron set state_ = ? where id = ?";
    
    	// 查询sql
    	String select = "select id, task_name, cron, class_name, method_name, type_, state_, remark_ from sys_cron";
    
    	/**
    	 * sys_cron 初始化方法
    	 * 
    	 * @return
    	 * @throws SQLException
    	 */
    	public boolean init() throws SQLException {
    
    		Connection conn = template.getDataSource().getConnection();
    		ResultSet tabs = null;
    		try {
    			DatabaseMetaData dbMetaData = conn.getMetaData();
    			String[] types = { "TABLE" };
    			tabs = dbMetaData.getTables(null, null, tableName, types);
    			if (tabs.next()) {
    				return true;
    			}
    		} catch (Exception e) {
    			e.printStackTrace();
    		} finally {
    			tabs.close();
    			conn.close();
    		}
    		return false;
    	}
    
    	/**
    	 * sys_cron 插入
    	 * 
    	 * @param entity
    	 * @throws SQLException
    	 */
    	public void insert(CronTask entity) throws SQLException {
    		if (!init()) {
    			template.execute(create);
    		}
    		template.update(insert, entity.getId(), entity.getTask_name(), entity.getCron(), entity.getClass_name(),
    				entity.getMethod_name(), entity.getType_(), entity.getState_(), entity.getRemark_());
    	}
    
    	/**
    	 * sys_cron 删除
    	 * 
    	 * @param entity
    	 */
    	public void delete(String id) {
    		template.update(delete, id);
    	}
    
    	/**
    	 * sys_cron 更新
    	 */
    
    	public void update(Integer state, Long id) {
    		template.update(update, state, id);
    	}
    
    	/**
    	 * sys_cron 查询
    	 * 
    	 * @throws SQLException
    	 */
    	public List<CronTask> select() throws SQLException {
    		if (!init()) {
    			template.execute(create);
    		}
    
    		List<CronTask> list = template.query(select, new CronTaskMapper());
    		return list;
    	}
    }