java实现导出mysql表结构、索引信息、约束信息（续）

代码实现

import com.alibaba.fastjson.JSONObject;
import org.apache.poi.xwpf.usermodel.XWPFDocument;
import org.apache.poi.xwpf.usermodel.XWPFParagraph;
import org.apache.poi.xwpf.usermodel.XWPFRun;
import org.apache.poi.xwpf.usermodel.XWPFTable;

import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.*;

public class ConstructionToWord {
    private final String DRIVER = "com.mysql.jdbc.Driver";
    //private final String DRIVER = "com.mysql.cj.jdbc.Driver";
    private final String URL = "jdbc:mysql://localhost:3306/test"+"?useUnicode=true&characterEncoding=utf8&useSSL=false";
    private final String USER_NAME = "root";
    private final String PASS_WORD = "123456#";
    private final String database = "test";
    private final String reportPath = "G:";

    // 启动方法
    public static void main(String[] args) {

        try {
            ConstructionToWord rd = new ConstructionToWord();
            rd.report();
        }catch (Exception e){
            System.out.println("异常：自行处理或者联系我都阔以.");
            e.printStackTrace();
        }

    }


    Connection conn = null;
    PreparedStatement pst = null;
    ResultSet rs = null;

    // 获取查询数据
    public Map<String, List<TableColumn>> getTableColumnData() throws Exception{

        System.out.println("数据生成中，请稍等...");
        Map<String,List<TableColumn>> map = new HashMap<String,List<TableColumn>>();

        List<Table> tables = getTables(database);

        for (Table table : tables) {
            List<TableColumn> columns = getColumns(database,table.getTableName());
            map.put(table.getTableName(),columns);
        }

        return map;

    }


    // 获取表字段信息
    public List<TableColumn>  getColumns(String database,String tableName) throws Exception{

        String sql = "select column_name,column_comment,CHARACTER_MAXIMUM_LENGTH,column_type,is_nullable, column_key from information_schema.columns  where  table_schema=? and table_name=?  group by column_name";
        ResultSet rs = getConn(database,tableName,sql);

        List<TableColumn> tableColumns = new ArrayList<TableColumn>();

        while (rs.next()){

            TableColumn tc = new TableColumn();
            tc.setTableName(tableName);
            tc.setColumnName(rs.getString("column_name"));
            tc.setColumnType(rs.getString("column_type"));
            tc.setMaxLength(rs.getString("CHARACTER_MAXIMUM_LENGTH"));
            tc.setColumnKey(rs.getString("column_key"));
            tc.setIsNullable(rs.getString("is_nullable"));
            tc.setColumnComment(rs.getString("column_comment"));
            tableColumns.add(tc);

        }

        releaseConn();

        return tableColumns;

    }
    // 获取表索引信息
    public List<TableIndex>  getIndexTable(String database,String tableName) throws Exception{

        String sql = "select COLUMN_NAME,INDEX_NAME,CARDINALITY,INDEX_TYPE,INDEX_COMMENT from information_schema.STATISTICS where TABLE_SCHEMA = ? and TABLE_NAME =? group by COLUMN_NAME;";
        ResultSet rs = getConn(database,tableName,sql);

        List<TableIndex> tableIndexs = new ArrayList<TableIndex>();

        while (rs.next()){

            TableIndex tableIndex =new TableIndex();
            tableIndex.setTableName(tableName);
            tableIndex.setColumnName(rs.getString("COLUMN_NAME"));
            tableIndex.setIndexName(rs.getString("INDEX_NAME"));
            tableIndex.setCardinality(rs.getString("CARDINALITY"));
            tableIndex.setIndexType(rs.getString("INDEX_TYPE"));
            tableIndex.setColumnName(rs.getString("COLUMN_NAME"));

            tableIndexs.add(tableIndex);

        }
        releaseConn();

        return tableIndexs;

    }

    // 获取键信息
    public List<TableKey>  getTableKey(String database,String tableName) throws Exception{

        String sql = "select CONSTRAINT_NAME,COLUMN_NAME,POSITION_IN_UNIQUE_CONSTRAINT,REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME from information_schema.KEY_COLUMN_USAGE where TABLE_SCHEMA = ? and TABLE_NAME =? group by COLUMN_NAME";
        ResultSet rs = getConn(database,tableName,sql);

        List<TableKey> keys = new ArrayList<TableKey>();

        while (rs.next()){

            TableKey key =new TableKey();
            key.setTableName(tableName);
            key.setColumnName(rs.getString("COLUMN_NAME"));
            key.setConstraintName(rs.getString("CONSTRAINT_NAME"));
            key.setUniqueConstraint(rs.getString("POSITION_IN_UNIQUE_CONSTRAINT"));
            key.setReferenceTableName(rs.getString("REFERENCED_TABLE_NAME"));
            key.setReferenceColumnName(rs.getString("REFERENCED_COLUMN_NAME"));

            keys.add(key);

        }
        releaseConn();

        return keys;

    }


    // 获取所有表
    public List<Table> getTables(String database) throws Exception{

        String  sql = "select table_name,table_comment from information_schema.tables where table_schema=?";
        ResultSet rs = getConn(database, "",sql);

        List<Table> tables = new ArrayList<Table>();
        while(rs.next()){
            Table table = new Table();
            table.setTableName(rs.getString( "table_name"));
            table.setTableCommont(rs.getString("table_comment"));
            tables.add(table);
        }

        releaseConn();
        return  tables;

    }

    // 连接数据库
    private ResultSet getConn(String dataBase,String tableName,String sql){

        try{

            Class.forName(DRIVER);
            conn = DriverManager.getConnection(URL,USER_NAME,PASS_WORD);
            pst = conn.prepareStatement(sql);
            pst.setString(1,dataBase);
            if(!"".equals(tableName)){
                pst.setString(2,tableName);
            }
            rs = pst.executeQuery();
            return  rs;

        }catch (Exception e){
            e.printStackTrace();
        }

        return null;

    }

    // 释放连接
    private void  releaseConn(){
        try{
            if(rs != null ){
                rs.close();
            }
            if(pst != null){
                pst.close();
            }
            if(conn != null){
                conn.close();
            }
        }catch (Exception e){
            e.printStackTrace();
        }
    }


    // 导出数据
    public void report()  throws  Exception{

        Map<String, List<TableColumn>> data = this.getTableColumnData();       // 表名：表体
        List<Table> tables = this.getTables(this.database);         // 表体(列名、类型、注释)
        Map<String,String> tableMap = new HashMap<String,String>();              // 表名:中文名


        //JSONObject json = new JSONObject((HashMap)data);

        for (Table table : tables) {
            tableMap.put(table.getTableName(),table.getTableCommont());
        }

        // 构建表格数据
        XWPFDocument document = new XWPFDocument();
        Integer i = 1;
        for (String tableName : data.keySet()) {
			//获取索引
            List<TableIndex> indices = this.getIndexTable(database,tableName);
            //获取键
            List<TableKey> tableKeys = this.getTableKey(database,tableName);
            XWPFParagraph paragraph = document.createParagraph();                // 创建标题对象
            XWPFRun run = paragraph.createRun();                                 // 创建文本对象
            run.setText((i+"、"+tableName+"    "+tableMap.get(tableName)));      // 标题名称
            run.setFontSize(14);                                                 // 字体大小
            run.setBold(true);                                                   // 字体加粗

            int j = 0;
            XWPFTable columnTable = document.createTable(data.get(tableName).size()+1,6);
            // 第一行
            columnTable.setCellMargins(10,50,10,200);
            columnTable.getRow(j).getCell(0).setText("字段名称");
            columnTable.getRow(j).getCell(1).setText("字段类型");
            columnTable.getRow(j).getCell(2).setText("最大长度");
            columnTable.getRow(j).getCell(3).setText("约束");
            columnTable.getRow(j).getCell(4).setText("为空");
            columnTable.getRow(j).getCell(5).setText("字段含义");
            j++;

            for (TableColumn tableColumn : data.get(tableName)) {

                columnTable.getRow(j).getCell(0).setText(tableColumn.getColumnName());
                columnTable.getRow(j).getCell(1).setText(tableColumn.getColumnType());
                columnTable.getRow(j).getCell(2).setText(tableColumn.getMaxLength());
                columnTable.getRow(j).getCell(3).setText(tableColumn.getColumnKey());
                columnTable.getRow(j).getCell(4).setText(tableColumn.getIsNullable());
                columnTable.getRow(j).getCell(5).setText(tableColumn.getColumnComment());
                j++;

            }
            //创建段落
            XWPFParagraph indexParagraph = document.createParagraph();
            XWPFRun indexTitle = indexParagraph.createRun();                                 // 创建文本对象
            indexTitle.setText("索引清单：");      // 标题名称
            indexTitle.setFontSize(10);                                                 // 字体大小
            indexTitle.setBold(true);
            if (indices.size()>0&&!indices.isEmpty()){
                int k = 0;
                XWPFTable indexTable = document.createTable(indices.size()+1,5);
                // 第一行
                indexTable.setCellMargins(10,50,10,200);
                indexTable.getRow(k).getCell(0).setText("字段名称");
                indexTable.getRow(k).getCell(1).setText("索引名称");
                indexTable.getRow(k).getCell(2).setText("唯一性");
                indexTable.getRow(k).getCell(3).setText("索引类型");
                indexTable.getRow(k).getCell(4).setText("注释");

                k++;

                for (TableIndex index : indices) {

                    indexTable.getRow(k).getCell(0).setText(index.getColumnName());
                    indexTable.getRow(k).getCell(1).setText(index.getIndexName());
                    indexTable.getRow(k).getCell(2).setText(index.getCardinality());
                    indexTable.getRow(k).getCell(3).setText(index.getIndexType());
                    indexTable.getRow(k).getCell(4).setText(index.getIndexComment());
                    k++;

                }
            }else {
                indexTitle.setText("该表没有索引数据");
            }
            //创建段落
            XWPFParagraph keyParagraph = document.createParagraph();
            XWPFRun keyTitle = keyParagraph.createRun();                                 // 创建文本对象
            keyTitle.setText("键清单：");                                            // 标题名称
            keyTitle.setFontSize(10);                                                 // 字体大小
            keyTitle.setBold(true);
            if(tableKeys.size()>0&&!tableKeys.isEmpty()) {

                int x = 0;
                XWPFTable tableKey = document.createTable(tableKeys.size() + 1, 5);
                // 第一行
                tableKey.setCellMargins(10, 50, 10, 200);
                tableKey.getRow(x).getCell(0).setText("字段名称");
                tableKey.getRow(x).getCell(1).setText("约束名称");
                tableKey.getRow(x).getCell(2).setText("唯一性");
                tableKey.getRow(x).getCell(3).setText("引用表名");
                tableKey.getRow(x).getCell(4).setText("引用列名");

                x++;

                for (TableKey keyTab : tableKeys) {

                    tableKey.getRow(x).getCell(0).setText(keyTab.getColumnName());
                    tableKey.getRow(x).getCell(1).setText(keyTab.getConstraintName());
                    tableKey.getRow(x).getCell(2).setText(keyTab.getUniqueConstraint());
                    tableKey.getRow(x).getCell(3).setText(keyTab.getReferenceTableName());
                    tableKey.getRow(x).getCell(4).setText(keyTab.getReferenceColumnName());
                    x++;

                }
            }else {
                keyTitle.setText("该表没有键数据");
            }
            i++;
        }

        // 文档输出
        FileOutputStream out = new FileOutputStream(reportPath + new SimpleDateFormat("yyyyMMddHHmmss").format(new Date()).toString()+"_"+database +".docx");
        document.write(out);
        out.close();
        System.out.println("Word生成完成!!!");

    }

    // 表
    class Table{

        private String tableName;

        private String tableCommont;

        public String getTableName() {
            return tableName;
        }

        public void setTableName(String tableName) {
            this.tableName = tableName;
        }

        public String getTableCommont() {
            return tableCommont;
        }

        public void setTableCommont(String tableCommont) {
            this.tableCommont = tableCommont;
        }

    }

    // 表列信息
    class TableColumn{
        // 表名
        private String tableName;
        // 字段名
        private String columnName;
        // 字段类型
        private String maxLength;
        private String columnType;
        // 字段注释
        private String columnComment;
        // 可否为空
        private String isNullable;
        // 约束
        private String columnKey;

        public String getTableName() {
            return tableName;
        }

        public void setTableName(String tableName) {
            this.tableName = tableName;
        }

        public String getColumnName() {
            return columnName;
        }

        public void setColumnName(String columnName) {
            this.columnName = columnName;
        }

        public String getColumnType() {
            return columnType;
        }

        public void setColumnType(String columnType) {
            this.columnType = columnType;
        }

        public String getMaxLength() {
            return maxLength;
        }

        public void setMaxLength(String maxLength) {
            this.maxLength = maxLength;
        }

        public String getColumnComment() {
            return columnComment;
        }

        public void setColumnComment(String columnComment) {
            this.columnComment = columnComment;
        }

        public String getIsNullable() {
            return isNullable;
        }

        public void setIsNullable(String isNullable) {
            this.isNullable = isNullable;
        }

        public String getColumnKey() {
            return columnKey;
        }

        public void setColumnKey(String columnKey) {
            this.columnKey = columnKey;
        }

    }
    //表索引
    class  TableIndex{
        private String tableName;
        private String indexName;
        private String columnName;
        private String cardinality;
        private String indexType;
        private String indexComment;

        public String getTableName() {
            return tableName;
        }

        public void setTableName(String tableName) {
            this.tableName = tableName;
        }

        public String getIndexName() {
            return indexName;
        }

        public void setIndexName(String indexName) {
            this.indexName = indexName;
        }

        public String getColumnName() {
            return columnName;
        }

        public void setColumnName(String columnName) {
            this.columnName = columnName;
        }

        public String getCardinality() {
            return cardinality;
        }

        public void setCardinality(String cardinality) {
            this.cardinality = cardinality;
        }

        public String getIndexType() {
            return indexType;
        }

        public void setIndexType(String indexType) {
            this.indexType = indexType;
        }

        public String getIndexComment() {
            return indexComment;
        }

        public void setIndexComment(String indexComment) {
            this.indexComment = indexComment;
        }
    }
    //键
    class TableKey{
        private String tableName;
        private String columnName;
        private String constraintName;
        private String uniqueConstraint;
        private String referenceTableName;
        private String referenceColumnName;

        public String getTableName() {
            return tableName;
        }

        public void setTableName(String tableName) {
            this.tableName = tableName;
        }

        public String getColumnName() {
            return columnName;
        }

        public void setColumnName(String columnName) {
            this.columnName = columnName;
        }

        public String getConstraintName() {
            return constraintName;
        }

        public void setConstraintName(String constraintName) {
            this.constraintName = constraintName;
        }

        public String getUniqueConstraint() {
            return uniqueConstraint;
        }

        public void setUniqueConstraint(String uniqueConstraint) {
            this.uniqueConstraint = uniqueConstraint;
        }

        public String getReferenceTableName() {
            return referenceTableName;
        }

        public void setReferenceTableName(String referenceTableName) {
            this.referenceTableName = referenceTableName;
        }

        public String getReferenceColumnName() {
            return referenceColumnName;
        }

        public void setReferenceColumnName(String referenceColumnName) {
            this.referenceColumnName = referenceColumnName;
        }
    }

}

运行结果如下