Qt QSqlQueryModel详解

1.功能概述

QSqlQueryModel是QSqlTableModel的父类。QSqlQueryModel封装了执行SELECT语句从数据库查询数据的功能，但是QSqlQueryModel只能作为只读数据源使用，不可以编辑数据。

QSqlTableModel详解

2.常用API

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

QSqlQuery query()  //返回当前关联的QSqlQuery()对象

void setQuery()  //设置一个QSqlQuery对象，获取数据

QSqlRecord record() //返回一个空记录，包含当前查询的字段信息

QSqlRecord record(int row) //返回行号为row的记录

QSqlQueryModel作为数据模型从数据库里查询数据，只需要使用setQuery()函数设置一个select查询语句即可。

3.QSqlQuery

QSqlQuery是能执行任意SQL语句的类，如select、insert、update、delete等。能和QSqlQueryModel一起联合使用。

4.示例

#ifndef WIDGET_H
#define WIDGET_H#include <QWidget>
#include <QSqlQueryModel>
#include <QSqlQuery>
#include <QSqlDatabase>
#include <QDataWidgetMapper>
#include "ComboBoxDelegate.h"
#include "SpinBoxDelegate.h"
#include <QModelIndex>namespace Ui {
class Widget;
}class Widget : public QWidget
{Q_OBJECTpublic:explicit Widget(QWidget *parent = 0);~Widget();private:void initTableView();private slots:void on_btnOpen_clicked();void on_tableView_clicked(const QModelIndex &index);void on_btnAdd_clicked();void on_btnDel_clicked();private:Ui::Widget *ui;private:QSqlQueryModel *m_model = nullptr;ComboBoxDelegate m_cbxDelegate;SpinBoxDelegate m_spinDelegate;QSqlDatabase m_db;QDataWidgetMapper *m_dataMapper = nullptr;
};#endif // WIDGET_H

#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);
}Widget::~Widget()
{delete ui;
}void Widget::initTableView()
{//数据表modelm_model = new QSqlQueryModel(this);//查询数据m_model->setQuery("select id,name,sex,age,addr,height from students");m_model->setHeaderData(0,Qt::Horizontal,"编号");m_model->setHeaderData(1,Qt::Horizontal,"姓名");m_model->setHeaderData(2,Qt::Horizontal,"性别");m_model->setHeaderData(3,Qt::Horizontal,"年龄");m_model->setHeaderData(4,Qt::Horizontal,"地址");m_model->setHeaderData(5,Qt::Horizontal,"身高");//设置模型ui->tableView->setModel(m_model);//设置性别代理ui->tableView->setItemDelegateForColumn(2,&m_cbxDelegate);//设置年龄代理ui->tableView->setItemDelegateForColumn(3,&m_spinDelegate);//创建界面组件与数据模型的字段之间的数据映射m_dataMapper = new QDataWidgetMapper(this);//设置数据模型m_dataMapper->setModel(m_model);m_dataMapper->setSubmitPolicy(QDataWidgetMapper::AutoSubmit);//界面组件与tabModel的具体字段之间的联系m_dataMapper->addMapping(ui->spinBoxNum,0);m_dataMapper->addMapping(ui->lineEditName,1);m_dataMapper->addMapping(ui->cbxSex,2);m_dataMapper->addMapping(ui->spinBoxAge,3);m_dataMapper->addMapping(ui->lineEditAddr,4);m_dataMapper->addMapping(ui->doubleSpinBoxHeight,5);//移动到首记录m_dataMapper->toFirst();
}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_tableView_clicked(const QModelIndex &index)
{m_dataMapper->setCurrentIndex(index.row());
}void Widget::on_btnAdd_clicked()
{QSqlQuery query;query.prepare("insert into students (id,name,sex,age,addr,height)""values(:ID,:Name,:Sex,:Age,:Addr,:Height)");query.bindValue(":ID",20);query.bindValue(":Name","马超");query.bindValue(":Sex","男");query.bindValue(":Age",27);query.bindValue(":Addr","蜀国");query.bindValue(":Height",1.76);if(!query.exec())return;
}void Widget::on_btnDel_clicked()
{QModelIndex curIndex = ui->tableView->currentIndex();//获取当前记录QSqlRecord curRec = m_model->record(curIndex.row());if(curRec.isEmpty())return;//获取idint id = curRec.value("id").toInt();QSqlQuery query;query.prepare("delete from students where id = :ID");query.bindValue(":ID",id);if(!query.exec())return;
}