java生成word版数据字典

原创
2018/08/21 10:49
阅读数 1.9K

同事要导出一份数据字典,想通过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">&nbsp;&nbsp;'+ tabObj.name + '</div>' +
			               					 '<div  style="background:rgb(76,115,120);color:rgb(255,255,255);margin-top:1px;">&nbsp;&nbsp;';
			    		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>&nbsp;&nbsp;";
			    		}
			    		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文件,打开时提示:

点击确定,提示:

选择“是”,则看到数据字典文件内容:

网页显示的内容(暂时没有标注主键,外键,关联信息):

展开阅读全文
打赏
0
0 收藏
分享
加载中
更多评论
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部