一、背景
项目验收需要提供数据库详细的设计文档,但是如果手写,一个系统几百甚至上千个表,非常费劲,所以基于lowagie插件完成导出需求。
二、引入依赖
<dependency>
<groupId>com.lowagie</groupId>
<artifactId>itext</artifactId>
<version>2.1.7</version>
</dependency>
<dependency>
<groupId>com.lowagie.text</groupId>
<artifactId>com.springsource.com.lowagie.text</artifactId>
<version>2.0.8</version>
</dependency>
三、代码实现
⚠️注意:
- 按照你需要的类型来进行查询,详细可参考information_schema.COLUMNS表所包含的所有值
这里只是举个例子
ORDINAL_POSITION:表示序号
column_comment表示字段的注释
column_type表示字段的类型(例如 int(32))
IS_NULLABLE 表示字段是否为空(YES为NULL,NO为NOT NULL)
COLUMN_KEY表示索引类型(主键)
extra 额外列信息(例如是否自增,是否为系统时间戳等等)
SELECT MAX(b.ORDINAL_POSITION) ORDINAL_POSITION ,MAX(b.column_comment),b.COLUMN_NAME,MAX(b.column_type) ,MAX(b.IS_NULLABLE),MAX(b.COLUMN_KEY),MAX(b.EXTRA) FROM information_schema.COLUMNS b WHERE b.TABLE_NAME = '{table_name}' GROUP BY COLUMN_NAME ORDER BY ORDINAL_POSITION asc
- 因为文档要求“数据说明”的格式是 索引类型+额外列信息+是否为空,所以代码里我会把这一条数据的三个字段塞到一个单元格里,这里需要额外注意下。
import com.lowagie.text.Cell;
import com.lowagie.text.Document;
import com.lowagie.text.PageSize;
import com.lowagie.text.Paragraph;
import com.lowagie.text.Table;
import com.lowagie.text.rtf.RtfWriter2;
import java.awt.*;
import java.sql.Statement;
import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
/**
* @Description:
* @createDate : 2023/10/12 4:32 下午
**/
public class GenTableDocUtils {
/**
* 键类型字典
*/
private static Map<String, String> keyType = new HashMap<String, String>();
//初始化jdbc
static {
try {
keyType.put("P", "主键");
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
//数据库名
private static String databaseName = "tm";
//url
private static String url = "jdbc:mysql://127.0.0.1:3306/" + databaseName + "?allowPublicKeyRetrieval=true&useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&useSSL=false&verifyServerCertificate=false&serverTimezone=Asia/Shanghai&autoReconnect=true";
//用户名
private static String username = "root";
//密码
private static String password = "root";
//查询所有表的sql语句
private static String sql_get_all_tables = "SELECT TABLE_NAME,TABLE_COMMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = '" + databaseName + "' ORDER BY TABLE_NAME;";
//查询所有字段的sql语句
private static String sql_get_all_columns = "SELECT MAX(b.ORDINAL_POSITION) ORDINAL_POSITION ,MAX(b.column_comment),b.COLUMN_NAME,MAX(b.column_type) ,MAX(b.IS_NULLABLE),MAX(b.COLUMN_KEY),MAX(b.EXTRA) FROM information_schema.COLUMNS b WHERE b.TABLE_NAME = '{table_name}' GROUP BY COLUMN_NAME ORDER BY ORDINAL_POSITION asc";
public static void main(String[] args) throws Exception {
//初始化word文档
Document document = new Document(PageSize.A4);
//这里填写文档的保存路径
RtfWriter2.getInstance(document, new FileOutputStream("/Users/xingxuanming/Downloads/123.doc"));
document.open();
//查询开始
Connection conn = getConnection();
//获取所有表
java.util.List tables = getDataBySQL(sql_get_all_tables, conn);
int i = 1;
for (Iterator iterator = tables.iterator(); iterator.hasNext(); ) {
String[] arr = (String[]) iterator.next();
//循环获取字段信息
String tableName = arr[0];
System.out.print(i + ".正在处理数据表-----------" + arr[0]);
addTableMetaData(document, arr, i);
List columns = getDataBySQL(sql_get_all_columns.replace("{table_name}", arr[0]), conn);
addTableDetail(document, columns);
addBlank(document);
System.out.println("...done");
i++;
}
document.close();
conn.close();
}
/**
* 添加一个空行
*
* @param document
* @throws Exception
*/
public static void addBlank(Document document) throws Exception {
Paragraph ph = new Paragraph("");
ph.setAlignment(Paragraph.ALIGN_LEFT);
document.add(ph);
}
/**
* 添加包含字段详细信息的表格
*
* @param document
* @param columns
* @throws Exception
*/
public static void addTableDetail(Document document, List columns) throws Exception {
Table table = new Table(5);
table.setWidth(100f);
table.setBorderWidth(1);
table.setBorderColor(Color.BLACK);
table.setPadding(0);
table.setSpacing(0);
Cell cell1 = new Cell("序号");
cell1.setHeader(true);
Cell cell9 = new Cell("中文描述");
cell9.setHeader(true);
Cell cell2 = new Cell("列名");
cell2.setHeader(true);
Cell cell3 = new Cell("类型及精度");
cell3.setHeader(true);
Cell cell6 = new Cell("数据说明");
cell6.setHeader(true);
//设置表头格式
table.setWidths(new float[]{8f, 25f, 15f, 12f, 20f});
cell1.setHorizontalAlignment(Cell.ALIGN_CENTER);
cell2.setHorizontalAlignment(Cell.ALIGN_CENTER);
cell3.setHorizontalAlignment(Cell.ALIGN_CENTER);
cell6.setHorizontalAlignment(Cell.ALIGN_CENTER);
cell9.setHorizontalAlignment(Cell.ALIGN_CENTER);
table.addCell(cell1);
table.addCell(cell9);
table.addCell(cell2);
table.addCell(cell3);
table.addCell(cell6);
table.endHeaders();// 表头结束
for (Iterator iterator = columns.iterator(); iterator.hasNext(); ) {
String[] arr2 = (String[]) iterator.next();
Cell c1 = new Cell(arr2[0]);
Cell c2 = new Cell(arr2[1]);
Cell c3 = new Cell(arr2[2]);
Cell c7 = new Cell(arr2[3]);
Cell c8 = new Cell(arr2[5] + " " + arr2[6] + " " + (arr2[4].equals("YES") ? "NULL" : "NOT NULL"));
c1.setHorizontalAlignment(Cell.ALIGN_CENTER);
c2.setHorizontalAlignment(Cell.ALIGN_CENTER);
c3.setHorizontalAlignment(Cell.ALIGN_CENTER);
c7.setHorizontalAlignment(Cell.ALIGN_CENTER);
c8.setHorizontalAlignment(Cell.ALIGN_CENTER);
table.addCell(c1);
table.addCell(c2);
table.addCell(c3);
table.addCell(c7);
table.addCell(c8);
}
document.add(table);
}
/**
* 增加表概要信息
*
* @param dcument
* @param arr
* @param i
* @throws Exception
*/
public static void addTableMetaData(Document dcument, String[] arr, int i) throws Exception {
Paragraph ph = new Paragraph(i + ". 表名: " + arr[0] + " 说明: " + (arr[1] == null ? "" : arr[1]));
ph.setAlignment(Paragraph.ALIGN_LEFT);
dcument.add(ph);
}
/**
* 把SQL语句查询出列表
*
* @param sql
* @param conn
* @return
*/
public static java.util.List getDataBySQL(String sql, Connection conn) {
Statement stmt = null;
ResultSet rs = null;
List list = new ArrayList();
try {
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
while (rs.next()) {
String[] arr = new String[rs.getMetaData().getColumnCount()];
for (int i = 0; i < arr.length; i++) {
arr[i] = rs.getString(i + 1);
}
list.add(arr);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (rs != null) {
rs.close();
}
if (stmt != null) {
stmt.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
return list;
}
/**
* 获取数据库连接
*
* @return
*/
public static Connection getConnection() {
try {
return DriverManager.getConnection(url, username, password);
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
}