在开发过程中需要统一维护一套数据库结构文档,系统交付等环节也需要使用到。文档的维护就是一个难点。我推荐一个基于java开发的数据库表结构文档生成工具: screw (螺丝钉) 数据库表结构文档生成工具(https://blog.csdn.net/minkeyto/article/details/107358209)。国人开发的,值得骄傲。
我在它的基础上做了二次开发,基于springboot 完成的。
主要的功能: 1.word文档下载 ,html文档的在线阅读 2.通过阅读源码找到了自定义文档模板
1. 项目结构图:
2. 功能代码
2.1 controller
@Controller
public class DataSourceController {
@Autowired
private DataSourceService service;
@RequestMapping(value="/scanFile", method = RequestMethod.GET)
public String scanFile(HttpServletRequest request, HttpServletResponse response) {
//接受前台参数
String dataSourceType = request.getParameter("dataSourceType");
//生成对应的文件
service.createFile(dataSourceType,"HTML");
File[] fileList = null;
File findFile = null;
String fileName = null;
try {
String folderPath = System.getProperty("user.dir") +File.separator+"src"+File.separator+"main"+File.separator+"resources"+File.separator+"public";
File file = new File(folderPath);
fileList = file.listFiles();
//根据dataSourceType找到对应的文件
for (int i = 0; i < fileList.length; i++) {
if(fileList[i].getName().contains(dataSourceType) && fileList[i].getName().contains(".html")) {
findFile = fileList[i];
}
}
fileName = findFile.getName();
fileName = URLEncoder.encode(fileName, "UTF-8");
}catch (Exception e) {
e.printStackTrace();
}
return "redirect:/"+fileName;
}
@RequestMapping("/download2")
public void download2(HttpServletRequest request, HttpServletResponse response) {
service.download2(request, response);
}
2.2 service
@Service
public class DataSourceService {
/**
* MySQL数据库连接信息配置
*/
@Value("${mysqlinfo.url}")
private String mySqlJdbcUrl;
@Value("${mysqlinfo.driver-class-name}")
private String mySqlDriverClassName;
@Value("${mysqlinfo.username}")
private String mySqlUserName;
@Value("${mysqlinfo.password}")
private String mySqlPassWord;
/**
* oracle 数据库连接信息配置
*/
@Value("${oracleinfo.url}")
private String oracleJdbcUrl;
@Value("${oracleinfo.driver-class-name}")
private String oracleDriverClassName;
@Value("${oracleinfo.username}")
private String oracleUserName;
@Value("${oracleinfo.password}")
private String oraclePassWord;
public void createFile(String dataSourceType,String documentTyoe) {
//设置生成文件的存储位置
String folderPath = System.getProperty("user.dir") +File.separator+"src"+File.separator+"main"+File.separator+"resources"+File.separator+"public";
//数据源:HikariCP 线程池, SpringBoot 2.0开始内置了HikariCP,2.0之前的版本需要引入依赖
HikariConfig hikariConfig = new HikariConfig();
//下载mysql数据库文档
if("mysql".equals(dataSourceType)) {
// com.mysql.jdbc.Driver MySQL5驱动;com.mysql.cj.jdbc.Driver MySQL6之后的驱动
hikariConfig.setDriverClassName(mySqlDriverClassName);
hikariConfig.setJdbcUrl(mySqlJdbcUrl);
hikariConfig.setUsername(mySqlUserName);
hikariConfig.setPassword(mySqlPassWord);
}
//下载oracle数据库文档
else if("oracle".equals(dataSourceType)) {
hikariConfig.setDriverClassName(oracleDriverClassName);
hikariConfig.setJdbcUrl(oracleJdbcUrl);
hikariConfig.setUsername(oracleUserName);
hikariConfig.setPassword(oraclePassWord);
}
//设置可以获取tables remarks信息
hikariConfig.addDataSourceProperty("useInformationSchema", "true");
hikariConfig.setMinimumIdle(2);
hikariConfig.setMaximumPoolSize(5);
DataSource dataSource = new HikariDataSource(hikariConfig);
// 1、生成文件配置
EngineConfig engineConfig = null;
if("WORD".equals(documentTyoe)) {
engineConfig = EngineConfig.builder()
//生成文件路径(改成自己的生成路径)
.fileOutputDir(folderPath)
//生成后是否立即打开目录
.openOutputDir(true)
//文件类型 有HTML、WORD、MD三种枚举选择
.fileType(EngineFileType.WORD)
//生成模板实现
.produceType(EngineTemplateType.freemarker).build();
}else if("HTML".equals(documentTyoe)) {
engineConfig = EngineConfig.builder()
//生成文件路径(改成自己的生成路径)
.fileOutputDir(folderPath)
//生成后是否立即打开目录
.openOutputDir(true)
//文件类型 有HTML、WORD、MD三种枚举选择
.fileType(EngineFileType.HTML)
//生成模板实现
.produceType(EngineTemplateType.freemarker).build();
}
// 忽略表名(可选)
List<String> ignoreTableName = Arrays.asList("aa", "test_group");
// 忽略表前缀(可选)
List<String> ignorePrefix = Collections.singletonList("czb_");
// 忽略表后缀(可选)
List<String> ignoreSuffix = Arrays.asList("_test", "_test1");
// 2、配置想要忽略的表(可选)
ProcessConfig processConfig = ProcessConfig.builder()
.ignoreTableName(ignoreTableName)
.ignoreTablePrefix(ignorePrefix)
.ignoreTableSuffix(ignoreSuffix)
.build();
//系统当前时间
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
// 3、生成文档配置(包含以下自定义版本号、标题、描述(数据库名 + 描述 = 文件名)等配置连接)
Configuration config = Configuration.builder()
.version("1.0.0")
.title(dataSourceType+"数据库文档")
.description(dataSourceType+"_数据库设计文档")
.dataSource(dataSource)
.engineConfig(engineConfig)
.produceConfig(processConfig).build();
// 4、执行生成
new DocumentationExecute(config).execute();
}
public void download2(HttpServletRequest request, HttpServletResponse response) {
//接受前台参数
String dataSourceType = request.getParameter("dataSourceType");
//生成对应的文件
createFile(dataSourceType,"WORD");
File[] fileList = null;
File findFile = null;
try {
String folderPath = System.getProperty("user.dir") +File.separator+"src"+File.separator+"main"+File.separator+"resources"+File.separator+"public";
File file = new File(folderPath);
fileList = file.listFiles();
//根据dataSourceType找到对应的文件
for (int i = 0; i < fileList.length; i++) {
if(fileList[i].getName().contains(dataSourceType) && fileList[i].getName().contains(".doc")) {
findFile = fileList[i];
}
}
String fileName = findFile.getName();
try {
fileName = URLEncoder.encode(fileName, "UTF-8");
response.setContentType("application/octet-stream");
response.setHeader("Content-disposition", "attachment;filename=" + fileName);
OutputStream fileOut = response.getOutputStream();
int len = 0;
byte[] buffer = new byte[1024];
InputStream is = new FileInputStream(findFile.getAbsoluteFile());
while ((len = is.read(buffer)) != -1) {
fileOut.write(buffer, 0, len);
}
is.close();
fileOut.flush();
fileOut.close();
} catch (Exception e) {
e.printStackTrace();
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
2.3 启动类
@SpringBootApplication
public class DemoApplication {
public static void main(String[] args) {
SpringApplication.run(DemoApplication.class, args);
}
}
2.4 index.html
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>数据库信息浏览</title>
</head>
<body>
<table border="1" align="center" cellspacing="0" cellpadding="0" >
<tr>
<td>mysql数据库信息</td>
<td><a href="/scanFile?dataSourceType=mysql">网页版html</a></td>
<td><a href="/download2?dataSourceType=mysql">下载word版</a></td>
</tr>
<tr>
<td>oracle数据库信息</td>
<td><a href="/scanFile?dataSourceType=oracle">网页版html</a></td>
<td><a href="/download2?dataSourceType=oracle">下载word版</a></td>
</tr>
</table>
</body>
</html>
2.5 yml配置文件
spring:
mvc:
static-path-pattern: /**
resource:
static-locations: classpath:/META-INF/resources/,classpath:/resources/,classpath:/static/,classpath:/public/
server:
#端口号
port: 8088
context-path: /index.html
#mysql数据库信息,用以生成word和html
mysqlinfo:
url: jdbc:mysql://localhost:3306/databasename?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai
username: root
password: root
driver-class-name: com.mysql.cj.jdbc.Driver
#oracle数据库信息,用以生成word和html
oracleinfo:
url: jdbc:oracle:thin:@ip:1521:databassename
username: name
password: pwd
3.调整自定义模板
调整screw-core中的模板