一、背景描述
系统上线或者交付,或者需要提供整理数据库表信息,如果一个个整理未免麻烦,接下来一个demo示例如何用JAVA导出Mysql数据库表信息生成Word文档。
1、项目源码地址:https://gitlab.com/qyBegonia/dbfile
2、项目整体结构如下:
3、最终导出生成word效果:
4、sql验证"test" 库中表总数量(108):
SELECT count(*) TABLES,table_schema from information_schema.tables WHERE table_schema='test' group by table_schema
接下来我们看下怎么编写小工具使用。
二、代码实现
1、pom.xml中引入相关依赖:
<!-- ================== 将数据库表信息生成word文档信息所需 ====================== -->
<!-- https://mvnrepository.com/artifact/com.lowagie/itext -->
<dependency>
<groupId>com.lowagie</groupId>
<artifactId>itext</artifactId>
<version>2.1.7</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.itextpdf/itext-asian -->
<dependency>
<groupId>com.itextpdf</groupId>
<artifactId>itext-asian</artifactId>
<version>5.2.0</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.lowagie/itext-rtf -->
<dependency>
<groupId>com.lowagie</groupId>
<artifactId>itext-rtf</artifactId>
<version>2.1.7</version>
</dependency>
2、application.yml配置文件
server:
port: 8080
servlet:
application-display-name: dbfile
spring:
application:
name: dbfile
profiles:
active: dev
# 配置数据源
datasource:
type: com.alibaba.druid.pool.DruidDataSource
driverClassName: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/test?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false
username: root
password: 12345678
initial-size: 10
max-active: 200
min-idle: 10
max-wait: 60000
pool-prepared-statements: true
max-pool-prepared-statement-per-connection-size: 20
time-between-eviction-runs-millis: 60000
min-evictable-idle-time-millis: 300000
management:
security:
enabled: false
# mybatis-plus相关配置
mybatis-plus:
# xml扫描,多个目录用逗号或者分号分隔(告诉 Mapper 所对应的 XML 文件位置)
mapper-locations: classpath:**/*Mapper.xml
# 以下配置均有默认值,可以不设置
global-config:
#主键类型 0:"数据库ID自增", 1:"用户输入ID",2:"全局唯一ID (数字类型唯一ID)", 3:"全局唯一ID UUID";
id-type: 0
#字段策略 0:"忽略判断",1:"非 NULL 判断"),2:"非空判断"
field-strategy: 2
#驼峰下划线转换
db-column-underline: true
#刷新mapper 调试神器
refresh-mapper: false
configuration:
# 是否开启自动驼峰命名规则映射:从数据库列名到Java属性驼峰命名的类似映射
map-underscore-to-camel-case: true
cache-enabled: false
jdbc-type-for-null: 'null'
3、核心类
TableService.java
@Slf4j
@Service
public class TableService implements ITableService {
@Autowired
private TableMapper tableMapper;
@Autowired
private TableToWordUtil tableToWordUtil;
@Override
public ApiResult getTableInfo(String tableName) {
List<Tables> tables = null;
// 1、获取数据库所有表信息(若param传入表名则导出单个表信息)
tables = tableMapper.getAllTables(ExportConstants.DATABASE, tableName);
if (CollectionUtils.isEmpty(tables)) {
return ApiResult.fail("数据库或表信息不存在");
}
// 2、生成文件名信息 - 年月日时分秒
String date = null;
try {
date = DateTimeUtils.dateFormat(new Date(), DateTimeUtils.PARSE_PATTERNS[12]);
} catch (ParseException e) {
e.printStackTrace();
}
String docFileName = ExportConstants.FILE_PATH + "\\" + ExportConstants.FILE_NAME + "-" + date + ".doc";
// 3、调用工具类生成文件
tableToWordUtil.toWord(tables, docFileName, ExportConstants.FILE_NAME);
// 4、返回文件地址
String filePath = docFileName.replaceAll("\\\\", "/");
return ApiResult.ok("导出数据库表信息生成Word成功", filePath);
}
}
TableToWordUtil.java
@Service
public class TableToWordUtil {
@Autowired
TableMapper tableMapper;
/**
* 生成word文档
*
* @param tables:该数据库下所有表信息
* @param fileName:生成文件地址
* @param title:文件内容标题
* @return: void
*/
public void toWord(List<Tables> tables, String fileName, String title) {
Document document = new Document(PageSize.A4);
try {
// 创建文件夹
File dir = new File(ExportConstants.FILE_PATH);
dir.mkdirs();
// 创建文件
File file = new File(fileName);
if (file.exists() && file.isFile()) {
file.delete();
}
file.createNewFile();
// 写入文件信息
RtfWriter2.getInstance(document, new FileOutputStream(fileName));
document.open();
Paragraph ph = new Paragraph();
Font f = new Font();
Paragraph p = new Paragraph(title, new Font(Font.NORMAL, 24, Font.BOLDITALIC, new Color(0, 0, 0)));
p.setAlignment(1);
document.add(p);
ph.setFont(f);
for (int i = 0; i < tables.size(); i++) {
String table_name = tables.get(i).getName();
String table_comment = tables.get(i).getComment();
List<TableFileds> fileds = tableMapper.getTableInfo(tables.get(i).getName());
String all = "" + (i + 1) + " 、表名称:" + table_name + "(" + table_comment + ")";
Table table = new Table(6);
document.add(new Paragraph(""));
table.setBorderWidth(1);
table.setPadding(0);
table.setSpacing(0);
//添加表头的元素,并设置表头背景的颜色
Color chade = new Color(176, 196, 222);
Cell cell = new Cell("编号");
addCell(table, cell, chade);
cell = new Cell("字段名");
addCell(table, cell, chade);
cell = new Cell("类型");
addCell(table, cell, chade);
cell = new Cell("是否非空");
addCell(table, cell, chade);
cell = new Cell("是否主键");
addCell(table, cell, chade);
cell = new Cell("注释");
addCell(table, cell, chade);
table.endHeaders();
// 表格的主体
for (int k = 0; k < fileds.size(); k++) {
addContent(table, cell, (k + 1) + "");
addContent(table, cell, fileds.get(k).getField());
addContent(table, cell, fileds.get(k).getType());
addContent(table, cell, fileds.get(k).getNull().equals("YES") ? "否" : "是");
addContent(table, cell, fileds.get(k).getKey().equals("PRI") ? "是" : "否");
addContent(table, cell, fileds.get(k).getComment());
}
Paragraph pheae = new Paragraph(all);
//写入表说明
document.add(pheae);
//生成表格
document.add(table);
}
document.close();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 添加表头到表格
*
* @param table
* @param cell
* @param chade
*/
private void addCell(Table table, Cell cell, Color chade) {
cell.setHorizontalAlignment(Element.ALIGN_CENTER);
cell.setBackgroundColor(chade);
table.addCell(cell);
}
/**
* 添加内容到表格
*
* @param table
* @param content
*/
private void addContent(Table table, Cell cell, String content) {
cell = new Cell(content);
cell.setHorizontalAlignment(Element.ALIGN_CENTER);
table.addCell(cell);
}
}
TableMapper.java
@Mapper
public interface TableMapper {
/**
* 获取指定数据库下所有表名和注释
*
* @param dbName:数据库名
* @return: java.util.List<com.mpolicy.dbfile.modules.system.entity.Tables>
*/
@Select("<script> select table_name as name,table_comment as comment from information_schema.tables where table_schema =#{dbName} <if test=\"tableName != null and tableName != ''\"> and table_name = #{tableName}</if> order by table_name </script>")
List<Tables> getAllTables(@Param("dbName") String dbName, @Param("tableName") String tableName);
/**
* 获取指定表信息
*
* @param tableName:表
* @return: java.util.List<com.mpolicy.dbfile.modules.system.entity.TableFileds>
*/
@Select("SHOW FULL FIELDS FROM ${tableName}")
List<TableFileds> getTableInfo(@Param("tableName") String tableName);
TableController.java
@Slf4j
@RestController
@RequestMapping("/api")
@Api(tags = "系统管理-数据库表管理")
public class TableController extends BaseController {
@Autowired
ITableService tableService;
@GetMapping(value = "/tableToWord/{tableName}", produces = "application/json;charset=utf-8", name = "导出数据库表信息生成Word")
@ApiOperation(value = "导出数据库表信息生成Word", httpMethod = "GET", response = ApiResult.class, notes = "导出数据库表信息生成Word")
public ApiResult tableToWord(@PathVariable String tableName) {
try {
return tableService.getTableInfo(tableName);
} catch (Exception e) {
log.error("导出数据库表信息生成Word失败", e);
return ApiResult.fail(e.getMessage());
}
}
}
三、验证
导出文件:
提供了一个get请求的接口http://localhost:8080/api/tableToWord/null
全量表导出
http://localhost:8080/api/tableToWord/user_info
导出单个表