1200字范文 > 导出数据库表结构word文档

导出数据库表结构word文档

时间：2021-03-27 04:17:22

相关推荐

导出数据库表结构word文档

前言

导出数据库表结构到word文档。

效果展示

表

CREATE TABLE `user_info` (`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',`refer_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '关联id',`phone` char(11) NOT NULL DEFAULT '' COMMENT '手机号',`name` varchar(32) NOT NULL DEFAULT '' COMMENT '名称',`age` int(2) NOT NULL DEFAULT '0' COMMENT '年龄',`salary` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '值',`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',`is_deleted` tinyint(2) NOT NULL DEFAULT '0' COMMENT '删除标识(0: 正常,1: 已删除)',PRIMARY KEY (`id`),KEY `idx_phone` (`phone`) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

word文档

废话不多说，直接上代码

Test.java

import mons.collections4.CollectionUtils;import org.apache.poi.xwpf.usermodel.*;import org.springframework.util.StringUtils;import java.io.File;import java.io.FileOutputStream;import java.io.IOException;import java.util.List;public class Test {public static void main(String[] args) {XWPFDocument doc = new XWPFDocument();XWPFParagraph paragraph_title = doc.createParagraph();XWPFRun run_title = paragraph_title.createRun();run_title.setText("数据库表设计文档");run_title.setFontSize(16);run_title.setBold(true);FileOutputStream out = null;try {//数据库名String dbName = "dbName";//获取指定数据库的所有表信息List<Table> tableInfos = TableUtil.getTableInfos(dbName);if (CollectionUtils.isNotEmpty(tableInfos)) {for (int i = 0; i < tableInfos.size(); i++) {makeTable(doc, tableInfos.get(i), i);}}String filePath = "D:/数据库表设计文档.docx";out = new FileOutputStream(new File(filePath));doc.write(out);} catch (Exception e) {e.printStackTrace();} finally {if (out != null) {try {out.close();} catch (IOException e) {e.printStackTrace();}}}}/*** 制作表格** @param doc* @param tableInfo* @param pos*/private static void makeTable(XWPFDocument doc, Table tableInfo, Integer pos) {//换行XWPFParagraph paragraph_linebreak = doc.createParagraph();XWPFRun run_linebreak = paragraph_linebreak.createRun();run_linebreak.addBreak();run_linebreak.setBold(true);//表名XWPFParagraph paragraph_tablename = doc.createParagraph();XWPFRun run_tablename = paragraph_tablename.createRun();String tableName = "";if (StringUtils.isEmpty(tableInfo.getTABLE_COMMENT())) {tableName = "表名" + tableInfo.getTABLE_NAME();} else {tableName = "表名" + tableInfo.getTABLE_NAME() + "(" + tableInfo.getTABLE_COMMENT() + ")";}run_tablename.setText(tableName);run_tablename.setFontSize(14);run_tablename.setBold(true);//字段集合List<Column> columns = tableInfo.getColumns();//行数(字段数 + 标题行)int rows = columns.size() + 1;//列数int cols = 6;//创建表XWPFTable table = doc.createTable(rows, 6);//宽度table.setWidth(8000);//获取第 1 行XWPFTableRow row_title = table.getRow(0);//设置第 1 行单元格内容row_title.getCell(0).setText("字段名");row_title.getCell(1).setText("字段说明");row_title.getCell(2).setText("数据类型");row_title.getCell(3).setText("是否可为空");row_title.getCell(4).setText("默认值");row_title.getCell(5).setText("索引");//设置第 1 行单元格背景色for (int i = 0; i < cols; i++) {row_title.getCell(i).setColor("97FFFF");}//将数据库表字段信息写入表格中for (int i = 0; i < columns.size(); i++) {Column column = columns.get(i);XWPFTableRow row = table.getRow(i + 1);row.getCell(0).setText(column.getCOLUMN_NAME());row.getCell(1).setText(column.getCOLUMN_COMMENT());row.getCell(2).setText(column.getCOLUMN_TYPE());row.getCell(3).setText(column.getIS_NULLABLE());row.getCell(4).setText(column.getCOLUMN_DEFAULT());row.getCell(5).setText(column.getCOLUMN_KEY());}doc.setTable(pos, table);}}

TableUtil.groovy

import groovy.sql.Sql;class TableUtil {/*** 获取指定数据库的所有表信息** @param dbName* @return*/static List<Table> getTableInfos(String dbName) {Sql sql = Sql.newInstance("jdbc:mysql://127.0.0.1:3306/" + dbName + "?characterEncoding=UTF-8&useCursorFetch=true&defaultFetchSize=1000&useOldAliasMetadataBehavior=true","user","password")def resultOfTables = sql.rows("SELECT t.TABLE_NAME,t.TABLE_COMMENT,t.ENGINE \n" +"\tFROM INFORMATION_SCHEMA.TABLES t \n" +"\tWHERE t.TABLE_SCHEMA='" + dbName + "';")def tables = new ArrayList<Table>()if (resultOfTables) {for (int i = 0; i < resultOfTables.size(); i++) {Table table = resultOfTables[i]def resultOfColumns = sql.rows("SELECT c.COLUMN_NAME,c.COLUMN_TYPE,c.COLUMN_COMMENT,c.COLUMN_DEFAULT,c.COLUMN_KEY,c.IS_NULLABLE\n" +"\tFROM INFORMATION_SCHEMA.COLUMNS c \n" +"\tWHERE c.TABLE_SCHEMA='" + dbName + "' AND c.TABLE_NAME=?; ", [table.getTABLE_NAME()])if (resultOfColumns) {def columns = new ArrayList<Column>()for (int j = 0; j < resultOfColumns.size(); j++) {Column column = resultOfColumns[j]columns.add(column)}table.setColumns(columns)}tables.add(table)}}return tables}}

Table.java

import lombok.Data;import java.util.List;@Datapublic class Table {private String TABLE_NAME;private String TABLE_COMMENT;private String ENGINE;private List<Column> columns;}

Column.java

import lombok.Data;@Datapublic class Column {private String COLUMN_NAME;private String COLUMN_TYPE;private String COLUMN_COMMENT;private String COLUMN_DEFAULT;private String COLUMN_KEY;private String IS_NULLABLE;}