数据库导出为word,自动生成数据库设计交付文档

1 领导让搞数据库文档，实在太过繁琐，自己库表又多，照着网上例子找了个数据库导出word的功能

 2 目前只是为了postgres数据库可用，目前自己用的是这个，mysql也能用，自己稍微改一下就行
3 目前只是查询了带有字段注释的内容，没注释的不导出，自己可以根据需求修改mapper中的第二个sql

上代码
entity

@Data
public class TableStruct {
    private String field;
    private String type;
    private String comment;
}

import org.apache.poi.xwpf.usermodel.*;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTDecimalNumber;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTPPr;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTString;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTStyle;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.math.BigInteger;
import java.net.URLEncoder;
import java.time.LocalDate;
import java.util.List;
import java.util.stream.Collectors;

@RestController
@RequestMapping(value = "/kk")
public class KkDbTable {

    @Resource
    TableMapper tableMapper;


    @GetMapping("/info/download")
    public void downSeg(HttpServletResponse response){
        try {
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            String fileName = URLEncoder.encode("表结构注释说明"+ LocalDate.now().toString(), "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".docx");
            // 创建文档
            XWPFDocument document = new XWPFDocument();
            //设置标题
            XWPFStyles styles = document.createStyles();
            String heading1StyleId = "hdi";
            addCustomHeadingStyle(styles, heading1StyleId, "kk出品不一定精品，但是能用", 1);
            List<String> allTables = tableMapper.getAllTables();
            for (String temp : allTables) {//生成标题
                //生成表头
                List<TableStruct> tableInfo = tableMapper.getTableInfo(temp);
                if(CollectionUtils.isEmpty(tableInfo)||tableInfo.get(0)==null){
                    continue;
                }
                tableInfo = tableInfo.stream().filter(x -> !StringUtils.isEmpty(x.getComment())).collect(Collectors.toList());
                XWPFParagraph title1Paragraph = document.createParagraph();
                title1Paragraph.setStyle(heading1StyleId);
                XWPFRun title1Run = title1Paragraph.createRun();
                title1Run.setText(temp);
                XWPFTable table = document.createTable(tableInfo.size() + 1, 4);
                table.setWidth(100);
                table.getRow(0).getCell(0).setText("序号");
                table.getRow(0).getCell(1).setText("字段名称");
                table.getRow(0).getCell(2).setText("字段类型");
                table.getRow(0).getCell(3).setText("字段描述");
                for (int row = 0; row < tableInfo.size(); row++) {
                    table.getRow(row + 1).getCell(0).setText(Integer.toString(row));
                    table.getRow(row + 1).getCell(1).setText(tableInfo.get(row).getField());
                    table.getRow(row + 1).getCell(2).setText(tableInfo.get(row).getType());
                    table.getRow(row + 1).getCell(3).setText(tableInfo.get(row).getComment());
                }
            }

            ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
            document.write(byteArrayOutputStream);
            ServletOutputStream outputStream = response.getOutputStream();
            outputStream.write(byteArrayOutputStream.toByteArray());
            outputStream.flush();
            outputStream.close();
            document.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    private static void addCustomHeadingStyle(XWPFStyles styles, String styleId, String styleName, int headingLevel) {
        CTStyle ctStyle = CTStyle.Factory.newInstance();
        ctStyle.setStyleId(styleId);
        CTString styleNameString = CTString.Factory.newInstance();
        styleNameString.setVal(styleName);
        ctStyle.setName(styleNameString);
        CTDecimalNumber indentNumber = CTDecimalNumber.Factory.newInstance();
        indentNumber.setVal(BigInteger.valueOf(headingLevel));
        CTPPr ppr = CTPPr.Factory.newInstance();
        ppr.setOutlineLvl(indentNumber);
        ctStyle.setPPr(ppr);
        XWPFStyle style = new XWPFStyle(ctStyle);
        styles.addStyle(style);
    }

}

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
@Mapper
public interface TableMapper {

    // 获取所有表名
    @Select("SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'")
    List<String> getAllTables();

    // 获取表的详细信息 mysql
//    @Select("SELECT column_name, data_type, character_maximum_length, is_nullable, column_default " +
//            "FROM information_schema.columns " +
//            "WHERE table_name = #{tableName} AND table_schema = 'public'")

    String sql="SELECT\n" +
            "    a.attname AS field,\n" +
            "    pg_type.typname AS type,\n" +
            "    d.description AS comment\n" +
            "FROM\n" +
            "    pg_class t,\n" +
            "    pg_namespace n,\n" +
            "    pg_attribute a,\n" +
            "    pg_type,\n" +
            "    pg_description d\n" +
            "WHERE\n" +
            "    t.relnamespace = n.oid\n" +
            "    AND a.attrelid = t.oid\n" +
            "    AND a.atttypid = pg_type.oid\n" +
            "    AND a.attnum > 0 AND NOT a.attisdropped\n" +
            "    AND d.objoid = t.oid\n" +
            "    AND d.objsubid = a.attnum\n" +
            "    AND n.nspname = 'public'   \n" +
            "    AND t.relname = #{tableName} ;  ";
    @Select(sql)
    List<TableStruct> getTableInfo(@Param("tableName") String tableName);

    }

功能简单 效果如下图