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

//------------------Above Date--------------------------
void Liveinfo::UpdateRoom(int RoomNum,int change)
{
	QSqlTableModel * updateroom = new QSqlTableModel;		//修改已入住的房间
	updateroom->setTable("Room");
	updateroom->select();	
	for(int i = 0;i < updateroom->rowCount(); ++i)
	{		
		QSqlRecord Roomrecord = updateroom->record(i);
		int check_room = Roomrecord.value("Num").toInt();
		if( check_room == RoomNum )
		{
			Roomrecord.setValue("Live",change);
			updateroom->setRecord(i, Roomrecord);
		}
	}		
	updateroom->submitAll();
}

void MaterialSaverDB::saveTemplateChapter( StockMaterial* tmpl )
{
    if( ! tmpl ) {
        kDebug() << "Parameter error, zero material!";
        return;
    }
    dbID id = tmpl->getID();
    dbID chapId = tmpl->chapterId();

    QSqlTableModel model;
    model.setTable("stockMaterial");
    QString templID = id.toString();
    model.setFilter( "matID=" + templID );
    model.select();

    QSqlRecord buffer = model.record();

    if( model.rowCount() > 0)
    {
        kDebug() << "Updating material chapter " << templID << endl;
        buffer = model.record(0);
        buffer.setValue( "chapterID", chapId.toString() );
        model.setRecord(0, buffer);
        model.submitAll();
    } else {
        kDebug() << "Could not update material chapter, not found with id " << templID;
    }
}

bool DatabaseHandler::writeCensus(census * obj) {
    qDebug() << "Writing object data to database.";
    QSqlTableModel table;
    table.setTable("census");
    table.setFilter("rcns_id=" + QString::number(obj->id) + " AND usr='" + obj->usr + "'");
    table.select();
    // get record structure from db
    QSqlRecord record(table.record());
    // initialize record with census-structure values
    setRecordTable(&record, obj);

    // insert or update records in db
    if (table.rowCount() == 0) { //INSERT
        qDebug() << "Insert!";
        // remove first entry of record
        // auto increment of id is handled by postgres
        record.remove(0);
        bool done = table.insertRecord(-1,record);
        qDebug() << table.lastError();
        return done;
    } else { //UPDATE
        qDebug() << "Update!";
        record.setValue("fcns_id",table.record(0).value(0).toInt());
        bool check = true;
        check = check && table.setRecord(0, record);
        check = check && table.submitAll();
        qDebug() << table.lastError();
        return check;
    }
    return true;
}

void PatientAccounter::printBill(const QDate& date)
{
	QString Document;

	addRTFHeader(Document);
	addDocumentHeader(Document);
	addPatientHeader(Document);
	addInfoText(Document);

	QString billFile = createBillFile(date);
	//std::cerr << "Target: " << billFile.toStdString() << std::endl;
	if (!addTreatments(Document))
	{
		//Again nothing to account, simple return
		return;
	}
	addDocumentFooter(Document);
	finishRTF(Document);

	//Get The document Footer
	std::ofstream outStream;
	outStream.open(billFile.toStdString().c_str());
	outStream << Document.toStdString();

	//Increment the Bill Identifier
	QSqlTableModel* miscTable = new QSqlTableModel;
	miscTable->setTable("misc");
	miscTable->select();
	QSqlRecord miscRecord = miscTable->record(0);
	miscRecord.setValue(BillNumber, m_billNumber + 1);
	miscTable->setRecord(0, miscRecord);
	miscTable->submit();
	delete miscTable;
}

bool MaterialSaverDB::saveTemplate( StockMaterial *mat )
{
    bool res = true;

    // Transaktion ?

    QSqlTableModel model;
    model.setTable("stockMaterial");
    QString templID = QString::number( mat->getID() );
    model.setFilter( "matID=" + templID );
    model.select();

    QSqlRecord buffer = model.record();

    if( model.rowCount() > 0)
    {
        kDebug() << "Updating material " << mat->getID() << endl;

        // mach update
        buffer = model.record(0);
        fillMaterialBuffer( buffer, mat, false );
        model.setRecord(0, buffer);
        model.submitAll();
    }
    else
    {
        // insert
        kDebug() << "Creating new material database entry" << endl;

        fillMaterialBuffer( buffer, mat, true );
        model.insertRecord(-1, buffer);
        model.submitAll();

        /* Jetzt die neue Template-ID selecten */
        dbID id = KraftDB::self()->getLastInsertID();
        kDebug() << "New Database ID=" << id.toInt() << endl;

        if( id.isOk() ) {
            mat->setID( id.toInt() );
            templID = id.toString();
        } else {
            kDebug() << "ERROR: Kann AUTOINC nicht ermitteln" << endl;
            res = false;
        }
    }
    return res;
}

void Database::deactivateUser(QString &userName)
{
    QSqlTableModel model;
    model.setTable("user");
    model.select();

    for (int i=0; i<model.rowCount(); ++i)
    {
        QSqlRecord record = model.record(i);
        if (record.value("name").toString()==userName)
        {
            record.setValue("isActive", false);
            model.setRecord(i, record);
            model.submitAll();
            break;
        }
    }
}

void Database::saveXP(QString &userName, int xp)
{
    QSqlTableModel model;
    model.setTable("user");
    model.select();

    for (int i=0; i<model.rowCount(); ++i)
    {
        QSqlRecord record = model.record(i);
        if (record.value("name").toString()==userName)
        {
            record.setValue("experiencePoints", xp);
            model.setRecord(i, record);
            model.submitAll();
            break;
        }
    }
}

void Database::saveLevel(QString &userName, int level)
{
    QSqlTableModel model;
    model.setTable("user");
    model.select();

    for (int i=0; i<model.rowCount(); ++i)
    {
        QSqlRecord record = model.record(i);
        if (record.value("name").toString()==userName)
        {
            record.setValue("level", level);
            model.setRecord(i, record);
            model.submitAll();
            break;
        }
    }
}

void CDeleteAclTask::updateRules(int ruleId,int cmdType)
{
	QSqlTableModel model;
	model.setTable("aclrules");
	QString filter1=QString("id=%1").arg(ruleId);
	model.setFilter(filter1);
	model.select();

	if (model.rowCount()==1)
	{
		QSqlRecord record=model.record(0);
		record.setValue("cmdtype",cmdType);
		model.setRecord(0,record);
	}

	model.submitAll();
	//QString error=model.lastError().text();
	//QMessageBox::information(this, tr("information"), error);
}

void Database::saveUser(QString &userName, User user)
{
    QSqlTableModel model;
    model.setTable("user");
    model.select();

    for (int i=0; i<model.rowCount(); ++i)
    {
        QSqlRecord record = model.record(i);
        if (record.value("name").toString()==userName)
        {
            record.setValue("experiencePoints", user.experiencePoints);
            record.setValue("isActive", user.isActive);
            record.setValue("level", user.level);
            model.setRecord(i, record);
            model.submitAll();
            break;
        }
    }
}

//.........这里部分代码省略.........
    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]
    }
}

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();
//.........这里部分代码省略.........