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
DESCRIBEstatement for each table name. - Read table structure information such as
field,type,null,key,defaultandextra. - 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>
