<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE html> <html lang="en"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <!-- sql-programming.qdoc --> <title>Using the SQL Model Classes | Qt SQL 5.12.6</title> <link rel="stylesheet" type="text/css" href="style/offline-simple.css" /> <script type="text/javascript"> document.getElementsByTagName("link").item(0).setAttribute("href", "style/offline.css"); // loading style sheet breaks anchors that were jumped to before // so force jumping to anchor again setTimeout(function() { var anchor = location.hash; // need to jump to different anchor first (e.g. none) location.hash = "#"; setTimeout(function() { location.hash = anchor; }, 0); }, 0); </script> </head> <body> <div class="header" id="qtdocheader"> <div class="main"> <div class="main-rounded"> <div class="navigationbar"> <table><tr> <td >Qt 5.12</td><td ><a href="qtsql-index.html">Qt SQL</a></td><td >Using the SQL Model Classes</td></tr></table><table class="buildversion"><tr> <td id="buildversion" width="100%" align="right"><a href="qtsql-index.html">Qt 5.12.6 Reference Documentation</a></td> </tr></table> </div> </div> <div class="content"> <div class="line"> <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> <span class="naviSeparator"> ◦ </span> <a class="nextPage" href="sql-presenting.html">Presenting Data in a Table View</a> </p><p/> <div class="sidebar"> <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> <div class="sidebar-content" id="sidebar-content"></div></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> <div class="table"><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></div> <p>These classes derive from <a href="../qtcore/qabstracttablemodel.html">QAbstractTableModel</a> (which in turn inherits from <a href="../qtcore/qabstractitemmodel.html">QAbstractItemModel</a>) and make it easy to present data from a database in an item view class such as <a href="../qtwidgets/qlistview.html">QListView</a> and <a href="../qtwidgets/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 id="the-sql-query-model">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="../qtcore/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="../qtcore/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 <a href="qsqlquerymodel.html#record">QSqlQueryModel::record</a>(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="../qtcore/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 id="the-sql-table-model">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="../qtcore/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="../qtcore/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="../qtcore/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="qsqltablemodel.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="../qtcore/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 id="the-sql-relational-table-model">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 <code>book</code> table has a field called <code>authorid</code> that refers to the author table's <code>id</code> field, we say that <code>authorid</code> is a foreign key.</p> <div class="table"><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></div> <p>The screenshot on the left shows a plain <a href="qsqltablemodel.html">QSqlTableModel</a> in a <a href="../qtwidgets/qtableview.html">QTableView</a>. Foreign keys (<code>city</code> and <code>country</code>) 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> <span class="naviSeparator"> ◦ </span> <a class="nextPage" href="sql-presenting.html">Presenting Data in a Table View</a> </p> </div> </div> </div> </div> </div> <div class="footer"> <p> <acronym title="Copyright">©</acronym> 2019 The Qt Company Ltd. Documentation contributions included herein are the copyrights of their respective owners.<br/> 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.<br/> 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. </p> </div> </body> </html>