一、介绍

演示如何在模型/视图框架下使用Qt SQL类

Books示例展示了如何将Qt的SQL类与模型/视图框架一起使用，为存储在数据库中的信息创建富用户界面。

关于一套藏书的信息保存在一个数据库中。这些书按作者、书名、体裁和出版年份分类。尽管这些字段都可以使用标准小部件显示和编辑，但是描述图书任意评级的附加字段还需要一些额外的东西。

书籍的评级系统会给每本书分配一些星星；一本书拥有的越多，就越有价值。通过单击包含评级的单元格，可以修改星星的数量，并更新数据库中的评级。

initDB.h

  • 该类主要是创建数据库并插入数据
  • 其他内容在注释里 ```cpp

    ifndef INITDB_H

    define INITDB_H

include

include

// 添加book的插入语句 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(); qDebug() << “sql1: “ << q.executedQuery() << “, name” << name << “, lastId” << q.lastInsertId(); return q.lastInsertId(); }

QVariant addAuthor(QSqlQuery& q, const QString& name, const QDate& birthdate) { q.addBindValue(name); q.addBindValue(birthdate); q.exec(); qDebug() << “sql2: “ << q.executedQuery() << “, name” << name << “, lastId” << q.lastInsertId(); return q.lastInsertId(); }

// 初始化数据库 QSqlError initDb() { // 设置数据库 QSqlDatabase db = QSqlDatabase::addDatabase(“QSQLITE”); db.setDatabaseName(“:memory:”);

  1. if (!db.open())
  2.     return db.lastError();
  3. // 判断数据库是否存在 books authors表
  4. QStringList tables = db.tables();
  5. if (tables.contains("books", Qt::CaseInsensitive) && tables.contains("authors", Qt::CaseInsensitive))
  6.     return QSqlError();
  8. // 创建表
  9. QSqlQuery q;
  10. if (!q.exec(QLatin1String("create table books("
  11.                           "     id integer primary key, "
  12.                           "     title varchar, "
  13.                           "     author integer, "
  14.                           "     genre integer, "
  15.                           "     year integer, "
  16.                           "     rating integer"
  17.                           ")")))
  18.     return q.lastError(); // 书
  20. if (!q.exec(QLatin1String("create table authors("
  21.                           "     id integer primary key, "
  22.                           "     name varchar, "
  23.                           "     birthdate date"
  24.                           ")")))
  25.     return q.lastError(); // 作者
  27. if (!q.exec(QLatin1String("create table genres("
  28.                           "     id integer primary key, "
  29.                           "     name varchar"
  30.                           ")")))
  31.     return q.lastError(); // 类型
  33. // 插入作者信息
  34. if (!q.prepare(QLatin1String("insert into authors(name, birthdate) values(?, ?)")))
  35.     return q.lastError();
  36. QVariant asimovId = addAuthor(q, QLatin1String("Isaac Asimov"), QDate(1920, 2, 1));
  37. QVariant greeneId = addAuthor(q, QLatin1String("Graham Greene"), QDate(1904, 10, 2));
  38. QVariant pratchettId = addAuthor(q, QLatin1String("Terry Pratchett"), QDate(1948, 4, 28));
  40. // 插入体裁信息
  41. if (!q.prepare(QLatin1String("insert into genres(name) values(?)")))
  42.     return q.lastError();
  43. QVariant sfiction = addGenre(q, QLatin1String("Science Fiction"));
  44. QVariant fiction = addGenre(q, QLatin1String("Fiction"));
  45. QVariant fantasy = addGenre(q, QLatin1String("Fantasy"));
  47. // 插入数据信息
  48. if (!q.prepare(QLatin1String("insert into books(title, year, author, genre, rating) values(?, ?, ?, ?, ?)")))
  49.     return q.lastError();
  50. addBook(q, QLatin1String("Foundation"), 1951, asimovId, sfiction, 3);
  51. addBook(q, QLatin1String("Foundation and Empire"), 1952, asimovId, sfiction, 4);
  52. addBook(q, QLatin1String("Second Foundation"), 1953, asimovId, sfiction, 3);
  53. addBook(q, QLatin1String("Foundation's Edge"), 1982, asimovId, sfiction, 3);
  54. addBook(q, QLatin1String("Foundation and Earth"), 1986, asimovId, sfiction, 4);
  55. addBook(q, QLatin1String("Prelude to Foundation"), 1988, asimovId, sfiction, 3);
  56. addBook(q, QLatin1String("Forward the Foundation"), 1993, asimovId, sfiction, 3);
  57. addBook(q, QLatin1String("The Power and the Glory"), 1940, greeneId, fiction, 4);
  58. addBook(q, QLatin1String("The Third Man"), 1950, greeneId, fiction, 5);
  59. addBook(q, QLatin1String("Our Man in Havana"), 1958, greeneId, fiction, 4);
  60. addBook(q, QLatin1String("Guards! Guards!"), 1989, pratchettId, fantasy, 3);
  61. addBook(q, QLatin1String("Night Watch"), 2002, pratchettId, fantasy, 3);
  62. addBook(q, QLatin1String("Going Postal"), 2004, pratchettId, fantasy, 3);
  64. return QSqlError();

}

endif

  2. <a name="ca1jM"></a>
  3. ### bookdelegate.h
  5. - `paint`和`sizeHint`这两个是继承`QItemDelegate`必须重写的函数
  6. - editorEvent:
  7.    - 开始编辑项目时，会使用触发编辑的事件、模型、项目索引以及用于渲染项目的选项调用此函数。
  8.    - 即使鼠标事件没有开始编辑项目，鼠标事件也会发送到 `editorEvent()`。
  9.    - 例如，如果您希望在项目上按下鼠标右键时打开上下文菜单，这会很有用。
  10.    - 基本实现返回 false（表明它尚未处理该事件）。
  11.    - [https://doc.qt.io/qt-5/qabstractitemdelegate.html#editorEvent](https://doc.qt.io/qt-5/qabstractitemdelegate.html#editorEvent)
  12. - createEditor：
  13.    - 返回用于编辑具有给定索引的数据项的编辑器。请注意，索引包含有关正在使用的模型的信息。编辑器的父小部件由parent指定，item选项由option指定。
  14.    - 基本实现返回 nullptr。如果您想要自定义编辑，则需要重新实现此功能。
  15.    - 返回的编辑器小部件应该有 `**Qt::StrongFocus**`；否则，小部件接收到的 QMouseEvents 将传播到视图。除非编辑器绘制自己的背景（例如，使用 `setAutoFillBackground()`），否则视图的背景会发光。
  16. ```cpp
  17. #ifndef BOOKDELEGATE_H
  18. #define BOOKDELEGATE_H
  20. #include <QModelIndex>
  21. #include <QPixmap>
  22. #include <QSize>
  23. #include <QSqlRelationalDelegate>
  25. QT_FORWARD_DECLARE_CLASS(QPainter)
  27. class BookDelegate : public QSqlRelationalDelegate
  28. {
  29. public:
  30.     BookDelegate(QObject *parent);
  32.     void paint(QPainter *painter, const QStyleOptionViewItem &option, 
  33.                 const QModelIndex &index) const override;
  35.     QSize sizeHint(const QStyleOptionViewItem &option,
  36.                    const QModelIndex &index) const override;
  38.     bool editorEvent(QEvent *event, QAbstractItemModel *model,
  39.                      const QStyleOptionViewItem &option,
  40.                      const QModelIndex &index) override;
  42.     QWidget *createEditor(QWidget *parent, const QStyleOptionViewItem &option,
  43.                           const QModelIndex &index) const override;
  45. private:
  46.     QPixmap star;
  47. };

bookdelegate.cpp

  2. #include "bookdelegate.h"
  4. #include <QtWidgets>
  6. BookDelegate::BookDelegate(QObject* parent)
  7.     : QSqlRelationalDelegate(parent)
  8.     , star(QPixmap(":images/star.png"))
  9. {
  10. }
  12. void BookDelegate::paint(QPainter* painter, const QStyleOptionViewItem& option,
  13.     const QModelIndex& index) const
  14. {
  15.     if (index.column() != 5) {
  16.         QStyleOptionViewItem opt = option;
  17.         // Since we draw the grid ourselves:
  18.         opt.rect.adjust(0, 0, -1, -1);
  19.         QSqlRelationalDelegate::paint(painter, opt, index);
  20.     } else {
  21.         const QAbstractItemModel* model = index.model();
  22.         QPalette::ColorGroup cg = (option.state & QStyle::State_Enabled) ?
  23.             (option.state & QStyle::State_Active) ?
  24.             QPalette::Normal : QPalette::Inactive : QPalette::Disabled;
  26.         if (option.state & QStyle::State_Selected)
  27.             painter->fillRect(
  28.                 option.rect,
  29.                 option.palette.color(cg, QPalette::Highlight));
  31.         int rating = model->data(index, Qt::DisplayRole).toInt();
  32.         int width = star.width();
  33.         int height = star.height();
  34.         int x = option.rect.x();
  35.         int y = option.rect.y() + (option.rect.height() / 2) - (height / 2);
  36.         for (int i = 0; i < rating; ++i) {
  37.             painter->drawPixmap(x, y, star);
  38.             x += width;
  39.         }
  40.         // Since we draw the grid ourselves:
  41.         drawFocus(painter, option, option.rect.adjusted(0, 0, -1, -1));
  42.     }
  44.     QPen pen = painter->pen();
  45.     painter->setPen(option.palette.color(QPalette::Mid));
  46.     painter->drawLine(option.rect.bottomLeft(), option.rect.bottomRight());
  47.     painter->drawLine(option.rect.topRight(), option.rect.bottomRight());
  48.     painter->setPen(pen);
  49. }
  51. // 这个是设置第五列星星图标的大小
  52. QSize BookDelegate::sizeHint(const QStyleOptionViewItem& option,
  53.     const QModelIndex& index) const
  54. {
  55.     if (index.column() == 5)
  56.         return QSize(5 * star.width(), star.height()) + QSize(1, 1);
  57.     // Since we draw the grid ourselves:
  58.     return QSqlRelationalDelegate::sizeHint(option, index) + QSize(1, 1);
  59. }
  61. // 处理鼠标按下事件,增加一个星星,然后返回false;其他事件就过滤掉
  62. bool BookDelegate::editorEvent(QEvent* event, QAbstractItemModel* model,
  63.     const QStyleOptionViewItem& option,
  64.     const QModelIndex& index)
  65. {
  66.     if (index.column() != 5)
  67.         return QSqlRelationalDelegate::editorEvent(event, model, option, index);
  69.     if (event->type() == QEvent::MouseButtonPress) {
  70.         QMouseEvent* mouseEvent = static_cast<QMouseEvent*>(event);
  71.         int stars = qBound(0, int(0.7 + qreal(mouseEvent->pos().x() - option.rect.x()) / star.width()), 5);
  72.         model->setData(index, QVariant(stars));
  73.         // So that the selection can change:
  74.         return false;
  75.     }
  77.     return true;
  78. }
  80. //创建editor widget控件的,该控件用于编辑model中的数据
  81. QWidget* BookDelegate::createEditor(QWidget* parent,
  82.     const QStyleOptionViewItem& option, const QModelIndex& index) const
  83. {
  84.     if (index.column() != 4)
  85.         return QSqlRelationalDelegate::createEditor(parent, option, index);
  87.     // 对于编辑年份，返回一个范围从-1000到2100的QSpinBox
  88.     QSpinBox* sb = new QSpinBox(parent);
  89.     sb->setFrame(false);
  90.     sb->setMaximum(2100);
  91.     sb->setMinimum(-1000);
  93.     return sb;
  94. }

book.h

  • 这一部分就是与界面相关的了
  • 数据库初始化、数据查询设置代理等 ```cpp

    ifndef BOOKWINDOW_H

    define BOOKWINDOW_H

include

include

include “ui_bookwindow.h”

class BookWindow: public QMainWindow { Q_OBJECT public: BookWindow();

private slots: void about();

private: void showError(const QSqlError &err); Ui::BookWindow ui; QSqlRelationalTableModel *model; int authorIdx, genreIdx;

  1. void createMenuBar();

};

endif

  1. <a name="z9Fxa"></a>
  2. ### book.cpp
  3. ```cpp
  4. #include "bookwindow.h"
  5. #include "bookdelegate.h"
  6. #include "initdb.h"
  8. #include <QString>
  9. #include <QtSql>
  11. BookWindow::BookWindow()
  12. {
  13.     ui.setupUi(this);
  15.     if (!QSqlDatabase::drivers().contains("QSQLITE"))
  16.         QMessageBox::critical(this, "无法加载数据库", "这个演示需要SQLITE驱动程序");
  18.     // 初始化数据库:
  19.     QSqlError err = initDb();
  20.     if (err.type() != QSqlError::NoError) {
  21.         showError(err);
  22.         return;
  23.     }
  25.     // 创建数据模型:
  26.     model = new QSqlRelationalTableModel(ui.bookTable);
  27.     model->setEditStrategy(QSqlTableModel::OnManualSubmit);
  28.     model->setTable("books");
  30.     // 记录列的索引:
  31.     authorIdx = model->fieldIndex("author");
  32.     genreIdx = model->fieldIndex("genre");
  34.     // 设置与其他数据库表的关系:
  35.     model->setRelation(authorIdx, QSqlRelation("authors", "id", "name"));
  36.     model->setRelation(genreIdx, QSqlRelation("genres", "id", "name"));
  38.     // 设置本地化的标题头:
  39.     model->setHeaderData(authorIdx, Qt::Horizontal, tr("Author Name"));
  40.     model->setHeaderData(genreIdx, Qt::Horizontal, tr("Genre"));
  41.     model->setHeaderData(model->fieldIndex("title"), Qt::Horizontal, tr("Title"));
  42.     model->setHeaderData(model->fieldIndex("year"), Qt::Horizontal, tr("Year"));
  43.     model->setHeaderData(model->fieldIndex("rating"), Qt::Horizontal, tr("Rating"));
  45.     // 填充模型:
  46.     if (!model->select()) {
  47.         showError(model->lastError());
  48.         return;
  49.     }
  51.     // 设置模型并隐藏ID列:
  52.     ui.bookTable->setModel(model);
  53.     ui.bookTable->setItemDelegate(new BookDelegate(ui.bookTable));
  54.     ui.bookTable->setColumnHidden(model->fieldIndex("id"), true);
  55.     ui.bookTable->setSelectionMode(QAbstractItemView::SingleSelection);
  57.     // 初始化Author组合框:
  58.     ui.authorEdit->setModel(model->relationModel(authorIdx));
  59.     ui.authorEdit->setModelColumn(
  60.         model->relationModel(authorIdx)->fieldIndex("name"));
  62.     ui.genreEdit->setModel(model->relationModel(genreIdx));
  63.     ui.genreEdit->setModelColumn(
  64.         model->relationModel(genreIdx)->fieldIndex("name"));
  66.     // 锁定并禁止调整评级列的宽度:
  67.     ui.bookTable->horizontalHeader()->setSectionResizeMode(
  68.         model->fieldIndex("rating"),
  69.         QHeaderView::ResizeToContents);
  71.     QDataWidgetMapper* mapper = new QDataWidgetMapper(this);
  72.     mapper->setModel(model);
  73.     mapper->setItemDelegate(new BookDelegate(this));
  74.     mapper->addMapping(ui.titleEdit, model->fieldIndex("title"));
  75.     mapper->addMapping(ui.yearEdit, model->fieldIndex("year"));
  76.     mapper->addMapping(ui.authorEdit, authorIdx);
  77.     mapper->addMapping(ui.genreEdit, genreIdx);
  78.     mapper->addMapping(ui.ratingEdit, model->fieldIndex("rating"));
  80.     connect(ui.bookTable->selectionModel(), &QItemSelectionModel::currentRowChanged,
  81.         mapper, &QDataWidgetMapper::setCurrentModelIndex);
  83.     ui.bookTable->setCurrentIndex(model->index(0, 0));
  84.     createMenuBar();
  85. }
  87. // 展示错误信息
  88. void BookWindow::showError(const QSqlError& err)
  89. {
  90.     QMessageBox::critical(this, QStringLiteral("无法初始化数据库"), "误差初始化数据库:" + err.text());
  91. }
  93. // 创建菜单栏
  94. void BookWindow::createMenuBar()
  95. {
  96.     QAction* quitAction = new QAction(tr("&Quit"), this);
  97.     QAction* aboutAction = new QAction(tr("&About"), this);
  98.     QAction* aboutQtAction = new QAction(tr("&About Qt"), this);
  100.     QMenu* fileMenu = menuBar()->addMenu(tr("&File"));
  101.     fileMenu->addAction(quitAction);
  103.     QMenu* helpMenu = menuBar()->addMenu(tr("&Help"));
  104.     helpMenu->addAction(aboutAction);
  105.     helpMenu->addAction(aboutQtAction);
  107.     connect(quitAction, &QAction::triggered, this, &BookWindow::close);
  108.     connect(aboutAction, &QAction::triggered, this, &BookWindow::about);
  109.     connect(aboutQtAction, &QAction::triggered, qApp, &QApplication::aboutQt);
  110. }
  112. void BookWindow::about()
  113. {
  114.     QMessageBox::about(this, QStringLiteral("关于Books"),
  115.         QStringLiteral("<b>Books</b>示例展示了如何在模型/视图框架下使用Qt SQL类"));
  116. }