Article directory
- 1 QSqlQueryModel function overview
- 2 Use QSqlQueryModel to realize data query
-
- 2.1 Instance function
- 2.2 Visual UI design
- 2.3 Main window class definition (remove automatically generated slot function)
- 2.4 Open the database
- 2.5 Record movement
1 QSqlQueryModel function overview
As you can see from the figure below, QSqlQueryModel is the parent class of QSqlTableModel. QSqlQueryModel encapsulates the function of executing SELECT statements to query data from the database, but QSqlQueryModel can only be used as a read-only data source and cannot edit data.
The main interface functions of the QSqlQueryModel class are shown in Table 11-10 (the const keyword and default parameters in the function are omitted).
Use QSqlQueryModel as the data model to query data from the database, just use the setQuery() function to set a SELECT query statement. QSqlQueryModel can be used as the data source of view components such as QTableView, or use QDataWidgetMapper to create a mapping between fields and interface components, but the queried data cannot be edited.
2 Use QSqlQueryModel to realize data query
2.1 Instance function
Use QSqlQueryModel to query data from one data table or multiple data tables, just design the SELECT statement. Example samp11_2 uses QSqQueryModel to query records from the employee table, and displays them on the interface. The running window is shown in the figure below.
Several toolbar buttons on the window toolbar only have the functions of opening the database and record movement, and the record movement is realized by calling the record movement function of the QdataWidget-Mapper class. There is no data editing and saving function on the window, because the data queried by QSqlQueryModel is read-only.
2.2 Visual UI design
samp11_2 adopts a visual UI design, and the specific framework is shown in the figure below
2.3 Main window class definition (remove automatically generated slot function)
#ifndef MAINWINDOW_H
#define MAINWINDOW_H
#include <QMainWindow>
#include <QLabel>
#include <QString>
#include <QtSql>
#include <QDataWidgetMapper>
namespace Ui {<!-- -->
class MainWindow;
}
class MainWindow : public QMainWindow
{<!-- -->
Q_OBJECT
private:
QLabel *LabInfo;
QSqlDatabase DB; //Database
QSqlQueryModel *qryModel; //data model
QItemSelectionModel *theSelection; //selection model
QDataWidgetMapper *dataMapper;//data interface mapping
void openTable();//Open the data table
void refreshTableView();//Refresh the current row of TableView when moving records
public:
explicit MainWindow(QWidget *parent = 0);
~MainWindow();
private slots:
// The row of SelectionModel of QTableView has changed and processed
void on_currentRowChanged(const QModelIndex & amp; current, const QModelIndex & amp; previous);
private:
Ui::MainWindow *ui;
};
#endif // MAINWINDOW_H
The data model variable qryModel of the QSqlQueryModel type is defined here, as well as the variables mapped to the database, selection model, and data interface. The refreshTableView() function is used to refresh the current row position on the tableView after the record is moved.
The custom slot function on_currentRowChanged() handles the query and photo display of the Photo field when the current row of the selection model changes.
2.4 Open the database
The “Open Database” button on the toolbar corresponds to actOpenDB, select the SQLite database file, and then call the openTable() function to open the database. The code of the slot function on_actOpenDB_triggered() is exactly the same as that of the example samp11_1, so it will not be listed here.
openTable() is used to query data, create interface display and other specific operations, and its code is as follows:
void MainWindow::openTable()
{<!-- -->//Open the data table
qryModel=new QSqlQueryModel(this);
qryModel->setQuery("SELECT empNo, Name, Gender, Height, Birthday, Mobile, Province, City, Department, "
"Education, Salary FROM employee ORDER BY empNo");
if (qryModel->lastError().isValid())
{<!-- -->
QMessageBox::critical(this, "error", "data table query error, error message\
" + qryModel->lastError().text(),
QMessageBox::Ok, QMessageBox::NoButton);
return;
}
LabInfo->setText(QString::asprintf("Number of records: %d", qryModel->rowCount()));
qryModel->setHeaderData(0,Qt::Horizontal,"job number");
qryModel->setHeaderData(1,Qt::Horizontal,"Name");
qryModel->setHeaderData(2,Qt::Horizontal,"gender");
qryModel->setHeaderData(3,Qt::Horizontal,"height");
qryModel->setHeaderData(4,Qt::Horizontal,"date of birth");
qryModel->setHeaderData(5,Qt::Horizontal,"Mobile phone");
qryModel->setHeaderData(6,Qt::Horizontal,"province");
qryModel->setHeaderData(7,Qt::Horizontal,"City");
qryModel->setHeaderData(8,Qt::Horizontal,"Department");
qryModel->setHeaderData(9,Qt::Horizontal,"Education");
qryModel->setHeaderData(10,Qt::Horizontal,"salary");
theSelection=new QItemSelectionModel(qryModel);
//When the selected row changes
connect(theSelection,SIGNAL(currentRowChanged(QModelIndex,QModelIndex)),
this,SLOT(on_currentRowChanged(QModelIndex,QModelIndex)));
ui->tableView->setModel(qryModel);
ui->tableView->setSelectionModel(theSelection);
// ui->tableView->resizeColumnsToContents();
// ui->tableView->horizontalHeader()->setStretchLastSection(true);
//Create data mapping
dataMapper = new QDataWidgetMapper();
dataMapper->setSubmitPolicy(QDataWidgetMapper::AutoSubmit);
dataMapper->setModel(qryModel);
dataMapper->addMapping(ui->dbSpinEmpNo,0);//"empNo";
dataMapper->addMapping(ui->dbEditName,1);//"Name";
dataMapper->addMapping(ui->dbComboSex,2);//"Gender";
dataMapper->addMapping(ui->dbSpinHeight,3);//"Height";
dataMapper->addMapping(ui->dbEditBirth,4);//"Birthday";
dataMapper->addMapping(ui->dbEditMobile,5);//"Mobile";
dataMapper->addMapping(ui->dbComboProvince,6);//"Province";
dataMapper->addMapping(ui->dbEditCity,7);//"City";
dataMapper->addMapping(ui->dbComboDep,8);//"Department";
dataMapper->addMapping(ui->dbComboEdu,9);//"Education";
dataMapper->addMapping(ui->dbSpinSalary,10);//"Salary";
dataMapper->toFirst();
ui->actOpenDB->setEnabled(false);
}
The program first creates a private variable qryModel of type QSqlQueryModel, and then calls the setQuery() function to set the SELECT query statement. The SELECT statement queries all other fields except Memo and Photo from the employee table.
Use the setHeaderData() function to set the display title for each field. To simplify the code, the serial number of the field is directly used here.
The following is the code for the slot function on_currentRowChange():
void MainWindow::on_currentRowChanged(const QModelIndex & amp;current, const QModelIndex & amp;previous)
{<!-- -->
Q_UNUSED(previous);
if (!current.isValid())
{<!-- -->
ui->dbLabPhoto->clear();
return;
}
dataMapper->setCurrentModelIndex(current); //Update the row number of data mapping
bool first=(current.row()==0); //Whether it is the first record
bool last=(current.row()==qryModel->rowCount()-1);//Whether it is the end record
ui->actRecFirst->setEnabled(!first); //Update enabled status
ui->actRecPrevious->setEnabled(!first);
ui->actRecNext->setEnabled(!last);
ui->actRecLast->setEnabled(!last);
int curRecNo=theSelection->currentIndex().row();
QSqlRecord curRec=qryModel->record(curRecNo); //Get the current record
int empNo=curRec.value("EmpNo").toInt();
QSqlQuery query; //Query the data of the Memo and Photo fields of the current empNo
query.prepare("select EmpNo, Memo, Photo from employee where EmpNo = :ID");
query.bindValue(":ID",empNo);
query.exec();
query. first();
QVariant va=query. value("Photo");//
if (!va.isValid()) //The content of the picture field is empty
ui->dbLabPhoto->clear();
else
{<!-- -->//Display photo
QByteArray data=va.toByteArray();
QPixmap pic;
pic.loadFromData(data);
ui->dbLabPhoto->setPixmap(pic.scaledToWidth(ui->dbLabPhoto->size().width()));
}
QVariant va2=query.value("Memo");//Display notes
ui->dbEditMemo->setPlainText(va2.toString());
}
This function implements three functions. The first function is to update the row number of the data mapping, namely:
dataMapper->setCurrentModelIndex(current); //Update the line number of data mapping
Refresh the display component associated with the field on the window to display the content of the current record
The second function is to judge whether it is the first record or the last record according to the current line number, so as to update the enabling status of the Action for moving the four records on the interface.
The third function is to get the value of the EmpNo field of the current record (that is, the employee number), and then use a QSqlQuery variable query to execute the query statement, only query the data of the Memo and Photo fields of this employee, and then display it on the interface component. The QSqlQuery class is used here, which is used to execute arbitrary SQL statements.
When setting the SELECT statement in the variable qryModel of the QSqlQueryModel class, not all fields are queried, because Photo is a BLOB field, and it will inevitably take up a lot of memory after being queried, and when doing record traversal, if there is a BLOB field data execution speed will be very fast slow. Therefore, in this example, QSqlQueryModel is used to query and display the query of ordinary fields, while the query of Memo and Photo field data adopts the method of on-demand query, which can reduce memory consumption and improve the execution speed of record traversal
The rest of the openTable() function is to set the data model and selection model of the tableView, and then create the data interface mapping variable dataMapper to set the mapping relationship between each interface component and the field.
2.5 Record movement
The QDataWidgetMapper class for data mapping always points to the current record of the data model after setting the data model. QDataWidgetMapper has 4 functions to move the current record, namely toFirst(), toLast(), toNext() and toPrevious(). When the current record is moved, it will cause the selection model associated with the data model to emit the currentRowChanged() signal, and the associated custom slot function on currentRowChanged() will also be executed.
There are 4 record movement buttons on the toolbar, they call the record movement function of QDataWidgetMapper to realize record movement, the slot function codes of the 4 Actions are as follows:
void MainWindow::on_actRecFirst_triggered()
{<!-- --> //first record
dataMapper->toFirst();
refreshTableView();
}
void MainWindow::on_actRecPrevious_triggered()
{<!-- --> //The previous record
dataMapper->toPrevious();
refreshTableView();
}
void MainWindow::on_actRecNext_triggered()
{<!-- -->//The next record
dataMapper->toNext();
refreshTableView();
}
void MainWindow::on_actRecLast_triggered()
{<!-- -->//The last record
dataMapper->toLast();
refreshTableView();
}
After using the record movement operation of QDataWidgetMapper, QDataWidgetMapper will move to the new record, and the interface components that map the fields will also automatically display the data of the fields of the new record. However, the current row of the tableView will not change automatically, so you need to call the refreshTableView() function to refresh the display of the tableView. The code of the refreshTableView() function is as follows:
void MainWindow::refreshTableView()
{<!-- -->//Refresh the currently selected row of tableView
int index=dataMapper->currentIndex();
QModelIndex curIndex=qryModel->index(index,1);//
theSelection->clearSelection();//Clear the selection
theSelection->setCurrentIndex(curIndex,QItemSelectionModel::Select);//Set the currently selected row for the behavior just inserted
}