MariaDB Java Application

Create a MariaDB Java Test Script on Rocky Linux 9.

In this blog, we are going to prepare a basic Linux server using Java code to test connectivity to a MariaDB Database Server. The following steps are completed on a Rocky Linux 9 server but should work on most other versions of Linux, for example, CentOS 7/8/9.

This Java code will connect to two MaxScale servers to demonstrate the sequential mode of the MariaDB/J connector.

Before proceeding with the next steps, make sure you have an application server running MariaDB Java Connector, and have built a secondary MaxScale server.

To allow the application to connect to the MaxScale servers, a user must be created on the primary database server. We can do this via MaxScale, a GUI tool, or directly on the server. To create the user, log in using an existing user that has the correct permission to create an application test user.

The subnet ‘10.106.0.%’ will need to be modified. Avoid using ‘%’ as a default and use your application server IP address instead:

SQL

CREATE USER IF NOT EXISTS 'javatest'@'10.106.0.%' IDENTIFIED BY 'aBcd123_';
GRANT SELECT, INSERT, UPDATE, CREATE, DROP ON *.* TO 'javatest'@'10.106.0.%';

Once we have the MariaDB database connector and Java installed, we will write Java code that will create the required database and a test table. Use whichever editor you prefer, for example, vi:

Bash

vi ~/mariadbcreate.java

Insert this code.

Note: the user and password must match the user you created in the earlier steps, and both IP addresses (listed on line 20) need to be that of your MaxScale servers.

Java

import java.sql.*;
import java.util.Properties;
import java.util.UUID;
import java.util.concurrent.TimeUnit;

// above are the libraries that have to import for this task

public class mariadbcreate {

    //this is the main method/function of the class that is the entry point of the java class

    public static void main(String[] args) {

        // Define the primary server properties
        // Make sure you set your connection string accordingly.

        Properties primaryServerProps = new Properties();
        primaryServerProps.put("user", "javatest");
        primaryServerProps.put("password", "aBcd123_");
        String primaryJdbcUrl = "jdbc:mariadb:sequential://10.106.0.5:3306,10.106.0.6:3306/";

        //connection variable to connect with the database with provided properties
        Connection conn = null;
        try {
            // If the connection to the primary server fails, try connecting to the backup server
            conn = DriverManager.getConnection(primaryJdbcUrl, primaryServerProps);
            System.out.println("Connected to MaxScale Server, to create Schema!");
            // Create database, table, and insert data
            createDatabase(conn);
        } catch (SQLException createerror) {
            // Catch-all for other exceptions
            createerror.printStackTrace();
        }
        boolean done = false;
        while (!done) {
            try {
                // Try connecting to the primary server
                conn = DriverManager.getConnection(primaryJdbcUrl, primaryServerProps);
                System.out.println("Connected to MaxScale Server!");
                // Create database, table, and insert data
                InsertData(conn);
                done = true;
            } catch (SQLException e) {
                //visible when our primary server is unable to connect/reach.
                System.err.println("Failed to connect to MaxScale Server. Trying Alternative Server...");
                try {
                    // If the connection to the primary server fails, try connecting to the backup server
                    conn = DriverManager.getConnection(primaryJdbcUrl, primaryServerProps);
                    System.out.println("Connected to Alternative Server!");
                    // Create database, table, and insert data
                    InsertData(conn);
                    done = true;
                } catch (SQLException ex) {
                    // Catch-all for other exceptions
                    ex.printStackTrace();
                }
            } finally {
                try {
                    // Done with the connection.
                    if (conn != null) {
                        conn.close();
                    }
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
        }
    }

    private static void createDatabase(Connection connection) throws SQLException {
        Statement statement = connection.createStatement();
        // Drop database if it exists
        statement.executeUpdate("CREATE DATABASE IF NOT EXISTS javademo");
        statement.executeUpdate("USE javademo");
        // Create the test table with UUID column (without primary key)
        statement.executeUpdate("CREATE TABLE IF NOT EXISTS javademotable (uuid CHAR(36))");
        System.out.println("Database and table inserted successfully!");
        // Close resources
        statement.close();
    }

    private static void InsertData(Connection connection) throws SQLException {
        Statement statement = connection.createStatement();
        statement.executeUpdate("USE javademo");
        // Insert data in a loop
        for (int i = 1; i <= 100; i++) {
            UUID uuid = UUID.randomUUID();
            long startTime = System.currentTimeMillis();

            statement.executeUpdate("INSERT INTO javademotable (uuid) VALUES ('" + uuid.toString() + "')");
            long endTime = System.currentTimeMillis();

            System.out.println("Inserting Record took " + (endTime - startTime) + " milliseconds");
            try {
                Thread.sleep(1000);
            } catch (InterruptedException e) {
                Thread.currentThread().interrupt();
            }
        }
        System.out.println("Database, table, and data inserted successfully!");
        // Close resources
        statement.close();
    }
}

Before we run the Java file, we need to compile it:

Bash

javac ~/mariadbcreate.java

Make sure you are in the correct directory:

Bash

cd ~

Then run it:

Bash

java mariadbcreate

The Java code will loop for the number of seconds defined by default 100. When completed, the command line will show the number of records inserted. For each inserted line of data, the Java code will show the transaction time.

Now that we have the Java code running for the test application, you can learn how to obtain Continuous Availability within the database layer to avoid any database downtime.

MariaDB Java Application

Create a MariaDB Java Test Script on Rocky Linux 9.

Leave a Reply Cancel reply