In this recipe, we will learn how to display multiple sets of data obtained from our SQL database on a model view in our program.
Follow these steps to display information from a database on a model view widget:
- We will be using the database table called
employee, which we used in the previous example. This time, we need a lot more data in theemployeetable. Open up your web browser and log in to your phpMyAdmin control panel. Add data for a few more employees so that we can display it later in our program:
- After that, open up Qt Creator, create a new Qt Widgets Application project, and then add the SQL module to your project.
- Next, open up
mainwindow.uiand add a table widget (not table view) from Item Widget (Item-Based) under the Widget box pane. Select the main window on the canvas and click on either the Layout Vertically or Layout Horizontally button to make the table widget stick to the size of the main window, even when it's resized:
- After that, double-click on the table widget and a window will then appear. Under the Columns tab, add five items by clicking on the + button at the top-left corner. Name the items
ID,Name,Age,Gender, andMarried. Click OK when you're done:
- Then, right-click on the table widget and select Go to slot… in the pop-up menu. Scroll all the way down, select the itemChanged(QTableWidgetItem*) option in the pop-up window, and press OK. A slot function will be created in both your source files.
- Open up
mainwindow.hand add these private variables to ourMainWindowclass:private: Ui::MainWindow *ui; bool hasInit; QSqlDatabase db;
- We also add the following class headers to
mainwindow.h:#include <QtSql> #include <QSqlDatabase> #include <QSqlQuery> #include <QMessageBox> #include <QDebug> #include <QTableWidgetItem>
- Once you're done with that, open up
mainwindow.cppand we're going to write tons of code there. First, we need to declare what will happen when the program is started. Add the following code to the constructor of theMainWindowclass:MainWindow::MainWindow(QWidget *parent) : QMainWindow(parent), ui(new Ui::MainWindow) { hasInit = false; ui->setupUi(this); db = QSqlDatabase::addDatabase("QMYSQL"); db.setHostName("127.0.0.1"); db.setUserName("yourusername"); db.setPassword("yourpassword"); db.setDatabaseName("databasename"); ui->tableWidget->setColumnHidden(0, true); if (db.open()) { QSqlQuery query; if (query.exec("SELECT id, name, age, gender, married FROM employee")) { while (query.next()) { qDebug() << query.value(0) << query.value(1) << query.value(2) << query.value(3) << query.value(4); QString id = query.value(0).toString(); QString name = query.value(1).toString(); QString age = query.value(2).toString(); int gender = query.value(3).toInt(); bool married = query.value(4).toBool(); ui->tableWidget->setRowCount(ui->tableWidget->rowCount() + 1); QTableWidgetItem* idItem = new QTableWidgetItem(id); QTableWidgetItem* nameItem = new QTableWidgetItem(name); QTableWidgetItem* ageItem = new QTableWidgetItem(age); QTableWidgetItem* genderItem = new QTableWidgetItem(); if (gender == 0) genderItem->setData(0, "Male"); else genderItem->setData(0, "Female"); QTableWidgetItem* marriedItem = new QTableWidgetItem(); if (married) marriedItem->setData(0, "Yes"); else marriedItem->setData(0, "No"); ui->tableWidget->setItem(ui->tableWidget->rowCount() - 1, 0, idItem); ui->tableWidget->setItem(ui->tableWidget->rowCount() - 1, 1, nameItem); ui->tableWidget->setItem(ui->tableWidget->rowCount() - 1, 2, ageItem); ui->tableWidget->setItem(ui->tableWidget->rowCount() - 1, 3, genderItem); ui->tableWidget->setItem(ui->tableWidget->rowCount() - 1, 4, marriedItem); } hasInit = true; } else { qDebug() << query.lastError().text(); } } else { qDebug() << "Failed to connect to database."; } } - After that, declare what will happen when an item of the table widget has been edited. Add the following code to the slot function called
on_tableWidget_itemChanged():void MainWindow::on_tableWidget_itemChanged(QTableWidgetItem *item) { if (hasInit) { QString id = ui->tableWidget->item(item->row(), 0)->data(0).toString(); QString name = ui->tableWidget->item(item->row(), 1)->data(0).toString(); QString age = QString::number(ui->tableWidget->item(item->row(), 2)->data(0).toInt()); ui->tableWidget->item(item->row(), 2)->setData(0, age); QString gender; if (ui->tableWidget->item(item->row(), 3)->data(0).toString() == "Male") { gender = "0"; } else { ui->tableWidget->item(item->row(), 3)->setData(0, "Female"); gender = "1"; } QString married; if (ui->tableWidget->item(item->row(), 4)->data(0).toString() == "No") { married = "0"; } else { ui->tableWidget->item(item->row(), 4)->setData(0, "Yes"); married = "1"; } qDebug() << id << name << age << gender << married; QSqlQuery query; if (query.exec("UPDATE employee SET name = '" + name + "', age = '" + age + "', gender = '" + gender + "', married = '" + married + "' WHERE id = " + id)) { QMessageBox::information(this, "Update Success", "Data updated to database."); } else { qDebug() << query.lastError().text(); } } } - Lastly, close the database at the class destructor:
MainWindow::~MainWindow() { db.close(); delete ui; } - Compile and run the example now and you should be getting something like this:
A table widget is similar to the one you see in spreadsheet applications such as Microsoft Excel and Open Office Calc. In contrast with other types of model viewers such as list view or tree view, table view (or table widget) is a two-dimensional model viewer, which displays data in the form of rows and columns.
The main difference between a table view and table widget in Qt is that a table widget is built on top of a table view class, which means a table widget is easier to use and more suitable for beginners. However, a table widget is less flexible and tends to be less scalable than a table view, which is not the best choice if you want to customize your table.
After retrieving data from MySQL, we created a QTableWidgetItem item for each of the data items and set which column and row should be added to the table widget. Before adding an item to the table widget, we must increase the row count of the table by calling QTableWidget::setRowCount(). We can also get the current row count of the table widget by simply calling QTableWidget::rowCount().
The first column from the left is hidden from view because we only use it to save the ID of the data so that we can use it to update the database when one of the data items in its row has changed.
The slot function on_tableWidget_itemChanged() will be called when the data in one of the cells has changed. It will not only get called when you edit the data in the cell, but also when the data is first added to the table after being retrieved from the database. To ensure that this function is only triggered when we edit the data, we used a Boolean variable called hasInit to check whether we have done the initialization process (adding the first batch of data to the table) or not. If hasInit is false, ignore the function call.
To prevent users from entering a totally irrelevant type of data, such as inserting alphabets into a supposedly numerical-only data cell, we checked manually whether the data is anything close to what we'd expected when it's being edited. Revert it to a default value if it doesn't come close to anything considered as valid. This is of course a simple hack, which does the job but is not the most professional method. Alternatively, you can try to create a new class that inherits the QItemDelegate class and define how your model view should behave. Then, call QTableWidget::setItemDelegate() to apply the class to your table widget.