package com.rlcloud.risk.util;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* @Description : 连接Mysql,表结构导出到Excel文件中
* @date 2024/3/26 10:47
* @return
* @auther xushuanglu
*/
public class MySQLGetTable {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306";
String user = "root";
String password = "123456";
try {
// 连接数据库
Connection conn = DriverManager.getConnection(url, user, password);
// 获取数据库元数据
DatabaseMetaData meta = conn.getMetaData();
// 从元数据中获取表信息
ResultSet tables = meta.getTables("databaseName", null, "%", new String[] {"TABLE"});
// 连接数据库
Connection conn1 = DriverManager.getConnection(url, user, password);
DatabaseMetaData metaData = conn1.getMetaData();
// 创建Excel工作簿和工作表
Workbook workbook = new XSSFWorkbook();
// 遍历所有表
while (tables.next()) {
//第一步获取数据库表名
String tableName = tables.getString("TABLE_NAME");
System.out.println("Table Name: " + tableName);
//一张表
ResultSet resultSet = metaData.getColumns("databaseName", null, tableName, "%");
//创建sheet
Sheet sheet = workbook.createSheet(tableName);
// 写入表头
Row header = sheet.createRow(0);
header.createCell(0).setCellValue("Name");
header.createCell(1).setCellValue("Datatype");
header.createCell(2).setCellValue("Comment");
header.createCell(3).setCellValue("Null Option");
header.createCell(4).setCellValue("Is PK");
header.createCell(5).setCellValue("Is FK");
header.createCell(5).setCellValue("tableName");
int rowNum = 1;
while (resultSet.next()) {
// 读取字段名和类型
String columnName = resultSet.getString("COLUMN_NAME");
String typeName = resultSet.getString("TYPE_NAME");
String columnSize = resultSet.getString("COLUMN_SIZE");
String remarks = resultSet.getString("REMARKS");
String tableName1 = resultSet.getString("TABLE_NAME");
// 写入数据到Excel
Row row = sheet.createRow(rowNum);
row.createCell(0).setCellValue(columnName);
row.createCell(1).setCellValue(typeName + "(" + columnSize + ")");
row.createCell(2).setCellValue(remarks);
row.createCell(3).setCellValue("NULL");
row.createCell(4).setCellValue("NO");
row.createCell(5).setCellValue("NO");
row.createCell(5).setCellValue(tableName1);
rowNum++;
}
resultSet.close();
}
// 关闭连接
conn.close();
// 关闭连接
conn1.close();
// 写入Excel文件
FileOutputStream out = new FileOutputStream("exportExcel.xlsx");
workbook.write(out);
out.close();
System.out.println("Excel文件已生成!");
} catch (SQLException | FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
}
导出样例图: