C++ 在 Qt 中使用 SQLite

Using SQLite with Qt

提问by Stick it to THE MAN

I am thinking of using SQLite as a backend DB for a C++ applicatiojn I am writing. I have read the relevant docs on both teh trolltech site and sqlite, but the information seems a little disjointed, there is no simple snippet that shows a complete CRUD example.

我正在考虑使用 SQLite 作为我正在编写的 C++ 应用程序的后端数据库。我已经阅读了 trolltech 站点和 sqlite 上的相关文档，但信息似乎有点脱节，没有显示完整 CRUD 示例的简单片段。

I want to write a set of helper functions to allow me to execute CRUD actions in SQLite easily, from my app.

我想编写一组辅助函数，以允许我从我的应用程序中轻松地在 SQLite 中执行 CRUD 操作。

The following smippet is pseudocode for the helper functions I envisage writing. I would be grateful for suggestions on how to "fill up" the stub functions. One thing that is particularly frustrating is that there is no clear mention in any of the docs, on the relationship between a query and the database on which the query is being run - thus suggesting some kind of default connection/table.

以下 smippet 是我设想编写的辅助函数的伪代码。对于如何“填充”存根函数的建议，我将不胜感激。特别令人沮丧的一件事是，在任何文档中都没有明确提及查询与运行查询的数据库之间的关系 - 因此建议使用某种默认连接/表。

In my application, I need to be able to explicitly specify the database on which queries are run, so it would be useful if any answers spell out how to explicitly specify the database/table involved in a query (or other database action for that matter).

在我的应用程序中，我需要能够显式指定运行查询的数据库，因此如果任何答案说明如何显式指定查询中涉及的数据库/表（或其他数据库操作），这将很有用）。

My pseudocode follows below:

我的伪代码如下：

#include <boost/shared_ptr.hh>

typedef boost::shared_ptr<QSqlDatabase> dbPtr;


dbPtr createConnection(const QString& conn_type = "QSQLITE", const QString& dbname = ":memory:")
{
    dbPtr db (new QSQlDatabase::QSqlDatabase());

    if (db.get())
    {
        db->addDatabase(conn_type);
        db->setDatabaseName(dbname);

        if (!db.get()->open)
            db.reset();
    }

    return db;
}

bool runQuery(const Qstring& sql)
{
    //How does SQLite know which database to run this SQL statement against ?
    //How to iterate over the results of the run query?
}

bool runPreparedStmtQuery(const QString query_name, const QString& params)
{
    //How does SQLite know which database to run this SQL statement against ?
    //How do I pass parameters (say a comma delimited list to a prepared statement ?
    //How to iterate over the results of the run query?
}

bool doBulkInsertWithTran(const Qstring& tablename, const MyDataRows& rows)
{
    //How does SQLite know which database to run this SQL statement against ?
    //How to start/commit|rollback
}

In case what I'm asking is not clear, I am asking what would be the correct wat to implement each of the above functions (possibly with the exception of the first - unless it can be bettered of course).

如果我的要求不清楚，我会问什么是实现上述每个功能的正确 wat（可能除了第一个 - 除非它当然可以改进）。

[Edit]

[编辑]

Clarified question by removing requirement to explicitly specify a table (this is already done in the SQL query - I forgot. Thanks for pointing that out Tom

通过删除明确指定表的要求来澄清问题（这已经在 SQL 查询中完成 - 我忘记了。感谢您指出这一点 Tom

回答by Ton van den Heuvel

By default, Qt uses the application's default database to run queries against. That is the database that was added using the default connection name. See the Qt documentationfor more information. I am not sure what you mean by the default database table, since the table to operate on is normally specified in the query itself?

默认情况下，Qt 使用应用程序的默认数据库来运行查询。这是使用默认连接名称添加的数据库。有关更多信息，请参阅Qt 文档。我不确定默认数据库表是什么意思，因为要操作的表通常是在查询本身中指定的？

To answer your question, here is an implementation for one of your methods. Note that instead of returning a boolI would return a QSqlQueryinstance instead to be able to iterate over the results of a query.

为了回答您的问题，这里是您的一种方法的实现。请注意，bool我将返回一个QSqlQuery实例而不是返回一个实例，以便能够迭代查询的结果。

QSqlQuery runQuery(const Qstring& sql)
{
    // Implicitly uses the database that was added using QSqlDatabase::addDatabase()
    // using the default connection name.
    QSqlQuery query(sql);
    query.exec();
    return query;
}

You would use this as follows:

您可以按如下方式使用它：

QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
db.setHostName("localhost");
db.setDatabaseName("data.db");
if (!db.open())
{
  raise ...
}

QSqlQuery query = runQuery("SELECT * FROM user;");
while (query.next())
{
  ...
}

Note that it is also possible to explicitly specify for which database a query should be run by explicitly specifying the relevant QSqlDatabaseinstance as the second parameter for the QSqlQueryconstructor:

请注意，还可以通过将相关QSqlDatabase实例显式指定为QSqlQuery构造函数的第二个参数来显式指定应为哪个数据库运行查询：

QSqlDatabase myDb;
...
QSqlQuery query = QSqlQuery("SELECT * FROM user;", myDb);
...