Java查询数据库表信息导出Word

参考: POI生成Word多级标题格式_poi设置word标题-CSDN博客

1.概述

        使用jdbc查询数据库把表信息导出为word文档, 导出为word时需要下载word模板文件。

已实现数据库: 

• KingbaseES, 实现代码: 点击跳转

2.效果图

2.1.生成word内容

所有数据库合并

数据库不合并

2.2.生成文件列表

3.代码文件结构

4.代码实现

4.1.dto

4.1.1.DbInfoDTO.java

package cn.nordrassil.db2doc.dto;import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;import java.util.List;@Data
@AllArgsConstructor
@NoArgsConstructor
public class DbInfoDTO {private String dbName;private String dbDescribe;private List<TableInfo> tableInfos;@Data@AllArgsConstructor@NoArgsConstructorpublic static class TableInfo {private String tableName;private String tableDescribe;private List<TableField> tableFields;private List<List<String>> tableContents;public TableInfo(String tableName, String tableDescribe, List<TableField> tableFields) {this.tableName = tableName;this.tableDescribe = tableDescribe;this.tableFields = tableFields;}}@Data@AllArgsConstructor@NoArgsConstructorpublic static class TableField {private String name;private String type;private Integer length;private String allowEmpty;private String isPrimaryKey;private String defaultValue;private String describe;}}

4.1.2.DbConnParamDTO.java

package cn.nordrassil.db2doc.dto;import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;@Data
@AllArgsConstructor
@NoArgsConstructor
public class DbConnParamDTO {private String host;private Integer port;private String username;private String password;// 随机填一个数据库即可private String randomDatabase;// 数据库前缀过滤private String dbPrefixFilter;// 等于true是只会导出第一张表private Boolean testRun;
}

4.1.3.ExportWordParamDTO

package cn.nordrassil.db2doc.dto;import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;import java.util.List;@Data
@AllArgsConstructor
@NoArgsConstructor
public class ExportWordParamDTO {// 数据信息private List<DbInfoDTO> ds;// 导出word模板文件private String wordTemplateFile;// 导出目录private String exportFolder;// 所有数据库是否合并到一个文档, 默认每个数据库一个文档private Boolean merge = Boolean.FALSE;
}

4.2.util

4.2.1.CommonUtil.java

package cn.nordrassil.db2doc.util;import java.io.*;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;public class CommonUtil {private static DateTimeFormatter dateFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss.SSS");private static DateTimeFormatter dateFormatter2 = DateTimeFormatter.ofPattern("yyyyMMdd_HHmmss");public static String readByLineRs(String path) {StringBuffer sb = new StringBuffer();File file = new File(path);BufferedReader reader = null;String temp = null;try {reader = new BufferedReader(new FileReader(file));while ((temp = reader.readLine()) != null) {sb.append(temp).append("\n");}} catch (Exception e) {e.printStackTrace();} finally {if (reader != null) {try {reader.close();} catch (Exception e) {e.printStackTrace();}}}return sb.toString();}/*** 依次替换{}* 例 : join("a={},b={}",1,2) => a=1,b=2** @param str* @param param* @return*/public static String join(String str, Object... param) {try {for (Object p : param) {String processedParam = p.toString().replace("\\", "\\\\");str = str.replaceFirst("\\{\\}", processedParam);}} catch (Exception e) {for (Object p : param) {str = str + p;}}return str;}public static void writeString(String content, String path) {try {File file = new File(path);if (!file.exists()) {file.createNewFile();}FileWriter fw = new FileWriter(file.getAbsoluteFile());BufferedWriter bw = new BufferedWriter(fw);bw.write(content);bw.close();} catch (IOException e) {e.printStackTrace();}}public static void info(String str, Object... param) {System.out.println(LocalDateTime.now().format(dateFormatter) + "|" + join(str, param));}public static String getDayTime() {return LocalDateTime.now().format(dateFormatter2);}public static void createDirectoryIfNotExists(String absolutePath) {File file = new File(absolutePath);// 获取文件所在的目录File parentDir = file.getParentFile();if (parentDir != null && !parentDir.exists()) {// 创建目录parentDir.mkdirs();}}}

4.3.service

4.3.1.DbInfoExportWordService.java

package cn.nordrassil.db2doc;import cn.nordrassil.db2doc.dto.DbInfoDTO;
import cn.nordrassil.db2doc.dto.ExportWordParamDTO;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.xwpf.usermodel.*;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTShd;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTStyles;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTTcPr;import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;import static cn.nordrassil.db2doc.util.CommonUtil.*;// 参考: https://blog.csdn.net/qq_37945565/article/details/121518330public class DbInfoExportWordService {public static List<String> tableTitles = new ArrayList<String>() {{add("序号");add("字段名");add("字段类型");add("长度");add("非空");add("主键");add("默认值");add("注释");}};public static List<String> fields = new ArrayList<String>() {{add("name");add("type");add("length");add("allowEmpty");add("isPrimaryKey");add("defaultValue");add("describe");}};public static void generateWord(ExportWordParamDTO paramDTO) throws Exception {// 获得模板文档的整体样式XWPFDocument template = new XWPFDocument(new FileInputStream(paramDTO.getWordTemplateFile()));CTStyles wordStyles = template.getStyle();int totalTableCount = 0;int index = 1;List<DbInfoDTO> ds = paramDTO.getDs();XWPFDocument document = null;boolean merge = paramDTO.getMerge() != null && paramDTO.getMerge();if (merge) {document = new XWPFDocument();}for (DbInfoDTO d : ds) {info("执行第[{}/{}]生成word文档...表数量:{}", index, ds.size(), d.getTableInfos().size());if (!merge) {document = new XWPFDocument();}// 获取新建文档对象的样式XWPFStyles newStyles = document.createStyles();// 关键行// 修改设置文档样式为静态块中读取到的样式newStyles.setStyles(wordStyles);if (merge) {XWPFParagraph title1 = document.createParagraph();title1.setStyle("2");XWPFRun titleRun1 = title1.createRun();String dbText = index + "." + d.getDbName();if (d.getDbDescribe() != null) {dbText += "(" + d.getDbDescribe().replaceAll("\r\n", "").replaceAll("\r", "").replaceAll("\n", "") + ")";}titleRun1.setText(dbText);}List<DbInfoDTO.TableInfo> tableInfos = d.getTableInfos();totalTableCount += tableInfos.size();for (int k = 0; k < tableInfos.size(); k++) {DbInfoDTO.TableInfo tableInfo = tableInfos.get(k);XWPFParagraph title2 = document.createParagraph();XWPFRun titleRun2 = title2.createRun();String tableText;if (merge) {title2.setStyle("3");tableText = index + "." + (k + 1) + "." + tableInfo.getTableName();} else {title2.setStyle("2");tableText = (k + 1) + "." + tableInfo.getTableName();}if (tableInfo.getTableDescribe() != null) {tableText += "(" + tableInfo.getTableDescribe().replaceAll("\r\n", "").replaceAll("\r", "").replaceAll("\n", "") + ")";}titleRun2.setText(tableText);// 创建表格(第一列为序号)List<DbInfoDTO.TableField> tableFields = tableInfo.getTableFields();XWPFTable table = document.createTable(tableFields.size(), 8);int titleFontSize = 10;int textFontSize = 8;// 设置表格标题样式for (int i = 0; i < table.getRows().get(0).getTableCells().size(); i++) {XWPFTableCell cell = table.getRows().get(0).getCell(i);XWPFParagraph cellParagraph = cell.getParagraphs().get(0);cellParagraph.setIndentationLeft(0); // 设置左侧缩进为0cellParagraph.setIndentationRight(0); // 设置右侧缩进为0cellParagraph.setIndentationFirstLine(0); // 设置首行缩进为0cellParagraph.setIndentationLeft(0);// 设置单元格背景色为灰色CTTcPr cellPr = cell.getCTTc().addNewTcPr();CTShd shd = cellPr.addNewShd();shd.setFill("D3D3D3");cellParagraph.setAlignment(ParagraphAlignment.CENTER);XWPFRun cellRun = cellParagraph.createRun();cellRun.setBold(true);cellRun.setText(tableTitles.get(i));cellRun.setFontSize(titleFontSize);}// 写入表格内容for (int row = 1; row < table.getNumberOfRows(); row++) {for (int col = 0; col < table.getRow(row).getTableCells().size(); col++) {XWPFTableCell cell = table.getRow(row).getCell(col);XWPFParagraph cellParagraph = cell.getParagraphs().get(0);XWPFRun cellRun = cellParagraph.createRun();if (col == 0) {cellRun.setText(row + "");} else {DbInfoDTO.TableField tableField = tableFields.get(row - 1);JSONObject jsonObject = JSONObject.parseObject(JSONObject.toJSONString(tableField));cellRun.setText(jsonObject.getString(fields.get(col - 1)));}CTTcPr tcPr = cell.getCTTc().getTcPr();if (tcPr == null) {tcPr = cell.getCTTc().addNewTcPr();}if (!tcPr.isSetTcMar()) {tcPr.addNewTcMar();}tcPr.getTcMar().addNewLeft().setW(0);tcPr.getTcMar().addNewRight().setW(0);tcPr.getTcMar().addNewTop().setW(0);tcPr.getTcMar().addNewBottom().setW(0);cellRun.setFontSize(textFontSize);cellParagraph.setIndentationLeft(0);// 设置段落左对齐cellParagraph.setAlignment(ParagraphAlignment.LEFT);// 设置左缩进为 0cellParagraph.setIndentationLeft(0);}}}if (!merge) {// 保存文档String outputFile = paramDTO.getExportFolder() + File.separator + d.getDbName() + ".docx";createDirectoryIfNotExists(outputFile);try (FileOutputStream out = new FileOutputStream(outputFile)) {document.write(out);info("第[{}/{}]数据库信息生成word文档完成, 文档路径:{}", index, ds.size(), outputFile);} catch (IOException e) {e.printStackTrace();}}index++;}if (merge) {// 保存文档String outputFile = paramDTO.getExportFolder() + File.separator + getDayTime() + "_all_db.docx";createDirectoryIfNotExists(outputFile);try (FileOutputStream out = new FileOutputStream(outputFile)) {document.write(out);info("所有数据库信息生成word文档完成, 文档路径:{}", outputFile);} catch (IOException e) {e.printStackTrace();}}info("数据库已导出为word文档完成, 数据库数量:{}, 所有表总数量:{}", ds.size(), totalTableCount);}
}

4.3.2.GetDbInfoService.java

package cn.nordrassil.db2doc;import cn.nordrassil.db2doc.dto.DbConnParamDTO;
import cn.nordrassil.db2doc.dto.DbInfoDTO;import java.util.List;public interface GetDbInfoService {List<DbInfoDTO> get(DbConnParamDTO dto);
}

5.依赖 

5.1.mave依赖

<dependency><groupId>com.alibaba</groupId><artifactId>fastjson</artifactId><version>1.2.83</version><scope>compile</scope></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><version>1.18.28</version><scope>provided</scope></dependency><!-- Apache Commons Compress 用于处理多种归档文件格式 --><dependency><groupId>org.apache.commons</groupId><artifactId>commons-compress</artifactId><version>1.24.0</version></dependency><!-- Apache Commons IO 提供了 IO 操作的工具类 --><dependency><groupId>commons-io</groupId><artifactId>commons-io</artifactId><version>2.13.0</version></dependency><dependency><groupId>com.github.junrar</groupId><artifactId>junrar</artifactId><version>7.5.5</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>5.2.3</version></dependency>

5.2.word文档模板

word/word模板.docx · 楽焫旒璡/public-share - 码云 - 开源中国

6.测试

6.1.MyTest.java

package cn.nordrassil.db2doc;import cn.nordrassil.db2doc.dto.DbConnParamDTO;
import cn.nordrassil.db2doc.dto.DbInfoDTO;
import cn.nordrassil.db2doc.dto.ExportWordParamDTO;
import cn.nordrassil.db2doc.impl.GetDbInfoKingBaseEsService;
import cn.nordrassil.db2doc.util.CommonUtil;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;import java.io.File;
import java.util.ArrayList;
import java.util.List;public class MyTest {public static void main(String[] args) throws Exception {String exportFolder = System.getProperty("user.dir") + File.separator + "files";String templateFile = "F:\\文档\\开发\\word模板.docx";String resultFile = exportFolder + File.separator + "result.json";//getDbInfo(resultFile);List<DbInfoDTO> ds = readData(resultFile);ExportWordParamDTO exportWordParamDTO = new ExportWordParamDTO(ds, templateFile, exportFolder, true);//DbInfoExportWordService.generateWord(exportWordParamDTO);}public static void getDbInfo(String resultFile) throws Exception {GetDbInfoService getDbInfoService = new GetDbInfoKingBaseEsService();List<DbInfoDTO> dbInfoDTOS = getDbInfoService.get(new DbConnParamDTO("192.168.1.1",5432,"root","123456","test","test",true));CommonUtil.writeString(JSONObject.toJSONString(dbInfoDTOS), resultFile);}public static List<DbInfoDTO> readData(String resultFile) {JSONArray jas = JSONObject.parseArray(CommonUtil.readByLineRs(resultFile));List<DbInfoDTO> ds = new ArrayList<>();for (int i = 0; i < jas.size(); i++) {JSONObject ja = jas.getJSONObject(i);DbInfoDTO dbInfo = new DbInfoDTO();dbInfo.setDbName(ja.getString("dbName"));dbInfo.setDbDescribe(ja.getString("dbDescribe"));List<DbInfoDTO.TableInfo> tableInfos = new ArrayList<>();dbInfo.setTableInfos(tableInfos);JSONArray tis = ja.getJSONArray("tableInfos");for (int k = 0; k < tis.size(); k++) {JSONObject ti = tis.getJSONObject(k);DbInfoDTO.TableInfo tableInfo = new DbInfoDTO.TableInfo();tableInfos.add(tableInfo);tableInfo.setTableName(ti.getString("tableName"));tableInfo.setTableDescribe(ti.getString("tableDescribe"));JSONArray tfs = ti.getJSONArray("tableFields");List<DbInfoDTO.TableField> tableFields = new ArrayList<>();tableInfo.setTableFields(tableFields);for (int j = 0; j < tfs.size(); j++) {JSONObject tf = tfs.getJSONObject(j);tableFields.add(new DbInfoDTO.TableField(tf.getString("name"),tf.getString("type"),tf.getInteger("length"),tf.getString("allowEmpty"),tf.getString("isPrimaryKey"),tf.getString("defaultValue"),tf.getString("describe")));}}ds.add(dbInfo);}return ds;}
}