C++ QSqlQuery::at方法代码示例

Movie Database::getMovie(QString title) {
    QSqlQuery getMovieQuery;
    Movie temp;
    getMovieQuery.prepare("SELECT * FROM movieDB where originalTitle LIKE :title ;");
    getMovieQuery.bindValue(":title", "%"+title+"%");
    getMovieQuery.exec();
    getMovieQuery.last();

    if(getMovieQuery.at()+1 == 1) {
        temp.setTMDB_ID(getMovieQuery.value(0).toInt());
        temp.setYear(getMovieQuery.value(3).toInt());
        temp.setOriginalTitle(getMovieQuery.value(2).toString());
        temp.setTitle(getMovieQuery.value(1).toString());
        temp.setRuntime(getMovieQuery.value(4).toInt());
        temp.setSynopsis(getMovieQuery.value(5).toString());
    }else if(getMovieQuery.at()+1 == -1) {
        getMovieQuery.clear();
        getMovieQuery.prepare("SELECT * FROM movieDB where Title LIKE :title ;");
        getMovieQuery.bindValue(":title", "%"+title+"%");
        getMovieQuery.exec();
        getMovieQuery.last();

        if(getMovieQuery.at()+1 == 1) {
            temp.setTMDB_ID(getMovieQuery.value(0).toInt());
            temp.setYear(getMovieQuery.value(3).toInt());
            temp.setOriginalTitle(getMovieQuery.value(2).toString());
            temp.setTitle(getMovieQuery.value(1).toString());
            temp.setRuntime(getMovieQuery.value(4).toInt());
            temp.setSynopsis(getMovieQuery.value(5).toString());
        }
    }

    return temp;
}

void LogEngine::checkDBSize()
{
    QString queryString = "SELECT ROWID FROM entries;";
    QSqlQuery query;
    query.exec(queryString);
    int numRows = 0;
    if (m_db.driver()->hasFeature(QSqlDriver::QuerySize)) {
        numRows = query.size();
    } else {
        // this can be very slow
        query.last();
        numRows = query.at() + 1;
    }

    if (numRows >= m_dbMaxSize) {
        // keep only the latest m_dbMaxSize entries
        qCDebug(dcLogEngine) << "Deleting oldest entries and keep only the latest" << m_dbMaxSize << "entries.";
        QString queryDeleteString = QString("DELETE FROM entries WHERE ROWID IN (SELECT ROWID FROM entries ORDER BY timestamp DESC LIMIT -1 OFFSET %1);").arg(QString::number(m_dbMaxSize));
        if (!query.exec(queryDeleteString)) {
            qCWarning(dcLogEngine) << "Error deleting oldest log entries to keep size. Driver error:" << query.lastError().driverText() << "Database error:" << query.lastError().databaseText();
        } else {
            emit logDatabaseUpdated();
        }
    }
}

//returns all info associated with the record of specified id
HistoryItem DbManager::getHistoryItem(const int& id) const
{

    QSqlQuery query;
    query.prepare("SELECT * FROM History WHERE id = :id");
    query.bindValue(":id", id);
    if (!query.exec()) { qCritical() << "The getHistoryItemStats select statement couldn't be executed"; }

    qDebug() << query.size();

    HistoryItem historyItem;

    if (query.next())
    {
        historyItem.id = query.value(query.record().indexOf("id")).toInt();
        historyItem.fileName = query.value(query.record().indexOf("fileName")).toString();  //Get the data from the query
        historyItem.dateTime = query.value(query.record().indexOf("dateTime")).toString();
        historyItem.compressionRatio = query.value(query.record().indexOf("compressionRatio")).toDouble();
        historyItem.originalSize = query.value(query.record().indexOf("originalSize")).toDouble();
        historyItem.compressedSize = query.value(query.record().indexOf("compressedSize")).toDouble();
        historyItem.executionTime = query.value(query.record().indexOf("executionTime")).toDouble();
        historyItem.notes = query.value(query.record().indexOf("notes")).toString();
    }

    //checking that there was indeed only one query returned. BTW, query.size() always returns 0
    query.last();
    Q_ASSERT( query.at() + 1 == 1); //how can there be more than one record associated with the unique id?


    return historyItem;
}

void MainWindow::readAd(int adId)//读取广告
{
    QSqlQuery adQuery;//对广告表单搜索
    if(adQuery.exec("SELECT aditem FROM adtable WHERE id = "+QString().setNum(adId)))
    {
        int adNumRows = 0;//定义搜索返回的行数
        if(db.driver()->hasFeature(QSqlDriver::QuerySize))//判断驱动是否包含QuerySize
        {
            adNumRows = adQuery.size();//将返回的行数赋于fstNumRows
        }
        else
        {
            adQuery.last();//移至最后一行
            adNumRows = adQuery.at()+1;//将返回的行数赋于fstNumRows
            adQuery.seek(-1);//返回第一行
        }
        if(adNumRows>0)//判断是否大于0
        {
            while(adQuery.next())
            {
                this->adView->loadMap(adQuery.value(0).toByteArray());//读取广告
            }
        }
    }
}

bool Database::exist(Movie m) {
    QSqlQuery existQuery;
    existQuery.prepare("SELECT * FROM movieDB where tmd_ID = :id;");
    existQuery.bindValue(":id", m.getTmdbId());
    existQuery.exec();
    existQuery.last();

    if(existQuery.at()+1 == 1) {
        return true;
    }else return false;

}

void Execut_window::getAllCount(void)
{
    QSqlQuery query;
    int temporaryA = 0;
    int temporaryB = 0;
    int k = 0;
    int choiceModel = 0;//选择drawerNo的位置
    saveDrawer[0] = 0;

    if (execute_V->execute_model == 1)//入柜
    {
        choiceModel = 17;
    }
    else if (execute_V->execute_model == 2)//还
    {
        choiceModel = 9;
    }
    else if ((execute_V->execute_model == 4) || (execute_V->execute_model == 6))//替换
    {
        executeInfoError(1,"请取出试剂");
        choiceModel = 8;
    }

    if ((execute_V->execute_model == 2) || (execute_V->execute_model == 1) ||\
            (execute_V->execute_model == 4) || (execute_V->execute_model == 6))
    {

        query.exec(QString("SELECT * from %1 ORDER BY drawerNo ASC").arg(execute_V->T_executeTable));

        query.last();
        execute_V->acountRow = query.at()+1;//0

        for(int i = 0; i < execute_V->acountRow; i++)
        {
            query.seek(i);        
            temporaryA = query.value(choiceModel).toInt();
            if(temporaryA != temporaryB)
            {
                temporaryB = temporaryA;
                k++;
                saveDrawer[0]=k;
                saveDrawer[k]=temporaryB;
            }


        }



    }
}

static QScriptValue qmlsqldatabase_item(QScriptContext *context, QScriptEngine *engine)
{
    QSqlQuery query = qscriptvalue_cast<QSqlQuery>(context->thisObject().data());
    int i = context->argument(0).toNumber();
    if (query.at() == i || query.seek(i)) { // Qt 4.6 doesn't optimize seek(at())
        QSqlRecord r = query.record();
        QScriptValue row = engine->newObject();
        for (int j=0; j<r.count(); ++j) {
            row.setProperty(r.fieldName(j), QScriptValue(engine,r.value(j).toString()));
        }
        return row;
    }
    return engine->undefinedValue();
}

void Graph::populateFromResult(QSqlQuery & qry) {
    bool valid = FALSE;
    bool old_repaint = autoRepaint();
    setAutoRepaint(FALSE);
    QVariant var;
    double val;
    int cols = qry.driver()->record(qry).count();
    while(qry.next()) {
        if(!qry.isNull(0)) {
            setReferenceLabel(qry.at(), qry.value(0).toString());
        }
        for(int c = 1; c < cols; c++) {
            if(!qry.isNull(c)) {
                val = qry.value(c).toDouble(&valid);
                if(valid) {
                    setSetValue(qry.at(), c - 1, val);
                }
            }
        }
    }
    setAutoRepaint(old_repaint);
    if(autoRepaint()) repaint();
}

void Execut_window::searchPositionInfo(QString name,int i)//获取对应抽屉内的试剂rowid
{
    QSqlQuery query;
    int temporaryC = 0;
    query.exec(QString("select * from %1 where drawerNo=%2").arg(name).arg(saveDrawer[i]));


    query.last();
    temporaryC= query.at()+1;//at 0开始

    for(int i = 0; i < temporaryC ;i++)
    {
        query.seek(i);
        saveRowid[0] = i+1;
        saveRowid[i+1] = query.value(0).toInt();
    }
}

 QScriptValue property(const QScriptValue &object,
                       const QScriptString &name, uint)
 {
     QSqlQuery query = qscriptvalue_cast<QSqlQuery>(object.data());
     if (name == str_length) {
         int s = query.size();
         if (s<0) {
             // Inefficient.
             if (query.last()) {
                 return query.at()+1;
             } else {
                 return 0;
             }
         } else {
             return s;
         }
     } else if (name == str_forwardOnly) {
         return query.isForwardOnly();
     }
     return engine()->undefinedValue();
 }

bool Database::addUser (QString userName)
{
    QSqlQuery query;
    int numRows;

    query.exec("SELECT name FROM user WHERE id > 0");
    while (query.next())
    {

        QString name = query.value(0).toString();
        if (name==userName)
            return false;
    }

    query.exec("SELECT id FROM user WHERE id > 0");
    QSqlDatabase defaultDB = QSqlDatabase::database();
    if (defaultDB.driver()->hasFeature(QSqlDriver::QuerySize))
    {
        numRows = query.size();
    }
    else
    {
        query.last();
        numRows = query.at() + 1;
    }
    numRows++;
    //query.exec("insert into players values(1, 'MA')");
    query.prepare("insert into user(id, name, experiencePoints, isActive, level) "
                  "values(:id, :name, :experiencePoints, :isActive, :level)");
    query.bindValue(":id", numRows);
    query.bindValue(":name", userName);
    query.bindValue(":experiencePoints", 0);
    query.bindValue(":isActive", 0);
    query.bindValue(":level", 1);
    query.exec();

    return true;
}

void sql_intro_snippets()
{
    {
//! [26]
    QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
    db.setHostName("bigblue");
    db.setDatabaseName("flightdb");
    db.setUserName("acarlson");
    db.setPassword("1uTbSbAs");
    bool ok = db.open();
//! [26]
    Q_UNUSED(ok);
    }

    {
//! [27]
    QSqlDatabase firstDB = QSqlDatabase::addDatabase("QMYSQL", "first");
    QSqlDatabase secondDB = QSqlDatabase::addDatabase("QMYSQL", "second");
//! [27]
    }

    {
//! [28]
    QSqlDatabase defaultDB = QSqlDatabase::database();
//! [28] //! [29]
    QSqlDatabase firstDB = QSqlDatabase::database("first");
//! [29] //! [30]
    QSqlDatabase secondDB = QSqlDatabase::database("second");
//! [30]
    }

    {
    // SELECT1
//! [31]
    QSqlQuery query;
    query.exec("SELECT name, salary FROM employee WHERE salary > 50000");
//! [31]

//! [32]
    while (query.next()) {
        QString name = query.value(0).toString();
        int salary = query.value(1).toInt();
        qDebug() << name << salary;
    }
//! [32]
    }

    {
    // FEATURE
//! [33]
    QSqlQuery query;
    int numRows;
    query.exec("SELECT name, salary FROM employee WHERE salary > 50000");

    QSqlDatabase defaultDB = QSqlDatabase::database();
    if (defaultDB.driver()->hasFeature(QSqlDriver::QuerySize)) {
        numRows = query.size();
    } else {
        // this can be very slow
        query.last();
        numRows = query.at() + 1;
    }
//! [33]
    }

    {
    // INSERT1
//! [34]
    QSqlQuery query;
    query.exec("INSERT INTO employee (id, name, salary) "
               "VALUES (1001, 'Thad Beaumont', 65000)");
//! [34]
    }

    {
    // NAMED BINDING
//! [35]
    QSqlQuery query;
    query.prepare("INSERT INTO employee (id, name, salary) "
                  "VALUES (:id, :name, :salary)");
    query.bindValue(":id", 1001);
    query.bindValue(":name", "Thad Beaumont");
    query.bindValue(":salary", 65000);
    query.exec();
//! [35]
    }

    {
    // POSITIONAL BINDING
//! [36]
    QSqlQuery query;
    query.prepare("INSERT INTO employee (id, name, salary) "
                  "VALUES (?, ?, ?)");
    query.addBindValue(1001);
    query.addBindValue("Thad Beaumont");
    query.addBindValue(65000);
    query.exec();
//! [36]
    }

//.........这里部分代码省略.........

void MainWindow::menuTreeClick(QModelIndex)
{
    QVariant clickedItem = menuTree->selectionModel()->currentIndex().data(Qt::UserRole);
    int rowId = menuTree->selectionModel()->currentIndex().row()+1;
    QSqlQuery rowQuery;
    switch(clickedItem.toInt())
    {
    case 1:
        {
            if(rowQuery.exec("SELECT tableid, adid FROM firstmenus WHERE id = "+QString().setNum(rowId)))
            {
                int adNumRows = 0;//定义搜索返回的行数
                if(db.driver()->hasFeature(QSqlDriver::QuerySize))//判断驱动是否包含QuerySize
                {
                    adNumRows = rowQuery.size();//将返回的行数赋于adNumRows
                }
                else
                {
                    rowQuery.last();//移至最后一行
                    adNumRows = rowQuery.at()+1;//将返回的行数赋于adNumRows
                    rowQuery.seek(-1);//返回第一行
                }
                if(adNumRows>0)//判断是否大于0
                {
                    rowQuery.next();
                    if(rowQuery.value(0).toInt()==0)
                    {
                        if(this->adView->isHidden())
                        {
                            this->rightSplitter->hide();
                            this->adView->show();
                        }
                        this->readAd(rowQuery.value(1).toInt());
                        this->adView->viewUpdate();
                    }
                    else
                    {
                        if(this->rightSplitter->isHidden())
                        {
                            this->adView->hide();
                            this->rightSplitter->show();
                        }
                        this->readTable(rowQuery.value(0).toInt());
                    }
                }
            }            
        }
        break;

    case 2:
        {
            if(rowQuery.exec("SELECT tableid, adid FROM secondmenus WHERE id = "+QString().setNum(rowId)))
            {
                int adNumRows = 0;//定义搜索返回的行数
                if(db.driver()->hasFeature(QSqlDriver::QuerySize))//判断驱动是否包含QuerySize
                {
                    adNumRows = rowQuery.size();//将返回的行数赋于adNumRows
                }
                else
                {
                    rowQuery.last();//移至最后一行
                    adNumRows = rowQuery.at()+1;//将返回的行数赋于adNumRows
                    rowQuery.seek(-1);//返回第一行
                }
                if(adNumRows>0)//判断是否大于0
                {
                    rowQuery.next();
                    if(rowQuery.value(0).toInt()==0)
                    {
                        if(this->adView->isHidden())
                        {
                            this->rightSplitter->hide();
                            this->adView->show();
                        }
                        this->readAd(rowQuery.value(1).toInt());
                        this->adView->viewUpdate();
                    }
                    else
                    {
                        if(this->rightSplitter->isHidden())
                        {
                            this->adView->hide();
                            this->rightSplitter->show();
                        }
                        this->readTable(rowQuery.value(0).toInt());
                    }
                }
            }
        }
        break;

    case 3:
        {
            if(rowQuery.exec("SELECT tableid, adid FROM thirdmenus WHERE id = "+QString().setNum(rowId)))
            {
                int adNumRows = 0;//定义搜索返回的行数
                if(db.driver()->hasFeature(QSqlDriver::QuerySize))//判断驱动是否包含QuerySize
                {
                    adNumRows = rowQuery.size();//将返回的行数赋于adNumRows
                }
//.........这里部分代码省略.........

//创建目录树
void MainWindow::createMenuTree()
{
    QSqlQuery fstMenuQuery;//对firstmenus搜索
    QSqlQuery secMenuQuery;//对secondmenus搜索
    QSqlQuery thdMenuQuery;//对thirdmenus搜索
    if(fstMenuQuery.exec("SELECT menuitem, id FROM firstmenus;"))//判断搜索数据库中一级目录
    {
        int fstNumRows = 0;//定义搜索返回的行数
        if(db.driver()->hasFeature(QSqlDriver::QuerySize))//判断驱动是否包含QuerySize
        {
            fstNumRows = fstMenuQuery.size();//将返回的行数赋于fstNumRows
        }
        else
        {
            fstMenuQuery.last();//移至最后一行
            fstNumRows = fstMenuQuery.at()+1;//将返回的行数赋于fstNumRows
            fstMenuQuery.seek(-1);//返回第一行
        }
        if(fstNumRows>0)//判断搜索得到的结果是否大于0
        {
            menuTree = new QTreeView();//建立目录树
            treeModel = new QStandardItemModel(fstNumRows,1);//设置目录的大小
            treeModel->setHeaderData(0,Qt::Horizontal,tr("目录"));//设置目录头
            int fstInt = 0;
            while(fstMenuQuery.next())//加载一级目录
            {
                QStandardItem *fstItem = new QStandardItem(fstMenuQuery.value(0).toString());//新建一级目录各子项
                fstItem->setData(1,Qt::UserRole);//设置目录阶数
                treeModel->setItem(fstInt,fstItem);//将一级目录加载到目录树中
                fstInt++;//fstInt自加

                if(secMenuQuery.exec("SELECT menuitem, id FROM secondmenus WHERE parentid = "+ fstMenuQuery.value(1).toString()))//判断是否有二级目录
                {
                    int secNumRows = 0;//定义搜索返回的行数
                    if(db.driver()->hasFeature(QSqlDriver::QuerySize))
                    {
                        secNumRows = secMenuQuery.size();
                    }
                    else
                    {
                        secMenuQuery.last();
                        secNumRows = secMenuQuery.at()+1;
                        secMenuQuery.seek(-1);
                    }
                    if(secNumRows>0)//判断搜索得到的结果是否大于0
                    {
                        while(secMenuQuery.next())//加载二级目录
                        {
                            QStandardItem *secItem = new QStandardItem(secMenuQuery.value(0).toString());//新建二级目录各子项
                            secItem->setData(2,Qt::UserRole);//设置目录阶数为2
                            fstItem->appendRow(secItem);//加载二级目录

                            if(thdMenuQuery.exec("SELECT menuitem, id FROM thirdmenus WHERE parentid = "+ secMenuQuery.value(1).toString()))
                            {
                                int thdNumRows = 0;
                                if(db.driver()->hasFeature(QSqlDriver::QuerySize))
                                {
                                    thdNumRows = thdMenuQuery.size();
                                }
                                else
                                {
                                    thdMenuQuery.last();
                                    thdNumRows = thdMenuQuery.at()+1;
                                    thdMenuQuery.seek(-1);
                                }
                                if(thdNumRows>0)
                                {
                                    while(thdMenuQuery.next())//加载三级目录
                                    {
                                        QStandardItem *thdItem = new QStandardItem(thdMenuQuery.value(0).toString());//新建三级目录各子项
                                        thdItem->setData(3,Qt::UserRole);//设置目录阶数为3
                                        secItem->appendRow(thdItem);//加载三级目录
                                    }
                                }
                            }
                        }
                    }
                }
            }
            menuTree->setModel(treeModel);//设置目录树的MODEL
            menuTree->setEditTriggers(QAbstractItemView::NoEditTriggers);//设置为不可编辑
            menuTree->resizeColumnToContents(0);//设置树的宽度
            mainSplitter->addWidget(menuTree);//将目录加载到主面板中

            connect(menuTree,SIGNAL(clicked(QModelIndex)),this,SLOT(menuTreeClick(QModelIndex)));//当用户点击树目录时，启动menuTreeClick函数
        }
    }
}

void MainWindow::realtimeDataSlot(){



       double flowrate,voltage,irvalue,timestamp,relaystate;
       QString dev_name;

       // assign the appropriate driver to the database
       QSqlDatabase  db_plot = QSqlDatabase::addDatabase("QSQLITE");

       // open the specified database
        db_plot.setDatabaseName("/home/uday/Desktop/Project2/data.db");


        if(!db_plot.open()){
             qDebug("Failed to open the database");
        }
        else{
            // qDebug("Connected to the database");

        /* order the querries ordered by timestamp and compare previous last row with current_last row
         * select the querries greater than previous timestamp and
         * add the data to the graph corresponding to the device
         **/

        QString stmt =QString("SELECT  Flowrate, Voltage, IR_sensor, RelayState, DeviceID,Timestamp FROM DATA ORDER BY Timestamp;");
       // qDebug() <<"Query:"<< stmt;
        QSqlQuery query;
        query.prepare(stmt);

        if( !query.exec() ){
            qDebug() << query.lastError();
        }
        else{
           qDebug( "Qeury: Executed successfully" );
          }
        //count the current number of rows in database ordered by timestamp
        int  cur_last_row;

        if(query.last())   cur_last_row = query.at();

        qDebug()<<"current last row: "<<cur_last_row;

        if(cur_last_row >= prev_last_row) {

                     /*Before adding data, check to see if new devices are added
                      * and add a curve for the device
                      **/

                     // if db is not specified, then default database of this program is choosen
                     QSqlQuery dev_query("SELECT  DISTINCT DeviceID FROM DATA ORDER BY Timestamp;");
                     if( !dev_query.exec() )
                       qDebug() << dev_query.lastError();
                     else{
                       qDebug( "Qeury executed successfully" );
                     }

                     // find the number of distinct devices
                      int cur_num_dev;
                      if(dev_query.last())   cur_num_dev = dev_query.at() ;// dev_num counts from "zero"

                     //qDebug() << "number of distinct devices: " << cur_num_dev;


                     if(cur_num_dev>=num_dev){

                         for(int i=num_dev; i<cur_num_dev+1; i++){
                              dev_query.seek(i);
                              dev_id[i]=dev_query.value(0).toString();
                              qDebug() << dev_id[i];
                             // add the add_graph function
                             // addCurve(ui->qtplot,i);
                              addCurve(ui->qtplot_2,i);
                              addCurve(ui->qtplot_3,i);
                         }
                         num_dev=cur_num_dev+1;
                      }

                       query.seek(prev_last_row);
                       flowrate=query.value(0).toDouble();//flowrate,voltage,irvalue,timestamp,relaystate
                       voltage=query.value(1).toDouble();
                       irvalue=query.value(2).toDouble();
                       relaystate=query.value(3).toDouble();
                       timestamp=query.value(5).toDouble();
                      // qDebug()<<flowrate<<voltage<<irvalue<<relaystate<< timestamp;

                       dev_name=query.value(4).toString();
                      //  qDebug()<<dev_name;
                       // add the current data to the corresponding curve
                       //addData(ui->qtplot,dev_name,timestamp,flowrate);
                       addData(ui->qtplot_2,dev_name,timestamp,voltage);
                       addData(ui->qtplot_3,dev_name, timestamp,irvalue);

                     //  if(relaystate==0)  {ui->on->setStyleSheet("background-color:  #D1CCCC");
                    //   ui->off->setStyleSheet("background-color: red");}
                     //  if(relaystate==1)  {ui->on->setStyleSheet("background-color: red");
                      // ui->off->setStyleSheet("background-color:  #D1CCCC");}


                       prev_last_row +=1;
//.........这里部分代码省略.........