Message Queue Telemetry Transport (MQTT) is awesome when it comes to Machine-to-Machine (M2M) Communication. Due to its applied Publish-Subscribe pattern it offers great scalability even with thousands of connected devices.
The picture above shows a classic M2M landscape with a few publishers and a few subscribers.
Talking from the perspective of a provider of M2M services (which you are when you are hosting your own broker e.g. for homeautomation or your applications), you typically have additional needs to generate added value for yourself or your customer. So let’s say you want to store all MQTT publishes which are broadcasted to the broker for later analysis in a SQL database.
The concrete use case
So we want to store every message in a SQL database in our concrete use case. Let’s say we want to store them into a MySQL/MariaDB. The following simple database scheme will be used:
Implementation with a wildcard subscriber
The easiest way to achieve the storage is to add an additional client which subscribes to the Wildcard Topic (which happens to be # in MQTT). This ensures that the client receives all messages which are distributed by the broker. The client can now persist the message to the MySQL database every time a message arrives.
This would look like this:
We chose to implement the client library with Eclipse Paho. For brevity only the relevant callback part on message arrival is shown here. The full source code can be found here.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
......
privatestaticfinalStringSQL_INSERT="INSERT INTO `Messages` (`message`,`topic`,`quality_of_service`) VALUES (?,?,?)";
......
@Override
publicvoidmessageArrived(MqttTopic topic,MqttMessage message)throwsException{
//Let's assume we have a prepared statement with the SQL.
try{
sta