Qt QSqlTableModel详解

缺乏、安全感 2022-11-06 08:58 543阅读 0赞

# 1.QSqlTableModel #

用来显示数据库中数据表的数据，实现对数据的编辑、插入、删除等操作。实现数据的排序和过滤

# 2.常用API #

void setTable(const QString &tableName)  //设置数据表名称

void setFilter(const QString &filter)  //设置记录过滤条件

void setSort(int column,Qt::SortOrder order)  //设置排序字段和排序规则，需调用select()才生效

bool setHeaderData(int , Qt::Orientation , const QVariant &)  //设置表头

int fieldIndex(QString &fieldName)  //根据字段名称返回其在模型中的字段序号，若字段不存在返回-1

void select()  //查询数据表数据，并使用设置的排序和过滤规则

void clear()  //清除数据模型，释放所有获取的数据

QSqlRecord record()  //返回一条空记录，只有字段名，可用来获取字段信息

bool insertRecord(int row,QSqlRecord &values)  //在行号row之前插入一条记录

bool insertRows(int row,int count)  //在行号row之前插入count空行

bool removeRows(int row,int count)  //从行号row开始，删除count行。

bool submit()  //提交当前行的修改到数据库

bool submitAll()  //提交所有未更新的修改到数据库

**QSqlTableModel::setEditStrategy()函数用于设置编辑策略**

*  QSqlTableModel::OnFieldChange   //字段值变化时立即更新到数据库
 *  QSqlTableModel::OnRowChange    //当前行变换时更新到数据库
 *  QSqlTableModel::OnManualSubmit    //所有修改暂时缓存，手动调用submitAll()保存所有修改，或调用revertAll()取消所有未保存修改

# 3.示例 #

![20210310212602403.gif][]

### 3.1打开数据库文件 ###

void Widget::on_btnOpen_clicked()
    {
        QString file = QFileDialog::getOpenFileName(this,"选择数据库文件","",
                                                   "SQLite数据库(*.db *.db3)");
        if(file.isEmpty())
            return;
    
        m_db = QSqlDatabase::addDatabase("QSQLITE");
        m_db.setDatabaseName(file);
    
        if(!m_db.open())
        {
            qDebug()<<"打开失败";
            return;
        }
    
        initTableView();
    }
    
    void Widget::initTableView()
    {
        //数据表model
        m_model = new QSqlTableModel(this, m_db);
    
        //设置数据表
        m_model->setTable("students");
    
        //查询数据
        if(!m_model->select())
        {
            qDebug()<<"查询失败";
            return;
        }
    
        m_model->setHeaderData(m_model->fieldIndex("id"),Qt::Horizontal,"编号");
        m_model->setHeaderData(m_model->fieldIndex("name"),Qt::Horizontal,"姓名");
        m_model->setHeaderData(m_model->fieldIndex("sex"),Qt::Horizontal,"性别");
        m_model->setHeaderData(m_model->fieldIndex("age"),Qt::Horizontal,"年龄");
        m_model->setHeaderData(m_model->fieldIndex("addr"),Qt::Horizontal,"地址");
        m_model->setHeaderData(m_model->fieldIndex("height"),Qt::Horizontal,"身高");
    
        //设置模型
        ui->tableView->setModel(m_model);
    
        //设置性别代理
        ui->tableView->setItemDelegateForColumn(m_model->fieldIndex("sex"),&m_cbxDelegate);
    
        //设置年龄代理
        ui->tableView->setItemDelegateForColumn(m_model->fieldIndex("age"),&m_spinDelegate);
    
        //创建界面组件与数据模型的字段之间的数据映射
        m_dataMapper = new QDataWidgetMapper(this);
    
        //设置数据模型
        m_dataMapper->setModel(m_model);
    
        m_dataMapper->setSubmitPolicy(QDataWidgetMapper::AutoSubmit);
    
        //界面组件与tabModel的具体字段之间的联系
        m_dataMapper->addMapping(ui->spinBoxNum,m_model->fieldIndex("id"));
        m_dataMapper->addMapping(ui->lineEditName,m_model->fieldIndex("name"));
        m_dataMapper->addMapping(ui->cbxSex,m_model->fieldIndex("sex"));
        m_dataMapper->addMapping(ui->spinBoxAge,m_model->fieldIndex("age"));
        m_dataMapper->addMapping(ui->lineEditAddr,m_model->fieldIndex("addr"));
        m_dataMapper->addMapping(ui->doubleSpinBoxHeight,m_model->fieldIndex("height"));
    
        //移动到首记录
        m_dataMapper->toFirst();
    
    
        QSqlRecord record = m_model->record();
        for(int i=0;i<record.count();i++)
        {
            ui->cbxSort->addItem(record.fieldName(i));
        }
    }

### 3.2添加、删除 ###

void Widget::on_btnAdd_clicked()
    {
        int rowCount = m_model->rowCount();
        m_model->insertRow(rowCount);
    
        QModelIndex curIndex = m_model->index(m_model->rowCount()-1,1);
    
        int curRow = curIndex.row();
        //QModelIndex curIndex = m_model->index(m_model->rowCount()-1,1);
        m_model->setData(m_model->index(curRow,0),rowCount+1);
        m_model->setData(m_model->index(curRow,1),"马超");
        m_model->setData(m_model->index(curRow,2),"男");
        m_model->setData(m_model->index(curRow,3),27);
        m_model->setData(m_model->index(curRow,4),"蜀国");
        m_model->setData(m_model->index(curRow,5),1.76);
    }
    
    void Widget::on_btnDel_clicked()
    {
        QModelIndex curIndex = ui->tableView->currentIndex();
        m_model->removeRow(curIndex.row());
    }

### 3.3保存、取消 ###

void Widget::on_btnCancel_clicked()
    {
        m_model->revertAll();
    }
    
    void Widget::on_btnSave_clicked()
    {
        bool bRet = m_model->submitAll();
    }

### 3.4排序 ###

void Widget::on_rBUp_clicked()
    {
        m_model->setSort(ui->cbxSort->currentIndex(),Qt::AscendingOrder);
        m_model->select();
    }
    
    void Widget::on_rBDown_clicked()
    {
        m_model->setSort(ui->cbxSort->currentIndex(),Qt::DescendingOrder);
        m_model->select();
    }

### 3.5过滤 ###

void Widget::on_rBMan_clicked()
    {
        m_model->setFilter("sex='男'");
    }
    
    void Widget::on_rBWoman_clicked()
    {
        m_model->setFilter("sex='女'");
    }
    
    void Widget::on_rBAll_clicked()
    {
        m_model->setFilter("");
    }

### 3.6源码 ###

#include "widget.h"
    #include "ui_widget.h"
    #include <QFileDialog>
    #include <QDebug>
    #include <QSqlRecord>
    
    Widget::Widget(QWidget *parent) :
        QWidget(parent),
        ui(new Ui::Widget)
    {
        ui->setupUi(this);
    
        ui->tableView->verticalHeader()->setHidden(true);
    
        ui->tableView->setSelectionBehavior(QAbstractItemView::SelectRows);
    
        ui->tableView->setSelectionMode(QAbstractItemView::SingleSelection);
    
        ui->tableView->setAlternatingRowColors(true);
    
        this->resize(1000,600);
    
        ui->rBUp->setChecked(true);
    
        ui->rBAll->setChecked(true);
    }
    
    Widget::~Widget()
    {
        delete ui;
    }
    
    void Widget::initTableView()
    {
        //数据表model
        m_model = new QSqlTableModel(this, m_db);
    
        //设置数据表
        m_model->setTable("students");
    
        //查询数据
        if(!m_model->select())
        {
            qDebug()<<"查询失败";
            return;
        }
    
        m_model->setHeaderData(m_model->fieldIndex("id"),Qt::Horizontal,"编号");
        m_model->setHeaderData(m_model->fieldIndex("name"),Qt::Horizontal,"姓名");
        m_model->setHeaderData(m_model->fieldIndex("sex"),Qt::Horizontal,"性别");
        m_model->setHeaderData(m_model->fieldIndex("age"),Qt::Horizontal,"年龄");
        m_model->setHeaderData(m_model->fieldIndex("addr"),Qt::Horizontal,"地址");
        m_model->setHeaderData(m_model->fieldIndex("height"),Qt::Horizontal,"身高");
    
        //设置模型
        ui->tableView->setModel(m_model);
    
        //设置性别代理
        ui->tableView->setItemDelegateForColumn(m_model->fieldIndex("sex"),&m_cbxDelegate);
    
        //设置年龄代理
        ui->tableView->setItemDelegateForColumn(m_model->fieldIndex("age"),&m_spinDelegate);
    
        //创建界面组件与数据模型的字段之间的数据映射
        m_dataMapper = new QDataWidgetMapper(this);
    
        //设置数据模型
        m_dataMapper->setModel(m_model);
    
        m_dataMapper->setSubmitPolicy(QDataWidgetMapper::AutoSubmit);
    
        //界面组件与tabModel的具体字段之间的联系
        m_dataMapper->addMapping(ui->spinBoxNum,m_model->fieldIndex("id"));
        m_dataMapper->addMapping(ui->lineEditName,m_model->fieldIndex("name"));
        m_dataMapper->addMapping(ui->cbxSex,m_model->fieldIndex("sex"));
        m_dataMapper->addMapping(ui->spinBoxAge,m_model->fieldIndex("age"));
        m_dataMapper->addMapping(ui->lineEditAddr,m_model->fieldIndex("addr"));
        m_dataMapper->addMapping(ui->doubleSpinBoxHeight,m_model->fieldIndex("height"));
    
        //移动到首记录
        m_dataMapper->toFirst();
    
    
        QSqlRecord record = m_model->record();
        for(int i=0;i<record.count();i++)
        {
            ui->cbxSort->addItem(record.fieldName(i));
        }
    }
    
    
    void Widget::on_btnOpen_clicked()
    {
        QString file = QFileDialog::getOpenFileName(this,"选择数据库文件","",
                                                   "SQLite数据库(*.db *.db3)");
        if(file.isEmpty())
            return;
    
        m_db = QSqlDatabase::addDatabase("QSQLITE");
        m_db.setDatabaseName(file);
    
        if(!m_db.open())
        {
            qDebug()<<"打开失败";
            return;
        }
    
        initTableView();
    }
    
    void Widget::on_rBMan_clicked()
    {
        m_model->setFilter("sex='男'");
    }
    
    void Widget::on_rBWoman_clicked()
    {
        m_model->setFilter("sex='女'");
    }
    
    void Widget::on_rBAll_clicked()
    {
        m_model->setFilter("");
    }
    
    void Widget::on_rBUp_clicked()
    {
        m_model->setSort(ui->cbxSort->currentIndex(),Qt::AscendingOrder);
        m_model->select();
    }
    
    void Widget::on_rBDown_clicked()
    {
        m_model->setSort(ui->cbxSort->currentIndex(),Qt::DescendingOrder);
        m_model->select();
    }
    
    void Widget::on_cbxSort_currentIndexChanged(int index)
    {
        if(ui->rBUp->isChecked())
        {
            m_model->setSort(index,Qt::AscendingOrder);
        }
        else
        {
            m_model->setSort(index,Qt::DescendingOrder);
        }
        m_model->select();
    }
    
    void Widget::on_tableView_clicked(const QModelIndex &index)
    {
        m_dataMapper->setCurrentIndex(index.row());
    }
    
    void Widget::on_btnAdd_clicked()
    {
        int rowCount = m_model->rowCount();
        m_model->insertRow(rowCount);
    
        QModelIndex curIndex = m_model->index(m_model->rowCount()-1,1);
    
        int curRow = curIndex.row();
        //QModelIndex curIndex = m_model->index(m_model->rowCount()-1,1);
        m_model->setData(m_model->index(curRow,0),rowCount+1);
        m_model->setData(m_model->index(curRow,1),"马超");
        m_model->setData(m_model->index(curRow,2),"男");
        m_model->setData(m_model->index(curRow,3),27);
        m_model->setData(m_model->index(curRow,4),"蜀国");
        m_model->setData(m_model->index(curRow,5),1.76);
    }
    
    void Widget::on_btnDel_clicked()
    {
        QModelIndex curIndex = ui->tableView->currentIndex();
        m_model->removeRow(curIndex.row());
    }
    
    void Widget::on_btnCancel_clicked()
    {
        m_model->revertAll();
    }
    
    void Widget::on_btnSave_clicked()
    {
        bool bRet = m_model->submitAll();
    }

### 3.7完整工程 ###

[https://download.csdn.net/download/wzz953200463/15714400][https_download.csdn.net_download_wzz953200463_15714400]

[20210310212602403.gif]: /images/20221023/2d4055fd32424aa187dd713163ab2676.png
[https_download.csdn.net_download_wzz953200463_15714400]: https://download.csdn.net/download/wzz953200463/15714400