1 /****************************************************************************
3 ** Copyright (C) 2011 Nokia Corporation and/or its subsidiary(-ies).
4 ** All rights reserved.
5 ** Contact: Nokia Corporation (qt-info@nokia.com)
7 ** This file is part of the documentation of the Qt Toolkit.
9 ** $QT_BEGIN_LICENSE:FDL$
10 ** GNU Free Documentation License
11 ** Alternatively, this file may be used under the terms of the GNU Free
12 ** Documentation License version 1.3 as published by the Free Software
13 ** Foundation and appearing in the file included in the packaging of
17 ** Alternatively, this file may be used in accordance with the terms
18 ** and conditions contained in a signed written agreement between you
26 ****************************************************************************/
30 \title Database Classes
32 \brief Database related classes, e.g. for SQL databases.
36 \page sql-programming.html
37 \title SQL Programming
39 \nextpage Connecting to Databases
41 \brief Database integration for Qt applications.
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.
55 \o \l{Database Classes}
56 \o \l{Connecting to Databases}
58 \o \l{SQL Database Drivers}
60 \o \l{Executing SQL Statements}
62 \o \l{Data Types for Qt-supported Database Systems}
64 \o \l{Using the SQL Model Classes}
65 \o \l{Presenting Data in a Table View}
66 \o \l{Creating Data-Aware Forms}
69 \section1 Database Classes
71 These classes provide access to SQL databases.
73 \annotatedlist database
75 The SQL classes are divided into three layers:
77 \section2 Driver Layer
79 This comprises the classes QSqlDriver, QSqlDriverCreator<T>,
80 QSqlDriverCreatorBase, QSqlDriverPlugin, and QSqlResult.
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.
85 \section2 SQL API Layer
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,
94 \section2 User Interface Layer
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}.
101 Note that to use any of these classes, a QCoreApplication object
102 must have been instantiated first.
106 \page sql-connecting.html
107 \title Connecting to Databases
110 \contentspage SQL Programming
111 \previouspage SQL Programming
112 \nextpage Executing SQL Statements
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
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
132 \snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 26
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}.
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"}:
153 \snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 27
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.
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
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
170 To remove a database connection, first close the database using
171 QSqlDatabase::close(), then remove it using the static method
172 QSqlDatabase::removeDatabase().
176 \page sql-sqlstatements.html
177 \title Executing SQL Statements
180 \previouspage Connecting to Databases
181 \contentspage SQL Programming
182 \nextpage Using the SQL Model Classes
185 The QSqlQuery class provides an interface for executing SQL
186 statements and navigating through the result set of a query.
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}).
193 \section2 Executing a Query
195 To execute an SQL statement, simply create a QSqlQuery object and
196 call QSqlQuery::exec() like this:
198 \snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 31
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
205 If an error occurs, \l{QSqlQuery::exec()}{exec()} returns false.
206 The error is then available as QSqlQuery::lastError().
208 \section2 Navigating the Result Set
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:
218 \snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 32
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.
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}.
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.
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.
246 \snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 33
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.
254 \section2 Inserting, Updating, and Deleting Records
256 QSqlQuery can execute arbitrary SQL statements, not just
257 \c{SELECT}s. The following example inserts a record into a table
260 \snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 34
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:
268 \snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 35
270 Here's an example of positional binding:
272 \snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 36
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().
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.
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.
290 Updating a record is similar to inserting it into a table:
292 \snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 37
294 You can also use named or positional binding to associate
295 parameters to actual values.
297 Finally, here's an example of a \c DELETE statement:
299 \snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 38
301 \section2 Transactions
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
314 \snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 39
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
322 It would be useful to mention transactions, and the fact that
323 some databases don't support them.
329 \title Using the SQL Model Classes
332 \previouspage Executing SQL Statements
333 \contentspage SQL Programming
334 \nextpage Presenting Data in a Table View
336 In addition to QSqlQuery, Qt offers three higher-level classes
337 for accessing databases. These classes are QSqlQueryModel,
338 QSqlTableModel, and QSqlRelationalTableModel.
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.
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.
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.
361 \section2 The SQL Query Model
363 QSqlQueryModel offers a read-only model based on an SQL query.
367 \snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 40
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.
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).
379 \section2 The SQL Table Model
381 QSqlTableModel offers a read-write model that works on a single
386 \snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 41
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.
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
397 \snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 42
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()}:
405 \snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 43
407 Here's how to insert a row and populate it:
409 \snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 44
411 Here's how to delete five consecutive rows:
413 \snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 45
414 \snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 46
416 The first argument to QSqlTableModel::removeRows() is the index
417 of the first row to delete.
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.
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.
433 QSqlTableModel::OnFieldChange seems to deliver the promise that
434 you never need to call submitAll() explicitly. There are two
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.
443 \section2 The SQL Relational Table Model
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.
453 \row \o \inlineimage noforeignkeys.png
454 \o \inlineimage foreignkeys.png
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.
463 The following code snippet shows how the QSqlRelationalTableModel
466 \snippet examples/sql/relationaltablemodel/relationaltablemodel.cpp 0
468 \snippet examples/sql/relationaltablemodel/relationaltablemodel.cpp 1
469 \snippet examples/sql/relationaltablemodel/relationaltablemodel.cpp 2
471 See the QSqlRelationalTableModel documentation for details.
475 \page sql-presenting.html
476 \title Presenting Data in a Table View
479 \previouspage Using the SQL Model Classes
480 \contentspage SQL Programming
481 \nextpage Creating Data-Aware Forms
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.
489 \image relationaltable.png A table view displaying a QSqlTableModel
491 The following example creates a view based on an SQL data model:
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
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
501 \snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 20
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.
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.
514 \snippet examples/sql/relationaltablemodel/relationaltablemodel.cpp 3
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).
525 \image insertrowinmodelview.png Inserting a row in a model
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
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.
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:
548 \snippet examples/sql/querymodel/editablesqlmodel.cpp 0
550 \snippet examples/sql/querymodel/editablesqlmodel.cpp 1
552 The setFirstName() helper function is defined as follows:
554 \snippet examples/sql/querymodel/editablesqlmodel.cpp 2
556 The setLastName() function is similar. See the
557 \l{sql/querymodel}{Query Model} example for the complete source code.
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.
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.
571 \image relationaltable.png Editing a foreign key in a relational table
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
581 \title Creating Data-Aware Forms
584 \previouspage Presenting Data in a Table View
585 \contentspage SQL Programming
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
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.
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.
604 \image qdatawidgetmapper-simple.png
606 The \l{sql/books}{Books} example shows how information can
607 be presented for easy access by using QDataWidgetMapper and a set of
608 simple input widgets.