DDL with MariaDB Connector/J

Java developers can use MariaDB Connector/J to perform basic DDL (Data Definition Language) operations with MariaDB Enterprise.

DDL Operations

DDL (Data Definition Language) refers to all SQL-schema statements in the SQL standard (ISO/IEC 9075-2:2016).

Some examples of DDL include ALTER TABLE, CREATE TABLE, DROP TABLE, CREATE DATABASE, and TRUNCATE TABLE.

Code Example: CREATE TABLE

CREATE TABLE is a DDL (Data Definition Language) operation that creates a new table.

The following example program creates the test.contacts table, which is described in the example setup. If you already created the table, the example program should still work because it uses the IF NOT EXISTS clause for CREATE TABLE. The example, could be used as an alternative to running a CREATE TABLE DDL statement in MariaDB Client:

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

public class App {

    public static void main(String[] argv) {

        Properties connConfig = new Properties();
        connConfig.setProperty("user", "db_user");
        connConfig.setProperty("password", "db_user_password");

        try (Connection conn = DriverManager.getConnection("jdbc:mariadb://192.0.2.1:3306", connConfig)) {

            try (Statement stmt = conn.createStatement()) {

                stmnt.execute("CREATE TABLE IF NOT EXISTS test.contacts (id INT PRIMARY KEY AUTO_INCREMENT,first_name VARCHAR(25),last_name VARCHAR(25),email VARCHAR(100)) ENGINE=InnoDB;");
            }
        } catch (Exception e) {

            e.printStackTrace();
        }
    }
}

Confirm the table was properly created using MariaDB Client to execute a SHOW TABLES statement.

Example output:

SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| contacts       |
+----------------+

Code Example: TRUNCATE TABLE

TRUNCATE TABLE is a DDL (Data Definition Language) operation that truncates a table. TRUNCATE drops the table, and creates a new table with the same definition, in effect deleting all data from an existing table.

The following example program truncates the test.contacts table created in the example setup:

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

public class App {

    public static void main(String[] argv) {

        Properties connConfig = new Properties();
        connConfig.setProperty("user", "db_user");
        connConfig.setProperty("password", "db_user_password");

        try (Connection conn = DriverManager.getConnection("jdbc:mariadb://192.0.2.1:3306", connConfig)) {

            try (Statement stmt = conn.createStatement()) {

                stmnt.execute("TRUNCATE test.contacts");
            }
        } catch (Exception e) {

            e.printStackTrace();
        }
    }
}

Confirm the table was properly truncated using MariaDB Client to query the table's data using a SELECT statement. The statement should return an empty set, because the TRUNCATE statement deletes all data in the table.

Example output:

SELECT * from test.contacts;
Empty set (0.001 sec)