欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

工具类自动生成数据库表结构数据库字典

程序员文章站 2022-03-15 19:36:56
...

提示:文章写完后,目录可以自动生成,如何生成可参考右边的帮助文档

文章目录


前言

项目开发完毕后,需要写文档,其中有一个就是整理数据库的表结构,一个一个手写太麻烦了,网上找了一个工具类自动生成excel文档


代码如下(示例):

 package com.guidao.pathwayManage.web.util;
import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.HashMap;
import java.util.Map;
import java.util.Set;

import org.apache.commons.collections4.MapUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * 生成数据库数据结构速查文件(数据库字典)
 * 

 */
public class ProduceGuideOfDatabase {

   
    public static void main(String[] args) {
        System.out.println("开始运行程序。。。");
        long preTime = System.currentTimeMillis();
        // 程序访问数据库拉取字典数据-程序整合成字典文件(配置数据库连接、要拉取得库名,一键运行代码即可)
        reArrangeFromSQL();
        System.out.println("运行完成,耗时:" + (System.currentTimeMillis() - preTime) + "ms");
    }

    /**
     * 直接从SQL中读取数据进行重整成excel
     */
    private static void reArrangeFromSQL() {
        String ip = "192.168.1.xxx", user = "数据库用户", password = "密码", database = "information_schema";
        Map<String, Map<String, TablePojo>> database_tables = new HashMap<>();
        try {
            String sqlStr = "SELECT pretab.TABLE_NAME AS 表名,pretab.TABLE_COMMENT AS 表释义,precol.COLUMN_NAME AS 字段名,precol.COLUMN_TYPE AS 字段类型, precol.COLUMN_DEFAULT AS 字段默认值,precol.COLUMN_COMMENT AS 表字段释义 FROM information_schema.`TABLES` AS pretab RIGHT JOIN information_schema.`COLUMNS` AS precol ON precol.TABLE_NAME=pretab.TABLE_NAME WHERE pretab.TABLE_SCHEMA =? GROUP BY precol.TABLE_NAME,precol.COLUMN_NAME ORDER BY precol.TABLE_NAME;";
            Connection connection = getConnection(ip, user, password, database);
            PreparedStatement pstmt = connection.prepareStatement(sqlStr);
            ResultSet rs = null;
            String[] databaseNames = "数据库名"
                    .split("-");
            for (String databaseName : databaseNames) {
                pstmt.setString(1, databaseName);
                rs = pstmt.executeQuery();// 获取数据
                String columnLines = "";
                int countAll = 0;// 表总数
                Map<String, TablePojo> tableNames = new HashMap<>();
                String preTableName = "";
                String preTableComment = "";
                while (rs.next()) {
                    String currentTableName = isBlank(rs.getString(1)) ? "" : rs.getString(1);
                    if (tableNames.containsKey(getRealTablename(currentTableName))) {
                        continue;
                    }
                    String currentTableComment = isBlank(rs.getString(2)) ? "" : rs.getString(2);
                    String currentColumnName = isBlank(rs.getString(3)) ? "" : rs.getString(3);
                    String currentColumnType = isBlank(rs.getString(4)) ? "" : rs.getString(4);
                    String currentColumnDefault = isBlank(rs.getString(5)) ? "" : rs.getString(5);
                    String currentColumnComment = isBlank(rs.getString(6)) ? "" : rs.getString(6);
                    if (currentTableName.equals(preTableName)) {
                        columnLines += currentColumnName + "#" + currentColumnType + "#" + currentColumnDefault + "#"
                                + currentColumnComment + "@";
                        continue;
                    }
                    if (countAll != 0 && !tableNames.containsKey(getRealTablename(preTableName))) {
                        TablePojo tablePojo = new TablePojo(preTableName, preTableComment, columnLines.substring(0,
                                columnLines.length() - 1));
                        tableNames.put(getRealTablename(preTableName), tablePojo);
                    }
                    countAll++;
                    columnLines = currentColumnName + "#" + currentColumnType + "#" + currentColumnDefault + "#"
                            + currentColumnComment + "@";
                    preTableName = currentTableName;
                    preTableComment = currentTableComment;
                }
                // 最后一组数据判断+保存
               /* if (!tableNames.containsKey(getRealTablename(preTableName))) {
                    TablePojo tablePojo = new TablePojo(preTableName, preTableComment, columnLines.substring(0,
                            columnLines.length() - 1));
                    tableNames.put(getRealTablename(preTableName), tablePojo);
                }*/
                database_tables.put(databaseName, tableNames);
            }
            rs.close();
            pstmt.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        String url = "C:\\Users\\liuyifeng\\Desktop\\";
        String forFile = "22222.xlsx";
        if (MapUtils.isNotEmpty(database_tables)) {
            if (forFile.contains(".xlsx")) {
                arrangeToXLSX(database_tables, url, forFile);
            } else {
                arrangeToXLS(database_tables, url, forFile);
            }
        }
    }

    /**
     * 取数据整合到excel-xls
     * @param tableNamesMap
     * @param fos
     */
    private static void arrangeToXLS(Map<String, Map<String, TablePojo>> database_tables, String url, String forFile) {
        try (FileOutputStream fos = new FileOutputStream(url + forFile);) {
            if (MapUtils.isNotEmpty(database_tables)) {
                HSSFWorkbook currentWorkbook = new HSSFWorkbook();
                // 获取所有样式
                Map<String, CellStyle> cellStyles = getCellStyles(currentWorkbook);
                Set<String> databaseNames = database_tables.keySet();
                for (String databaseName : databaseNames) {
                    HSSFSheet currentSheet = currentWorkbook.createSheet(databaseName);
                    HSSFRow currentRow = null;
                    HSSFCell currentCell = null;
                    int rowIndex = -1;
                    Map<String, TablePojo> tableNames = database_tables.get(databaseName);
                    for (TablePojo tablePojo : tableNames.values()) {
                        // 空行
                        currentSheet.createRow(++rowIndex);
                        // 表头
                        currentRow = currentSheet.createRow(++rowIndex);
                        currentRow.setHeightInPoints(18);
                        currentCell = currentRow.createCell(0);
                        currentCell.setCellStyle(cellStyles.get("bluesStyle"));
                        currentCell.setCellValue(tablePojo.getTableName() + "(" + tablePojo.getTableComment() + ")");
                        CellRangeAddress region = new CellRangeAddress(rowIndex, rowIndex, 0, 3);
                        currentSheet.addMergedRegion(region);
                        // 表-标题栏
                        currentRow = currentSheet.createRow(++rowIndex);
                        currentRow.setHeightInPoints(18);
                        currentCell = currentRow.createCell(0);
                        currentCell.setCellStyle(cellStyles.get("blueStyle"));
                        currentCell.setCellValue("字段名");
                        currentCell = currentRow.createCell(1);
                        currentCell.setCellStyle(cellStyles.get("blueStyle"));
                        currentCell.setCellValue("类型");
                        currentCell = currentRow.createCell(2);
                        currentCell.setCellStyle(cellStyles.get("blueStyle"));
                        currentCell.setCellValue("默认值");
                        currentCell = currentRow.createCell(3);
                        currentCell.setCellStyle(cellStyles.get("blueStyle"));
                        currentCell.setCellValue("描述");
                        // 表字段
                        String tableColumnsStr = tablePojo.getTableColumns();
                        for (String tableColumns : tableColumnsStr.split("@")) {
                            currentRow = currentSheet.createRow(++rowIndex);
                            currentRow.setHeightInPoints(18);
                            String[] tableColumnArr = tableColumns.split("#");
                            for (int i = 0; i < tableColumnArr.length; i++) {
                                currentCell = currentRow.createCell(i);
                                currentCell.setCellStyle(cellStyles.get("baseStyle"));
                                currentCell.setCellValue(tableColumnArr[i]);
                            }
                        }
                    }
                }
                currentWorkbook.write(fos);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 取数据整合到excel-xlsx
     */
    private static void arrangeToXLSX(Map<String, Map<String, TablePojo>> database_tables, String url, String forFile) {
        try (FileOutputStream fos = new FileOutputStream(url + forFile);) {
            if (MapUtils.isNotEmpty(database_tables)) {
                XSSFWorkbook currentWorkbook = new XSSFWorkbook();
                // 获取所有样式
                Map<String, CellStyle> cellStyles = getCellStyles(currentWorkbook);
                Set<String> databaseNames = database_tables.keySet();
                for (String databaseName : databaseNames) {
                    XSSFSheet currentSheet = currentWorkbook.createSheet(databaseName);
                    XSSFRow currentRow = null;
                    XSSFCell currentCell = null;
                    int rowIndex = -1;
                    Map<String, TablePojo> tableNames = database_tables.get(databaseName);
                    for (TablePojo tablePojo : tableNames.values()) {
                        // 空行
                        currentSheet.createRow(++rowIndex);
                        // 表头
                        currentRow = currentSheet.createRow(++rowIndex);
                        currentRow.setHeightInPoints(18);
                        currentCell = currentRow.createCell(0);
                        currentCell.setCellStyle(cellStyles.get("bluesStyle"));
                        currentCell.setCellValue(tablePojo.getTableName() + "(" + tablePojo.getTableComment() + ")");
                        CellRangeAddress region = new CellRangeAddress(rowIndex, rowIndex, 0, 3);
                        currentSheet.addMergedRegion(region);
                        // 表-标题栏
                        currentRow = currentSheet.createRow(++rowIndex);
                        currentRow.setHeightInPoints(18);
                        currentCell = currentRow.createCell(0);
                        currentCell.setCellStyle(cellStyles.get("blueStyle"));
                        currentCell.setCellValue("字段名");
                        currentCell = currentRow.createCell(1);
                        currentCell.setCellStyle(cellStyles.get("blueStyle"));
                        currentCell.setCellValue("类型");
                        currentCell = currentRow.createCell(2);
                        currentCell.setCellStyle(cellStyles.get("blueStyle"));
                        currentCell.setCellValue("默认值");
                        currentCell = currentRow.createCell(3);
                        currentCell.setCellStyle(cellStyles.get("blueStyle"));
                        currentCell.setCellValue("描述");
                        // 表字段
                        String tableColumnsStr = tablePojo.getTableColumns();
                        for (String tableColumns : tableColumnsStr.split("@")) {
                            currentRow = currentSheet.createRow(++rowIndex);
                            currentRow.setHeightInPoints(18);
                            String[] tableColumnArr = tableColumns.split("#");
                            for (int i = 0; i < tableColumnArr.length; i++) {
                                currentCell = currentRow.createCell(i);
                                currentCell.setCellStyle(cellStyles.get("baseStyle"));
                                currentCell.setCellValue(tableColumnArr[i]);
                            }
                        }
                    }
                }
                currentWorkbook.write(fos);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 样式集锦
     * 
     * @author ruran
     */
    private static Map<String, CellStyle> getCellStyles(Workbook workbook) {
        // 实线边框
        // style1.setBorderTop(BorderStyle.THIN);
        // style1.setBorderBottom(BorderStyle.THIN);
        // style1.setBorderLeft(BorderStyle.THIN);
        // style1.setBorderRight(BorderStyle.THIN);
        // 设置自动换行
        // baseStyle.setWrapText(true);

        Map<String, CellStyle> cellStylesMap = new HashMap<>();
        // baseStyle
        CellStyle baseStyle = workbook.createCellStyle();
        // 水平对齐方式
        baseStyle.setAlignment(HorizontalAlignment.LEFT);
        // 垂直对齐方式
        baseStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 宋体设置
        Font baseFont = workbook.createFont();
        baseFont.setFontName("宋体");
        baseStyle.setFont(baseFont);
        cellStylesMap.put("baseStyle", baseStyle);// 存放样式-baseStyle

        // 深蓝色底部、白色字体、加粗
        CellStyle bluesStyle = workbook.createCellStyle();
        bluesStyle.cloneStyleFrom(cellStylesMap.get("baseStyle"));// 继承某样式
        // 背景色
        bluesStyle.setFillForegroundColor(IndexedColors.ROYAL_BLUE.getIndex());
        bluesStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);// 这一行是必须的,不然会得不到想要的结果
        // 白色加粗字体
        Font bluesFont = workbook.createFont();
        bluesFont.setColor(IndexedColors.WHITE.getIndex());
        bluesFont.setBold(true);
        bluesFont.setFontName("宋体");
        bluesStyle.setFont(bluesFont);
        cellStylesMap.put("bluesStyle", bluesStyle);// 存放样式-bluesStyle

        // 浅蓝色底部
        CellStyle blueStyle = workbook.createCellStyle();
        blueStyle.cloneStyleFrom(cellStylesMap.get("baseStyle"));// 继承某样式
        // 背景色
        blueStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
        blueStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);// 这一行是必须的,不然会得不到想要的结果
        cellStylesMap.put("blueStyle", blueStyle);// 存放样式-blueStyle

        return cellStylesMap;
    }

    /**
     * 字符串判非空
     */
    private static boolean isNotBlank(String str) {
        if (null == str) {
            return false;
        }
        if (str.trim().length() == 0) {
            return false;
        }
        return true;
    }

    /**
     * 字符串判非空
     * 
     * @author ruran
     */
    private static boolean isBlank(String str) {
        if (null == str) {
            return true;
        }
        if (str.trim().length() == 0) {
            return true;
        }
        return false;
    }

    /**
     * 获取真实的表名 - 逻辑是去除末尾的数字
     * 
     * @author ruran
     * @param tableName
     * @return
     */
    private static String getRealTablename(String tableName) {
        if (isBlank(tableName)) {
            return null;
        }
        return tableName.replaceAll("\\d+$", ""); 
        }
    
    /**
     * 获取数据连接
     * 
     * @author ruran
     * @param database
     * @return
     */
    private static Connection getConnection(String ip, String user, String password, String database) {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            System.out.println("成功加载MySQL驱动程序...");
            Connection connention = DriverManager.getConnection("jdbc:mysql://" + ip + ":3306/" + database, user,
                    password);
            System.out.println("成功建立MySQL连接...");
            return connention;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 表数据内部类
     * 
     * @author ruran
     */
    @SuppressWarnings("unused")
    private static class TablePojo {
        String tableName = "";
        String tableComment = "";
        String tableColumns = "";

        public TablePojo() {

        }

        public TablePojo(String tablename, String tablecomment, String tablecolumns) {
            tableName = tablename;
            tableComment = tablecomment;
            tableColumns = tablecolumns;
        }

        public String getTableName() {
            return tableName;
        }

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

        public String getTableComment() {
            return tableComment;
        }

        public void setTableComment(String tableComment) {
            this.tableComment = tableComment;
        }

        public String getTableColumns() {
            return tableColumns;
        }

       public void setTableColumns(String tableColumns) {
             this.tableColumns = tableColumns;
        }
       }
    }
相关标签: 代码/工具类片段