<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en_US" lang="en_US">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<!-- sql-programming.qdoc -->
<title>Qt 4.8: Using the SQL Model Classes</title>
<link rel="stylesheet" type="text/css" href="style/style.css" />
<script src="scripts/jquery.js" type="text/javascript"></script>
<script src="scripts/functions.js" type="text/javascript"></script>
<link rel="stylesheet" type="text/css" href="style/superfish.css" />
<link rel="stylesheet" type="text/css" href="style/narrow.css" />
<!--[if IE]>
<meta name="MSSmartTagsPreventParsing" content="true">
<meta http-equiv="imagetoolbar" content="no">
<![endif]-->
<!--[if lt IE 7]>
<link rel="stylesheet" type="text/css" href="style/style_ie6.css">
<![endif]-->
<!--[if IE 7]>
<link rel="stylesheet" type="text/css" href="style/style_ie7.css">
<![endif]-->
<!--[if IE 8]>
<link rel="stylesheet" type="text/css" href="style/style_ie8.css">
<![endif]-->
<script src="scripts/superfish.js" type="text/javascript"></script>
<script src="scripts/narrow.js" type="text/javascript"></script>
</head>
<body class="" onload="CheckEmptyAndLoadList();">
<div class="header" id="qtdocheader">
<div class="content">
<div id="nav-logo">
<a href="index.html">Home</a></div>
<a href="index.html" class="qtref"><span>Qt Reference Documentation</span></a>
<div id="narrowsearch"></div>
<div id="nav-topright">
<ul>
<li class="nav-topright-home"><a href="http://qt.digia.com/">Qt HOME</a></li>
<li class="nav-topright-dev"><a href="http://qt-project.org/">DEV</a></li>
<li class="nav-topright-doc nav-topright-doc-active"><a href="http://qt-project.org/doc/">
DOC</a></li>
<li class="nav-topright-blog"><a href="http://blog.qt.digia.com/">BLOG</a></li>
</ul>
</div>
<div id="shortCut">
<ul>
<li class="shortCut-topleft-inactive"><span><a href="index.html">Qt 4.8</a></span></li>
<li class="shortCut-topleft-active"><a href="http://qt-project.org/doc/">ALL VERSIONS </a></li>
</ul>
</div>
<ul class="sf-menu" id="narrowmenu">
<li><a href="#">API Lookup</a>
<ul>
<li><a href="classes.html">Class index</a></li>
<li><a href="functions.html">Function index</a></li>
<li><a href="modules.html">Modules</a></li>
<li><a href="namespaces.html">Namespaces</a></li>
<li><a href="qtglobal.html">Global Declarations</a></li>
<li><a href="qdeclarativeelements.html">QML elements</a></li>
</ul>
</li>
<li><a href="#">Qt Topics</a>
<ul>
<li><a href="qt-basic-concepts.html">Programming with Qt</a></li>
<li><a href="qtquick.html">Device UIs & Qt Quick</a></li>
<li><a href="qt-gui-concepts.html">UI Design with Qt</a></li>
<li><a href="supported-platforms.html">Supported Platforms</a></li>
<li><a href="technology-apis.html">Qt and Key Technologies</a></li>
<li><a href="best-practices.html">How-To's and Best Practices</a></li>
</ul>
</li>
<li><a href="#">Examples</a>
<ul>
<li><a href="all-examples.html">Examples</a></li>
<li><a href="tutorials.html">Tutorials</a></li>
<li><a href="demos.html">Demos</a></li>
<li><a href="qdeclarativeexamples.html">QML Examples</a></li>
</ul>
</li>
</ul>
</div>
</div>
<div class="wrapper">
<div class="hd">
<span></span>
</div>
<div class="bd group">
<div class="sidebar">
<div class="searchlabel">
Search index:</div>
<div class="search" id="sidebarsearch">
<form id="qtdocsearch" action="" onsubmit="return false;">
<fieldset>
<input type="text" name="searchstring" id="pageType" value="" />
<div id="resultdialog">
<a href="#" id="resultclose">Close</a>
<p id="resultlinks" class="all"><a href="#" id="showallresults">All</a> | <a href="#" id="showapiresults">API</a> | <a href="#" id="showarticleresults">Articles</a> | <a href="#" id="showexampleresults">Examples</a></p>
<p id="searchcount" class="all"><span id="resultcount"></span><span id="apicount"></span><span id="articlecount"></span><span id="examplecount"></span> results:</p>
<ul id="resultlist" class="all">
</ul>
</div>
</fieldset>
</form>
</div>
<div class="box first bottombar" id="lookup">
<h2 title="API Lookup"><span></span>
API Lookup</h2>
<div id="list001" class="list">
<ul id="ul001" >
<li class="defaultLink"><a href="classes.html">Class index</a></li>
<li class="defaultLink"><a href="functions.html">Function index</a></li>
<li class="defaultLink"><a href="modules.html">Modules</a></li>
<li class="defaultLink"><a href="namespaces.html">Namespaces</a></li>
<li class="defaultLink"><a href="qtglobal.html">Global Declarations</a></li>
<li class="defaultLink"><a href="qdeclarativeelements.html">QML elements</a></li>
</ul>
</div>
</div>
<div class="box bottombar" id="topics">
<h2 title="Qt Topics"><span></span>
Qt Topics</h2>
<div id="list002" class="list">
<ul id="ul002" >
<li class="defaultLink"><a href="qt-basic-concepts.html">Programming with Qt</a></li>
<li class="defaultLink"><a href="qtquick.html">Device UIs & Qt Quick</a></li>
<li class="defaultLink"><a href="qt-gui-concepts.html">UI Design with Qt</a></li>
<li class="defaultLink"><a href="supported-platforms.html">Supported Platforms</a></li>
<li class="defaultLink"><a href="technology-apis.html">Qt and Key Technologies</a></li>
<li class="defaultLink"><a href="best-practices.html">How-To's and Best Practices</a></li>
</ul>
</div>
</div>
<div class="box" id="examples">
<h2 title="Examples"><span></span>
Examples</h2>
<div id="list003" class="list">
<ul id="ul003">
<li class="defaultLink"><a href="all-examples.html">Examples</a></li>
<li class="defaultLink"><a href="tutorials.html">Tutorials</a></li>
<li class="defaultLink"><a href="demos.html">Demos</a></li>
<li class="defaultLink"><a href="qdeclarativeexamples.html">QML Examples</a></li>
</ul>
</div>
</div>
</div>
<div class="wrap">
<div class="toolbar">
<div class="breadcrumb toolblock">
<ul>
<li class="first"><a href="index.html">Home</a></li>
<!-- Breadcrumbs go here -->
<li>Using the SQL Model Classes</li>
</ul>
</div>
<div class="toolbuttons toolblock">
<ul>
<li id="smallA" class="t_button">A</li>
<li id="medA" class="t_button active">A</li>
<li id="bigA" class="t_button">A</li>
<li id="print" class="t_button"><a href="javascript:this.print();">
<span>Print</span></a></li>
</ul>
</div>
</div>
<div class="content mainContent">
<link rel="prev" href="sql-sqlstatements.html" />
<link rel="next" href="sql-presenting.html" />
<p class="naviNextPrevious headerNavi">
<a class="prevPage" href="sql-sqlstatements.html">Executing SQL Statements</a>
<a class="nextPage" href="sql-presenting.html">Presenting Data in a Table View</a>
</p><p/>
<div class="toc">
<h3><a name="toc">Contents</a></h3>
<ul>
<li class="level2"><a href="#the-sql-query-model">The SQL Query Model</a></li>
<li class="level2"><a href="#the-sql-table-model">The SQL Table Model</a></li>
<li class="level2"><a href="#the-sql-relational-table-model">The SQL Relational Table Model</a></li>
</ul>
</div>
<h1 class="title">Using the SQL Model Classes</h1>
<span class="subtitle"></span>
<!-- $$$sql-model.html-description -->
<div class="descr"> <a name="details"></a>
<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>
<table class="generic">
<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>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>
<h3>The SQL Query Model</h3>
<p><a href="qsqlquerymodel.html">QSqlQueryModel</a> offers a read-only model based on an SQL query.</p>
<p>Example:</p>
<pre class="cpp"> <span class="type"><a href="qsqlquerymodel.html">QSqlQueryModel</a></span> model;
model<span class="operator">.</span>setQuery(<span class="string">"SELECT * FROM employee"</span>);
<span class="keyword">for</span> (<span class="type">int</span> i <span class="operator">=</span> <span class="number">0</span>; i <span class="operator"><</span> model<span class="operator">.</span>rowCount(); <span class="operator">+</span><span class="operator">+</span>i) {
<span class="type">int</span> id <span class="operator">=</span> model<span class="operator">.</span>record(i)<span class="operator">.</span>value(<span class="string">"id"</span>)<span class="operator">.</span>toInt();
<span class="type"><a href="qstring.html">QString</a></span> name <span class="operator">=</span> model<span class="operator">.</span>record(i)<span class="operator">.</span>value(<span class="string">"name"</span>)<span class="operator">.</span>toString();
<a href="qtglobal.html#qDebug">qDebug</a>() <span class="operator"><</span><span class="operator"><</span> id <span class="operator"><</span><span class="operator"><</span> 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>
<h3>The SQL Table Model</h3>
<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 class="cpp"> <span class="type"><a href="qsqltablemodel.html">QSqlTableModel</a></span> model;
model<span class="operator">.</span>setTable(<span class="string">"employee"</span>);
model<span class="operator">.</span>setFilter(<span class="string">"salary > 50000"</span>);
model<span class="operator">.</span>setSort(<span class="number">2</span><span class="operator">,</span> <span class="type"><a href="qt.html">Qt</a></span><span class="operator">::</span>DescendingOrder);
model<span class="operator">.</span>select();
<span class="keyword">for</span> (<span class="type">int</span> i <span class="operator">=</span> <span class="number">0</span>; i <span class="operator"><</span> model<span class="operator">.</span>rowCount(); <span class="operator">+</span><span class="operator">+</span>i) {
<span class="type"><a href="qstring.html">QString</a></span> name <span class="operator">=</span> model<span class="operator">.</span>record(i)<span class="operator">.</span>value(<span class="string">"name"</span>)<span class="operator">.</span>toString();
<span class="type">int</span> salary <span class="operator">=</span> model<span class="operator">.</span>record(i)<span class="operator">.</span>value(<span class="string">"salary"</span>)<span class="operator">.</span>toInt();
<a href="qtglobal.html#qDebug">qDebug</a>() <span class="operator"><</span><span class="operator"><</span> name <span class="operator"><</span><span class="operator"><</span> 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 class="cpp"> <span class="keyword">for</span> (<span class="type">int</span> i <span class="operator">=</span> <span class="number">0</span>; i <span class="operator"><</span> model<span class="operator">.</span>rowCount(); <span class="operator">+</span><span class="operator">+</span>i) {
<span class="type"><a href="qsqlrecord.html">QSqlRecord</a></span> record <span class="operator">=</span> model<span class="operator">.</span>record(i);
<span class="type">double</span> salary <span class="operator">=</span> record<span class="operator">.</span>value(<span class="string">"salary"</span>)<span class="operator">.</span>toInt();
salary <span class="operator">*</span><span class="operator">=</span> <span class="number">1.1</span>;
record<span class="operator">.</span>setValue(<span class="string">"salary"</span><span class="operator">,</span> salary);
model<span class="operator">.</span>setRecord(i<span class="operator">,</span> record);
}
model<span class="operator">.</span>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 class="cpp"> model<span class="operator">.</span>setData(model<span class="operator">.</span>index(row<span class="operator">,</span> column)<span class="operator">,</span> <span class="number">75000</span>);
model<span class="operator">.</span>submitAll();</pre>
<p>Here's how to insert a row and populate it:</p>
<pre class="cpp"> model<span class="operator">.</span>insertRows(row<span class="operator">,</span> <span class="number">1</span>);
model<span class="operator">.</span>setData(model<span class="operator">.</span>index(row<span class="operator">,</span> <span class="number">0</span>)<span class="operator">,</span> <span class="number">1013</span>);
model<span class="operator">.</span>setData(model<span class="operator">.</span>index(row<span class="operator">,</span> <span class="number">1</span>)<span class="operator">,</span> <span class="string">"Peter Gordon"</span>);
model<span class="operator">.</span>setData(model<span class="operator">.</span>index(row<span class="operator">,</span> <span class="number">2</span>)<span class="operator">,</span> <span class="number">68500</span>);
model<span class="operator">.</span>submitAll();</pre>
<p>Here's how to delete five consecutive rows:</p>
<pre class="cpp"> model<span class="operator">.</span>removeRows(row<span class="operator">,</span> <span class="number">5</span>);
model<span class="operator">.</span>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>
<h3>The SQL Relational Table Model</h3>
<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>
<table class="generic">
<tr valign="top" class="odd"><td ><img src="images/noforeignkeys.png" alt="" /></td><td ><img src="images/foreignkeys.png" alt="" /></td></tr>
</table>
<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 class="cpp"> model<span class="operator">-</span><span class="operator">></span>setTable(<span class="string">"employee"</span>);
model<span class="operator">-</span><span class="operator">></span>setRelation(<span class="number">2</span><span class="operator">,</span> <span class="type"><a href="qsqlrelation.html">QSqlRelation</a></span>(<span class="string">"city"</span><span class="operator">,</span> <span class="string">"id"</span><span class="operator">,</span> <span class="string">"name"</span>));
model<span class="operator">-</span><span class="operator">></span>setRelation(<span class="number">3</span><span class="operator">,</span> <span class="type"><a href="qsqlrelation.html">QSqlRelation</a></span>(<span class="string">"country"</span><span class="operator">,</span> <span class="string">"id"</span><span class="operator">,</span> <span class="string">"name"</span>));</pre>
<p>See the <a href="qsqlrelationaltablemodel.html">QSqlRelationalTableModel</a> documentation for details.</p>
</div>
<!-- @@@sql-model.html -->
<p class="naviNextPrevious footerNavi">
<a class="prevPage" href="sql-sqlstatements.html">Executing SQL Statements</a>
<a class="nextPage" href="sql-presenting.html">Presenting Data in a Table View</a>
</p>
</div>
</div>
</div>
<div class="ft">
<span></span>
</div>
</div>
<div class="footer">
<p>
<acronym title="Copyright">©</acronym> 2015 The Qt Company Ltd.
Documentation contributions included herein are the copyrights of
their respective owners.</p>
<br />
<p>
The documentation provided herein is licensed under the terms of the
<a href="http://www.gnu.org/licenses/fdl.html">GNU Free Documentation
License version 1.3</a> as published by the Free Software Foundation.</p>
<p>
Documentation sources may be obtained from <a href="http://www.qt-project.org">
www.qt-project.org</a>.</p>
<br />
<p>
Qt and respective logos are trademarks of The Qt Company Ltd
in Finland and/or other countries worldwide. All other trademarks are property
of their respective owners. <a title="Privacy Policy"
href="http://en.gitorious.org/privacy_policy/">Privacy Policy</a></p>
</div>
<script src="scripts/functions.js" type="text/javascript"></script>
</body>
</html>
