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

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

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

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

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

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; 
	} 
}