I would like to store published MQTT messages to database (postgresql). I did a search on how to do this and found an article https://www.hivemq.com/blog/mqtt-sql-database/ that rather than having a client to subscribe to all topic (#) and store to database, I should let the MQTT server handle it. So the solution is to use HiveMQ with plugins or extensions.
I have been trying to create an extensions to store MQTT messages to database but I am having a problem with java.lang.ClassNotFoundException: org.postgresql.Driver. In my extension, I use postgresql-42.2.18.jar. But I have no idea how to add the .jar to newly created extension. I have tried to put the .jar file in the same folder as the extension, but still I have the same error: class not found. I already use CLASSPATH variable. My simple java program is able to connect to the database without a problem using the .jar file from the CLASSPATH. I also tried to add “-cp /home/user/jar/postgresql-42.2.18.jar” to the JAVA_OPTS in run.sh, but still have no luck.
Please suggest what should I do in order to resolve this problem. Or are there any resources I should take a look as an example on how to store messages to database? Thank you very much.
Hey Worawitp,
Nice to see your interest on this topic. Have you tried to include the needed jar inside your extension via build tools like gradle or maven? This way the class files of the postgress driver will be included in the extension jar and accessible from it.
How are you building the extension? Are you using our hello-world-extension (GitHub - hivemq/hivemq-hello-world-extension: HiveMQ extension for demonstrating the HiveMQ extension system) ?
Best regards,
Daniel
2 Likes
Dear Daniel,
Thank you for the suggestion. Yes, I am using your hello-world-extension. I clone the hello-world-extension from git-hub and work from there. I import the cloned gradle project into Eclipse IDE and build the extension using hivemqExtensionZip. Actually, I have never used gradle or maven before. But I will try to figure out the way to add the jar file into the project using gradle tool. I have tried to add the jar file into project “Java Build Path” but the jar file is not included in the extension. I guess this might not be the correct way to do it. Again, thank you for the suggestion and I will report the result back later.
Best regards,
Worawit
Dear Daniel,
OK, I am back. It works!!
I add “runtime(files(“libs/postgresql-42.2.18.jar”))” under “dependencies” in “build.gradle.kts” file. Then run the hivemqExtensionZip gradle task. The .zip extension have a larger size which means that the new extension jar file include postgresql library. Now the extension is able to connect to the database. Thank you very much. Really appreciated.
Best regards,
Worawit
1 Like
Hey Worawit,
Yeah - nice to hear that. That was exactly what i meant 
Best regards,
Daniel from HiveMQ
1 Like