com.github.xiaoyuge5201.util.ExportDatabaseDocument Maven / Gradle / Ivy

Go to download
Show more of this group Show more artifacts with this name
Show all versions of datasource-spring-boot-starter Show documentation
数据库连接工具，查询mysql、oracle、sqlserver、postgresql的数据表以及字段信息；同时支持导出数据库设计文档
The newest version!
package com.github.xiaoyuge5201.util;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import com.github.xiaoyuge5201.config.MyDataSourceProperties;
import com.github.xiaoyuge5201.entity.ColumnEntity;
import org.apache.commons.lang3.RandomStringUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.UnsupportedEncodingException;
import java.nio.charset.StandardCharsets;
import java.time.LocalDateTime;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * 导出设计文档
 *
 * @author yugb
 */
public class ExportDatabaseDocument {
    private static final Logger logger = LoggerFactory.getLogger(ExportDatabaseDocument.class);

    /**
     * 导出设计文档
     *
     * @param response        返回
     * @param request         请求
     * @param driverClassName 驱动
     * @param url             地址
     * @param username        用户
     * @param password        密码
     * @param database        数据库
     */
    public static void export(HttpServletResponse response, HttpServletRequest request, String driverClassName, String url, String username, String password, String database) {
        try {

            response.setContentType("text/html;charset=utf-8");
            try {
                request.setCharacterEncoding("UTF-8");
            } catch (UnsupportedEncodingException e1) {
                e1.printStackTrace();
            }
            response.setContentType("application/x-msdownload;");
            String filename = "数据库设计文档" + LocalDateTime.now() + ".xlsx";
            response.setHeader("Content-disposition", "attachment; filename="
                    + new String(filename.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1));
            XSSFWorkbook result = new XSSFWorkbook();
            logger.info("获取数据集中......");
            List list = QuerySqlUtil.listColumnsByDatasourceParams(driverClassName, url, username, password, database);
            logger.info("开始导出......");
            assert list != null;
            list.stream().collect(Collectors.groupingBy(ColumnEntity::getTableName)).forEach((tableName, tableDesc) -> {
                TemplateExportParams params = new TemplateExportParams("template-tableDesc.xlsx");
                Map map = new HashMap<>(16);
                map.put("maplist", tableDesc);
                map.put("TABLE_NAME", tableName);
                String tem = (tableDesc.get(0).getTableComment() == null) ? "" : tableDesc.get(0).getTableComment();
                map.put("TABLE_COMMENT", tem);
                Workbook r = ExcelExportUtil.exportExcel(params, map);
                if (r != null) {
                    //注意以下事项：
                    //1. sheet名称不能有重复
                    //2. sheet的名称不能为空或空字符串（不能包含： 、/、？、*、[]等；能包含英文、汉字、| 、（）等）都不可以
                    //3. sheet的名称不能sheet名称不能超过31个，不然会自动切割字符串，导致sheet名可能重复
                    // character count MUST be greater than or equal to 1 and less than or equal to 31
                    StringBuffer stringBuffer = new StringBuffer();
                    if (StringUtils.isNotBlank(tem)) {
                        stringBuffer.append(tem);
                    } else {
                        stringBuffer.append(tableName.length() > 31 ? tableName.substring(0, 25) : tableName);
                    }
                    stringBuffer.append("(").append(RandomStringUtils.randomAlphanumeric(4)).append(")");
                    WorkbookUtils.copySheet(result, (XSSFSheet) r.getSheetAt(0), (XSSFSheet) result.createSheet(stringBuffer.toString()));

                    logger.info("数据表："+tableName+"已装备完毕！");
                }
            });
            result.write(response.getOutputStream());
            logger.info("导出成功......");
        } catch (Exception ex) {
            ex.printStackTrace();
            logger.error(ex.getMessage());
        }
    }

    /**
     * 导出本项目的数据库设计文档
     * 通过@ConfigurationProperties软绑定springboot的yml/yaml/properties配置文件中的数据库链接属性
     * 需要配置spring.datasource.database属性（数据库名称）
     *
     * @param response   响应
     * @param request    请求
     * @param properties 数据库链接信息
     */
    public static void export(HttpServletResponse response, HttpServletRequest request, MyDataSourceProperties properties) {
        export(response, request, properties.getDriverClassName(), properties.getUrl(), properties.getUsername(), properties.getPassword(), properties.getDatabase());
    }
}