记一次练手的项目经历-事件管理系统

此项目既为练手，也为简化个人工作。回过头来看，其实前端耗费的时间比较久，其实可以用elementadmin这个成熟框架的。

项目主要技术点：

1. 动态数据源，数据库动态添加，添加后动态切换查询数据
2. 自定义缓存，并启动定时任务定时清理缓存，并使用aop自定义拦截查询并放入缓存
3. 嵌入式数据库derby
4. 多线程异步查询数据，每个线程使用不同的数据源
5. aop获取远程处理结果并进行二次处理
6. 服务器访问IP白名单
7. 统一异常处理
8. 权限控制
9. 前后端分离
10. excel导出
11. 文件下载的一点心得

项目使用技术：后端使用springboot2.1.0.RELEASE+mybatis2.1.4+druid+derby+poi+jwt+lombok，前端主要使用vue+elementui+markdown

 

目录

1.动态数据源，数据库动态添加，添加后动态切换查询数据

2.自定义缓存，并启动定时任务定时清理缓存，并使用aop自定义拦截查询并放入缓存

3.嵌入式数据库derby

4.多线程异步查询数据，每个线程使用不同的数据源

5.aop获取远程处理结果并进行二次处理

6.服务器访问IP白名单

7.统一异常处理

8.权限控制

9.前后端分离

10.excel导出

11.文件下载的一点心得

---

1.动态数据源，数据库动态添加，添加后动态切换查询数据

这算是本次项目的核心，因为所有其他查询，包括多个数据的查询都要依仗这个数据源的添加，核心是使用mybatis动态数据源AbstractRoutingDataSource,需要自定义实现这个类，这是个抽象类，并且定义为系统的数据源datasource，这个抽象类主要实现一个方法，就是

protected abstract Object determineCurrentLookupKey();

这个方法就是确定是要使用哪个数据源的方法

看源代码：

/**
	     * Retrieve the current target DataSource. Determines the
	 * {@link #determineCurrentLookupKey() current lookup key}, performs
	 * a lookup in the {@link #setTargetDataSources targetDataSources} map,
	 * falls back to the specified
	 * {@link #setDefaultTargetDataSource default target DataSource} if necessary.
	 * @see #determineCurrentLookupKey()
*/
protected DataSource determineTargetDataSource() {
		Assert.notNull(this.resolvedDataSources, "DataSource router not initialized");
		Object lookupKey = determineCurrentLookupKey();
		DataSource dataSource = this.resolvedDataSources.get(lookupKey);
		if (dataSource == null && (this.lenientFallback || lookupKey == null)) {
			dataSource = this.resolvedDefaultDataSource;
		}
		if (dataSource == null) {
			throw new IllegalStateException("Cannot determine target DataSource for lookup key [" + lookupKey + "]");
		}
		return dataSource;
	}

如果根据这个方法找不到数据源的话，就会找默认的数据源

所以我们就需要定义一个默认的数据源，该类完整如下

public class SystemDynamicDataSource extends AbstractRoutingDataSource {
    private Map<Object,Object> dataSourceMap;
    public static final ThreadLocal<Integer> DATA_SOURCE = new ThreadLocal<>();

    private static SystemDynamicDataSource systemDynamicDataSource;

    @Autowired
    public void setSystemDynamicDataSource(SystemDynamicDataSource value) {
        systemDynamicDataSource = value;
    }
    public SystemDynamicDataSource(DataSource dataSource, Map<Object, Object> dataSourceMap){
        this.dataSourceMap = dataSourceMap;
        // 默认使用的数据源
        super.setDefaultTargetDataSource(dataSource);
        // 多个数据源的map 。  master ： jdbc:mysql://182.92.236.164:3306
        //                   salve  ： jdbc:mysql://182.92.236.164:3307
        super.setTargetDataSources(dataSourceMap);
        super.afterPropertiesSet();
    }

    public static void setDataSource(Integer key, DataSource dataSource){
        DruidDataSource oldDataSource = (DruidDataSource) systemDynamicDataSource.dataSourceMap.put(key, dataSource);
        if (oldDataSource != null) {
            oldDataSource.close();
        }
        systemDynamicDataSource.afterPropertiesSet();
    }
    public static void removeDataSource(Integer key){
        DruidDataSource oldDataSource = (DruidDataSource) systemDynamicDataSource.dataSourceMap.remove(key);
        if (oldDataSource != null) {
            oldDataSource.close();
        }
        systemDynamicDataSource.afterPropertiesSet();
    }
    public static boolean isExist(Integer key){
        return systemDynamicDataSource.dataSourceMap.get(key) != null;
    }
    @Override
    protected Object determineCurrentLookupKey() {
        return DATA_SOURCE.get();
    }
    public static void setDataSource(Integer dataSource){
        DATA_SOURCE.set(dataSource);
    }

    public static void removeDataSource(){
        DATA_SOURCE.remove();
    }
}

一般我们使用这种根据key找value的一般会想到用Map，但是最关键的是这个方法没有参数，没有办法把key传过去，所以采用的是ThreadLocal，这样就可以确认当前线程的值了，进而找到数据源。

数据源是可以动态添加的，看源码，当选择数据源的时候，他是从resolvedDataSources这个成员变量里面找的，所以要动态添加数据源，最终是要添加到这个map里面，但是这个变量是私有成员，拿不到这个变量，但是根据是有方法向里面添加的，这个方法就是public void afterPropertiesSet()；它是由targetDataSources这个map的成员变量，将里面有效的值添加到resolvedDataSources里面的，所以我们在实现的时候，不但要设置一下targetDataSources，而且添加的时候必须要手动调用afterPropertiesSet()将值送到resolvedDataSources里面。为了方便之后的动态添加，我将targetDataSources抽取为一个成员变量。

看源码

	public void afterPropertiesSet() {
		if (this.targetDataSources == null) {
			throw new IllegalArgumentException("Property 'targetDataSources' is required");
		}
		this.resolvedDataSources = new HashMap<>(this.targetDataSources.size());
		this.targetDataSources.forEach((key, value) -> {
			Object lookupKey = resolveSpecifiedLookupKey(key);
			DataSource dataSource = resolveSpecifiedDataSource(value);
			this.resolvedDataSources.put(lookupKey, dataSource);
		});
		if (this.defaultTargetDataSource != null) {
			this.resolvedDefaultDataSource = resolveSpecifiedDataSource(this.defaultTargetDataSource);
		}
	}

之后就简单了，在配置类里面声明一下，但是需要将启动类的自动配置DataSourceAutoConfiguration取消掉

看代码：

@Configuration
public class SystemDynamicDataSourceConfig {
    @Bean("systemDataSource")
    @ConfigurationProperties("spring.datasource.druid")// 把配置文件中这个属性下的值获取到，并创建数据源
    public DataSource systemDataSource(){
        DruidDataSource ds = new DruidDataSource();
        return ds;
    }
    @Primary // 主数据源，不加会报错
    @Bean                              //@Qualifier DataSource 多个对象，值是对象的beanName
    public SystemDynamicDataSource dataSource(@Qualifier("systemDataSource") DataSource systemDataSource){
        Map<Object, Object> map = new ConcurrentHashMap<>();
        return new SystemDynamicDataSource(systemDataSource, map);
    }
}

 

配置取消：

@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class})
public class IMWebApplication {
    public static void main(String[] args) {
        SpringApplication.run(IMWebApplication.class,args);
    }
}

数据库的枚举，为了识别是什么数据库添加的

public enum DataTypeEnum {
    /** mysql */
    MYSQL("mysql", "mysql", "com.mysql.cj.jdbc.Driver", "`", "`", "'", "'"),

    /** oracle */
    ORACLE("oracle", "oracle", "oracle.jdbc.OracleDriver", "\"", "\"", "\"", "\"");

    private String feature;
    private String desc;
    private String driver;
    private String keywordPrefix;
    private String keywordSuffix;
    private String aliasPrefix;
    private String aliasSuffix;

    private static final String JDBC_URL_PREFIX = "jdbc:";

    DataTypeEnum(String feature, String desc, String driver, String keywordPrefix, String keywordSuffix, String aliasPrefix, String aliasSuffix) {
        this.feature = feature;
        this.desc = desc;
        this.driver = driver;
        this.keywordPrefix = keywordPrefix;
        this.keywordSuffix = keywordSuffix;
        this.aliasPrefix = aliasPrefix;
        this.aliasSuffix = aliasSuffix;
    }

    public static DataTypeEnum urlOf(String jdbcUrl) {
        String url = jdbcUrl.toLowerCase().trim();
        for (DataTypeEnum dataTypeEnum : values()) {
            if (url.startsWith(JDBC_URL_PREFIX + dataTypeEnum.feature)) {
                try {
                    Class<?> aClass = Class.forName(dataTypeEnum.getDriver());
                    if (null == aClass) {
                        throw new SystemInvalidException("找不到数据库驱动: " + jdbcUrl);
                    }
                } catch (ClassNotFoundException e) {
                    throw new SystemInvalidException("找不到数据库驱动: " + jdbcUrl);
                }
                return dataTypeEnum;
            }
        }
        return null;
    }

    public String getFeature() {
        return feature;
    }

    public String getDesc() {
        return desc;
    }

    public String getDriver() {
        return driver;
    }
}

然后就是数据库的工具类，添加或者测试sql使用

@Slf4j
public class DBUtils {
    /**
     * 获取数据源
     *
     * @param jdbcUrl /
     * @param userName /
     * @param password /
     * @return DataSource
     */
    public static DataSource getDataSource(String jdbcUrl, String userName, String password) {


        return getDataSource(jdbcUrl,userName,password,5,5,10);
    }

    private static DruidDataSource getDataSource(String jdbcUrl, String userName, String password, int minIdle, int initial, int maxActive) {
        DruidDataSource druidDataSource = new DruidDataSource();
        String className;
        try {
            className = DriverManager.getDriver(jdbcUrl.trim()).getClass().getName();
        } catch (SQLException e) {
            throw new SystemInvalidException("未识别的数据库驱动: " + jdbcUrl);
        }
        if (StringUtils.isEmpty(className)) {
            DataTypeEnum dataTypeEnum = DataTypeEnum.urlOf(jdbcUrl);
            if (null == dataTypeEnum) {
                throw new SystemInvalidException("不支持的数据库驱动: jdbcUrl=" + jdbcUrl);
            }
            druidDataSource.setDriverClassName(dataTypeEnum.getDriver());
        } else {
            druidDataSource.setDriverClassName(className);
        }


        druidDataSource.setUrl(jdbcUrl);
        druidDataSource.setUsername(userName);
        druidDataSource.setPassword(password);
        // 配置获取连接等待超时的时间
        druidDataSource.setMaxWait(5000);
        // 配置初始化大小、最小、最大
        druidDataSource.setInitialSize(initial);
        druidDataSource.setMinIdle(minIdle);
        druidDataSource.setMaxActive(maxActive);

        // 如果链接出现异常则直接判定为失败而不是一直重试,重试一次
        druidDataSource.setConnectionErrorRetryAttempts(1);
        druidDataSource.setBreakAfterAcquireFailure(true);
        try {
            druidDataSource.init();
        } catch (SQLException e) {
            druidDataSource.close();
            throw new SystemInvalidException("数据库连接池初始化失败" + e.getMessage(), e);
        }

        return druidDataSource;
    }

    /**
     * 测试连接
     * @param jdbcUrl
     * @param userName
     * @param password
     * @return
     */
    public static boolean testConnection(String jdbcUrl, String userName, String password) {
        DruidDataSource dataSource = getDataSource(jdbcUrl, userName, password,1,1,1);
        Connection connection = null;
        try {
            connection = dataSource.getConnection();
            if (null != connection) {
                return true;
            }
        } catch (Exception e) {
            throw new SystemInvalidException("连接失败: " + e.getMessage());
        } finally {
            try {
                releaseConnection(connection);
            }catch (Exception e) {
                throw new SystemInvalidException("连接失败：" + e.getMessage());
            }
            dataSource.close();
        }
        return false;
    }

    /**
     * 释放连接
     * @param connection
     */
    private static void releaseConnection(Connection connection) throws SQLException {
        if (null != connection) {
            connection.close();
        }
    }

    public static List<SystemDatabase> getSystemDatabaseByCache(){
        return getSystemDatabaseByCache(-1);
    }
    public static List<SystemDatabase> getSystemDatabaseByCache(int type){
        List<SystemDatabase> list = new ArrayList<>();
        ConcurrentHashMap<String, SystemCacheUtils.SystemCache> dbcached = SystemCacheUtils.getCachedMapByName(Dict.SYSTEMDB_CACHE_KEY);
        if(dbcached == null || dbcached.size() == 0){
            return list;
        }
        dbcached.values().forEach(x->{
            SystemDatabase sb = new SystemDatabase();
            BeanUtils.copyProperties(x.getCache(),sb);
            list.add(sb);
        });
        switch (type) {
            case 0 :
                return list.stream().filter(x->x.getIsMDb() == 0).collect(Collectors.toList());
            case 1 :
                return list.stream().filter(x->x.getIsMDb() == 1).collect(Collectors.toList());
            case 2 :
                return list.stream().filter(x->x.getIsMDb() == 2).collect(Collectors.toList());
            default:
                return list;
        }
    }
}

对于代码中多次出现的druidDataSource.close();这个方法，是因为我在看堆内存的时候，发现每次垃圾回收他都不会被回收掉，也就是说他的引用还在，这不太行，于是调用这个方法，不往数据库加的就关掉，这样才会被回收。

 

顺便提一嘴，oracle的连接时urk是有两种写法的，sid格式和ServiceName格式

sid格式就是jdbc:oracle:thin:@<host>:<port>:<SID>

ServiceName格式就是jdbc:oracle:thin:@//<host>:<port>/<service_name>

2.自定义缓存，并启动定时任务定时清理缓存，并使用aop自定义拦截查询并放入缓存

缓存定义了一个工具类，记录一下，这里面没什么好讲的

public class SystemCacheUtils {

    private static final Logger log = LoggerFactory.getLogger(SystemCacheUtils.class);
    private static final int MAX_SIZE = 50000;
    private static ConcurrentHashMap<String,ConcurrentHashMap<String, SystemCache>> cacheData = new ConcurrentHashMap<>();

    private static final String DEFAULT_NAME = "SYSTEM_DEFAULT_NAME";

    public static Collection<ConcurrentHashMap<String, SystemCache>> getAllCaches(){
        return cacheData.values();
    }

    public static void set(String name, String key, Object value, long expiredTime) {
        if(StringUtils.isEmpty(name) || StringUtils.isEmpty(key) || StringUtils.isEmpty(value)) {
            throw new SystemInvalidException("不可以设置空key或者空value");
        }
        ConcurrentHashMap<String, SystemCache> systemSystemCacheConcurrentHashMap = cacheData.get(name);
        if(systemSystemCacheConcurrentHashMap == null){
            systemSystemCacheConcurrentHashMap = new ConcurrentHashMap<>(16);
            cacheData.put(name, systemSystemCacheConcurrentHashMap);
        } else {
            clearCacheMemory(name, systemSystemCacheConcurrentHashMap);
        }
        SystemCache systemCache = new SystemCache();
        systemCache.setCache(value);
        systemCache.setCurrentTime(System.currentTimeMillis());
        systemCache.setExpiredTime(expiredTime <= 0L ? 0L : (System.currentTimeMillis() + expiredTime));
        systemSystemCacheConcurrentHashMap.put(key,systemCache);
    }

    private static void clearCacheMemory(String name, ConcurrentHashMap<String, SystemCache> systemSystemCacheConcurrentHashMap) {
        if(Dict.CACHE_KEY修改.equals(name) || Dict.CACHE_KEY修改.equals(name) || Dict.SYSTEMDB_CACHE_KEY.equals(name)) return;
        if(systemSystemCacheConcurrentHashMap.size()> MAX_SIZE) {
            systemSystemCacheConcurrentHashMap.forEach((key,value)->{
                if (value.isExpired()) {
                    systemSystemCacheConcurrentHashMap.remove(key);
                }
            });
        }
        if(systemSystemCacheConcurrentHashMap.size()> MAX_SIZE) {
            TreeMap<Long,String> treeMap = new TreeMap<>();
            systemSystemCacheConcurrentHashMap.forEach((key, value) -> {
                treeMap.put(value.currentTime,key);
            });
            treeMap.forEach((key, value) -> {
                if(systemSystemCacheConcurrentHashMap.size()> MAX_SIZE) {
                    systemSystemCacheConcurrentHashMap.remove(value);
                }
            });
        }
    }

    public static void set(String key, Object value) {
        set(DEFAULT_NAME, key, value, 0L);
    }
    public static void set(String key, Object value, long expiredTime) {
        set(DEFAULT_NAME, key, value, expiredTime);
    }
    public static void set(String name, String key, Object value) {
        set(name, key, value, 0L);
    }
    public static <T> T get(String name, String key) {
        if(StringUtils.isEmpty(name) || StringUtils.isEmpty(key)) return null;
        ConcurrentHashMap<String, SystemCache> systemSystemCacheConcurrentHashMap = cacheData.get(name);
        if (systemSystemCacheConcurrentHashMap == null) return null;
        SystemCache systemCache = systemSystemCacheConcurrentHashMap.get(key);
        if (systemCache == null) {
            return null;
        }
        if (systemCache.isExpired()) {
            systemSystemCacheConcurrentHashMap.remove(key);
            return null;
        }
        return (T)systemCache.getCache();
    }
    public static <T> T get(String key) {
        return get(DEFAULT_NAME, key);
    }

    public static ConcurrentHashMap<String, SystemCache> getCachedMapByName(String name) {
        return cacheData.get(name);
    }
    public static void remove(String name, String key) {
        ConcurrentHashMap<String, SystemCache> stringSystemCacheConcurrentHashMap = cacheData.get(name);
        if(stringSystemCacheConcurrentHashMap != null) {
            stringSystemCacheConcurrentHashMap.remove(key);
        }
    }
    public static void renew(String name, String key, long renewtime){
        if(StringUtils.isEmpty(name) || StringUtils.isEmpty(key)) return;
        ConcurrentHashMap<String, SystemCache> systemSystemCacheConcurrentHashMap = cacheData.get(name);
        if (systemSystemCacheConcurrentHashMap == null) return;
        SystemCache systemCache = systemSystemCacheConcurrentHashMap.get(key);
        if (systemCache == null) {
            return;
        }
        systemCache.setExpiredTime(System.currentTimeMillis() + renewtime);
    }
    public static void renew(String key, long renewtime){
        renew(DEFAULT_NAME, key, renewtime);
    }
    public static void remove(String key) {
        remove(DEFAULT_NAME, key);
    }
    @Data
    public static class SystemCache{
        private Object cache;
        private long expiredTime;
        private long currentTime;
        public boolean isExpired(){
            if (expiredTime <= 0L) {
                return false;
            }
            return expiredTime - System.currentTimeMillis() <= 0;
        }
    }
}

然后定义一个定时任务，定时清理

@Slf4j
@EnableScheduling
@Configuration
public class SystemCacheConfig {

    @Scheduled(fixedRate=10 * 60 * 1000)
    public void fixedClearExpiredKeys(){
        log.info("-------------开始清理---------------------");
        for (ConcurrentHashMap<String, SystemCacheUtils.SystemCache> cache : SystemCacheUtils.getAllCaches()) {
            if (cache == null) continue;
            cache.forEach((key,value)->{
                if (value.isExpired()) {
                    cache.remove(key);
                    log.info("-------------"+key+" 值 "+value+"被清理---------------------");
                }
            });
        }
        log.info("-------------清理完成---------------------");
    }
}

自定义缓存，是只针对重要数据做缓存

@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
public @interface SystemCacheDelete {
}

@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
public @interface SystemCacheable {
}

@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
public @interface SystemCachePut {
}

切面

@Component
@Aspect
public class SystemCacheAspect {
    /**
     * 配置切入点
     */
    @Pointcut("@annotation(com.ibank.im.app.aop.cache.annotation.SystemCacheable)")
    public void systemCacheable() {
        // 该方法无方法体,主要为了让同类中其他方法使用此切入点
    }
    @Pointcut("@annotation(com.ibank.im.app.aop.cache.annotation.SystemCachePut)")
    public void systemCachePut() {
        // 该方法无方法体,主要为了让同类中其他方法使用此切入点
    }
    @Pointcut("@annotation(com.ibank.im.app.aop.cache.annotation.SystemCacheDelete)")
    public void systemCacheDelete() {
        // 该方法无方法体,主要为了让同类中其他方法使用此切入点
    }
    @Around("systemCacheable()")
    public Object systemCacheableAround(ProceedingJoinPoint joinPoint) throws Throwable {

        Object[] args = joinPoint.getArgs();
        ConcurrentHashMap<String, SystemCacheUtils.SystemCache> cachedMapByName = SystemCacheUtils.getCachedMapByName(Dict.SYSTEMDB_CACHE_KEY);
        if(cachedMapByName == null || cachedMapByName.size() ==0){
            if(args == null || args.length ==0) {
                List<SystemDatabase> proceed = (List<SystemDatabase>) joinPoint.proceed();
                proceed.forEach(db -> {
                    SystemCacheUtils.set(Dict.SYSTEMDB_CACHE_KEY,String.valueOf(db.getSysdbId()),db);
                });
                return proceed;
            }else {
                SystemDatabase sb = (SystemDatabase) joinPoint.proceed(args);
                if(sb != null) {
                    SystemCacheUtils.set(Dict.SYSTEMDB_CACHE_KEY,String.valueOf(sb.getSysdbId()),sb);
                }
                return sb;
            }
        } else {
            if(args == null || args.length ==0) {
                return DBUtils.getSystemDatabaseByCache();
            } else {
                SystemCacheUtils.SystemCache systemCache = cachedMapByName.get(String.valueOf(args[0]));
                if(systemCache == null || systemCache.getCache() == null) {
                    SystemDatabase sb = (SystemDatabase) joinPoint.proceed();
                    if(sb != null) {
                        SystemCacheUtils.set(Dict.SYSTEMDB_CACHE_KEY,String.valueOf(sb.getSysdbId()),sb);
                    }
                    return sb;
                }
                return systemCache.getCache();
            }
        }
    }
    @Around("systemCachePut()")
    public void systemPutAround(ProceedingJoinPoint joinPoint) throws Throwable {
        SystemDatabase proceed = (SystemDatabase) joinPoint.getArgs()[0];
        joinPoint.proceed();
        SystemDatabase o = SystemCacheUtils.get(Dict.SYSTEMDB_CACHE_KEY, String.valueOf(proceed.getSysdbId()));
        if (o != null) {
            for (Field declaredField : o.getClass().getDeclaredFields()) {
                declaredField.setAccessible(true);
                Object o1 = declaredField.get(proceed);
                if (o1 != null) {
                    declaredField.set(o,o1);
                }
            }
            SystemCacheUtils.set(Dict.SYSTEMDB_CACHE_KEY,String.valueOf(proceed.getSysdbId()),o);
        } else {
            SystemCacheUtils.set(Dict.SYSTEMDB_CACHE_KEY,String.valueOf(proceed.getSysdbId()),proceed);
        }

    }
    @Around("systemCacheDelete()")
    public void systemCacheUpdateAround(ProceedingJoinPoint joinPoint) throws Throwable {
        Integer proceed = (Integer) joinPoint.getArgs()[0];
        joinPoint.proceed();
        SystemCacheUtils.remove(Dict.SYSTEMDB_CACHE_KEY, String.valueOf(proceed));
    }
}

最后，重要数据在启动的时候就入缓存

@Slf4j
@Component
public class SystemDatabaseJobRunner implements ApplicationRunner {

    @Resource
    private SystemDataBaseMapper systemDataBaseMapper;

    @Autowired
    private TaskJobExcutorService taskJobExcutorService;


    @Override
    public void run(ApplicationArguments args) throws Exception {
        log.info("-------------------- 开始注入数据库---------------------");
        List<SystemDatabase> systemDatabase = systemDataBaseMapper.getSystemDatabase();
        if(systemDatabase == null || systemDatabase.size() ==0) {
            log.warn("------------------未配置任何数据库，需要先增加数据库---------------");
        }else {
            systemDatabase.forEach(x -> {
                try {
                    SystemDynamicDataSource.setDataSource(x.getSysdbId(), DBUtils.getDataSource(x.getSysdbAddr(),x.getSysdbUser(),x.getSysdbPwd()));
                }catch (Exception e){
                    log.error("数据库注入失败：",e);
                }

            });
            log.info("--------------------数据库注入完成---------------------");
        }
        List<SystemDatabase> systemDatabaseByCache = DBUtils.getSystemDatabaseByCache(1);
        if(systemDatabaseByCache.size() == 0) {
            log.warn("------------------未配置主库，需要先增加数据库---------------");
        } else {
            systemDatabaseByCache.forEach(x -> {
                taskJobExcutorService.refresh修改Cache(x);
                taskJobExcutorService.refresh修改Cache(x);
            });
        }
    }
}

3.嵌入式数据库derby

使用derby数据库的好处是不用安装，因为一些原因必须要使用嵌入式数据库。

maven引用

<dependency>
   <groupId>org.apache.derby</groupId>
   <artifactId>derby</artifactId>
   <version>10.13.1.1</version>
</dependency>

展示一些连接参数

spring:
  datasource:
    druid:
      db-type: com.alibaba.druid.pool.DruidDataSource
      url: jdbc:derby:datas/imappdb;create=true
      driver-class-name: org.apache.derby.jdbc.EmbeddedDriver
      username: aaa
      password: aaaaaa
      # 初始连接数
      initial-size: 5
      # 最小连接数
      min-idle: 5
      # 最大连接数
      max-active: 10
      # 获取连接超时时间
      max-wait: 5000
      # 连接有效性检测时间
      time-between-eviction-runs-millis: 60000
      # 连接在池中最小生存的时间
      min-evictable-idle-time-millis: 300000
      # 连接在池中最大生存的时间
      max-evictable-idle-time-millis: 900000
      test-while-idle: true
      test-on-borrow: false
      test-on-return: false
      # 检测连接是否有效
      validation-query: values 1
      query-timeout: 1

参数一些解析，url就是jdbc:derby:开头，后面是数据库，我是前面加了一层文件夹，;create=true这个参数是说数据是要持久化的，不然只存在内存里面。项目启动会自动创建这个数据库，用户名和密码就是你设置的。检测连接是否有效的参数是values 1。

举一个创建表的例子

create table SystemDatabase(
  sysdbId INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1) primary key,
	sysdbName VARCHAR(50) not null,
	sysdbAddr VARCHAR(500) not null,
	sysdbUser VARCHAR(20) not null,
	sysdbPwd VARCHAR(50) not null,
	isMDb INTEGER not null default 0 --数据库u类型
);

而且使用mybatis插入回显主键值的时候，使用的是

   <insert id="insertSystemDatabase" parameterType="com.ibank.im.app.entity.db.SystemDatabase">
      <selectKey keyColumn="sysdbId" keyProperty="sysdbId" resultType="int" order="AFTER">
          VALUES IDENTITY_VAL_LOCAL()
      </selectKey>
      insert into SystemDatabase(sysdbName,sysdbAddr,sysdbUser,sysdbPwd,isMDb) values(#{sysdbName},#{sysdbAddr},#{sysdbUser},#{sysdbPwd},#{isMDb})
    </insert>

顺便提一嘴，myabtis可以使用懒加载，result节点上有一个select，这个可以设置为另一个select的id，可以不用写在Mapper接口上

   <resultMap id="resultIMDealScript" type="com.ibank.im.app.entity.imdeal.IMDealScript">
        <id property="imSptId" column="imSptId"></id>
        <result property="imSptSQL" column="imSptSQL"></result>
        <result property="imSptParams" column="imSptParams"></result>
        <result property="imSptRelationId" column="imSptRelationId"></result>
        <result property="imSptRelationParams" column="imSptRelationParams"></result>
        <result property="imdlId" column="imdlId"></result>
        <association property="relationScript" column="imSptRelationId" select="getRelationScript"></association>
        <collection property="systemDatabaseList" column="imSptId" select="getRelationSystemDatabaseList"></collection>
    </resultMap>

4.多线程异步查询数据，每个线程使用不同的数据源

这个承接的是1，最主要的就是一个配置和一个类

一个配置：

@Configuration
@EnableAsync
public class AsyncThreadTaskConfig {

    @Primary
    @Bean
    public ThreadPoolTaskExecutor taskExecutor() {
        ThreadPoolTaskExecutor executor = new ThreadPoolTaskExecutor();
        //核心线程池大小
        executor.setCorePoolSize(32);
        //最大线程数
        executor.setMaxPoolSize(50);
        //队列容量
        executor.setQueueCapacity(200);
        //活跃时间
        executor.setKeepAliveSeconds(60);
        //线程名字前缀
        executor.setThreadNamePrefix("异步任务-");
        // setRejectedExecutionHandler：当pool已经达到max size的时候，如何处理新任务
        // CallerRunsPolicy：不在新线程中执行任务，而是由调用者所在的线程来执行
        executor.setRejectedExecutionHandler(new ThreadPoolExecutor.CallerRunsPolicy());
        executor.initialize();
        return executor;
    }
}

 

一个类或者异步方法上主要有个注解即可@Async，如果不需要返回值，void即可生效，如果有返回值，返回值需要包裹在Future<>里面，返回的时候用方法

return AsyncResult.forValue(remoteDBQueryMapper.queryRemoteQueryLog(params));

获取返回值

接口定义

 Future<List<Map>> queryRemoteData(SystemDatabase systemDatabase, Map map);

5.aop获取远程处理结果并进行二次处理

为什么要进行二次处理，因为在连接oracle的时候，因为所有结果默认（远程查询数据的时候是不知道什么字段以及什么类型的，所以采用了不处理，一切默认），但是在日期字段的时候出现了问题，查询oracle的时候，mybatis默认使用的是oracle.sql.TIMESTAMP，也没啥问题，能展示就行，但是我发现传到前端的时候老是没法序列化成json，我就进行了二次处理。而且字段也需要小写处理，因为后续牵扯数据的处理

注解：

@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ResultHandle {
}

切面

public class ResultHandlerAspect {
    /**
     * 配置切入点
     */
    @Pointcut("@annotation(com.ibank.im.app.aop.lower.annotation.ResultHandle)")
    public void resultHandler() {
        // 该方法无方法体,主要为了让同类中其他方法使用此切入点
    }
    @Around("resultHandler()")
    public List<Map<String,String>> systemCacheableAround(ProceedingJoinPoint joinPoint) throws Throwable {
        List<Map> result = (List<Map>) joinPoint.proceed();
        List<Map<String,String>> list = new ArrayList<>();
        for (Map map : result) {
            Map<String,String> data = new HashMap();
            map.forEach((key, value) -> {
                if(value != null) {
                    if(value instanceof oracle.sql.TIMESTAMP) {
                        try {
                            data.put(((String)key).toLowerCase(), ((oracle.sql.TIMESTAMP)value).timestampValue().toLocalDateTime().format(DateTimeFormatter.ISO_DATE_TIME));
                        } catch (SQLException e) {
                            throw new SystemInvalidException("日期格式转换错误",e);
                        }
                    } else if(value instanceof Timestamp) {
                        data.put(((String)key).toLowerCase(), ((Timestamp)value).toLocalDateTime().format(DateTimeFormatter.ISO_DATE));
                    } else if(value instanceof BigDecimal) {
                        data.put(((String)key).toLowerCase(), ((BigDecimal)value).toString());
                    } else {
                        data.put(((String)key).toLowerCase(), String.valueOf(value));
                    }
                }
            });
            list.add(data);
        }
        return list;
    }
}

6.服务器访问IP白名单

这个只能在启动的时候设置，ip例如allow = "127.0.0.1|192.168.111.13|192.168.111.132";deny = "192.168.111.132|192.168.111.135|192.168.111.134";tomcat设计应该是责任链模式，执行是从一个个管道Pipe执行，每个Pipe里面都有一个Valve，RemoteAddrValve 就是负责访问的valve

 

@Configuration
public class TomcatConfig {

    @Value("${imserver.allowIP}")
    private String allowIP;
    @Value("${imserver.denyIP}")
    private String denyIP;

    @Value("${imserver.port}")
    private int serverPort;

    @Bean
    public TomcatServletWebServerFactory servletContainer(){
        TomcatServletWebServerFactory tomcat = new TomcatServletWebServerFactory ();
        tomcat.setPort(serverPort);
        if((!CommonUtils.isNullOrEmpty(allowIP) && !"*".equals(allowIP))|| (!CommonUtils.isNullOrEmpty(denyIP) && !"*".equals(denyIP))) {
            RemoteAddrValve remoteAddrValve = new RemoteAddrValve();
            if(!CommonUtils.isNullOrEmpty(allowIP) && !"*".equals(allowIP)) {
                remoteAddrValve.setAllow(allowIP);
            }
            if(!CommonUtils.isNullOrEmpty(denyIP) && !"*".equals(denyIP)) {
                remoteAddrValve.setDeny(denyIP);
            }
            tomcat.addContextValves(remoteAddrValve);
        }
        return tomcat;

    }
}

7.统一异常处理

核心就俩注解，@RestControllerAdvice和@ExceptionHandler

代码

@RestControllerAdvice
public class SystemExceptionHandler {

    @ExceptionHandler(Exception.class)
    @ResponseBody
    public ResponseEntity handleException(Exception e) throws Exception {
        if (e instanceof AccessDeniedException || e instanceof AuthenticationException) {
            throw e;
        }
        if(e instanceof SystemInvalidException){
            return ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR).body(e.getMessage());
        }
        return ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR).body("系统内部错误");
    }
}

为什么要做异常的抛出，因为这部分要配合前端做权限控制的，如下。 

8.权限控制

这一部分代码是借鉴了eladmin的代码。使用jwt进行鉴权。有兴趣的可以去eladmin看下源码

@Configuration
@EnableGlobalMethodSecurity(prePostEnabled = true, securedEnabled = true)
public class SystemSecurityConfig extends WebSecurityConfigurerAdapter {

    @Autowired
    private JwtAuthenticationEntryPoint authenticationErrorHandler;
    @Autowired
    private JwtAccessDeniedHandler jwtAccessDeniedHandler;
    @Autowired
    private TokenConfigurer tokenConfigurer;
    @Bean
    GrantedAuthorityDefaults grantedAuthorityDefaults() {
        // 去除 ROLE_ 前缀
        return new GrantedAuthorityDefaults("");
    }

    @Bean
    public PasswordEncoder passwordEncoder() {
        // 密码加密方式
        return new BCryptPasswordEncoder();
    }
    @Override
    protected void configure(HttpSecurity httpSecurity) throws Exception {
        httpSecurity
                // 禁用 CSRF
                .csrf().disable()
                // 授权异常
                .exceptionHandling()
                .authenticationEntryPoint(authenticationErrorHandler)
                .accessDeniedHandler(jwtAccessDeniedHandler)
                // 防止iframe 造成跨域
                .and()
                .headers()
                .frameOptions()
                .disable()
                // 不创建会话
                .and()
                .sessionManagement()
                .sessionCreationPolicy(SessionCreationPolicy.STATELESS)
                .and()
                .authorizeRequests()
                // 静态资源等等
                .antMatchers(
                        "/**/*.ico",
                        "/**/*.svg",
                        "/**/*.gif",
                        "/**/*.html",
                        "/**/*.css",
                        "/**/*.js",
                        "/**/*.png",
                        "/**/*.jpg",
                        "/**/*.woff",
                        "/**/*.woff2",
                        "/**/*.ttf",
                        "/**/*.eot"
                ).permitAll()
                // 放行OPTIONS请求
                .antMatchers(HttpMethod.OPTIONS, "/**").permitAll()
                // 自定义匿名访问所有url放行：允许匿名和带Token访问，细腻化到每个 Request 类型
                // GET
                .antMatchers("/auth/*","/imdeal/getimg").permitAll()
                // 所有请求都需要认证
                .anyRequest().authenticated()
                .and().apply(tokenConfigurer);
    }
}

@Component
public class TokenConfigurer extends SecurityConfigurerAdapter<DefaultSecurityFilterChain, HttpSecurity> {

    @Autowired
    private TokenFilter customFilter;

    @Override
    public void configure(HttpSecurity http) {
        http.addFilterBefore(customFilter, UsernamePasswordAuthenticationFilter.class);
    }
}

@Component
public class JwtAccessDeniedHandler implements AccessDeniedHandler {

    @Override
    public void handle(HttpServletRequest request, HttpServletResponse response, AccessDeniedException accessDeniedException) throws IOException {
        //当用户在没有授权的情况下访问受保护的REST资源时，将调用此方法发送403 Forbidden响应
        response.sendError(HttpServletResponse.SC_FORBIDDEN, accessDeniedException.getMessage());
    }
}

 

@Component
public class JwtAuthenticationEntryPoint implements AuthenticationEntryPoint {

    @Override
    public void commence(HttpServletRequest request,
                         HttpServletResponse response,
                         AuthenticationException authException) throws IOException {
        // 当用户尝试访问安全的REST资源而不提供任何凭据时，将调用此方法发送401 响应
        response.sendError(HttpServletResponse.SC_UNAUTHORIZED, authException==null?"Unauthorized":authException.getMessage());
    }
}

 

@Component
public class TokenFilter extends GenericFilterBean {
    private static final Logger log = LoggerFactory.getLogger(TokenFilter.class);


    @Autowired
    private TokenProvider tokenProvider;
    @Autowired
    private SystemSecurityProperties properties;

    @Override
    public void doFilter(ServletRequest servletRequest, ServletResponse servletResponse, FilterChain filterChain)
            throws IOException, ServletException {
        HttpServletRequest httpServletRequest = (HttpServletRequest) servletRequest;
        String token = SecurityUtils.resolveToken(httpServletRequest, properties);
        // 对于 Token 为空的不需要去查 Redis
        if (!CommonUtils.isNullOrEmpty(token)) {
            OnlineUser onlineUser = SystemCacheUtils.get(properties.getOnlineKey() + token);
            if (onlineUser != null && StringUtils.hasText(token)) {
                SecurityContextHolder.getContext().setAuthentication(new UsernamePasswordAuthenticationToken(onlineUser, token, onlineUser.getAuthorities()));
                // Token 续期
                tokenProvider.checkRenewal(token);
            }
        }
        filterChain.doFilter(servletRequest, servletResponse);
    }

}

@Component
public class TokenProvider implements InitializingBean {

    private final String AUTHORITIES_KEY = "system-auth";
    private JwtParser jwtParser;
    private JwtBuilder jwtBuilder;
    @Autowired
    private SystemSecurityProperties properties;

    @Override
    public void afterPropertiesSet() {
        byte[] keyBytes = Decoders.BASE64.decode(properties.getBase64Secret());
        Key key = Keys.hmacShaKeyFor(keyBytes);
        jwtParser = Jwts.parserBuilder()
                .setSigningKey(key)
                .build();
        jwtBuilder = Jwts.builder()
                .signWith(key, SignatureAlgorithm.HS512);
    }

    /**
     * 创建Token 设置永不过期，
     * Token 的时间有效性转到Redis 维护
     *
     * @param authentication /
     * @return /
     */
    public String createToken(Authentication authentication) {
        /*
         * 获取权限列表
         */
        String authorities = authentication.getAuthorities().stream()
                .map(GrantedAuthority::getAuthority)
                .collect(Collectors.joining(","));

        return jwtBuilder
                // 加入ID确保生成的 Token 都不一致
                .setId(CommonUtils.randomUUID())
                .claim(AUTHORITIES_KEY, authorities)
                .setSubject(authentication.getName())
                .compact();
    }

    /**
     * @param token 需要检查的token
     */
    public void checkRenewal(String token) {
        Object o = SystemCacheUtils.get(properties.getOnlineKey() + token);
        if (o !=null ){
            SystemCacheUtils.renew(properties.getOnlineKey() + token, properties.getRenew());
        }
    }
}

@Service(value = "imsysel")
public class IMSysElPermissionConfig {
    public Boolean check(String ...permissions){
        final OnlineUser authentication = getCurrentUser();
        if(authentication.getUser().getIsAdmin()) {
            return true;
        }
        Set<String> permissons = authentication.getAuthorities().stream().map(GrantedAuthority::getAuthority).collect(Collectors.toSet());
        return Arrays.stream(permissions).anyMatch(permissons::contains);
    }
    /**
     * 获取系统用户名称
     *
     * @return 系统用户名称
     */
    private OnlineUser getCurrentUser() {
        final Authentication authentication = SecurityContextHolder.getContext().getAuthentication();
        if (authentication == null) {
            throw new SystemInvalidException("当前登录状态过期");
        }
        OnlineUser principal = (OnlineUser) authentication.getPrincipal();
        if(principal != null) {
            return principal;
        }
        throw new SystemInvalidException("找不到当前登录的信息");
    }
}

这里稍微解析一下，@EnableGlobalMethodSecurity这个方法开启方法注解，全局配置还是得继承WebSecurityConfigurerAdapter，授权异常之类的感觉不是很需要，统一异常处理感觉可以处理，这地感觉可以优化。

SecurityContextHolder.getContext().setAuthentication(new UsernamePasswordAuthenticationToken(onlineUser, token, onlineUser.getAuthorities()));

这段代码就是用来认证的，所有请求都要重新认证 。

IMSysElPermissionConfig是用来在方法上校验权限的，因为已经有个这个UsernamePasswordAuthenticationToken，所以是认证过的，但是权限需要校验，核心注解也就是

@PreAuthorize("@imsysel.check('sysrole')")

登录代码如下

@RestController
@RequestMapping("/auth")
public class AuthorizationController {

    @Autowired
    private SystemSecurityProperties properties;

    @Autowired
    private SystemUserAndRoleService systemUserAndRoleService;

    @Autowired
    private OnlineUserService onlineUserService;
    @Autowired
    private TokenProvider tokenProvider;
    @Autowired
    private AuthenticationManagerBuilder authenticationManagerBuilder;

    @GetMapping("/code")
    public ResponseEntity getCode() {
        return ResponseEntity.ok(ImageUtils.getBase64Images());
    }
    @PostMapping("/login")
    public ResponseEntity login(@RequestBody AuthUserVo authUser, HttpServletRequest request) {
        // 密码解密
        String password = null;
        try {
            password = RsaUtils.decryptByPrivateKey(RsaProperties.privateKey, authUser.getUserPwd());
        } catch (Exception e) {
            throw new SystemInvalidException("密码解密异常", e);
        }
        // 查询验证码
        String code = SystemCacheUtils.get(Dict.TOKEN_IMAGE,authUser.getUuid());
        // 清除验证码
        SystemCacheUtils.remove(Dict.TOKEN_IMAGE,authUser.getUuid());
        if (CommonUtils.isNullOrEmpty(code)) {
            throw new SystemInvalidException("验证码不存在或已过期");
        }
        if (CommonUtils.isNullOrEmpty(authUser.getToken()) || !authUser.getToken().equalsIgnoreCase(code)) {
            throw new SystemInvalidException("验证码错误");
        }
        UsernamePasswordAuthenticationToken authenticationToken =
                new UsernamePasswordAuthenticationToken(authUser.getUserId(), password);
        Authentication authentication = null;
        try {
            authentication = authenticationManagerBuilder.getObject().authenticate(authenticationToken);
        } catch (Exception e) {
            throw new SystemInvalidException("用户名或密码错误",e);
        }

        SecurityContextHolder.getContext().setAuthentication(authentication);
        // 生成令牌
        String token = tokenProvider.createToken(authentication);
        final OnlineUser onlineUser = (OnlineUser) authentication.getPrincipal();
        // 保存在线信息
        onlineUserService.save(onlineUser, token, request);
        onlineUserService.checkLoginOnUser(authUser.getUserId(), token);
        return ResponseEntity.ok(properties.getTokenStartWith() + token);
    }
}

9.前后端分离

前后端分离主要是vue+elementui，项目借鉴了eladmin，前端也没什么好说的，主要是封装request：

import axios from 'axios'
import router from '@/router'
import { Notification } from 'element-ui'
// import store from '../store'
import { getToken, removeToken } from '@/utils/auth'
import Config from '@/settings'
// import Cookies from 'js-cookie'

// 创建axios实例
const service = axios.create({
  baseURL: process.env.NODE_ENV === 'production' ? process.env.VUE_APP_BASE_API : '/', // api 的 base_url
  timeout: Config.timeout // 请求超时时间
})

// request拦截器
service.interceptors.request.use(
  config => {
    if (getToken()) {
      config.headers.IMWebAuthorization = getToken() // 让每个请求携带自定义token 请根据实际情况自行修改
    }
    config.headers['Content-Type'] = 'application/json'
    return config
  },
  error => {
    Promise.reject(error)
  }
)

// response 拦截器
service.interceptors.response.use(
  response => {
    if (response.data instanceof Blob) {
      return response
    }
    return response.data
  },
  error => {
    if (error.toString().indexOf('Error: timeout') !== -1) {
      Notification.error({
        title: '网络请求超时',
        duration: 300000
      })
      return Promise.reject(error)
    }
    // 兼容blob下载出错json提示
    if (error.response.data instanceof Blob) {
      const reader = new FileReader()
      reader.readAsText(error.response.data, 'utf-8')
      reader.onload = function (e) {
        const errorMsg = reader.result
        Notification.error({
          title: errorMsg,
          duration: 3000
        })
      }
    } else {
      let code = 0
      try {
        code = error.response.status
      } catch (e) {
        if (error.toString().indexOf('Error: timeout') !== -1) {
          Notification.error({
            title: '网络请求超时',
            duration: 3000
          })
          return Promise.reject(error)
        }
      }
      if (code) {
        if (code === 401) {
          Notification.warning({
            title: '登录超时',
            duration: 3000,
            onClose: () => {
              removeToken()
              location.reload()
            }
          })
        } else if (code === 403) {
          router.push({ path: '/401' })
        } else if (code === 500) {
          const errorMsg = error.response.data
          if (errorMsg !== undefined) {
            Notification.error({
              title: errorMsg,
              duration: 3000
            })
          } else {
            Notification.error({
              title: '未知错误',
              duration: 3000
            })
          }
        } else {
          const errorMsg = error.response.data.message
          if (errorMsg !== undefined) {
            Notification.error({
              title: errorMsg,
              duration: 5000
            })
          } else {
            Notification.error({
              title: '未知错误',
              duration: 3000
            })
          }
        }
      } else {
        Notification.error({
          title: '远程服务器断开连接',
          duration: 3000
        })
      }
    }
    return Promise.reject(error)
  }
)
export default service

封装文件下载

export default function filedownload (res) {
  const filename = res.headers['content-disposition']
  const blob = new Blob([res.data])
  var downloadElement = document.createElement('a')
  var href = window.URL.createObjectURL(blob)
  downloadElement.href = href
  let finalfilename = filename.split('filename=')[1]
  if (finalfilename.startsWith('"')) {
    finalfilename = finalfilename.substring(1)
  }
  if (finalfilename.endsWith('"')) {
    finalfilename = finalfilename.substring(0, finalfilename.length - 1)
  }
  downloadElement.download = decodeURIComponent(finalfilename)
  document.body.appendChild(downloadElement)
  downloadElement.click()
  document.body.removeChild(downloadElement)
  window.URL.revokeObjectURL(href)
}

axios文件下载的请求

request({ url: '/querylog/downloadlog', method: 'post', responseType: 'blob', data: this.logForm }).then(res => {
        filedownload(res)
        this.downloading = false
        this.$message({
          message: '查询成功',
          type: 'success'
        })
      }).catch(res => {
        this.downloading = false
      })

以及上传下载的请求

const param = new FormData() // 创建form对象
      param.append('file', params.file) // 通过append向form对象添加数据
      param.append('yyzcSumId', this.singleGenFormData.genCurrentYYZCSumId)
      const config = {
        headers: { 'Content-Type': 'multipart/form-data' }
      }
      request({ url: '/yyzc/batchGen', method: 'post', responseType: 'blob', data: param, config }).then(res => {
        this.$message({
          message: '生成成功',
          type: 'success'
        })
        filedownload(res)
        this.batchGenVisble = false
        this.$refs.batchupload.clearFiles()
      })

 

在写代码的时候，发现一个非常奇怪的问题，就是双向绑定的时候，进行替换值的时候，直接用=替换数组没有更改，可能当时添加的监视还在，没有深究，但是用splice进行替换，数据就可以双向绑定了。

非常重要：双向绑定前端的时候，一定要对数据进行分页或者懒加载，不然上万条数据在创建dom直接能给浏览器搞奔溃

10.excel导出

这个是借鉴的博客https://blog.csdn.net/haibo_bear/article/details/89921608。

解析：

InputStream inputStream = file.getInputStream();
            Workbook wb = null;
            if (file.getOriginalFilename().endsWith(".xls") ||file.getOriginalFilename().endsWith(".XLS")) {
                wb = new HSSFWorkbook(inputStream);
            } else if (file.getOriginalFilename().endsWith(".xlsx") ||file.getOriginalFilename().endsWith(".XLSX")) {
                wb = new XSSFWorkbook(inputStream);
            } else {

            }
            Sheet sheet = wb.getSheetAt(0);
            // 得到总行数
            int rowNum = sheet.getLastRowNum();
            if(rowNum < 1 ) {
                throw new SystemInvalidException("缺少解析数据");
            }
            Row row = sheet.getRow(0);
            int colNum = row.getPhysicalNumberOfCells();
            Map<Integer,String> paramMapIndex = new HashMap<>();
            for (int i = 0; i < colNum; i++) {
                String cellFormatValue = getCellFormatValue(row.getCell(i)).trim();
                if(paramSet.contains(cellFormatValue)) {
                    paramMapIndex.put(i,cellFormatValue);
                }
            }
            if(paramMapIndex.size() != paramSet.size()) {
                throw new SystemInvalidException("缺少参数");
            }
            Map<String,List<String>> headerMapData = new HashMap<>();
            // 正文内容应该从第二行开始
            for (int i = 1; i <= rowNum; i++) {
                row = sheet.getRow(i);
                int j = 0;
//                Map<Integer, String> cellValue = new HashMap<Integer, Object>();
                int countEmptyCell = 0;
                while (j < colNum) {
                    String obj = getCellFormatValue(row.getCell(j));
                    List<String> list = headerMapData.get(paramMapIndex.get(i));
                    if(list == null) {
                        list = new ArrayList<>();
                    }
                    if("".equals(obj.trim())){
                        countEmptyCell++;
                    }else{
                        list.add(obj.trim());
                        headerMapData.put(paramMapIndex.get(j), list);
                    }
                    j++;
                }
                //全是空行，结束
                if(countEmptyCell == colNum) {
                    break;
                }
            }

生成：

public byte[] downLoadExcelWithRjnl(QueryData queryData) {
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet("select mcjnlquerylog");
        //新增数据行，并且设置单元格数据

        int rowNum = 1;

        String[] headers = {修改};
        //headers表示excel表中第一行的表头

        XSSFRow row = sheet.createRow(0);
        //在excel表中添加表头

        for(int i=0;i<headers.length;i++){
            XSSFCell cell = row.createCell(i);
            XSSFRichTextString text = new XSSFRichTextString(headers[i]);
            cell.setCellValue(text);
        }

        //在表中存放查询到的数据放入对应的列
        for (Map map : queryData.getQueryList()) {
            XSSFRow row1 = sheet.createRow(rowNum);
            row1.createCell(0).setCellValue((String) map.get(修改));
           
            List<Map> rjnl = (List<Map>) map.get(Dict.RJNL_RESULT_KEY);
            if(rjnl != null && rjnl.size() > 0) {
                int currentRownum = rowNum;
                for (Map map1 : rjnl) {
                    XSSFRow row2 = null;
                    if(currentRownum < rowNum) {
                        row2 = sheet.createRow(rowNum);
                    } else {
                        row2 = row1;
                    }
                    row2.createCell(38).setCellValue((String) map1.get(修改));
                   
                    rowNum++;
                }
                for (int i = 0; i < 38; i++) {
                    CellRangeAddress region = new CellRangeAddress(currentRownum, rowNum-1, i, i);
                    sheet.addMergedRegion(region);//合并单元格
                }
            } else {
                rowNum++;
            }
        }
        if(!CommonUtils.isNullOrEmpty(queryData.getErrormsg())) {
            XSSFRow errwrrow = sheet.createRow(rowNum);
            XSSFCell cell = errwrrow.createCell(0);
            cell.setCellValue(queryData.getErrormsg());
            CellRangeAddress region = new CellRangeAddress(rowNum, rowNum, 0, 48);
            sheet.addMergedRegion(region);
        }
        ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
        try {
            workbook.write(byteArrayOutputStream);
            return byteArrayOutputStream.toByteArray();
        } catch (IOException e) {
            throw new SystemInvalidException("文件写入错误",e);
        }finally {
            try {
                workbook.close();
            } catch (IOException e) {
                throw new SystemInvalidException("文件流关闭失败",e);
            }
        }
    }

11.文件下载的一点心得

 

文件下载可以使用两种方式，使用response输出流，或者使用ResponseEntity<byte[]>

第一种，就是从response里面获取流进行写入，然后设置响应头

public void singleGenYYZC(@RequestBody Map<String,String> requestMap, HttpServletResponse response){
        YYZCSummary yyzcSummary = yyzcService.getYYZCSummaryFromRequestMap(requestMap);
        OutputStream outputStream = null;
        try {
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(yyzcSummary.getYyzcSumName()+"_"+System.currentTimeMillis()+".zip", "UTF-8"));  // 需要编码否则中文乱码
            response.setContentType("application/zip;charset=utf-8");
            response.setCharacterEncoding("UTF-8");
            outputStream = response.getOutputStream();
            yyzcService.singleGenYYZC(requestMap,outputStream,yyzcSummary);
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            if(outputStream!=null){
                try {
                    outputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

个人倾向第二种，使用ByteArrayOutputStream，将数据写入后，获取byte。

public ResponseEntity<byte[]> batchGenYYZC(MultipartFile file, String yyzcSumId){
        YYZCSummary yyzcSummary = yyzcService.getYYZCSummaryFromYYZCSumId(yyzcSumId);
        byte[] bytes = yyzcService.batchGenYYZC(file,yyzcSummary);
        try {
            HttpHeaders header = new HttpHeaders();
            header.add("Content-Disposition","attachment;filename=" + URLEncoder.encode(yyzcSummary.getYyzcSumName()+"_"+System.currentTimeMillis()+".zip" ,"UTF-8"));
            header.setContentType(MediaType.valueOf("application/zip;charset=utf-8"));
            return new ResponseEntity<byte[]>(bytes,header, HttpStatus.CREATED);
        } catch (UnsupportedEncodingException e) {
            throw new SystemInvalidException("文件名编码失败",e);
        }

    }

附上一段生成zip压缩文件的代码

private byte[] generateZipYYZCFile(List<YYZCData> yyzcDataList, Map<String, List<String>> headerMapData, String yyzcSumSteps) {
        ZipOutputStream zipOutputStream = null;
        ByteArrayOutputStream byteArrayOutputStream = null;
        checkEverySQLParamCount(yyzcDataList,headerMapData);
        try {
            Map<String, Map<Integer, List<YYZCData>>> dbMapTypeMapData = yyzcDataList.stream().collect(Collectors.groupingBy(YYZCData::getYyzcDtDb, Collectors.groupingBy(YYZCData::getYyzcDtType)));
            byteArrayOutputStream = new ByteArrayOutputStream();
            zipOutputStream = new ZipOutputStream(byteArrayOutputStream);
            for (Map.Entry<String, Map<Integer, List<YYZCData>>> mapTypeMapDataEntry : dbMapTypeMapData.entrySet()) {
                Map<Integer, List<YYZCData>> mapDataEntryData = mapTypeMapDataEntry.getValue();
                String dbName = mapTypeMapDataEntry.getKey();
                for (Map.Entry<Integer, List<YYZCData>> dataListEntry : mapDataEntryData.entrySet()) {
                    Integer dbType = dataListEntry.getKey();
                    ZipEntry zipEntry = new ZipEntry(dbName + intMapType.get(dbType) + ".sql");
                    zipOutputStream.putNextEntry(zipEntry);
                    List<YYZCData> sqlList = dataListEntry.getValue();
                    sqlList.sort((x, y) -> x.getYyzcDtOrder() - y.getYyzcDtOrder());
                    for (YYZCData yyzcData : sqlList) {
                        String yyzcDtParams = yyzcData.getYyzcDtParams();
                        String yyzcDtSql = yyzcData.getYyzcDtSql();
                        if (StringUtils.isEmpty(yyzcDtParams)) {
                            zipOutputStream.write(yyzcDtSql.getBytes());
                            continue;
                        }
                        String[] splitParams = yyzcDtParams.split(",");
                        int size = headerMapData.get(splitParams[0]).size();
                        for (int i = 0; i < size; i++) {
                            String tmp = yyzcDtSql;
                            for (String param : splitParams) {
                                String data = headerMapData.get(param).get(i);
                                tmp = tmp.replaceAll("#\\{" + param + "\\}", data);

                            }
                            tmp = tmp + "\r\n";
                            zipOutputStream.write(tmp.getBytes("UTF-8"));
                        }

                        zipOutputStream.write("\r\n".getBytes("UTF-8"));
                    }
                    zipOutputStream.closeEntry();
                }
            }
            if (!StringUtils.isEmpty(yyzcSumSteps)) {
                ZipEntry zipEntry = new ZipEntry("文件.txt");
                zipOutputStream.putNextEntry(zipEntry);
                zipOutputStream.write(yyzcSumSteps.getBytes());
                zipOutputStream.closeEntry();
            }
            zipOutputStream.flush();
            zipOutputStream.finish();// bug，不加这个byte会少数据
            byteArrayOutputStream.flush();
            return byteArrayOutputStream.toByteArray();
        } catch (IOException e) {
            throw new SystemInvalidException("文件生成失败",e);
        }finally {
            if (byteArrayOutputStream != null) {
                try {
                    byteArrayOutputStream.close();
                } catch (IOException e) {
                    throw new SystemInvalidException("文件关闭失败",e);
                }
            }
            if (zipOutputStream != null) {
                try {
                    zipOutputStream.close();
                } catch (IOException e) {
                    throw new SystemInvalidException("文件关闭失败",e);
                }
            }
        }
    }

主要是

new ZipOutputStream(byteArrayOutputStream);

包含这个二进制流，然后压缩文件里面的每一个文件，就是一个ZipEntry，put然后写入数据然后close，最后一定要加入这个

zipOutputStream.finish();

不然在前端生成的文件根本打不开，流没有写入完成。

最后，解决了一个bug，就是将文件名传入前端的时候，文件名如果是中文，就会乱码，因为我们需要后台对文件名进行编码的，使用

URLEncoder.encode(yyzcSummary.getYyzcSumName()+"_"+System.currentTimeMillis()+".zip" ,"UTF-8")

即可，文件下载见9.前后端分离

 

 

 

写在最后，因为从需求到前端到后端到测试，全是自己一个人，bug其实着实多。gitee上有代码，但是暂时没有开放。

只有自己去写项目的时候才发现自己啥也不会，技术太low了，纯粹面向百度开发，差的太远了，以后要加紧学习了。