1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
|
<?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/offline.css" />
</head>
<body>
<div class="header" id="qtdocheader">
<div class="content">
<a href="index.html" class="qtref"><span>Qt Reference Documentation</span></a>
</div>
<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>
<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 class="ft">
<span></span>
</div>
</div>
<div class="footer">
<p>
<acronym title="Copyright">©</acronym> 2012 Nokia Corporation and/or its
subsidiaries. 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>
Nokia, Qt and their respective logos are trademarks of Nokia Corporation
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>
</body>
</html>
|
