MariaDB Connector/J Examples

Example programs provided here illustrate how to use MariaDB Connector/J to connect to MariaDB Enterprise and retrieve and manipulate data.

Short Example

The following example program selects data from the test.contacts table created in the example setup. Complete information on Using Connector/J is available.

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 = stmt.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>

Long Example

The following example program connects using TLS (Transport Layer Security) and demonstrates CRUD (CREATE, READ, UPDATE, DELETE) operations, within a transaction as a batch operation, with the example setup.

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

public class App {

   public static void main(String[] argv) {

       // Connection Configuration
       Properties connConfig = new Properties();
       connConfig.setProperty("user", "db_user");
       connConfig.setProperty("password", "db_user_password");
       connConfig.setProperty("useSsl", "true");
       connConfig.setProperty("serverSslCert", "/path/to/ca-bundle.pem");

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

           // Disable Auto-Commit
           conn.setAutoCommit(false);

           // Use Statement to Create test.contacts table
           try (Statement stmt = conn.createStatement()) {
               stmt.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;");

               // Prepare INSERT Statement to Add Contacts
               try (PreparedStatement prep = conn.prepareStatement(
                           "INSERT INTO test.contacts (first_name, last_name, email) VALUES (?, ?, ?)",
                           Statement.RETURN_GENERATED_KEYS)) {

                   // Add Contact
                   prep.setString(1, "John");
                   prep.setString(2, "Smith");
                   prep.setString(3, "johnsmith@example.com");
                   prep.addBatch();

                   // Add Contact
                   prep.setString(1, "Jon");
                   prep.setString(2, "Smith");
                   prep.setString(3, "jon.smith@example.com");
                   prep.addBatch();

                   // Add Contact
                   prep.setString(1, "Johnny");
                   prep.setString(2, "Smith");
                   prep.setString(3, "johnny.smith@example.com");
                   prep.addBatch();

                   // Execute Prepared Statements in Batch
                   System.out.println("Batch Counts");
                   int[] updateCounts = prep.executeBatch();
                   for (int count : updateCounts) {

                       // Print Counts
                       System.out.println(count);
                   }
             }

             // Prepare UPDATE Statement
             try (PreparedStatement prep = conn.prepareStatement(
                         "UPDATE test.contacts " +
                         "SET email = ? WHERE id = ?")) {

                 // Change Email Address
                 prep.setString(1, "john.smith@example.com");

                 // Change ID
                 prep.setInt(2, 1);
                 prep.execute();
             }

             // Prepare DELETE Statement
             try (PreparedStatement prep = conn.prepareStatement(
                         "DELETE FROM test.contacts " +
                         "WHERE id = ?")) {

                 // ID of Row to Remove
                 prep.setInt(1, 3);
                 prep.execute();
             }

             // Commit Changes
             conn.commit();

             // Execute a SELECT Statement
             ResultSet contact_list = stmt.executeQuery(
                     "SELECT first_name, last_name, email " +
                     "FROM test.contacts");

             // Iterate over the Result-set

             System.out.println("Contacts:");
             while (contact_list.next()) {

                 // Print Row
                 System.out.println(
                         String.format(
                             "- %s %s <%s>",
                             contact_list.getString("first_name"),
                             contact_list.getString("last_name"),
                             contact_list.getString("email")));
             }
        }

        // Catch SQL Exceptions for Queries
        catch (SQLException exc) {
             exc.printStackTrace();
             conn.rollback();
        }
      }

      // Catch SQL Exceptions from Connection
      catch (SQLException e) {
         e.printStackTrace();
      }
   }
}

Example output:

Batch Counts:
1
1
1
Contacts:
- John Smith <john.smith@example.com>
- Jon Smith <jon.smith@example.com>