source: trunk/doc/src/snippets/sqldatabase/sqldatabase.cpp@ 5

Last change on this file since 5 was 2, checked in by Dmitry A. Kuminov, 16 years ago

Initially imported qt-all-opensource-src-4.5.1 from Trolltech.

File size: 13.2 KB
Line 
1/****************************************************************************
2**
3** Copyright (C) 2009 Nokia Corporation and/or its subsidiary(-ies).
4** Contact: Qt Software Information ([email protected])
5**
6** This file is part of the documentation of the Qt Toolkit.
7**
8** $QT_BEGIN_LICENSE:LGPL$
9** Commercial Usage
10** Licensees holding valid Qt Commercial licenses may use this file in
11** accordance with the Qt Commercial License Agreement provided with the
12** Software or, alternatively, in accordance with the terms contained in
13** a written agreement between you and Nokia.
14**
15** GNU Lesser General Public License Usage
16** Alternatively, this file may be used under the terms of the GNU Lesser
17** General Public License version 2.1 as published by the Free Software
18** Foundation and appearing in the file LICENSE.LGPL included in the
19** packaging of this file. Please review the following information to
20** ensure the GNU Lesser General Public License version 2.1 requirements
21** will be met: http://www.gnu.org/licenses/old-licenses/lgpl-2.1.html.
22**
23** In addition, as a special exception, Nokia gives you certain
24** additional rights. These rights are described in the Nokia Qt LGPL
25** Exception version 1.0, included in the file LGPL_EXCEPTION.txt in this
26** package.
27**
28** GNU General Public License Usage
29** Alternatively, this file may be used under the terms of the GNU
30** General Public License version 3.0 as published by the Free Software
31** Foundation and appearing in the file LICENSE.GPL included in the
32** packaging of this file. Please review the following information to
33** ensure the GNU General Public License version 3.0 requirements will be
34** met: http://www.gnu.org/copyleft/gpl.html.
35**
36** If you are unsure which license is appropriate for your use, please
37** contact the sales department at [email protected].
38** $QT_END_LICENSE$
39**
40****************************************************************************/
41
42#include <QtGui>
43#include <QtSql>
44
45#include <iostream>
46
47using namespace std;
48
49QString tr(const char *text)
50{
51 return QApplication::translate(text, text);
52}
53
54void QSqlDatabase_snippets()
55{
56 {
57//! [0]
58 QSqlDatabase db = QSqlDatabase::addDatabase("QPSQL");
59 db.setHostName("acidalia");
60 db.setDatabaseName("customdb");
61 db.setUserName("mojito");
62 db.setPassword("J0a1m8");
63 bool ok = db.open();
64//! [0]
65 Q_UNUSED(ok);
66 }
67
68 {
69//! [1]
70 QSqlDatabase db = QSqlDatabase::database();
71//! [1]
72 }
73}
74
75void QSqlField_snippets()
76{
77#if 0
78 {
79//! [2]
80 QSqlField field("age", QVariant::Int);
81 field.setValue(QPixmap()); // WRONG
82//! [2]
83 }
84#endif
85
86 {
87//! [3]
88 QSqlField field("age", QVariant::Int);
89 field.setValue(QString("123")); // casts QString to int
90//! [3]
91 }
92
93 {
94//! [4]
95 QSqlQuery query;
96//! [4] //! [5]
97 QSqlRecord record = query.record();
98//! [5] //! [6]
99 QSqlField field = record.field("country");
100//! [6]
101 }
102}
103
104void doSomething(const QString &)
105{
106}
107
108void QSqlQuery_snippets()
109{
110 {
111 // typical loop
112//! [7]
113 QSqlQuery query("SELECT country FROM artist");
114 while (query.next()) {
115 QString country = query.value(0).toString();
116 doSomething(country);
117 }
118//! [7]
119 }
120
121 {
122 // field index lookup
123//! [8]
124 QSqlQuery query("SELECT * FROM artist");
125 int fieldNo = query.record().indexOf("country");
126 while (query.next()) {
127 QString country = query.value(fieldNo).toString();
128 doSomething(country);
129 }
130//! [8]
131 }
132
133 {
134 // named with named
135//! [9]
136 QSqlQuery query;
137 query.prepare("INSERT INTO person (id, forename, surname) "
138 "VALUES (:id, :forename, :surname)");
139 query.bindValue(":id", 1001);
140 query.bindValue(":forename", "Bart");
141 query.bindValue(":surname", "Simpson");
142 query.exec();
143//! [9]
144 }
145
146 {
147 // positional with named
148//! [10]
149 QSqlQuery query;
150 query.prepare("INSERT INTO person (id, forename, surname) "
151 "VALUES (:id, :forename, :surname)");
152 query.bindValue(0, 1001);
153 query.bindValue(1, "Bart");
154 query.bindValue(2, "Simpson");
155 query.exec();
156//! [10]
157 }
158
159 {
160 // positional 1
161//! [11]
162 QSqlQuery query;
163 query.prepare("INSERT INTO person (id, forename, surname) "
164 "VALUES (?, ?, ?)");
165 query.bindValue(0, 1001);
166 query.bindValue(1, "Bart");
167 query.bindValue(2, "Simpson");
168 query.exec();
169//! [11]
170 }
171
172 {
173 // positional 2
174//! [12]
175 QSqlQuery query;
176 query.prepare("INSERT INTO person (id, forename, surname) "
177 "VALUES (?, ?, ?)");
178 query.addBindValue(1001);
179 query.addBindValue("Bart");
180 query.addBindValue("Simpson");
181 query.exec();
182//! [12]
183 }
184
185 {
186 // stored
187//! [13]
188 QSqlQuery query;
189 query.prepare("CALL AsciiToInt(?, ?)");
190 query.bindValue(0, "A");
191 query.bindValue(1, 0, QSql::Out);
192 query.exec();
193 int i = query.boundValue(1).toInt(); // i is 65
194//! [13]
195 Q_UNUSED(i);
196 }
197
198 QSqlQuery query;
199
200 {
201 // examine with named binding
202//! [14]
203 QMapIterator<QString, QVariant> i(query.boundValues());
204 while (i.hasNext()) {
205 i.next();
206 cout << i.key().toAscii().data() << ": "
207 << i.value().toString().toAscii().data() << endl;
208 }
209//! [14]
210 }
211
212 {
213 // examine with positional binding
214//! [15]
215 QList<QVariant> list = query.boundValues().values();
216 for (int i = 0; i < list.size(); ++i)
217 cout << i << ": " << list.at(i).toString().toAscii().data() << endl;
218//! [15]
219 }
220}
221
222void QSqlQueryModel_snippets()
223{
224 {
225//! [16]
226 QSqlQueryModel *model = new QSqlQueryModel;
227 model->setQuery("SELECT name, salary FROM employee");
228 model->setHeaderData(0, Qt::Horizontal, tr("Name"));
229 model->setHeaderData(1, Qt::Horizontal, tr("Salary"));
230
231//! [17]
232 QTableView *view = new QTableView;
233//! [17] //! [18]
234 view->setModel(model);
235//! [18] //! [19]
236 view->show();
237//! [16] //! [19] //! [20]
238 view->setEditTriggers(QAbstractItemView::NoEditTriggers);
239//! [20]
240 }
241
242//! [21]
243 QSqlQueryModel model;
244 model.setQuery("SELECT * FROM employee");
245 int salary = model.record(4).value("salary").toInt();
246//! [21]
247 Q_UNUSED(salary);
248
249 {
250//! [22]
251 int salary = model.data(model.index(4, 2)).toInt();
252//! [22]
253 Q_UNUSED(salary);
254 }
255
256 for (int row = 0; row < model.rowCount(); ++row) {
257 for (int col = 0; col < model.columnCount(); ++col) {
258 qDebug() << model.data(model.index(row, col));
259 }
260 }
261}
262
263class MyModel : public QSqlQueryModel
264{
265public:
266 QVariant data(const QModelIndex &item, int role) const;
267
268 int m_specialColumnNo;
269};
270
271//! [23]
272QVariant MyModel::data(const QModelIndex &item, int role) const
273{
274 if (item.column() == m_specialColumnNo) {
275 // handle column separately
276 }
277 return QSqlQueryModel::data(item, role);
278}
279//! [23]
280
281void QSqlTableModel_snippets()
282{
283//! [24]
284 QSqlTableModel *model = new QSqlTableModel;
285 model->setTable("employee");
286 model->setEditStrategy(QSqlTableModel::OnManualSubmit);
287 model->select();
288 model->removeColumn(0); // don't show the ID
289 model->setHeaderData(0, Qt::Horizontal, tr("Name"));
290 model->setHeaderData(1, Qt::Horizontal, tr("Salary"));
291
292 QTableView *view = new QTableView;
293 view->setModel(model);
294 view->show();
295//! [24]
296
297 {
298//! [25]
299 QSqlTableModel model;
300 model.setTable("employee");
301 QString name = model.record(4).value("name").toString();
302//! [25]
303 }
304}
305
306void sql_intro_snippets()
307{
308 {
309//! [26]
310 QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
311 db.setHostName("bigblue");
312 db.setDatabaseName("flightdb");
313 db.setUserName("acarlson");
314 db.setPassword("1uTbSbAs");
315 bool ok = db.open();
316//! [26]
317 Q_UNUSED(ok);
318 }
319
320 {
321//! [27]
322 QSqlDatabase firstDB = QSqlDatabase::addDatabase("QMYSQL", "first");
323 QSqlDatabase secondDB = QSqlDatabase::addDatabase("QMYSQL", "second");
324//! [27]
325 }
326
327 {
328//! [28]
329 QSqlDatabase defaultDB = QSqlDatabase::database();
330//! [28] //! [29]
331 QSqlDatabase firstDB = QSqlDatabase::database("first");
332//! [29] //! [30]
333 QSqlDatabase secondDB = QSqlDatabase::database("second");
334//! [30]
335 }
336
337 {
338 // SELECT1
339//! [31]
340 QSqlQuery query;
341 query.exec("SELECT name, salary FROM employee WHERE salary > 50000");
342//! [31]
343
344//! [32]
345 while (query.next()) {
346 QString name = query.value(0).toString();
347 int salary = query.value(1).toInt();
348 qDebug() << name << salary;
349 }
350//! [32]
351 }
352
353 {
354 // FEATURE
355//! [33]
356 QSqlQuery query;
357 int numRows;
358 query.exec("SELECT name, salary FROM employee WHERE salary > 50000");
359
360 QSqlDatabase defaultDB = QSqlDatabase::database();
361 if (defaultDB.driver()->hasFeature(QSqlDriver::QuerySize)) {
362 numRows = query.size();
363 } else {
364 // this can be very slow
365 query.last();
366 numRows = query.at() + 1;
367 }
368//! [33]
369 }
370
371 {
372 // INSERT1
373//! [34]
374 QSqlQuery query;
375 query.exec("INSERT INTO employee (id, name, salary) "
376 "VALUES (1001, 'Thad Beaumont', 65000)");
377//! [34]
378 }
379
380 {
381 // NAMED BINDING
382//! [35]
383 QSqlQuery query;
384 query.prepare("INSERT INTO employee (id, name, salary) "
385 "VALUES (:id, :name, :salary)");
386 query.bindValue(":id", 1001);
387 query.bindValue(":name", "Thad Beaumont");
388 query.bindValue(":salary", 65000);
389 query.exec();
390//! [35]
391 }
392
393 {
394 // POSITIONAL BINDING
395//! [36]
396 QSqlQuery query;
397 query.prepare("INSERT INTO employee (id, name, salary) "
398 "VALUES (?, ?, ?)");
399 query.addBindValue(1001);
400 query.addBindValue("Thad Beaumont");
401 query.addBindValue(65000);
402 query.exec();
403//! [36]
404 }
405
406 {
407 // UPDATE1
408//! [37]
409 QSqlQuery query;
410 query.exec("UPDATE employee SET salary = 70000 WHERE id = 1003");
411//! [37]
412 }
413
414 {
415 // DELETE1
416//! [38]
417 QSqlQuery query;
418 query.exec("DELETE FROM employee WHERE id = 1007");
419//! [38]
420 }
421
422 {
423 // TRANSACTION
424//! [39]
425 QSqlDatabase::database().transaction();
426 QSqlQuery query;
427 query.exec("SELECT id FROM employee WHERE name = 'Torild Halvorsen'");
428 if (query.next()) {
429 int employeeId = query.value(0).toInt();
430 query.exec("INSERT INTO project (id, name, ownerid) "
431 "VALUES (201, 'Manhattan Project', "
432 + QString::number(employeeId) + ')');
433 }
434 QSqlDatabase::database().commit();
435//! [39]
436 }
437
438 {
439 // SQLQUERYMODEL1
440//! [40]
441 QSqlQueryModel model;
442 model.setQuery("SELECT * FROM employee");
443
444 for (int i = 0; i < model.rowCount(); ++i) {
445 int id = model.record(i).value("id").toInt();
446 QString name = model.record(i).value("name").toString();
447 qDebug() << id << name;
448 }
449//! [40]
450 }
451
452 {
453 // SQLTABLEMODEL1
454//! [41]
455 QSqlTableModel model;
456 model.setTable("employee");
457 model.setFilter("salary > 50000");
458 model.setSort(2, Qt::DescendingOrder);
459 model.select();
460
461 for (int i = 0; i < model.rowCount(); ++i) {
462 QString name = model.record(i).value("name").toString();
463 int salary = model.record(i).value("salary").toInt();
464 qDebug() << name << salary;
465 }
466//! [41]
467 }
468
469 {
470 // SQLTABLEMODEL2
471 QSqlTableModel model;
472 model.setTable("employee");
473
474//! [42]
475 for (int i = 0; i < model.rowCount(); ++i) {
476 QSqlRecord record = model.record(i);
477 double salary = record.value("salary").toInt();
478 salary *= 1.1;
479 record.setValue("salary", salary);
480 model.setRecord(i, record);
481 }
482 model.submitAll();
483//! [42]
484
485 // SQLTABLEMODEL3
486 int row = 1;
487 int column = 2;
488//! [43]
489 model.setData(model.index(row, column), 75000);
490 model.submitAll();
491//! [43]
492
493 // SQLTABLEMODEL4
494//! [44]
495 model.insertRows(row, 1);
496 model.setData(model.index(row, 0), 1013);
497 model.setData(model.index(row, 1), "Peter Gordon");
498 model.setData(model.index(row, 2), 68500);
499 model.submitAll();
500//! [44]
501
502//! [45]
503 model.removeRows(row, 5);
504//! [45] //! [46]
505 model.submitAll();
506//! [46]
507 }
508}
509
510//! [47]
511class XyzResult : public QSqlResult
512{
513public:
514 XyzResult(const QSqlDriver *driver)
515 : QSqlResult(driver) {}
516 ~XyzResult() {}
517
518protected:
519 QVariant data(int /* index */) { return QVariant(); }
520 bool isNull(int /* index */) { return false; }
521 bool reset(const QString & /* query */) { return false; }
522 bool fetch(int /* index */) { return false; }
523 bool fetchFirst() { return false; }
524 bool fetchLast() { return false; }
525 int size() { return 0; }
526 int numRowsAffected() { return 0; }
527 QSqlRecord record() { return QSqlRecord(); }
528};
529//! [47]
530
531//! [48]
532class XyzDriver : public QSqlDriver
533{
534public:
535 XyzDriver() {}
536 ~XyzDriver() {}
537
538 bool hasFeature(DriverFeature /* feature */) const { return false; }
539 bool open(const QString & /* db */, const QString & /* user */,
540 const QString & /* password */, const QString & /* host */,
541 int /* port */, const QString & /* options */)
542 { return false; }
543 void close() {}
544 QSqlResult *createResult() const { return new XyzResult(this); }
545};
546//! [48]
547
548int main(int argc, char **argv)
549{
550 QApplication app(argc, argv);
551
552 QSqlDatabase_snippets();
553 QSqlField_snippets();
554 QSqlQuery_snippets();
555 QSqlQueryModel_snippets();
556 QSqlTableModel_snippets();
557
558 XyzDriver driver;
559 XyzResult result(&driver);
560}
Note: See TracBrowser for help on using the repository browser.