java实现数据库字典生成 话不多说 代码奉上

java实现数据库字典生成 word格式 话不多说 代码奉上

对源码不感兴趣的可以直接点击下方“在线生成数据库字典"再生生成word：
在线生成数据库字典

只需要运行main方法即可生成 非常滴方便 word格式

示例


csdn源码

main方法

package com.hm.main;

import com.hm.bean.ColumnInfo;
import com.hm.bean.ColumnNameEnum;
import com.hm.bean.DataDirectory;
import com.hm.service.DataDirectoryService;
import org.springframework.context.support.AbstractApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import java.util.LinkedHashMap;

public class DataDirectoryGenerateMain {
    public static void main(String[] args){
        AbstractApplicationContext context =
                new ClassPathXmlApplicationContext("applicationContext.xml");
        DataDirectoryService service = context.getBean(DataDirectoryService.class);

        DataDirectory dataDirectory = new DataDirectory();
        //存储的地方
        dataDirectory.setBaseFielPath("F:\\");
        //
        dataDirectory.setDatabaseName("wnxg");
        System.out.println("存储路径："+dataDirectory.getBaseFielPath());
        System.out.println("数据库："+dataDirectory.getDatabaseName()+" 数据库字典生成！");
        LinkedHashMap<ColumnNameEnum, ColumnInfo> columnInfoMap = new LinkedHashMap<ColumnNameEnum, ColumnInfo>();
        columnInfoMap.put(ColumnNameEnum.COLUMN_NAME, new ColumnInfo("字段名", 15));
        columnInfoMap.put(ColumnNameEnum.COLUMN_TYPE, new ColumnInfo("数据类型", 15));
        columnInfoMap.put(ColumnNameEnum.IS_NULLABLE, new ColumnInfo("允许为空", 15));
        columnInfoMap.put(ColumnNameEnum.COLUMN_COMMENT, new ColumnInfo("字段说明", 15));
        columnInfoMap.put(ColumnNameEnum.COLUMN_DEFAULT, new ColumnInfo("默认值", 15));
        columnInfoMap.put(ColumnNameEnum.COLUMN_KEY, new ColumnInfo("键值", 15));
        dataDirectory.setTableHeader(columnInfoMap);

        service.generateDataDirectory(dataDirectory);
    }
}

service层
package com.hm.service;

import com.hm.bean.ColumnInfo;
import com.hm.bean.DataDirectory;
import com.hm.dao.DataDirectoryDAO;
import com.hm.util.POIWordUtil;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.apache.poi.xwpf.usermodel.*;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

@Service
public class DataDirectoryService {

private static Logger log = Logger.getLogger(DataDirectoryService.class);

@Resource
DataDirectoryDAO dataDirectoryDAO;

/**
 * 生成word版本数据字典的调用入口
 *
 * @param dataDirectory
 */
public void generateDataDirectory(DataDirectory dataDirectory) {
    if (dataDirectory.getTableHeader().size() == 0) {
        log.info("改错了吧，不要随便编辑字段宽度");
        return;
    }

    log.info("阿西吧！我要放大招了");
    Map<String, String> tableCommentMap = dataDirectoryDAO.getTableComment(dataDirectory.getDatabaseName());
    Map<String, List<String>> tableColumnsMap = dataDirectoryDAO.getTableColumnsInfo(dataDirectory);
    generatedoc(dataDirectory, tableColumnsMap, tableCommentMap);
}

private void generatedoc(DataDirectory dataDirectory, Map<String, List<String>> tableMap, Map<String, String> tableCommentMap) {
    String databaseName = dataDirectory.getDatabaseName();
    String filePath = dataDirectory.getBaseFielPath() + databaseName + "数据库字典.docx";

    OutputStream out = null;
    try {
        XWPFDocument doc = new XWPFDocument();

        //设置标题
        POIWordUtil.setTitle(doc, databaseName + "数据库字典", 20, ParagraphAlignment.CENTER);
        //换行三次
        POIWordUtil.nextLine(doc, 3);

        Iterator iterators = tableMap.entrySet().iterator();
        while (iterators.hasNext()) {
            Map.Entry entry = (Map.Entry) iterators.next();
            String table_name = (String) entry.getKey();
            List<String> columnList = (List<String>) entry.getValue();

            String tableNameStr = table_name;
            if (StringUtils.isNotEmpty(tableCommentMap.get(table_name))) {
                tableNameStr += "【" + tableCommentMap.get(table_name) + "】";
            }
            //插入表名
            POIWordUtil.newParagraphText(doc, tableNameStr);
            POIWordUtil.nextLine(doc, 0);
        }
        Iterator iterator = tableMap.entrySet().iterator();
        while (iterator.hasNext()) {
            Map.Entry entry = (Map.Entry) iterator.next();
            String table_name = (String) entry.getKey();
            List<String> columnList = (List<String>) entry.getValue();

            String tableNameStr = table_name;
            if (StringUtils.isNotEmpty(tableCommentMap.get(table_name))) {
                tableNameStr += "【" + tableCommentMap.get(table_name) + "】";
            }
            //插入表名
            POIWordUtil.newParagraphText(doc, tableNameStr);

            //创建指定行列的表格
            XWPFTable table = POIWordUtil.createTable(doc, columnList.size() + 1, dataDirectory.getTableHeader().size());

            //设置表头，列宽
            XWPFTableRow rowHeader = table.getRow(0);
            Iterator headeriterator = dataDirectory.getTableHeader().entrySet().iterator();
            for (int i = 0; headeriterator.hasNext(); i++) {
                Map.Entry headerEntry = (Map.Entry) headeriterator.next();
                ColumnInfo columnInfo = (ColumnInfo) headerEntry.getValue();


                String width = (int) (9000 * ((double) columnInfo.getWidthPercent() / 90)) + "";

                setTableCell(rowHeader, i, columnInfo.getDesc(), width);
            }

            //插入单元格文本
            for (int i = 0; i < columnList.size(); i++) {
                String[] columninfos = columnList.get(i).split("@");
                XWPFTableRow row = table.getRow(i + 1);
                for (int j = 0; j < columninfos.length; j++) {
                    setTableCell(row, j, columninfos[j], "0");
                }
            }
            POIWordUtil.nextLine(doc, 1);
        }
        log.info("你好有缘人，快去你自己设置的路径看看吧！！！");
        out = new FileOutputStream(new File(filePath));
        doc.write(out);
    } catch (Exception e) {
        log.error("", e);
    }
}

public static void setTableCell(XWPFTableRow row, int CellPos, String text, String width) {
    XWPFTableCell cell = row.getCell(CellPos);
    POIWordUtil.setTableCellTextCenter(cell);
    if (!"0".equals(width)) {
        POIWordUtil.setTableCellWidth(cell, width);

    }
    cell.setText(text);
}

}

dao层

package com.hm.dao;

import com.hm.bean.ColumnNameEnum;
import com.hm.bean.DataDirectory;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

@Repository("mysql_dao")
public class DataDirectoryDAO {
    private static Logger log = Logger.getLogger(DataDirectoryDAO.class);

    @Resource(name = "dataSource")
    public DataSource dataSource;

    /**
     * 获取表字段信息
     *
     * @param dataDirectory
     * @return
     */
    public Map<String, List<String>> getTableColumnsInfo(DataDirectory dataDirectory) {
        Map<String, List<String>> map = new LinkedHashMap<String, List<String>>();

        String databaseName = dataDirectory.getDatabaseName();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "select table_name from information_schema.Tables where table_schema=?";
        try {
            conn = dataSource.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1, databaseName);
            rs = ps.executeQuery();
            while (rs.next()) {
                String table_name = rs.getString("table_name");
                map.put(table_name, getColumnsInfo(dataDirectory, table_name));
            }
        } catch (SQLException e) {
            log.error("get connection error!", e);
        } finally {
            freeConnection(ps, conn);
        }
        return map;
    }

    private List<String> getColumnsInfo(DataDirectory dataDirectory, String table_name) {
        List<String> list = new ArrayList<String>();

        String databaseName = dataDirectory.getDatabaseName();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "select " + dataDirectory.getColumnSql() + " from information_schema.COLUMNS where table_schema=? and table_name=?";

        try {
            conn = dataSource.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1, databaseName);
            ps.setString(2, table_name);
            rs = ps.executeQuery();
            while (rs.next()) {
                StringBuffer sb = new StringBuffer("");
                Iterator<ColumnNameEnum> iterator = dataDirectory.getTableHeader().keySet().iterator();
                while(iterator.hasNext()){
                    sb.append(isEmptyReplaceStr(rs.getString(iterator.next().name()), "-")).append("@");
                }
                list.add(sb.delete(sb.length()-1, sb.length()).toString());
            }
        } catch (SQLException e) {
            log.error("get connection error!", e);
        } finally {
            freeConnection(ps, conn);
        }
        return list;
    }

    /**
     * 获取表的注释
     *
     * @param databaeName
     * @return
     */
    public Map<String, String> getTableComment(String databaeName) {
        Map<String, String> tableCommentMap = new HashMap<String, String>();

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "select table_name, table_comment " +
                "from information_schema.TABLES where table_schema=?";

        try {
            conn = dataSource.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1, databaeName);
            rs = ps.executeQuery();
            while (rs.next()) {
                String table_name = isEmptyReplaceStr(rs.getString("table_name"), "");
                String table_comment = isEmptyReplaceStr(rs.getString("table_comment"), "");
                tableCommentMap.put(table_name, table_comment);
            }
        } catch (SQLException e) {
            log.error("get connection error!", e);
        } finally {
            freeConnection(ps, conn);
        }
        return tableCommentMap;
    }

    private String isEmptyReplaceStr(String str, String replaceStr) {
        if (StringUtils.isEmpty(str)) {
            return replaceStr;
        }
        return str;
    }

    private void freeConnection(PreparedStatement ps, Connection conn) {
        try {
            if (ps != null) {
                ps.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            log.error("close connection error!", e);
        }
    }
}

工具类
package com.hm.util;

import org.apache.poi.xwpf.usermodel.*;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.*;
import java.math.BigInteger;

public class POIWordUtil {

    /**
     * 换行设置
     *
     * @param doc
     * @param times 换几行
     */
    public static void nextLine(XWPFDocument doc, int times) {
        for (int i = 0; i < times; i++) {
            XWPFParagraph nextLine = doc.createParagraph();
            nextLine.createRun().setText("\r");
        }
    }

    /**
     * 设置标题
     *
     * @param doc
     * @param text
     * @param fontSize
     * @param align
     */
    public static void setTitle(XWPFDocument doc, String text, int fontSize, ParagraphAlignment align) {
        XWPFParagraph titleParagraph = doc.createParagraph();
        titleParagraph.setAlignment(align);
        XWPFRun titleRun = titleParagraph.createRun();
        titleRun.setText(text);
        titleRun.setFontSize(fontSize);
    }

    /**
     * 新开一个段落，插入文本
     *
     * @param doc
     * @param text
     */
    public static void newParagraphText(XWPFDocument doc, String text) {
        XWPFParagraph xwpfParagraph = doc.createParagraph();
        xwpfParagraph.createRun().setText(text);
    }

    public static XWPFTable createTable(XWPFDocument doc, int rows, int cols) {
        XWPFTable table = doc.createTable(rows, cols);
        CTTblPr tablePr = table.getCTTbl().getTblPr();
        tablePr.getTblW().setType(STTblWidth.DXA);
        tablePr.getTblW().setW(new BigInteger("9000"));
        return table;
    }

    /**
     * 设置表格中单元格的宽度
     *
     * @param cell
     * @param width
     */
    public static void setTableCellWidth(XWPFTableCell cell, String width) {
        CTTc cttc = cell.getCTTc();
        CTTblWidth ctTblWidth = cttc.addNewTcPr().addNewTcW();
        ctTblWidth.setType(STTblWidth.DXA);
        ctTblWidth.setW(new BigInteger(width));
    }

    /**
     * 设置单元格文本居中
     *
     * @param cell
     */
    public static void setTableCellTextCenter(XWPFTableCell cell) {
        CTTc cttc = cell.getCTTc();
        CTTcPr ctPr = cttc.addNewTcPr();
        ctPr.addNewVAlign().setVal(STVerticalJc.CENTER);
        cttc.getPList().get(0).addNewPPr().addNewJc().setVal(STJc.CENTER);
    }
}








package com.hm.bean;

/**
 * class desc:自定义和数据库字段名对应的描述信息，用于表头显示
 * <p>
 * create time:2019-2-21 00:00:00
 *
 * @version 1.0.0
 */
public class ColumnInfo {
    private String desc; //描述
    private int widthPercent; //宽度占比以0~100的数字表示

    public ColumnInfo(String desc, int widthPercent) {
        this.desc = desc;
        this.widthPercent = widthPercent;
    }

    public String getDesc() {
        return desc;
    }

    public void setDesc(String desc) {
        this.desc = desc;
    }

    public int getWidthPercent() {
        return widthPercent;
    }

    public void setWidthPercent(int widthPercent) {
        this.widthPercent = widthPercent;
    }
}




[源码文件](https://download.youkuaiyun.com/download/qq_41071241/12068774)

有不懂得尽管问  有时间会回复