记忆力减退之QTableMode与数据库关联

QSqlDatabase db;
QSqlTableModel *model;
 
void Widget::Init(){
    //判断数据库是否存在，如果存在直接打开，如果不存在则在打开时直接新建，需要手动新增表
    QString dbName = "myDatabase.db";
    QFileInfo info(dbName);
    bool isexists = info.exists();
 
    db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName(dbName);
    bool ok = db.open();//如果存在直接打开，如果不存在则在打开时直接新建
    if(ok){
        //创建表
        if(!isexists){
            QString sql = "create table student(studentID int, name text, "
                          "class text, chinese float, math float, "
                          "english float, physics float, sports float, total float);";
            QSqlQuery query(db);
            bool ret = query.exec(sql);
            if(!ret){
                QMessageBox::warning(this,"警告","创建数据库失败！",QMessageBox::Yes);
                return;
            }
        }
    }
    //数据库与table view相关联
    model = new QSqlTableModel(this, db);
    model->setTable("student");//表关联
    //修改表头别名
    model->setHeaderData(0, Qt::Horizontal, QVariant("学号"));
    model->setHeaderData(1, Qt::Horizontal, QVariant("姓名"));
    model->setHeaderData(2, Qt::Horizontal, QVariant("班级"));
    model->setHeaderData(3, Qt::Horizontal, QVariant("语文"));
    model->setHeaderData(4, Qt::Horizontal, QVariant("数学"));
    model->setHeaderData(5, Qt::Horizontal, QVariant("英语"));
    model->setHeaderData(6, Qt::Horizontal, QVariant("物理"));
    model->setHeaderData(7, Qt::Horizontal, QVariant("体育"));
    model->setHeaderData(8, Qt::Horizontal, QVariant("合计"));
    model->select();
    ui->tableViewShow->setModel(model);
}
 
void Widget::on_btnOK_clicked()
{
    model->submitAll();//确认提交
    model->select();
}
 
void Widget::on_btnAdd_clicked()
{
    model->insertRow(model->rowCount());//新增一行
    model->select();
}
 
void Widget::on_btnCancel_clicked()
{
    model->revertAll();//取消还原
    model->select();
}
 
void Widget::on_btnSelect_clicked()
{
    model->select();//刷新view显示
}
 
void Widget::on_btnAscending_clicked()
{
    QModelIndex index= ui->tableViewShow->currentIndex();
    model->sort(index.column(), Qt::AscendingOrder);//升序
    ui->tableViewShow->setCurrentIndex(index);
}
 
void Widget::on_btnDescending_clicked()
{
    QModelIndex index= ui->tableViewShow->currentIndex();
    model->sort(index.column(), Qt::DescendingOrder);//降序
    ui->tableViewShow->setCurrentIndex(index);
}
 
void Widget::on_btnDelete_clicked()
{
    int selectRow = ui->tableViewShow->currentIndex().row();
    model ->removeRow(selectRow);//删除当前行
    model->submitAll();
    model->select();
}
 
void Widget::on_btnAffair_clicked()
{
    //声明事务：删除一行
    model->database().transaction();
    int selectRow = ui->tableViewShow->currentIndex().row();
    model->removeRow(selectRow);
    bool ret = model->submitAll();
    if(ret){
        int ret = QMessageBox::information(this,"删除确认","是否确认删除当前行",
                                           QMessageBox::Yes | QMessageBox::No);
        if(ret == QMessageBox::No){
            model->database().rollback();//回滚取消事务
        }
        else{
            model->database().commit();//提交事务
        }
    }
    model->select();//刷新view显示
}