上传txt生成字典 java_java生成word版数据字典

最新推荐文章于 2023-08-16 10:44:21 发布

weixin_39541693

最新推荐文章于 2023-08-16 10:44:21 发布

阅读量167

点赞数

文章标签：上传txt生成字典 java

本文链接：https://blog.csdn.net/weixin_39541693/article/details/114245415

版权

同事要导出一份数据字典，想通过powerdesigner来生成。由于操作系统不匹配的问题，导致各种下载ODBC，powerdesigner(16.5)，Oracle数据库，最后还是没有逆向成功。因为他的需求比较简单，决定用java帮他实现一下。

开发工具Eclipse，开发框架springboot,先看下项目结构，以防止模板文件找不到：

1、pom.xml:

xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">

4.0.0

com.tiger

testApp

0.0.1-SNAPSHOT

jar

testApp

http://maven.apache.org

1.8

UTF-8

org.springframework.boot

spring-boot-starter-parent

1.5.11.RELEASE

org.springframework.boot

spring-boot-starter

org.springframework.boot

spring-boot-starter-web

org.springframework.boot

spring-boot-starter-thymeleaf

org.springframework

spring-context-support

mysql

mysql-connector-java

org.springframework.boot

spring-boot-starter-jdbc

org.freemarker

freemarker

2.3.28

org.springframework.boot

spring-boot-maven-plugin

true

org.apache.maven.plugins

maven-compiler-plugin

2.0.2

utf-8

1.8

src/main/resources

src/main/java

**/*.xml

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 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> tabList = new ArrayList>();

// 获取表

rs = metaData.getTables(null, "%", "%", new String[] { "TABLE" });

while (rs.next()) {

Map tabMap = new HashMap();

String tablename = rs.getString("TABLE_NAME");

String tableRemark = rs.getString("REMARKS");

tabMap.put("name", tablename + ((tableRemark == null || "".equals(tableRemark))?"":"-("+ tableRemark + ")"));

List> colList = new ArrayList>();

// 获取列

crs = metaData.getColumns(null, "%", tablename, "%");

while (crs.next()) {

Map colMap = new HashMap();

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 data = new HashMap();

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 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,因为对前端不熟，大部分代码都是拼凑、抄网上的:

测试ER图

/* @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;

}

测试ER图

function list(){

$.ajax({

url:'/db/list',

type:'GET',

cache:false,

dataType:'json',

success:function(data,textStatus,jqXHR){

for(i=0;i

var tabObj = data.tabList[i];

console.log(tabObj);

var divHtml = '

' +

'+ tabObj.name + '

' +

for(j=0;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 + ")
";

}

divHtml = divHtml + '

' ;

$("#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

if(wchirld[i].className==classname){

chirldArr.push(wchirld[i]);

}

return chirldArr;

}

function getMinHeightOfCols(chirdArr, num){

var onlyOneColsArr = [];

for(var i = 0; i

if(i

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;

}

7、模板文件 test.ftl:

第一、新建word文件test.doc，编写一个表的信息，包括表名，表注释，字段表格(一行表头，一行数据即可)。表格为4列：列名，中文名，类型和长度；

第二、将文件另存为word类型的xml文件：test.xml，并格式化(内容非常长);

第三，改造模板。test.xml文件的内容很长，但是需要修改的地方很少：