Using the SQL Model Classes

QSqlQueryModel offers a read-only model based on an SQL query.

Example:

    QSqlQueryModel model;
    model.setQuery("SELECT * FROM employee");

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

After setting the query using QSqlQueryModel::setQuery(), you can use QSqlQueryModel::record(int) to access the individual records. You can also use QSqlQueryModel::data() and any of the other functions inherited from QAbstractItemModel.

There's also a setQuery() overload that takes a QSqlQuery object and operates on its result set. This enables you to use any features of QSqlQuery to set up the query (e.g., prepared queries).

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.

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.