JPA Calling a MySQL stored function

I have a stored function called getNextCode() that requires a string parameter and returns a string. I wish to call this from my JPA enabled application. T he following code fragment shows the use of the crateNativeQuery() method to achieve this.

    private String _getNextRollPackageCode()
    {
        String query = String.format(
                "SELECT getNextCode( '%s')",
                this.ROLL_PACKAGES_MODEL_NAME
        );

        return this.ROLL_PACKAGE_CODE_PREFIX + "-" +  (String) _entityManager.createNativeQuery(query)
                .getSingleResult();
    }

Ubuntu: Setting up an MQTT Server and connecting via SSL

Foreward

This has been a very frustrating story as I was asked to setup an MQTT server that would support -- every IT Manager's pride and joy -- SSL. The setup was quite smooth, thanks to the good people of Digital Ocean. Their post How to Install and Secure the Mosquitto MQTT Messaging Broker on Ubuntu 16.04, explains everything crystal clear.

I also wanted to setup the websockets protocol, so a Javascript client would continue to work. Last but not least be able to programmatically connect using programs written in Java, C# and Python 3. This blog post shows the exact setup we used to make things work, as well as minimal code samples about how to connect.

For the shake of this example, my server DNS name is supposed to be mysite.net, the MQTT server user is mqtt-user and the password we set, is my_Super_Secure_Password.

Server setup and testing

Starting with the setup, I followed Digital Oceans instructions and making a fiew slight changed ended up with this:

# MQTT Config File with websockets and SSL
# Created March - 26th -2018
#

# ----------------------------------------------------------------------------
# Log Setup
# ----------------------------------------------------------------------------
log_type error
log_type warning
log_type notice
log_type information
log_type websockets
websockets_log_level 255
log_timestamp true
# log dest is set in /etc/mosquitto/mosquitto.conf
# web sockets log level can be removed if you verify that websockets is working

# ----------------------------------------------------------------------------
# User Security Setup
# ----------------------------------------------------------------------------
allow_anonymous false
password_file /etc/mosquitto/passwd

# Do not allow non-secure connection from anywhere but the localhost
bind_address    localhost
port            1883

# ----------------------------------------------------------------------------
# This is the SSL Setup
# ----------------------------------------------------------------------------
listener        8883 
protocol        mqtt      

certfile        /etc/letsencrypt/live/mysite.net/cert.pem
cafile          /etc/letsencrypt/live/mysite.net/chain.pem
keyfile         /etc/letsencrypt/live/mysite.net/privkey.pem
require_certificate false

# ----------------------------------------------------------------------------
# Here is our SSL enabled web sockets config
# ----------------------------------------------------------------------------
listener        9993 
protocol        websockets
http_dir        /var/www/mysite.net/mqtt

certfile        /etc/letsencrypt/live/mysite.net/cert.pem
cafile          /etc/letsencrypt/live/mysite.net/chain.pem
keyfile         /etc/letsencrypt/live/mysite.net/privkey.pem
require_certificate false

This is the contents of the /etc/mosquitto/conf.d/default.conf file. To test I switched to my local console and issued the following command:

 mosquitto_sub -h mysite.net -p 8883 -t test -u mqtt_user -P my_Super_Secure_Password --capath /etc/ssl/certs

then opened a second terminal, and each time I would type

mosquitto_pub -h mysite.net -p 8883 -t test -m "Hello Sailor"  -u mqtt_user -P my_Super_Secure_Password --capath /etc/ssl/certs

I would see a Hello Sailor line on the first console screen. That more or less did it

Connecting with Code

This is the part where we completely lost it. All examples in the web show how to perform an SSL connection using your own private key pair but in our case we wanted something much simpler. Our requirement was that the connection be SSL encrypted but we would use the mqtt generated user and password pair. In the Java world, we really got mixed up with keystores, trust manager factories and the like while all it took to post a simple message was :

package com.kfator.mqttclient;

import static java.nio.charset.StandardCharsets.UTF_8;
import java.security.SecureRandom;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.net.ssl.SSLContext;
import org.eclipse.paho.client.mqttv3.MqttClient;
import org.eclipse.paho.client.mqttv3.MqttConnectOptions;
import org.eclipse.paho.client.mqttv3.persist.MemoryPersistence;

public class Main {
    public static final String MQTT_URL = "ssl://mysite.net:8883";
    public static final String MQTT_USER_NAME = "mqtt_user";
    public static final String MQTT_USER_PASSWORD = "my_Super_Secure_Password";

    public static void main(String args[])
    {
        try {
            MqttClient client = new MqttClient(
                    MQTT_URL,
                    MqttClient.generateClientId(),  // ClientId
                    new MemoryPersistence());       // Persistence

            SSLContext sslContext = SSLContext.getInstance("SSL");
            sslContext.init(null, null, new SecureRandom());

            MqttConnectOptions options = new MqttConnectOptions();
            options.setSocketFactory(sslContext.getSocketFactory());
            options.setUserName(MQTT_USER_NAME);
            options.setPassword(MQTT_USER_PASSWORD.toCharArray());

            client.connect(options);
            client.publish(
                "test", // topic
                "Hello from Java".getBytes(UTF_8), // payload
                2, // QoS
                false); // retained?
            client.disconnect();

            System.out.println("Connection established!");
        } catch (Exception ex) {
            Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}

The code runs from a Netbenas 8.2 Maven project. To include the paho-mqtt libraries we (sort of) followed the instructions at MQTT Client Library Encyclopedia – Eclipse Paho Java page. the actual steps were to create an empty Mevn Projet, then right click on the dependencies Node, select the add dependency menu item and fill in the form details as show at the begining of the Paho-Java page.

The C# code using the Mqtt-Paho library for .NET goes like this:

    _clientMonitor = new MqttClient("mysite.net", 8883, true, new X509Certificate(), null, MqttSslProtocols.TLSv1_0);

... and finally, python 3 only requires the use of client.tls_set() before calling connect(). Here is the related fragment from out demo program.

client = mqtt.Client()
client.on_connect = on_connect
client.on_message = on_message
client.username_pw_set(MQTT_USER_NAME, MQTT_PASSWORD)

client.tls_set()
client.connect(HOST, PORT)

And the best part was, that all these worked ...