XSSF模板导出excel和多数据源查询以及sql查询速度优化

前两天接到任务，要统计数据后然后导出excel，对方给了模板，按照模板格式要求导出指定数据

而且整个库数据比较多，达到100多万数据，还得涉及到数据库查询的优化，就记录一下处理过程

1 统计数据

        先查询数据，因为要从两个库拿数据，所以用mybatisplus里的@DS注解，在service层使用，直接查外部库；并且表名不确定，要动态拼接，所以准备在代码里拼接sql；预先定义sql各种查询接口，查询一般用List<Map>封装起来，在这里接收数据时，出现了bug，当传递参数拼接执行sql后，查到的结果为空，但返回的是all elements are null，报空指针异常;这时普通的非空判断不起作用；提供一个解决思路：try...catch捕捉异常，在catch里 将值设置为需要的；

预定义代码里调用拼接sql接口：

<!--xml层-->
<select id="select" resultType="java.util.Map">
${sql}
</select>
<select id="selectByEw" resultType="java.util.Map">
${sql} ${ew.customSqlSegment}
</select>
<update id="update">
update ${tablename}
<set>
<foreach collection="params.entrySet()" index="key" item="value" separator=",">
${key} = #{value}
</foreach>
</set>
${ew.customSqlSegment}
</update>
<insert id="insert">
insert into ${tablename}
<foreach collection="params.keys" item="key" open="(" close=")"
separator=",">
${key}
</foreach>
values
<foreach collection="params.values" item="value" open="("
close=")" separator=",">
#{value}
</foreach>
</insert>
<delete id="delete">
delete from ${tablename} ${ew.customSqlSegment}
</delete>

mapper层

List<Map<String,Object>> select(@Param("sql") String sql);
IPage<Map<String,Object>> select(@Param("sql") String sql, Page<?> page);
List<Map<String,Object>> selectByEw(String sql, @Param(Constants.WRAPPER) Wrapper wrapper);
IPage<Map<String,Object>> selectByEw(String sql, Page<?> page, @Param(Constants.WRAPPER) Wrapper wrapper);
Integer update(String tablename, Map<String, ?> params, @Param(Constants.WRAPPER) Wrapper wrapper);
Integer insert(String tablename, Map<String, ?> params);
Integer delete(String tablename, @Param(Constants.WRAPPER) Wrapper wrapper);

service层

Resource
private CoreMapper coreMapper;
@DS("#dbname")
public List<Map<String,Object>> select(String dbname, String sql){
return coreMapper.select(sql);
};
public List<Map<String,Object>> select(String sql){
return coreMapper.select(sql);
};
@DS("#dbname")
public IPage<Map<String,Object>> select(String dbname , String sql, PageRequest pageRequest ){
return coreMapper.select(sql,new Page<>(pageRequest.getPage(), pageRequest.getRows()));
};
public IPage<Map<String,Object>> select( String sql, PageRequest pageRequest ){
return coreMapper.select(sql,new Page<>(pageRequest.getPage(), pageRequest.getRows()));
};
@DS("#dbname")
public List<Map<String,Object>> selectByEw(String dbname, String sql ,QueryWrapper<?> queryWrapper){
return coreMapper.selectByEw(sql,queryWrapper);
}
public List<Map<String,Object>> selectByEw(String sql ,QueryWrapper<?> queryWrapper){
return coreMapper.selectByEw(sql,queryWrapper);
}
@DS("#dbname")
public IPage<Map<String,Object>> selectByEw(String dbname , String sql , PageRequest pageRequest,QueryWrapper<?> queryWrapper){
return coreMapper.selectByEw(sql,new Page<>(pageRequest.getPage(), pageRequest.getRows()),queryWrapper);
}
public IPage<Map<String,Object>> selectByEw( String sql , PageRequest pageRequest,QueryWrapper<?> queryWrapper){
return coreMapper.selectByEw(sql,new Page<>(pageRequest.getPage(), pageRequest.getRows()),queryWrapper);
}
@DS("#dbname")
public Integer update(String dbname ,String tablename ,Map<String,?>params,QueryWrapper<?> queryWrapper){
return coreMapper.update(tablename,params,queryWrapper);
}
public Integer update(String tablename ,Map<String,?>params,QueryWrapper<?> queryWrapper){
return coreMapper.update(tablename,params,queryWrapper);
}
@DS("#dbname")
public Integer insert(String dbname ,String tablename ,Map<String,?>params){
return coreMapper.insert(tablename,params);
}
public Integer insert(String tablename ,Map<String,?>params){
return coreMapper.insert(tablename,params);
}
@DS("#dbname")
public Integer delete(String dbname ,String tablename ,QueryWrapper<?> queryWrapper){
return coreMapper.delete(tablename,queryWrapper);
}
public Integer delete(String tablename ,QueryWrapper<?> queryWrapper){
return coreMapper.delete(tablename,queryWrapper);
}

2 sql查询优化

        接口查询统计数据写好后测试，因为数据量大概150w条，仅一条sql查询时间大概9s左右，严重影响到接口执行效率，于是对sql分段执行，采取加库索引的办法解决，对需要查询的字段创建index；经测试后执行百万条数据处理时间在1s之内；

例如 oracle库 建索引：

CREATE INDEX "SDE"."DLBM_INDEX" ON "SDE"."SRSSDDLTB" ("DLBM") 
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "SDE_TBS" 
;

3 POI导出excel

        然后对数据进行处理，用JsonObject接收存储（底层是实现Map），使用Java POI进行模板导出，模板路径可以用相对路径拼接，或者在yml配置读取；最后响应成流的形式供前台下载；根据自身业务逻辑调整代码，获取塞值变换，最后刷出关闭流；如果excel里内嵌公式的话，还需要调用强制刷新，

new XSSFWorkbook(new FileInputStream(file)).getsheet("name").setForceFormulaRecalculation;