Working with databases in Qt in a multithreaded environment
All those who develop applications on Qt, sooner or later face the work with the database in a multithreaded environment. And if you do not read the Assistant carefully, then you may come across some very interesting rakes.
Consider a typical problem. You write an application that works with the database, while you need to access the database from several threads (it does not matter at the same time or not). For example, this happens if you write the server part of a certain application, or you simply have a logging thread with a record in the database.
If we carefully read the assistant (that is, not only the description of classes, but also general articles), we will see the following inscription in the description of multi-threaded programming under Qt: “A connection can only be used by the thread that created it. At the same time, transferring the connection itself or using requests from other streams is not supported ”(loose translation).
Thus, the banal mutex we have planned to connect to the database unfortunately will not work. Also (if we don’t need to divide the connection between several threads all the time, and in the first half of the program’s work, one thread should use it, and then the other) and the moveToThread () method will not work well, for example, when working with sockets.
')
Ok, we cannot share one connection between multiple threads. But how can we get around this problem? I see two ways:
Well, the first option is too simple and is not suitable for the option when threads are created, they do something with the database and almost immediately die (there will be an extra person overhead for a connection). Although for some cases the first option is ideal;)
So the second way.
The Singleton pattern implies that we can have only one object of a certain class, and any treatment will execute code within this object. Further in the article we will slightly deviate from the canonical form of this pattern, but more on that later.
To implement this pattern, we must prohibit the following for the class:
We must also give the opportunity to get this very single instance of the class.
The class is called DatabaseAccessor. We write the minimal implementation of a singleton.
That is, we simply create an object and return it when we first access DatabaseAccessor :: getInstance (). Later we return the same object.
Well, everything is simple, we add a connection to the database to the constructor.
When the program was initialized, we simply registered the necessary data for access to the database and created a connection object to the database.
Now we need to give the opportunity to work with this database. To begin with, we will implement the possibility of a simple query without receiving the data back (update, deletion, insertion without the need to know the new id).
Here we create a public slot in our singleton that accepts a query string and sends it to the database. In a typical flow, we create a signal and connect it to a singleton slot. When starting the stream, we send a request to delete the user with id 5.
First, we need to first decide what we want from our singleton. Either we want it to perform a large number of various queries (similar to the usual class for working with the database), or we have a certain set of typical queries that we need to perform. In the second option, we can transfer all validation to our singleton and thereby reduce the amount of code in the project;) According to the old tradition, we will implement the second option;) Add a method that will check the user's login / password.
Here we added another slot to our singleton, which takes 2 parameters (username and password). Also in our stream we connected it to the signal in the “queue with blocking” mode (that is, the slot will run in the context of the stream with a singleton, but our stream will wait until the signal reaches the addressee). We also added a slot to our stream that accepts the found user id. At the start, the thread emits a signal to check the user and waits until the answer comes (the variable lastResultQueryIsReallyLast is responsible for this). Naturally, Singleton does not know all its user threads, so the invokeMethod () method is used to call the method on the object that sender () returns (this is the method that returns the sender of the signal if we are in the slot). Moreover, the sender method is called directly in order not to wait for the next pass of the event cycle.
In principle, the first method (when we do more general methods of accessing the database) is easily obtained from the second. It is just necessary in the singleton method to bypass all the rows returned by the database and stuff them into some QList, which is returned to the sender of the request.
Principle, it turned out not difficult and quite nice.
Plus, we have the ability to break into several connections (remember, I talked about the retreat from the pattern). In this case, we need to slightly rewrite the method of obtaining the instance (we need to add balancing on several instances and return the least busy plus, of course, remember who took what instance), you also need to add the connection name to the database creation (for example, you can generate the name of the first object access to this instance) and add a method that will return the desired connection name based on the sender of the request.
Description of the environment
Consider a typical problem. You write an application that works with the database, while you need to access the database from several threads (it does not matter at the same time or not). For example, this happens if you write the server part of a certain application, or you simply have a logging thread with a record in the database.
Rake
Rake description
If we carefully read the assistant (that is, not only the description of classes, but also general articles), we will see the following inscription in the description of multi-threaded programming under Qt: “A connection can only be used by the thread that created it. At the same time, transferring the connection itself or using requests from other streams is not supported ”(loose translation).
Explanation of the rake
Thus, the banal mutex we have planned to connect to the database unfortunately will not work. Also (if we don’t need to divide the connection between several threads all the time, and in the first half of the program’s work, one thread should use it, and then the other) and the moveToThread () method will not work well, for example, when working with sockets.
')
Attempt to reflect on the problem
Ok, we cannot share one connection between multiple threads. But how can we get around this problem? I see two ways:
- Do not bathe and make your connection in each stream
- Refer to the good old pattern Singleton
Well, the first option is too simple and is not suitable for the option when threads are created, they do something with the database and almost immediately die (there will be an extra person overhead for a connection). Although for some cases the first option is ideal;)
So the second way.
A bit of theory about the pattern
The Singleton pattern implies that we can have only one object of a certain class, and any treatment will execute code within this object. Further in the article we will slightly deviate from the canonical form of this pattern, but more on that later.
Singleton implementation in C ++
To implement this pattern, we must prohibit the following for the class:
- Creating a new object
- Create a copy of the object
- Object assignment operation
We must also give the opportunity to get this very single instance of the class.
The class is called DatabaseAccessor. We write the minimal implementation of a singleton.
//databaseaccessor.h
class DatabaseAccessor
{
public :
static DatabaseAccessor* getInstance();
private :
DatabaseAccessor();
DatabaseAccessor( const DatabaseAccessor& );
DatabaseAccessor& operator =( const DatabaseAccessor& );
};
//databaseaccessor.cpp
DatabaseAccessor::DatabaseAccessor()
{
}
DatabaseAccessor* DatabaseAccessor::getInstance()
{
static DatabaseAccessor instance;
return &instance;
}
* This source code was highlighted with Source Code Highlighter .That is, we simply create an object and return it when we first access DatabaseAccessor :: getInstance (). Later we return the same object.
Add a connection to the database
Well, everything is simple, we add a connection to the database to the constructor.
//databaseaccessor.h
class DatabaseAccessor
{
public :
static DatabaseAccessor* getInstance();
static QString dbHost;
static QString dbName;
static QString dbUser;
static QString dbPass;
private :
DatabaseAccessor();
DatabaseAccessor( const DatabaseAccessor& );
DatabaseAccessor& operator =( const DatabaseAccessor& );
QSqlDatabase db;
};
//databaseaccessor.cpp
DatabaseAccessor::DatabaseAccessor()
{
db = QSqlDatabase::addDatabase( "QMYSQL" );
db.setHostName(dbHost);
db.setDatabaseName(dbName);
db.setUserName(dbUser);
db.setPassword(dbPass);
if (db.open())
{
qDebug( "connected to database" );
}
else
{
qDebug( "Error occured in connection to database" );
}
}
DatabaseAccessor* DatabaseAccessor::getInstance()
{
static DatabaseAccessor instance;
return &instance;
}
//main.cpp
int main( int argc, char *argv[])
{
//...
DatabaseAccessor::dbHost = "localhost" ;
DatabaseAccessor::dbName = "our_db" ;
DatabaseAccessor::dbUser = "root" ;
DatabaseAccessor::dbPass = "" ;
DatabaseAccessor::getInstance();
//...
}
* This source code was highlighted with Source Code Highlighter .When the program was initialized, we simply registered the necessary data for access to the database and created a connection object to the database.
So, what is next?
Now we need to give the opportunity to work with this database. To begin with, we will implement the possibility of a simple query without receiving the data back (update, deletion, insertion without the need to know the new id).
//databaseaccessor.h
class DatabaseAccessor
{
public :
static DatabaseAccessor* getInstance();
static QString dbHost;
static QString dbName;
static QString dbUser;
static QString dbPass;
public slots:
void executeSqlQuery(QString);
private :
DatabaseAccessor();
DatabaseAccessor( const DatabaseAccessor& );
DatabaseAccessor& operator =( const DatabaseAccessor& );
QSqlDatabase db;
};
//databaseaccessor.cpp
DatabaseAccessor::DatabaseAccessor()
{
db = QSqlDatabase::addDatabase( "QMYSQL" );
db.setHostName(dbHost);
db.setDatabaseName(dbName);
db.setUserName(dbUser);
db.setPassword(dbPass);
if (db.open())
{
qDebug( "connected to database" );
}
else
{
qDebug( "Error occured in connection to database" );
}
}
DatabaseAccessor* DatabaseAccessor::getInstance()
{
static DatabaseAccessor instance;
return &instance;
}
void DatabaseAccessor::executeSqlQuery(QString query)
{
QSqlQuery sqlQuery(query, db);
}
//ourthread.h
class OurThread : public QThread
{
Q_OBJECT
//...
signals:
void executeSqlQuery(QString);
//...
}
//ourthread.cpp
OurThread::OurThread()
{
connect( this , SIGNAL(executeSqlQuery(QString)), DatabaseAccessor::getInstance(), SLOT(executeSqlQuery(QString)));
}
void OurThread::run()
{
emit executeSqlQuery( "DELETE FROM users WHERE uid=5" );
}
* This source code was highlighted with Source Code Highlighter .Here we create a public slot in our singleton that accepts a query string and sends it to the database. In a typical flow, we create a signal and connect it to a singleton slot. When starting the stream, we send a request to delete the user with id 5.
But how to get the result of the query?
First, we need to first decide what we want from our singleton. Either we want it to perform a large number of various queries (similar to the usual class for working with the database), or we have a certain set of typical queries that we need to perform. In the second option, we can transfer all validation to our singleton and thereby reduce the amount of code in the project;) According to the old tradition, we will implement the second option;) Add a method that will check the user's login / password.
//databaseaccessor.h
class DatabaseAccessor
{
public :
static DatabaseAccessor* getInstance();
static QString dbHost;
static QString dbName;
static QString dbUser;
static QString dbPass;
public slots:
void executeSqlQuery(QString);
void validateUser(QString, QString);
private :
DatabaseAccessor();
DatabaseAccessor( const DatabaseAccessor& );
DatabaseAccessor& operator =( const DatabaseAccessor& );
QSqlDatabase db;
};
//databaseaccessor.cpp
DatabaseAccessor::DatabaseAccessor()
{
db = QSqlDatabase::addDatabase( "QMYSQL" );
db.setHostName(dbHost);
db.setDatabaseName(dbName);
db.setUserName(dbUser);
db.setPassword(dbPass);
if (db.open())
{
qDebug( "connected to database" );
}
else
{
qDebug( "Error occured in connection to database" );
}
}
DatabaseAccessor* DatabaseAccessor::getInstance()
{
static DatabaseAccessor instance;
return &instance;
}
void DatabaseAccessor::executeSqlQuery(QString query)
{
QSqlQuery sqlQuery(query, db);
}
void DatabaseAccessor::validateUser(QString login, QString pass)
{
login.remove(QRegExp( "['\"]" ));
pass.remove(QRegExp( "['\"]" ));
QString query = "SELECT IFNULL(uid, -1) as user_id FROM users WHERE username='" +login+ "' AND password='" +pass+ "'" ;
QSqlQuery sqlQuery(query, db);
if (sqlQuery.first())
{
long userId = sqlQuery. value (0).toInt();
QMetaObject::invokeMethod(sender(), "setUserId" , Qt::DirectConnection, Q_ARG( long , userId));
}
else
{
QMetaObject::invokeMethod(sender(), "setUserId" , Qt::DirectConnection, Q_ARG( long , -1));
}
}
//ourthread.h
class OurThread : public QThread
{
Q_OBJECT
//...
signals:
void executeSqlQuery(QString);
void validateUser(QString, QString);
public slots:
void setUserId( long );
private :
bool lastResultQueryIsReallyLast;
long userId;
bool checkUser( const char *, const char *);
//...
}
//ourthread.cpp
OurThread::OurThread()
{
lastResultQueryIsReallyLast = false ;
connect( this , SIGNAL(validateUser(QString,QString)), DatabaseAccessor::getInstance(), SLOT(validateUser(QString,QString)), Qt::BlockingQueuedConnection);
connect( this , SIGNAL(executeSqlQuery(QString)), DatabaseAccessor::getInstance(), SLOT(executeSqlQuery(QString)));
}
void OurThread::run()
{
checkUser( "user" , "password" );
}
bool OurThread::checkUser( const char * login, const char * pass)
{
emit validateUser(login, pass);
while (!lastResultQueryIsReallyLast)
{
msleep(1);
}
lastResultQueryIsReallyLast = false ;
return (userId > 0);
}
void OurThread::setUserId( long userId)
{
this ->userId = userId;
lastResultQueryIsReallyLast = true ;
}
* This source code was highlighted with Source Code Highlighter .Here we added another slot to our singleton, which takes 2 parameters (username and password). Also in our stream we connected it to the signal in the “queue with blocking” mode (that is, the slot will run in the context of the stream with a singleton, but our stream will wait until the signal reaches the addressee). We also added a slot to our stream that accepts the found user id. At the start, the thread emits a signal to check the user and waits until the answer comes (the variable lastResultQueryIsReallyLast is responsible for this). Naturally, Singleton does not know all its user threads, so the invokeMethod () method is used to call the method on the object that sender () returns (this is the method that returns the sender of the signal if we are in the slot). Moreover, the sender method is called directly in order not to wait for the next pass of the event cycle.
In principle, the first method (when we do more general methods of accessing the database) is easily obtained from the second. It is just necessary in the singleton method to bypass all the rows returned by the database and stuff them into some QList, which is returned to the sender of the request.
Finally
Principle, it turned out not difficult and quite nice.
Plus, we have the ability to break into several connections (remember, I talked about the retreat from the pattern). In this case, we need to slightly rewrite the method of obtaining the instance (we need to add balancing on several instances and return the least busy plus, of course, remember who took what instance), you also need to add the connection name to the database creation (for example, you can generate the name of the first object access to this instance) and add a method that will return the desired connection name based on the sender of the request.
Source: https://habr.com/ru/post/52536/
All Articles