How do I export MySQL database schema into markdown format?

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

How do I create MySQL database programmatically in Java?

There are times that you might need to create a database or tables right after you run your program instead of manually creating it. In this example, I will show you how you can do this using JDBC and MySQL database. The first thing we need to do as usual when creating a JDBC program is to define a JDBC URL. One thing that you’ll notice here is that we don’t define the database name in the URL. So the URL will be like jdbc:mysql://localhost.

After defining the URL, we need to create a connection to the database. We issued the DriverManager.getConnection() method and pass the URL, username and password as the arguments. The next step is to create a PreparedStatement. When we call the preparedStatement() method we pass an SQL command to create the database, which is CREATE DATABASE IF NOT EXISTS demodb. This will create a new database if there is no database with demodb name exists in the database. Finally, call the PreparedStatement‘s execute() method

Now you can try for your self, start typing the following code snippet in your text editor or IDE, and execute it to create the database.

package org.kodejava.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class CreateMySQLDatabaseExample {
    public static void main(String[] args) {
        // Defines the JDBC URL. As you can see, we are not specifying
        // the database name in the URL.
        String url = "jdbc:mysql://localhost";

        // Defines username and password to connect to database server.
        String username = "root";
        String password = "root";

        // SQL command to create a database in MySQL.
        String sql = "CREATE DATABASE IF NOT EXISTS demodb";

        try (Connection conn = DriverManager.getConnection(url, username, password);
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.execute();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

After you are executing the code snippet above you will find a new database named demodb created in your MySQL database server.

Maven Dependencies

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

Maven Central

How to establish connection to a database using Properties object?

In the following code snippet, you will see how to pass some connection arguments when connecting to a database. To do this, we can use the java.util.Properties class. We can put some key value pairs as a connection arguments to the Properties object
before we pass this information into the DriverManager class.

Let’s see the example below:

package org.kodejava.jdbc;

import java.sql.*;
import java.util.Properties;

public class GetConnectionWithProperties {
    private static final String URL = "jdbc:mysql://localhost/kodejava";
    private static final String USERNAME = "kodejava";
    private static final String PASSWORD = "s3cr*t";

    public static void main(String[] args) {
        GetConnectionWithProperties demo = new GetConnectionWithProperties();
        try (Connection connection = demo.getConnection()) {
            // do something with the connection.
            Statement stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM product");
            while (rs.next()) {
                System.out.println("Code = " + rs.getString("code"));
                System.out.println("Name = " + rs.getString("name"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private Connection getConnection() throws SQLException {
        Properties connectionProps = new Properties();
        connectionProps.put("user", USERNAME);
        connectionProps.put("password", PASSWORD);

        Connection connection = DriverManager.getConnection(URL, connectionProps);
        System.out.println("Connected to database.");
        return connection;
    }
}

Maven Dependencies

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

Maven Central

How to automatically close resources in JDBC?

One thing that we need to do manually when programming using JDBC is to make sure to close all the resources that we use. All resources including the ResultSet, Statement and Connection must be closed. This will usually produce a lot of boilerplate code in our program.

Starting from JDBC 4.1, which is a part of Java 7, we can use the try-with-resources statement to automatically manage the resources that we use. This try statement closes the resources used when the block finishes its execution either normally or abruptly.

Here is an example that shows us how to use the try-with-resources statement.

package org.kodejava.jdbc;

import java.sql.*;

public class TryWithResourceJdbc {
    private static final String URL = "jdbc:mysql://localhost/kodejava";
    private static final String USERNAME = "kodejava";
    private static final String PASSWORD = "s3cr*t";

    public static void main(String[] args) {
        try (Connection conn =
                     DriverManager.getConnection(URL, USERNAME, PASSWORD);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery("SELECT * FROM product")) {

            while (rs.next()) {
                String code = rs.getString("code");
                String name = rs.getString("name");

                System.out.println("Code: " + code + "; Name: " + name);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Maven Dependencies

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

Maven Central