导出数据库表结构到文档中

狄龙疤

已于 2024-02-28 10:12:52 修改

阅读量324

点赞数 2

分类专栏： jdbc javaee poi 文章标签： mysql DDL java POI jdbc

于 2024-02-28 09:46:27 首次发布

本文链接：https://blog.csdn.net/heshiyuan1406146854/article/details/136337470

版权

poi 同时被 3 个专栏收录

7 篇文章 0 订阅

订阅专栏

jdbc

4 篇文章 0 订阅

订阅专栏

javaee

4 篇文章 0 订阅

订阅专栏

导出效果：

完整代码：

Controller层：

import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

@Api(value = "TestController",description="TestController")
@RestController
@RequestMapping("/test")
public class TestController {

    @Autowired
    private IMetadataService metadataService;

    @IgnoreSecurity
    @ApiOperation(value = "DDL2WordTable")
    @GetMapping(value = "/DDL2WordTable")
    public void DDL2WordTable(HttpServletRequest request, HttpServletResponse response){
        try {
            this.metadataService.DDL2WordTable( response );
        }catch ( Exception e ){
            e.printStackTrace();
        }
    }
}

service 层：

IMetadataService.java：

import javax.servlet.http.HttpServletResponse;
import java.util.List;

public interface IMetadataService {
    void DDL2WordTable(HttpServletResponse response);

    List<MySqlTableInfo> collectTableInfos();
}

MetadataServiceImpl.java：

import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;

@Service
@Slf4j
public class MetadataServiceImpl implements IMetadataService {

    @Autowired
    private MetadataMapper metadataMapper;

    @Value( "${spring.datasource.url}" )
    private String url;

    @Override
    public void DDL2WordTable(HttpServletResponse response) {
        try {
            List<MySqlTableInfo> tableInfos = this.collectTableInfos();
            if( tableInfos == null || tableInfos.size() == 0 ){
                return;
            }
            for( MySqlTableInfo tableInfo:tableInfos ){
                System.out.println( tableInfo.getTableName() + "  " + tableInfo.getFieldInfos().size() );
            }
            response.setContentType("application/msword");
            String filename = URLEncoder.encode( "xxxx数据库表结构.docx","UTF-8" );
            response.setHeader("Content-Disposition", "attachment; filename=" + filename);
            POIWordUtils.writeMysqlTables2Word( tableInfos,response.getOutputStream() );
        }catch ( Exception e ){
            e.printStackTrace();
        }
    }

    @Override
    public List<MySqlTableInfo> collectTableInfos() {
        List<String> tableNames = this.metadataMapper.showTableNames();
        if( tableNames == null || tableNames.size() == 0 ){
            return null;
        }
        // todo 从 jdbc url 中提取？？？
        String databaseName = "xxxxxxxxxxxxxx";
        List<MySqlTableInfo> tableInfos = new ArrayList<>();
        for( String tableName:tableNames ){
            List<InformationSchemaCOLUMNS> columnsList = this.metadataMapper.queryInformationSchemaCOLUMNS( databaseName,tableName );
            if( columnsList == null || columnsList.size() == 0 ){
                continue;
            }
            MySqlTableInfo tableInfo = new MySqlTableInfo();
            List<MySqlTableFieldInfo> fieldInfos = new ArrayList<>();
            for( InformationSchemaCOLUMNS columns:columnsList ){
                MySqlTableFieldInfo fieldInfo = new MySqlTableFieldInfo();
                fieldInfo.setName( columns.getColumnName() );
                fieldInfo.setType( columns.getDataType() );
                Long character_maximum_length = columns.getCharacterMaximumLength();
                if( character_maximum_length != null ){
                    fieldInfo.setLength( character_maximum_length.toString() );
                }
                String is_nullable = columns.getIsNullable();
                if( "YES".equals( is_nullable ) ){
                    fieldInfo.setNullable( true );
                }else if( "NO".equals( is_nullable ) ){
                    fieldInfo.setNullable( false );
                }
                fieldInfo.setComment( columns.getColumnComment() );
                String column_key = columns.getColumnKey();
                if( "PRI".equals( column_key ) ){
                    fieldInfo.setPrimaryKey( true );
                }else if( "UNI".equals( column_key ) ){
                    // todo 唯一索引
                    fieldInfo.setIndexType( "唯一索引" );
                }else if( "MUL".equals( column_key ) ){
                    fieldInfo.setIndexType( "普通索引" );
                }else {
                    // todo 其他索引返回什么
                }
                fieldInfo.setDefaultValue( columns.getColumnDefault() );
                fieldInfos.add( fieldInfo );
            }
            tableInfo.setTableName( tableName );
            tableInfo.setFieldInfos( fieldInfos );
            tableInfos.add( tableInfo );
        }

        // set titles
        List<String> titles = new ArrayList<>();
        titles.add( "字段名称" );
        titles.add( "类型" );
        titles.add( "长度" );
        titles.add( "允许为空" );
        titles.add( "字段说明" );
        titles.add( "键" );
        titles.add( "默认值" );
        titles.add( "索引类型" );
        for( MySqlTableInfo tableInfo:tableInfos ){
            tableInfo.setTitles( titles );
        }
        return tableInfos;
    }
}

Mapper 层：

MetadataMapper.java：

import org.apache.ibatis.annotations.Param;

import java.util.List;

@DataSource("dataSource")
public interface MetadataMapper extends BaseMapper {
    List<String> showTableNames();

    List<InformationSchemaCOLUMNS> queryInformationSchemaCOLUMNS(@Param( "databaseName" ) String databaseName,
                                                                 @Param( "tableName" ) String tableName);
}

MetadataMapper.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.xxx.mapper.MetadataMapper">

    <select id="showTableNames" resultType="java.lang.String">
        SHOW TABLES
    </select>

    <select id="queryInformationSchemaCOLUMNS" resultType="com.xxx.vo.InformationSchemaCOLUMNS">
        SELECT
            TABLE_CATALOG AS tableCatalog,
            TABLE_SCHEMA tableSchema,
            TABLE_NAME AS tableName,
            COLUMN_NAME AS columnName,
            ORDINAL_POSITION AS ordinalPosition,
            COLUMN_DEFAULT AS columnDefault,
            IS_NULLABLE AS isNullable,
            DATA_TYPE AS dataType,
            CHARACTER_MAXIMUM_LENGTH AS characterMaximumLength,
            CHARACTER_OCTET_LENGTH AS characterOctetLength,
            NUMERIC_PRECISION AS numericPrecision,
            NUMERIC_SCALE AS numericScale,
            DATETIME_PRECISION AS datetimePrecision,
            CHARACTER_SET_NAME AS characterSetName,
            COLLATION_NAME AS collationName,
            COLUMN_TYPE AS columnType,
            COLUMN_KEY AS columnKey,
            EXTRA AS extra,
            PRIVILEGES AS privileges,
            COLUMN_COMMENT AS columnComment,
            GENERATION_EXPRESSION AS generationExpression
        FROM
            information_schema.COLUMNS
        WHERE
            table_name = #{tableName}
        AND
            table_schema = #{databaseName}
    </select>
</mapper>

用到的 VO 类：

InformationSchemaCOLUMNS.java：

import lombok.Getter;
import lombok.Setter;

@Getter
@Setter
public class InformationSchemaCOLUMNS {

    private Long ordinalPosition;

    /**
     * 字段长度
     */
    private Long characterMaximumLength;
    private Long characterOctetLength;
    private Long numericPrecision;
    private Long numericScale;
    private Long datetimePrecision;
    private String tableCatalog;
    private String tableSchema;
    private String tableName;

    /**
     * 字段名称，如：username
     */
    private String columnName;

    /**
     * 默认值
     */
    private String columnDefault;

    /**
     * 字段是否允许空值，'YES'、'NO'
     */
    private String isNullable;

    /**
     * 字段的数据类型,如：varchar、datetime ...
     */
    private String dataType;
    private String characterSetName;
    private String collationName;
    private String columnType;

    /**
     * 该字段的索引类型( 'PRI'：主键索引，'UNI'：唯一索引，'MUL'：普通索引 )
     */
    private String columnKey;
    private String extra;
    private String privileges;

    /**
     * 字段的注释
     */
    private String columnComment;
    private String generationExpression;
}

MySqlTableInfo.java：

import lombok.Getter;
import lombok.Setter;

import java.util.List;

@Getter
@Setter
public class MySqlTableInfo {

    private String tableName;

    private List<String> titles;
    private List<MySqlTableFieldInfo> fieldInfos;
}

MySqlTableFieldInfo.java：

import lombok.Getter;
import lombok.Setter;

@Getter
@Setter
public class MySqlTableFieldInfo {

    /**
     * 字段名称，如：username
     */
    private String name;

    /**
     * 字段的数据类型
     */
    private String type;

    /**
     * 字段长度
     */
    private String length;

    /**
     * 字段是否允许空值
     */
    private boolean nullable;

    /**
     * 字段的注释
     */
    private String comment;

    /**
     * 该字段是否是主键
     */
    private boolean primaryKey;

    /**
     * 默认值
     */
    private String defaultValue;
    private String indexType;
}

POI 工具类：

POIWordUtils.java：

import org.apache.poi.xwpf.usermodel.*;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTP;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTTc;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

public class POIWordUtils {

    public static void writeMysqlTables2Word(List<MySqlTableInfo> tableInfos, OutputStream outputStream){
        XWPFDocument xwpfDocument = null;
        try {
            xwpfDocument = new XWPFDocument();
            // 创建1行3列的表格

            for( MySqlTableInfo tableInfo:tableInfos ){
                appendMysqlTable2Word( xwpfDocument,tableInfo );
            }
            xwpfDocument.write( outputStream );
        }catch ( Exception e ){
            e.printStackTrace();
        }finally {
            if( xwpfDocument != null ){
                try {
                    xwpfDocument.close();
                }catch ( Exception e ){
                    e.printStackTrace();
                }
            }
        }
    }

    private static void appendMysqlTable2Word(XWPFDocument xwpfDocument, MySqlTableInfo tableInfo) {
        xwpfDocument.createParagraph().createRun().setText( "" );
        xwpfDocument.createParagraph().createRun().setText( "" );
        xwpfDocument.createParagraph().createRun().setText( "" );
        XWPFRun run = xwpfDocument.createParagraph().createRun();
        run.setFontSize( 12 );
        run.setText( tableInfo.getTableName() );

        List<String> titles = tableInfo.getTitles();
        List<MySqlTableFieldInfo> fieldInfos = tableInfo.getFieldInfos();
        int colCount = titles.size();
        int rowCount = 1 + tableInfo.getFieldInfos().size();

        XWPFTable table = xwpfDocument.createTable(rowCount,colCount );
        table.setWidth(10000);
        // table.setTableAlignment( TableRowAlign.CENTER );
        XWPFTableRow row_title = table.getRow(0);

        // 设置标题行
        for( int colIndex = 0;colIndex<colCount;colIndex++ ){
            XWPFTableCell cell = row_title.getCell(colIndex);
            cell.setWidthType( TableWidthType.PCT );
            cell.setWidth( "12.5%" );
            String title = titles.get(colIndex);
            setTextForCell( cell,title );
            cell.setVerticalAlignment(XWPFTableCell.XWPFVertAlign.CENTER);
        }
        for( int rowIndex=1;rowIndex<rowCount;rowIndex++ ){
            XWPFTableRow row = table.getRow(rowIndex);
            row.setHeight( 500 );
            MySqlTableFieldInfo fieldInfo = fieldInfos.get(rowIndex - 1);

            XWPFTableCell cell0 = row.getCell(0);
            XWPFTableCell cell1 = row.getCell(1);
            XWPFTableCell cell2 = row.getCell(2);
            XWPFTableCell cell3 = row.getCell(3);
            XWPFTableCell cell4 = row.getCell(4);
            XWPFTableCell cell5 = row.getCell(5);
            XWPFTableCell cell6 = row.getCell(6);
            XWPFTableCell cell7 = row.getCell(7);

            cell0.setWidthType( TableWidthType.PCT );
            cell1.setWidthType( TableWidthType.PCT );
            cell2.setWidthType( TableWidthType.PCT );
            cell3.setWidthType( TableWidthType.PCT );
            cell4.setWidthType( TableWidthType.PCT );
            cell5.setWidthType( TableWidthType.PCT );
            cell6.setWidthType( TableWidthType.PCT );
            cell7.setWidthType( TableWidthType.PCT );

           /* cell0.setVerticalAlignment(XWPFTableCell.XWPFVertAlign.CENTER);
            cell1.setVerticalAlignment(XWPFTableCell.XWPFVertAlign.CENTER);
            cell2.setVerticalAlignment(XWPFTableCell.XWPFVertAlign.CENTER);
            cell3.setVerticalAlignment(XWPFTableCell.XWPFVertAlign.CENTER);
            cell4.setVerticalAlignment(XWPFTableCell.XWPFVertAlign.CENTER);
            cell5.setVerticalAlignment(XWPFTableCell.XWPFVertAlign.CENTER);
            cell6.setVerticalAlignment(XWPFTableCell.XWPFVertAlign.CENTER);
            cell7.setVerticalAlignment(XWPFTableCell.XWPFVertAlign.CENTER);*/

            cell0.setWidth( "12.5%" );
            cell1.setWidth( "12.5%" );
            cell2.setWidth( "12.5%" );
            cell3.setWidth( "12.5%" );
            cell4.setWidth( "12.5%" );
            cell5.setWidth( "12.5%" );
            cell6.setWidth( "12.5%" );
            cell7.setWidth( "12.5%" );

            setTextForCell( cell0,fieldInfo.getName() );
            setTextForCell( cell1,fieldInfo.getType() );
            String length = fieldInfo.getLength();
            if( length == null ){
                setTextForCell( cell2,"" );
            }else {
                setTextForCell( cell2,String.valueOf( fieldInfo.getLength() ) );
            }
            if( fieldInfo.isNullable() ){
                setTextForCell( cell3,"YES" );
            }else {
                setTextForCell( cell3,"NO" );
            }
            setTextForCell( cell4,fieldInfo.getComment() );
            if( fieldInfo.isPrimaryKey() ){
                setTextForCell( cell5,"主键" );
            }
            setTextForCell( cell6,fieldInfo.getDefaultValue() );
            setTextForCell( cell7,fieldInfo.getIndexType() );
        }
    }

    private static void setTextForCell(XWPFTableCell cell,String text) {
        // cell.setText( text );
        CTTc ctTc = cell.getCTTc();
        CTP ctP = (ctTc.sizeOfPArray() == 0) ?ctTc.addNewP() : ctTc.getPArray(0);
        XWPFParagraph paragraph = cell.getParagraph(ctP);
        XWPFRun run = paragraph.createRun();
         paragraph.setAlignment( ParagraphAlignment.CENTER );
         paragraph.setVerticalAlignment( TextAlignment.CENTER );
        // 即小四
        run.setFontSize( 12 );
        run.setText( text );
    }
}