MariaDB Connector/J Examples

Overview

Example programs provided here illustrate how to use MariaDB Connector/J to connect to MariaDB database products 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://HOST:PORT", 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>

Connector/J 3.x

In MariaDB Connector/J 3.x, 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 = 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>

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

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) {
      // Connection Configuration
      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/ca-bundle.pem");

      // Create Connection to the database
      try (Connection conn = DriverManager.getConnection("jdbc:mariadb://HOST:PORT", 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();
      }
   }
}

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

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

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) {
      // 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 the database
      try (Connection conn = DriverManager.getConnection("jdbc:mariadb://HOST:PORT", 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();
      }
   }
}

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/skysql_chain.pem");

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