基于AbstractRoutingDataSource实现dble配置文件多数据源自动切换，并导出表中的数据

最新推荐文章于 2024-04-25 22:40:53 发布

逗神。

最新推荐文章于 2024-04-25 22:40:53 发布

阅读量714

点赞数 1

分类专栏： AbstractRoutingDataSource 文章标签： java maven mysql

本文链接：https://blog.csdn.net/sinat_38406526/article/details/121518087

版权

AbstractRoutingDataSource 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

该博客介绍了如何在使用dble作为数据库中间件时，因审计数据量大导致查询导出问题，从而开发了一款动态切换数据源的工具。文章详细阐述了项目的构建过程，包括引入相关依赖，配置Spring的XML文件，以及自定义DynamicDataSource实现数据源的初始化和动态切换。此外，还展示了如何定义导出模板和数据源选择规则，以实现数据的高效导出。

摘要由CSDN通过智能技术生成

背景：使用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方法执行导出
	}