QT数据库编程

ui界面

mainwindow.cpp

#include "mainwindow.h"
#include "ui_mainwindow.h"
#include <QButtonGroup>
#include <QFileDialog>
#include <QMessageBox>
MainWindow::MainWindow(QWidget* parent): QMainWindow(parent), ui(new Ui::MainWindow)
{ui->setupUi(this);//    this->setCentralWidget(ui->splitter);ui->tableView->setSelectionMode(QAbstractItemView::SingleSelection); //设置择的模式只可以选中一个ui->tableView->setAlternatingRowColors(true); //设置不同行颜色//设置分组QButtonGroup* group = new QButtonGroup(this);group->addButton(ui->radioButton);group->addButton(ui->radioButton_2);
}MainWindow::~MainWindow()
{delete ui;
}void MainWindow::on_pushButton_clicked()
{QString file = QFileDialog::getOpenFileName(this, "选择文件", "../", "SQLITE(*.*)");if (file.isEmpty())return;db = QSqlDatabase::addDatabase("QSQLITE"); //添加驱动db.setDatabaseName(file); //加入文件if (!db.open())QMessageBox::warning(this, "错误", "打开失败");else {openTable(); //打开数据库表格}qDebug() << "完成";
}void MainWindow::do_current_changed(const QModelIndex& current, const QModelIndex&)
{Q_UNUSED(current);qDebug() << "当前改变";ui->pushButton_8->setEnabled(tabModel->isDirty()); //是改过的
}void MainWindow::do_current_row_changed(const QModelIndex& current, const QModelIndex&)
{qDebug() << "点击了单元格";if (!current.isValid()) {ui->label_10->clear();return;}ui->pushButton_8->setEnabled(true);//映射dataMapper->setCurrentIndex(current.row());QSqlRecord curRecord = tabModel->record(current.row());if (!curRecord.isEmpty()) {qDebug() << "数据" << curRecord;}
}void MainWindow::openTable()
{//创建模型，打开数据库表格tabModel = new QSqlTableModel(this, db);tabModel->setTable("test"); //设置数据库表名称tabModel->setEditStrategy(QSqlTableModel::OnManualSubmit); //手动提交tabModel->setSort(tabModel->fieldIndex("id"), Qt::DescendingOrder); //按id 降序排序， 升序 AscendingOrderif (!tabModel->select()) {QMessageBox::critical(this, "错误", tabModel->lastError().text());}ui->statusbar->showMessage(QString("记录条数为 %1").arg(tabModel->rowCount()));//设置字段显示的标题tabModel->setHeaderData(tabModel->fieldIndex("id"), Qt::Horizontal, "ID号");tabModel->setHeaderData(tabModel->fieldIndex("name"), Qt::Horizontal, "姓名");// model /viewselModel = new QItemSelectionModel(tabModel, this);ui->tableView->setModel(tabModel);ui->tableView->setSelectionModel(selModel); //注意：必须先设置模型在设置选择模型，否则不生效//    ui->tableView->setColumnHidden(tabModel->fieldIndex("id"), true); //隐藏数据//代理QStringList str;str << "男"<< "女";delegateSex.setItems(str, false);ui->tableView->setItemDelegateForColumn(tabModel->fieldIndex("name"), &delegateSex);//字段与widget映射dataMapper = new QDataWidgetMapper(this);dataMapper->setModel(tabModel);dataMapper->setSubmitPolicy(QDataWidgetMapper::AutoSubmit); //数据自动提交dataMapper->addMapping(ui->spinBox, tabModel->fieldIndex("id"));dataMapper->addMapping(ui->lineEdit, tabModel->fieldIndex("name"));dataMapper->toFirst(); //显示第一条记录// 状态发生变化//    ui->pushButton->setEnabled(false);//获取字段名更新QSqlRecord emptyRec = tabModel->record();qDebug() << "emptyRec = " << emptyRec;for (int i = 0; i < emptyRec.count(); ++i) {ui->comboBox->addItem(emptyRec.fieldName(i));}//信号连接connect(selModel, &QItemSelectionModel::currentChanged, this, &MainWindow::do_current_changed);connect(selModel, &QItemSelectionModel::currentRowChanged, this, &MainWindow::do_current_row_changed);
}void MainWindow::on_pushButton_3_clicked()
{QModelIndex index = ui->tableView->currentIndex();QSqlRecord rec = tabModel->record();rec.setValue(tabModel->fieldIndex("name"), "王五");tabModel->insertRecord(index.row(), rec);selModel->clearSelection();selModel->setCurrentIndex(tabModel->index(tabModel->rowCount() - 1, 1), QItemSelectionModel::Select);//更新状态栏ui->statusbar->showMessage(QString("记录条数为 %1").arg(tabModel->rowCount()));
}void MainWindow::on_pushButton_2_clicked()
{QSqlRecord rec = tabModel->record();rec.setValue(tabModel->fieldIndex("name"), "王五");tabModel->insertRecord(tabModel->rowCount(), rec);selModel->clearSelection();selModel->setCurrentIndex(tabModel->index(tabModel->rowCount() - 1, 1), QItemSelectionModel::Select);//更新状态栏ui->statusbar->showMessage(QString("记录条数为 %1").arg(tabModel->rowCount()));
}void MainWindow::on_pushButton_4_clicked()
{QModelIndex index = ui->tableView->currentIndex();tabModel->removeRow(index.row());ui->statusbar->showMessage(QString("记录条数为 %1").arg(tabModel->rowCount()));
}void MainWindow::on_pushButton_5_clicked()
{QString aFile = QFileDialog::getOpenFileName(this, "选择图片", "", "(*.jpg)");if (aFile.isEmpty())return;QFile* file = new QFile(aFile, this);if (file->open(QIODevice::ReadOnly)) {QByteArray arr = file->readAll();file->close();QSqlRecord rec = tabModel->record(selModel->currentIndex().row());rec.setValue("photh", arr); //设置数据tabModel->setRecord(selModel->currentIndex().row(), rec); //设置行数据QPixmap pix;pix.load(aFile); //加载图片ui->label_10->setPixmap(pix.scaledToWidth(pix.width()));}
}void MainWindow::on_pushButton_6_clicked()
{QSqlRecord rec = tabModel->record(selModel->currentIndex().row());rec.setNull("photh");tabModel->setRecord(selModel->currentIndex().row(), rec);ui->label_10->clear();
}void MainWindow::on_pushButton_7_clicked()
{if (tabModel->rowCount() == 0)return;for (int i = 0; i < tabModel->rowCount(); ++i) {QSqlRecord rec = tabModel->record(i);rec.setValue("name", rec.value("name").toInt() + 100);tabModel->setRecord(i, rec);}if (tabModel->submitAll()) { // submit不成功，需要allQMessageBox::information(this, "成功", "涨工资成功");}
}void MainWindow::on_pushButton_8_clicked()
{bool ret = tabModel->submitAll();if (!ret) {QMessageBox::critical(this, "失败", "保存失败");}
}void MainWindow::on_pushButton_9_clicked()
{tabModel->revertAll(); //还原
}void MainWindow::on_comboBox_currentIndexChanged(int index)
{if (ui->radioButton->isChecked()) {tabModel->sort(index, Qt::AscendingOrder);} else {tabModel->sort(index, Qt::DescendingOrder);}tabModel->select();
}void MainWindow::on_radioButton_clicked()
{try {tabModel->sort(ui->comboBox->currentIndex(), Qt::AscendingOrder); // sort不需要select} catch (QException err) {qDebug() << err.what();}
}void MainWindow::on_radioButton_2_clicked()
{try {tabModel->sort(ui->comboBox->currentIndex(), Qt::DescendingOrder); // sort不需要select} catch (QException err) {qDebug() << err.clone();}
}void MainWindow::on_radioButton_3_clicked()
{tabModel->setFilter("name='12'");ui->statusbar->showMessage(QString("记录条数为 %1").arg(tabModel->rowCount()));
}void MainWindow::on_radioButton_4_clicked()
{tabModel->setFilter("name='张三'");ui->statusbar->showMessage(QString("记录条数为 %1").arg(tabModel->rowCount()));
}void MainWindow::on_radioButton_5_clicked()
{tabModel->setFilter("");ui->statusbar->showMessage(QString("记录条数为 %1").arg(tabModel->rowCount()));
}

 mainwindow.h

#ifndef MAINWINDOW_H
#define MAINWINDOW_H#include "tcomboxdelegate.h"
#include <QDataWidgetMapper>
#include <QMainWindow>
#include <QSql>
#include <QSqlTableModel>
#include <QtSql>
QT_BEGIN_NAMESPACE
namespace Ui {
class MainWindow;
}
QT_END_NAMESPACEclass MainWindow : public QMainWindow {Q_OBJECTpublic:MainWindow(QWidget* parent = nullptr);~MainWindow();private slots:void on_pushButton_clicked();void do_current_changed(const QModelIndex& current, const QModelIndex& previous);void do_current_row_changed(const QModelIndex& current, const QModelIndex& previous);void on_pushButton_3_clicked();void on_pushButton_2_clicked();void on_pushButton_4_clicked();void on_pushButton_5_clicked();void on_pushButton_6_clicked();void on_pushButton_7_clicked();void on_pushButton_8_clicked();void on_pushButton_9_clicked();void on_comboBox_currentIndexChanged(int index);void on_radioButton_clicked();void on_radioButton_2_clicked();void on_radioButton_3_clicked();void on_radioButton_4_clicked();void on_radioButton_5_clicked();private:Ui::MainWindow* ui;QSqlTableModel* tabModel;QDataWidgetMapper* dataMapper;QItemSelectionModel* selModel;QSqlDatabase db;TComBoxDelegate delegateSex;TComBoxDelegate deleagteDepart;void openTable();
};
#endif // MAINWINDOW_H

tcomboxdelegate.cpp

#include "tcomboxdelegate.h"
#include <QComboBox>
TComBoxDelegate::TComBoxDelegate(QObject* parent): QStyledItemDelegate { parent }
{
}
QWidget* TComBoxDelegate::createEditor(QWidget* parent, const QStyleOptionViewItem& option, const QModelIndex& index) const
{QComboBox* editor = new QComboBox(parent);editor->setEditable(m_editable);qDebug() << m_itemList.size();for (auto item : m_itemList) {editor->addItem(item);}return editor;
}void TComBoxDelegate::setEditorData(QWidget* editor, const QModelIndex& index) const
{QComboBox* box = dynamic_cast<QComboBox*>(editor); //把editor转为指向QSpinBox的指针 动态强转QString value = index.model()->data(index, Qt::DisplayRole).toString();box->setCurrentText(value);
}void TComBoxDelegate::setModelData(QWidget* editor, QAbstractItemModel* model, const QModelIndex& index) const
{QComboBox* box = dynamic_cast<QComboBox*>(editor); //把editor转为指向QSpinBox的指针 动态强转QString value = box->currentText();model->setData(index, value);
}void TComBoxDelegate::updateEditorGeometry(QWidget* editor, const QStyleOptionViewItem& option, const QModelIndex& index) const
{editor->setGeometry(option.rect);
}void TComBoxDelegate::setItems(QStringList list, bool editabale)
{m_itemList = list;m_editable = editabale;
}

tcomboxdelegate.h

#ifndef TCOMBOXDELEGATE_H
#define TCOMBOXDELEGATE_H#include <QObject>
#include <QStyledItemDelegate>class TComBoxDelegate : public QStyledItemDelegate {Q_OBJECT
public:explicit TComBoxDelegate(QObject* parent = nullptr);// QAbstractItemDelegate interface
public:virtual QWidget* createEditor(QWidget* parent, const QStyleOptionViewItem& option, const QModelIndex& index) const override;virtual void setEditorData(QWidget* editor, const QModelIndex& index) const override;virtual void setModelData(QWidget* editor, QAbstractItemModel* model, const QModelIndex& index) const override;virtual void updateEditorGeometry(QWidget* editor, const QStyleOptionViewItem& option, const QModelIndex& index) const override;void setItems(QStringList list, bool editabale);private:QStringList m_itemList;bool m_editable;
};#endif // TCOMBOXDELEGATE_H

QSqlQueryModel模块使用