同事要导出一份数据字典,想通过powerdesigner来生成。由于操作系统不匹配的问题,导致各种下载ODBC,powerdesigner(16.5),Oracle数据库,最后还是没有逆向成功。因为他的需求比较简单,决定用java帮他实现一下。
开发工具Eclipse,开发框架springboot,先看下项目结构,以防止模板文件找不到:
1、pom.xml:
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.tiger</groupId>
<artifactId>testApp</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>jar</packaging>
<name>testApp</name>
<url>http://maven.apache.org</url>
<properties>
<maven.compiler.source>1.8</maven.compiler.source>
<maven.compiler.target>1.8</maven.compiler.target>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
</properties>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>1.5.11.RELEASE</version>
<relativePath />
</parent>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context-support</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.freemarker</groupId>
<artifactId>freemarker</artifactId>
<version>2.3.28</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<configuration>
<executable>true</executable>
</configuration>
</plugin>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<version>2.0.2</version>
<configuration>
<encoding>utf-8</encoding>
<source>1.8</source>
<target>1.8</target>
</configuration>
</plugin>
</plugins>
<resources>
<resource>
<directory>src/main/resources</directory>
</resource>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.xml</include>
</includes>
</resource>
</resources>
</build>
</project>
2、TemplateController.java:
package com.tiger.testApp.controller;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;
@RestController
@EnableAutoConfiguration
@RequestMapping("/db")
public class TemplateController {
/**
* 数据库连接地址
*/
private static String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF8";
/**
* 获取表名和注释以及表的字段信息
*/
@RequestMapping(value="/list",method=RequestMethod.GET)
public Map<String,Object> test() {
Connection conn = null;
DatabaseMetaData metaData = null;
ResultSet rs = null;
ResultSet crs = null;
Properties props =new Properties();
try {
Class.forName("com.mysql.jdbc.Driver");
props.setProperty("user", "root");
props.setProperty("password", "123456");
//防止mysql数据库拿不到表的注释
props.setProperty("remarks", "true");
props.setProperty("useInformationSchema", "true");
conn = DriverManager.getConnection(url, props);
metaData = conn.getMetaData();
List<Map<String,Object>> tabList = new ArrayList<Map<String,Object>>();
// 获取表
rs = metaData.getTables(null, "%", "%", new String[] { "TABLE" });
while (rs.next()) {
Map<String,Object> tabMap = new HashMap<String,Object>();
String tablename = rs.getString("TABLE_NAME");
String tableRemark = rs.getString("REMARKS");
tabMap.put("name", tablename + ((tableRemark == null || "".equals(tableRemark))?"":"-("+ tableRemark + ")"));
List<Map<String,String>> colList = new ArrayList<Map<String,String>>();
// 获取列
crs = metaData.getColumns(null, "%", tablename, "%");
while (crs.next()) {
Map<String,String> colMap = new HashMap<String,String>();
colMap.put("colName", crs.getString("COLUMN_NAME"));
colMap.put("colType", crs.getString("TYPE_NAME"));
colMap.put("colLength", crs.getString("COLUMN_SIZE"));
colMap.put("colRemark", crs.getString("REMARKS"));
colList.add(colMap);
}
tabMap.put("colList", colList);
tabList.add(tabMap);
}
Map<String,Object> data = new HashMap<String,Object>();
data.put("tabList", tabList);
//生成word
WordUtil.generateWord(data);
//返回给前台用于生成ER图
return data;
} catch (Exception e) {
e.printStackTrace(System.out);
return null;
} finally {
try {
if (null != rs) {
rs.close();
}
if (null != conn) {
conn.close();
}
} catch (Exception e2) {
}
}
}
}
3、WordUtil.java,主要用于填充freemarker模板:
package com.tiger.testApp.controller;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.io.Writer;
import java.util.Map;
import freemarker.template.Configuration;
import freemarker.template.Template;
/**word生成工具类
* @author wh
* @date 2018年8月21日 上午10:03:22
* @Description: 根据模板生成word文档
*/
public class WordUtil {
private static Configuration configuration = null;
//模板路径
private static final String templateFolder = WordUtil.class.getClass().getResource("/").getPath() + "tmpl/";
//word文件生成路径
private static String FILEPATH = "D:\\\\test.doc";
static {
configuration = new Configuration();
configuration.setDefaultEncoding("utf-8");
try {
configuration.setDirectoryForTemplateLoading(new File(templateFolder));
} catch (IOException e) {
e.printStackTrace();
}
}
private WordUtil() {
throw new AssertionError();
}
/**将数据填充到word模板,生成word文件
* @param map
* @throws IOException
*/
public static void generateWord(Map<String,Object> map) throws IOException {
Template freemarkerTemplate = configuration.getTemplate("test.ftl");
try {
Writer w = new OutputStreamWriter(new FileOutputStream(new File(FILEPATH)), "utf-8");
freemarkerTemplate.process(map, w);
w.close();
} catch(Exception e) {
e.printStackTrace();
}
}
}
4、application.properties,主要用于配置数据源(实际情况应该是动态数据源,动态连接给定的数据库信息,这里是例子,简化了):
spring.datasource.url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8
spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.max-idle=10
spring.datasource.max-wait=10000
spring.datasource.min-idle=5
spring.datasource.initial-size=5
server.session.timeout=10
server.tomcat.uri-encoding=UTF-8
spring.http.multipart.maxFileSize=100Mb
spring.http.multipart.maxRequestSize=100Mb
5、启动类,Application.java:
package com.tiger.testApp;
import java.lang.reflect.Method;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.web.servlet.ServletComponentScan;
@ServletComponentScan
@SpringBootApplication
@EnableAutoConfiguration
public class Application {
public static void main(String[] args) throws Exception {
SpringApplication.run(Application.class, args);
System.out.println("************************************");
System.out.println("*********java生成ER图和数据字典**********");
System.out.println("************************************");
//browse("http://localhost:8080/login.html");
}
}
6、login.html,因为对前端不熟,大部分代码都是拼凑、抄网上的:
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>测试ER图</title>
<script src="jquery-3.2.1.min.js"></script>
<style>
/* @media screen and (max-width: 800px) {
.main {
column-count: 2;
}
}
@media screen and (max-width: 500px) {
.main {
column-count: 1;
}
} */
.main{
-moz-column-count:4;
-webkit-column-count:4;
column-count:4;
-moz-column-gap: 2em;
-webkit-column-gap: 2em;
column-gap: 2em;
width: 100%;
margin:2em auto;
background-color:gray;
}
.item{
padding: 2em;
margin-bottom: 2em;
-moz-page-break-inside: avoid;
-webkit-column-break-inside: avoid;
break-inside: avoid;
bolider:1px rgb(white);
float:left;
font-size:14px;
width:250px;
}
</style>
</head>
<body>
<header>测试ER图</header>
<div id="main">
</div>
<script type="text/javascript">
function list(){
$.ajax({
url:'/db/list',
type:'GET',
cache:false,
dataType:'json',
success:function(data,textStatus,jqXHR){
for(i=0;i<data.tabList.length;i++){
var tabObj = data.tabList[i];
console.log(tabObj);
var divHtml = '<div class="item">' +
'<div style="height:30px;background:rgb(4,47,124);color:rgb(32,175,255);border:solid 1px 1px 0px 1px #fff"> '+ tabObj.name + '</div>' +
'<div style="background:rgb(76,115,120);color:rgb(255,255,255);margin-top:1px;"> ';
for(j=0;j<tabObj.colList.length;j++){
var colObj = tabObj.colList[j];
var col = colObj.colName;
if(colObj.colRemark != null && colObj.colRemark != '' && colObj.colRemark != undefined){
col = colObj.colRemark;
if(col.length>8){
col = col.substring(0,7) + '...';
}
}
divHtml += col + " " + colObj.colType + "(" + colObj.colLength + ")<br> ";
}
divHtml = divHtml + '</div></div>' ;
$("#main").append(divHtml);
}
waterFlow("main", "item");
},
error:function(xhr,textStatus){
alert("查询失败");
console.log('错误')
console.log(xhr)
console.log(textStatus)
}
});
}
list();
function waterFlow(parent, chirld){
var wparent = document.getElementById(parent);
var allArr = getAllChirld(wparent,chirld);
var wscreenWidth = document.documentElement.clientWidth;
var wchirldWidth = wparent.getElementsByTagName("*");
var num = Math.floor(wscreenWidth/wchirldWidth[0].offsetWidth);
wparent.style.cssText = 'width:'+wchirldWidth[0].offsetWidth*num+'px;margin:0 auto';
getMinHeightOfCols(allArr, num);
}
function getAllChirld(parent,classname){
var wchirld = parent.getElementsByTagName("*");
var chirldArr = [];
for(var i = 0; i<wchirld.length; i++){
if(wchirld[i].className==classname){
chirldArr.push(wchirld[i]);
}
}
return chirldArr;
}
function getMinHeightOfCols(chirdArr, num){
var onlyOneColsArr = [];
for(var i = 0; i<chirdArr.length; i++){
if(i<num){
onlyOneColsArr[i]=chirdArr[i].offsetHeight;
} else {
var minHeightOfCols = Math.min.apply(null, onlyOneColsArr);
var minHeightOfindex = getminIndex(onlyOneColsArr, minHeightOfCols);
chirdArr[i].style.position = "absolute";
chirdArr[i].style.top = minHeightOfCols + "px";
chirdArr[i].style.left = chirdArr[minHeightOfindex].offsetLeft + "px";
onlyOneColsArr[minHeightOfindex] += chirdArr[i].offsetHeight;
}
}
}
//此方法是为了进行最小高度下标的确定
function getminIndex(onlyOneColsArr, min){
for(var i in onlyOneColsArr){
if(onlyOneColsArr[i] == min){
return i;
}
}
}
</script>
</body>
</html>
7、模板文件 test.ftl:
第一、新建word文件test.doc,编写一个表的信息,包括表名,表注释,字段表格(一行表头,一行数据即可)。表格为4列:列名,中文名,类型和长度;
第二、将文件另存为word类型的xml文件:test.xml,并格式化(内容非常长);
第三,改造模板。test.xml文件的内容很长,但是需要修改的地方很少:
<w:body>
<#-- 此处开始正文部分 -->
<#-- tabList为程序中定义的列表变量名称 -->
<#list tabList as tab>
<w:p w14:paraId="333B9ED5" w14:textId="0B1E083F" w:rsidR="003D1047"
w:rsidRDefault="005A2ED3" w:rsidP="005A2ED3">
<w:pPr>
<w:pStyle w:val="2" />
</w:pPr>
<w:proofErr w:type="spellStart" />
<w:r>
<w:rPr>
<w:rFonts w:hint="eastAsia" />
</w:rPr>
</w:r>
<w:r>
<#-- 此处是表名部分 -->
<w:t>${tab.name!''}</w:t>
</w:r>
<w:proofErr w:type="spellEnd" />
</w:p>
<w:tbl><#-- 字段表格 -->
<w:tblPr>
<w:tblStyle w:val="a4" />
<w:tblW w:w="0" w:type="auto" />
<w:tblInd w:w="360" w:type="dxa" />
<w:tblLook w:val="04A0" w:firstRow="1" w:lastRow="0"
w:firstColumn="1" w:lastColumn="0" w:noHBand="0" w:noVBand="1" />
</w:tblPr>
<w:tblGrid>
<w:gridCol w:w="2074" />
<w:gridCol w:w="2074" />
<w:gridCol w:w="2074" />
<w:gridCol w:w="2074" />
</w:tblGrid>
<w:tr w:rsidR="005A2ED3" w14:paraId="5EB6BCCE" w14:textId="77777777"
w:rsidTr="005A2ED3"><#-- 表头开始 -->
<w:tc>
<w:tcPr>
<w:tcW w:w="2074" w:type="dxa" />
</w:tcPr>
<w:p w14:paraId="5BADFFA7" w14:textId="5F07E972" w:rsidR="005A2ED3"
w:rsidRDefault="005A2ED3" w:rsidP="005A2ED3">
<w:pPr>
<w:pStyle w:val="a3" />
<w:ind w:firstLineChars="0" w:firstLine="0" />
</w:pPr>
<w:r>
<w:rPr>
<w:rFonts w:hint="eastAsia" />
</w:rPr>
<w:t>列名</w:t>
</w:r>
</w:p>
</w:tc>
<w:tc>
<w:tcPr>
<w:tcW w:w="2074" w:type="dxa" />
</w:tcPr>
<w:p w14:paraId="3D359B09" w14:textId="319CF3EF" w:rsidR="005A2ED3"
w:rsidRDefault="005A2ED3" w:rsidP="005A2ED3">
<w:pPr>
<w:pStyle w:val="a3" />
<w:ind w:firstLineChars="0" w:firstLine="0" />
</w:pPr>
<w:r>
<w:rPr>
<w:rFonts w:hint="eastAsia" />
</w:rPr>
<w:t>中文名</w:t>
</w:r>
</w:p>
</w:tc>
<w:tc>
<w:tcPr>
<w:tcW w:w="2074" w:type="dxa" />
</w:tcPr>
<w:p w14:paraId="4C897B4A" w14:textId="6D46DF74" w:rsidR="005A2ED3"
w:rsidRDefault="005A2ED3" w:rsidP="005A2ED3">
<w:pPr>
<w:pStyle w:val="a3" />
<w:ind w:firstLineChars="0" w:firstLine="0" />
</w:pPr>
<w:r>
<w:rPr>
<w:rFonts w:hint="eastAsia" />
</w:rPr>
<w:t>类型</w:t>
</w:r>
</w:p>
</w:tc>
<w:tc>
<w:tcPr>
<w:tcW w:w="2074" w:type="dxa" />
</w:tcPr>
<w:p w14:paraId="514BA99D" w14:textId="52F757DB" w:rsidR="005A2ED3"
w:rsidRDefault="005A2ED3" w:rsidP="005A2ED3">
<w:pPr>
<w:pStyle w:val="a3" />
<w:ind w:firstLineChars="0" w:firstLine="0" />
</w:pPr>
<w:r>
<w:rPr>
<w:rFonts w:hint="eastAsia" />
</w:rPr>
<w:t>长度</w:t>
</w:r>
</w:p>
</w:tc>
</w:tr><#-- 表头结束 -->
<#list tab.colList as col>
<w:tr w:rsidR="005A2ED3" w14:paraId="6BCC05B3" w14:textId="77777777"
w:rsidTr="005A2ED3">
<w:tc>
<w:tcPr>
<w:tcW w:w="2074" w:type="dxa" />
</w:tcPr>
<w:p w14:paraId="237623B3" w14:textId="28F74654" w:rsidR="005A2ED3"
w:rsidRDefault="008C6C12" w:rsidP="005A2ED3">
<w:pPr>
<w:pStyle w:val="a3" />
<w:ind w:firstLineChars="0" w:firstLine="0" />
</w:pPr>
<w:proofErr w:type="spellStart" />
<w:r>
<w:rPr>
<w:rFonts w:hint="eastAsia" />
</w:rPr>
</w:r>
<w:r w:rsidR="005A2ED3">
<w:rPr>
<w:rFonts w:hint="eastAsia" />
</w:rPr>
<w:t>${col.colName!''}</w:t>
</w:r>
<w:proofErr w:type="spellEnd" />
</w:p>
</w:tc>
<w:tc>
<w:tcPr>
<w:tcW w:w="2074" w:type="dxa" />
</w:tcPr>
<w:p w14:paraId="06443CFC" w14:textId="6EC69EAB" w:rsidR="005A2ED3"
w:rsidRDefault="005A2ED3" w:rsidP="005A2ED3">
<w:pPr>
<w:pStyle w:val="a3" />
<w:ind w:firstLineChars="0" w:firstLine="0" />
</w:pPr>
<w:r>
<w:rPr>
<w:rFonts w:hint="eastAsia" />
</w:rPr>
<w:t>${col.colRemark!''}</w:t>
</w:r>
</w:p>
</w:tc>
<w:tc>
<w:tcPr>
<w:tcW w:w="2074" w:type="dxa" />
</w:tcPr>
<w:p w14:paraId="02E43072" w14:textId="29A47710" w:rsidR="005A2ED3"
w:rsidRDefault="00DE0026" w:rsidP="005A2ED3">
<w:pPr>
<w:pStyle w:val="a3" />
<w:ind w:firstLineChars="0" w:firstLine="0" />
</w:pPr>
<w:r w:rsidR="005A2ED3">
<w:t>${col.colType!''}</w:t>
</w:r>
</w:p>
</w:tc>
<w:tc>
<w:tcPr>
<w:tcW w:w="2074" w:type="dxa" />
</w:tcPr>
<w:p w14:paraId="07232EEE" w14:textId="45973F68" w:rsidR="005A2ED3"
w:rsidRDefault="005A2ED3" w:rsidP="005A2ED3">
<w:pPr>
<w:pStyle w:val="a3" />
<w:ind w:firstLineChars="0" w:firstLine="0" />
</w:pPr>
<w:r>
<w:rPr>
<w:rFonts w:hint="eastAsia" />
</w:rPr>
</w:r>
<w:r>
<w:t>${col.colLength!''}</w:t>
</w:r>
</w:p>
</w:tc>
</w:tr>
</#list><#-- 字段遍历结束 -->
</w:tbl>
<w:p w14:paraId="4F690628" w14:textId="77777777" w:rsidR="005A2ED3"
w:rsidRDefault="005A2ED3" w:rsidP="008C6C12">
<w:pPr>
<w:rPr>
<w:rFonts w:hint="eastAsia" />
</w:rPr>
</w:pPr>
<w:bookmarkStart w:id="0" w:name="_GoBack" />
<w:bookmarkEnd w:id="0" />
</w:p>
<w:sectPr w:rsidR="005A2ED3">
<w:pgSz w:w="11906" w:h="16838" />
<w:pgMar w:top="1440" w:right="1800" w:bottom="1440"
w:left="1800" w:header="851" w:footer="992" w:gutter="0" />
<w:cols w:space="425" />
<w:docGrid w:type="lines" w:linePitch="312" />
</w:sectPr>
</#list><#-- 表遍历结束 -->
</w:body>
需要修改的地方,都已经添加注释,主要是表遍历,表名替换,表字段遍历,字段名替换。
7、启动项目,访问:http://localhost:8080/login.html
生成的word文件,打开时提示:
点击确定,提示:
选择“是”,则看到数据字典文件内容:
网页显示的内容(暂时没有标注主键,外键,关联信息):