1 | /****************************************************************************
|
---|
2 | **
|
---|
3 | ** Copyright (C) 2011 Nokia Corporation and/or its subsidiary(-ies).
|
---|
4 | ** All rights reserved.
|
---|
5 | ** Contact: Nokia Corporation ([email protected])
|
---|
6 | **
|
---|
7 | ** This file is part of the documentation of the Qt Toolkit.
|
---|
8 | **
|
---|
9 | ** $QT_BEGIN_LICENSE:FDL$
|
---|
10 | ** Commercial Usage
|
---|
11 | ** Licensees holding valid Qt Commercial licenses may use this file in
|
---|
12 | ** accordance with the Qt Commercial License Agreement provided with the
|
---|
13 | ** Software or, alternatively, in accordance with the terms contained in a
|
---|
14 | ** written agreement between you and Nokia.
|
---|
15 | **
|
---|
16 | ** GNU Free Documentation License
|
---|
17 | ** Alternatively, this file may be used under the terms of the GNU Free
|
---|
18 | ** Documentation License version 1.3 as published by the Free Software
|
---|
19 | ** Foundation and appearing in the file included in the packaging of this
|
---|
20 | ** file.
|
---|
21 | **
|
---|
22 | ** If you have questions regarding the use of this file, please contact
|
---|
23 | ** Nokia at [email protected].
|
---|
24 | ** $QT_END_LICENSE$
|
---|
25 | **
|
---|
26 | ****************************************************************************/
|
---|
27 |
|
---|
28 | /*!
|
---|
29 | \group database
|
---|
30 | \title Database Classes
|
---|
31 |
|
---|
32 | \brief Database related classes, e.g. for SQL databases.
|
---|
33 | */
|
---|
34 |
|
---|
35 | /*!
|
---|
36 | \page sql-programming.html
|
---|
37 | \title SQL Programming
|
---|
38 | \ingroup qt-sql
|
---|
39 | \nextpage Connecting to Databases
|
---|
40 |
|
---|
41 | \brief Database integration for Qt applications.
|
---|
42 |
|
---|
43 | This overview assumes that you have at least a basic knowledge of
|
---|
44 | SQL. You should be able to understand simple \c SELECT, \c
|
---|
45 | INSERT, \c UPDATE, and \c DELETE statements. Although the \l
|
---|
46 | QSqlTableModel class provides an interface to database browsing
|
---|
47 | and editing that does not require a knowledge of SQL, a basic
|
---|
48 | understanding of SQL is highly recommended. A standard text
|
---|
49 | covering SQL databases is \e {An Introduction to Database Systems}
|
---|
50 | (7th Ed.) by C. J. Date, ISBN 0201385902.
|
---|
51 |
|
---|
52 | \section1 Topics:
|
---|
53 |
|
---|
54 | \list
|
---|
55 | \o \l{Database Classes}
|
---|
56 | \o \l{Connecting to Databases}
|
---|
57 | \list
|
---|
58 | \o \l{SQL Database Drivers}
|
---|
59 | \endlist
|
---|
60 | \o \l{Executing SQL Statements}
|
---|
61 | \list
|
---|
62 | \o \l{Data Types for Qt-supported Database Systems}
|
---|
63 | \endlist
|
---|
64 | \o \l{Using the SQL Model Classes}
|
---|
65 | \o \l{Presenting Data in a Table View}
|
---|
66 | \o \l{Creating Data-Aware Forms}
|
---|
67 | \endlist
|
---|
68 |
|
---|
69 | \section1 Database Classes
|
---|
70 |
|
---|
71 | These classes provide access to SQL databases.
|
---|
72 |
|
---|
73 | \annotatedlist database
|
---|
74 |
|
---|
75 | The SQL classes are divided into three layers:
|
---|
76 |
|
---|
77 | \section2 Driver Layer
|
---|
78 |
|
---|
79 | This comprises the classes QSqlDriver, QSqlDriverCreator<T>,
|
---|
80 | QSqlDriverCreatorBase, QSqlDriverPlugin, and QSqlResult.
|
---|
81 |
|
---|
82 | This layer provides the low-level bridge between the specific databases
|
---|
83 | and the SQL API layer. See \l{SQL Database Drivers} for more information.
|
---|
84 |
|
---|
85 | \section2 SQL API Layer
|
---|
86 |
|
---|
87 | These classes provide access to databases. Connections
|
---|
88 | are made using the QSqlDatabase class. Database
|
---|
89 | interaction is achieved by using the QSqlQuery class.
|
---|
90 | In addition to QSqlDatabase and QSqlQuery, the SQL API
|
---|
91 | layer is supported by QSqlError, QSqlField, QSqlIndex,
|
---|
92 | and QSqlRecord.
|
---|
93 |
|
---|
94 | \section2 User Interface Layer
|
---|
95 |
|
---|
96 | These classes link the data from a database to data-aware widgets.
|
---|
97 | They include QSqlQueryModel, QSqlTableModel, and QSqlRelationalTableModel.
|
---|
98 | These classes are designed to work with Qt's
|
---|
99 | \l{Model/View Programming}{model/view framework}.
|
---|
100 |
|
---|
101 | Note that to use any of these classes, a QCoreApplication object
|
---|
102 | must have been instantiated first.
|
---|
103 | */
|
---|
104 |
|
---|
105 | /*!
|
---|
106 | \page sql-connecting.html
|
---|
107 | \title Connecting to Databases
|
---|
108 | \ingroup qt-sql
|
---|
109 |
|
---|
110 | \contentspage SQL Programming
|
---|
111 | \previouspage SQL Programming
|
---|
112 | \nextpage Executing SQL Statements
|
---|
113 |
|
---|
114 | To access a database with QSqlQuery or QSqlQueryModel, create and
|
---|
115 | open one or more database connections. Database connections are
|
---|
116 | normally identified by connection name, \e{not} by database name.
|
---|
117 | You can have multiple connections to the same database.
|
---|
118 | QSqlDatabase also supports the concept of a \e{default}
|
---|
119 | connection, which is an unnamed connection. When calling QSqlQuery
|
---|
120 | or QSqlQueryModel member functions that take a connection name
|
---|
121 | argument, if you don't pass a connection name, the default
|
---|
122 | connection will be used. Creating a default connection is
|
---|
123 | convenient when your application only requires one database
|
---|
124 | connection.
|
---|
125 |
|
---|
126 | Note the difference between creating a connection and opening it.
|
---|
127 | Creating a connection involves creating an instance of class
|
---|
128 | QSqlDatabase. The connection is not usable until it is opened. The
|
---|
129 | following snippet shows how to create a \e{default} connection
|
---|
130 | and then open it:
|
---|
131 |
|
---|
132 | \snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 26
|
---|
133 |
|
---|
134 | The first line creates the connection object, and the last line
|
---|
135 | opens it for use. In between, we initialize some connection
|
---|
136 | information, including the \l{QSqlDatabase::setDatabaseName()}
|
---|
137 | {database name}, the \l{QSqlDatabase::setHostName()} {host name},
|
---|
138 | the \l{QSqlDatabase::setUserName()} {user name}, and the
|
---|
139 | \l{QSqlDatabase::setPassword()} {password}. In this case, we are
|
---|
140 | connecting to the MySQL database \c{flightdb} on the host
|
---|
141 | \c{bigblue}. The \c{"QMYSQL"} argument to
|
---|
142 | \l{QSqlDatabase::addDatabase()} {addDatabase()} specifies the type
|
---|
143 | of database driver to use for the connection. The set of database
|
---|
144 | drivers included with Qt are shown in the table of \l{SQL Database
|
---|
145 | Drivers#Supported Databases} {supported database drivers}.
|
---|
146 |
|
---|
147 | The connection in the snippet will be the \e{default} connection,
|
---|
148 | because we don't pass the second argument to
|
---|
149 | \l{QSqlDatabase::addDatabase()} {addDatabase()}, which is the
|
---|
150 | connection name. For example, here we establish two MySQL database
|
---|
151 | connections named \c{"first"} and \c{"second"}:
|
---|
152 |
|
---|
153 | \snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 27
|
---|
154 |
|
---|
155 | After these connections have been initialized, \l{QSqlDatabase::}
|
---|
156 | {open()} for each one to establish the live connections. If the
|
---|
157 | \l{QSqlDatabase::} {open()} fails, it returns false. In that case,
|
---|
158 | call QSqlDatabase::lastError() to get error information.
|
---|
159 |
|
---|
160 | Once a connection is established, we can call the static function
|
---|
161 | QSqlDatabase::database() from anywhere with a connection name to
|
---|
162 | get a pointer to that database connection. If we don't pass a
|
---|
163 | connection name, it will return the default connection. For
|
---|
164 | example:
|
---|
165 |
|
---|
166 | \snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 28
|
---|
167 | \snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 29
|
---|
168 | \snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 30
|
---|
169 |
|
---|
170 | To remove a database connection, first close the database using
|
---|
171 | QSqlDatabase::close(), then remove it using the static method
|
---|
172 | QSqlDatabase::removeDatabase().
|
---|
173 | */
|
---|
174 |
|
---|
175 | /*!
|
---|
176 | \page sql-sqlstatements.html
|
---|
177 | \title Executing SQL Statements
|
---|
178 | \ingroup qt-sql
|
---|
179 |
|
---|
180 | \previouspage Connecting to Databases
|
---|
181 | \contentspage SQL Programming
|
---|
182 | \nextpage Using the SQL Model Classes
|
---|
183 |
|
---|
184 |
|
---|
185 | The QSqlQuery class provides an interface for executing SQL
|
---|
186 | statements and navigating through the result set of a query.
|
---|
187 |
|
---|
188 | The QSqlQueryModel and QSqlTableModel classes described in the
|
---|
189 | next section provide a higher-level interface for accessing
|
---|
190 | databases. If you are unfamiliar with SQL, you might want to skip
|
---|
191 | directly to the next section (\l{Using the SQL Model Classes}).
|
---|
192 |
|
---|
193 | \section2 Executing a Query
|
---|
194 |
|
---|
195 | To execute an SQL statement, simply create a QSqlQuery object and
|
---|
196 | call QSqlQuery::exec() like this:
|
---|
197 |
|
---|
198 | \snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 31
|
---|
199 |
|
---|
200 | The QSqlQuery constructor accepts an optional QSqlDatabase object
|
---|
201 | that specifies which database connection to use. In the example
|
---|
202 | above, we don't specify any connection, so the default connection
|
---|
203 | is used.
|
---|
204 |
|
---|
205 | If an error occurs, \l{QSqlQuery::exec()}{exec()} returns false.
|
---|
206 | The error is then available as QSqlQuery::lastError().
|
---|
207 |
|
---|
208 | \section2 Navigating the Result Set
|
---|
209 |
|
---|
210 | QSqlQuery provides access to the result set one record at a time.
|
---|
211 | After the call to \l{QSqlQuery::exec()}{exec()}, QSqlQuery's
|
---|
212 | internal pointer is located one position \e{before} the first
|
---|
213 | record. We must call QSqlQuery::next() once to advance to the
|
---|
214 | first record, then \l{QSqlQuery::next()}{next()} again repeatedly
|
---|
215 | to access the other records, until it returns false. Here's a
|
---|
216 | typical loop that iterates over all the records in order:
|
---|
217 |
|
---|
218 | \snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 32
|
---|
219 |
|
---|
220 | The QSqlQuery::value() function returns the value of a field in
|
---|
221 | the current record. Fields are specified as zero-based indexes.
|
---|
222 | QSqlQuery::value() returns a QVariant, a type that can hold
|
---|
223 | various C++ and core Qt data types such as \c int, QString, and
|
---|
224 | QByteArray. The different database types are automatically mapped
|
---|
225 | into the closest Qt equivalent. In the code snippet, we call
|
---|
226 | QVariant::toString() and QVariant::toInt() to convert
|
---|
227 | variants to QString and \c int.
|
---|
228 |
|
---|
229 | For an overview of the recommended types for use with Qt-supported
|
---|
230 | Databases, please refer to \l{Data Types for Qt-supported Database
|
---|
231 | Systems} {this table}.
|
---|
232 |
|
---|
233 | You can iterate back and forth using QSqlQuery::next(),
|
---|
234 | QSqlQuery::previous(), QSqlQuery::first(), QSqlQuery::last(), and
|
---|
235 | QSqlQuery::seek(). The current row index is returned by
|
---|
236 | QSqlQuery::at(), and the total number of rows in the result set
|
---|
237 | is avaliable as QSqlQuery::size() for databases that support it.
|
---|
238 |
|
---|
239 | To determine whether a database driver supports a given feature,
|
---|
240 | use QSqlDriver::hasFeature(). In the following example, we call
|
---|
241 | QSqlQuery::size() to determine the size of a result set of
|
---|
242 | the underlying database supports that feature; otherwise, we
|
---|
243 | navigate to the last record and use the query's position to tell
|
---|
244 | us how many records there are.
|
---|
245 |
|
---|
246 | \snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 33
|
---|
247 |
|
---|
248 | If you iterate through a result set only using next() and seek()
|
---|
249 | with positive values, you can call
|
---|
250 | QSqlQuery::setForwardOnly(true) before calling exec(). This is an
|
---|
251 | easy optimization that will speed up the query significantly when
|
---|
252 | operating on large result sets.
|
---|
253 |
|
---|
254 | \section2 Inserting, Updating, and Deleting Records
|
---|
255 |
|
---|
256 | QSqlQuery can execute arbitrary SQL statements, not just
|
---|
257 | \c{SELECT}s. The following example inserts a record into a table
|
---|
258 | using \c{INSERT}:
|
---|
259 |
|
---|
260 | \snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 34
|
---|
261 |
|
---|
262 | If you want to insert many records at the same time, it is often
|
---|
263 | more efficient to separate the query from the actual values being
|
---|
264 | inserted. This can be done using placeholders. Qt supports two
|
---|
265 | placeholder syntaxes: named binding and positional binding.
|
---|
266 | Here's an example of named binding:
|
---|
267 |
|
---|
268 | \snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 35
|
---|
269 |
|
---|
270 | Here's an example of positional binding:
|
---|
271 |
|
---|
272 | \snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 36
|
---|
273 |
|
---|
274 | Both syntaxes work with all database drivers provided by Qt. If
|
---|
275 | the database supports the syntax natively, Qt simply forwards the
|
---|
276 | query to the DBMS; otherwise, Qt simulates the placeholder syntax
|
---|
277 | by preprocessing the query. The actual query that ends up being
|
---|
278 | executed by the DBMS is available as QSqlQuery::executedQuery().
|
---|
279 |
|
---|
280 | When inserting multiple records, you only need to call
|
---|
281 | QSqlQuery::prepare() once. Then you call
|
---|
282 | \l{QSqlQuery::bindValue()}{bindValue()} or
|
---|
283 | \l{QSqlQuery::addBindValue()}{addBindValue()} followed by
|
---|
284 | \l{QSqlQuery::exec()}{exec()} as many times as necessary.
|
---|
285 |
|
---|
286 | Besides performance, one advantage of placeholders is that you
|
---|
287 | can easily specify arbitrary values without having to worry about
|
---|
288 | escaping special characters.
|
---|
289 |
|
---|
290 | Updating a record is similar to inserting it into a table:
|
---|
291 |
|
---|
292 | \snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 37
|
---|
293 |
|
---|
294 | You can also use named or positional binding to associate
|
---|
295 | parameters to actual values.
|
---|
296 |
|
---|
297 | Finally, here's an example of a \c DELETE statement:
|
---|
298 |
|
---|
299 | \snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 38
|
---|
300 |
|
---|
301 | \section2 Transactions
|
---|
302 |
|
---|
303 | If the underlying database engine supports transactions,
|
---|
304 | QSqlDriver::hasFeature(QSqlDriver::Transactions) will return
|
---|
305 | true. You can use QSqlDatabase::transaction() to initiate a
|
---|
306 | transaction, followed by the SQL commands you want to execute
|
---|
307 | within the context of the transaction, and then either
|
---|
308 | QSqlDatabase::commit() or QSqlDatabase::rollback(). When
|
---|
309 | using transactions you must start the transaction before you
|
---|
310 | create your query.
|
---|
311 |
|
---|
312 | Example:
|
---|
313 |
|
---|
314 | \snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 39
|
---|
315 |
|
---|
316 | Transactions can be used to ensure that a complex operation is
|
---|
317 | atomic (for example, looking up a foreign key and creating a
|
---|
318 | record), or to provide a means of canceling a complex change in
|
---|
319 | the middle.
|
---|
320 |
|
---|
321 | \omit
|
---|
322 | It would be useful to mention transactions, and the fact that
|
---|
323 | some databases don't support them.
|
---|
324 | \endomit
|
---|
325 | */
|
---|
326 |
|
---|
327 | /*!
|
---|
328 | \page sql-model.html
|
---|
329 | \title Using the SQL Model Classes
|
---|
330 | \ingroup qt-sql
|
---|
331 |
|
---|
332 | \previouspage Executing SQL Statements
|
---|
333 | \contentspage SQL Programming
|
---|
334 | \nextpage Presenting Data in a Table View
|
---|
335 |
|
---|
336 | In addition to QSqlQuery, Qt offers three higher-level classes
|
---|
337 | for accessing databases. These classes are QSqlQueryModel,
|
---|
338 | QSqlTableModel, and QSqlRelationalTableModel.
|
---|
339 |
|
---|
340 | \table
|
---|
341 | \row \o QSqlQueryModel
|
---|
342 | \o A read-only model based on an arbitrary SQL query.
|
---|
343 | \row \o QSqlTableModel
|
---|
344 | \o A read-write model that works on a single table.
|
---|
345 | \row \o QSqlRelationalTableModel
|
---|
346 | \o A QSqlTableModel subclass with foreign key support.
|
---|
347 | \endtable
|
---|
348 |
|
---|
349 | These classes derive from QAbstractTableModel (which in turn
|
---|
350 | inherits from QAbstractItemModel) and make it easy to present
|
---|
351 | data from a database in an item view class such as QListView and
|
---|
352 | QTableView. This is explained in detail in the \l{Presenting Data
|
---|
353 | in a Table View} section.
|
---|
354 |
|
---|
355 | Another advantage of using these classes is that it can make your
|
---|
356 | code easier to adapt to other data sources. For example, if you
|
---|
357 | use QSqlTableModel and later decide to use XML files to store
|
---|
358 | data instead of a database, it is essentially just a matter of
|
---|
359 | replacing one data model with another.
|
---|
360 |
|
---|
361 | \section2 The SQL Query Model
|
---|
362 |
|
---|
363 | QSqlQueryModel offers a read-only model based on an SQL query.
|
---|
364 |
|
---|
365 | Example:
|
---|
366 |
|
---|
367 | \snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 40
|
---|
368 |
|
---|
369 | After setting the query using QSqlQueryModel::setQuery(), you can
|
---|
370 | use QSqlQueryModel::record(int) to access the individual records.
|
---|
371 | You can also use QSqlQueryModel::data() and any of the other
|
---|
372 | functions inherited from QAbstractItemModel.
|
---|
373 |
|
---|
374 | There's also a \l{QSqlQueryModel::setQuery()}{setQuery()}
|
---|
375 | overload that takes a QSqlQuery object and operates on its result
|
---|
376 | set. This enables you to use any features of QSqlQuery to set up
|
---|
377 | the query (e.g., prepared queries).
|
---|
378 |
|
---|
379 | \section2 The SQL Table Model
|
---|
380 |
|
---|
381 | QSqlTableModel offers a read-write model that works on a single
|
---|
382 | SQL table at a time.
|
---|
383 |
|
---|
384 | Example:
|
---|
385 |
|
---|
386 | \snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 41
|
---|
387 |
|
---|
388 | QSqlTableModel is a high-level alternative to QSqlQuery for
|
---|
389 | navigating and modifying individual SQL tables. It typically
|
---|
390 | results in less code and requires no knowledge of SQL syntax.
|
---|
391 |
|
---|
392 | Use QSqlTableModel::record() to retrieve a row in the table, and
|
---|
393 | QSqlTableModel::setRecord() to modify the row. For example, the
|
---|
394 | following code will increase every employee's salary by 10 per
|
---|
395 | cent:
|
---|
396 |
|
---|
397 | \snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 42
|
---|
398 |
|
---|
399 | You can also use QSqlTableModel::data() and
|
---|
400 | QSqlTableModel::setData(), which are inherited from
|
---|
401 | QAbstractItemModel, to access the data. For example, here's how
|
---|
402 | to update a record using
|
---|
403 | \l{QSqlTableModel::setData()}{setData()}:
|
---|
404 |
|
---|
405 | \snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 43
|
---|
406 |
|
---|
407 | Here's how to insert a row and populate it:
|
---|
408 |
|
---|
409 | \snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 44
|
---|
410 |
|
---|
411 | Here's how to delete five consecutive rows:
|
---|
412 |
|
---|
413 | \snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 45
|
---|
414 | \snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 46
|
---|
415 |
|
---|
416 | The first argument to QSqlTableModel::removeRows() is the index
|
---|
417 | of the first row to delete.
|
---|
418 |
|
---|
419 | When you're finished changing a record, you should always call
|
---|
420 | QSqlTableModel::submitAll() to ensure that the changes are
|
---|
421 | written to the database.
|
---|
422 |
|
---|
423 | When and whether you actually \e need to call submitAll() depends
|
---|
424 | on the table's \l{QSqlTableModel::editStrategy()}{edit strategy}.
|
---|
425 | The default strategy is QSqlTableModel::OnRowChange, which
|
---|
426 | specifies that pending changes are applied to the database when
|
---|
427 | the user selects a different row. Other strategies are
|
---|
428 | QSqlTableModel::OnManualSubmit (where all changes are cached in
|
---|
429 | the model until you call submitAll()) and
|
---|
430 | QSqlTableModel::OnFieldChange (where no changes are cached).
|
---|
431 | These are mostly useful when QSqlTableModel is used with a view.
|
---|
432 |
|
---|
433 | QSqlTableModel::OnFieldChange seems to deliver the promise that
|
---|
434 | you never need to call submitAll() explicitly. There are two
|
---|
435 | pitfalls, though:
|
---|
436 |
|
---|
437 | \list
|
---|
438 | \o Without any caching, performance may drop significantly.
|
---|
439 | \o If you modify a primary key, the record might slip through
|
---|
440 | your fingers while you are trying to populate it.
|
---|
441 | \endlist
|
---|
442 |
|
---|
443 | \section2 The SQL Relational Table Model
|
---|
444 |
|
---|
445 | QSqlRelationalTableModel extends QSqlTableModel to provide
|
---|
446 | support for foreign keys. A foreign key is a 1-to-1 mapping
|
---|
447 | between a field in one table and the primary key field of another
|
---|
448 | table. For example, if a \c book table has a field called \c
|
---|
449 | authorid that refers to the author table's \c id field, we say
|
---|
450 | that \c authorid is a foreign key.
|
---|
451 |
|
---|
452 | \table
|
---|
453 | \row \o \inlineimage noforeignkeys.png
|
---|
454 | \o \inlineimage foreignkeys.png
|
---|
455 | \endtable
|
---|
456 |
|
---|
457 | The screenshot on the left shows a plain QSqlTableModel in a
|
---|
458 | QTableView. Foreign keys (\c city and \c country) aren't resolved
|
---|
459 | to human-readable values. The screenshot on the right shows a
|
---|
460 | QSqlRelationalTableModel, with foreign keys resolved into
|
---|
461 | human-readable text strings.
|
---|
462 |
|
---|
463 | The following code snippet shows how the QSqlRelationalTableModel
|
---|
464 | was set up:
|
---|
465 |
|
---|
466 | \snippet examples/sql/relationaltablemodel/relationaltablemodel.cpp 0
|
---|
467 | \codeline
|
---|
468 | \snippet examples/sql/relationaltablemodel/relationaltablemodel.cpp 1
|
---|
469 | \snippet examples/sql/relationaltablemodel/relationaltablemodel.cpp 2
|
---|
470 |
|
---|
471 | See the QSqlRelationalTableModel documentation for details.
|
---|
472 | */
|
---|
473 |
|
---|
474 | /*!
|
---|
475 | \page sql-presenting.html
|
---|
476 | \title Presenting Data in a Table View
|
---|
477 | \ingroup qt-sql
|
---|
478 |
|
---|
479 | \previouspage Using the SQL Model Classes
|
---|
480 | \contentspage SQL Programming
|
---|
481 | \nextpage Creating Data-Aware Forms
|
---|
482 |
|
---|
483 | The QSqlQueryModel, QSqlTableModel, and QSqlRelationalTableModel
|
---|
484 | classes can be used as a data source for Qt's view classes such
|
---|
485 | as QListView, QTableView, and QTreeView. In practice, QTableView
|
---|
486 | is by far the most common choice, because an SQL result set is
|
---|
487 | essentially a two-dimensional data structure.
|
---|
488 |
|
---|
489 | \image relationaltable.png A table view displaying a QSqlTableModel
|
---|
490 |
|
---|
491 | The following example creates a view based on an SQL data model:
|
---|
492 |
|
---|
493 | \snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 17
|
---|
494 | \snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 18
|
---|
495 | \snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 19
|
---|
496 |
|
---|
497 | If the model is a read-write model (e.g., QSqlTableModel), the
|
---|
498 | view lets the user edit the fields. You can disable this by
|
---|
499 | calling
|
---|
500 |
|
---|
501 | \snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 20
|
---|
502 |
|
---|
503 | You can use the same model as a data source for multiple views.
|
---|
504 | If the user edits the model through one of the views, the other
|
---|
505 | views will reflect the changes immediately. The
|
---|
506 | \l{sql/tablemodel}{Table Model} example shows how it works.
|
---|
507 |
|
---|
508 | View classes display a header at the top to label the columns. To
|
---|
509 | change the header texts, call
|
---|
510 | \l{QAbstractItemModel::setHeaderData()}{setHeaderData()} on the
|
---|
511 | model. The header's labels default to the table's field names.
|
---|
512 | For example:
|
---|
513 |
|
---|
514 | \snippet examples/sql/relationaltablemodel/relationaltablemodel.cpp 3
|
---|
515 |
|
---|
516 | QTableView also has a vertical header on the left with numbers
|
---|
517 | identifying the rows. If you insert rows programmatically using
|
---|
518 | QSqlTableModel::insertRows(), the new rows will be marked with an
|
---|
519 | asterisk (*) until they are submitted using
|
---|
520 | \l{QSqlTableModel::submitAll()}{submitAll()} or automatically
|
---|
521 | when the user moves to another record (assuming the
|
---|
522 | \l{QSqlTableModel::EditStrategy}{edit strategy} is
|
---|
523 | QSqlTableModel::OnRowChange).
|
---|
524 |
|
---|
525 | \image insertrowinmodelview.png Inserting a row in a model
|
---|
526 |
|
---|
527 | Likewise, if you remove rows using
|
---|
528 | \l{QSqlTableModel::removeRows()}{removeRows()}, the rows will be
|
---|
529 | marked with an exclamation mark (!) until the change is
|
---|
530 | submitted.
|
---|
531 |
|
---|
532 | The items in the view are rendered using a delegate. The default
|
---|
533 | delegate, QItemDelegate, handles the most common data types (\c
|
---|
534 | int, QString, QImage, etc.). The delegate is also responsible for
|
---|
535 | providing editor widgets (e.g., a combobox) when the user starts
|
---|
536 | editing an item in the view. You can create your own delegates by
|
---|
537 | subclassing QAbstractItemDelegate or QItemDelegate. See
|
---|
538 | \l{Model/View Programming} for more information.
|
---|
539 |
|
---|
540 | QSqlTableModel is optimized to operate on a single table at a
|
---|
541 | time. If you need a read-write model that operates on an
|
---|
542 | arbitrary result set, you can subclass QSqlQueryModel and
|
---|
543 | reimplement \l{QAbstractItemModel::flags()}{flags()} and
|
---|
544 | \l{QAbstractItemModel::setData()}{setData()} to make it
|
---|
545 | read-write. The following two functions make fields 1 and 2 of a
|
---|
546 | query model editable:
|
---|
547 |
|
---|
548 | \snippet examples/sql/querymodel/editablesqlmodel.cpp 0
|
---|
549 | \codeline
|
---|
550 | \snippet examples/sql/querymodel/editablesqlmodel.cpp 1
|
---|
551 |
|
---|
552 | The setFirstName() helper function is defined as follows:
|
---|
553 |
|
---|
554 | \snippet examples/sql/querymodel/editablesqlmodel.cpp 2
|
---|
555 |
|
---|
556 | The setLastName() function is similar. See the
|
---|
557 | \l{sql/querymodel}{Query Model} example for the complete source code.
|
---|
558 |
|
---|
559 | Subclassing a model makes it possible to customize it in many
|
---|
560 | ways: You can provide tooltips for the items, change the
|
---|
561 | background color, provide calculated values, provide different
|
---|
562 | values for viewing and editing, handle null values specially, and
|
---|
563 | more. See \l{Model/View Programming} as well as the \l
|
---|
564 | QAbstractItemView reference documentation for details.
|
---|
565 |
|
---|
566 | If all you need is to resolve a foreign key to a more
|
---|
567 | human-friendly string, you can use QSqlRelationalTableModel. For
|
---|
568 | best results, you should also use QSqlRelationalDelegate, a
|
---|
569 | delegate that provides combobox editors for editing foreign keys.
|
---|
570 |
|
---|
571 | \image relationaltable.png Editing a foreign key in a relational table
|
---|
572 |
|
---|
573 | The \l{sql/relationaltablemodel}{Relational Table Model} example
|
---|
574 | illustrates how to use QSqlRelationalTableModel in conjunction with
|
---|
575 | QSqlRelationalDelegate to provide tables with foreign key
|
---|
576 | support.
|
---|
577 | */
|
---|
578 |
|
---|
579 | /*!
|
---|
580 | \page sql-forms.html
|
---|
581 | \title Creating Data-Aware Forms
|
---|
582 | \ingroup qt-sql
|
---|
583 |
|
---|
584 | \previouspage Presenting Data in a Table View
|
---|
585 | \contentspage SQL Programming
|
---|
586 |
|
---|
587 | Using the SQL models described above, the contents of a database can
|
---|
588 | be presented to other model/view components. For some applications,
|
---|
589 | it is sufficient to present this data using a standard item view,
|
---|
590 | such as QTableView. However, users of record-based applications often
|
---|
591 | require a form-based user interface in which data from a specific
|
---|
592 | row or column in a database table is used to populate editor widgets
|
---|
593 | on a form.
|
---|
594 |
|
---|
595 | Such data-aware forms can be created with the QDataWidgetMapper class,
|
---|
596 | a generic model/view component that is used to map data from a model
|
---|
597 | to specific widgets in a user interface.
|
---|
598 |
|
---|
599 | QDataWidgetMapper operates on a specific database table, mapping items
|
---|
600 | in the table on a row-by-row or column-by-column basis. As a result,
|
---|
601 | using QDataWidgetMapper with a SQL model is as simple as using it with
|
---|
602 | any other table model.
|
---|
603 |
|
---|
604 | \image qdatawidgetmapper-simple.png
|
---|
605 |
|
---|
606 | The \l{demos/books}{Books} demonstration shows how information can
|
---|
607 | be presented for easy access by using QDataWidgetMapper and a set of
|
---|
608 | simple input widgets.
|
---|
609 | */
|
---|