Qt QSqlTableModel表格模型，可以在表格里增删改查

connection.h

#ifndef CONNECTION_H
#define CONNECTION_H
#include <QMessageBox>
#include <QSqlDatabase>
#include <QSqlQuery>
#include <QCoreApplication>
static bool createConnection()
{
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    QString dataPath = QCoreApplication::applicationDirPath()+"/my.db";
    db.setDatabaseName(dataPath);
    if(!db.open())
    {
        QMessageBox::critical(0,"cannot open database","Unable to establish a database connection.",
                              QMessageBox::Cancel);
        return false;
    }
    QSqlQuery query;
    query.exec("create table student(id int primary key,name varchar,course int)");
    query.exec("insert into student values(1,'李强',11)");
    query.exec("insert into student values(2,'马亮',11)");
    query.exec("insert into student values(3,'孙红',12)");
    //创建course表
    query.exec("create table coures(id int primary key,name varchar,teacher varchar)");
    query.exec("insert into course values(10,'数学','王老师')");
    query.exec("insert into course values(10,'英语','张老师')");
    query.exec("insert into course values(10,'计算机','白老师')");
    return true;
}

#endif // CONNECTION_H

main.cpp

#include "mainwindow.h"
#include <QApplication>
#include "connection.h"


int main(int argc, char *argv[])
{
    QApplication a(argc, argv);
    if(!createConnection()) return 1;

    MainWindow w;
    w.show();

    return a.exec();
}

mainWindow.h

#ifndef MAINWINDOW_H
#define MAINWINDOW_H

#include <QMainWindow>

namespace Ui {
class MainWindow;
}

class QSqlTableModel;

class MainWindow : public QMainWindow
{
    Q_OBJECT

public:
    explicit MainWindow(QWidget *parent = 0);
    ~MainWindow();

private slots:
    void on_pushButton_clicked();

    void on_pushButton_2_clicked();

    void on_pushButton_7_clicked();

    void on_pushButton_8_clicked();

    void on_pushButton_5_clicked();

    void on_pushButton_6_clicked();

    void on_pushButton_4_clicked();

    void on_pushButton_3_clicked();

private:
    Ui::MainWindow *ui;
    QSqlTableModel*  model;
};

#endif // MAINWINDOW_H

mainwindow.cpp

#include "mainwindow.h"
#include "ui_mainwindow.h"
#include <QSqlQueryModel>
#include <QSqlTableModel>
#include <QSqlRelationalTableModel>
#include <QTableView>
#include <QDebug>
#include <QMessageBox>
#include <QSqlError>
#include "connection.h"

MainWindow::MainWindow(QWidget *parent) :
    QMainWindow(parent),
    ui(new Ui::MainWindow)
{
    ui->setupUi(this);
    model = new QSqlTableModel(this);
    model->setTable("student");
    model->select();
    //设置编辑策略OnFieldChange:所有对模型的改变都会立即应用到数据库；OnRowChange:对一条记录的改变会在用户选择另一条记录时被应用；
    //OnManualSubmit所有改变都会在模型中进行缓存，知道调用submitAll()或revertAll()
    model->setEditStrategy(QSqlTableModel::OnManualSubmit);
    ui->tableView->setModel(model);
}

MainWindow::~MainWindow()
{
    delete ui;
}

//提交修改按钮
void MainWindow::on_pushButton_clicked()
{
    //开始事务操作
    model->database().transaction();
    if(model->submitAll())
    {
        if(model->database().commit())//提交
        {
           QMessageBox::information(this,tr("tableModel"),tr("数据库修改成功！"));
        }
        else
        {
            model->database().rollback();//回滚
            QMessageBox::warning(this,tr("tableModel"),tr("数据库错误：%1").arg(model->lastError().text()),QMessageBox::Ok);
        }
    }
}

//撤销修改，只能撤销未提交的修改
void MainWindow::on_pushButton_2_clicked()
{
    model->revertAll();
}

//查询按钮，就行筛选
void MainWindow::on_pushButton_7_clicked()
{
    QString name = ui->lineEdit->text();
    //根据姓名进行筛选，一定要使用单引号
    model->setFilter(QString("name='%1'").arg(name));
    model->select();
}

//显示全表
void MainWindow::on_pushButton_8_clicked()
{
    model->setTable("student");
    model->select();
}

//按id升序排列
void MainWindow::on_pushButton_5_clicked()
{
    model->setSort(0,Qt::AscendingOrder);
    model->select();
}
//按id降序排列
void MainWindow::on_pushButton_6_clicked()
{
    model->setSort(0,Qt::DescendingOrder);
    model->select();
}
//删除选中行
void MainWindow::on_pushButton_4_clicked()
{
    //获取选中的行
    int curRow = ui->tableView->currentIndex().row();
    //删除改行
    model->removeRow(curRow);
    int ok = QMessageBox::warning(this,QString::fromLocal8Bit("删除当前行"),QString::fromLocal8Bit("你确定删除当前行吗?"),QMessageBox::Yes,QMessageBox::No);
    //QMessageBox::warning(this,tr("删除当前行"),tr("你确定删除当前行吗?"),QMessageBox::Yes,QMessageBox::No);
    if(ok==QMessageBox::No)
    {
        model->revertAll();//如果不删除，则撤销
    }
    else
    {
        model->submitAll();//否则提交，在数据库中删除该行
    }
}

//添加记录按钮
void MainWindow::on_pushButton_3_clicked()
{
    //获得表的行数
    int rowNum = model->rowCount();
    int id = 10;
    //添加一行
    model->insertRow(rowNum);
    model->setData(model->index(rowNum,0),id);
    //可以直接提交
    model->submitAll();
}

mainwindow