本篇文章作为某市网站数据迁移项目的开发总结,大概场景是某市网站要统一所有区以及委办局的网站进行集约化管理,所以需要将各个区以及委办局的网站数据迁移到市网站数据库中,并不是单纯的迁移各个区以及委办局的网站数据库的表,而且是根据网站栏目结构信息进行分类,以及网站栏目下的文章进行迁移并分类到市网站数据库中的标准视图中,如果用ETL工具,没法太细粒度的去筛选和转换数据,因此需要独立开发一套数据迁移平台,满足当前需求。
数据迁移平台核心功能分三块:
1.调用任务;
2.在线数据源配置;
3.数据源数据处理。
本篇重点讲述项目中在线数据源配置的实现,并不是在配置文件中实现的多数据源配置。
数据源数据库主要为oracle、mysql、sqlserver三种,所以在线数据源配置功能也得支持三种数据库。
考虑到版本问题,目前使用mysql超过5.5以上的版本较多,使用驱动包POM如下:
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.13</version>
</dependency>
oracle使用驱动包POM如下:
<dependency>
<groupId>oracle</groupId>
<artifactId>ojdbc7</artifactId>
<version>12.1.0.1.0</version>
<scope>system</scope>
<systemPath>${pom.basedir}/src/main/resources/lib/ojdbc7.jar</systemPath>
</dependency>
sqlserver使用驱动包POM如下:
<dependency>
<groupId>com.microsoft.sqlserver</groupId>
<artifactId>sqljdbc4</artifactId>
<version>4.2</version>
<scope>system</scope>
<systemPath>${pom.basedir}/src/main/resources/lib/sqljdbc42.jar</systemPath>
</dependency>
注意:由于oracle没法在POM中直接下载ojdbc7.jar 所以使用了systemPath,sqlserver驱动包也用了systemPath
在线数据源配置-表单页面截图:
1.MYSQL
2.ORACLE
3.SQLSERVER
数据源连接池使用的是druid。
数据源的连接测试功能代码如下:
1.数据源实体类
package com.fastservice.model.module;
import javax.persistence.*;
import java.io.Serializable;
import java.util.Date;
@Table(name = "database_config")
public class DatabaseConfig implements Serializable {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer id;
/**
* 连接名称
*/
@Column(name = "conn_name")
private String connName;
/**
* 主机名
*/
@Column(name = "host_name")
private String hostName;
/**
* 数据库名
*/
@Column(name = "db_name")
private String dbName;
/**
* 端口号
*/
private String port;
/**
* 用户名
*/
private String username;
/**
* 密码
*/
private String password;
/**
* 数据库类型(1.mysql;2.oracle;3.sqlserver;4.dm)
*/
@Column(name = "db_type")
private Integer dbType;
/**
* 连接url
*/
@Column(name = "db_url")
private String dbUrl;
@Column(name = "db_driver")
private String dbDriver;
/**
* 数据表空间
*/
@Column(name = "db_space")
private String dbSpace;
/**
* 索引表空间
*/
@Column(name = "db_space_index")
private String dbSpaceIndex;
/**
* 所属机构
*/
@Column(name = "org_by")
private Integer orgBy;
/**
* 创建人
*/
@Column(name = "create_by")
private Integer createBy;
/**
* 创建时间
*/
@Column(name = "create_time")
private Date createTime;
/**
* 修改时间
*/
@Column(name = "update_time")
private Date updateTime;
/**
* 任务执行编码
*/
@Column(name = "execute_code")
private String executeCode;
/**
* 删除标识(1.正常;0.删除)
*/
private Integer del;
/**
* @return id
*/
public Integer getId() {
return id;
}
/**
* @param id
*/
public void setId(Integer id) {
this.id = id;
}
/**
* 获取连接名称
*
* @return conn_name - 连接名称
*/
public String getConnName() {
return connName;
}
/**
* 设置连接名称
*
* @param connName 连接名称
*/
public void setConnName(String connName) {
this.connName = connName;
}
/**
* 获取主机名
*
* @return host_name - 主机名
*/
public String getHostName() {
return hostName;
}
/**
* 设置主机名
*
* @param hostName 主机名
*/
public void setHostName(String hostName) {
this.hostName = hostName;
}
/**
* 获取数据库名
*
* @return db_name - 数据库名
*/
public String getDbName() {
return dbName;
}
/**
* 设置数据库名
*
* @param dbName 数据库名
*/
public void setDbName(String dbName) {
this.dbName = dbName;
}
/**
* 获取端口号
*
* @return port - 端口号
*/
public String getPort() {
return port;
}
/**
* 设置端口号
*
* @param port 端口号
*/
public void setPort(String port) {
this.port = port;
}
/**
* 获取用户名
*
* @return username - 用户名
*/
public String getUsername() {
return username;
}
/**
* 设置用户名
*
* @param username 用户名
*/
public void setUsername(String username) {
this.username = username;
}
/**
* 获取密码
*
* @return password - 密码
*/
public String getPassword() {
return password;
}
/**
* 设置密码
*
* @param password 密码
*/
public void setPassword(String password) {
this.password = password;
}
/**
* 获取数据库类型(1.mysql;2.oracle;3.sqlserver;4.dm)
*
* @return db_type - 数据库类型(1.mysql;2.oracle;3.sqlserver;4.dm)
*/
public Integer getDbType() {
return dbType;
}
/**
* 设置数据库类型(1.mysql;2.oracle;3.sqlserver;4.dm)
*
* @param dbType 数据库类型(1.mysql;2.oracle;3.sqlserver;4.dm)
*/
public void setDbType(Integer dbType) {
this.dbType = dbType;
}
/**
* 获取连接url
*
* @return db_url - 连接url
*/
public String getDbUrl() {
return dbUrl;
}
/**
* 设置连接url
*
* @param dbUrl 连接url
*/
public void setDbUrl(String dbUrl) {
this.dbUrl = dbUrl;
}
/**
* @return db_driver
*/
public String getDbDriver() {
return dbDriver;
}
/**
* @param dbDriver
*/
public void setDbDriver(String dbDriver) {
this.dbDriver = dbDriver;
}
/**
* 获取数据表空间
*
* @return db_space - 数据表空间
*/
public String getDbSpace() {
return dbSpace;
}
/**
* 设置数据表空间
*
* @param dbSpace 数据表空间
*/
public void setDbSpace(String dbSpace) {
this.dbSpace = dbSpace;
}
/**
* 获取索引表空间
*
* @return db_space_index - 索引表空间
*/
public String getDbSpaceIndex() {
return dbSpaceIndex;
}
/**
* 设置索引表空间
*
* @param dbSpaceIndex 索引表空间
*/
public void setDbSpaceIndex(String dbSpaceIndex) {
this.dbSpaceIndex = dbSpaceIndex;
}
/**
* 获取所属机构
*
* @return org_by - 所属机构
*/
public Integer getOrgBy() {
return orgBy;
}
/**
* 设置所属机构
*
* @param orgBy 所属机构
*/
public void setOrgBy(Integer orgBy) {
this.orgBy = orgBy;
}
/**
* 获取创建人
*
* @return create_by - 创建人
*/
public Integer getCreateBy() {
return createBy;
}
/**
* 设置创建人
*
* @param createBy 创建人
*/
public void setCreateBy(Integer createBy) {
this.createBy = createBy;
}
/**
* 获取创建时间
*
* @return create_time - 创建时间
*/
public Date getCreateTime() {
return createTime;
}
/**
* 设置创建时间
*
* @param createTime 创建时间
*/
public void setCreateTime(Date createTime) {
this.createTime = createTime;
}
/**
* 获取修改时间
*
* @return update_time - 修改时间
*/
public Date getUpdateTime() {
return updateTime;
}
/**
* 设置修改时间
*
* @param updateTime 修改时间
*/
public void setUpdateTime(Date updateTime) {
this.updateTime = updateTime;
}
/**
* 获取任务执行编码
*
* @return execute_code - 任务执行编码
*/
public String getExecuteCode() {
return executeCode;
}
/**
* 设置任务执行编码
*
* @param executeCode 任务执行编码
*/
public void setExecuteCode(String executeCode) {
this.executeCode = executeCode;
}
/**
* 获取删除标识(1.正常;0.删除)
*
* @return del - 删除标识(1.正常;0.删除)
*/
public Integer getDel() {
return del;
}
/**
* 设置删除标识(1.正常;0.删除)
*
* @param del 删除标识(1.正常;0.删除)
*/
public void setDel(Integer del) {
this.del = del;
}
}
2.数据源工具类
package com.fastservice.database.utils;
import com.alibaba.druid.pool.DruidDataSource;
import com.fastservice.database.DatabaseMeta;
import com.fastservice.model.module.DatabaseConfig;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import javax.sql.DataSource;
import java.sql.*;
import java.util.*;
/**
* 数据源连接工具
*/
public class DatabaseUtil{
public static Logger logger = LoggerFactory.getLogger(DatabaseUtil.class);
public static final String DATABASE_TASK_PARAM = "executeCode";
public static final String MYSQL_URL = "jdbc:mysql://TEMP_URL:TEMP_PORT/TEMP_DB_NAME?useUnicode=true&characterEncoding=utf-8";
public static final String ORACLE_URL = "jdbc:oracle:thin:@TEMP_URL:TEMP_PORT:TEMP_DB_NAME";
public static final String SQLSERVER_URL = "jdbc:sqlserver://TEMP_URL:TEMP_PORT;DatabaseName=TEMP_DB_NAME";
/**
* 获得数据源
* @param config
* @return
*/
public static DataSource getDataSource(DatabaseConfig config){
DruidDataSource datasource = new DruidDataSource();
if(DatabaseMeta.TYPE_DATABASE_MYSQL == config.getDbType().intValue()){
datasource = getNewMySqlDataSource(config,datasource);
}else if(DatabaseMeta.TYPE_DATABASE_ORACLE == config.getDbType().intValue()){
datasource = getNewOracleDataSource(config,datasource);
}else if(DatabaseMeta.TYPE_DATABASE_SQL_SERVER == config.getDbType().intValue()){
datasource = getNewSqlServerDataSource(config,datasource);
}else if(DatabaseMeta.TYPE_DATABASE_DM == config.getDbType().intValue()){
}
return datasource;
}
/**
* mysql datasource 初始化
* @param config
* @param datasource
* @return
*/
public static DruidDataSource getNewMySqlDataSource(DatabaseConfig config,DruidDataSource datasource){
String url = MYSQL_URL.replace("TEMP_URL",config.getHostName())
.replace("TEMP_PORT",config.getPort())
.replace("TEMP_DB_NAME",config.getDbName());
datasource.setUrl(url);
datasource.setUsername(config.getUsername());
datasource.setPassword(config.getPassword());
datasource.setDriverClassName(DatabaseMeta.Driver.MYSQL.getValue());
datasource.setInitialSize(1);
datasource.setMinIdle(1);
datasource.setMaxActive(20);
datasource.setMaxWait(20000);
datasource.setTimeBetweenEvictionRunsMillis(60000);
datasource.setMinEvictableIdleTimeMillis(300000);
datasource.setValidationQuery("SELECT 1");
datasource.setTestWhileIdle(true);
datasource.setTestOnBorrow(true);
datasource.setTestOnReturn(false);
datasource.setPoolPreparedStatements(true);
datasource.setMaxPoolPreparedStatementPerConnectionSize(20);
try {
datasource.setFilters("stat,wall,log4j");
} catch (SQLException e) {
logger.error("druid configuration initialization filter", e);
}
datasource.setConnectionProperties("druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000");
return datasource;
}
/**
* Oracle datasource 初始化
* @param config
* @param datasource
* @return
*/
public static DruidDataSource getNewOracleDataSource(DatabaseConfig config,DruidDataSource datasource){
String url = ORACLE_URL.replace("TEMP_URL",config.getHostName())
.replace("TEMP_PORT",config.getPort())
.replace("TEMP_DB_NAME",config.getDbName());
datasource.setUrl(url);
datasource.setUsername(config.getUsername());
datasource.setPassword(config.getPassword());
datasource.setDriverClassName(DatabaseMeta.Driver.ORACLE.getValue());
datasource.setInitialSize(1);
datasource.setMinIdle(1);
datasource.setMaxActive(20);
datasource.setMaxWait(20000);
datasource.setTimeBetweenEvictionRunsMillis(60000);
datasource.setMinEvictableIdleTimeMillis(300000);
datasource.setValidationQuery("select 1 from dual");
datasource.setTestWhileIdle(true);
datasource.setTestOnBorrow(true);
datasource.setTestOnReturn(false);
datasource.setPoolPreparedStatements(true);
datasource.setMaxPoolPreparedStatementPerConnectionSize(20);
try {
datasource.setFilters("stat,wall,log4j");
} catch (SQLException e) {
logger.error("druid configuration initialization filter", e);
}
datasource.setConnectionProperties("druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000");
return datasource;
}
/**
* SqlServer datasource 初始化
* @param config
* @param datasource
* @return
*/
public static DruidDataSource getNewSqlServerDataSource(DatabaseConfig config,DruidDataSource datasource){
String url = SQLSERVER_URL.replace("TEMP_URL",config.getHostName())
.replace("TEMP_PORT",config.getPort())
.replace("TEMP_DB_NAME",config.getDbName());
datasource.setUrl(url);
datasource.setUsername(config.getUsername());
datasource.setPassword(config.getPassword());
datasource.setDriverClassName(DatabaseMeta.Driver.SQL_SERVER.getValue());
datasource.setInitialSize(1);
datasource.setMinIdle(1);
datasource.setMaxActive(20);
datasource.setMaxWait(20000);
datasource.setTimeBetweenEvictionRunsMillis(60000);
datasource.setMinEvictableIdleTimeMillis(300000);
datasource.setValidationQuery("SELECT 1");
datasource.setTestWhileIdle(true);
datasource.setTestOnBorrow(true);
datasource.setTestOnReturn(false);
datasource.setPoolPreparedStatements(true);
datasource.setMaxPoolPreparedStatementPerConnectionSize(20);
try {
datasource.setFilters("stat,wall,log4j");
} catch (SQLException e) {
logger.error("druid configuration initialization filter", e);
}
datasource.setConnectionProperties("druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000");
return datasource;
}
private static Connection getConn(DataSource dataSource) {
try {
return dataSource.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
/**
* update
*
* @param dataSource
* @param sql
* @param params
*/
public static int update(DataSource dataSource, String sql, Object params[]) {
Connection connection = getConn(dataSource);
PreparedStatement preparedStatement = null;
int ret = 0;
try {
logger.info("sql={}",sql);
preparedStatement = connection.prepareStatement(sql);
if (params != null) {
logger.info("params={}",Arrays.toString(params));
for (int i = 0; i < params.length; i++) {
preparedStatement.setObject(i + 1, params[i]);
}
}
ret = preparedStatement.executeUpdate();
} catch (SQLException e) {
logger.error(e.getLocalizedMessage(),e);
} finally {
release(connection, preparedStatement, null);
}
return ret;
}
/**
* query
*
* @param dataSource
* @param sql
* @param params
* @return
*/
public static List<Map<String, Object>> query(DataSource dataSource, String sql, Object... params) {
Connection connection = getConn(dataSource);
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
//logger.info("sql={}",sql);
preparedStatement = connection.prepareStatement(sql);
if (params != null) {
//logger.info("params={}",Arrays.toString(params));
for (int i = 0; i < params.length; i++) {
preparedStatement.setObject(i + 1, params[i]);
}
}
resultSet = preparedStatement.executeQuery();
List<Map<String, Object>> ret = resultSetToList(resultSet);
return ret;
} catch (SQLException e) {
logger.error(e.getLocalizedMessage(),e);
} finally {
release(connection, preparedStatement, resultSet);
}
return null;
}
private static List<Map<String, Object>> resultSetToList(ResultSet resultSet) throws SQLException {
if (resultSet == null) {
return new ArrayList<Map<String, Object>>();
}
ResultSetMetaData resultSetMetaData = resultSet.getMetaData(); // 得到结果集(rs)的结构信息,比如字段数、字段名等
int columnCount = resultSetMetaData.getColumnCount(); // 返回此 ResultSet 对象中的列数
List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
while (resultSet.next()) {
Map<String, Object> rowData = new HashMap<String, Object>(columnCount);
for (int i = 1; i <= columnCount; i++) {
rowData.put(resultSetMetaData.getColumnName(i), resultSet.getObject(i));
}
list.add(rowData);
}
return list;
}
/**
* release
* @param connection
* @param preparedStatement
* @param resultSet
*/
public static void release(Connection connection, PreparedStatement preparedStatement, ResultSet resultSet) {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (preparedStatement != null) {
try {
preparedStatement.close();
} catch (SQLException e) {
logger.error(e.getLocalizedMessage(),e);
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
3.DatabaseMeta
package com.fastservice.database;
import java.util.HashMap;
import java.util.Map;
public class DatabaseMeta {
/**
* mysql
*/
public static final int TYPE_DATABASE_MYSQL = 1;
/**
*oracle
*/
public static final int TYPE_DATABASE_ORACLE = 2;
/**
* sqlserver
*/
public static final int TYPE_DATABASE_SQL_SERVER = 3;
/**
* 达梦
*/
public static final int TYPE_DATABASE_DM = 4;
public static final Map<Integer,String> TYPE_DATABASES;
static{
TYPE_DATABASES = new HashMap<Integer,String>();
TYPE_DATABASES.put(TYPE_DATABASE_MYSQL,"MYSQL");
TYPE_DATABASES.put(TYPE_DATABASE_ORACLE,"ORACLE");
TYPE_DATABASES.put(TYPE_DATABASE_SQL_SERVER,"SQLSERVER");
TYPE_DATABASES.put(TYPE_DATABASE_DM,"达梦");
}
public static enum Driver{
MYSQL("com.mysql.cj.jdbc.Driver"),
ORACLE("oracle.jdbc.OracleDriver"),
SQL_SERVER("com.microsoft.sqlserver.jdbc.SQLServerDriver"),
DM("dm.jdbc.driver.DmDriver");
private String value;
private Driver(String value) {
this.value = value;
}
public String getValue() {
return value;
}
}
}
4.DatabaseFactory 数据源工厂类
package com.fastservice.database;
import com.fastservice.exception.BizException;
import com.fastservice.model.module.DatabaseConfig;
public interface DatabaseFactoryInterface {
/**
* 连接测试
* @param config
* @return
* @throws BizException
*/
public Boolean getConnectionTestReport(DatabaseConfig config) throws BizException;
}
package com.fastservice.database;
import com.fastservice.core.BaseLogger;
import com.fastservice.database.utils.DatabaseUtil;
import com.fastservice.exception.BizException;
import com.fastservice.model.module.DatabaseConfig;
import com.fastservice.utils.Validate;
import org.springframework.stereotype.Component;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;
@Component
public class DatabaseFactory extends BaseLogger implements DatabaseFactoryInterface {
private Map<String,DataSource> dsMap = new ConcurrentHashMap<String,DataSource>();
public void addDataSource(String key,DataSource dataSource){
dsMap.put(key,dataSource);
}
public DataSource getDataSource(String key){
if(dsMap.containsKey(key)){
return dsMap.get(key);
}
return null;
}
public void removeDataSource(String key){
if(dsMap.containsKey(key)){
dsMap.remove(key);
}
}
//public void resetDataSource()
@Override
public Boolean getConnectionTestReport(DatabaseConfig config) throws BizException {
//测试的数据源不保存缓存中
DataSource dataSource = DatabaseUtil.getDataSource(config);
Connection connection = null;
boolean flag = false;
try {
connection = dataSource.getConnection();
if(!Validate.isNull(connection)){
flag = true;
}
} catch (SQLException e) {
logger.error(e.getLocalizedMessage(),e);
throw new BizException("连接失败");
}finally {
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
logger.error("Error disconnecting from database:"+e.getMessage());
throw new BizException("连接失败");
}
}
}
return flag;
}
public DataSource getDataSource(DatabaseConfig config){
DataSource dataSource = this.getDataSource(String.valueOf(config.getId()));
if(Validate.isNull(dataSource)){
dataSource = DatabaseUtil.getDataSource(config);
this.addDataSource(String.valueOf(config.getId()),dataSource);
}
return dataSource;
}
}
以上为在线数据源配置主要代码。
具体如何使用DatabaseFactory,如下所示:
package com.fastadmin.task;
import com.fastservice.core.BaseLogger;
import com.fastservice.database.DatabaseFactory;
import com.fastservice.database.utils.DatabaseUtil;
import com.fastservice.exception.BizException;
import com.fastservice.model.module.DatabaseConfig;
import com.fastservice.quartz.JobHandlerInterface;
import com.fastservice.service.module.DatabaseConfigService;
import com.fastservice.utils.Validate;
import net.sf.ehcache.Cache;
import net.sf.ehcache.CacheManager;
import net.sf.ehcache.Element;
import org.apache.shiro.cache.ehcache.EhCacheManager;
import javax.annotation.Resource;
import javax.sql.DataSource;
import java.util.List;
import java.util.Map;
/**
* 数据迁移公共任务类
*/
public abstract class CommonDataMigrationJobHandler extends BaseLogger implements JobHandlerInterface {
@Resource
private DatabaseFactory databaseFactory;
@Resource
private DatabaseConfigService databaseConfigService;
private DataSource dataSource;
/**
* 业务处理
* @return
* @throws Exception
*/
public abstract Boolean Handle(Integer orgBy) throws Exception;
@Override
public Boolean execute(String params) throws Exception {
if(Validate.isEmpty(params)){
return false;
}
if(params.indexOf(DatabaseUtil.DATABASE_TASK_PARAM) == -1){
return false;
}
String executeCode = "";
String[] args = params.split("=");
if(!Validate.isEmpty(args) && args.length > 1){
executeCode = args[1];
}
return this.executeHandle(executeCode);
}
public Boolean executeHandle(String executeCode)throws Exception{
if(Validate.isEmpty(executeCode)){
return false;
}
//根据executeCode读取在线数据源的配置信息
DatabaseConfig dbConfig = databaseConfigService.findDbConfigByExecuteCode(executeCode);
if(Validate.isNull(dbConfig)){
throw new BizException("executeCode["+executeCode+"]的数据源配置不存在");
}
//获取DataSource
this.dataSource = databaseFactory.getDataSource(dbConfig);
return this.Handle(dbConfig.getOrgBy());
}
/**
* 查询
* @param sql
* @param params
* @return
*/
public List<Map<String, Object>> query(String sql, Object... params){
//jdbcTemplate.q
return DatabaseUtil.query(this.dataSource,sql,params);
}
/**
* 增,删,改
* @param sql
* @param params
* @return
*/
public int update(String sql, Object params[]){
return DatabaseUtil.update(this.dataSource,sql,params);
}
}