DML with MariaDB Connector/J

This page is part of MariaDB's Documentation.

The parent of this page is: MariaDB Connector/J

Topics on this page:

Overview
DML Operations
Related Connection Parameters
Code Example: INSERT, UPDATE, DELETE
- Connector/J 3.0
- Connector/J 2.7
Code Example: SELECT
- Connector/J 3.0
- Connector/J 2.7

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, DELETE DELETE, INSERT INSERT, REPLACE REPLACE, SELECT SELECT, and UPDATE UPDATE.

Related Connection Parameters

The following connection parameters are related to prepared statements, which are very useful for executing DML:

Parameter Name	Description	Data Type	Default Value	Version Added	Version Removed
`cachePrepStmts`	Defines whether the prepared statement cache is enabled.	`boolean`	`true`	1.3.0	3.0.3
`prepStmtCacheSize`	Defines the number of prepared statements that are cached for each connection. This parameter only applies if `cachePrepStmts` is enabled.	`int`	`250`	1.3.0
`prepStmtCacheSqlLimit`	Defines the maximum length for a prepared statement in the cache. This parameter only applies if `cachePrepStmts` is enabled.	`int`	`2048`	1.3.0	3.0.3
`useServerPrepStmts`	Defines whether the connector uses server-side prepared statements using the PREPARE PREPARE, EXECUTE EXECUTE, and DROP PREPARE DROP PREPARE statements. By default, the connector uses client-side prepared statements. If the `rewriteBatchedStatements` parameter is set to `true`, this parameter will be set to `false`. In MariaDB Connector/J 3.2, the `/client prepare/` prefix allows selectively executing prepared statements on the client side even when the `useServerPrepStmts` option is enabled.	`boolean`	`false`	1.3.0

Code Example: INSERT, UPDATE, DELETE

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

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

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

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

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