c++ - SQLite+Qt : Select from table always returns single row

Question

我使用以下代码使用 Qt 从 SQLite 数据库中获取数据:

QSqlQuery wordQuery( QString( "SELECT id, word FROM Words WHERE ref_id = :ref_id" ) );
wordQuery.bindValue( ":ref_id", refId );
wordQuery.setForwardOnly( true );
wordQuery.exec( );

while( wordQuery.next( ) ) {
    // obtain data from `wordQuery` using QSqlQuery::value( )
}

Words 表确实包含 ref_id 字段等于所需值的行。我已经使用作为 Qt 演示应用程序提供的 sqlbrowser 检查了这一点。但是 QSqlQuery::next( ) 仅在第一次返回 true，我无法获取其余行。

顺便说一句，我在这里找到了similar question但对我来说，向后迭代不起作用

下面我放了整个函数代码:

QList<Word> LyricsMasterModel::fetchWords( const DbId &refId )
{
QList<Word> result;

if ( !db.isOpen( ) && !db.open( ) ) {
    return result;
}

QSqlQuery wordQuery;
wordQuery.setForwardOnly( true );
wordQuery.prepare( QString( "SELECT id, word FROM %1 WHERE ref_id = :ref_id" )
    .arg( WORDS_TABLE_NAME ) );
wordQuery.bindValue( ":ref_id", refId );
if ( !wordQuery.exec( ) ) {
    qDebug( ) << "SQL QUERY ERROR:" << wordQuery.lastError( ).text( );
}

const QSqlRecord wordTuple = wordQuery.record( );
const int idIndex = wordTuple.indexOf( "id" );
Q_ASSERT( -1 != idIndex );
const int wordIndex = wordTuple.indexOf( "word" );
Q_ASSERT( -1 != wordIndex );

while (wordQuery.next()) {
    Word word;

    word.setId( wordQuery.value( idIndex ).toByteArray( ) );
    word.setSongId( refId );
    word.setWord( wordQuery.value( wordIndex ).toString( ) );

    result << word;
}

db.close( );
return result;
}

解决方案:我没能找到问题的原因，但以下变体似乎是有效的:

QSqlQuery wordQuery;
wordQuery.prepare( QString( "SELECT id, word FROM %1 WHERE ref_id = :ref_id" )
    .arg( WORDS_TABLE_NAME ) );
wordQuery.bindValue( ":ref_id", refId );
const bool res = wordQuery.exec( );
Q_ASSERT( res );

if ( wordQuery.last( ) ) {
    do {
        // do stuff
    } while (wordQuery.previous());
}

Answer 1

您的代码在绑定(bind)方面存在一个主要问题，即:

QSqlQuery::QSqlQuery(const QString & query = QString(), QSqlDatabase db = QSqlDatabase())

Constructs a QSqlQuery object using the SQL query and the database db. If db is not specified, or is invalid, the application's default database is used. If query is not an empty string, it will be executed.

因此，您的查询将在构造期间执行，因为它不是空的，并且绑定(bind)已经太晚了。

我会亲自构造默认为空字符串的实例，然后根据 documentation 进行显式准备。 .

所以，我会这样写:

QSqlQuery wordQuery();
wordQuery.setForwardOnly(true);
wordQuery.prepare("SELECT id, word FROM Words WHERE ref_id = :ref_id");
wordQuery.bindValue(":ref_id", refId);
if (!wordQuery.exec())
    qDebug() << "SQL QUERY ERROR:" << wordQuery.lastError().text();

while (wordQuery.next()) {
    // obtain data from `wordQuery` using QSqlQuery::value( )
}