How do I export MySQL database schema into markdown format?

By Wayan in JDBC 0 Comment

The following code example demonstrate how to export MySQL database schema into markdown table format. We get the table structure information by executing MySQL’s DESCRIBE statement.

The steps we do in the code snippet below:

  • Connect to the database.
  • We obtain the list of table name from the database / schema.
  • Executes DESCRIBE statement for each table name.
  • Read table structure information such as field, type, null, key, default and extra.
  • Write the information into markdown table format and save it into table.md.

And here are the complete code snippet.

package org.kodejava.jdbc;

import java.io.BufferedWriter;
import java.io.FileWriter;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class DescribeMySQLToMarkDown {
    private static final String URL = "jdbc:mysql://localhost/kodejava";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "";

    public static void main(String[] args) {
        String tableQuery = """
                select table_name
                from information_schema.tables
                where table_schema = 'kodejava'
                  and table_type = 'BASE TABLE'
                order by table_name;
                """;

        try (Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD)) {
            Statement stmt = connection.createStatement();
            ResultSet resultSet = stmt.executeQuery(tableQuery);
            List<String> tables = new ArrayList<>();
            while (resultSet.next()) {
                tables.add(resultSet.getString("table_name"));
            }

            System.out.println(tables.size() + " tables found.");

            try (BufferedWriter writer = new BufferedWriter(new FileWriter("table.md"))) {
                for (String table : tables) {
                    System.out.println("Processing table: " + table);
                    Statement statement = connection.createStatement();
                    ResultSet descResult = statement.executeQuery("DESCRIBE " + table);

                    writer.write(String.format("Table Name: **%s**%n%n", table));
                    writer.write("| Field Name | Data Type | Null | Key | Default | Extra |\n");
                    writer.write("|:---|:---|:---|:---|:---|:---|\n");
                    while (descResult.next()) {
                        String field = descResult.getString("field");
                        String type = descResult.getString("type");
                        String nullInfo = descResult.getString("null");
                        String key = descResult.getString("key");
                        String defaultInfo = descResult.getString("default");
                        String extra = descResult.getString("extra");
                        String line = String.format("| %s | %s | %s | %s | %s | %s |%n",
                                field, type, nullInfo, key, defaultInfo, extra);
                        writer.write(line);
                    }
                    writer.write("\n<br/>\n<br/>\n");
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

This code snippet will produce something like below. I have tidy up the markdown for a better presentation.

Table Name: **books**

| Field Name | Data Type       | Null | Key | Default | Extra          |
|:-----------|:----------------|:-----|:----|:--------|:---------------|
| id         | bigint unsigned | NO   | PRI | null    | auto_increment |
| isbn       | varchar(30)     | NO   |     | null    |                |

<br/>
<br/>

Maven dependencies

<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>8.1.0</version>
</dependency>

Maven Central

Wayan
Latest posts by Wayan (see all)
Views: 1,552

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.