QSql 操作

连接 数据库

 QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
    db.setHostName("127.0.0.1");
    db.setUserName("root");
    db.setPassword("iamxgl");
    db.setDatabaseName("d0718");
    bool bRet = db.open();
    if(bRet == false)
    {
        qDebug() << "error open database" << db.lastError().text();
        exit(0);
    }

添加

  // QSqlTableModel: 数据表对应的数据结构
    QSqlTableModel model;
    model.setTable("tuser");
    //model.setFilter("username<>'newuser'");
    model.select();  // exec query
    int ret = model.rowCount();

    // read data from database
    for(int i=0; i<ret; ++i)
    {
       QSqlRecord record = model.record(i);
       for(int j=0; j<record.count(); j++)
       {
           qDebug() << record.value(j);
       }
    }

    // update data to database
 //   model.setData(model.index(0, 1), "xxxxxx");
  //  model.submitAll();

    // insert data to database
    QSqlRecord record = model.record();
//    record.setValue("id", );
    record.setValue("username", "newuser1");
    record.setValue("password", "newpassword");
    record.setValue("gender", 1);
    model.insertRecord(-1, record);
    model.submitAll();

del

Widget03::Widget03(QWidget *parent) :
    QWidget(parent)
{
    _model = new QSqlTableModel;
    _model->setTable("tuser");
    _model->select();
   _model->setEditStrategy(QSqlTableModel::OnManualSubmit);

    _model->setHeaderData(0, Qt::Horizontal, "编号");
    _model->setHeaderData(1, Qt::Horizontal, "用户名");
    _model->setHeaderData(2, Qt::Horizontal, "密码");
    _model->setHeaderData(3, Qt::Horizontal, "性别");

    _view = new QTableView;
    _view->setModel(_model);
//    _view->hideColumn(2);

    QVBoxLayout* lay = new QVBoxLayout(this);
    lay->addWidget(_view);

    QHBoxLayout* hBox = new QHBoxLayout;
    lay->addLayout(hBox);
    hBox->addStretch();

    QPushButton* del = new QPushButton("del");
    connect(del, SIGNAL(clicked()), this, SLOT(slotDelClicked()));
    hBox->addWidget(del);

    QPushButton* submit = new QPushButton("submit");
    connect(submit, SIGNAL(clicked()), this, SLOT(slotSubmitClicked()));
    hBox->addWidget(submit);

    QPushButton* add = new QPushButton("add");
    connect(add, SIGNAL(clicked()), this, SLOT(slotAddClicked()));
    hBox->addWidget(add);
}
void Widget03::slotAddClicked()
{
    // start transaction
    _model->database().transaction();

    QSqlRecord record = _model->record();
    _model->insertRecord(-1, record);
}
void Widget03::slotDelClicked()
{
    // 通过_view去获取被选中的部分的数据model
    QItemSelectionModel * selectModel = _view->selectionModel();
// 通过选中的数据结构，获取这些格子的ModelIndex
    QModelIndexList selectList =  selectModel->selectedIndexes();
    QList<int> delRow;

    // 遍历这些格子，获取格子所在行，因为可能存在相同的行，所以要去重
    for(int i=0; i<selectList.size(); ++i)
    {
        QModelIndex index = selectList.at(i);
      //  _model->removeRow(index.row());
        delRow << index.row();
    }

    while(delRow.size() > 0)
    {
        int row = delRow.at(0);
        delRow.removeAll(row);
        _model->removeRow(row);
    }

    _model->submitAll();

}


void Widget03::slotSubmitClicked()
{
    if(!_model->submitAll())
    {
        QMessageBox::critical(this, "Error", QSqlDatabase().lastError().text());
        _model->database().rollback();
    }
    else
    {
        _model->database().commit();
    }
}

1. void Widget::on_pushButton_5_clicked() //升序  
2. {  
3.     model->setSort(0,Qt::AscendingOrder); //id属性，即第0列，升序排列  
4.     model->select();  
5. }  
6. void Widget::on_pushButton_6_clicked() //降序  
7. {  
8.     model->setSort(0,Qt::DescendingOrder);  
9.     model->select();  
10. }