前言:最近老大急需出一份数据库设计文档,急到只有半天时间,然后呢,几十上百张表,不知道要写到什么时候,想了想有没有什么方式偷懒一波,又想了一下,数据库表设计的时候,每张表和字段都是加了注释了的,对!就是这个好习惯,于是,我有办法,废话少说,上步骤和代码,基础思路是springboot+mybatis+POI(吐槽一下,网上烂大街的博客,基本没见到POI代码标识版本的,看到就窝火,因为POI版本经常性的不向下兼容)
1.使用idea创建一个springboot,依赖和构建使用的是gradle;
2.创建完成后的,gradle依赖如下:
compile 'org.mybatis.spring.boot:mybatis-spring-boot-starter:1.1.1'
compile group: 'mysql', name: 'mysql-connector-java', version: '8.0.12'
compile group: 'org.apache.poi', name: 'poi', version: '3.16'
compile group: 'org.apache.commons', name: 'commons-lang3', version: '3.8.1'
compile group: 'org.docx4j', name: 'docx4j', version: '6.1.0'
compile group: 'org.apache.poi', name: 'ooxml-schemas', version: '1.4'
compile group: 'org.apache.poi', name: 'poi-ooxml', version: '3.16'
compile group: 'org.apache.poi', name: 'poi-ooxml-schemas', version: '3.16'
compile group: 'org.apache.xmlbeans', name: 'xmlbeans', version: '3.0.2'
compile group: 'org.apache.xmlbeans', name: 'xmlbeans-xmlpublic', version: '2.6.0'
3.将resources文件夹下的application.properties后缀名更改为yml,然后在yml文件中添加如下信息:
spring:
datasource:
# jdbc 配置
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://您数据库的IP地址:端口/information_schema?useUnicode=true&serverTimezone=Asia/Shanghai&useSSL=false&characterEncoding=UTF-8
username: 用户名
password: 密码
# 连接池配置,说明请参考Druid Wiki,配置_DruidDataSource参考配置
# druid:
# initialSize: 1
# minIdle: 1
# maxActive: 20
# maxWait: {}
# timeBetweenEvictionRunsMillis: {}
# minEvictableIdleTimeMillis: {}
# validationQuery: {}
# testWhileIdle: {}
# testOnBorrow: {}
# testOnReturn: {}
# poolPreparedStatements: {}
# maxPoolPreparedStatementPerConnectionSize: {}
# active devtools
devtools:
restart:
enabled: true
# date format config
jackson:
date-format: yyyy-MM-dd HH:mm:ss
time-zone: GMT+8
output:
ansi:
enabled: always
date-format: yyyy-MM-dd
# mybatis config
mybatis:
type-aliases-package: com.iwiteks.house.entity # 指的是实体所在的包名
mapper-locations[0]: classpath:mapperxmls/*.xml
server:
port: 8088
4.先上工程结构图,避免乱了看博客的人的思路
4.1启动类添加mybatis扫描:
@MapperScan("com.iwiteks.tools")
完整代码如下:
package com.iwiteks.tools;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
@MapperScan("com.iwiteks.tools")
public class IwiteksToolsApplication {
public static void main(String[] args) {
SpringApplication.run(IwiteksToolsApplication.class, args);
}
}
4.2先编写mybatis xml sql
TableDao.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.iwiteks.tools.dao.TableDao">
<!-- 代码生成工具生成(结果集) -->
<resultMap id="AllColumnMap" type="com.iwiteks.tools.entity.Table">
<result column="table_name" property="tableName"/>
<result column="table_comment" property="tableComment"/>
<result column="engine" property="engine"/>
</resultMap>
<!-- 代码生成工具生成(对应表字段的sql片段) -->
<sql id="all_column">
`table_name`,
table_comment,
engine
</sql>
<!-- 代码生成工具生成(查询数据,其中pojo为参数过滤对象) -->
<select id="select" resultMap="AllColumnMap">
SELECT <include refid="all_column"/>
FROM tables
<where>
table_schema = #{dbName}
</where>
</select>
</mapper>
ColumnsDao.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.iwiteks.tools.dao.TableDao">
<!-- 代码生成工具生成(结果集) -->
<resultMap id="AllColumnMap" type="com.iwiteks.tools.entity.Columns">
<result column="column_name" property="columnName"/>
<result column="column_type" property="columnType"/>
<result column="column_comment" property="columnComment"/>
<result column="is_nullable" property="isNullable"/>
</resultMap>
<!-- 代码生成工具生成(对应表字段的sql片段) -->
<sql id="all_column">
`column_key`,
`column_name`,
`column_type`,
`column_comment`,
`is_nullable`
</sql>
<!-- 代码生成工具生成(查询数据,其中pojo为参数过滤对象) -->
<select id="select" resultMap="AllColumnMap">
SELECT <include refid="all_column"/>
FROM columns
<where>
`table_schema` = #{dbName} AND `table_name` = #{tableName}
</where>
</select>
</mapper>
4.3dao层两个文件代码如下:
TableDao.java代码如下:
package com.iwiteks.tools.dao;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Repository;
import java.util.List;
import com.iwiteks.tools.entity.Table;
@Repository
public interface TableDao {
List<Table> select(@Param("dbName") String dbName);
}
ColumnsDao.java代码如下:
package com.iwiteks.tools.dao;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Repository;
import java.util.List;
import com.iwiteks.tools.entity.Columns;
@Repository
public interface ColumnsDao {
List<Columns> select(@Param("tableName") String tableName, @Param("dbName") String dbName);
}
4.4service层两个文件如下如下:
TableService.java代码如下:
package com.iwiteks.tools.service;
import com.iwiteks.tools.entity.Table;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
import java.util.List;
import com.iwiteks.tools.dao.TableDao;
@Service
public class TableService {
@Resource
private TableDao tableDao;
public List<Table> select(String dbName) {
return tableDao.select(dbName);
}
}
ColumnsService.java文件代码如下:
package com.iwiteks.tools.service;
import com.iwiteks.tools.entity.Columns;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
import java.util.List;
import com.iwiteks.tools.dao.ColumnsDao;
@Service
public class ColumnsService {
@Resource
private ColumnsDao columnsDao;
public List<Columns> select(String tableName, String dbName) {
return columnsDao.select(tableName, dbName);
}
}
4.5两个实体类的代码如下:
Table.java文件代码如下:
package com.iwiteks.tools.entity;
import java.util.List;
public class Table {
private String tableName;
private String tableComment;
private String engine;
private List<Columns> columns;
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 getEngine() {
return engine;
}
public void setEngine(String engine) {
this.engine = engine;
}
public List<Columns> getColumns() {
return columns;
}
public void setColumns(List<Columns> columns) {
this.columns = columns;
}
}
Columns.java文件代码如下:
package com.iwiteks.tools.entity;
public class Columns {
private String columnName;
private String columnType;
private String columnComment;
private String isNullable;
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 getColumnComment() {
return columnComment;
}
public void setColumnComment(String columnComment) {
this.columnComment = columnComment;
}
public String getIsNullable() {
return isNullable;
}
public void setIsNullable(String isNullable) {
this.isNullable = isNullable;
}
}
备注:Columns.java属于Table.java的子集数据
4.6controller层代码如下(代码实在截图不下了,也懒得弄了,直接贴代码吧):
package com.iwiteks.tools.controller;
import com.iwiteks.tools.entity.Columns;
import com.iwiteks.tools.entity.Table;
import com.iwiteks.tools.service.ColumnsService;
import com.iwiteks.tools.service.TableService;
import org.apache.poi.xwpf.usermodel.*;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTTblPr;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTTblWidth;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.STTblWidth;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigInteger;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
@RestController
@RequestMapping(value = "/api/gen")
public class GenController {
@Autowired
private TableService tableService;
@Autowired
private ColumnsService columnsService;
@GetMapping(value = "/start")
public List<Table> gen(@RequestParam(value = "dbName") String dbName) {
List<Table> results = tableService.select(dbName);
if (results != null) {
List<Columns> columnsList = null;
for (int i = 0, len = results.size(); i < len; i++) {
columnsList = columnsService.select(results.get(i).getTableName(), dbName);
results.get(i).setColumns(columnsList);
}
return results;
} else {
return null;
}
}
/**
* 导出
* @param dbName
* @param response
* @throws IOException
*/
@GetMapping(value = "/exportDocument")
public void exportTable(@RequestParam(value = "dbName") String dbName, HttpServletResponse response) throws IOException {
List<Table> results = tableService.select(dbName);
if (results == null) {
return;
} else {
if (results.size() == 0) {
return;
}
}
List<Columns> columnsList = null;
for (int i = 0, len = results.size(); i < len; i++) {
columnsList = columnsService.select(results.get(i).getTableName(), dbName);
results.get(i).setColumns(columnsList);
}
// 创建空白文档
XWPFDocument document = new XWPFDocument();
//基础对象
// 段落
XWPFParagraph titleParag = null;
// 标题
XWPFRun titleRun = null;
// 表格
XWPFTable table = null;
// 表格属性
CTTblPr tablePr = null;
// 表格宽度
CTTblWidth width = null;
// 表头行
XWPFTableRow headRow = null;
XWPFTableCell headCell0 = null;
XWPFTableCell headCell1 = null;
XWPFTableCell headCell2 = null;
XWPFTableCell headCell3 = null;
XWPFTableCell headCell4 = null;
XWPFTableCell headCell5 = null;
XWPFRun headRun0 = null;
XWPFRun headRun1 = null;
XWPFRun headRun2 = null;
XWPFRun headRun3 = null;
XWPFRun headRun4 = null;
XWPFRun headRun5 = null;
// 行
XWPFTableRow contentRow = null;
XWPFTableCell cell0 = null;
XWPFTableCell cell1 = null;
XWPFTableCell cell2 = null;
XWPFTableCell cell3 = null;
XWPFTableCell cell4 = null;
XWPFTableCell cell5 = null;
XWPFRun pRun0 = null;
XWPFRun pRun1 = null;
XWPFRun pRun2 = null;
XWPFRun pRun3 = null;
XWPFRun pRun4 = null;
XWPFRun pRun5 = null;
for (int i = 0, len = results.size(); i < len; i++) {
//创建标题段落
titleParag = document.createParagraph();
titleRun = titleParag.createRun();
titleRun.setText(String.valueOf(i + 1) + "." + results.get(i).getTableName() + "(" + results.get(i).getTableComment() + ")");
titleRun.setFontSize(20);
titleRun.setBold(true);//字体是否加粗
titleParag.setAlignment(ParagraphAlignment.LEFT);//段落居左
table = document.createTable(results.get(i).getColumns().size() + 1, 6);
tablePr = table.getCTTbl().addNewTblPr();
width = tablePr.addNewTblW();
width.setW(BigInteger.valueOf(8000));
//设置表格宽度为非自动
width.setType(STTblWidth.DXA);
//表头行
headRow = table.getRow(0);
headCell0 = headRow.getCell(0);
headCell1 = headRow.getCell(1);
headCell2 = headRow.getCell(2);
headCell3 = headRow.getCell(3);
headCell4 = headRow.getCell(4);
headCell5 = headRow.getCell(5);
// 0
titleParag = headCell0.addParagraph();
headRun0 = titleParag.createRun();
headRun0.setText("序号");
headRun0.setFontSize(12);
headRun0.setBold(true);//是否粗体
headCell0.setColor("DEDEDE");
titleParag.setVerticalAlignment(TextAlignment.CENTER);//垂直居中
titleParag.setAlignment(ParagraphAlignment.CENTER);//水平居中
// 1
titleParag = headCell1.addParagraph();
headRun1 = titleParag.createRun();
headRun1.setText("字段名");
headRun1.setFontSize(12);
headRun1.setBold(true);
headCell1.setColor("DEDEDE");
titleParag.setVerticalAlignment(TextAlignment.CENTER);//垂直居中
titleParag.setAlignment(ParagraphAlignment.CENTER);//水平居中
// 2
titleParag = headCell2.addParagraph();
headRun2 = titleParag.createRun();
headRun2.setFontSize(12);
headRun2.setText("字段中文名");
headRun2.setBold(true);
headCell2.setColor("DEDEDE");
titleParag.setVerticalAlignment(TextAlignment.CENTER);//垂直居中
titleParag.setAlignment(ParagraphAlignment.CENTER);//水平居中
// 3
titleParag = headCell3.addParagraph();
headRun3 = titleParag.createRun();
headRun3.setFontSize(12);
headRun3.setText("数据类型(长度)");
headRun3.setBold(true);
headCell3.setColor("DEDEDE");
titleParag.setVerticalAlignment(TextAlignment.CENTER);//垂直居中
titleParag.setAlignment(ParagraphAlignment.CENTER);//水平居中
// 4
titleParag = headCell4.addParagraph();
headRun4 = titleParag.createRun();
headRun4.setFontSize(12);
headRun4.setText("是否必填");
headRun4.setBold(true);
headCell4.setColor("DEDEDE");
titleParag.setVerticalAlignment(TextAlignment.CENTER);//垂直居中
titleParag.setAlignment(ParagraphAlignment.CENTER);//水平居中
// 5
titleParag = headCell5.addParagraph();
headRun5 = titleParag.createRun();
headRun5.setFontSize(12);
headRun5.setText("备注");
headRun5.setBold(true);
headCell5.setColor("DEDEDE");
titleParag.setVerticalAlignment(TextAlignment.CENTER);//垂直居中
titleParag.setAlignment(ParagraphAlignment.CENTER);//水平居中
//表主体行
for (int j = 0; j < results.get(i).getColumns().size(); j++) {
contentRow = table.getRow(j + 1);
cell0 = contentRow.getCell(0);
cell1 = contentRow.getCell(1);
cell2 = contentRow.getCell(2);
cell3 = contentRow.getCell(3);
cell4 = contentRow.getCell(4);
cell5 = contentRow.getCell(5);
// 0
titleParag = cell0.addParagraph();
pRun0 = titleParag.createRun();
pRun0.setText(String.valueOf(j + 1));
cell0.setVerticalAlignment(XWPFTableCell.XWPFVertAlign.CENTER);//垂直居中
titleParag.setAlignment(ParagraphAlignment.CENTER);//水平居中
// 1
titleParag = cell1.addParagraph();
pRun1 = titleParag.createRun();
pRun1.setText(results.get(i).getColumns().get(j).getColumnName());
cell1.setVerticalAlignment(XWPFTableCell.XWPFVertAlign.CENTER);//垂直居中
titleParag.setAlignment(ParagraphAlignment.CENTER);//水平居中
// 2
titleParag = cell2.addParagraph();
pRun2 = titleParag.createRun();
pRun2.setText(results.get(i).getColumns().get(j).getColumnComment());
cell2.setVerticalAlignment(XWPFTableCell.XWPFVertAlign.CENTER);//垂直居中
titleParag.setAlignment(ParagraphAlignment.CENTER);//水平居中
// 3
titleParag = cell3.addParagraph();
pRun3 = titleParag.createRun();
pRun3.setText(results.get(i).getColumns().get(j).getColumnType());
cell3.setVerticalAlignment(XWPFTableCell.XWPFVertAlign.CENTER);//垂直居中
titleParag.setAlignment(ParagraphAlignment.CENTER);//水平居中
// 4
titleParag = cell4.addParagraph();
pRun4 = titleParag.createRun();
pRun4.setText(results.get(i).getColumns().get(j).getIsNullable().equals("NO") ? "是" : "否");
cell4.setVerticalAlignment(XWPFTableCell.XWPFVertAlign.CENTER);//垂直居中
titleParag.setAlignment(ParagraphAlignment.CENTER);//水平居中
// 5
titleParag = cell5.addParagraph();
pRun5 = titleParag.createRun();
pRun5.setText(j == 0 ? "主键" : "");
cell5.setVerticalAlignment(XWPFTableCell.XWPFVertAlign.CENTER);//垂直居中
titleParag.setAlignment(ParagraphAlignment.CENTER);//水平居中
}
}
// 导出操作
OutputStream output = response.getOutputStream();
//生成word文件的文件名
String fileName= new String((dbName + new SimpleDateFormat("-yyyy-MM-dd").format(new Date()) +".docx").getBytes("UTF-8"),"iso-8859-1");
response.setHeader("Content-disposition", "attachment; filename=" + fileName);
//把word文档写到输出流
document.write(output);
}
}
至此,所有代码都已经编写完毕,直接请求controller层的/api/gen/exportDocument就能导出word文档了,不过呢,如果数据库设计的sql脚本您公司没有必须加comment,那么,文档也是缺失注释这块的,思路就是,通过infomation-scema表先获取指定数据库下的所有表名称+表注释,然后再遍历每张表去拉取列中隶属表的所有列的属性,然后输出到word中,效果截图如下:
有什么问题不清楚的,您可以通过qq:972516490@qq.com联系我,本博客只是了记录一下,如果您有更好的解决方案,大家可以一起交流一下。