Good day.
I am mastering Qt work with MySQL database. Please do not laugh hard. Interested in how to properly establish and close a database connection. For example, there is such code:

void DBChooser::fillList() { QSqlDatabase db; db = QSqlDatabase::addDatabase("QMYSQL", "connection"); db.setHostName("127.0.0.1"); db.setPort(3309); db.setUserName("root"); db.setPassword("123987"); if(db.open()) { QSqlQuery query; query.exec("SHOW DATABASES"); while(query.next()) { qDebug()<<query.value(0).toString(); } db.close(); QSqlDatabase::removeDatabase("connection"); } else { QMessageBox::critical(this, "Ошибка", db.lastError().text()); exit(0); } }

The questions are as follows:
1. You can create a connection once for the entire program, and then only open and close the connection ( open() and close() ) to execute each request? Or is all the above text needed to process each request?
2. Purpose of static method QSqlDatabase::removeDatabase() ? If the connection is closed by close() , then why still remove it from the list of connections? And what is this list of connections?
3. How to build a database access layer architecture correctly?
I will be glad to any "crumbs". Thank.

    2 answers 2

    1. You can create a connection once for the entire program, and then only open and close the connection ( open() and close() ) to execute each request? Or is all the above text needed to process each request?

    In Qt, the emphasis is precisely on the fact that once created a connection to the database will last open, if not throughout the entire life cycle of the program, then in any case most of this time. This is a convenient approach if single-threaded access to the DBMS is planned, but it creates certain kinds of problems in the case of multi-threaded access to it.

    It is also necessary to remember that once created connection does not remain in an open state for an infinitely long time (unless otherwise redefined in the server settings). Nevertheless, QSqlDatabase for MySQL (actually, like for some other DBMS) provides the ability to reconnect if the connection is closed on timeout: MYSQL_OPT_RECONNECT=1

    So, if you plan to have single-threaded access to the DBMS, then it’s enough to create a connection once (possibly with the reconnection option) and then use it alone.

    1. The purpose of the static method QSqlDatabase::removeDatabase() ? If the connection is closed by close() , then why still remove it from the list of connections? And what is this list of connections?

    To ensure that the QSqlDatabase connection object can be easily reached from anywhere in the Qt program, all instances of the designated class are stored in a regular static hash:

     static QHash<QString,QSqlDatabase> hash;

    The key in the hash is the string with the name of the connection, and the value of the connection itself. Thus, adding using the method:

     QSqlDatabase QSqlDatabase::addDatabase(const QString &type , const QString &connectionName = QLatin1String(defaultConnection))

    ... a new connection, it will always be possible to get it later, calling for help another method:

     QSqlDatabase QSqlDatabase::database(const QString &connectionName = QLatin1String(defaultConnection), bool open = true)

    In turn method:

     void QSqlDatabase::removeDatabase(const QString &connectionName)

    ... just strip the corresponding element in the hash.

    However, this approach has a significant drawback: you cannot start deleting the connection object until the last of the used QSqlDatabase objects of the same connection, as well as the associated QSqlQuery . This problem assumes that when using QSqlDatabase::database() , a copy of QSqlDatabase returned that is contained in the static hash and shares the same resources with it.

    Of course, you can not call the QSqlDatabase::removeDatabase() method, limiting QSqlDatabase::removeDatabase() to closing the connection, however, with active reconnection to the DBMS, this will cause the static hash to grow from unused QSqlDatabase objects, which in itself is not gut.

    1. How to build a database access layer architecture correctly?

    It depends on how the connection (s) to the database will be used in the program. If this is a single-threaded version, then you can not worry too much and build everything exactly as the Qt help says:

    1. add a connection through QSqlDatabase::addDatabase() somewhere at the start of the program;
    2. We work with queries, setting up a connection object for them, which in turn is obtained through QSqlDatabase::database() ;
    3. call QSqlDatabase::removeDatabase() when exiting the program.

    However, problems begin with multi-threading connections. QSqlDatabase does not support sending requests from other threads, except from the one in which the class object was created. Accordingly, if several streams are involved in the program at once, then each of them will have to create its own connection. Of course, they will have to be removed from the hash in the same place or in another tricky way, since QSqlDatabase::removeDatabase() although declared to be a thread-safe method, it really is not .

    For myself, I have so far developed the only solution that is more or less satisfied in the current situation with a multithreaded connection - the use of QThreadStorage .

    File databaseconn.h :

     #ifndef DATABASECONN_H #define DATABASECONN_H #include <QtSql/QSqlDatabase> class DatabaseConn { public: static QSqlDatabase db(); }; #endif

    File databaseconn.cpp :

     #include <QtCore/QThreadStorage> #include <QtCore/QReadWriteLock> #include <QtCore/QSharedPointer> #include <QtCore/QGlobalStatic> #include <QtCore/QDebug> #include <QtCore/QUuid> #include <QtSql/QSqlQuery> #include <QtSql/QSqlError> #include "adatabaseconn.h"

    First, we protect the three main methods that manage connections to the DBMS with a static mutex.

     Q_GLOBAL_STATIC(QReadWriteLock, _g_rw_lock) QSqlDatabase addDatabase(const QString &cname) { QWriteLocker locker(_g_rw_lock); return QSqlDatabase::addDatabase("QMYSQL", cname); } QSqlDatabase database(const QString &cname) { QReadLocker locker(_g_rw_lock); return QSqlDatabase::database(cname, false); } void removeDatabase(const QString &cname) { QWriteLocker locker(_g_rw_lock); QSqlDatabase::removeDatabase(cname); }

    The DatabaseStorage class when creating its object generates a unique name for the new connection. This is especially useful if transient flows are used, for example, when using QtConcurrent :

     class DatabaseStorage { public: DatabaseStorage() : _cname(QUuid::createUuid().toString()) {} ~DatabaseStorage() {removeDatabase(_cname);} QString _cname; };

    All DatabaseStorage instances are stored in static QThreadStorage . It is, in general, a regular container, but all the elements of which exist and can only be removed in accordance with the flows in which they were created:

     Q_GLOBAL_STATIC(QThreadStorage<QSharedPointer<DatabaseStorage> >, _g_storages)

    The main method of the DatabaseConn class that returns instances of connection objects:

     QSqlDatabase DatabaseConn::db() { // Если ранее в текущем потоке, в котором существует // контекст выполнения, уже было создано подключение, // то и возвращаем именно его. if(_g_storages->hasLocalData() == true) return database(_g_storages->localData()->_cname); // В обратном случае создаём новое подключение. QSharedPointer<DatabaseStorage> storage; storage.reset(new DatabaseStorage()); QSqlDatabase db = addDatabase(storage->_cname); if(db.isValid()) { db.setDatabaseName("..."); db.setUserName("..."); db.setPassword("..."); db.setHostName("..."); db.setPort(3306); db.setConnectOptions("MYSQL_OPT_RECONNECT=1"); if(db.open()) { QStringList qstrs; qstrs.append("SET NAMES 'utf8mb4'"); qstrs.append("SET CHARACTER SET 'utf8mb4'"); qstrs.append("SET SESSION collation_connection" \ " = 'utf8mb4_unicode_ci'"); QSqlQuery query(db); foreach(const QString &qstr, qstrs) { if(!query.exec(qstr)) { QMessageLogger logger(__FILE__, __LINE__ , Q_FUNC_INFO, "DatabaseConn"); logger.warning() << qPrintable(query.lastError().text()); logger.debug() << qPrintable(query.lastQuery()); return QSqlDatabase(); } } // Сохраняем открытое подключение в хранилище. _g_storages->setLocalData(storage); } else { QMessageLogger(__FILE__, __LINE__ , Q_FUNC_INFO, "DatabaseConn") .warning() << qPrintable(db.lastError().text()); } } return db; }

    It should be noted that before the thread ends its existence, the QSqlDatabase::removeDatabase() method will be called QSqlDatabase::removeDatabase() , which closes the connection itself. In other words, the designated method will call QSqlDatabase::close() itself.

    • Thank you for the comprehensive answer. Much is not yet clear, but I will understand, so much interesting information. - Artik

    why open-close all the time? Once opened and run. The opening and closing operation is relatively expensive. As with files. Second, the removeDatabase method serves to remove the connection to a specific database. There may be several of them in the program. Each under its own name (you have a connection)

    • Doesn't MySQL itself close the timeout connection? The period between the processing of requests can be large and muskul closes the connection, then you need to open it again. - Artik
    • may close if configured. But did not notice out of the box. The whole day people are sitting. behind the program, brings in, walks through the offices, for lunch, works from the same place. - maint
    • Are you sure that the program keeps the connection all this time? Can it again create / close them each time the database is accessed? - Artik
    • for me and end user'a like that on the drum in my opinion - maint
    • That I understand that for the user these nuances are in the drum, but I need to write the code (programmer's hare). And I need to know whether to open a connection every time I access the database or not? - Artik