1. 使用事务,将多条数据库更改语句或者,循环遍历语句放到一次事务中来处理。
SqliteDB::startTransaction();
do_CRUD;
MayBe: SqliteDB::RollBack();
SqliteDB::Commit();
2. 使用批处理执行参数为集合类型的操作
QVariantList bindValue1;
QVariantList bindValue2;
QString strSql = " delete from tablename where field1 = ? and field2 = ?"
QSqlquery sqlQuery.addbindValue(bindValue1);
QSqlquery sqlQuery.addbindValue(bindValue2);
sqlQuery.queryBatch();
getResult();
3. 一般对于插入操作,要返回插入数据库后,所在行的自增长序号ID, 或者自定义的某一列。后续流程会用到这个ID,来标记数据行。
两种方式获取插入自增长ID:
-select last_insert_rowid() as newID from TableName
select max(ID) from 表
还可以放在事务一起处理。
注意的是:
SQLiteAPI 函数sqlite3_last_insert_rowid()可以取得最后一条插入的记录的rowid。但sqlite3_last_insert_rowid()是基于当前进程的。也就是说,sqlite3_last_insert_rowid()取到的是当前进程最后一次插入记录的rowid。对于不是当前进程插入的记录,sqlite3_last_insert_rowi()均返回0。sqlite3_last_insert_rowi()对应的SQL声明为last_insert_rowid(),
操作例子:
bool SampleRecordDAO::updatePrintStatus(const QSet<int> &sampleIdSet, bool isPrint)
{
int sampleIdSize = sampleIdSet.size();
if (sampleIdSize <= 0)
{
return true;
}
QSqlQuery query(SqliteDbHelper::Instance()->getDB());
query.prepare("UPDATE SampleInfo SET ResultStatePrint = ? where ID = ? ");
QVariantList stateList,idList;
for (QSet<int>::const_iterator idItr = sampleIdSet.begin(); idItr != sampleIdSet.end(); ++idItr)
{
stateList << (int)isPrint;
idList << *idItr;
}
query.addBindValue(stateList);
query.addBindValue(idList);
if (!query.execBatch())
{
revDebug << query.lastError().text();
return false;
}
return true;
}
bool SampleRecordDAO::deleteSampleInfoAbout4Ids(QList<int> sampleIds)
{
//1.Haven't Sample Id
int sampleSize = sampleIds.size();
if (sampleSize <= 0)
{
return true;
}
//2.Build Sample Id List
QVariantList idList;
for (int sampleIdx = 0; sampleIdx < sampleSize; ++sampleIdx)
{
idList << sampleIds.at(sampleIdx);
}
//3.Delete DB
SqliteDbHelper::Instance()->startTransaction();
QSqlQuery query(SqliteDbHelper::Instance()->getDB());
do
{
query.prepare("DELETE FROM SampleInfo WHERE ID = ? ");
query.addBindValue(idList);
if (!query.execBatch())
{
break;
}
query.prepare("DELETE FROM SampleInfoOrg WHERE ID = ? ");
query.addBindValue(idList);
if (!query.execBatch())
{
break;
}
query.prepare("DELETE FROM MicroscopyInfoTable WHERE ID = ? ");
query.addBindValue(idList);
if (!query.execBatch())
{
break;
}
query.prepare("DELETE FROM SampleInforgraincountTable WHERE ID = ? ");
query.addBindValue(idList);
if (!query.execBatch())
{
break;
}
SqliteDbHelper::Instance()->commit();
return true;
}
while(false);
revDebug << query.lastError().text();
SqliteDbHelper::Instance()->rollback();
return false;
}