c++ - Qt程序在向mysql数据库插入1023行数据时挂起

Question

我正在为 pfsense 生成的凭证编写一个凭证管理系统。该程序相对简单，因为它需要导入 pfsense 生成的 csv 文件，将它们插入数据库，然后才能将它们打印为收据。我已经编写了将数据插入数据库的函数，但是当我运行它时，程序会挂起一点并显示没有响应。有没有什么办法可以让程序像暂停一样等待执行完成而不说它已停止响应？任何帮助，将不胜感激。我的功能如下:

void MainWindow::updateVouchers()
{
int insertions = 0;//used to keep track of total number of successful insertions into database
int total = 0; //used to keep track of how many lines of codes need to be inserted

//check if a the user selected a valid csv file and gave a valid voucher expiration time
if (im_FileName.isNull() || im_FileName.isEmpty() || voucherTime <= 0 )
{
    ui->statusBar->showMessage("Please select a voucher file to import and enter a valid time",5000);
    return;
}

{
    QSqlDatabase dbcon = QSqlDatabase::addDatabase("QMYSQL","updateconnection");

    QString host = "localhost";
    QString dbname = "xxxxxxx";
    QString uname = "admin";
    QString pword = "xxxxxx";
    int port = 3306;
    dbcon.setHostName(host);
    dbcon.setDatabaseName(dbname);
    dbcon.setUserName(uname);
    dbcon.setPassword(pword);
    dbcon.setPort(port);


    if ( !dbcon.open() ) //condition used to check if database connection was bad
    {
        QSqlError error = dbcon.lastError();
        QMessageBox::warning(this,"Error Connecting to database",
                             QString("There was an error while connecting to %1, the error is %2").arg(dbname).arg(error.text()),
                             QMessageBox::Ok,QMessageBox::Cancel);
    }
    else //if connection to database was good write vouchers to database
    {
        QFile theFile(im_FileName); //create a file with the csv which was imported
        int count = 0;
        QString line = QString();

        //check to see if the file can be opened
        if ( !theFile.open(QIODevice::ReadOnly | QIODevice::Text))
        {
            QMessageBox::warning(this,tr("Error Opening the file!"),QString("There was an erro opening file %1").arg(im_FileName),QMessageBox::Ok,QMessageBox::Close);
        }
        else //if the file was opened successfully, create a stream and update the vouchers
        {
            //ensure that a valid filename and voucher time has been specified

            QTextStream stream(&theFile);
            QSqlQuery query(dbcon);

            //loop through the stream reading one line at a time
            while ( !stream.atEnd() )
            {
                count++;
                line = stream.readLine();
                if ( count > 7 && !line.isEmpty() && !line.isNull() ) //make sure the line read has something in it
                {
                    QString newLine( line.remove(QRegExp("[\\s\"]") ) );
                    query.prepare("INSERT INTO vouchers (code, valid_time, isUsed, isPrinted)" "VALUES (?, ?, ?, ?)");
                    //query.prepare("INSERT INTO vouchers (code, valid_time, isUsed, isPrinted)" "VALUES (:code, :time, :used, :printed");
                    query.bindValue(0,newLine);
                    query.bindValue(1,voucherTime);
                    query.bindValue(2,false);
                    query.bindValue(3,false);
                    bool status = query.exec();
                    total++; //for each line of voucher code that is valid, update the total number of lines int the file
                    if ( status == true ) { insertions++; } //if code was entered into database okay, update amount of good insertions
                }
            }//end of while loop

        }//end of file open else

    }//end of database open else
    dbcon.close();
}

QSqlDatabase::removeDatabase("updateconnection");
ui->statusBar->showMessage(QString("%1 of %2 voucher codes were successfully inserted!").arg(insertions).arg(total),5000);

}

我犯了什么错误？

提前致谢。

Answer 1

您是否尝试过将 query.prepare() 行移动到 while 循环之外(上方)？看起来您正在为每条记录准备语句。这应该是不必要的，因为可重用性是准备好的语句存在的原因。

此外，即使在重用准备好的语句之后，您仍在执行超过 1,000 次操作。这可能只是一个缓慢的操作。您可能需要考虑将您的数据库代码放入另一个线程以保持 UI 线程响应。

我知道您的示例代码显示您的服务器位于 localhost 上。如果不是，我会再次断言，一个单独的数据库线程是可取的——到服务器的多次往返确实会累加起来。

最后，MySQL 有两个您可以使用的工具。一个是 extended inserts .您可以使用扩展插入语法构建 SQL 命令字符串，以一次性插入成批记录(如果这样做，请务必处理引用值)。第二个是 LOAD DATA命令;用于导入数据。