database QSqlDatabase & QSqlQuery 的正确方法是什么？

What is the correct way of QSqlDatabase & QSqlQuery?

提问by daisy

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, queryor dbwill be deconstructed automatically. But is that efficient ?

正如文档指出的那样，query还是db会自动解构。但这有效率吗？

Well , if i cache dbinside 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()。

回答by alexisdm

When you create a QSqlDatabaseobject with addDatabaseor 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.

当您QSqlDatabase使用addDatabase或调用创建对象时removeDatabase，您只是将元组（驱动程序、主机名：端口、数据库名称、用户名/密码）与名称（或默认连接名称，如果您未指定）关联或解除关联连接名称）。
SQL 驱动被实例化，但数据库只有在调用QSqlDatabase::open.

That connection name is defined application-wide. So if you call addDatabasein each of the objects that use it, you are changing all QSqlDatabaseobjects 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 QSqlQueryare detached from the QSqlDatabasebefore closing the database by calling QSqlQuery::finish(), which is automatic when the QSqlQueryobject goes out of scope,
• all QSqlDatabasewith the same connection name are close()d when you call QSqlDatabase::removeDatabase(close()is also called automatically when the QSqlDatabaseobject goes out of scope).

• 所有QSqlQuery都QSqlDatabase通过调用从关闭数据库之前分离出来QSqlQuery::finish()，当QSqlQuery对象超出范围时，这是自动的，
• 调用时所有QSqlDatabase具有相同连接名称的都是close()d QSqlDatabase::removeDatabase（close()当QSqlDatabase对象超出范围时也会自动调用）。

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)，您可以：

1. keep a single QSqlDatabaseinstance in one single class (for example, in your mainwindow), and use it in other objects that needs it either by passing the QSqlDatabasedirectly or just the connection name that you pass to QSqlDatabase::databaseto get the QSqlDatabaseinstance back. QSqlDatabase::databaseuses QHashto retrieve a QSqlDatabasefrom its name, so it is probably negligibly slower than passing the QSqlDatabaseobject 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.

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

2. configure the QSqlDatabaseonce, 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:

   {
       // 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).

1. 将单个QSqlDatabase实例保留在一个类中（例如，在您的主窗口中），并通过QSqlDatabase直接传递或仅传递您传递给以QSqlDatabase::database获取QSqlDatabase实例的连接名称，在需要它的其他对象中使用它。QSqlDatabase::database用于从其名称中QHash检索 a 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);             
   

2. 配置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
   } 
   

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

回答by James Turner

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），因此这通常是您在程序终止时会执行的操作。正如警告所说，您必须确保引用该数据库的所有数据库和查询对象都已被破坏，否则可能会发生不好的事情。

回答by Pescolly

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