The Qt 4 Database GUI Layer

Example Code

The simplest way to present data from a database is to simply combine a QSqlQueryModel with a QTableView:

 QSqlQueryModel model;
 model.setQuery("select * from person");

 QTableView view;
 view.setModel(&model);
 view.show();

To present the contents of a single table, we can use QSqlTableModel instead:

 QSqlTableModel model;
 model.setTable("person");
 model.select();

 QTableView view;
 view.setModel(&model);
 view.show();

In practice, it's common that we need to customize the rendering of a field in the database. In that case, we can create our own model based on QSqlQueryModel. The next code snippet shows a custom model that prepends '#' to the value in field 0 and converts the value in field 2 to uppercase:

 class CustomSqlModel : public QSqlQueryModel
 {
     Q_OBJECT

 public:
     CustomSqlModel(QObject *parent = 0);

     QVariant data(const QModelIndex &item, int role) const;
 };

 QVariant CustomSqlModel::data(const QModelIndex &index, int role) const
 {
     QVariant value = QSqlQueryModel::data(index, role);
     if (value.isValid() && role == Qt::DisplayRole) {
         if (index.column() == 0)
             return value.toString().prepend("#");
         else if (index.column() == 2)
             return value.toString().toUpper();
     }
     if (role == Qt::TextColorRole && index.column() == 1)
         return QVariant::fromValue(QColor(Qt::blue));
     return value;
 }

It is also possible to subclass QSqlQueryModel to add support for editing. This is done by reimplementing QAbstractItemModel::flags() to specify which database fields are editable and QAbstractItemModel::setData() to modify the database. Here's an example of a setData() reimplementation that changes the first or last name of a person:

 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;
 }

It relies on helper functions called setFirstName() and setLastName(), which execute an update. Here's setFirstName():

 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();
 }

See Qt's examples/sql directory for more examples.