背景:使用dble作为数据库中间件在业务中使用,审计数据量太大,通过dble查询导出全量数据,会导致dble内存飙升,但是,因为分库太多,所以需要开发一款可以动态切换数据源的工具将数据库导出。博文为阉割后的demo,仅供参考,源码已经上传,可以前往下载源码。
1、新建maven项目,pom引入如下
<!-- spring -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context-support</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-test</artifactId>
<version>${spring.version}</version>
</dependency>
<!-- mybatis -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>${mybatis.version}</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-spring</artifactId>
<version>${mybatis-spring.version}</version>
</dependency>
<!-- mysql -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.34</version>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.0.14</version>
</dependency>
<!--guava-->
<dependency>
<groupId>com.google.guava</groupId>
<artifactId>guava</artifactId>
<version>15.0</version>
</dependency>
<!-- junit-->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
<!-- json -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.28</version>
</dependency>
<!-- aspectj -->
<dependency>
<groupId>org.aspectj</groupId>
<artifactId>aspectjweaver</artifactId>
<version>1.8.8</version>
</dependency>
<!-- 日志 -->
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
<version>1.7.12</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-log4j12</artifactId>
<version>1.7.12</version>
</dependency>
<!-- 仓库中的包无法正常下载下来,所以,只能本地引用 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>1.1.2-beta5</version>
</dependency>
<dependency>
<groupId>org.apache.ant</groupId>
<artifactId>ant</artifactId>
<version>1.10.12</version>
</dependency>
2、新建applicationContext-jdbc.xml配置文件,自定义DynamicDataSource
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:tx="http://www.springframework.org/schema/tx"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx.xsd">
<!-- 动态数据源 -->
<bean id="dataSource" class="com.ds.tools.datasource.DynamicDataSource">
</bean>
<!-- 为Mybatis创建SqlSessionFactory,同时指定数据源 -->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource"/>
<property name="mapperLocations" value="classpath:com/ds/tools/dao/*DAO.xml"/>
</bean>
<!-- Mapper接口所在包名,Spring会自动查找其下的Mapper -->
<bean id="mapperScannerConfigurer" class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<property name="basePackage" value="**.dao"/>
<property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>
</bean>
<!-- 事务管理器 -->
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource"/>
</bean>
<!-- 启动注解事务 -->
<tx:annotation-driven transaction-manager="transactionManager"/>
</beans>
3、新建spring的配置文件applicationContext.xml,配置如下
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:aop="http://www.springframework.org/schema/aop"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd">
<!-- 装载service -->
<context:component-scan base-package="com.ds.tools.service" />
<!-- 开启aop注解方式,默认为false -->
<aop:aspectj-autoproxy/>
<!--spring容器工具类-->
<bean class="com.ds.tools.util.SpringContextUtil"></bean>
<!--其他引入-->
<import resource="applicationContext-jdbc.xml"></import>
</beans>
4、整个项目结构如下
4、DynamicDataSource代码说明
①DynamicDataSource继承AbstractRoutingDataSource,自定义init方法,init方法为整个初始化数据源的核心代码;
②List<DataNode> dataNodeList= SchemaLoaderUtil.getDataNodeList();为解析dble的schema.xml的方法;
③PropertiesFileUtil.getInstance(path);为获取本地配置的链接池配置信息
④获取到dble配置文件中各节点的mysql配置信息后,分别进行初始化,对于不同的数据源,设置不同的类型,将所有初始化完成的数据源调用AbstractRoutingDataSource的setTargetDataSources方法进行设置,此时,所有的数据源就已经初始化完成;
DynamicDataSource代码如下:
package com.ds.tools.datasource;
import com.alibaba.druid.pool.DruidDataSource;
import com.ds.tools.util.mycat.DataNode;
import com.ds.tools.util.PropertiesFileUtil;
import com.ds.tools.util.mycat.SchemaLoaderUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import java.sql.SQLException;
import java.util.*;
/**
* 动态数据源(数据源切换)
*/
public class DynamicDataSource extends AbstractRoutingDataSource {
private final static Logger _log = LoggerFactory.getLogger(DynamicDataSource.class);
private static final ThreadLocal<String> contextHolder = new ThreadLocal<>();
public static String STANDALONE_DB="dndb0"; //默认的公共库
public static Set<String> DB_SET=new HashSet<>(); //其他库
public static Set<String> LOG1_DB_SET=new HashSet<>(); //log1对应的库
public static Set<String> LOG2_DB_SET=new HashSet<>(); //log2对应的库
private static final String URL_PRE="jdbc:mysql://";
private static final String URL_SUFF="?useUnicode=true&characterEncoding=utf-8&autoReconnect=true";
public static final String DB_PRE="dn";
public static final String LOG1_DB_PRE="log1dn";
public static final String LOG2_DB_PRE="log2dn";
@Override
protected Object determineCurrentLookupKey() {
String dataSource = getDataSource();
_log.debug("the current datasource is :{}", dataSource);
return dataSource;
}
/**
* 设置数据源
* @param dataSource
*/
public static void setDataSource(String dataSource) {
contextHolder.set(dataSource);
}
/**
* 获取数据源
* @return
*/
public static String getDataSource() {
return contextHolder.get();
}
/**
* 清除数据源
*/
public static void clearDataSource() {
contextHolder.remove();
}
public void init(){
_log.info(">> >> >>初始化所有数据源...");
List<DataNode> dataNodeList= SchemaLoaderUtil.getDataNodeList();
String path= DynamicDataSource.class.getClassLoader().getResource("jdbc.properties").getPath();
PropertiesFileUtil propertiesFileUtil= PropertiesFileUtil.getInstance(path);
Map<Object,Object> dataSourceMap=new HashMap<>();
//初始化数据源
for(DataNode dataNode:dataNodeList){
DruidDataSource druidDataSource=new DruidDataSource();
druidDataSource.setDriverClassName(propertiesFileUtil.get("jdbc.driver"));
druidDataSource.setUrl(URL_PRE+dataNode.getDataHost().getWriteHostUrl()+"/"+dataNode.getDbName()+URL_SUFF);
druidDataSource.setUsername(dataNode.getDataHost().getUserName());
druidDataSource.setPassword(dataNode.getDataHost().getPassword());
druidDataSource.setInitialSize(propertiesFileUtil.getInt("druid.initialSize"));
druidDataSource.setMinIdle(propertiesFileUtil.getInt("druid.minIdle"));
druidDataSource.setMaxActive(propertiesFileUtil.getInt("druid.maxActive"));
druidDataSource.setMaxWait(propertiesFileUtil.getInt("druid.maxWait"));
druidDataSource.setTimeBetweenEvictionRunsMillis(propertiesFileUtil.getInt("druid.timeBetweenEvictionRunsMillis"));
druidDataSource.setMinEvictableIdleTimeMillis(propertiesFileUtil.getInt("druid.minEvictableIdleTimeMillis"));
druidDataSource.setValidationQuery(propertiesFileUtil.get("druid.validationQuery"));
druidDataSource.setTestWhileIdle(propertiesFileUtil.getBool("druid.testWhileIdle"));
druidDataSource.setTestOnBorrow(propertiesFileUtil.getBool("druid.testOnBorrow"));
druidDataSource.setTestOnReturn(propertiesFileUtil.getBool("druid.testOnReturn"));
try {
druidDataSource.setFilters(propertiesFileUtil.get("druid.filters"));
druidDataSource.init();
dataSourceMap.put(dataNode.getName(),druidDataSource);
if(dataNode.getName().startsWith(DB_PRE)&&!dataNode.getName().equals(STANDALONE_DB)){
DB_SET.add(dataNode.getName());
}else if(dataNode.getName().startsWith(LOG1_DB_PRE)){
LOG1_DB_SET.add(dataNode.getName());
}else if(dataNode.getName().startsWith(LOG2_DB_PRE)){
LOG2_DB_SET.add(dataNode.getName());
}
_log.info("数据库:{} 初始化成功!",dataNode.getName());
} catch (SQLException e) {
e.printStackTrace();
_log.error("数据库:{} 初始化失败,程序中断!",dataNode.getName());
System.exit(1);
}
}
setTargetDataSources(dataSourceMap);
_log.info(">> >> >>所有数据源初始结束");
}
@Override
public void afterPropertiesSet() {
init();
super.afterPropertiesSet();
}
public static void main(String[] args) {
DynamicDataSource dynamicDataSource = new DynamicDataSource();
dynamicDataSource.init();
}
}
解析配置文件的getDataNodeList方法代码如下:
/**
* 解析配置文件
* @return
*/
public static List<DataNode> getDataNodeList() {
Map<String, DataHost> dataHostMap = new HashMap<>();
List<DataNode> dataNodeList = new ArrayList<>();
DocumentBuilderFactory documentBuilderFactory = DocumentBuilderFactory.newInstance();
InputStream in = null;
try {
documentBuilderFactory.setValidating(false);
documentBuilderFactory.setNamespaceAware(false);
documentBuilderFactory.setFeature("http://xml.org/sax/features/namespaces", false);
documentBuilderFactory.setFeature("http://xml.org/sax/features/validation", false);
documentBuilderFactory.setFeature("http://apache.org/xml/features/nonvalidating/load-dtd-grammar", false);
documentBuilderFactory.setFeature("http://apache.org/xml/features/nonvalidating/load-external-dtd", false);
DocumentBuilder documentBuilder = documentBuilderFactory.newDocumentBuilder();
in = SchemaLoaderUtil.class.getClassLoader().getResourceAsStream("schema.xml");
Document document = documentBuilder.parse(in);
Element rootElement = document.getDocumentElement();
//获取dataHost
NodeList dataHostNList = rootElement.getElementsByTagName("dataHost");
for (int i = 0; i < dataHostNList.getLength(); i++) {
Element dataHostEl = (Element) dataHostNList.item(i);
DataHost dataHost = wrappDataHost(dataHostEl);
dataHostMap.put(dataHost.getName(), dataHost);
}
//获取dataNode
NodeList dataNodeNList = rootElement.getElementsByTagName("dataNode");
for (int i = 0; i < dataNodeNList.getLength(); i++) {
Element dataHostEl = (Element) dataNodeNList.item(i);
List<DataNode> dataNodeListItem = wrappDataNode(dataHostEl, dataHostMap);
dataNodeList.addAll(dataNodeListItem);
}
} catch (ParserConfigurationException e) {
e.printStackTrace();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} catch (SAXException e) {
e.printStackTrace();
} finally {
try {
in.close();
} catch (IOException e) {
e.printStackTrace();
}
}
return dataNodeList;
}
public static DataHost wrappDataHost(Element dataHostEl) {
DataHost dataHost = new DataHost();
dataHost.setName(dataHostEl.getAttribute("name"));
NodeList writeHostNode = dataHostEl.getElementsByTagName("writeHost");
if (writeHostNode.getLength() > 0) {
Element writeHostEl = (Element) writeHostNode.item(0);
dataHost.setWriteHostUrl(writeHostEl.getAttribute("url"));
dataHost.setUserName(writeHostEl.getAttribute("user"));
dataHost.setPassword(writeHostEl.getAttribute("password"));
NodeList readNodeList = writeHostEl.getElementsByTagName("readHost");
if (readNodeList.getLength() > 0) {
Element readHostEl = (Element) readNodeList.item(0);
dataHost.setReadHostUrl(readHostEl.getAttribute("url"));
}
}
return dataHost;
}
public static List<DataNode> wrappDataNode(Element dataNodeEl, Map<String, DataHost> dataHostMap) {
List<DataNode> dataNodeList = new ArrayList<>();
String nameM = dataNodeEl.getAttribute("name");
String databaseNameM = dataNodeEl.getAttribute("database");
String dataHost = dataNodeEl.getAttribute("dataHost");
if (nameM.equals("dndb0")) {
DataNode dataNode = new DataNode();
dataNode.setName(nameM);
dataNode.setDbName(databaseNameM);
dataNode.setDataHost(dataHostMap.get(dataHost));
dataNodeList.add(dataNode);
return dataNodeList;
}
String namePre = nameM.split("\\$")[0];
String databaseNameMPre = databaseNameM.split("\\$")[0];
String numScope = nameM.substring(nameM.indexOf("$") + 1);
String[] dbBanners = numScope.split("-");
int startN = Integer.valueOf(dbBanners[0]);
int endN = Integer.valueOf(dbBanners[1]);
for (int i = startN; i <= endN; i++) {
DataNode dataNode = new DataNode();
dataNode.setName(namePre + i);
dataNode.setDbName(databaseNameMPre + i);
dataNode.setDataHost(dataHostMap.get(dataHost));
dataNodeList.add(dataNode);
}
return dataNodeList;
}
5、定义导出的模板,在模板中定义数据源选择的规则init()方法,定义数据导出的规则,代码如下
package com.ds.tools.cmd;
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Stack;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.Sheet;
import com.ds.tools.datasource.DynamicDataSource;
import com.ds.tools.model.DBoffset;
import com.ds.tools.util.DateUtil;
import com.ds.tools.util.FileUtil;
import com.ds.tools.util.ZipUtil;
public abstract class ExportTemplate{
private final static Logger _log = LoggerFactory.getLogger(ExportTemplate.class);
//校验参数
private final static int MAX_ROW = 1000000;
Stack<DBoffset> stack;
int DBType; //0:公共库,1:log1库;2:log2库
String fileNamePre;
Class entityClass;
public void init() {
stack = new Stack<>();
if (DBType == 0) {
stack.push(new DBoffset(DynamicDataSource.STANDALONE_DB));
} else if (DBType == 1) {
for (String logDb : DynamicDataSource.LOG1_DB_SET) {
stack.push(new DBoffset(logDb));
}
} else if (DBType == 2) {
for (String logDb : DynamicDataSource.LOG2_DB_SET) {
stack.push(new DBoffset(logDb));
}
} else {
for (String otherDb : DynamicDataSource.DB_SET) {
stack.push(new DBoffset(otherDb));
}
}
}
/**
* 设置导出表DB类型
*/
public abstract void setDBType();
/**
* 设置文件名前缀
*/
public abstract void setFileNamePre();
/**
* 设置实体类
*/
public abstract void setEntityClass();
/**
* 校验传入的参数,并注入bean
* @param args
*/
public abstract void validateArgs(String[] args);
/**
* 获取列表
* @param args
* @return
*/
public abstract List queryList(String[] args,int startOffset);
public void exprot(String[] args) {
validateArgs(args);
long startTime=System.currentTimeMillis();
setDBType();
setFileNamePre();
setEntityClass();
init();
List<File> fileList = new ArrayList<>();
int fileCount = 1;
long rowCount = 0;
boolean needCreatFileFlag = true;
String fileName = null;
OutputStream out = null;
ExcelWriter writer = null;
Sheet sheet1 = null;
String dataDir= FileUtil.getDataDir();
String fileTime = DateUtil.getTime();
try {
while (!stack.empty()) {
DBoffset dBoffset = stack.pop();
if (needCreatFileFlag) {
fileName = fileNamePre + fileTime + "_" + fileCount + ".xlsx";
String filePath=dataDir+File.separator+fileName;
fileList.add(new File(filePath));
out = new FileOutputStream(filePath);
writer = EasyExcelFactory.getWriter(out);
sheet1 = new Sheet(1, 0, entityClass, "sheet1", null);
sheet1.setAutoWidth(Boolean.TRUE);
}
DynamicDataSource.setDataSource(dBoffset.getDbName());//动态选择数据源
_log.debug("当前数据源:{}", dBoffset.getDbName());
int startOffset = 0;
while (true) {
//单个sheet最大写入100万
if (rowCount > MAX_ROW) {
//关闭当前文件,重新压栈
writer.finish();
out.close();
fileCount++;
rowCount = 0;
needCreatFileFlag = true;
dBoffset.setOffset(startOffset);
stack.push(dBoffset);
break;
}
//重新压栈的需要跳步走
needCreatFileFlag = false;
if (dBoffset.getOffset() > 0&&rowCount==0) {
startOffset = dBoffset.getOffset();
}
List logList =queryList(args,startOffset);
writer.write(logList, sheet1);
rowCount = rowCount + logList.size();
if (logList.size() < 1000) {
break;
}
startOffset = startOffset + 1000;
_log.info(fileName + "已写行数:{}", rowCount);
}
}
writer.finish();
out.close();
//压缩文件
File file = new File(dataDir+File.separator+fileNamePre + fileTime + ".zip");
ZipUtil.zipFiles(fileList, file);
//删除源文件
for (File fileItem : fileList) {
fileItem.delete();
}
_log.info("导出结束!耗时:"+(System.currentTimeMillis()-startTime)/1000/60+"分钟");
} catch (Exception e) {
e.printStackTrace();
}
}
}
6、定义各个导出数据的入口,继承导出模板设置数据源类型,到这里为止,所有数据源动态切换的功能全部完成;代码如下:
package com.ds.tools.cmd;
import java.util.List;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.ds.tools.model.Log1;
import com.ds.tools.service.Log1Service;
import com.ds.tools.util.SpringContextUtil;
public class LogExportorTest1 extends ExportTemplate {
private final static Logger _log = LoggerFactory.getLogger(LogExportorTest1.class);
private Log1Service log1Service;
@Override
public void setDBType() {
this.DBType = 1;//设置类型
}
@Override
public void setFileNamePre() {
this.fileNamePre = "Log1导出";
}
@Override
public void setEntityClass() {
this.entityClass = Log1.class;
}
@Override
public void validateArgs(String[] args) {
//init spring 容器
ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");
log1Service = SpringContextUtil.getBean(Log1Service.class);
}
@Override
public List queryList(String[] args,int startOffset) {
Integer days=args.length==2?Integer.valueOf(args[1]):0;
List<Log1> logList = log1Service.getLog1ByOffset(startOffset, 1000);
return logList;
}
}
7、使用main方法直接调用导出数据的入口,则会开启导出;代码如下:
public static void main(String[] args) {
System.out.println("开始导出Log1...");
LogExportorTest1 log1Exportor = new LogExportorTest1();
log1Exportor.exprot(null);//调用ExportTemplate的exprot方法执行导出
}