QSqlTableModel操作数据库单表使用总结

最新推荐文章于 2024-10-25 14:20:58 发布

castlooo

最新推荐文章于 2024-10-25 14:20:58 发布

阅读量1.3k

点赞数 21

文章标签：数据库 qt

本文链接：https://blog.csdn.net/castlooo/article/details/140543121

版权

本文记录使用QSqlTableModel等组件实现单表的数据库操作。

QSqlTableModel是一个模型类，它的实例可以作为一个数据表的模型。使用QSqlTableModel模型和QTableView组件构成模型/视图结构，就可以实现数据表的数据显示和编辑。

移动到首记录，将首行的记录赋值给绑定的控件

所需的类及定义

解释	类
数据库连接	QSqlDatabase
显示控件	QTableView
Model视图	QSqlTableModel
选择Model	QItemSelectionModel
字段编辑绑定	QDataWidgetMapper
自定义combox代理控件	TComboBoxDelegate
性别字符列表	genderList
部门字符列表	deptList
省份字符列表	provinceList

    QSqlDatabase DB;
    QSqlTableModel *tabModel;
    QItemSelectionModel *selModel;
    QDataWidgetMapper *dataMapper;//数据映射

    TComboBoxDelegate delegateSex;
    TComboBoxDelegate delegateDept;

    QStringList genderList;
    QStringList deptList;
    QStringList provinceList;

表格控件设置QTableView

行背景交替颜色、单个单元格选择模式

    ui->tableView->setSelectionBehavior(QAbstractItemView::SelectItems);
    ui->tableView->setSelectionMode(QAbstractItemView::SingleSelection);
    ui->tableView->setAlternatingRowColors(true);

打开数据库连接db.open()

打开SQLite数据库

bool SqlTableModelExample::openDataBase(QString aFile)
{
    DB = QSqlDatabase::addDatabase("QSQLITE");//添加SQLite驱动
    DB.setDatabaseName(aFile);//设置数据库名称
    if(!DB.open())//打开数据库
    {
        QMessageBox::warning(this, "错误", "打开数据库失败");
        return false;
    }
    else
    {
        openTable();
        return true;
    }
}

表设置初始化、表查询

设置表名

tabModel = new QSqlTableModel(this,DB);
tabModel->setTable("employee");

设置表排序字段

tabModel->setSort(tabModel->fieldIndex("empNo"),Qt::AscendingOrder);

打开表-查询

if(!tabModel->select())
    {
        //MessageBoxHelper::critical(this,"打开数据表错误，错误信息：\n"+tabModel->lastError().text());
        QMessageBox::critical(this,"错误信息","打开数据表错误，错误信息：\n"+tabModel->lastError().text());
        return;
    }

显示行数

void SqlTableModelExample::showRecordCount()
{
    ui->statusbar->showMessage(QString("记录条数：%1").arg(tabModel->rowCount()));
}

设置表格的水平字段标题

tabModel->setHeaderData(tabModel->fieldIndex("empNo"),Qt::Horizontal,"工号");
    tabModel->setHeaderData(tabModel->fieldIndex("Name"),Qt::Horizontal,"姓名");
    tabModel->setHeaderData(tabModel->fieldIndex("Gender"),Qt::Horizontal,"性别");
    tabModel->setHeaderData(tabModel->fieldIndex("Birthday"),Qt::Horizontal,"出生日期");
    tabModel->setHeaderData(tabModel->fieldIndex("Province"),Qt::Horizontal,"省份");
    tabModel->setHeaderData(tabModel->fieldIndex("Department"),Qt::Horizontal,"部门");
    tabModel->setHeaderData(tabModel->fieldIndex("Salary"),Qt::Horizontal,"薪水");
    //不在列表tabView中显示
    tabModel->setHeaderData(tabModel->fieldIndex("Memo"),Qt::Horizontal,"备注");
    tabModel->setHeaderData(tabModel->fieldIndex("Photo"),Qt::Horizontal,"照片");

创建选择模型

selModel = new QItemSelectionModel(tabModel,this);

将选择模型的单元格值变化事件、行变化/切换绑定到处理方法

如单元格值发生变化，将保存、撤销操作按钮变亮；行切换时，绑定的显示控件显示对应行的值

    connect(selModel,&QItemSelectionModel::currentChanged,this,&SqlTableModelExample::do_currentChanged);
    connect(selModel,&QItemSelectionModel::currentRowChanged,this,&SqlTableModelExample::do_currentRowChanged);

绑定数据模型和选择模型

    ui->tableView->setModel(tabModel);
    ui->tableView->setSelectionModel(selModel);

绑定之后，将图片列和备注列在表格中不显示

    ui->tableView->setColumnHidden(tabModel->fieldIndex("Memo"),true);
    ui->tableView->setColumnHidden(tabModel->fieldIndex("Photo"),true);

绑定自定义下拉框到部门列、性别列

 bool isEditable = false;
    delegateSex.setItems(genderList,isEditable);
    ui->tableView->setItemDelegateForColumn(tabModel->fieldIndex("Gender"),&delegateSex);


    isEditable = true;
    delegateDept.setItems(deptList,isEditable);
    ui->tableView->setItemDelegateForColumn(tabModel->fieldIndex("Department"),&delegateDept);

创建界面组件与数据模型字段之间的数据映射

定义数据映射组件

    dataMapper = new QDataWidgetMapper(this);
    dataMapper->setModel(tabModel);
    dataMapper->setModel(tabModel);   
    dataMapper->setSubmitPolicy(QDataWidgetMapper::AutoSubmit); //自动提交

设置界面组件和模型字段之间的映射

    dataMapper->addMapping(ui->dbSpinEmpNo,tabModel->fieldIndex("empNo"));
    dataMapper->addMapping(ui->dbEditName,tabModel->fieldIndex("Name"));
    dataMapper->addMapping(ui->dbComboSex,tabModel->fieldIndex("Gender"));
    dataMapper->addMapping(ui->dbEditBirth,tabModel->fieldIndex("Birthday"));
    dataMapper->addMapping(ui->dbComboProvince,tabModel->fieldIndex("Province"));
    dataMapper->addMapping(ui->dbComboDept,tabModel->fieldIndex("Department"));
    dataMapper->addMapping(ui->dbSpinSalary,tabModel->fieldIndex("Salary"));
    dataMapper->addMapping(ui->dbEditMemo,tabModel->fieldIndex("Memo"));

移动到首记录，将首行的记录赋值给绑定的控件

dataMapper->toFirst();

表插入

void SqlTableModelExample::on_actRecInsert_triggered()
{
    QModelIndex curIndex = ui->tableView->currentIndex();
    QSqlRecord rec = tabModel->record();//获取一个空记录，只有字段定义
    tabModel->insertRecord(curIndex.row(),rec);
    selModel->clearSelection();
    selModel->setCurrentIndex(curIndex,QItemSelectionModel::Select);
    showRecordCount();
}

表追加一行

void SqlTableModelExample::on_actRecAppend_triggered()
{
    QSqlRecord rec = tabModel->record();//获取一个空记录
    rec.setValue(tabModel->fieldIndex("empNo"),2000+tabModel->rowCount());
    rec.setValue(tabModel->fieldIndex("Gender"),"男");
    tabModel->insertRecord(tabModel->rowCount(),rec);//插入到数据模型的最后

    selModel->clearSelection();
    QModelIndex curIndex = tabModel->index(tabModel->rowCount()-1,1);
    selModel->setCurrentIndex(curIndex,QItemSelectionModel::Select);
    showRecordCount();
}

表操作撤销

void SqlTableModelExample::on_actRevert_triggered()
{
    tabModel->revertAll();
    ui->actSubmit->setEnabled(false);
    ui->actRevert->setEnabled(false);
    showRecordCount();
}

表操作提交

void SqlTableModelExample::on_actSubmit_triggered()
{
    bool res = tabModel->submitAll();
    if(!res)
    {
        QMessageBox::critical(this,"错误提示","数据保存错误，错误信息："+tabModel->lastError().text());
        return;
    }
    else
    {
        ui->actSubmit->setEnabled(false);
        ui->actRevert->setEnabled(false);
    }
    showRecordCount();
}

表记录删除

void SqlTableModelExample::on_actRecDelete_triggered()
{
    QModelIndex curIndex = selModel->currentIndex();
    tabModel->removeRow(curIndex.row());
    showRecordCount();
}

表排序切换

void SqlTableModelExample::on_radioBtnDescend_clicked()
{
    tabModel->setSort(ui->comboFields->currentIndex(),Qt::DescendingOrder);
    //tabModel->setSort(ui->comboFields->currentIndex(),Qt::AscendingOrder);
    tabModel->select();//执行刷新 才会显示排序
}

//切换成别的字段排序
void SqlTableModelExample::on_comboFields_currentIndexChanged(int index)
{
    if(ui->radioBtnAescend->isChecked())
        tabModel->setSort(index,Qt::AscendingOrder);
    else
        tabModel->setSort(index,Qt::DescendingOrder);

    tabModel->select();
}

表条件过滤及过滤条件清空

void SqlTableModelExample::on_radioBtnMan_clicked()
{
    tabModel->setFilter(" Gender='男");
    showRecordCount();
}

void SqlTableModelExample::on_radioBtnBoth_clicked()
{
    tabModel->setFilter("");
    showRecordCount();
}

逐行修改数据并提交

void SqlTableModelExample::on_actScan_triggered()
{
    if(tabModel->rowCount()==0)
        return;
    for(int i=0;i<tabModel->rowCount();i++)
    {
        QSqlRecord rec = tabModel->record(i);
        float salary = rec.value("Salary").toFloat();
        rec.setValue("Salary",salary*1.1);
        tabModel->setRecord(i,rec);
    }

    if(tabModel->submitAll())
        QMessageBox::information(this,"提示","计算完毕");
}

BLOB字段的保存、清空、显示图片

void SqlTableModelExample::on_actPhoto_triggered()
{
    QString aFile = QFileDialog::getOpenFileName(this,"选择图片文件","","照片(*.jpg)");
    if(aFile.isEmpty())
        return;
    QByteArray data;
    QFile *file = new QFile(aFile);
    file->open(QIODevice::ReadOnly);
    data = file->readAll();
    file->close();
    delete file;

    int curRecNo = selModel->currentIndex().row();
    QSqlRecord curRec= tabModel->record(curRecNo);
    curRec.setValue("Photo",data);
    tabModel->setRecord(curRecNo,curRec);

    QPixmap pic;
    pic.load(aFile);
    ui->dbLabPhoto->setPixmap(pic.scaledToWidth(ui->dbLabPhoto->size().width()));;
}

通过tableModel.SubmitAll() 提交

表事件处理

单元格值发生变化处理事件

判断是否更改 isDirty()方法

//数据发生更改的时候，更新actPost 和 actCancel的状态
void SqlTableModelExample::do_currentChanged(const QModelIndex &current, const QModelIndex &previous)
{
    Q_UNUSED(current);
    Q_UNUSED(previous);
    ui->actSubmit->setEnabled(tabModel->isDirty());
    ui->actRevert->setEnabled(tabModel->isDirty());
}

焦点行发生变换

切换dataMapper

void SqlTableModelExample::do_currentRowChanged(const QModelIndex &current, const QModelIndex &previous)
{
    Q_UNUSED(previous);
    ui->actRecDelete->setEnabled(current.isValid());
    ui->actPhoto->setEnabled(current.isValid());
    ui->actPhotoClear->setEnabled(current.isValid());
    if(!current.isValid())
    {
        ui->dbLabPhoto->clear();
        return;
    }
    int curRecNo = current.row();
    dataMapper->setCurrentIndex(curRecNo);
    QSqlRecord curRec = tabModel->record(curRecNo);//获取当前记录
    if(curRec.isNull("Photo"))
        ui->dbLabPhoto->clear();
    else
    {
        QByteArray data =curRec.value("Photo").toByteArray();
        QPixmap pic;
        pic.loadFromData(data);
        ui->dbLabPhoto->setPixmap(pic.scaledToWidth(ui->dbLabPhoto->size().width()));
    }
}

表其他操作

获取水平列名

void SqlTableModelExample::getFieldNames()
{
    QSqlRecord emptyRec = tabModel->record();
    for(int i=0;i<emptyRec.count();i++)
    {
        qDebug()<<emptyRec.count();
        ui->comboFields->addItem(emptyRec.fieldName(i));
    }
}