java 达梦数据库结构 转为word文档

---

前言

达梦数据库结构转为word文档

---

一、spring boot 适配达梦

1. 手动引入达梦jdbc驱动包，复制jar包到resources的lib目录下

2. pom.xml中增加依赖，打包插件添加配置

 <dependency>
     <groupId>com.dm</groupId>
     <artifactId>Dm7JdbcDriver17</artifactId>
     <version>1.7</version>
     <scope>system</scope>
     <systemPath>${project.basedir}/src/main/resources/lib/Dm7JdbcDriver17.jar</systemPath>
 </dependency>

   <plugin>
       <groupId>org.springframework.boot</groupId>
       <artifactId>spring-boot-maven-plugin</artifactId>
       <configuration>
       	   <!-- 打包包含达梦jdbc驱动jar包 -->
           <includeSystemScope>true</includeSystemScope>
       </configuration>
   </plugin>

3. application.yml里添加达梦数据库的配置

spring:
  datasource:
    driver-class-name: dm.jdbc.driver.DmDriver
    url: jdbc:dm://192.168.1.33:5237/DMDEMO
    username: DMDEMO
    password: DMDEMO

---

二、添加mybatis-plus、druid

1.添加maven依赖

 <!--druid 连接池-->
 <dependency>
     <groupId>com.alibaba</groupId>
     <artifactId>druid-spring-boot-starter</artifactId>
     <version>1.2.16</version>
 </dependency>

 <!-- mybatis-plus -->
 <dependency>
     <groupId>com.baomidou</groupId>
     <artifactId>mybatis-plus-boot-starter</artifactId>
     <version>3.5.3.1</version>
 </dependency>
 <dependency>
     <groupId>com.baomidou</groupId>
     <artifactId>mybatis-plus</artifactId>
     <version>3.5.3.1</version>
 </dependency>

2. application.yml配置mybatis-plus

#配置mapper xml文件的路径
mybatis-plus:
  mapper-locations: classpath:mapping/*.xml

---

三、编写生成word代码

1. entity

import lombok.Data;

@Data
public class DmTableInfo {
    private String tableName;
    private String tableComment;
}

import lombok.Data;

@Data
public class DmTableField {
    private String columnName;
    private String dataType;
    private String dataLength;
    private String nullable;
    private String comments;
}

2. Mapper

import com.example.dmword.table.entity.DmTableField;
import com.example.dmword.table.entity.DmTableInfo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

import java.util.List;

@Mapper
public interface DmTableMapper {

    List<DmTableInfo> findAllTables(@Param("schemaName") String schemaName);

    List<DmTableField> findFieldsByTableName(@Param("schemaName") String schemaName, 
                                             @Param("tableName") String tableName);

}

3. xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.example.dmword.table.mapper.DmTableMapper">

    <select id="findAllTables" resultType="com.example.dmword.table.entity.DmTableInfo">
        select
            a.table_name as tableName,
            b.comments as tableComment
        from
            dba_tables a
        LEFT OUTER JOIN USER_TAB_COMMENTS b on a.table_name = b.table_name
        where
            a.owner = #{schemaName}
        order by
            a.table_name
    </select>

    <select id="findFieldsByTableName" resultType="com.example.dmword.table.entity.DmTableField">
        select
            a.column_name as columnName,
            a.data_type as dataType,
            a.data_length as dataLength,
            a.nullable as nullable,
            b.comments as comments
        from
            user_tab_columns a
        LEFT OUTER JOIN ALL_COL_COMMENTS b on (a.Table_Name = b.Table_Name and a.column_name = b.column_name)
        where
            a.Table_Name = #{tableName}
        and b.schema_name = #{schemaName}
        order by
            a.column_id
    </select>

</mapper>

4. service

import com.example.dmword.table.entity.DmTableField;
import com.example.dmword.table.entity.DmTableInfo;
import com.example.dmword.table.mapper.DmTableMapper;
import com.lowagie.text.Font;
import com.lowagie.text.*;
import com.lowagie.text.rtf.RtfWriter2;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;

import java.awt.*;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;

@Service
@Slf4j
public class DmTableService {
    private final static String SCHEMA_NAME = "SCHEMA_NAME";
    private final static String ROOT_PATH = "E:\\src";
    private final static String FILE_NAME = "数据库设计文档.doc";

    private final DmTableMapper dmTableMapper;

    public DmTableService(DmTableMapper dmTableMapper) {
        this.dmTableMapper = dmTableMapper;
    }

    public void tableInfo2Word() {
        List<DmTableInfo> tables = dmTableMapper.findAllTables(SCHEMA_NAME);
        Document document = new Document(PageSize.A4);
        try {
            // 创建文件
            File file = createFile();

            // 写入文件信息
            RtfWriter2.getInstance(document, new FileOutputStream(file));
            document.open();

            // 文档标题
            setDocTitle(document);

            int i = 0;
            for (DmTableInfo tableInfo : tables) {
                // 标题
                setTableTitle(document, tableInfo, ++i);

                // 创建表格
                Table table = getTable();
                // 设置表格头部
                setTableHeader(table);
                // 表格的主体
                setTableBody(tableInfo.getTableName(), table);
                // 生成表格
                document.add(table);

                // 换行
                document.add(new Paragraph(""));
            }

            document.close();
        } catch (Exception e) {
            log.error("生成数据库设计文档失败！", e);
        }
    }

    private void setDocTitle(Document document) throws DocumentException {
        Font font = new Font(Font.NORMAL, 24, Font.NORMAL, new Color(0, 0, 0));
        Paragraph paragraph = new Paragraph("数据库设计文档", font);
        paragraph.setAlignment(1);
        document.add(paragraph);
    }

    private File createFile() throws IOException {
        File dir = new File(ROOT_PATH);
        boolean result = dir.mkdirs();
        log.info("创建根目录结果：{}", result);

        // 创建文件
        File file = new File(ROOT_PATH + File.separator + FILE_NAME);
        if (file.exists() && file.isFile()) {
            boolean deleteFlag = file.delete();
            log.info("删除旧doc文件结果：{}", deleteFlag);
        }
        result = file.createNewFile();
        log.info("创建文件结果：{}", result);
        return file;
    }

    private void setTableTitle(Document document, DmTableInfo tableInfo, int num) throws DocumentException {
        String tableComment = tableInfo.getTableComment();
        tableComment = tableComment == null ? "" : "（" + tableComment + "）";
        String tableTitle = num + ". 表名称: " + tableInfo.getTableName() + tableComment;
        document.add(new Paragraph(tableTitle));
    }

    private Table getTable() throws DocumentException {
        Table table = new Table(5);
        // 表格宽度占比
        table.setWidth(90.0f);

        // 每列宽度
        int[] widths = {5, 30, 20, 10, 30};
        table.setWidths(widths);

        table.setBorderWidth(1);
        table.setPadding(0);
        table.setSpacing(0);
        return table;
    }

    private void setTableHeader(Table table) {
        //添加表头的元素，并设置表头背景的颜色
        Color color = new Color(176, 196, 222);

        Cell cell = new Cell("编号");
        addTableHeader(table, cell, color);
        cell = new Cell("字段名");
        addTableHeader(table, cell, color);
        cell = new Cell("类型");
        addTableHeader(table, cell, color);
        cell = new Cell("非空");
        addTableHeader(table, cell, color);
        cell = new Cell("注释");
        addTableHeader(table, cell, color);

        // 表头结束
        table.endHeaders();
    }

    private void setTableBody(String tableName, Table table) {
        List<DmTableField> fields = dmTableMapper.findFieldsByTableName(SCHEMA_NAME, tableName);
        for (int i = 0; i < fields.size(); i++) {
            addContent(table, (i + 1) + "");
            addContent(table, fields.get(i).getColumnName());

            String type = fields.get(i).getDataType();
            if (!"CLOB".equals(type)) {
                type += "(" + fields.get(i).getDataLength() + ")";
            }
            addContent(table, type);

            addContent(table, "Y".equals(fields.get(i).getNullable()) ? "否" : "是");
            addContent(table, fields.get(i).getComments());
        }
    }

    /**
     * 添加表头到表格
     */
    private void addTableHeader(Table table, Cell cell, Color color) {
        cell.setHorizontalAlignment(Element.ALIGN_CENTER);
        cell.setBackgroundColor(color);
        table.addCell(cell);
    }

    /**
     * 添加内容到表格
     */
    private void addContent(Table table, String content) {
        Cell cell = new Cell(content);
        cell.setHorizontalAlignment(Element.ALIGN_CENTER);
        table.addCell(cell);
    }

}