DML with MariaDB Connector/J

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

DML Operations

DML (Data Manipulation Language) refers to all SQL-data statements in the SQL standard (ISO/IEC 9075-2:2016), for example, DELETE, INSERT, REPLACE, SELECT, and UPDATE.

Code Example: INSERT, UPDATE, DELETE

INSERT, UPDATE, and DELETE are DML (Data Manipulation Language) operations that modify the data in a table.

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

To update or delete data, replace the INSERT statement in the code example with an UPDATE or DELETE 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");

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

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

The following code demonstrates how to execute SELECT 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://192.0.2.1:3306", 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>