Viadeo Twitter Google Bookmarks ! Facebook Digg del.icio.us MySpace Yahoo MyWeb Blinklist Netvouz Reddit Simpy StumbleUpon Bookmarks Windows Live Favorites 
Logo Documentation Qt ·  Page d'accueil  ·  Toutes les classes  ·  Toutes les fonctions  ·  Vues d'ensemble  · 

Using the SQL Model Classes

In addition to QSqlQuery, Qt offers three higher-level classes for accessing databases. These classes are QSqlQueryModel, QSqlTableModel, and QSqlRelationalTableModel.

QSqlQueryModelA read-only model based on an arbitrary SQL query.
QSqlTableModelA read-write model that works on a single table.
QSqlRelationalTableModelA QSqlTableModel subclass with foreign key support.

These classes derive from QAbstractTableModel (which in turn inherits from QAbstractItemModel) and make it easy to present data from a database in an item view class such as QListView and QTableView. This is explained in detail in the Presenting Data in a Table View section.

Another advantage of using these classes is that it can make your code easier to adapt to other data sources. For example, if you use QSqlTableModel and later decide to use XML files to store data instead of a database, it is essentially just a matter of replacing one data model with another.

The SQL Query Model

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).

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.

[Previous: Executing SQL Statements] [SQL Programming] [Next: Presenting Data in a Table View]

Publicité

Best Of

Actualités les plus lues

Semaine
Mois
Année

Le Qt Developer Network au hasard

Logo

Utiliser QML et QtWebKit avec PySide

Le Qt Developer Network est un réseau de développeurs Qt anglophone, où ils peuvent partager leur expérience sur le framework. Lire l'article.

Communauté

Ressources

Liens utiles

Contact

  • Vous souhaitez rejoindre la rédaction ou proposer un tutoriel, une traduction, une question... ? Postez dans le forum Contribuez ou contactez-nous par MP ou par email (voir en bas de page).

Qt dans le magazine

Cette page est une traduction d'une page de la documentation de Qt, écrite par Nokia Corporation and/or its subsidiary(-ies). Les éventuels problèmes résultant d'une mauvaise traduction ne sont pas imputables à Nokia. Qt 4.6
Copyright © 2012 Developpez LLC. Tous droits réservés Developpez LLC. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents et images sans l'autorisation expresse de Developpez LLC. Sinon, vous encourez selon la loi jusqu'à 3 ans de prison et jusqu'à 300 000 E de dommages et intérêts. Cette page est déposée à la SACD.
Vous avez déniché une erreur ? Un bug ? Une redirection cassée ? Ou tout autre problème, quel qu'il soit ? Ou bien vous désirez participer à ce projet de traduction ? N'hésitez pas à nous contacter ou par MP !
 
 
 
 
Partenaires

Hébergement Web