如何在sqlite数据库中的c++中同时保存或更新多个表？

Question

       QSqlQuery qry4;
       QSqlQuery qry5;
       QSqlQuery qry6;



 qry3.prepare("INSERT INTO apra(Source,Medium,Subject,'Date & Time received document','Date & Time RRO forwarded to HOO','ControlNo.') values('"+src+"','"+med+"','"+key+"','"+one+"','"+one+"','"+res+"')");
       qry4.prepare("INSERT INTO bag(Source,Medium,Subject,'Date & Time received document','Date & Time RRO forwarded to HOO','ControlNo.') values('"+src+"','"+med+"','"+key+"','"+one+"','"+one+"','"+res+"')");
       qry5.prepare("INSERT INTO ben(Source,Medium,Subject,'Date & Time received document','Date & Time RRO forwarded to HOO','ControlNo.') values('"+src+"','"+med+"','"+key+"','"+one+"','"+one+"','"+res+"')");
       qry6.prepare("INSERT INTO cent(Source,Medium,Subject,'Date & Time received document','Date & Time RRO forwarded to HOO','ControlNo.') values('"+src+"','"+med+"','"+key+"','"+one+"','"+one+"','"+res+"')");


 if(qry3.exec()){
           QMessageBox::information(this,"Database","qry3");
       }else{
           QMessageBox::critical(this,tr("error::"),qry3.lastError().text());
       }
       if(qry4.exec()){
           QMessageBox::information(this,"Database","qry4");
       }else{
           QMessageBox::critical(this,tr("error::"),qry4.lastError().text());
       }
       if(qry5.exec()){
           QMessageBox::information(this,"Database","qry5");
       }else{
           QMessageBox::critical(this,tr("error::"),qry5.lastError().text());
       }
       if(qry6.exec()){
           QMessageBox::information(this,"Database","qry6");
       }else{
           QMessageBox::critical(this,tr("error::"),qry6.lastError().text());
       }

我想知道一种简单的方法来更新sqlite中的表。因为这种方法还会弹出3次信息提示，如果我有10次以上的查询，会很费时，也不太理想。

Answer 1

假设您使用占位符创建了所有查询，并且它们都以相同的顺序具有相同的参数：

qry3.prepare("INSERT INTO apra(Source,Medium,Subject,'Date & Time received document','Date & Time RRO forwarded to HOO','ControlNo.') values(?,?,?,?,?,?)");
qry4.prepare("INSERT INTO bag(Source,Medium,Subject,'Date & Time received document','Date & Time RRO forwarded to HOO','ControlNo.') values(?,?,?,?,?,?)");
qry5.prepare("INSERT INTO ben(Source,Medium,Subject,'Date & Time received document','Date & Time RRO forwarded to HOO','ControlNo.') values(?,?,?,?,?,?)");
qry6.prepare("INSERT INTO cent(Source,Medium,Subject,'Date & Time received document','Date & Time RRO forwarded to HOO','ControlNo.') values(?,?,?,?,?,?)");

您可以按以下方式对它们进行循环：

bool success = true;
for (auto& q_wrapper : std::initializer_list<std::reference_wrapper<QSqlQuery>>{qry3, qry4, qry5, qry6}) {
  QSqlQuery& q = q_wrapper.get();
  q.addBindValue(src);
  q.addBindValue(med);
  q.addBindValue(key);
  q.addBindValue(one);
  q.addBindValue(one);
  q.addBindValue(res);
  success &&= q.exec();
  if (!success) {
    QMessageBox::critical(this,tr("error::"),q.lastError().text())
    break;
  }
}

if (success) {
  QMessageBox::information(this,"Database","all queries succeeded");
}

可能的改进：

• 切换到*命名占位符，并将所有占位符存储在QMap<QString, QVariant>中。
• 把所有的东西都包装在一个交易中。这使您能够避免部分更新。(使用QSqlDatabase().transaction()启动事务并调用QSqlDatabase().commit() (如果成功)或QSqlDatabase().rollback() (如果成功)