README.md · hurricane.hu/data_reports

一、前言

随着业务的飞速发展和各种业务数据库的扩展，很多场景需要用到导出线上数据进行报表分析，以便及时对业务做出核实的调整

二、各种导出工具

1、SQL工具导出，比如navicat、sqlyog等

缺点：比较原始的报表导出工具

1.1  一般需要运维或者DB开发在线上SQL运维工具执行sql导出数据，还需要应用开发人员提供相应的sql，对于业务和开发来说操作比较繁琐

1.2 查询大量数据时对业务数据库压力较大，容易造成数据库宕机

1.3 无法跨数据服务器执行SQL，整合报表数据比较困难

1.4 无法提供直观的可视化分析，比如web在线数据分析

2、通过写业务代码定制化报表

缺点：相比方式一，对业务来说比较方便了，可以随时在线导出报表，但是其他问题还是无法解决

1.1 查询大量数据时对业务数据库压力较大，容易造成数据库宕机

1.2 无法跨数据服务器执行SQL，整合报表数据比较困难

1.3 数据库结构改动需要重新开发上线，很难做到动态定制化报表
    
    3、通过数据平台

缺点：相比方式一和方式二，解决了几乎大部分问题，同步各业务数据到数据平台的数据库，可以定制各种各种的数据报表，但是对于各自业务数据库来说，比较重量级，而且数据平台的技术开发需要对业务数据库模型和相关的业务比较熟悉才行

三、轻量级数据报表插件开发
  ![报表导出后台](https://gitee.com/uploads/images/2018/0321/224213_8f84abb8_404419.png "屏幕截图.png")
  ![数据源配置后台](https://gitee.com/uploads/images/2018/0321/224300_c353a626_404419.png "屏幕截图.png")      
  
  1、V1.0 版本需求：

1.1 支持可插拔，即插即用模式，工作原理仿照阿里巴巴的druid数据源

1.2 支持自动建配置表

1.3 支持动态切换数据源，可动态配置执行sql的数据源

1.4 支持供外部接口调用

1.5 支持百万级以上大批量Excel数据导出

2、工作原理

1.1 可插拔功能实现，使用原始的Servlet，通过在web.xml配置请求Servlet
```
<servlet>
<servlet-name>report</servlet-name>
<servlet-class>com.data.reports.support.SqlReportServlet</servlet-class>
</servlet>

<servlet-mapping>
<servlet-name>report</servlet-name>
<url-pattern>/report/*</url-pattern>
</servlet-mapping>
                       
``` 
      1.2 支持自动建配置表功能实现，使用原始的jdbc建表

SQL数据源数据库表设计
```
CREATE TABLE `sql_db_config` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `config_code` varchar(100) DEFAULT NULL COMMENT '唯一编码',
  `config_name` varchar(100) DEFAULT NULL COMMENT '数据源名称',
  `url` varchar(255) DEFAULT NULL COMMENT '数据源url',
  `username` varchar(100) DEFAULT NULL COMMENT '用户名',
  `password` varchar(80) DEFAULT NULL COMMENT '密码',
  `status` int(2) DEFAULT '0' COMMENT '状态',
  `create_user` varchar(80) DEFAULT NULL COMMENT '创建人',
  `create_time` bigint(20) DEFAULT NULL COMMENT '创建时间',
  `modify_user` varchar(80) DEFAULT NULL COMMENT '更新人',
  `modify_time` bigint(20) DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='数据源配置';
```

SQL配置表数据库表设计
```
CREATE TABLE `sql_reports_config` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `db_code` varchar(100) DEFAULT NULL,
  `config_code` varchar(100) DEFAULT NULL,
  `report_name` varchar(100) DEFAULT NULL COMMENT '报表名称',
  `report_desc` varchar(255) DEFAULT NULL COMMENT '报表描述',
  `sql_content` text COMMENT 'SQL执行内容',
  `report_type` varchar(40) DEFAULT NULL COMMENT '报表类型',
  `status` int(2) DEFAULT '0' COMMENT '状态',
  `create_user` varchar(80) DEFAULT NULL COMMENT '创建人',
  `create_time` bigint(20) DEFAULT NULL COMMENT '创建时间',
  `modify_user` varchar(80) DEFAULT NULL COMMENT '更新人',
  `modify_time` bigint(20) DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='报表配置';
```

JDBC代码实现
```
package com.data.reports.manager;

import com.data.reports.manager.database.DataSourceContextHolder;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCallback;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.jdbc.support.rowset.SqlRowSet;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
* 报表增删改查实现
* @author Hurricane.Hu
* @version V1.0
* @Title: JdbcSqlConfigService.java
* @Package com.data.com.data.reports.manager
* @Description
* @date 2018 03-12 16:38.
*/
public class JdbcSqlConfigService implements SqlConfigService {

private final static Logger LOGGER= LoggerFactory.getLogger(JdbcSqlConfigService.class);

private static String TABLE_NAME ="SQL_REPORTS_CONFIG";

private static final String ADD_CONFIG ="INSERT INTO "+ TABLE_NAME +" (db_code,config_code,report_name,report_desc,sql_content,report_type,status,create_user,create_time)" +
            " VALUES(?, ?, ? , ? , ?, ?, ?, ?, ?, ROUND(UNIX_TIMESTAMP(NOW(4))*1000))";

private static final String DELETE_CONFIG ="DELETE FROM "+ TABLE_NAME +" WHERE id=?";

private static final String GET_BY_ID ="SELECT * FROM "+ TABLE_NAME + " WHERE id=?";

private static final String GET_BY_CODE ="SELECT * FROM "+ TABLE_NAME + " WHERE config_code=?";

private static final String CREATE_TABLE ="CREATE TABLE IF NOT EXISTS "+ TABLE_NAME +" (" +
            "`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',"+
            "`db_code` varchar(100) DEFAULT NULL COMMENT '数据源唯一编码'," +
            "`config_code` varchar(100) DEFAULT NULL COMMENT '唯一编码'," +
            "`report_name` varchar(100) DEFAULT NULL COMMENT '报表名称'," +
            "`report_desc` varchar(255) DEFAULT NULL COMMENT '报表描述'," +
            "`sql_content` text DEFAULT NULL COMMENT 'SQL执行内容'," +
            "`report_type` varchar(40) DEFAULT NULL COMMENT '报表类型'," +
            "`status` int(2) DEFAULT 0 COMMENT '状态'," +
            "`create_user` varchar(80) DEFAULT NULL COMMENT '创建人'," +
            "`create_time` bigint(20) DEFAULT NULL COMMENT '创建时间'," +
            "`modify_user` varchar(80) DEFAULT NULL COMMENT '更新人'," +
            "`modify_time` bigint(20) DEFAULT NULL COMMENT '更新时间'," +
            "PRIMARY KEY (`id`)" +
            ") ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='报表配置'";

private static final String CHECK_EXISTS ="SHOW TABLES LIKE '"+ TABLE_NAME +"'";

private static volatile boolean isTableExists;

private JdbcTemplate jdbcTemplate;

public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

private static String getUpdateSqlAndArgs(SqlReportConfig config,List<Object> args){
        if (config.getId() == null) {
            throw new IllegalArgumentException("id is null.");
        }
        StringBuilder sql = new StringBuilder("UPDATE "
                                              + TABLE_NAME
                                              + " SET modify_time=ROUND(UNIX_TIMESTAMP(now(4))*1000)");
        if (StringUtils.isNotBlank(config.getConfigCode())) {
            sql.append(",config_code=?");
            args.add(config.getConfigCode());
        }
        if (StringUtils.isNotBlank(config.getDbCode())) {
            sql.append(",db_code=?");
            args.add(config.getDbCode());
        }
        if (StringUtils.isNotBlank(config.getReportName())) {
            sql.append(",report_name=?");
            args.add(config.getReportName());
        }
        if (StringUtils.isNotBlank(config.getReportDesc())) {
            sql.append(",report_desc=?");
            args.add(config.getReportDesc());
        }
        if (StringUtils.isNotBlank(config.getSqlContent())) {
            sql.append(",sql_content=?");
            args.add(config.getSqlContent());
        }
        if (StringUtils.isNotBlank(config.getReportType())) {
            sql.append(",report_type=?");
            args.add(config.getReportType());
        }
        if (config.getStatus() != null) {
            sql.append(",status=?");
            args.add(config.getStatus());
        }
        sql.append(" WHERE id=?");
        args.add(config.getId());
        return sql.toString();
    }

private static String getQueryPageSqlAndArgs(Map<String,String> parameters,List<Object> args){
        StringBuilder sql = new StringBuilder("SELECT * FROM " + TABLE_NAME +" WHERE 1=1 ");
        if (parameters==null || parameters.size() <=0 || args==null){
            return sql.toString();
        }
        if (StringUtils.isNotBlank(parameters.get("configCode"))) {
            sql.append(" AND config_code=? ");
            args.add(parameters.get("configCode"));
        }
        if (StringUtils.isNotBlank(parameters.get("dbCode"))) {
            sql.append(" AND db_code=? ");
            args.add(parameters.get("dbCode"));
        }
        if (StringUtils.isNotBlank(parameters.get("reportName"))) {
            sql.append(" AND report_name=? ");
            args.add(parameters.get("reportName"));
        }
        if (StringUtils.isNotBlank(parameters.get("reportDesc"))) {
            sql.append(" AND report_desc=? ");
            args.add(parameters.get("reportDesc"));
        }
        if (StringUtils.isNotBlank(parameters.get("reportType"))) {
            sql.append(" AND report_type=? ");
            args.add(parameters.get("reportType"));
        }
        if (StringUtils.isNotBlank(parameters.get("status"))) {
            sql.append(" AND status=? ");
            args.add(parameters.get("status"));
        }
        sql.append(" LIMIT ?,? ");
        int currentPage=StringUtils.isBlank(parameters.get("page"))? 1: Integer.parseInt(parameters.get("page"));
        int pageSize=StringUtils.isBlank(parameters.get("perPageCount"))? 15: Integer.parseInt(parameters.get("perPageCount"));
        int start=(currentPage-1) * pageSize;
        args.add(start);
        args.add(pageSize);
        return sql.toString();
    }

private void createTableNX(){
        if(isTableExists){
            return;
        }
        isTableExists=jdbcTemplate.query(CHECK_EXISTS, new ResultSetExtractor<Boolean>() {
            public Boolean extractData(ResultSet resultSet) throws SQLException, DataAccessException {
                return resultSet.next();
            }
        });
        if(!isTableExists){
            if(LOGGER.isDebugEnabled()){
                LOGGER.debug("创建报表配置表成功.");
            }
            jdbcTemplate.execute(CREATE_TABLE);
        }
    }

public void init(){//初始化建表，如果表不存在
        createTableNX();
    }

public Integer addSqlReportConfig(final SqlReportConfig config) {

KeyHolder holder = new GeneratedKeyHolder();

jdbcTemplate.update(new PreparedStatementCreator() {

public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement preparedStatement = connection.prepareStatement(ADD_CONFIG, Statement.RETURN_GENERATED_KEYS);
                preparedStatement.setString(1, config.getDbCode());
                preparedStatement.setString(2, config.getConfigCode());
                preparedStatement.setString(3, config.getReportName());
                preparedStatement.setString(4, config.getReportDesc());
                preparedStatement.setString(5, config.getSqlContent());
                preparedStatement.setString(6, config.getReportType());
                preparedStatement.setInt(7, config.getStatus());
                preparedStatement.setString(8, config.getCreateUser());
                return preparedStatement;
            }
        }, holder);

return holder.getKey().intValue();
    }

public SqlReportConfig getSqlReportConfigById(final Integer id) {
        return jdbcTemplate.query(GET_BY_ID, new PreparedStatementSetter() {

public void setValues(PreparedStatement preparedStatement) throws SQLException {
                preparedStatement.setInt(1, id);
            }
        }, new ResultSetExtractor<SqlReportConfig>() {
            public SqlReportConfig extractData(ResultSet resultSet) throws SQLException, DataAccessException {
                if (resultSet.next()) {
                    SqlReportConfig config = new SqlReportConfig();
                    config.setDbCode(resultSet.getString("db_code"));
                    config.setConfigCode(resultSet.getString("config_code"));
                    config.setReportName(resultSet.getString("report_name"));
                    config.setReportDesc(resultSet.getString("report_desc"));
                    config.setSqlContent(resultSet.getString("sql_content"));
                    config.setReportType(resultSet.getString("report_type"));
                    config.setStatus(resultSet.getInt("status"));
                    config.setCreateTime(resultSet.getLong("create_time"));
                    config.setCreateUser(resultSet.getString("create_user"));
                    config.setModifyTime(resultSet.getLong("modify_time"));
                    config.setModifyUser(resultSet.getString("modify_user"));
                    return config;
                }
                return null;
            }
        });
    }

public SqlReportConfig getSqlReportConfigByCode(final String code) {
        return jdbcTemplate.query(GET_BY_CODE, new PreparedStatementSetter() {

public void setValues(PreparedStatement preparedStatement) throws SQLException {
                preparedStatement.setString(1, code);
            }
        }, new ResultSetExtractor<SqlReportConfig>() {
            public SqlReportConfig extractData(ResultSet resultSet) throws SQLException, DataAccessException {
                if (resultSet.next()) {
                    SqlReportConfig config = new SqlReportConfig();
                    config.setDbCode(resultSet.getString("db_code"));
                    config.setConfigCode(resultSet.getString("config_code"));
                    config.setReportName(resultSet.getString("report_name"));
                    config.setReportDesc(resultSet.getString("report_desc"));
                    config.setSqlContent(resultSet.getString("sql_content"));
                    config.setReportType(resultSet.getString("report_type"));
                    config.setStatus(resultSet.getInt("status"));
                    return config;
                }
                return null;
            }
        });
    }

public Integer updateSqlReportConfig(final SqlReportConfig config) {
        List<Object> args= new ArrayList<>();
        return jdbcTemplate.update(getUpdateSqlAndArgs(config, args), args.toArray());

}

public Integer deleteSqlReportConfig(final Integer id) {
       return jdbcTemplate.execute(new PreparedStatementCreator() {

public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
               return connection.prepareStatement(DELETE_CONFIG);
           }
       }, new PreparedStatementCallback<Integer>() {

public Integer doInPreparedStatement(PreparedStatement preparedStatement) throws SQLException, DataAccessException {
               preparedStatement.setInt(1, id);
               return preparedStatement.executeUpdate();
           }
       });
    }

public List<SqlReportConfig> listSqlReportConfigPage(Map<String,String> parameters) {
        createTableNX();
        List<Object> args=new ArrayList<>();
        String sql=getQueryPageSqlAndArgs(parameters,args);
        return jdbcTemplate.query(sql, new RowMapper<SqlReportConfig>() {
            @Override
            public SqlReportConfig mapRow(ResultSet resultSet, int rowNum) throws SQLException {
                SqlReportConfig config = new SqlReportConfig();
                config.setId(resultSet.getInt("id"));
                config.setDbCode(resultSet.getString("db_code"));
                config.setConfigCode(resultSet.getString("config_code"));
                config.setReportName(resultSet.getString("report_name"));
                config.setReportDesc(resultSet.getString("report_desc"));
                config.setSqlContent(resultSet.getString("sql_content"));
                config.setReportType(resultSet.getString("report_type"));
                config.setStatus(resultSet.getInt("status"));
                config.setCreateUser(resultSet.getString("create_user"));
                config.setCreateTime(resultSet.getLong("create_time"));
                config.setModifyUser(resultSet.getString("modify_user"));
                config.setModifyTime(resultSet.getLong("modify_time"));
                return config;
            }
        }, args.toArray());
    }

@Override
    public SqlRowSet getResultSet4ExecuteReportSql(SqlReportConfig config) {
        DataSourceContextHolder.setDbType(config.getDbCode());
        return jdbcTemplate.queryForRowSet(config.getSqlContent());
    }

@Override
    public Integer getCount4ExecuteReportSql(String sql) {
        return jdbcTemplate.query(sql, new ResultSetExtractor<Integer>() {
            public Integer extractData(ResultSet resultSet) throws SQLException, DataAccessException {
                return resultSet.getInt(1);
            }
        });
    }
}
```

JDBC代码实现
```
package com.data.reports.manager.database;

import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
* 数据源配置实现
* @author Hurricane.Hu
* @version V1.0
* @Title: JdbcDataSourceConfigService.java
* @Package com.data.com.data.reports.manager
* @Description
* @date 2018 03-12 16:38.
*/
public class JdbcDataSourceConfigService implements DataSourceConfigService {

private final static Logger LOGGER= LoggerFactory.getLogger(JdbcDataSourceConfigService.class);

private static String TABLE_NAME ="SQL_DB_CONFIG";

private static final String ADD_CONFIG="INSERT INTO "+ TABLE_NAME +" (config_code,config_name,url,username,password,status,create_user,create_time)" +
            " VALUES( ?, ? , ? , ?, ?, ?, ?, ? ,ROUND(UNIX_TIMESTAMP(NOW(4))*1000))";

private static final String DELETE_CONFIG="DELETE FROM "+ TABLE_NAME +" WHERE id=?";

private static final String GET_BY_ID ="SELECT * FROM "+ TABLE_NAME + " WHERE id=?";

private static final String GET_BY_CODE ="SELECT * FROM "+ TABLE_NAME + " WHERE config_code=?";

private static final String CREATE_TABLE ="CREATE TABLE IF NOT EXISTS "+ TABLE_NAME +" (" +
            "`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',"+
            "`config_code` varchar(100) DEFAULT NULL COMMENT '唯一编码'," +
            "`config_name` varchar(100) DEFAULT NULL COMMENT '数据源名称'," +
            "`url` varchar(255) DEFAULT NULL COMMENT '数据源url'," +
            "`username` varchar(100) DEFAULT NULL COMMENT '用户名'," +
            "`password` varchar(80) DEFAULT NULL COMMENT '密码'," +
            "`status` int(2) DEFAULT 0 COMMENT '状态'," +
            "`create_user` varchar(80) DEFAULT NULL COMMENT '创建人'," +
            "`create_time` bigint(20) DEFAULT NULL COMMENT '创建时间'," +
            "`modify_user` varchar(80) DEFAULT NULL COMMENT '更新人'," +
            "`modify_time` bigint(20) DEFAULT NULL COMMENT '更新时间'," +
            "PRIMARY KEY (`id`)" +
            ") ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='数据源配置'";

private static final String CHECK_EXISTS ="SHOW TABLES LIKE '"+ TABLE_NAME +"'";

private static volatile boolean isTableExists;

private JdbcTemplate jdbcTemplate;

public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

private static String getUpdateSqlAndArgs(DataSourceConfig config,List<Object> args){
        if (config.getId() == null) {
            throw new IllegalArgumentException("id is null.");
        }
        StringBuilder sql = new StringBuilder("UPDATE "
                + TABLE_NAME
                + " SET modify_time=ROUND(UNIX_TIMESTAMP(now(4))*1000)");
        if (StringUtils.isNotBlank(config.getConfigCode())) {
            sql.append(",config_code=?");
            args.add(config.getConfigCode());
        }
        if (StringUtils.isNotBlank(config.getConfigName())) {
            sql.append(",config_name=?");
            args.add(config.getConfigName());
        }
        if (StringUtils.isNotBlank(config.getUrl())) {
            sql.append(",url=?");
            args.add(config.getUrl());
        }
        if (StringUtils.isNotBlank(config.getUsername())) {
            sql.append(",username=?");
            args.add(config.getUsername());
        }
        if (StringUtils.isNotBlank(config.getPassword())) {
            sql.append(",password=?");
            args.add(config.getPassword());
        }
        if (config.getStatus() != null) {
            sql.append(",status=?");
            args.add(config.getStatus());
        }
        sql.append(" WHERE id=?");
        args.add(config.getId());
        return sql.toString();
    }

private static String getQueryPageSqlAndArgs(Map<String,String> parameters,List<Object> args){
        StringBuilder sql = new StringBuilder("SELECT * FROM " + TABLE_NAME +" WHERE 1=1 ");
        if (parameters==null || parameters.size() <=0 || args==null){
            return sql.toString();
        }
        if (StringUtils.isNotBlank(parameters.get("configCode"))) {
            sql.append(" AND config_code=? ");
            args.add(parameters.get("configCode"));
        }
        if (StringUtils.isNotBlank(parameters.get("configName"))) {
            sql.append(" AND config_name=? ");
            args.add(parameters.get("configName"));
        }
        if (StringUtils.isNotBlank(parameters.get("status"))) {
            sql.append(" AND status=? ");
            args.add(parameters.get("status"));
        }
        sql.append(" LIMIT ?,? ");
        int currentPage=StringUtils.isBlank(parameters.get("page"))? 1: Integer.parseInt(parameters.get("page"));
        int pageSize=StringUtils.isBlank(parameters.get("perPageCount"))? 15: Integer.parseInt(parameters.get("perPageCount"));
        int start=(currentPage-1) * pageSize;
        args.add(start);
        args.add(pageSize);
        return sql.toString();
    }

public void init(){//初始化建表，如果表不存在
        createTableNX();
    }

@Override
    public Integer addDataSourceConfig(DataSourceConfig config) {
        return null;
    }

@Override
    public DataSourceConfig getDataSourceConfigById(Integer id) {
        return null;
    }

@Override
    public DataSourceConfig getDataSourceConfigByCode(final String code) {
        return jdbcTemplate.query(GET_BY_CODE, new PreparedStatementSetter() {

public void setValues(PreparedStatement preparedStatement) throws SQLException {
                preparedStatement.setString(1, code);
            }
        }, new ResultSetExtractor<DataSourceConfig>() {
            public DataSourceConfig extractData(ResultSet resultSet) throws SQLException, DataAccessException {
                if (resultSet.next()) {
                    DataSourceConfig dataSourceConfig = new DataSourceConfig();
                    dataSourceConfig.setConfigCode(resultSet.getString("config_code"));
                    dataSourceConfig.setConfigName(resultSet.getString("config_name"));
                    dataSourceConfig.setUrl(resultSet.getString("url"));
                    dataSourceConfig.setUsername(resultSet.getString("username"));
                    dataSourceConfig.setPassword(resultSet.getString("password"));
                    dataSourceConfig.setStatus(resultSet.getInt("status"));
                    return dataSourceConfig;
                }
                return null;
            }
        });
    }

@Override
    public Integer updateDataSourceConfig(DataSourceConfig config) {
        return null;
    }

@Override
    public Integer deleteDataSourceConfig(Integer id) {
        return null;
    }

@Override
    public List<DataSourceConfig> listDataSourcePage(Map<String, String> parameters) {
        createTableNX();
        List<Object> args=new ArrayList<>();
        String sql=getQueryPageSqlAndArgs(parameters,args);
        return jdbcTemplate.query(sql, new RowMapper<DataSourceConfig>() {
            @Override
            public DataSourceConfig mapRow(ResultSet resultSet, int rowNum) throws SQLException {
                DataSourceConfig dataSourceConfig = new DataSourceConfig();
                dataSourceConfig.setId(resultSet.getInt("id"));
                dataSourceConfig.setConfigCode(resultSet.getString("config_code"));
                dataSourceConfig.setConfigName(resultSet.getString("config_name"));
                dataSourceConfig.setUrl(resultSet.getString("url"));
                dataSourceConfig.setUsername(resultSet.getString("username"));
                dataSourceConfig.setPassword(resultSet.getString("password"));
                dataSourceConfig.setStatus(resultSet.getInt("status"));
                dataSourceConfig.setCreateUser(resultSet.getString("create_user"));
                dataSourceConfig.setCreateTime(resultSet.getLong("create_time"));
                dataSourceConfig.setModifyUser(resultSet.getString("modify_user"));
                dataSourceConfig.setModifyTime(resultSet.getLong("modify_time"));
                return dataSourceConfig;
            }
        }, args.toArray());
    }

@Override
    public Map<String, String> getDataSourceMap() {
        createTableNX();
        String sql=getQueryPageSqlAndArgs(null,new ArrayList<>());
        final Map<String,String> datasourceMap=new HashMap<>();
        jdbcTemplate.query(sql, new RowMapper<DataSourceConfig>() {
            @Override
            public DataSourceConfig mapRow(ResultSet resultSet, int rowNum) throws SQLException {
                datasourceMap.put(resultSet.getString("config_code"),resultSet.getString("config_name"));
                return null;
            }
        });

return datasourceMap;
    }
}
```

1.3 动态数据源实现，利用Spring jdbc框架的AbstractRoutingDataSource

动态数据源
```
package com.data.reports.manager.database;

import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.lang.StringUtils;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

import javax.sql.DataSource;
import java.lang.reflect.Field;
import java.util.HashMap;
import java.util.Map;
import java.util.concurrent.locks.ReentrantLock;

/**
* @author hujifang
* @version V1.0
* @Title: DynamicDataSource.java
* @Package com.data.reports.manager.database
* @Description 动态数据源
* @date 2018 03-16 15:25.
*/
public class DynamicDataSource extends AbstractRoutingDataSource {

private static final String DATA_SOURCES_NAME = "targetDataSources";

//数据源配置服务
    private DataSourceConfigService dataSourceConfigService;

private ReentrantLock lock=new ReentrantLock();

public void setDataSourceConfigService(DataSourceConfigService dataSourceConfigService) {
        this.dataSourceConfigService = dataSourceConfigService;
    }

@SuppressWarnings("unchecked")
    private Map<Object, Object> getTargetDataSources() throws NoSuchFieldException, IllegalAccessException {
        Field field = AbstractRoutingDataSource.class.getDeclaredField(DATA_SOURCES_NAME);
        field.setAccessible(true);
        return (Map<Object, Object>) field.get(this);
    }

@Override
    protected String determineCurrentLookupKey() {
        String dataSourceName = DataSourceContextHolder.getDbType();
        if (StringUtils.isBlank(dataSourceName)) {
            // 默认的数据源名字
            dataSourceName = DataSourceContextHolder.DEFAULT;
        }
        try {
            Map<Object,Object> targetDataSources=getTargetDataSources();
            if(targetDataSources ==null){
                targetDataSources=new HashMap<>();
            }
            DataSource dataSource=(DataSource)targetDataSources.get(dataSourceName);
            if(dataSource==null){
                // 从已创建的数据库中获取要访问的数据库，如果没有则创建一个
                selectDataSource(targetDataSources,dataSourceName);
            }
        } catch (NoSuchFieldException | IllegalAccessException e) {
            throw new IllegalStateException(e);
        }
        return dataSourceName;
    }

/**
     * 该方法为同步方法，防止并发创建两个相同的数据源 使用双检锁的方式，防止并发
     * @param dbType 数据库配置编号
     */
    private void selectDataSource(Map<Object,Object> targetDataSources,String dbType) {
        try {
            lock.lock();
            // 再次从数据库中获取，双检锁
            DataSource dataSource = (DataSource)targetDataSources.get(dbType);
            if (dataSource==null) {
                // 为空则创建数据库
                dataSource = createDataSource(dbType);
                if (null != dataSource) {
                    DataSourceContextHolder.setDbType(dbType);
                    targetDataSources.put(dbType,dataSource);
                    super.afterPropertiesSet();
                } else {
                    throw new IllegalStateException("创建数据源失败.");
                }
            }
        } finally {
            lock.unlock();
        }
    }

/**
     * 查询对应数据库的信息
     * @param dbType 数据库配置编号
     * @return 数据源
     */
    private DataSource createDataSource(String dbType) {
        String originalType = DataSourceContextHolder.getDbType();
        // 先切换回主库
        DataSourceContextHolder.setDbType(DataSourceContextHolder.DEFAULT);
        // 查询所需信息
        DataSourceConfig dataSourceConfig = dataSourceConfigService.getDataSourceConfigByCode(dbType);
        if (dataSourceConfig==null){
            throw new IllegalStateException("DataSource is error.");
        }
        // 切换回目标库
        DataSourceContextHolder.setDbType(originalType);
        return createDataSource(dataSourceConfig.getUrl(),
                                dataSourceConfig.getUsername(),
                                dataSourceConfig.getPassword());
    }

/**
     * 创建数据源
     * @param url 数据库连接
     * @param username 用户名
     * @param password 密码
     * @return 数据源
     */
    private DataSource createDataSource(String url, String username, String password) {
        BasicDataSource dataSource = new BasicDataSource();
        dataSource.setDriverClassName(DbDriverClassUtil.getDriverClassByUrl(url));
        dataSource.setUrl(url);
        dataSource.setUsername(username);
        dataSource.setPassword(password);
        dataSource.setInitialSize(2);
        dataSource.setMaxActive(5);
        dataSource.setMaxIdle(2);
        dataSource.setTimeBetweenEvictionRunsMillis(1000);
        dataSource.setTestWhileIdle(true);
        return dataSource;
    }
}
```

1.4 百万级以上数据导出
* 使用poi高级版本SXSSFWorkbook类，支持把内存中数据定量刷盘
* 使用同一个WorkBook多个Sheet分页数据
* 同时使用RowSet离线数据，不至于sql查询数量过大连接数据库超时

```
package com.data.reports.export.excel;

import com.data.reports.export.excel.annotation.ExcelField;
import com.data.reports.export.excel.annotation.ExcelSheet;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.jdbc.support.rowset.SqlRowSetMetaData;

import java.io.ByteArrayOutputStream;
import java.io.FileOutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Modifier;
import java.util.ArrayList;
import java.util.List;

/**
* Excel导出工具
*
* @author hujifang 2017-09-08 22:27:20
*/
public class ExcelExportUtil {

/**
     * 导出Excel对象
     *
     * @param dataList  Excel数据
     * @return
     */
    public static Workbook exportWorkbook(List<?> dataList){

// data
        if (dataList==null || dataList.size()==0) {
            throw new IllegalArgumentException("data can not be empty.");
        }

// sheet
        Class<?> sheetClass = dataList.get(0).getClass();
        ExcelSheet excelSheet = sheetClass.getAnnotation(ExcelSheet.class);

String sheetName = dataList.get(0).getClass().getSimpleName();
        HSSFColor.HSSFColorPredefined headColor = null;
        if (excelSheet != null) {
            if (excelSheet.name()!=null && excelSheet.name().trim().length()>0) {
                sheetName = excelSheet.name().trim();
            }
            headColor = excelSheet.headColor();
        }

// sheet field
        List<Field> fields = new ArrayList<>();
        if (sheetClass.getDeclaredFields()!=null && sheetClass.getDeclaredFields().length>0) {
            for (Field field: sheetClass.getDeclaredFields()) {
                if (Modifier.isStatic(field.getModifiers())) {
                    continue;
                }
                fields.add(field);
            }
        }

if (fields.size() == 0) {
            throw new IllegalArgumentException("data field can not be empty.");
        }

// book
        Workbook workbook = new HSSFWorkbook();     // HSSFWorkbook=2003/xls、XSSFWorkbook=2007/xlsx
        Sheet sheet = workbook.createSheet(sheetName);

// sheet header row
        CellStyle headStyle = null;
        if (headColor != null) {
            headStyle = workbook.createCellStyle();
            /*Font headFont = book.createFont();
            headFont.setColor(headColor);
            headStyle.setFont(headFont);*/

headStyle.setFillForegroundColor(headColor.getIndex());
            headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            headStyle.setFillBackgroundColor(headColor.getIndex());
        }

Row headRow = sheet.createRow(0);
        for (int i = 0; i < fields.size(); i++) {
            Field field = fields.get(i);
            ExcelField excelField = field.getAnnotation(ExcelField.class);
            String fieldName = (excelField!=null && excelField.name()!=null && excelField.name().trim().length()>0)?excelField.name():field.getName();

Cell cellX = headRow.createCell(i, CellType.STRING);
            if (headStyle != null) {
                cellX.setCellStyle(headStyle);
            }
            cellX.setCellValue(String.valueOf(fieldName));
        }

// sheet data rows
        for (int dataIndex = 0; dataIndex < dataList.size(); dataIndex++) {
            int rowIndex = dataIndex+1;
            Object rowData = dataList.get(dataIndex);

Row rowX = sheet.createRow(rowIndex);

for (int i = 0; i < fields.size(); i++) {
                Field field = fields.get(i);
                try {
                    field.setAccessible(true);
                    Object fieldValue = field.get(rowData);

Cell cellX = rowX.createCell(i, CellType.STRING);
                    cellX.setCellValue(String.valueOf(fieldValue));
                } catch (IllegalAccessException e) {
                    throw new IllegalArgumentException(e);
                }
            }
        }

return workbook;
    }

/**
     * 导出工作簿 Excel
     * @param workbook 工作簿
     * @param dataList 数据集合
     * @param currentPage 工作簿当前页
     * @return Workbook
     */
    public static Workbook exportWorkbook(Workbook workbook, List<?> dataList, String sheetName, int currentPage) {
        // data
        if (dataList==null || dataList.size()==0) {
            throw new IllegalArgumentException("data can not be empty.");
        }
        if (workbook==null){
            workbook = new SXSSFWorkbook(1000);
        }

// sheet
        Class<?> sheetClass = dataList.get(0).getClass();
        ExcelSheet excelSheet = sheetClass.getAnnotation(ExcelSheet.class);

HSSFColor.HSSFColorPredefined headColor = null;
        if (excelSheet != null && StringUtils.isBlank(sheetName)) {
            sheetName=StringUtils.trim(sheetName);
            headColor = excelSheet.headColor();
        }
        sheetName = StringUtils.isBlank(sheetName) ? dataList.get(0).getClass().getSimpleName() : sheetName;

if (fields.size() == 0) {
            throw new IllegalArgumentException("data field can not be empty.");
        }

Sheet sheet = workbook.createSheet(sheetName+currentPage);

// sheet header row
        CellStyle headStyle = null;
        if (headColor != null) {
            headStyle = workbook.createCellStyle();

Row headRow = sheet.createRow(0);
        for (int i = 0; i < fields.size(); i++) {
            Field field = fields.get(i);
            ExcelField excelField = field.getAnnotation(ExcelField.class);
            String fieldName = excelField != null && StringUtils.isNotBlank(excelField.name()) ?excelField.name():field.getName();

// sheet data rows
        for (int dataIndex = 0; dataIndex < dataList.size(); dataIndex++) {
            int rowIndex = dataIndex+1;
            Object rowData = dataList.get(dataIndex);

Row rowX = sheet.createRow(rowIndex);

return workbook;
    }

/**
     * 导出工作簿 Excel
     * @param sqlRowSet 数据游标集合
     * @param sheetName 工作簿名称
     * @param sheetPageSize 工作簿分页大小
     * @return Workbook
     */
    public static Workbook resultSetToExcel(SqlRowSet sqlRowSet, String sheetName, int sheetPageSize) {
        //工作簿每写入1000行数据，就把数据写入磁盘
        Workbook workbook = new SXSSFWorkbook(1000);
        Sheet sheet;
        int rowNo = 0;      //总行号
        int pageRowNo = 0;  //页行号

Row row;
        // sheet header row
        HSSFColor.HSSFColorPredefined headColor=HSSFColor.HSSFColorPredefined.LIGHT_GREEN;
        CellStyle headStyle = workbook.createCellStyle();
        headStyle.setFillForegroundColor(headColor.getIndex());
        headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        headStyle.setFillBackgroundColor(headColor.getIndex());

Cell cell;
        SqlRowSetMetaData md=sqlRowSet.getMetaData();
        int nColumn=md.getColumnCount();

sheetName= StringUtils.isBlank(sheetName)?"Sheet":sheetName.replaceAll("[\\[\\]\\*\\?\\\\:/]*","");
        while (sqlRowSet.next()){
            if (rowNo % sheetPageSize == 0){
                workbook.createSheet(sheetName+rowNo/sheetPageSize);
                pageRowNo = 0;      //每当新建了工作表就将当前工作表的行号重置为0
            }
            sheet=workbook.getSheetAt(rowNo/sheetPageSize);
            row = sheet.createRow(pageRowNo);
            if (pageRowNo == 0) {
                // 写入各个字段的名称
                for (int i = 1; i <= nColumn; i++) {
                    cell = row.createCell(i - 1);
                    cell.setCellStyle(headStyle);
                    cell.setCellType(CellType.STRING);
                    cell.setCellValue(md.getColumnLabel(i));
                }
            } else {
                for (int j = 1; j <= nColumn; j++) {
                    cell = row.createCell(j - 1);
                    cell.setCellType(CellType.STRING);
                    Object oj = sqlRowSet.getObject(j);
                    if (oj == null) {
                        oj = "";
                    }
                    cell.setCellValue(oj.toString());
                }
            }
            pageRowNo++;
            rowNo++;
        }
        return workbook;
    }

/**
     * 导出工作簿二进制格式
     * @param sqlRowSet 数据游标集合
     * @param sheetName 工作簿名称
     * @param sheetPageSize 工作簿分页大小
     */
    public static byte[] resultSetToExcelBytes(SqlRowSet sqlRowSet, String sheetName, int sheetPageSize) {
        // workbook
        Workbook workbook = resultSetToExcel(sqlRowSet, sheetName,sheetPageSize);

ByteArrayOutputStream byteArrayOutputStream = null;
        byte[] result;
        try {
            // workbook 2 ByteArrayOutputStream
            byteArrayOutputStream = new ByteArrayOutputStream();
            workbook.write(byteArrayOutputStream);

// flush
            byteArrayOutputStream.flush();

result = byteArrayOutputStream.toByteArray();
            return result;
        } catch (Exception e) {
            throw new IllegalArgumentException(e);
        } finally {
            try {
                if (byteArrayOutputStream != null) {
                    byteArrayOutputStream.close();
                }
            } catch (Exception e) {
                throw new IllegalArgumentException(e);
            }
        }
    }

/**
     * 导出Excel文件到磁盘
     *
     * @param dataList
     * @param filePath
     */
    public static void exportToFile(List<?> dataList, String filePath){
        // workbook
        Workbook workbook = exportWorkbook(dataList);

FileOutputStream fileOutputStream = null;
        try {
            // workbook 2 FileOutputStream
            fileOutputStream = new FileOutputStream(filePath);
            workbook.write(fileOutputStream);

// flush
            fileOutputStream.flush();
        } catch (Exception e) {
            throw new IllegalArgumentException(e);
        } finally {
            try {
                if (fileOutputStream!=null) {
                    fileOutputStream.close();
                }
            } catch (Exception e) {
                throw new IllegalArgumentException(e);
            }
        }
    }

/**
     * 导出Excel字节数据
     *
     * @param dataList
     * @return
     */
    public static byte[] exportToBytes(List<?> dataList){
        // workbook
        Workbook workbook = exportWorkbook(dataList);

// flush
            byteArrayOutputStream.flush();

}
```

hurricane.hu/data_reports

简介

发行版

贡献者

近期动态

hurricane.hu/data_reports .gitee-modal { width: 500px !important; }

简介

发行版

贡献者

近期动态

搜索帮助

hurricane.hu/data_reports