Accessing Databases with SQL Driver and Qt

Structured Query Language (SQL) is a special programming language used to manage data held in a relational database management system. A SQL server is a database system designed to use one of the many types of SQL programming languages to manage its data.

In this chapter, we will cover the following recipes:

• Setting up a database
• Connecting to a database
• Writing basic SQL queries
• Creating a login screen with Qt
• Displaying information from a database in model view
• Advanced SQL queries

Technical requirements

The technical requirements for this chapter include Qt 6.6.1 MinGW 64-bit and Qt Creator 12.0.2. All the code used in this chapter can be downloaded from the following GitHub repository: https://github.com/PacktPublishing/QT6-C-GUI-Programming-Cookbook---Third-Edition-/tree/main/Chapter12.

Setting up a database

Qt supports several different types of SQL drivers in the form of plugins/add-ons, such as SQLite, ODBC, PostgreSQL, MySQL, and so on. However, it’s very easy to integrate these drivers into your Qt project. We will learn how to do this in the following example.

How to do it…

In this example, we will learn how to use Qt with SQLite. Let’s set up our SQLite editor before we dive into Qt:

1. Download SQLiteStudio from https://sqlitestudio.pl and install it to administrate your SQLite databases:

Figure 12.1 – Install SQLiteStudio onto your computer

2. Open SQLiteStudio and you should see something like this:

Figure 12.2 – SQLiteStudio is a handy program for managing SQLite databases

3. We need to create a new database before we start; go to Database | Add a database. Select the SQLite 3 option for your database type, followed by selecting your file...

Connecting to a database

In this recipe, we will learn how to connect our Qt 6 application to the SQL server.

How to do it…

Connecting to SQL Server in Qt is really simple:

1. Open Qt Creator and create a new Qt Widgets Application project.
2. Open your project file (.pro), add the sql module to your project, and run qmake like this:

   QT += core gui sql

3. Open mainwindow.ui and drag seven label widgets, a combo box, and a checkbox to the canvas. Set the text properties of four of the labels to Name:, Age:, Gender:, and Married:. Then, set the objectName properties of the rest to name, age, gender, and married. There is no need to set the object name for the previous four labels because they’re for display purposes only:

Figure 12.10 – Setting the text properties

4. Open mainwindow.h and add the following headers below the QMainWindow header:

   #include <QMainWindow>
   #include <QtSql>
   #include <QSqlDatabase...

Writing basic SQL queries

In the previous example, we wrote our very first SQL query, which involves the SELECT statement. This time, we will learn how to use some other SQL statements, such as INSERT, UPDATE, and DELETE.

How to do it…

Let’s create a simple program that demonstrates basic SQL query commands by following these steps:

1. We can use our previous project files, but there are a couple of things we need to change. Open mainwindow.ui and replace the labels for Name and Age with line-edit widgets. Then, add three buttons to the canvas and call them UPDATE, INSERT, and DELETE:

Figure 12.12 – Modify the UI to this

2. Open mainwindow.h and add the following variables under the private inheritance:

   private:
        Ui::MainWindow *ui;
        QSqlDatabase db;
        bool connected;
        int currentID;

3. Open mainwindow...

Creating a login screen with Qt

In this recipe, we will learn how put our knowledge to use and create a functional login screen using Qt and SQLite.

How to do it…

Create your first functional login screen by following these steps:

1. Open a web browser and go to SQLiteStudio. We will create a new data table called user, which looks like this:

Figure 12.14 – Create a new user table

2. Let’s insert our first item of data into the newly created table and set user_employeeID to the ID of an existing employee. In this way, the user account we create will be linked to the data of one of the employees:

Figure 12.15 – The user_employeeID column is linked to the employee’s emp_id column

3. Open Qt Creator and create a new Qt Widgets Application project. We will start off with mainwindow.ui. Place a stacked widget on the canvas and make sure it contains two pages. Then, set up...

Displaying information from a database in model view

Follow these steps to display information from a database on a model view widget:

How to do it…

In this recipe, we will learn how to display multiple sets of data obtained from our SQL database in a model view in our program:

1. We will be using the database table called employee, which we used in the previous example in Creating a login screen with Qt. This time, we need a lot more data in the employee table. Open up your SQLiteStudio control panel. Add data for a few more employees so that we can display it later in our program:

Figure 12.20 – Add more dummy data to the employee table

2. Open Qt Creator, create a new Qt Widgets Application project, and then add the SQL module to your project.
3. Open mainwindow.ui and add a table widget (not a table view) from Item Widget (item-based) under the Widget box pane. Select the main window on the canvas and click on either...

Advanced SQL queries

By following this recipe, you will learn how to use advanced SQL statements such as INNER JOIN, COUNT, LIKE, and DISTINCT.

How to do it…

You can do a lot more than just perform simple queries on the SQL database. Let’s follow these steps to learn how:

1. We need to add a few tables to our database before we can dive into the programming part. Open your SQLiteStudio. We need several tables for this example to work:

Figure 12.24 – Additional tables that we need to create for this example

2. I will show you the structure of each of the tables required for this project and the dummy data inserted into the tables for testing. The first table is called branch, which is used to store the IDs and names of different branches of the dummy company:

Figure 12.25 – The branch table

3. Secondly, we have the department table, which stores the IDs and names of different...