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

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;
}

bool DatabaseMgrSQLite::insertProduct(const QString &aqstrProduct, const QString &aqstrCategory)	{
	if(m_QSqlDatabase.open())	{
		QString qry;
		QSqlQuery tempQuery;

		//Check if the Product defined via the argument is already in the table
		QString tqry = QString("SELECT ProductID FROM Product WHERE ProductName = '%1'").arg(aqstrProduct);
		tempQuery.exec(tqry);

		if(tempQuery.last() == '\0')	{
			//Product not found and should be inserted
			qry = QString("INSERT INTO Product(ProductName, CategoryID) "
				"SELECT '%1', CategoryID FROM Category WHERE CategoryName = '%2'").arg(aqstrProduct).arg(aqstrCategory);
			QSqlQuery query(qry);
		}
		else	{
			qDebug() << QString("Product %1 already in the database").arg(aqstrProduct);
		}
		return true;
	}
	else	{
		std::cout << "Error opening: " << lastError().text().toStdString() << std::endl;
		return false;
	}
}

//------------------------------------------------------------------------------------------
bool DatabaseMgrSQLite::insertCategory(const QString &aqstrCategory, const QString &aqstrObjectType) {
	if(m_QSqlDatabase.open())	{
		QString qry;
		QSqlQuery tempQuery;

		//Check if the Category defined via the argument is already in the table
		QString tqry = QString("SELECT CategoryID FROM Category WHERE CategoryName = '%1'").arg(aqstrCategory);
		tempQuery.exec(tqry);

		if(tempQuery.last() == '\0')	{
			//Category not found and should be inserted
			qry = QString("INSERT INTO Category(CategoryName, ObjectTypeID) "
				"SELECT '%1', ObjectTypeID FROM ObjectType WHERE ObjectTypeName = '%2'").arg(aqstrCategory).arg(aqstrObjectType);
			QSqlQuery query(qry);
		}
		else	{
			qDebug() << QString("Category %1 already in the database.").arg(aqstrCategory);
		}
		return true;
	}
	else	{
		std::cout << "Error opening: " << lastError().text().toStdString() << std::endl;
		return false;
	}
}

void VRShopEditorDlg::updateProduct(const QString& aqstrCategoryName)	{
    //Clear any content of the ComboBox Product
    m_pComboBoxProductPose->clear();
    m_pComboBoxProductCodePose->clear();
    QSqlQuery tQuery;
    //Check if the Category defined via the argument has any element
    QString tqry = QString("SELECT Product.ProductName FROM Product "
                           "JOIN Category "
                           "ON Product.CategoryID=Category.CategoryID "
                           "AND Category.CategoryName = '%1'").arg(aqstrCategoryName);
    tQuery.exec(tqry);
    //See if category includes any item
    if(tQuery.last() == '\0')	{
        std::cout << "Product is empty." << std::endl;
        m_pComboBoxProductPose->addItem("<empty>");
    }
    else	{
        //Not empty, navigate through results
        std::cout << "Product is not empty." << std::endl;
        QStringList items;
        QString item;
        if (tQuery.exec(tqry))	{
            //next() advances through results until the last item
            while(tQuery.next())	{
                items.push_back(tQuery.value(0).toString());
            }
            //Populate ComboBox with adequate data
            m_pComboBoxProductPose->addItems(items);
        }
    }
}

void VRShopEditorDlg::updateCategory()	{
    QSqlQuery tQuery;
    //Check if the Scene defined via the argument is already in the table
    QString tqry = QString("SELECT CategoryName FROM Category");
    tQuery.exec(tqry);
    //See if category includes any item
    if(tQuery.last() == '\0')	{
        std::cout << "Category is empty." << std::endl;
        m_pComboBoxCategoryPose->addItem("<empty>");
    }
    else	{
        //Not empty, navigate through results
        std::cout << "Category is not empty." << std::endl;
        QStringList items;
        QString item;
        if (tQuery.exec(tqry))	{
            //next() advances through results until the last item
            while(tQuery.next())	{
                items.push_back(tQuery.value(0).toString());
            }
            //Populate ComboBox with adequate data
            m_pComboBoxCategoryPose->addItems(items);
        }
    }
}

//---------------------------------------------------------------------------------------------
bool DatabaseMgrSQLite::insertProductCodeImage(const QString &aqstrProductCodeNumber, const QString &aqstrProductCodeImage)	{
	if(m_QSqlDatabase.open())	{
		QString qry;
		QSqlQuery tempQuery;

		//Check if the Product code image defined via the argument is already in the table
		QString tqry = QString("SELECT ProductCodeImageID FROM ProductCodeImage WHERE ProductCodeImageName = '%1'").arg(aqstrProductCodeImage);
		tempQuery.exec(tqry);
		if(tempQuery.last() == '\0')	{
			//Product not found and should be inserted
			qry = QString("INSERT INTO ProductCodeImage(ProductCodeImageName, ProductCodeID) "
				"SELECT '%1', ProductCodeID FROM ProductCode WHERE ProductCodeNumber = '%2'").arg(aqstrProductCodeImage).arg(aqstrProductCodeNumber);
			QSqlQuery query(qry);
		}
		else	{
			//Product code Image already in the database.
			qDebug() << QString("Product Code Image %1 already in the database.").arg(aqstrProductCodeImage);
		}
		return true;
	}
	else	{
		std::cout << "Error opening: " << lastError().text().toStdString() << std::endl;
		return false;
	}
}

vector<CategoryStats> DatabaseHelper::getYearStats(SortOrder sortOrder)
{
    QSqlQuery query;
    query.exec("SELECT DISTINCT year FROM pl_paper");

    vector<int> years;
    while (query.next()) {
        int year = query.value(0).toInt();
        years.push_back(year);
    }

    vector<CategoryStats> stats(years.size());
    for (vector<CategoryStats>::size_type i = 0; i < stats.size(); ++i) {
        query.prepare("SELECT COUNT(*) FROM pl_paper WHERE year = :year");
        query.bindValue(":year", years[i]);
        query.exec();
        query.last();

        stats[i].setName(to_string(static_cast<long long>(years[i])));
        stats[i].setCount(query.value(0).toInt());
    }

    sortStats(stats, sortOrder);

    return stats;
}

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();
        }
    }
}

void VRShopEditorDlg::addImage2Widget(const QString& aqstrProductCode)	{
    QSqlQuery tQuery;
    //Check if the Category defined via the argument has any element
    QString tqry = QString("SELECT ProductCodeImage.ProductCodeImageName FROM ProductCodeImage "
                           "JOIN ProductCode "
                           "ON ProductCodeImage.ProductCodeID=ProductCode.ProductCodeID "
                           "AND ProductCode.ProductCodeNumber = '%1'").arg(aqstrProductCode);
    tQuery.exec(tqry);
    //See if category includes any item
    if(tQuery.last() == '\0')	{
        std::cout << "Product image is empty." << std::endl;
    }
    else	{
        //Not empty, navigate through results
        std::cout << "Product image is not empty." << std::endl;
        QString name;
        if (tQuery.exec(tqry))	{
            //next() advances through results until the last item
            while(tQuery.next())	{
                name = tQuery.value(0).toString();
            }
            FunctorPathResource f;
            QImageReader image(f("/Images/").c_str()+ name);
            m_pImageViewerChoosingPose->setPixmap(QPixmap::fromImageReader(&image));
        }
    }
}

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());//读取广告
            }
        }
    }
}

//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;
}

int DatabaseHelper::getTagId(const string& tag)
{
    QSqlQuery query;
    query.prepare("SELECT tag_id FROM pl_tag WHERE tag_name = :tag_name");
    query.bindValue(":tag_name", tag.c_str());
    query.exec();

    if (!query.last()) return -1;
    return query.value(0).toInt(0);
}

int DatabaseHelper::getBookTitleId(const string& bookTitle)
{
    QSqlQuery query;
    query.prepare("SELECT book_title_id FROM pl_book_title WHERE book_title_name = :book_title_name");
    query.bindValue(":book_title_name", bookTitle.c_str());
    query.exec();

    if (!query.last()) return -1;
    return query.value(0).toInt();
}

string DatabaseHelper::getTag(int tagId)
{
    QSqlQuery query;
    query.prepare("SELECT tag_name FROM pl_tag WHERE tag_id = :tag_id");
    query.bindValue(":tag_id", tagId);
    query.exec();

    if (!query.last()) return string();
    return query.value(0).toString().toStdString();
}

string DatabaseHelper::getAuthor(int authorId)
{
    QSqlQuery query;
    query.prepare("SELECT author_name FROM pl_author WHERE author_id = :author_id");
    query.bindValue(":author_id", authorId);
    query.exec();

    if (!query.last()) return string();
    return query.value(0).toString().toStdString();
}