QT 使用QSqlTableModel对数据库进行创建，插入，显示

效果图

概述

• 本案例用于对数据库中的数据进行显示等其他操作，其他表格筛选，过滤等功能可看此博客

• 框架：数据模型使用QSqlTableModel，视图使用QTableView，表格的一些字体或者控件之类的使用QStyledItemDelegate实现。
  导航栏的变化实时的传回给表格，所有的数据库表都实现继承一个表格类，根据表格本身的特性可以设置自己的委托。数据库使用一个单列类进行管理，包括数据库的读取 ，创建，数据插入，以及对模型的映射等。

• 使用的数据库类型为QPSQL

功能点

1. 初始化数据
2. 插入数据
3. 数据显示

代码分析

初始数据

• 初始化数据库及表

  void LogManagement::initDB()
  {dataPtr->db = QSqlDatabase::addDatabase("QPSQL", "dabao_pouring__db_connection");dataPtr->db.setHostName("localhost");dataPtr->db.setDatabaseName("dabao_pouring_db");dataPtr->db.setUserName("postgres");dataPtr->db.setPassword("dj");if (!dataPtr->db.open()){qCritical() << "无法打开数据库：" << dataPtr->db.lastError().text();return;}initOperationLog();initErrorLog();
  }void LogManagement::initOperationLog()
  {QScopedPointer<QSqlQuery> query(new QSqlQuery(dataPtr->db));if (!query->exec("SELECT 1 FROM operationlog LIMIT 1")){QString createTableQuery = R"(CREATE TABLE IF NOT EXISTS operationlog (id SERIAL PRIMARY KEY,time TIMESTAMP NOT NULL,result VARCHAR(10) NOT NULL,content TEXT NOT NULL,error TEXT  NULL,operation VARCHAR(10) NULL))";if (!query->exec(createTableQuery)){qCritical() << "创建表失败：" << query->lastError().text();return;}}dataPtr->operationLogModel = new QSqlTableModel(this, dataPtr->db);dataPtr->operationLogModel->setTable("operationlog");                        // 设置要操作的表名dataPtr->operationLogModel->setEditStrategy(QSqlTableModel::OnManualSubmit); // 设置编辑策略if (!(dataPtr->operationLogModel->select()))                                 // 查询数据{qCritical() << "打开数据表失败：" << dataPtr->operationLogModel->lastError().text();return;}dataPtr->operationLogModel->setHeaderData(dataPtr->operationLogModel->fieldIndex("time"), Qt::Horizontal, "时间");dataPtr->operationLogModel->setHeaderData(dataPtr->operationLogModel->fieldIndex("result"), Qt::Horizontal, "操作情况");dataPtr->operationLogModel->setHeaderData(dataPtr->operationLogModel->fieldIndex("content"), Qt::Horizontal, "操作内容");dataPtr->operationLogModel->setHeaderData(dataPtr->operationLogModel->fieldIndex("error"), Qt::Horizontal, "异常");dataPtr->operationLogModel->setHeaderData(dataPtr->operationLogModel->fieldIndex("operation"), Qt::Horizontal, "操作");
  }
  

插入数据

void LogManagement::appendErrorLogData(const QString &time, const QString &type, const QString &content)
{if (!dataPtr->errorLogModel)return;int newRow = dataPtr->errorLogModel->rowCount();      // 获取当前行数，这将是新行的索引bool res = dataPtr->errorLogModel->insertRow(newRow); // 插入新行if (!res){qCritical() << "无法添加新记录";return;}// 设置新记录的值res = dataPtr->errorLogModel->setData(dataPtr->errorLogModel->index(newRow, dataPtr->errorLogModel->fieldIndex("type")), type);if (!res){return;}res = dataPtr->errorLogModel->setData(dataPtr->errorLogModel->index(newRow, dataPtr->errorLogModel->fieldIndex("time")), QDateTime::fromString(time, "yyyy-MM-dd hh:mm:ss"));if (!res){return;}res = dataPtr->errorLogModel->setData(dataPtr->errorLogModel->index(newRow, dataPtr->errorLogModel->fieldIndex("content")), content);if (!res){return;}// 提交新记录res = dataPtr->errorLogModel->submitAll();if (!res){qCritical() << "保存记录失败: " << dataPtr->errorLogModel->lastError().text();dataPtr->errorLogModel->revertAll(); // 如果提交失败，回滚所有更改}
}

数据显示

• 使用的是model-view的设计模式，对于一些特殊的数据显示，比较字体颜色，或者加入删除按钮之类，由于数据都来源于model，所以设置操作按钮之类的并不好直接实现，我想到的一个办法就是，在数据库表的最后一列插入一个空列，用于放置操作按钮，比如删除。

  void GeneralDelegate::initStyleOption(QStyleOptionViewItem *option, const QModelIndex &index) const
  {QStyledItemDelegate::initStyleOption(option, index);QVariant data = index.model()->data(index, Qt::EditRole);if (data.type() == QVariant::DateTime){QDateTime dateTime = data.toDateTime();option->displayAlignment = Qt::AlignCenter;option->text = dateTime.toString("yyyy-MM-dd hh:mm:ss");}else if (data.type() == QVariant::Int){option->displayAlignment = Qt::AlignRight;option->text = QString::number(data.toInt());}else{option->displayAlignment = Qt::AlignLeft;option->text = data.toString();}
  }void GeneralDelegate::paint(QPainter *painter, const QStyleOptionViewItem &option, const QModelIndex &index) const
  {int currentColumn = index.column();int columnCount = index.model()->columnCount();// 判断是否为最后一列bool isLastColumn = (currentColumn == columnCount - 1);if (isLastColumn){QRect buttonRect = option.rect.adjusted(2, 2, -2, -2); // 调整按钮位置和大小QStyleOptionButton buttonOption;buttonOption.rect = buttonRect;buttonOption.state |= QStyle::State_Enabled;buttonOption.state |= QStyle::State_MouseOver;buttonOption.palette.setBrush(QPalette::Button, QColor(Qt::red));buttonOption.text = "删除";painter->save();painter->setClipRect(buttonRect);QApplication::style()->drawControl(QStyle::CE_PushButton, &buttonOption, painter);painter->restore();}else{QStyleOptionViewItem options = option;initStyleOption(&options, index);options.displayAlignment = Qt::AlignCenter; // 居中QApplication::style()->drawControl(QStyle::CE_ItemViewItem, &options, painter);}
  }bool GeneralDelegate::editorEvent(QEvent *event, QAbstractItemModel *model, const QStyleOptionViewItem &option, const QModelIndex &index)
  {int currentColumn = index.column();int columnCount = index.model()->columnCount();// 判断是否为最后一列bool isLastColumn = (currentColumn == columnCount - 1);if (event->type() == QEvent::MouseButtonRelease && isLastColumn){QMouseEvent *mouseEvent = static_cast<QMouseEvent *>(event);QRect buttonRect = option.rect.adjusted(2, 2, -2, -2);if (buttonRect.contains(mouseEvent->pos())){// 触发删除操作emit deleteRequested(index);return true;}}return QStyledItemDelegate::editorEvent(event, model, option, index);
  }
  

总结

• 知识理应共享，源码在此