C++ QSqlTableModel::record方法代码示例

//.........这里部分代码省略.........
    QSqlQuery query;
    query.exec("UPDATE employee SET salary = 70000 WHERE id = 1003");
//! [37]
    }

    {
    // DELETE1
//! [38]
    QSqlQuery query;
    query.exec("DELETE FROM employee WHERE id = 1007");
//! [38]
    }

    {
    // TRANSACTION
//! [39]
    QSqlDatabase::database().transaction();
    QSqlQuery query;
    query.exec("SELECT id FROM employee WHERE name = 'Torild Halvorsen'");
    if (query.next()) {
        int employeeId = query.value(0).toInt();
        query.exec("INSERT INTO project (id, name, ownerid) "
                   "VALUES (201, 'Manhattan Project', "
                   + QString::number(employeeId) + ')');
    }
    QSqlDatabase::database().commit();
//! [39]
    }

    {
    // SQLQUERYMODEL1
//! [40]
    QSqlQueryModel model;
    model.setQuery("SELECT * FROM employee");

    for (int i = 0; i < model.rowCount(); ++i) {
        int id = model.record(i).value("id").toInt();
        QString name = model.record(i).value("name").toString();
        qDebug() << id << name;
    }
//! [40]
    }

    {
    // SQLTABLEMODEL1
//! [41]
    QSqlTableModel model;
    model.setTable("employee");
    model.setFilter("salary > 50000");
    model.setSort(2, Qt::DescendingOrder);
    model.select();

    for (int i = 0; i < model.rowCount(); ++i) {
        QString name = model.record(i).value("name").toString();
        int salary = model.record(i).value("salary").toInt();
        qDebug() << name << salary;
    }
//! [41]
    }

    {
    // SQLTABLEMODEL2
    QSqlTableModel model;
    model.setTable("employee");

//! [42]
    for (int i = 0; i < model.rowCount(); ++i) {
        QSqlRecord record = model.record(i);
        double salary = record.value("salary").toInt();
        salary *= 1.1;
        record.setValue("salary", salary);
        model.setRecord(i, record);
    }
    model.submitAll();
//! [42]

    // SQLTABLEMODEL3
    int row = 1;
    int column = 2;
//! [43]
    model.setData(model.index(row, column), 75000);
    model.submitAll();
//! [43]

    // SQLTABLEMODEL4
//! [44]
    model.insertRows(row, 1);
    model.setData(model.index(row, 0), 1013);
    model.setData(model.index(row, 1), "Peter Gordon");
    model.setData(model.index(row, 2), 68500);
    model.submitAll();
//! [44]

//! [45]
    model.removeRows(row, 5);
//! [45] //! [46]
    model.submitAll();
//! [46]
    }
}

void loginFrame::readRegistryData()
{
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName("Registry.db");
    if(!db.open())
        return;
    //从数据库中读取ip
    //从数据库中读取自动登录的id，并自动登录
    QSqlTableModel *model3 = new QSqlTableModel();
    model3->setTable("serverip");
    if(model3->select())                //如果userlist表存在
    {
        QSqlRecord re = model3->record(0);
        QString serverip = re.value("ip").toString();
        ui->lineEdit_2->setText(serverip);
    }
    /////////////////////////////////
    //从数据库中读取自动登录的id，并自动登录
    QSqlTableModel *model2 = new QSqlTableModel();
    model2->setTable("autologin");
    QString autoid;
    if(!model2->select())                //如果userlist表不存在，则返回
        autoid = "0";
    QSqlRecord re = model2->record(0);
    autoid = re.value("id").toString();
    QString autoPasw = " ";
    int autoIndex = -1;
//    QTableView *view = new QTableView(this);
//    view->setModel(model2);
//    view->show();

    //从数据库中读取账号信息
    QSqlTableModel *model = new QSqlTableModel();
    model->setTable("userlist");
    if(!model->select())                //如果userlist表不存在，则返回
        return;
//    QTableView *view2 = new QTableView(this);
//    view2->setModel(model);
//    view2->show();
    int count = 0;
    for(int i=0;i<model->rowCount();i++)
    {
        QSqlRecord rec = model->record(i);
        QString id = rec.value("id").toString();
        QString username = QString::fromLocal8Bit(rec.value("username").toByteArray());
        QString password = QString::fromLocal8Bit(rec.value("password").toByteArray());
        UserInfo user;
        user.id = id;
        user.password = password;
        user.username = username;
        RegistyShowID.append(user);
        count++;
        ui->comboBox->insertItem(count,QIcon(":/images/head.png"),id);
        if(id == autoid)                 //找出自动登录id的index
        {
            autoIndex = i;
            autoPasw = password;
        }
    }
    if(autoid != " " && autoIndex >=0)   //若为" "，则没有自动登录的id
    {
        ui->comboBox->setCurrentIndex(autoIndex);
        if(autoPasw != " ")
            ui->lineEdit_Password->setText(autoPasw);
        this->isauto = true;             //自动登录
    }
}

void EditColumnFormatsDialog::editItem(EditorType type) {
    if (type == EditItem && selectionIsEmpty()) {
        selectionIsEmptyWarning();
        return;
    }
    QModelIndex index = listView->currentIndex();
    QSqlRecord record = model_->record(index.row());
    int id = record.value(ColumnFormat_Id).toInt();
    QSqlQuery query(QString("SELECT Intervals.Name FROM ColumnFormats "
                            "JOIN ColumnFormatInterval USING (ColumnFormatID) "
                            "JOIN Intervals USING (IntervalID) "
                            "WHERE ColumnFormatInterval.ColumnFormatID = %1 "
                            "ORDER BY SortOrder").arg(id));
    if (!query.isActive()) {
        QMessageBox::warning(this, tr("Database Error"),
                             query.lastError().text());
        return;
    }
    QList<EpCore::Interval> intervals;
    while (query.next()) {
        QString value = query.value(0).toString();
        intervals.append(value);
    }
    EditColumnFormatDialog d(type, this);
    if (type == EditItem) {
        QString columnFormatName = record.value(ColumnFormat_Name).toString();
        EpCore::ColumnFormat columnFormat(columnFormatName, intervals);
        d.setColumnFormat(columnFormat);
    }
    else if (type == NewItem) {
        EpCore::ColumnFormat columnFormat;
        d.setColumnFormat(columnFormat);
    }
    if (d.exec() == QDialog::Accepted) {
        EpCore::ColumnFormat columnFormat = d.columnFormat();
        if (type == EditItem)
            removeIntervals();
        int row = index.row();
        if (type == NewItem)
            model_->insertRows(row, 1);
        model_->setData(model_->index(row, ColumnFormat_Name), 
                        columnFormat.name());
        model_->submitAll();
        model_->select();
        QSqlTableModel updateModel;
        updateModel.setTable("ColumnFormats");
        updateModel.setFilter(QString("Name = '%1'").arg(columnFormat.name()));
        updateModel.select();
        int newId = 0;
        QString newName;
        if (updateModel.rowCount() >= 1) {
            QSqlRecord record = updateModel.record(0);
            newId = record.value(0).toInt();
            newName = record.value(1).toString();
        }
        QList<EpCore::Interval> intervals = columnFormat.intervals();
        QListIterator<EpCore::Interval> iter(intervals);
        int i = 0;
        QSqlDatabase::database().transaction();
        while (iter.hasNext()) {
            QSqlQuery lookup(QString("SELECT IntervalID FROM Intervals "
                                     "WHERE Name = '%1'")
                             .arg(iter.next().name()));
            int lookupId;
            while (lookup.next()) {
                lookupId = lookup.value(0).toInt();
                QSqlQuery insertQuery(QString("INSERT INTO "
                                              "ColumnFormatInterval "
                                              "(ColumnFormatID, "
                                              "IntervalID, SortOrder) "
                                              "VALUES (%1, %2, %3)")
                                      .arg(newId).arg(lookupId).arg(i++));
            }
        }
        QSqlDatabase::database().commit();
    }
}

int IDResource::getSingleIdRes(QString& idType)
{
	QSqlTableModel model;
	model.setTable(idType);
	QString filter1="";
	int minNum,maxNum;

	if (idType==tr("ACLID"))
	{
		filter1=QString("ID>=%1 and ID<=%2").arg(g_minACLID).arg(g_maxACLID);
		minNum=g_minACLID;
		maxNum=g_maxACLID;
	}
	else if (idType==tr("XACLID"))
	{
		filter1=QString("ID>=%1 and ID<=%2").arg(g_minXACLID).arg(g_maxXACLID);
		minNum=g_minXACLID;
		maxNum=g_maxXACLID;
	}
	else if (idType==tr("PLID"))
	{
		filter1=QString("ID>=%1 and ID<=%2").arg(g_minPLID).arg(g_maxPLID);
		minNum=g_minPLID;
		maxNum=g_maxPLID;
	}
	
	model.setFilter(filter1);
	model.setSort( 0, Qt::AscendingOrder);
	model.select();
		
	QSet<int>idSet;
	
	for(int j=0;j<model.rowCount();++j)
	{
		QSqlRecord record=model.record(j);
		idSet<<record.value("id").toInt();
	}
	
	QQueue<int>queueId;
	
	for(int j=minNum;j!=maxNum+1;++j)
	{
		if(!idSet.contains(j))
		{
			queueId.enqueue(j);
		}
	}
	
	idSet.clear();
	
	if(queueId.count()<=0)
	{
		QMessageBox::information(this, tr("information"), tr("无可用ID资源"));
	}

	while(true)
	{
		int resId=queueId.dequeue();
	    if(!idConflict(resId,idType))
			return resId;
	}
	
	return 0;
}

void estore::on_confirm_clicked()
{

    int num = ui->num->text().toInt();
    QSqlTableModel model;
    model.setTable("commodity");
    model.setFilter(tr("number = '%1'").arg(num));
    model.select();
    if(model.rowCount()==1)//查询到有一个结果
    {
        QString name =model.record(0).value("name").toString();
        QString description =model.record(0).value("description").toString();
        double primeprice = model.record(0).value("primeprice").toDouble();
        double saleprice = model.record(0).value("saleprice").toDouble();
        int column = model.record(0).value("column").toInt();
        int sum = model.record(0).value("sum").toInt();
        double discount = model.record(0).value("discount").toDouble();
        QString publishmentbrand =model.record(0).value("publishmentbrand").toString();
        QString ISBNmaterialcolumn =model.record(0).value("ISBNmaterialcolumn").toString();
        QString date =model.record(0).value("date").toString();
        QString pagenumbersizeweight =model.record(0).value("pagenumbersizeweight").toString();
        QString si = ui->sum->text();
        int suminput= si.toInt();
        int numflag1=1;
        for(int i=0;i<si.size();i++)
            if ((si[i]<'0')||(si[i]>'9'))
            {
                numflag1=0;
                break;
            }
        if(si.size()==0)
        {
            QMessageBox::critical(NULL, "错误","数量未填写！");
        }
        else if(numflag1==0)
        {
            QMessageBox::critical(NULL, "错误","数量填写格式错误！");

        }


        else if(suminput > sum )
        {
            QMessageBox::warning(NULL, tr("错误"), tr("输入商品数量大于库存！"));
        }
        else
        {
            confirm c(name, suminput);
            if(c.exec()==QDialog::Accepted)
            {

                if (addTrolley(num, name, description, primeprice, saleprice,column, suminput, discount, publishmentbrand, ISBNmaterialcolumn, date, pagenumbersizeweight))
                {
                    model.setData(model.index(0,6),sum-suminput);
                    model.submitAll();
                    QMessageBox::information(NULL, tr("成功"), tr("商品成功添加至购物车！"));
                    accept();
                }
            }

        }

    }else
    {
        QMessageBox::warning(NULL, tr("错误"), tr("未查询到商品！"));
    }
}

bool DocumentSaverDB::saveDocument(KraftDoc *doc )
{
    bool result = false;
    if( ! doc ) return result;

    QSqlTableModel model;
    model.setTable("document");

    QSqlRecord record;

    kDebug() << "############### Document Save ################" << endl;

    if( doc->isNew() ) {
        record = model.record();
    } else {
      model.setFilter("docID=" + doc->docID().toString());
      model.select();
      if ( model.rowCount() > 0 ) {
        record = model.record(0);
      } else {
        kError() << "Could not select document record" << endl;
        return result;
      }
       // The document was already saved.
    }

    if( !doc->isNew() && doc->docTypeChanged() && doc->newIdent() ) {
        // an existing doc has a new document type. Fix the doc number cycle and pick a new ident
        DocType dt( doc->docType() );
        QString ident = dt.generateDocumentIdent( doc );
        doc->setIdent( ident );
    }

    fillDocumentBuffer( record, doc );

    if( doc->isNew() ) {
      kDebug() << "Doc is new, inserting" << endl;
      if( !model.insertRecord(-1, record)) {
          QSqlError err = model.lastError();
          kDebug() << "################# SQL Error: " << err.text();
      }
      model.submitAll();

      dbID id = KraftDB::self()->getLastInsertID();
      doc->setDocID( id );

      // get the uniq id and write it into the db
      DocType dt( doc->docType() );
      QString ident = dt.generateDocumentIdent( doc );
      doc->setIdent( ident );
      model.setFilter("docID=" + id.toString());
      model.select();
      if ( model.rowCount() > 0 ) {
        model.setData(model.index(0, 1), ident);
        model.submitAll();
      }

    } else {
      kDebug() << "Doc is not new, updating #" << doc->docID().intID() << endl;

      record.setValue( "docID", doc->docID().toString() );

      model.setRecord(0, record);
      model.submitAll();
    }

    saveDocumentPositions( doc );

    kDebug() << "Saved document no " << doc->docID().toString() << endl;

    return result;
}

void DocumentSaverDB::saveDocumentPositions( KraftDoc *doc )
{
    DocPositionList posList = doc->positions();

    // invert all pos numbers to avoid a unique violation
    // FIXME: We need non-numeric ids
    QSqlQuery upq;
    QString queryStr = "UPDATE docposition SET ordNumber = -1 * ordNumber WHERE docID=";
    queryStr +=  doc->docID().toString();
    queryStr += " AND ordNumber > 0";
    upq.prepare( queryStr );
    upq.exec();

    int ordNumber = 1;

    QSqlTableModel model;
    model.setTable("docposition");
    model.setEditStrategy(QSqlTableModel::OnManualSubmit);

    QVector<int> deleteIds;

    DocPositionListIterator it( posList );
    while( it.hasNext() ) {
        DocPositionBase *dpb = it.next();

        DocPosition *dp = static_cast<DocPosition*>(dpb);
        QSqlRecord record ;
        bool doInsert = true;

        int posDbID = dp->dbId().toInt();
        kDebug() << "Saving Position DB-Id: " << posDbID << endl;

        if( dp->toDelete() ) {
            kDebug() << "Delete item " << dp->dbId().toString() << endl;

            // store the id to delete, rather than killing the model index.
            // did that before here, which removed wrong items.
            deleteIds << posDbID;

            // delete all existing attributes no, which will not disturb the model index
            dp->attributes().dbDeleteAll( dp->dbId() );
            continue;
        }

        if( posDbID > -1 ) {
            const QString selStr = QString("docID=%1 AND positionID=%2").arg( doc->docID().toInt() ).arg( posDbID );
            // kDebug() << "Selecting with " << selStr << endl;
            model.setFilter( selStr );
            model.select();
            if ( model.rowCount() > 0 ) {
                if( ! dp->toDelete() )
                    record = model.record(0);
                doInsert = false;
            } else {
                kError() << "ERR: Could not select document position record" << endl;
                return;
            }
        } else {
            // The record is new
            record = model.record();
        }

        if( record.count() > 0 ) {
            // kDebug() << "Updating position " << dp->position() << " is " << dp->text() << endl;
            QString typeStr = PosTypePosition;
            double price = dp->unitPrice().toDouble();

            if ( dp->type() == DocPositionBase::ExtraDiscount ) {
                typeStr = PosTypeExtraDiscount;
            }

            record.setValue( "docID",     QVariant(doc->docID().toInt()));
            record.setValue( "ordNumber", QVariant(ordNumber));
            record.setValue( "text",      QVariant(dp->text()));
            record.setValue( "postype",   QVariant(typeStr));
            record.setValue( "amount",    QVariant(dp->amount()));
            int unitId = dp->unit().id();
            record.setValue( "unit",      QVariant(unitId));
            record.setValue( "price",     QVariant(price));
            record.setValue( "taxType",   QVariant(dp->taxType()));

            ordNumber++; // FIXME

            if( doInsert ) {
                kDebug() << "Inserting!" << endl;
                model.insertRecord(-1, record);
                model.submitAll();
                dp->setDbId( KraftDB::self()->getLastInsertID().toInt() );
            } else {
                kDebug() << "Updating!" << endl;
                model.setRecord(0, record);
                model.submitAll();
            }
        } else {
            kDebug() << "ERR: No record object found!" << endl;
        }

        dp->attributes().save( dp->dbId() );

        QSqlError err = model.lastError();
//.........这里部分代码省略.........

void event::on_confirm_clicked()
{
    QString discount = ui->discount->text();

    int numflag1=1,a=1;
    for(int i=0;i<discount.size();i++)
        if ((discount[i]<'0')||(discount[i]>'9'))
        {
            numflag1=0;
            break;
        }
    if(numflag1==0)
    {
        QMessageBox::critical(NULL, "警告","折扣输入格式有误！");
        a=0;
    }
    float dis = discount.toFloat();

    if(dis<1||dis>100)
    {
        QMessageBox::critical(NULL, "警告","折扣输入格式有误！");
        a=0;
    }
    float d =1-dis/100;
    if(ui->book->isChecked())
    {
        QSqlTableModel model;
        model.setTable("commodity");
        model.setFilter(QObject::tr("column = 1 ")); //根据类别进行筛选
        model.select(); //显示结果
        for (int i = 0; i < model.rowCount(); i++)
        {
            qDebug()<<model.record(i);
            qDebug()<<d;
            model.setData(model.index(i,7),d);
            double p= model.record(i).value("primeprice").toFloat();
            double a = d*p;
            int yy = (int)(a*100+1.0e-6);
            double b = (double)yy/100;
            model.setData(model.index(i,4),b);
            model.submitAll();
            qDebug()<<model.record(i);
        }
        if(a==1)
        {
            QMessageBox::information(NULL, "成功","信息更改成功！");
            accept();
        }
    }
    else if(ui->clothes->isChecked())
    {
        QSqlTableModel model;
        model.setTable("commodity");
        model.setFilter(QObject::tr("column = 2 ")); //根据类别进行筛选
        model.select(); //显示结果
        for (int i = 0; i < model.rowCount(); i++)
        {
            qDebug()<<model.record(i);
            qDebug()<<d;
            model.setData(model.index(i,7),d);
            double p= model.record(i).value("primeprice").toFloat();
            double a = d*p;
            int yy = (int)(a*100+1.0e-6);
            double b = (double)yy/100;
            model.setData(model.index(i,4),b);
            model.submitAll();
            qDebug()<<model.record(i);
        }
        if(a==1)
        {
            QMessageBox::information(NULL, "成功","信息更改成功！");
            accept();
        }
    }
    else if(ui->food->isChecked())
    {
        QSqlTableModel model;
        model.setTable("commodity");
        model.setFilter(QObject::tr("column = 3 ")); //根据类别进行筛选
        model.select(); //显示结果
        qDebug()<<model.rowCount();
        for (int i = 0; i < model.rowCount(); i++)
        {
            qDebug()<<model.record(i);
            qDebug()<<d;
            model.setData(model.index(i,7),d);
            double p= model.record(i).value("primeprice").toFloat();
            double a = d*p;
            int yy = (int)(a*100+1.0e-6);
            double b = (double)yy/100;
            model.setData(model.index(i,4),b);
            model.submitAll();
            qDebug()<<model.record(i);
        }
        if(a==1)
        {
            QMessageBox::information(NULL, "成功","信息更改成功！");
            accept();
        }
    }
//.........这里部分代码省略.........

void ClinicChargeStatisticForm::updateTable()
{
    initTable();
    QDate startDate = m_startDateEdit->date();
    QDate endDate = m_endDateEdit->date();

    if(startDate > endDate)
        return;

    QString strName = m_nameEdit->text();
    Gender eGender = (Gender)m_genderComboBox->currentIndex();
    if(myDB::connectDB())
    {
        QSqlTableModel *model = new QSqlTableModel;
        model->setTable(strClinicCharge);
        QString strSql = "" , strTemp = "";

        strTemp = "Gender = " + QString::number((int)eGender);
        strSql += strTemp;

        if(!strName.isEmpty())
        {
            strTemp = " and Name = \'" + strName + "\'";
            strSql += strTemp;
        }

        QString strStartTime = startDate.toString("yyyy-MM-dd") + "T00:00:00";
        QString strEndTime = endDate.toString("yyyy-MM-dd") + "T23:59:59";

        strTemp = " and Time between \'" + strStartTime + "\' and \'" + strEndTime + "\'";
        strSql += strTemp;

        model->setFilter(strSql);
        model->select();

        int index = 0;
        for(int i = 0; i < model->rowCount();i++)
        {
            QSqlRecord record = model->record(i);
            QString strID = record.value("ID").toString();
            QSqlTableModel *mymodel = new QSqlTableModel;
            mymodel->setTable(strClinicChargeDetails);
            mymodel->setFilter("ChargeId = \'" + strID + "\'");
            mymodel->select();
            for(int j = 0; j < mymodel->rowCount(); j++)
            {
                m_resultsModel->setItem(index,0,new QStandardItem(strID));

                m_resultsModel->setItem(index,1,new QStandardItem(record.value("Name").toString()));
                m_resultsModel->setItem(index,2,new QStandardItem(record.value("Department").toString()));
                m_resultsModel->setItem(index,3,new QStandardItem(record.value("Doctor").toString()));
                QSqlRecord record1 = mymodel->record(j);
                m_resultsModel->setItem(index,4,new QStandardItem(record1.value("ChargeItemName").toString()));
                m_resultsModel->setItem(index,5,new QStandardItem(record1.value("ChargeItemCount").toString()));
                m_resultsModel->setItem(index,6,new QStandardItem(record1.value("ChargeItemPrice").toString()));
                m_resultsModel->setItem(index,7,new QStandardItem(record.value("Time").toString()));
                index++;
            }
        }
    }
}