<?xml version="1.0" encoding="iso-8859-1"?>
<!DOCTYPE html
PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<!-- sql-programming.qdoc -->
<head>
<title>Qt 4.6: Using the SQL Model Classes</title>
<link rel="prev" href="sql-sqlstatements.html" />
<link rel="contents" href="sql-programming.html" />
<link rel="next" href="sql-presenting.html" />
<link href="classic.css" rel="stylesheet" type="text/css" />
</head>
<body>
<table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td align="left" valign="top" width="32"><a href="http://qt.nokia.com/"><img src="images/qt-logo.png" align="left" border="0" /></a></td>
<td width="1"> </td><td class="postheader" valign="center"><a href="index.html"><font color="#004faf">Home</font></a> · <a href="classes.html"><font color="#004faf">All Classes</font></a> · <a href="functions.html"><font color="#004faf">All Functions</font></a> · <a href="overviews.html"><font color="#004faf">Overviews</font></a></td></tr></table><p>
[Previous: <a href="sql-sqlstatements.html">Executing SQL Statements</a>]
[<a href="sql-programming.html">SQL Programming</a>]
[Next: <a href="sql-presenting.html">Presenting Data in a Table View</a>]
</p>
<h1 class="title">Using the SQL Model Classes<br /><span class="subtitle"></span>
</h1>
<p>In addition to <a href="qsqlquery.html">QSqlQuery</a>, Qt offers three higher-level classes for accessing databases. These classes are <a href="qsqlquerymodel.html">QSqlQueryModel</a>, <a href="qsqltablemodel.html">QSqlTableModel</a>, and <a href="qsqlrelationaltablemodel.html">QSqlRelationalTableModel</a>.</p>
<p><table class="generic" align="center" cellpadding="2" cellspacing="1" border="0">
<tr valign="top" class="odd"><td><a href="qsqlquerymodel.html">QSqlQueryModel</a></td><td>A read-only model based on an arbitrary SQL query.</td></tr>
<tr valign="top" class="even"><td><a href="qsqltablemodel.html">QSqlTableModel</a></td><td>A read-write model that works on a single table.</td></tr>
<tr valign="top" class="odd"><td><a href="qsqlrelationaltablemodel.html">QSqlRelationalTableModel</a></td><td>A <a href="qsqltablemodel.html">QSqlTableModel</a> subclass with foreign key support.</td></tr>
</table></p>
<p>These classes derive from <a href="qabstracttablemodel.html">QAbstractTableModel</a> (which in turn inherits from <a href="qabstractitemmodel.html">QAbstractItemModel</a>) and make it easy to present data from a database in an item view class such as <a href="qlistview.html">QListView</a> and <a href="qtableview.html">QTableView</a>. This is explained in detail in the <a href="sql-presenting.html">Presenting Data in a Table View</a> section.</p>
<p>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 <a href="qsqltablemodel.html">QSqlTableModel</a> 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.</p>
<a name="the-sql-query-model"></a>
<h2>The SQL Query Model</h2>
<p><a href="qsqlquerymodel.html">QSqlQueryModel</a> offers a read-only model based on an SQL query.</p>
<p>Example:</p>
<pre> 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;
}</pre>
<p>After setting the query using <a href="qsqlquerymodel.html#setQuery">QSqlQueryModel::setQuery</a>(), you can use QSqlQueryModel::record(int) to access the individual records. You can also use <a href="qsqlquerymodel.html#data">QSqlQueryModel::data</a>() and any of the other functions inherited from <a href="qabstractitemmodel.html">QAbstractItemModel</a>.</p>
<p>There's also a <a href="qsqlquerymodel.html#setQuery">setQuery()</a> overload that takes a <a href="qsqlquery.html">QSqlQuery</a> object and operates on its result set. This enables you to use any features of <a href="qsqlquery.html">QSqlQuery</a> to set up the query (e.g., prepared queries).</p>
<a name="the-sql-table-model"></a>
<h2>The SQL Table Model</h2>
<p><a href="qsqltablemodel.html">QSqlTableModel</a> offers a read-write model that works on a single SQL table at a time.</p>
<p>Example:</p>
<pre> 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;
}</pre>
<p><a href="qsqltablemodel.html">QSqlTableModel</a> is a high-level alternative to <a href="qsqlquery.html">QSqlQuery</a> for navigating and modifying individual SQL tables. It typically results in less code and requires no knowledge of SQL syntax.</p>
<p>Use <a href="qsqlquerymodel.html#record">QSqlTableModel::record</a>() to retrieve a row in the table, and <a href="qsqltablemodel.html#setRecord">QSqlTableModel::setRecord</a>() to modify the row. For example, the following code will increase every employee's salary by 10 per cent:</p>
<pre> 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();</pre>
<p>You can also use <a href="qsqltablemodel.html#data">QSqlTableModel::data</a>() and <a href="qsqltablemodel.html#setData">QSqlTableModel::setData</a>(), which are inherited from <a href="qabstractitemmodel.html">QAbstractItemModel</a>, to access the data. For example, here's how to update a record using <a href="qsqltablemodel.html#setData">setData()</a>:</p>
<pre> model.setData(model.index(row, column), 75000);
model.submitAll();</pre>
<p>Here's how to insert a row and populate it:</p>
<pre> 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();</pre>
<p>Here's how to delete five consecutive rows:</p>
<pre> model.removeRows(row, 5);
model.submitAll();</pre>
<p>The first argument to <a href="qsqltablemodel.html#removeRows">QSqlTableModel::removeRows</a>() is the index of the first row to delete.</p>
<p>When you're finished changing a record, you should always call <a href="qsqltablemodel.html#submitAll">QSqlTableModel::submitAll</a>() to ensure that the changes are written to the database.</p>
<p>When and whether you actually <i>need</i> to call submitAll() depends on the table's <a href="qsqltablemodel.html#editStrategy">edit strategy</a>. The default strategy is <a href="qsqltablemodel.html#EditStrategy-enum">QSqlTableModel::OnRowChange</a>, which specifies that pending changes are applied to the database when the user selects a different row. Other strategies are <a href="qsqltablemodel.html#EditStrategy-enum">QSqlTableModel::OnManualSubmit</a> (where all changes are cached in the model until you call submitAll()) and <a href="qsqltablemodel.html#EditStrategy-enum">QSqlTableModel::OnFieldChange</a> (where no changes are cached). These are mostly useful when <a href="qsqltablemodel.html">QSqlTableModel</a> is used with a view.</p>
<p><a href="qsqltablemodel.html#EditStrategy-enum">QSqlTableModel::OnFieldChange</a> seems to deliver the promise that you never need to call submitAll() explicitly. There are two pitfalls, though:</p>
<ul>
<li>Without any caching, performance may drop significantly.</li>
<li>If you modify a primary key, the record might slip through your fingers while you are trying to populate it.</li>
</ul>
<a name="the-sql-relational-table-model"></a>
<h2>The SQL Relational Table Model</h2>
<p><a href="qsqlrelationaltablemodel.html">QSqlRelationalTableModel</a> extends <a href="qsqltablemodel.html">QSqlTableModel</a> 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 <tt>book</tt> table has a field called <tt>authorid</tt> that refers to the author table's <tt>id</tt> field, we say that <tt>authorid</tt> is a foreign key.</p>
<p><table class="generic" align="center" cellpadding="2" cellspacing="1" border="0">
<tr valign="top" class="odd"><td><img src="images/noforeignkeys.png" /></td><td><img src="images/foreignkeys.png" /></td></tr>
</table></p>
<p>The screenshot on the left shows a plain <a href="qsqltablemodel.html">QSqlTableModel</a> in a <a href="qtableview.html">QTableView</a>. Foreign keys (<tt>city</tt> and <tt>country</tt>) aren't resolved to human-readable values. The screenshot on the right shows a <a href="qsqlrelationaltablemodel.html">QSqlRelationalTableModel</a>, with foreign keys resolved into human-readable text strings.</p>
<p>The following code snippet shows how the <a href="qsqlrelationaltablemodel.html">QSqlRelationalTableModel</a> was set up:</p>
<pre> model->setTable("employee");
model->setRelation(2, QSqlRelation("city", "id", "name"));
model->setRelation(3, QSqlRelation("country", "id", "name"));</pre>
<p>See the <a href="qsqlrelationaltablemodel.html">QSqlRelationalTableModel</a> documentation for details.</p>
<p>
[Previous: <a href="sql-sqlstatements.html">Executing SQL Statements</a>]
[<a href="sql-programming.html">SQL Programming</a>]
[Next: <a href="sql-presenting.html">Presenting Data in a Table View</a>]
</p>
<p /><address><hr /><div align="center">
<table width="100%" cellspacing="0" border="0"><tr class="address">
<td width="40%" align="left">Copyright © 2010 Nokia Corporation and/or its subsidiary(-ies)</td>
<td width="20%" align="center"><a href="trademarks.html">Trademarks</a></td>
<td width="40%" align="right"><div align="right">Qt 4.6.3</div></td>
</tr></table></div></address></body>
</html>
distrib
>
Mandriva
>
current
>
i586
>
media
>
main-updates
>
by-pkgid
>
8e6051afcdb111a0317a58fb64c2abf5
>
files
>
5838
