Qt Assistant Keyword: Presenting Data in a Table View

Qt Version 5.14.0

The three classes QSqlQueryModel, QSqlTableModel and QSqlRelationalTableModel can be used as data sources for Qt View related display classes, such as QTableView, QListView and QTreeView.The most common should be the QTableView, because the resulting dataset of an SQL statement must be a two-dimensional data structure.

Views

View creation: Create a new view and set a data source for it:

QTableView *view = new QTableView;
view->setModel(model);
iew->show();

Settings View Not Editable

view->setEditTriggers(QAbstractItemView::NoEditTriggers);

Multiple views can use the same data Model, and when data is modified in one View, the other View immediately refreshes the display.

The view class displays a header at the top of the view, and you can call the setHeaderData() function of the model to modify the header text.The following code sets the title bar as the field name of the data table:

model->setHeaderData(0, Qt::Horizontal, QObject::tr("ID"));
model->setHeaderData(0, Qt::Horizontal, QObject::tr("Name"));
model->setHeaderData(0, Qt::Horizontal, QObject::tr("City"));
model->setHeaderData(0, Qt::Horizontal, QObject::tr("Country"));

The QTableView also has a vertical Title bar, which is commonly used to display line numbers.When a new row is inserted using QSqlTableModel::insertRows(), the vertical title bar corresponding to the new row displays'*', and the row number of the new row is not displayed until the modification is written to the database.

Similarly, when you call removeRows() to delete a row, the title bar will show "!" to the row before the modification is submitted.

Cells in the view display data that is rendered using delegates.The default delegate class is QItemDelegate, which can render basic data types such as QString, int, QImage, and so on.When a user edits an item in the view, the delegate is also responsible for providing edit controls, such as a comboBox.You can implement your own delegate class by inheriting QItemDelegate or QAbstractItemDelegate.For more information, see the Qt Assistant keyword Model/View Programming.

QSqlTableModel can only operate on one data table. If you need a read-write model to operate on any result dataset, you can inherit the QSqlQueryModle class and re-implement the flags() and setData() functions.The following functions make the first and second fields of the query model readable and writable:

 Qt::ItemFlags EditableSqlModel::flags(const QModelIndex &index) const
 {
 	Qt::ItemFlags flags = QSqlQueryModel::flags(index);
 	if (index.column() == 1 || index.column() == 2)
 	flags |= Qt::ItemIsEditable;
 	return flags;
 }

 bool EditableSqlModel::setData(const QModelIndex &index, const QVariant &value, int /* role */)
 {
 	if (index.column() < 1 || index.column() > 2)
 		return false;
		
 	QModelIndex primaryKeyIndex = QSqlQueryModel::index(index.row(), 0);
 	int id = data(primaryKeyIndex).toInt();
 	clear();
 	bool ok;
 	if (index.column() == 1)
	{
 		ok = setFirstName(id, value.toString());
 	}
	else 
	{
 		ok = setLastName(id, value.toString());
 	}
 	refresh();
 	return ok;
 }

 bool EditableSqlModel::setFirstName(int personId, const QString &firstName)
 {
 	QSqlQuery query;
 	query.prepare("update person set firstname = ? where id = ?");
 	query.addBindValue(firstName);
 	query.addBindValue(personId);
 	return query.exec();
 }

A custom model can also perform many functions, such as setting background colors, setting edit controls, providing toolTip, dealing specifically with null values, and so on.

If you want the foreign key to appear as a more human-friendly string, you can use the QSqlRelationalTableModel class.For better display, you can use the QSqlRelationalDelegate delegate, which provides a drop-down list editing control when you edit foreign key values.

For more information, see the Qt Assistant keyword: Relational Table Model.