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