获取虚谷数据库所有表名、表注释、字段名、字段类型、字段注释到word中
<!--虚谷数据库-->
<dependency>
<groupId>com.xugudb</groupId>
<artifactId>xugu-jdbc</artifactId>
<version>12.3.2</version>
</dependency>
import lombok.Data;
import org.apache.poi.xwpf.usermodel.*;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
public class XuGuToWord {
private static String synchronousIp = "10.10.12.133";
private static String synchronousPort = "5138";
private static String synchronousUserName = "SYSDBA";
private static String synchronousPassWord = "SYSDBA";
private static String synchronousDataBase = "SYSTEM";
private static String synchronousCurrentSchema;
/**
* 构建jdbc url
*/
private static String buildJdbcUrl() {
return String.format("jdbc:xugu://%s:%s/%s?current_schema=%s&useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&serverTimezone=Asia/Shanghai",
synchronousIp, synchronousPort, synchronousDataBase, synchronousCurrentSchema);
}
public static void main(String[] args) {
//排除数据库列表
List<String> excludeDataBaseList = new ArrayList<>();
excludeDataBaseList.add("SYSDBA");
excludeDataBaseList.add("SYSSSO");
excludeDataBaseList.add("SYSAUDITOR");
excludeDataBaseList.add("GUEST");
excludeDataBaseList.add("IRSUSER");
excludeDataBaseList.add("PROJECTMGTX_REPORT");
excludeDataBaseList.add("PROJECTMGTX_PAY");
excludeDataBaseList.add("PROJECTMGTX_MP");
excludeDataBaseList.add("PROJECTMGTX_CONFIG");
excludeDataBaseList.add("PROJECTMGTX_APP");
List<String> dataBaseList = new ArrayList<>();
try (Connection conn = DriverManager.getConnection(buildJdbcUrl(), synchronousUserName, synchronousPassWord);
Statement st = conn.createStatement()) {
//获取所有的数据库
String sql = "SELECT schema_name FROM dba_schemas";
ResultSet rs = st.executeQuery(sql);
while (rs.next()) {
String dataBase = rs.getString("schema_name");
if (excludeDataBaseList.contains(dataBase)) {
continue;
}
dataBaseList.add(dataBase);
}
List<Map<String, String>> tableCommentMapList = new ArrayList<>();
for (String dataBase : dataBaseList) {
sql = "SELECT c.schema_name,a.table_name,b.col_name,b.col_no,b.type_name,b.scale,b.type_name,'cc',b.comments,a.comments as tableComment " +
"FROM dba_tables a LEFT JOIN dba_columns b ON a.table_id = b.table_id LEFT JOIN dba_schemas c ON a.schema_id = c.schema_id" +
" WHERE c.schema_name = " + "'" + dataBase + "'";
rs = st.executeQuery(sql);
// 使用Map来存储每个表的信息
Map<String, TableEntity> tableMap = new LinkedHashMap<>();
while (rs.next()) {
String schemaName = rs.getString("schema_name");
String tableName = rs.getString("table_name");
String colName = rs.getString("col_name");
// int colNo = rs.getInt("col_no");
String typeName = rs.getString("type_name");
// int scale = rs.getInt("scale");
String comments = rs.getString("comments");
String tableComment = rs.getString("tableComment");
// 如果表还不存在于map中，则创建新的TableEntity
if (!tableMap.containsKey(schemaName + ": " + tableName)) {
TableEntity tentity = new TableEntity();
tentity.setSchemaName(schemaName);
tentity.setTableName(tableName);
tentity.setTableComment(tableComment);
tentity.setFiled(new ArrayList<>());
tentity.setFiled_type(new ArrayList<>());
tentity.setComment(new ArrayList<>());
tableMap.put(schemaName + ": " + tableName, tentity);
tableCommentMapList.add(new LinkedHashMap<>() {{
put("tableName", schemaName + ": " + tableName);
put("tableComment", tableComment);
}});
}
// 获取表实体并添加字段信息
TableEntity tentity = tableMap.get(schemaName + ": " + tableName);
tentity.filed.add(colName);
tentity.filed_type.add(typeName);
tentity.comment.add(comments);
}
// 为每个表写入word
for (Map.Entry<String, TableEntity> entry : tableMap.entrySet()) {
writeWord(entry.getValue());
}
}
writeWordTable(tableCommentMapList);
} catch (Exception e) {
e.printStackTrace();
}
}
// 假设存在一个实体类来存储表信息
@Data
static class TableEntity {
String schemaName;
String tableName;
String tableComment;
List<String> filed;
List<String> filed_type;
List<String> comment;
}
/**
* 将表信息写入Word文档
*/
public static void writeWord(TableEntity tentity) {
try {
XWPFDocument d;
String templatePath = "./table.docx";
// 检查模板文件是否存在
File templateFile = new File(templatePath);
if (templateFile.exists()) {
// 加载现有的Word文档
d = new XWPFDocument(new FileInputStream(templatePath));
} else {
// 创建新的Word文档
d = new XWPFDocument();
System.out.println("模板文件不存在，创建新文档");
}
// 创建标题
String title = tentity.getSchemaName() + ":" + tentity.getTableName() + "(" + tentity.getTableComment() + ")";
System.out.println("标题 " + title);
// 添加标题段落
XWPFParagraph paragraph = d.createParagraph();
XWPFRun run = paragraph.createRun();
run.setText(title);
run.setBold(true);
// 计算表格行数
int column = tentity.filed.size() + 1;
int rownu = 3;
// 创建表格
XWPFTable t = d.createTable(column, rownu);
// 写入表头
XWPFTableRow headerRow = t.getRow(0);
headerRow.getCell(0).setText("字段");
headerRow.getCell(1).setText("数据类型");
headerRow.getCell(2).setText("注释");
// 填充表格数据
for (int i = 1; i < column; i++) {
XWPFTableRow row = t.getRow(i);
row.getCell(0).setText(tentity.filed.get(i - 1));
row.getCell(1).setText(tentity.filed_type.get(i - 1));
row.getCell(2).setText(tentity.comment.get(i - 1));
}
// 保存文档
FileOutputStream out = new FileOutputStream(templatePath);
d.write(out);
out.close();
d.close();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 将表信息写入Word文档
*/
public static void writeWordTable(List<Map<String, String>> tableCommentMapList) {
try {
XWPFDocument d;
String templatePath = "./table.docx";
// 检查模板文件是否存在
File templateFile = new File(templatePath);
if (templateFile.exists()) {
// 加载现有的Word文档
d = new XWPFDocument(new FileInputStream(templatePath));
} else {
// 创建新的Word文档
d = new XWPFDocument();
System.out.println("模板文件不存在，创建新文档");
}
// 创建标题
String title = "数据库表目录";
System.out.println("标题 " + title);
// 添加标题段落
XWPFParagraph paragraph = d.createParagraph();
XWPFRun run = paragraph.createRun();
run.setText(title);
run.setBold(true);
// 计算表格行数
int column = tableCommentMapList.size() + 1;
int rownu = 3;
// 创建表格
XWPFTable t = d.createTable(column, rownu);
// 写入表头
XWPFTableRow headerRow = t.getRow(0);
headerRow.getCell(0).setText("序号");
headerRow.getCell(1).setText("表名");
headerRow.getCell(2).setText("表名描述");
// 填充表格数据
for (int i = 1; i < column; i++) {
Map<String, String> stringStringMap = tableCommentMapList.get(i - 1);
XWPFTableRow row = t.getRow(i);
row.getCell(0).setText(String.valueOf(i));
row.getCell(1).setText(stringStringMap.get("tableName"));
row.getCell(2).setText(stringStringMap.get("tableComment"));
}
// 保存文档
FileOutputStream out = new FileOutputStream(templatePath);
d.write(out);
out.close();
d.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}