直接上代码
import com.fly.entity.Column;
import org.apache.poi.xwpf.usermodel.*;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
public class TableDesignDocGenerator {
private Connection connection;
public TableDesignDocGenerator(String url, String username, String password) {
try {
connection= DriverManager.getConnection(url, username, password);
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 根据表生成相应的表设计文档
* @param tableName 表名
*/
private List<Column> generateDesignDocFromTable(String tableName) {
String sql = "SELECT a.attnum AS number,\n" +
"a.attname AS name,\n" +
"t.typname AS type,\n" +
"a.attlen AS length,\n" +
"a.atttypmod AS lengthvar,\n" +
"a.attnotnull AS notnull,\n" +
"b.description AS comment\n" +
"FROM pg_class c,\n" +
"pg_attribute a LEFT OUTER JOIN pg_description b\n" +
"ON a.attrelid=b.objoid AND a.attnum = b.objsubid,\n" +
"pg_type t\n" +
"WHERE c.relname = '" + tableName + "'\n" +
"and a.attnum > 0\n" +
"and a.attrelid = c.oid\n" +
"and a.atttypid = t.oid\n" +
"ORDER BY a.attnum";
List<Column> columns = new ArrayList<>();
// 1.connect the database and retrieve the table information
try (
PreparedStatement preparedStatement = connection.prepareStatement(sql);
ResultSet resultSet = preparedStatement.executeQuery()
) {
while (resultSet.next()) {
Integer number = resultSet.getInt("number");
String name = resultSet.getString("name");
String type = resultSet.getString("type");
Integer length = resultSet.getInt("length");
Integer varcharLength = null;
String comment = resultSet.getString("comment");
boolean notNull = resultSet.getBoolean("notNull");
String content = "";
String description = "";
if ("varchar".equals(type)) {
varcharLength = resultSet.getInt("lengthvar") - 4;
}
if (comment != null) {
description = comment;
Pattern pattern = Pattern.compile("^[\\u0391-\\u9fa5a-zA-Z1-9]+");
Matcher matcher = pattern.matcher(comment);
if (matcher.find()) {
content = matcher.group();
}
}
Column column = new Column(name, type, content, description, number, varcharLength, length, notNull);
columns.add(column);
}
} catch (SQLException e) {
e.printStackTrace();
}
return columns;
}
private void generateDocTable(String tableName, XWPFDocument document) {
List<Column> columns = generateDesignDocFromTable(tableName);
// 2.开始写入word
XWPFTable table= document.createTable(columns.size() + 1, 5);//创建一个表格
XWPFTableRow headRow = table.getRow(0);
setHeaderCellText(headRow.getCell(0), "字段名称");
setHeaderCellText(headRow.getCell(1), "字段类型");
setHeaderCellText(headRow.getCell(2), "字段长度");
setHeaderCellText(headRow.getCell(3), "是否为空");
setHeaderCellText(headRow.getCell(4), "字段描述");
for (int i = 0; i < columns.size(); i++) {
System.out.println(columns.get(i));
XWPFTableRow row = table.getRow(i + 1);
setCellText(row.getCell(0), columns.get(i).getName() + "");
setCellText(row.getCell(1), columns.get(i).getType());
setCellText(row.getCell(2), columns.get(i).getVarcharLength() == null ? "" : columns.get(i).getVarcharLength().toString());
setCellText(row.getCell(3), columns.get(i).getNotNull() ? "否" : "是");
setCellText(row.getCell(4), columns.get(i).getDescribe());
}
}
/**
* 将一张表写入文档
* @param tableName 表名称
* @param path 路径
*/
public void writeTableToOneDoc(String tableName, String path) {
try (FileOutputStream out = new FileOutputStream(path + (path.endsWith("/") ? "" : "/") + tableName + ".doc")) {
XWPFDocument document = new XWPFDocument();
XWPFParagraph paragraph = document.createParagraph();
// 标题
paragraph.setStyle("标题 2");
XWPFRun run = paragraph.createRun();
run.setFontSize(24);
run.setBold(true);
run.setText(tableName);
// 生成表
generateDocTable(tableName, document);
document.write(out);
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 将数据库中所有表写入一个文档
* @param documentName 文档名称
* @param path 文档路径
*/
public void writeDatabaseToOneDoc(String documentName, String path) {
String sql = "SELECT tablename FROM pg_tables\n" +
"WHERE tablename NOT LIKE 'pg%'\n" +
"AND tablename NOT LIKE 'sql_%' \n" +
"ORDER BY tablename";
List<String> tableNameList = new ArrayList<>();
// 1.connect the database and retrieve the table information
try (
PreparedStatement preparedStatement = connection.prepareStatement(sql);
ResultSet resultSet = preparedStatement.executeQuery()
) {
while (resultSet.next()) {
String tableName = resultSet.getString("tablename");
tableNameList.add(tableName);
}
} catch (SQLException e) {
e.printStackTrace();
}
try (FileOutputStream out = new FileOutputStream(path + (path.endsWith("/") ? "" : "/") + documentName + ".doc")) {
XWPFDocument document = new XWPFDocument();
for (String tableName : tableNameList) {
XWPFParagraph paragraph = document.createParagraph();
// 标题
paragraph.setStyle("标题 2");
XWPFRun run = paragraph.createRun();
run.setFontSize(24);
run.setBold(true);
run.setText(tableName);
// 生成表
generateDocTable(tableName, document);
}
document.write(out);
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 写入某个单元格
* @param cell
* @param text
*/
private void setCellText(XWPFTableCell cell, String text) {
XWPFParagraph pIO =cell.addParagraph();
cell.removeParagraph(0);
XWPFRun rIO = pIO.createRun();
rIO.setFontFamily("宋体");
rIO.setColor("000000");
rIO.setFontSize(12);
rIO.setText(text);
}
/**
* 写入某个单元格，带灰色底纹
* @param cell
* @param text
*/
private void setHeaderCellText(XWPFTableCell cell, String text) {
XWPFParagraph p =cell.addParagraph();
cell.removeParagraph(0);
cell.setColor("BBBBBB");
XWPFRun r = p.createRun();
r.setFontFamily("宋体");
r.setColor("000000");
r.setFontSize(12);
r.setText(text);
}
}
public class Column {
String name;
String type;
String content;
String describe;
Integer number;
Integer varcharLength;
Integer length;
Boolean notNull;
public Column() {
}
public Column(String name, String type, String content, String describe, Integer number, Integer varcharLength, Integer length, Boolean notNull) {
this.name = name;
this.type = type;
this.content = content;
this.describe = describe;
this.number = number;
this.varcharLength = varcharLength;
this.length = length;
this.notNull = notNull;
}
}
网友评论