You can do a lot more than just performing simple queries on the SQL database. Let's follow these steps to learn how:
- We need to add a few tables to our database before we can dive into the programming part. Open your web browser and access phpMyAdmin. We need several tables for this example to work:
- 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:
- Secondly, we have the department table, which stores the IDs and names of different departments of the dummy company, which is also linked to the branch data by the branch IDs:
- We also have an employee table, which stores information on all the employees in the dummy company. This table is similar to the one we used in the previous examples, except it has two extra columns, birthday and departmentID:
- We also have a table called log, which contains dummy records of the login time for each employee. The loginTime column can be a timestamp or date-time variable type:
- We have the user table that we also used in the previous examples:
- Open Qt Creator. This time, instead of choosing Qt Widgets Application, we create a Qt Console Application:
- Open your project file (.pro) and add the sql module to your project:
QT += core sql
QT -= gui
- Open main.cpp and add the following header files to the top of the source file:
#include <QSqlDatabase>
#include <QSqlQuery>
#include <QSqlError>
#include <QDate>
#include <QDebug>
- Add the following function to display employees who are more than 30 years old:
void filterAge() {
qDebug() << "== Employees above 30 year old =============";
QSqlQuery query;
if (query.exec("SELECT name, age FROM employee WHERE age > 30")) {
while (query.next()) {
qDebug() << query.value(0).toString() << query.value(1).toString();
}
} else {
qDebug() << query.lastError().text();
}
qDebug() << "
";
}
- Add the following function to display the department and branch information of each employee:
void getDepartmentAndBranch() {
qDebug() << "== Get employees' department and branch =============";
QSqlQuery query;
if (query.exec("SELECT myEmployee.name, department.name, branch.name FROM (SELECT name, departmentID FROM employee) AS myEmployee INNER JOIN department ON department.id = myEmployee.departmentID INNER JOIN branch ON branch.id = department.branchID")) {
while (query.next()) {
qDebug() << query.value(0).toString() << query.value(1).toString() << query.value(2).toString();
}
} else {
qDebug() << query.lastError().text();
}
qDebug() << "
";
}
- Add the following function, which displays employees who are working in the New York branch and are under 30 years old:
void filterBranchAndAge() {
qDebug() << "== Employees from New York and age below 30 =============";
QSqlQuery query;
if (query.exec("SELECT myEmployee.name, myEmployee.age, department.name, branch.name FROM (SELECT name, age, departmentID FROM employee) AS myEmployee INNER JOIN department ON department.id = myEmployee.departmentID INNER JOIN branch ON branch.id = department.branchID WHERE branch.name = 'New York' AND age < 30")) {
while (query.next()) {
qDebug() << query.value(0).toString() << query.value(1).toString() << query.value(2).toString() << query.value(3).toString();
}
} else {
qDebug() << query.lastError().text();
}
qDebug() << "
";
}
- Add the following function, which counts the total number of female employees in the dummy company:
void countFemale() {
qDebug() << "== Count female employees =============";
QSqlQuery query;
if (query.exec("SELECT COUNT(gender) FROM employee WHERE gender = 1")) {
while (query.next()) {
qDebug() << query.value(0).toString();
}
} else {
qDebug() << query.lastError().text();
}
qDebug() << "
";
}
- Add the following function, which filters the employee list and only displays those whose names start with Ja:
void filterName() {
qDebug() << "== Employees name start with 'Ja' =============";
QSqlQuery query;
if (query.exec("SELECT name FROM employee WHERE name LIKE '%Ja%'")) {
while (query.next()) {
qDebug() << query.value(0).toString();
}
} else {
qDebug() << query.lastError().text();
}
qDebug() << "
";
}
- Add the following function, which displays employees who have birthdays in August:
void filterBirthday() {
qDebug() << "== Employees birthday in August =============";
QSqlQuery query;
if (query.exec("SELECT name, birthday FROM employee WHERE MONTH(birthday) = 8")) {
while (query.next()) {
qDebug() << query.value(0).toString() << query.value(1).toDate().toString("d-MMMM-yyyy");
}
} else {
qDebug() << query.lastError().text();
}
qDebug() << "
";
}
- Add the following function, which checks who logged into the dummy system on 27 April 2019 and displays their names on the terminal:
void checkLog() {
qDebug() << "== Employees who logged in on 27 April 2019 =============";
QSqlQuery query;
if (query.exec("SELECT DISTINCT myEmployee.name, FROM (SELECT id, name FROM employee) AS myEmployee INNER JOIN user ON user.employeeID = myEmployee.id INNER JOIN log ON log.userID = user.id WHERE DATE(log.loginTime) = '2019-04-27'")) {
while (query.next()) {
qDebug() << query.value(0).toString();
}
} else {
qDebug() << query.lastError().text();
}
qDebug() << "
";
}
- In the main() function, connect the program to the MySQL database and call all the functions that we defined in the previous steps. Close the database connection and we're done:
int main(int argc, char *argv[]) {
QCoreApplication a(argc, argv);
QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
db.setHostName("127.0.0.1");
db.setUserName("reonyx");
db.setPassword("reonyx");
db.setDatabaseName("testing");
if (db.open()) {
filterAge();
getDepartmentAndBranch();
filterBranchAndAge();
countFemale();
filterName();
filterBirthday();
checkLog();
db.close();
} else {
qDebug() << "Failed to connect to database.";
}
return a.exec();
}
- Compile and run the project and you should see a terminal window that displays the filtered results:
