菜鸟后端开发之导出

开发实现导入，就会有一些相应的功能，例如：导出

下面是我菜菜鸟自己的实现导出功能的过程

Controller层

controller层实现前后端的交互衔接，为防安全，实现时可以添加响应的权限。使用公用的工具类/jar包就不展示了。

@RestController
@Slf4j
@RequiredArgsConstructor
@RequestMapping("/baseTownMapping")
@Api(value = "baseTownMapping", tags = "关系映射")
public class BaseTownMappingController {    

    @ApiOperation(value = "乡镇管理-数据导出", notes = "乡镇管理-数据导出")
	@PreAuthorize("@pms.hasPermission('baseTownMapping_dataExport')")
	@GetMapping("dataExport")
	public  R dataExport(BaseTownMappingRequest request){
		try {
			//乡镇映射数据查询
			ExportFileVo fileVo = baseTownMappingService.dataExport(request);
			return R.ok(fileVo);
		} catch (Exception e) {
			return R.failed(e.getMessage());
		}
	}
}

Service层

Service

public interface BaseTownMappingService extends IService<BaseTownMapping> {

	/**
	 * 关系映射导出
	 * @param request
	 * @return
	 */
	ExportFileVo dataExport(BaseTownMappingRequest request);


}

ServiceImpl

@Service
public class BaseTownMappingServiceImpl extends ServiceImpl<BaseTownMappingMapper, BaseTownMapping> implements BaseTownMappingService {

    @Autowired(required = false)
	private BaseTownMappingMapper baseTownMappingMapper;
    
    @Autowired
	private BaseAreaService baseAreaService;

    @Override
	public ExportFileVo dataExport(BaseTownMappingRequest request) {
		ExportFileVo fileVo = null;
		try {
			PigUser user = SecurityUtils.getUser();
			List district = getDistrictByPermission();
			long total = baseTownMappingMapper.selectTownMappingCount(request);
			// 1：长期保存 2：下载后删除 3：定期删除
			Integer busType = FilesConstent.SYS_FILE_BUS_TYPE_2;
			if (total > BaseConstants.BASE_AOI_ORIGINAL_SIZE) {
				busType = FilesConstent.SYS_FILE_BUS_TYPE_3;
			}
			Map<String, String> areaMap = baseAreaService.getBaseAreaMap();
			Integer fileId = null;
			if (busType.equals(FilesConstent.SYS_FILE_BUS_TYPE_2)) {
				fileId = writeFile(request, busType, user, false, total, areaMap);
			} else {
				fileId = writeExcel(request, busType, user, total, areaMap);
			}
			fileVo = new ExportFileVo();
			fileVo.setFId(fileId);
			fileVo.setFileType(busType - 1);
		} catch (Exception e) {
			log.error("导出失败！", e);
			throw new RuntimeException("导出失败");
		}
		return fileVo;
	}


    private Integer writeFile(BaseTownMappingRequest baseTownMappingRequest, Integer busType,
							  PigUser user, boolean ifZip, Long total, Map<String, String> baseAreaMap) {
		String suffix = ".csv";
		String fileName =
				BaseConstants.BASE_TOWN_MAPPING_NAME + System.currentTimeMillis() + suffix;
		SysFilesParams sysFilesParams = filesWriteService.writeFileBefore(fileName,
				BaseConstants.BASE_TOWN_MAPPING_NAME, busType, user);
		BaseTownMappingHandler baseTownMappingHandler = new BaseTownMappingHandler(
				remoteFilesService, sysFilesParams, total,
				ifZip, baseAreaMap);
		baseTownMappingMapper.streamSelectTownMapping(baseTownMappingRequest, baseTownMappingHandler);
		return sysFilesParams.getFileId();
	}


    @Async
	protected Integer writeExcel(BaseTownMappingRequest baseTownMappingRequest, Integer busType,
								 PigUser user, Long total,
								 Map<String, String> baseAreaMap) {
		swireExecutor.execute(new Runnable() {
			@Override
			public void run() {
				writeFile(baseTownMappingRequest, busType, user, false, total, baseAreaMap);
			}
		});
		return 0;
	}

}

Handler

@Slf4j
public class BaseTownMappingHandler<T> implements ResultHandler<T> {

	//导出总条数
	private long dataTotalCount;
	private long currentIndex = 0;
	private Integer writeIndex = 0;

	/**
	 * 每批处理的大小  该值大小决定文件生成速度与内存占用大小（根据实际情况调整该值）
	 */
	private int batchSize = 10000;
	private RemoteFilesService remoteFilesService;
	private SysFilesParams sysFilesParams;
	private Boolean ifZip;

	private List<BaseTownMappingDTO> dataList = new ArrayList<>();
	Map<String, String> areaMap = new HashMap<>();

	public BaseTownMappingHandler() {

	}

	public BaseTownMappingHandler(RemoteFilesService remoteFilesService,
								  SysFilesParams sysFilesParams, long dataTotalCount, Boolean ifZip,Map<String, String> areaMap) {
		this.dataTotalCount = dataTotalCount;
		this.areaMap = areaMap;
		this.remoteFilesService = remoteFilesService;
		this.sysFilesParams = sysFilesParams;
		this.ifZip = ifZip;
	}

	@Override
	public void handleResult(ResultContext<? extends T> resultContext) {
		BaseTownMappingDTO baseTownMappingDTO = (BaseTownMappingDTO) resultContext.getResultObject();
		
		dataList.add(baseTownMappingDTO);

		this.currentIndex++;
		if (dataList.size() >= batchSize && this.currentIndex < dataTotalCount) {
			//查询其他数据
			/*buildDate(dataList);*/
			this.printMemoryInfo(10);
			handle(false);
			this.printMemoryInfo(11);
		}
		if (this.currentIndex >= dataTotalCount) {
			//查询其他数据
			/*buildDate(dataList);*/
			handle(true);
			this.printMemoryInfo(12);
		}
	}

	private void handle(boolean ifEnd) {
		if (!CollectionUtils.isEmpty(dataList)) {
			//组装其他数据
			ByteArrayOutputStream out = this.createDataForCsv(dataList);
			sysFilesParams.setFile(out.toByteArray());
			sysFilesParams.setIfEnd(ifEnd);
			sysFilesParams.setIfZip(this.ifZip);

			/**
			 * step3 写入文件并修改文件表记录
			 */
			R<SysFilesVo> returnVoObj = remoteFilesService.transferBatchBigFile(sysFilesParams,
					SecurityConstants.FROM_IN);
			dataList.clear();
		}
	}

	private static final char separator = ',';

	private ByteArrayOutputStream createDataForCsv(List<BaseTownMappingDTO> dataList) {
		CsvWriter writer = null;
		ByteArrayOutputStream out = null;
		try {
			out = new ByteArrayOutputStream(1024);
			//如果生产文件乱码，windows下用gbk，linux用UTF-8
			writer = new CsvWriter(out, separator, Charset.forName("GBK"));
			//写表头（34列）
			String[] header = {"系统编码", "系统名称", "客户编码", "客户名称", "创建时间", "操作人",};

			if (this.writeIndex == 0) {
				writer.writeRecord(header);
			}

			/**
			 * TODO
			 * 典型值性能测试
			 */
			// 逐条读取记录，直至读完
			for (BaseTownMappingDTO obj : dataList) {
				String[] record = new String[header.length];
				int i = 0;
				record[i++] = '\t'+obj.getTownCode();
				record[i++] = obj.getTownName();
				record[i++] = '\t'+obj.getSwireCode();
				record[i++] = obj.getSwireName();
				writer.writeRecord(record, true);
			}
			this.writeIndex++;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (null != writer) {
				writer.close();
			}
		}

		return out;
	}

	private void printMemoryInfo(Integer step) {
		Runtime runtime = Runtime.getRuntime();
		log.info(step + "本机CPU内核数：{},最大可用内存空间:{},可用内存空间:{},空闲内存空间:{}",
				runtime.availableProcessors(), runtime.maxMemory() / 1024 / 1024,
				runtime.totalMemory() / 1024 / 1024, runtime.freeMemory() / 1024 / 1024);
	}

	private int changeNullInteger(Integer number) {
		if (number == null) {
			return 0;
		}
		return number;
	}
}

Mapper层

Mapper

@Mapper
public interface BaseTownMappingMapper extends BaseMapper<BaseTownMapping>{

	/**
	 * 关系映射导出统计
	 * @param request
	 * @return
	 */
	Long selectTownMappingCount(@Param("param")BaseTownMappingRequest request);

    
    /**
	 * 关系映射导出  流式查询
	 * @param request
	 * @param townMappingHandler
	 */
	void streamSelectTownMapping(@Param("param")BaseTownMappingRequest request,
								 @Param("handler")BaseTownMappingHandler townMappingHandler);

}

MapperXML

    
    <!-- 查询导出总数 -->
	<select id="selectTownMappingCount" resultType="java.lang.Long"
			parameterType="com.topprismcloud.rtm.base.api.request.BaseTownMappingRequest">
		SELECT
			count(1)
		FROM base_town_mapping m
		ORDER BY m.update_time DESC
	</select>

    
    <!--流式查询用于导出 excel -->
	<select id="streamSelectTownMapping" resultType="com.topprismcloud.rtm.base.api.dto.BaseTownMappingDTO"
			parameterType="com.topprismcloud.rtm.base.api.request.BaseTownMappingRequest"
			fetchSize="1000">
		SELECT
			m.town_code as townCode,
			m.town_name as townName,
			m.swire_code as swireCode,
			m.swire_name as swireName,
			m.update_time as updateTime,
			m.update_by as updateBy
		FROM
		     base_town_mapping m
		WHERE  1=1
		<if test="param.townCode != null and param.townCode != ''">
			and m.town_code = #{param.townCode}
		</if>
		<if test="param.townName != null and param.townName != ''">
			and m.town_name like CONCAT('%',#{param.town_name},'%')
		</if>
		<if test="param.townCode != null and param.townCode != ''">
			and m.town_code like CONCAT('%',#{param.townCode},'%')
		</if>
		<if test="param.townName != null and param.townName != ''">
			and m.town_name like CONCAT('%',#{param.townName},'%')
		</if>
		<if test="param.swireCode != null and param.swireCode != ''">
			and m.swire_code like CONCAT('%',#{param.swireCode},'%')
		</if>
		<if test="param.swireName != null and param.swireName != ''">
			and m.swire_name like CONCAT('%',#{param.swireName},'%')
		</if>
		ORDER BY
		    m.create_time DESC
	</select>

实体

Request

@Data
@ApiModel(value = "关系映射请求参数")
public class BaseTownMappingRequest extends BaseRequest{

	/**
	 * id
	 */
	@ApiModelProperty(value = "id")
	private BigInteger id;

	/**
	 * 系统编码
	 */
	@ApiModelProperty(value = "系统编码")
	private String townCode;

	/**
	 * 系统名称
	 */
	@ApiModelProperty(value = "系统名称")
	private String townName;

	/**
	 * 客户编码
	 */
	@ApiModelProperty(value = "客户编码")
	private String swireCode;

	/**
	 * 客户名称
	 */
	@ApiModelProperty(value = "客户名称")
	private String swireName;

	public <T> QueryWrapper<T> queryWrapper() {
		QueryWrapper<T> queryWrapper = new QueryWrapper<>();
		if (CharSequenceUtil.isNotEmpty(townName)) {
			queryWrapper.like("td.town_name", townName);
		}
		if (CharSequenceUtil.isNotEmpty(townCode)) {
			queryWrapper.eq("td.town_code", townCode);
		}
		if (CharSequenceUtil.isNotEmpty(townName)) {
			queryWrapper.like("td.swire_name", swireName);
		}
		if (CharSequenceUtil.isNotEmpty(townCode)) {
			queryWrapper.eq("td.swire_code", swireCode);
		}
		if (CharSequenceUtil.isNotEmpty(getProvince())) {
			queryWrapper.eq("t.province", getProvince());
		}
		if (CharSequenceUtil.isNotEmpty(getCity())) {
			queryWrapper.eq("t.city", getCity());
		}
		if (CharSequenceUtil.isNotEmpty(getDistrict())) {
			queryWrapper.eq("t.district", getDistrict());
		}
		return queryWrapper;
	}

	@ApiModelProperty(value = "创建时间")
	private String createTime;

	@ApiModelProperty(value = "创建人")
	private String createBy;

	@ApiModelProperty(value = "修改时间")
	private Date updateTime;

	@ApiModelProperty(value = "修改人")
	private String updateBy;


}

DTO

@Data
public class BaseTownMappingDTO implements Serializable {

	@ApiModelProperty(value = "id")
	@ExcelProperty(index = 0,value = "id")
	private String id;

	/**
	 * 系统编码
	 */
	@ApiModelProperty(value = "系统编码")
	@ExcelProperty(index = 1,value = "系统编码")
	private String townCode;

	/**
	 * 系统名称
	 */
	@ApiModelProperty(value = "系统名称")
	@ExcelProperty(index = 2,value = "系统名称")
	private String townName;

	/**
	 * 客户编码
	 */
	@ApiModelProperty(value = "客户编码")
	@ExcelProperty(index = 3,value = "客户编码")
	private String swireCode;

	/**
	 * 客户名称
	 */
	@ApiModelProperty(value = "客户名称")
	@ExcelProperty(index = 4,value = "客户名称")
	private String swireName;


	@ApiModelProperty(value = "创建时间")
	@ExcelProperty(index = 5,value = "创建时间")
	private String createTime;
	@ApiModelProperty(value = "创建人")
	@ExcelProperty(index = 6,value = "创建人")
	private String createBy;

	@ApiModelProperty(value = "修改时间")
	@ExcelProperty(index = 7,value = "修改时间")
	private Date updateTime;
	@ApiModelProperty(value = "修改人")
	@ExcelProperty(index = 8,value = "修改人")
	private String updateBy;

}

就这么多吧，谢谢各位浏览，如有错误，望留下评论，万分感谢