Database connection in Qt

Now that we have learned how to set up a functional MySQL/MariaDB database system, let's move a step further and discover the database connection module in Qt!

Before we continue working on our login page from the previous chapter, let's start off with a new Qt project first so that it's easier to demonstrate the functionality solely related to database connection and so that we don't get distracted by the other stuff. This time, we'll go for the Terminal-style application called Qt Console Application, as we don't really need any GUI for this demonstration:

After you have created the new project, you should only see two files in the project, that is, [project_name].pro and main.cpp:

The first thing you need to do is to open up your project file (.pro), which in my case is DatabaseConnection.pro, and add the sql keyword at the back of the first line, like so:

QT += core sql 

As simple as that, we have successfully imported the sql module into our Qt project! Then, open up main.cpp and you should see a very simple script that contains only eight lines of code. This is basically all you need to create an empty console application:

#include <QCoreApplication> 
int main(int argc, char *argv[]) 
{ 
   QCoreApplication a(argc, argv); 
   return a.exec(); 
} 

In order for us to connect to our database, we must first import the relevant headers to main.cpp, like so:

#include <QCoreApplication> 
#include <QtSql> 
#include <QSqlDatabase> 
#include <QSqlQuery> 
#include <QDebug> 
int main(int argc, char *argv[]) 
{ 
   QCoreApplication a(argc, argv); 
   return a.exec(); 
} 

Without these header files, we won't be able to use the functions provided by Qt's sql module, which we have imported previously. Additionally, we also added the QDebug header so that we can easily print out any text on the console display (similar to the std::cout function provided by C++'s standard library).

Next, we'll add some code to the main.cpp file. Add the following highlighted code before return a.exec():

int main(int argc, char *argv[]) 
{ 
   QCoreApplication a(argc, argv); 
   QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL"); 
   db.setHostName("127.0.0.1"); 
   db.setPort(3306); 
   db.setDatabaseName("test"); 
   db.setUserName("testuser"); 
   db.setPassword("testpass"); 
   if (db.open()) 
   { 
         qDebug() << "Connected!"; 
   } 
   else 
   { 
         qDebug() << "Failed to connect."; 
         return 0; 
   } 
   return a.exec(); 
} 

Do note that the database name, username, and password could be different from what you have set in your database, so please make sure they are correct before compiling the project.

Once you are done with that, let's click the Run button and see what happens!:

If you see the following error, don't worry:

That is simply because you must install the MariaDB Connector (or MySQL Connector if you're running MySQL) to your computer and copy the DLL file over to your Qt installation path. Please make sure that the DLL file matches your server's database library. You can open up your phpMyAdmin home page and see which library it is currently using.

For some reason, even though I'm running XAMPP with MariaDB, the library name here shows libmysql instead of libmariadb, so I had to install MySQL Connector instead:

After you have downloaded the MariaDB Connector, install it on your computer:

The preceding screenshot shows the installation process for a Windows machine. If you're running Linux, you must download the right package for your Linux distribution. If you're running Debian, Ubuntu, or one of its variants, download the Debian and Ubuntu packages. If you're running Red Hat, Fedora, CentOS, or one of its variants, download the Red Hat, Fedora, and CentOS packages. The installation for these packages are automated, so you're good to go. However, if you're running neither of those, you'll have to download one of the gzipped tar files listed on the download page that fits your system requirement.

As for macOS, you need to use a package manager called Homebrew to install MariaDB server.

Once you have installed it, go to its installation directory and look for the DLL file (libmariadb.dll for MariaDB or libmysql.dll for MySQL). For Linux and macOS, it's libmariadb.so or libmysql.so instead of DLL.

Then, copy the file over to your application's build directory (the same folder as your application's executable file). After that, try and run your application again:

If you still getting Failed to connect but without the QMYSQL driver not loaded message, please check your XAMPP control panel and make sure that your database service is running; also make sure that the database name, username, and password that you put in the code is all the correct information.

Next, we can start playing around with SQL commands! Add the following code before return a.exec():

QString command = "SELECT name FROM department"; 
QSqlQuery query(db); 
if (query.exec(command)) 
{ 
   while(query.next()) 
   { 
         QString name = query.value("name").toString(); 
         qDebug() << name; 
   } 
} 

The preceding code sends the command text to the database and synchronously waits for the result to return from the server. After that, use a while loop to go through every single result and convert it to a string format. Then, display the result on the console window. If everything went right, you should see something like this:

Let's try out something more complex:

QString command = "SELECT my_user.username, department.name AS deptname FROM (SELECT * FROM user WHERE status = 0) AS my_user INNER JOIN department ON department.id = my_user.deptID"; 
QSqlQuery query(db); 
if (query.exec(command)) 
{ 
   while(query.next()) 
   { 
         QString username = query.value("username").toString(); 
         QString department = query.value("deptname").toString(); 
         qDebug() << username << department; 
   } 
} 

This time, we used INNER JOIN to combine two tables to select the username and department name. To avoid confusion regarding the variable called name, rename it to deptname using the AS statement. After that, display both the username and department name on the console window:

We're done... for now. Let's move on to the next section, where we will learn how to make our login page functional!