QSqlDatabase & QSqlQuery的正确方式是什么?

I got confused with the manual , should i work like this:

我被手册弄糊涂了，我应该这样工作吗:

{
 QSqlDatabase db = QSqlDatabase::addDatabase (...);
 QSqlQuery query (db);
 query.exec (...);
}

QSqlDatabase::removeDatabase (...);

As the document points out, query or db will be deconstructed automatically. But is that efficient ?

正如文档所指出的，查询或db将被自动解构。但这是有效的吗?

Well , if i cache db inside a class , like the following:

如果我在类中缓存db，如下所示:

class Dummy {
  Dummy() { 
    db = QSqlDatabase::addDatabase (...);
  }
  ~Dummy() {
    db.close();
  }

  bool run() {
    QSqlQuery query (db);
    bool retval = query.exec (...);
    blabla ...
  }

  private:
    QSqlDatabase db;
};

Sometimes i could see warnings like:

有时我可以看到这样的警告:

QSqlDatabasePrivate::removeDatabase: connection 'BLABLA' is still in use, all queries will cease to work.

Even if i didn't call run().

即使我没有调用run()。

3 个解决方案

#1

When you create a QSqlDatabase object with addDatabase or when you call removeDatabase, you are merely associating or disassociating a tuple (driver, hostname:port, database name, username/password) to a name (or to the default connection name if you don't specify a connection name).
The SQL driver is instantiated, but the database will only be opened when you call QSqlDatabase::open.

当您使用addDatabase创建QSqlDatabase对象或调用removeDatabase时，您只是将tuple(驱动程序、主机名:端口、数据库名、用户名/密码)与名称(或不指定连接名的默认连接名)关联或分离。实例化SQL驱动程序，但只有在调用QSqlDatabase::open时才会打开数据库。

That connection name is defined application-wide. So if you call addDatabase in each of the objects that use it, you are changing all QSqlDatabase objects that uses the same connection name and invalidating all queries that were active on them.

该连接名称被定义为应用程序范围。因此，如果在每个使用它的对象中调用addDatabase，就会更改所有使用相同连接名的QSqlDatabase对象，并使它们上的所有查询无效。

The first code example you cited shows how to correctly disassociate the connection name, by ensuring that:

您引用的第一个代码示例展示了如何正确地分离连接名，方法是确保:

all QSqlQuery are detached from the QSqlDatabase before closing the database by calling QSqlQuery::finish(), which is automatic when the QSqlQuery object goes out of scope,
在通过调用QSqlQuery:::finish()关闭数据库之前，所有QSqlQuery都与QSqlDatabase数据库分离，当QSqlQuery对象超出范围时，QSqlQuery会自动执行，
all QSqlDatabase with the same connection name are close()d when you call QSqlDatabase::removeDatabase (close() is also called automatically when the QSqlDatabase object goes out of scope).
当调用QSqlDatabase:::removeDatabase (close())时，具有相同连接名称的所有QSqlDatabase都是close()d。

When you create the QSqlDatabase, depending on whether you want the connection to stay open for the application lifetime (1) or just when needed (2), you can:

当您创建QSqlDatabase时，根据您是希望连接在应用程序生存期(1)中保持打开状态，还是需要(2)时，您可以:

keep a single QSqlDatabase instance in one single class (for example, in your mainwindow), and use it in other objects that needs it either by passing the QSqlDatabase directly or just the connection name that you pass to QSqlDatabase::database to get the QSqlDatabase instance back. QSqlDatabase::database uses QHash to retrieve a QSqlDatabase from its name, so it is probably negligibly slower than passing the QSqlDatabase object directly between objects and functions, and if you you use the default connection, you don't even have to pass anything anywhere, just call QSqlDatabase::database() without any parameter.

在一个类中保存一个QSqlDatabase实例(例如，在您的主窗口中)，并在其他需要它的对象中使用它，要么直接通过QSqlDatabase，要么直接传递到QSqlDatabase::数据库，以获取QSqlDatabase实例。数据库使用QHash从其名称中检索QSqlDatabase，因此它可能比在对象和函数之间直接传递QSqlDatabase对象要慢得多，如果您使用默认连接，您甚至不需要传递任何东西，只需调用QSqlDatabase::database()，而不需要任何参数。
```
// In an object that has the same lifetime as your application
// (or as a global variable, since it has almost the same goal here)
QSqlDatabase db;

// In the constructor or initialization function of that object       
db = QSqlDatabase::addDatabase("QSQLDRIVER", "connection-name"); 
db.setHostname(...);
// ...
if(!this->db.open())  // open it and keep it opened
{
    // Error handling...
}

// --------
// Anywhere you need it, you can use the "global" db object 
// or get the database connection from the connection name        
QSqlDatabase db = QSqlDatabase::database("connection-name"); 
QSqlQuery query(db);             
```
configure the QSqlDatabase once, open it to test that the parameters are correct, and ditch the instance. The connection name, will still be accessible anywhere, but the database will have to be reopened:

配置QSqlDatabase一次，打开它以测试参数是否正确，并抛弃实例。连接名仍然可以在任何地方访问，但是数据库必须重新打开:
```
{
    // Allocated on the stack
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLDRIVER", "connection-name"); 
    db.setHostname(...);
    // ...
    if(!this->db.open()) // test the connection
    {
       // Error handling
    }
// db is closed when it goes out of scope
} 

{
    // Same thing as for (1), but by default database() opens 
    // the connection if it isn't already opened 
    QSqlDatabase db = QSqlDatabase::database("connection-name"); 
    QSqlQuery query(db);

// if there is no other connection open with that connection name,
// the connection is closed when db goes out of scope
} 
```
In that case, note that you shouldn't close the database explicitly, because you can have multiple objects using the same database connection in a reentrant manner (for example, if a function A use the connection and calls B which also use the connection. If B closes the connection before returning control to A, the connection will also be closed for A, which is probably a bad thing).

在这种情况下，请注意，不应该显式地关闭数据库，因为可以有多个对象以可重入的方式使用相同的数据库连接(例如，如果函数a使用连接并调用同样使用连接的函数B)。如果B在将控件返回给A之前关闭连接，那么A的连接也将被关闭，这可能是一件坏事)。

#2

QSqlDatabase and QSqlQuery are lightweight wrappers around concrete implementations, so your first example is fine. If you provide a name when adding the connection, or use the default database, then simply writing 'QSqlDatabase db(name)' gives you the database object with very little overhead.

QSqlDatabase和QSqlQuery是围绕具体实现的轻量级包装器，所以您的第一个示例没有问题。如果您在添加连接时提供了一个名称，或者使用默认的数据库，那么只需编写“QSqlDatabase db(name)”，就会得到一个开销非常小的数据库对象。

removeDatabase is equivalent to closing the file (for sqlite) or the connection (for ODBC/MySql/Postgres), so that's typically something you would do at program termination. As the warning says, you must ensure all database and query objects which refer to that database, have already been destroyed, or bad things can happen.

removeDatabase等同于关闭文件(对于sqlite)或连接(对于ODBC/MySql/Postgres)，因此这通常是程序终止时要做的事情。正如警告所言，您必须确保引用该数据库的所有数据库和查询对象都已被销毁，否则会发生糟糕的事情。

#3

I find that the instructions have to be run exactly in the order it is below or else you have issues, either with the database connection or query. This works in Qt5.

我发现指令必须按照它下面的顺序运行，否则您会遇到数据库连接或查询问题。这在Qt5工作。

QSqlQueryModel *model = new QSqlQueryModel;
db = QSqlDatabase::addDatabase("QSQLITE");
db.setDatabaseName(fileName);

if (db.isValid())
{
    db.open();
    if (db.isOpen())
    {
        QSqlQuery searchQuery(db);
        searchQuery.prepare("SELECT * FROM myTable");
        searchQuery.exec();
        if(searchQuery.isActive())
        {
            model->setQuery(searchQuery);
            sui->DBDisplay->setModel(model);
            db.close();
        } else {
            qDebug() << "query is not active";
        }
    } else {
        qDebug() << "DB is not open";
    }
} else {
    qDebug() << "DB is not valid";
}

#1