Java實現(xiàn)一鍵獲取Mysql所有表字段設(shè)計和建表語句的工具類

更新時間：2023年05月17日 17:01:12 作者：zyqok

這篇文章主要為大家詳細介紹了如何利用Java編寫一個工具類，可以實現(xiàn)一鍵獲取Mysql所有表字段設(shè)計和建表語句，感興趣的小伙伴可以了解一下

工具描述：一鍵獲取所有表的建表語句、一鍵獲取所有表的表字段設(shè)計

最后生成在項目的根路徑下：

說明：本工具類由于需要導(dǎo)出表格，因此需要用到我的Excel工具類。

詳見（Java實現(xiàn)Excel導(dǎo)入導(dǎo)出操作詳解）

工具類源碼：

package com.zyq.utils.mysql;
 
import com.zyq.utils.excel.ExcelUtils;
 
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.*;
import java.util.*;
 
/**
 * @author Yuanqiang.Zhang
 * @since 2023/4/11
 */
public class MySqlUtils {
 
    private static String ip = "127.0.0.1";
    private static int port = 3306;
    private static String db = "zyq";
    private static String username = "root";
    private static String password = "123456";
    private static Connection conn;
    private static Map<String, String> tables;
 
    static {
        conn = getConnection();
        tables = getAllTable();
    }
 
    public static void main(String[] args) {
        // 獲取全表的建表語句
        getAllTableCreateSql();
        // 獲取全表的表設(shè)計
        getAllTableFields();
    }
 
    /**
     * 獲取全表設(shè)計
     */
    private static void getAllTableFields() {
        List<Object> head = new ArrayList<>();
        head.add("表名稱");
        head.add("字段名稱");
        head.add("字段描述");
        head.add("字段類型");
        head.add("是否可空");
        head.add("默認值");
        List<List<Object>> dataList = new ArrayList<>();
        dataList.add(head);
        int total = tables.size();
        int current = 0;
        for (Map.Entry<String, String> each : tables.entrySet()) {
            String tableName = each.getKey();
            List<List<Object>> tableFields = getTableFields(tableName);
            dataList.addAll(tableFields);
            current++;
            System.out.println(String.format("%s/%s %s 字段獲取完成", current, total, tableName));
        }
        File file = new File("表設(shè)計.xlsx");
        ExcelUtils.exportFile(file, dataList);
        System.out.println("表設(shè)計.xlsx 生成完成?。?);
    }
 
    /**
     * 獲取單個表設(shè)計
     *
     * @param tableName 單個表名
     * @return List<List < Object>>
     */
    private static List<List<Object>> getTableFields(String tableName) {
        String sql = "SELECT * FROM information_schema.columns WHERE table_schema = '" + db + "' AND table_name = '" + tableName + "'";
        List<List<Object>> fields = new ArrayList<>();
        try {
            Statement stat = conn.createStatement();
            ResultSet rs = stat.executeQuery(sql);
            while (rs.next()) {
                String columnName = rs.getString("COLUMN_NAME");
                String columnDefault = rs.getString("COLUMN_DEFAULT");
                String columnComment = rs.getString("COLUMN_COMMENT");
                String isNullable = rs.getString("IS_NULLABLE");
                String columnType = rs.getString("COLUMN_TYPE");
                List<Object> column = new ArrayList<>();
                column.add(tableName);
                column.add(columnName);
                column.add(columnComment);
                column.add(columnType);
                column.add(isNullable);
                column.add(columnDefault);
                fields.add(column);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return fields;
    }
 
    /**
     * 獲取所有表的建表語句
     */
    private static void getAllTableCreateSql() {
        StringBuilder sb = new StringBuilder();
        int total = tables.size();
        int current = 0;
        for (Map.Entry<String, String> each : tables.entrySet()) {
            String tableName = each.getKey();
            String tableComment = each.getValue();
            String desc = "";
            if (Objects.nonNull(tableComment) && !tableComment.trim().isEmpty()) {
                desc = String.format("-- %s（%s）", tableName, tableComment);
            } else {
                desc = String.format("-- %s", tableName);
            }
            String createTableSql = getCreateTableSql(tableName);
            sb.append(desc).append("\n");
            sb.append(createTableSql).append("\n\n");
            current++;
            System.out.println(String.format("%s/%s %s 建表SQL獲取完成", current, total, tableName));
        }
        File file = new File("create_tables.sql");
        writeFileContent(file, sb.toString());
        System.out.println("create_tables.sql 文件生成?。?);
    }
 
    /**
     * 獲取某張表的建表語句
     *
     * @param tableName 表名
     * @return 建表語句
     */
    private static String getCreateTableSql(String tableName) {
        String sql = "SHOW CREATE TABLE " + tableName;
        String createTableSql = "";
        try {
            Statement stat = conn.createStatement();
            ResultSet rs = stat.executeQuery(sql);
            while (rs.next()) {
                createTableSql = rs.getString("Create Table") + ";";
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return createTableSql;
    }
 
    /**
     * 獲取所有數(shù)據(jù)表名
     *
     * @return Map<表名, 備注>
     */
    private static Map<String, String> getAllTable() {
        String sql = "SHOW TABLE status";
        Map<String, String> map = new LinkedHashMap<>();
        try {
            Statement stat = conn.createStatement();
            ResultSet rs = stat.executeQuery(sql);
            while (rs.next()) {
                String name = rs.getString("name");
                String comment = rs.getString("comment");
                map.put(name, comment);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return map;
    }
 
 
    /**
     * 獲取數(shù)據(jù)庫鏈接
     *
     * @return Connection
     */
    private static Connection getConnection() {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            String url = String.format("jdbc:mysql://%s:%s/%s", ip, port, db);
            return DriverManager.getConnection(url, username, password);
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }
 
    /**
     * 將內(nèi)容寫入到文件中
     *
     * @param file    文件
     * @param content 內(nèi)容
     */
    public static void writeFileContent(File file, String content) {
        try (FileWriter writer = new FileWriter(file)) {
            writer.write(content);
            writer.flush();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
 
}

到此這篇關(guān)于Java實現(xiàn)一鍵獲取Mysql所有表字段設(shè)計和建表語句的工具類的文章就介紹到這了,更多相關(guān)Java Mysql工具類內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: