Using the SQL Model Classes

The SQL Table Model

QSqlTableModel offers a read-write model that works on a single SQL table at a time.

Example:

     QSqlTableModel model;
     model.setTable("employee");
     model.setFilter("salary > 50000");
     model.setSort(2, Qt::DescendingOrder);
     model.select();

     for (int i = 0; i < model.rowCount(); ++i) {
         QString name = model.record(i).value("name").toString();
         int salary = model.record(i).value("salary").toInt();
         qDebug() << name << salary;
     }

QSqlTableModel is a high-level alternative to QSqlQuery for navigating and modifying individual SQL tables. It typically results in less code and requires no knowledge of SQL syntax.

Use QSqlTableModel::record() to retrieve a row in the table, and QSqlTableModel::setRecord() to modify the row. For example, the following code will increase every employee's salary by 10 per cent:

     for (int i = 0; i < model.rowCount(); ++i) {
         QSqlRecord record = model.record(i);
         double salary = record.value("salary").toInt();
         salary *= 1.1;
         record.setValue("salary", salary);
         model.setRecord(i, record);
     }
     model.submitAll();

You can also use QSqlTableModel::data() and QSqlTableModel::setData(), which are inherited from QAbstractItemModel, to access the data. For example, here's how to update a record using setData():

     model.setData(model.index(row, column), 75000);
     model.submitAll();

Here's how to insert a row and populate it:

     model.insertRows(row, 1);
     model.setData(model.index(row, 0), 1013);
     model.setData(model.index(row, 1), "Peter Gordon");
     model.setData(model.index(row, 2), 68500);
     model.submitAll();

Here's how to delete five consecutive rows:

     model.removeRows(row, 5);
     model.submitAll();

The first argument to QSqlTableModel::removeRows() is the index of the first row to delete.

When you're finished changing a record, you should always call QSqlTableModel::submitAll() to ensure that the changes are written to the database.

When and whether you actually need to call submitAll() depends on the table's edit strategy. The default strategy is QSqlTableModel::OnRowChange, which specifies that pending changes are applied to the database when the user selects a different row. Other strategies are QSqlTableModel::OnManualSubmit (where all changes are cached in the model until you call submitAll()) and QSqlTableModel::OnFieldChange (where no changes are cached). These are mostly useful when QSqlTableModel is used with a view.

QSqlTableModel::OnFieldChange seems to deliver the promise that you never need to call submitAll() explicitly. There are two pitfalls, though:

• Without any caching, performance may drop significantly.
• If you modify a primary key, the record might slip through your fingers while you are trying to populate it.

The SQL Relational Table Model

QSqlRelationalTableModel extends QSqlTableModel to provide support for foreign keys. A foreign key is a 1-to-1 mapping between a field in one table and the primary key field of another table. For example, if a book table has a field called authorid that refers to the author table's id field, we say that authorid is a foreign key.

The screenshot on the left shows a plain QSqlTableModel in a QTableView. Foreign keys (city and country) aren't resolved to human-readable values. The screenshot on the right shows a QSqlRelationalTableModel, with foreign keys resolved into human-readable text strings.

The following code snippet shows how the QSqlRelationalTableModel was set up:

     model->setTable("employee");

     model->setRelation(2, QSqlRelation("city", "id", "name"));
     model->setRelation(3, QSqlRelation("country", "id", "name"));

See the QSqlRelationalTableModel documentation for details.