DML with MariaDB Connector/J

Overview

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

DML Operations

DML (Data Manipulation Language) refers to all SQL-data statements in the SQL standard (ISO/IEC 9075-2:2016), for example, DELETEDELETE, INSERTINSERT, REPLACEREPLACE, SELECTSELECT, and UPDATEUPDATE.

Code Example: INSERT, UPDATE, DELETE

INSERTINSERT, UPDATEUPDATE, and DELETEDELETE are DML (Data Manipulation Language) operations that modify the data in a table.

The following code demonstrates how to execute INSERTINSERT on the test.contacts table created in the example setup.

To update or delete data, replace the INSERTINSERT statement in the code example with an UPDATEUPDATE or DELETEDELETE statement.

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

public class App {

    public static void main(String[] argv) {
        String[][] contacts = { { "John", "Smith", "john.smith@example.com" },{ "Jon", "Smith", "jon.smith@example.com" }, { "Johnny", "Smith", "johnny.smith@example.com" } };
        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 (PreparedStatement stmnt = conn.prepareStatement("INSERT INTO test.contacts (first_name, last_name, email) VALUES (?, ?, ?)")) {
                for (String[] contact_entry : contacts) {
                    stmnt.setString(1, contact_entry[0]);
                    stmnt.setString(2, contact_entry[1]);
                    stmnt.setString(3, contact_entry[2]);

                    stmnt.executeQuery();
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Confirm that the data was properly inserted using MariaDB Client to execute a SELECTSELECT statement.

Example output:

SELECT * from test.contacts;
+----+------------+-----------+------------------------+
| id | first_name | last_name | email                  |
+----+------------+-----------+------------------------+
|  1 | John       | Smith     | john.smith@example.com |
+----+------------+-----------+------------------------+
|  2 | Jon        | Smith     | jon.smith@example.com  |
+----+------------+-----------+------------------------+
|  3 | Johnny     | Smith     |johnny.smith@example.com|
+----+------------+-----------+------------------------+

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) {
        String[][] contacts = { { "John", "Smith", "john.smith@example.com" },{ "Jon", "Smith", "jon.smith@example.com" }, { "Johnny", "Smith", "johnny.smith@example.com" } };
        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 (PreparedStatement stmnt = conn.prepareStatement("INSERT INTO test.contacts (first_name, last_name, email) VALUES (?, ?, ?)")) {
                for (String[] contact_entry : contacts) {
                    stmnt.setString(1, contact_entry[0]);
                    stmnt.setString(2, contact_entry[1]);
                    stmnt.setString(3, contact_entry[2]);

                    stmnt.executeQuery();
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Confirm that the data was properly inserted using MariaDB Client to execute a SELECT statement.

Example output:

SELECT * from test.contacts;
+----+------------+-----------+------------------------+
| id | first_name | last_name | email                  |
+----+------------+-----------+------------------------+
|  1 | John       | Smith     | john.smith@example.com |
+----+------------+-----------+------------------------+
|  2 | Jon        | Smith     | jon.smith@example.com  |
+----+------------+-----------+------------------------+
|  3 | Johnny     | Smith     |johnny.smith@example.com|
+----+------------+-----------+------------------------+

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) {
        String[][] contacts = { { "John", "Smith", "john.smith@example.com" },{ "Jon", "Smith", "jon.smith@example.com" }, { "Johnny", "Smith", "johnny.smith@example.com" } };
        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 (PreparedStatement stmnt = conn.prepareStatement("INSERT INTO test.contacts (first_name, last_name, email) VALUES (?, ?, ?)")) {
                for (String[] contact_entry : contacts) {
                    stmnt.setString(1, contact_entry[0]);
                    stmnt.setString(2, contact_entry[1]);
                    stmnt.setString(3, contact_entry[2]);

                    stmnt.executeQuery();
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Confirm that the data was properly inserted using MariaDB Client to execute a SELECT statement.

Example output:

SELECT * from test.contacts;
+----+------------+-----------+------------------------+
| id | first_name | last_name | email                  |
+----+------------+-----------+------------------------+
|  1 | John       | Smith     | john.smith@example.com |
+----+------------+-----------+------------------------+
|  2 | Jon        | Smith     | jon.smith@example.com  |
+----+------------+-----------+------------------------+
|  3 | Johnny     | Smith     |johnny.smith@example.com|
+----+------------+-----------+------------------------+

Code Example: SELECT

SELECTSELECT is a DML (Data Manipulation Language) operation that reads the data from a table.

The following code demonstrates how to execute SELECTSELECT on 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()) {
                try (ResultSet contact_list = stmnt.executeQuery("SELECT first_name, last_name, email FROM test.contacts")) {
                    while (contact_list.next()) {
                        System.out.println(String.format("%s %s <%s>",
                            contact_list.getString("first_name"),
                            contact_list.getString("last_name"),
                            contact_list.getString("email")));
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Example output:

John Smith <john.smith@example.com>
Jon Smith <jon.smith@example.com>
Johnny Smith <johnny.smith@example.com>

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()) {
                try (ResultSet contact_list = stmnt.executeQuery("SELECT first_name, last_name, email FROM test.contacts")) {
                    while (contact_list.next()) {
                        System.out.println(String.format("%s %s <%s>",
                            contact_list.getString("first_name"),
                            contact_list.getString("last_name"),
                            contact_list.getString("email")));
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Example output:

John Smith <john.smith@example.com>
Jon Smith <jon.smith@example.com>
Johnny Smith <johnny.smith@example.com>

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()) {
                try (ResultSet contact_list = stmnt.executeQuery("SELECT first_name, last_name, email FROM test.contacts")) {
                    while (contact_list.next()) {
                        System.out.println(String.format("%s %s <%s>",
                            contact_list.getString("first_name"),
                            contact_list.getString("last_name"),
                            contact_list.getString("email")));
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Example output:

John Smith <john.smith@example.com>
Jon Smith <jon.smith@example.com>
Johnny Smith <johnny.smith@example.com>