DDL with MariaDB Connector/J

Overview

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

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 TABLEALTER TABLE, CREATE TABLECREATE TABLE, DROP TABLEDROP TABLE, CREATE DATABASECREATE DATABASE, and TRUNCATE TABLETRUNCATE TABLE.

Code Example: CREATE TABLE

CREATE TABLECREATE 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 TABLECREATE TABLE. The example, could be used as an alternative to running a CREATE TABLECREATE 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://HOST:PORT", 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 TABLESSHOW TABLES statement.

Example output:

SHOW TABLES;

+----------------+
| Tables_in_test |
+----------------+
| contacts       |
+----------------+

Connector/J 3.0

In MariaDB Connector/J 3.0, TLS is enabled for connections to SkySQL using the sslMode parameter.

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");
        connConfig.setProperty("sslMode", "verify-full");
        connConfig.setProperty("serverSslCert", "/path/to/skysql_chain.pem");

        try (Connection conn = DriverManager.getConnection("jdbc:mariadb://HOST:PORT", 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       |
+----------------+

Connector/J 2.7

In MariaDB Connector/J 2.7 and before, TLS is enabled for connections to SkySQL using the useSsl parameter.

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");
        connConfig.setProperty("useSsl", "true");
        connConfig.setProperty("serverSslCert", "/path/to/skysql_chain.pem");

        try (Connection conn = DriverManager.getConnection("jdbc:mariadb://HOST:PORT", 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 TABLETRUNCATE 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://HOST:PORT", 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 SELECTSELECT statement. The statement should return an empty set, because the TRUNCATETRUNCATE statement deletes all data in the table.

Example output:

SELECT * from test.contacts;

Empty set (0.001 sec)

Connector/J 3.0

In MariaDB Connector/J 3.0, TLS is enabled for connections to SkySQL using the sslMode parameter.

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://HOST:PORT", 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)

Connector/J 2.7

In MariaDB Connector/J 2.7 and before, TLS is enabled for connections to SkySQL using the useSsl parameter.

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://HOST:PORT", 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)