| 版本 | 作者 | 日期 | 备注 |
|---|---|---|---|
| 0.1 | loon | 2018.10.25 | 初稿 |
数据库在某些情况下，相对于文件操作更加方便，软件在某些情况下对于数据库也是必不可少的，因此，我们有必要了解和学习在Qt开发中如何使用数据库，由于sqlite文件数据库较为方便且Qt默认带有其引擎，所以以下总结基于sqlite。
我们前面探索学习Qt时提到过帮助文档，那么如何使用Qt的SQL我们就来试一下，在Qt creator的帮助中搜索SQL，找到SQL Programming，里面提到一些建议和Database Classes以及这些类的用处：
These classes provide access to SQL databases.
| QSql | Contains miscellaneous identifiers used throughout the Qt SQL module |
|---|---|
| QSqlDatabase | Represents a connection to a database |
| QSqlDriver | Abstract base class for accessing specific SQL databases |
| QSqlDriverCreator | Template class that provides a SQL driver factory for a specific driver type |
| QSqlDriverCreatorBase | The base class for SQL driver factories |
| QSqlError | SQL database error information |
| QSqlField | Manipulates the fields in SQL database tables and views |
| QSqlIndex | Functions to manipulate and describe database indexes |
| QSqlQuery | Means of executing and manipulating SQL statements |
| QSqlQueryModel | Read-only data model for SQL result sets |
| QSqlRecord | Encapsulates a database record |
| QSqlRelationalTableModel | Editable data model for a single database table, with foreign key support |
| QSqlResult | Abstract interface for accessing data from specific SQL databases |
| QSqlTableModel | Editable data model for a single database table |
这些类包括在以下三层中：Driver Layer、SQL API Layer、User Interface Layer。
驱动层是SQL api和特殊数据库之间的低等级的桥梁，不同的数据库其SQL api可能会有所不同，其支持的数据库种类包括：
| QDB2 | IBM DB2 (version 7.1 and above) |
|---|---|
| QIBASE | Borland InterBase |
| QMYSQL | MySQL |
| QOCI | Oracle Call Interface Driver |
| QODBC | Open Database Connectivity (ODBC) - Microsoft SQL Server and other ODBC-compliant databases |
| QPSQL | PostgreSQL (versions 7.3 and above) |
| QSQLITE2 | SQLite version 2 |
| QSQLITE | SQLite version 3 |
| QTDS | Sybase Adaptive Server Note: obsolete from Qt 4.7 |
我们要用的就是QSQLITE，支持SQLite3及其以上版本。关于如何编译对应的数据库，以插件形式加入到Qt，这里就不多说了，后续需要的话可以单独做个总结，这都是在帮助文档中有的。
These classes provide access to databases. Connections are made using the QSqlDatabase class. Database interaction is achieved by using the QSqlQuery class. In addition to QSqlDatabase and QSqlQuery, the SQL API layer is supported by QSqlError, QSqlField, QSqlIndex, and QSqlRecord.
这些类提供对数据库的访问。使用QSqlDatabase类建立连接。数据库交互是通过使用QSqlQuery类实现的。除了QSqlDatabase和QSqlQuery之外，SQL API层还支持QSqlError、QSqlField、QSqlIndex和QSqlRecord。
These classes link the data from a database to data-aware widgets. They include QSqlQueryModel, QSqlTableModel, and QSqlRelationalTableModel. These classes are designed to work with Qt’s model/view framework.
Note that a QCoreApplication object must be instantiated before using any of these classes.
这些类将数据从数据库链接到支持数据的小部件。它们包括QSqlQueryModel、QSqlTableModel和QSqlRelationalTableModel。这些类被设计用于Qt的模型/视图框架。
注意，QCoreApplication对象必须在使用这些类之前实例化。
找到SQL programming，了解Qt如何进行SQL编程；
之后就是Connecting to Databases，看到如何连接数据库；
再接着 Executing SQL Statements，看如何执行sql语句；
再接着 Using the SQL Model Classes，使用SQL模块类进行数据查询等；
然后是Presenting Data in a Table View，将查询到的数据在表格视图中显示；
最后是Creating Data-Aware Forms，创建数据感知形式，以提交表单的形式插入数据或修改数据后在界面上呈现出来。
具体每一步在帮助中都描述的很清楚了，你只需要去阅读以下就可以了。
OK，当你阅读完之后，我们再找个例子先大致看一下如何使用，就找Qt creator自带的，在示例中直接搜索SQL：
那么我们按照上面总结的流程去看一下每个例子，最后再自己写一个例子，看例子的过程中有不懂的API记得F1寻找帮助，这样的话，掌握Qt SQL Programming只是时间问题了。
下面就以第一个例子Books来说一下：
打开示例，第一次打开需要点击配置进行项目配置，然后就可以编译运行了，运行结果如下：
可以看到项目中有.pro工程文件、头文件、源文件、界面文件和资源。
TEMPLATE = app
INCLUDEPATH += .
HEADERS = bookdelegate.h bookwindow.h initdb.h
RESOURCES = books.qrc
SOURCES = bookdelegate.cpp main.cpp bookwindow.cpp
FORMS = bookwindow.ui
QT += sql widgets widgets
target.path = $$[QT_INSTALL_EXAMPLES]/sql/books
INSTALLS += target
wince {
CONFIG(debug, debug|release):sqlPlugins.files = $$QT_BUILD_TREE/plugins/sqldrivers/*d4.dll
CONFIG(release, debug|release):sqlPlugins.files = $$QT_BUILD_TREE/plugins/sqldrivers/*[^d]4.dll
sqlPlugins.path = sqldrivers
INSTALLS += sqlPlugins
}
需要注意的时qmake需要添加sql，所以需要加上QT += sql，这个在Driver层中提到过，所以.pro中需要注意就是这个。
然后从main.cpp开始看起：
/****************************************************************************
**
** Copyright (C) 2015 The Qt Company Ltd.
** Contact: http://www.qt.io/licensing/
**
** This file is part of the demonstration applications of the Qt Toolkit.
**
** $QT_BEGIN_LICENSE:LGPL21$
** Commercial License Usage
** Licensees holding valid commercial Qt licenses may use this file in
** accordance with the commercial license agreement provided with the
** Software or, alternatively, in accordance with the terms contained in
** a written agreement between you and The Qt Company. For licensing terms
** and conditions see http://www.qt.io/terms-conditions. For further
** information use the contact form at http://www.qt.io/contact-us.
**
** GNU Lesser General Public License Usage
** Alternatively, this file may be used under the terms of the GNU Lesser
** General Public License version 2.1 or version 3 as published by the Free
** Software Foundation and appearing in the file LICENSE.LGPLv21 and
** LICENSE.LGPLv3 included in the packaging of this file. Please review the
** following information to ensure the GNU Lesser General Public License
** requirements will be met: https://www.gnu.org/licenses/lgpl.html and
** http://www.gnu.org/licenses/old-licenses/lgpl-2.1.html.
**
** As a special exception, The Qt Company gives you certain additional
** rights. These rights are described in The Qt Company LGPL Exception
** version 1.1, included in the file LGPL_EXCEPTION.txt in this package.
**
** $QT_END_LICENSE$
**
****************************************************************************/
#include "bookwindow.h"
#include <QtWidgets>
int main(int argc, char * argv[])
{
Q_INIT_RESOURCE(books);
QApplication app(argc, argv);
BookWindow win;
win.show();
return app.exec();
}
可以看到基类是widget，Q_INIT_RESOURCE(books)使用F1查看后发现是用来初始化资源文件books.qrc的。然后就是创建了win对象，那么我们接着去看BookWindow类吧。
bookwindow.h:
/****************************************************************************
**
** Copyright (C) 2015 The Qt Company Ltd.
** Contact: http://www.qt.io/licensing/
**
** This file is part of the demonstration applications of the Qt Toolkit.
**
** $QT_BEGIN_LICENSE:LGPL21$
** Commercial License Usage
** Licensees holding valid commercial Qt licenses may use this file in
** accordance with the commercial license agreement provided with the
** Software or, alternatively, in accordance with the terms contained in
** a written agreement between you and The Qt Company. For licensing terms
** and conditions see http://www.qt.io/terms-conditions. For further
** information use the contact form at http://www.qt.io/contact-us.
**
** GNU Lesser General Public License Usage
** Alternatively, this file may be used under the terms of the GNU Lesser
** General Public License version 2.1 or version 3 as published by the Free
** Software Foundation and appearing in the file LICENSE.LGPLv21 and
** LICENSE.LGPLv3 included in the packaging of this file. Please review the
** following information to ensure the GNU Lesser General Public License
** requirements will be met: https://www.gnu.org/licenses/lgpl.html and
** http://www.gnu.org/licenses/old-licenses/lgpl-2.1.html.
**
** As a special exception, The Qt Company gives you certain additional
** rights. These rights are described in The Qt Company LGPL Exception
** version 1.1, included in the file LGPL_EXCEPTION.txt in this package.
**
** $QT_END_LICENSE$
**
****************************************************************************/
#ifndef BOOKWINDOW_H
#define BOOKWINDOW_H
#include <QtWidgets>
#include <QtSql>
#include "ui_bookwindow.h"
class BookWindow: public QMainWindow
{
Q_OBJECT
public:
BookWindow();
private:
void showError(const QSqlError &err);
Ui::BookWindow ui;
QSqlRelationalTableModel *model;
int authorIdx, genreIdx;
};
#endif
bookwindow.cpp:
/****************************************************************************
**
** Copyright (C) 2015 The Qt Company Ltd.
** Contact: http://www.qt.io/licensing/
**
** This file is part of the demonstration applications of the Qt Toolkit.
**
** $QT_BEGIN_LICENSE:LGPL21$
** Commercial License Usage
** Licensees holding valid commercial Qt licenses may use this file in
** accordance with the commercial license agreement provided with the
** Software or, alternatively, in accordance with the terms contained in
** a written agreement between you and The Qt Company. For licensing terms
** and conditions see http://www.qt.io/terms-conditions. For further
** information use the contact form at http://www.qt.io/contact-us.
**
** GNU Lesser General Public License Usage
** Alternatively, this file may be used under the terms of the GNU Lesser
** General Public License version 2.1 or version 3 as published by the Free
** Software Foundation and appearing in the file LICENSE.LGPLv21 and
** LICENSE.LGPLv3 included in the packaging of this file. Please review the
** following information to ensure the GNU Lesser General Public License
** requirements will be met: https://www.gnu.org/licenses/lgpl.html and
** http://www.gnu.org/licenses/old-licenses/lgpl-2.1.html.
**
** As a special exception, The Qt Company gives you certain additional
** rights. These rights are described in The Qt Company LGPL Exception
** version 1.1, included in the file LGPL_EXCEPTION.txt in this package.
**
** $QT_END_LICENSE$
**
****************************************************************************/
#include "bookwindow.h"
#include "bookdelegate.h"
#include "initdb.h"
#include <QtSql>
BookWindow::BookWindow()
{
ui.setupUi(this);
if (!QSqlDatabase::drivers().contains("QSQLITE"))
QMessageBox::critical(this, "Unable to load database", "This demo needs the SQLITE driver");
// initialize the database
QSqlError err = initDb();
if (err.type() != QSqlError::NoError) {
showError(err);
return;
}
// Create the data model
model = new QSqlRelationalTableModel(ui.bookTable);
model->setEditStrategy(QSqlTableModel::OnManualSubmit);
model->setTable("books");
// Remember the indexes of the columns
authorIdx = model->fieldIndex("author");
genreIdx = model->fieldIndex("genre");
// Set the relations to the other database tables
model->setRelation(authorIdx, QSqlRelation("authors", "id", "name"));
model->setRelation(genreIdx, QSqlRelation("genres", "id", "name"));
// Set the localized header captions
model->setHeaderData(authorIdx, Qt::Horizontal, tr("Author Name"));
model->setHeaderData(genreIdx, Qt::Horizontal, tr("Genre"));
model->setHeaderData(model->fieldIndex("title"), Qt::Horizontal, tr("Title"));
model->setHeaderData(model->fieldIndex("year"), Qt::Horizontal, tr("Year"));
model->setHeaderData(model->fieldIndex("rating"), Qt::Horizontal, tr("Rating"));
// Populate the model
if (!model->select()) {
showError(model->lastError());
return;
}
// Set the model and hide the ID column
ui.bookTable->setModel(model);
ui.bookTable->setItemDelegate(new BookDelegate(ui.bookTable));
ui.bookTable->setColumnHidden(model->fieldIndex("id"), true);
ui.bookTable->setSelectionMode(QAbstractItemView::SingleSelection);
// Initialize the Author combo box
ui.authorEdit->setModel(model->relationModel(authorIdx));
ui.authorEdit->setModelColumn(model->relationModel(authorIdx)->fieldIndex("name"));
ui.genreEdit->setModel(model->relationModel(genreIdx));
ui.genreEdit->setModelColumn(model->relationModel(genreIdx)->fieldIndex("name"));
QDataWidgetMapper *mapper = new QDataWidgetMapper(this);
mapper->setModel(model);
mapper->setItemDelegate(new BookDelegate(this));
mapper->addMapping(ui.titleEdit, model->fieldIndex("title"));
mapper->addMapping(ui.yearEdit, model->fieldIndex("year"));
mapper->addMapping(ui.authorEdit, authorIdx);
mapper->addMapping(ui.genreEdit, genreIdx);
mapper->addMapping(ui.ratingEdit, model->fieldIndex("rating"));
connect(ui.bookTable->selectionModel(), SIGNAL(currentRowChanged(QModelIndex,QModelIndex)),
mapper, SLOT(setCurrentModelIndex(QModelIndex)));
ui.bookTable->setCurrentIndex(model->index(0, 0));
}
void BookWindow::showError(const QSqlError &err)
{
QMessageBox::critical(this, "Unable to initialize Database",
"Error initializing database: " + err.text());
}
分析一下其构造函数的流程：1、设置驱动，选择数据库类型；2、初始化数据库；3、创建数据模型；4、记录列号；5、设置到其它数据库表的关系；6、填充模型；7、设置模型并隐藏ID列 ；8、初始化作者组合框 。
bookwindow.ui:
initdb.h:
/****************************************************************************
**
** Copyright (C) 2015 The Qt Company Ltd.
** Contact: http://www.qt.io/licensing/
**
** This file is part of the demonstration applications of the Qt Toolkit.
**
** $QT_BEGIN_LICENSE:LGPL21$
** Commercial License Usage
** Licensees holding valid commercial Qt licenses may use this file in
** accordance with the commercial license agreement provided with the
** Software or, alternatively, in accordance with the terms contained in
** a written agreement between you and The Qt Company. For licensing terms
** and conditions see http://www.qt.io/terms-conditions. For further
** information use the contact form at http://www.qt.io/contact-us.
**
** GNU Lesser General Public License Usage
** Alternatively, this file may be used under the terms of the GNU Lesser
** General Public License version 2.1 or version 3 as published by the Free
** Software Foundation and appearing in the file LICENSE.LGPLv21 and
** LICENSE.LGPLv3 included in the packaging of this file. Please review the
** following information to ensure the GNU Lesser General Public License
** requirements will be met: https://www.gnu.org/licenses/lgpl.html and
** http://www.gnu.org/licenses/old-licenses/lgpl-2.1.html.
**
** As a special exception, The Qt Company gives you certain additional
** rights. These rights are described in The Qt Company LGPL Exception
** version 1.1, included in the file LGPL_EXCEPTION.txt in this package.
**
** $QT_END_LICENSE$
**
****************************************************************************/
#ifndef INITDB_H
#define INITDB_H
#include <QtSql>
void addBook(QSqlQuery &q, const QString &title, int year, const QVariant &authorId,
const QVariant &genreId, int rating)
{
q.addBindValue(title);
q.addBindValue(year);
q.addBindValue(authorId);
q.addBindValue(genreId);
q.addBindValue(rating);
q.exec();
}
QVariant addGenre(QSqlQuery &q, const QString &name)
{
q.addBindValue(name);
q.exec();
return q.lastInsertId();
}
QVariant addAuthor(QSqlQuery &q, const QString &name, const QDate &birthdate)
{
q.addBindValue(name);
q.addBindValue(birthdate);
q.exec();
return q.lastInsertId();
}
QSqlError initDb()
{
QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
db.setDatabaseName(":memory:");
if (!db.open())
return db.lastError();
QStringList tables = db.tables();
if (tables.contains("books", Qt::CaseInsensitive)
&& tables.contains("authors", Qt::CaseInsensitive))
return QSqlError();
QSqlQuery q;
if (!q.exec(QLatin1String("create table books(id integer primary key, title varchar, author integer, genre integer, year integer, rating integer)")))
return q.lastError();
if (!q.exec(QLatin1String("create table authors(id integer primary key, name varchar, birthdate date)")))
return q.lastError();
if (!q.exec(QLatin1String("create table genres(id integer primary key, name varchar)")))
return q.lastError();
if (!q.prepare(QLatin1String("insert into authors(name, birthdate) values(?, ?)")))
return q.lastError();
QVariant asimovId = addAuthor(q, QLatin1String("Isaac Asimov"), QDate(1920, 2, 1));
QVariant greeneId = addAuthor(q, QLatin1String("Graham Greene"), QDate(1904, 10, 2));
QVariant pratchettId = addAuthor(q, QLatin1String("Terry Pratchett"), QDate(1948, 4, 28));
if (!q.prepare(QLatin1String("insert into genres(name) values(?)")))
return q.lastError();
QVariant sfiction = addGenre(q, QLatin1String("Science Fiction"));
QVariant fiction = addGenre(q, QLatin1String("Fiction"));
QVariant fantasy = addGenre(q, QLatin1String("Fantasy"));
if (!q.prepare(QLatin1String("insert into books(title, year, author, genre, rating) values(?, ?, ?, ?, ?)")))
return q.lastError();
addBook(q, QLatin1String("Foundation"), 1951, asimovId, sfiction, 3);
addBook(q, QLatin1String("Foundation and Empire"), 1952, asimovId, sfiction, 4);
addBook(q, QLatin1String("Second Foundation"), 1953, asimovId, sfiction, 3);
addBook(q, QLatin1String("Foundation's Edge"), 1982, asimovId, sfiction, 3);
addBook(q, QLatin1String("Foundation and Earth"), 1986, asimovId, sfiction, 4);
addBook(q, QLatin1String("Prelude to Foundation"), 1988, asimovId, sfiction, 3);
addBook(q, QLatin1String("Forward the Foundation"), 1993, asimovId, sfiction, 3);
addBook(q, QLatin1String("The Power and the Glory"), 1940, greeneId, fiction, 4);
addBook(q, QLatin1String("The Third Man"), 1950, greeneId, fiction, 5);
addBook(q, QLatin1String("Our Man in Havana"), 1958, greeneId, fiction, 4);
addBook(q, QLatin1String("Guards! Guards!"), 1989, pratchettId, fantasy, 3);
addBook(q, QLatin1String("Night Watch"), 2002, pratchettId, fantasy, 3);
addBook(q, QLatin1String("Going Postal"), 2004, pratchettId, fantasy, 3);
return QSqlError();
}
#endif
初始化数据库的流程是：1、重载addDatabase驱动为sqlite3；2、设置数据库名，这里“:memory:”代表存放在内存中，即程序完成后就释放掉了，如果要使用文件存储，则为文件路径；3、打开数据库；4、创建表格；5、插入数据，插入数据时对应用和数据库操作又做了一层封装。
最后是bookdelegate.cpp:
/****************************************************************************
**
** Copyright (C) 2015 The Qt Company Ltd.
** Contact: http://www.qt.io/licensing/
**
** This file is part of the demonstration applications of the Qt Toolkit.
**
** $QT_BEGIN_LICENSE:LGPL21$
** Commercial License Usage
** Licensees holding valid commercial Qt licenses may use this file in
** accordance with the commercial license agreement provided with the
** Software or, alternatively, in accordance with the terms contained in
** a written agreement between you and The Qt Company. For licensing terms
** and conditions see http://www.qt.io/terms-conditions. For further
** information use the contact form at http://www.qt.io/contact-us.
**
** GNU Lesser General Public License Usage
** Alternatively, this file may be used under the terms of the GNU Lesser
** General Public License version 2.1 or version 3 as published by the Free
** Software Foundation and appearing in the file LICENSE.LGPLv21 and
** LICENSE.LGPLv3 included in the packaging of this file. Please review the
** following information to ensure the GNU Lesser General Public License
** requirements will be met: https://www.gnu.org/licenses/lgpl.html and
** http://www.gnu.org/licenses/old-licenses/lgpl-2.1.html.
**
** As a special exception, The Qt Company gives you certain additional
** rights. These rights are described in The Qt Company LGPL Exception
** version 1.1, included in the file LGPL_EXCEPTION.txt in this package.
**
** $QT_END_LICENSE$
**
****************************************************************************/
#include "bookdelegate.h"
#include <QtWidgets>
BookDelegate::BookDelegate(QObject *parent)
: QSqlRelationalDelegate(parent), star(QPixmap(":images/star.png"))
{
}
void BookDelegate::paint(QPainter *painter, const QStyleOptionViewItem &option,
const QModelIndex &index) const
{
if (index.column() != 5) {
QStyleOptionViewItem opt = option;
opt.rect.adjust(0, 0, -1, -1); // since we draw the grid ourselves
QSqlRelationalDelegate::paint(painter, opt, index);
} else {
const QAbstractItemModel *model = index.model();
QPalette::ColorGroup cg = (option.state & QStyle::State_Enabled) ?
(option.state & QStyle::State_Active) ? QPalette::Normal : QPalette::Inactive : QPalette::Disabled;
if (option.state & QStyle::State_Selected)
painter->fillRect(option.rect, option.palette.color(cg, QPalette::Highlight));
int rating = model->data(index, Qt::DisplayRole).toInt();
int width = star.width();
int height = star.height();
int x = option.rect.x();
int y = option.rect.y() + (option.rect.height() / 2) - (height / 2);
for (int i = 0; i < rating; ++i) {
painter->drawPixmap(x, y, star);
x += width;
}
drawFocus(painter, option, option.rect.adjusted(0, 0, -1, -1)); // since we draw the grid ourselves
}
QPen pen = painter->pen();
painter->setPen(option.palette.color(QPalette::Mid));
painter->drawLine(option.rect.bottomLeft(), option.rect.bottomRight());
painter->drawLine(option.rect.topRight(), option.rect.bottomRight());
painter->setPen(pen);
}
QSize BookDelegate::sizeHint(const QStyleOptionViewItem &option,
const QModelIndex &index) const
{
if (index.column() == 5)
return QSize(5 * star.width(), star.height()) + QSize(1, 1);
return QSqlRelationalDelegate::sizeHint(option, index) + QSize(1, 1); // since we draw the grid ourselves
}
bool BookDelegate::editorEvent(QEvent *event, QAbstractItemModel *model,
const QStyleOptionViewItem &option,
const QModelIndex &index)
{
if (index.column() != 5)
return QSqlRelationalDelegate::editorEvent(event, model, option, index);
if (event->type() == QEvent::MouseButtonPress) {
QMouseEvent *mouseEvent = static_cast<QMouseEvent*>(event);
int stars = qBound(0, int(0.7 + qreal(mouseEvent->pos().x()
- option.rect.x()) / star.width()), 5);
model->setData(index, QVariant(stars));
return false; //so that the selection can change
}
return true;
}
QWidget *BookDelegate::createEditor(QWidget *parent, const QStyleOptionViewItem &option,
const QModelIndex &index) const
{
if (index.column() != 4)
return QSqlRelationalDelegate::createEditor(parent, option, index);
// for editing the year, return a spinbox with a range from -1000 to 2100.
QSpinBox *sb = new QSpinBox(parent);
sb->setFrame(false);
sb->setMaximum(2100);
sb->setMinimum(-1000);
return sb;
}
可以看出其继承自QSqlRelationalDelegate，对于QSqlRelationalDelegate类根据帮助信息可以看出其是对QSqlRelationalTableModel中的数据进行显示和修改的一个类，所以该类的操作其实是对我们选择的数据模型的scoretable的重写操作。
每个示例都代表了一个应用方向，时间允许的话可以都阅读分析一下，但是要掌握的核心内容就是如何使用数据库，然后显示数据库数据，搞清楚其流程然后自己写个例子熟悉一下，Qt的数据库编程基本上就没有什么大问题了。
其实整个的分析过程中我们可以将其看作一个拼图游戏，我们知道最终的效果，就像我们知道最终会是什么样子，然后我们去完成拼图，虽然单独某个图块不熟悉，但是只要方向不错，那么最终仍然会拼出结果。这些小的图块就类似于代码中的事件用法、c++语法、控件用法等，我们可能在初期对其不甚了解，但只要方向没错，最终总会得到想要的结果，只是时间长短而已，并且随着了解的深入，对每一个图块也会越来越熟悉。