第2篇：Mysql数据库表结构导出字段到Excel(多个sheet中)

本文链接：https://blog.csdn.net/xushuanglu_csdn/article/details/137040720

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();
        }
    }
}