Qt and SQLite and in general, DB programming in Qt
Good day.
Below is a discussion of how to use SQLite in Qt . The author tried as much as possible to consider programming databases in Qt.
You can read about these two great products by following the links above, and we will specifically consider programming DB in Qt, in particular, using the example of SQLite. I can only say that SQLite is somewhat different from “ordinary” databases, such as MySQL, in that it does not have a client-server architecture. That is, the database engine is not a separate working process with which the program interacts. SQLite is the library with which your program is built and, thus, the engine becomes an integral part of the program. That is, imagine you decided to save all the data with which your program “collides” into a regular file. One day you decide to save the data in a file, but by organizing it from a "relational" point of view. After that, you realized that the new file structure should be "recognized in a special way." From this, at a minimum, it follows that you need to provide some API that provides a link between this data file and the application. In general, following the logical formulation of the above scenario, you have a database system that does not require a database server and the client itself. It turns out quite fast compared to the "client-server" database system, and the program itself is simplified.
I am on friendly terms with Qt and I recently needed its database functionality. With MySQL, I am also on quite friendly terms and tried to use Qt with MySQL in a program I was developing at that time. Having a lack of time and nerves to “link” MySQL with Qt, I decided to use SQLite, for which Qt has, so to speak, built-in support, that is, there was no need to install / configure anything new (this does not apply to the case if your Qt compiled with the support of "read" modules, without connecting the module QtSql). And yet, if I have to install the program in another computer, I will not “have to” install MySQL server, etc. (controversial topic - I know).
')
At the moment I use the SQLiteManager program to create databases, tables, etc., I use it recently, but I immediately liked the program. In my “workhorse”, the (a?) Qt Windows SDK is installed and I use QtCreator, I’ll say straight away - just shine ( not IMHO, really an excellent IDE ).
As already implicitly mentioned above, Qt has a separate module that provides a convenient "service" of using the database - QtSql . If you have experience with Qt, then you know about the .pro file, if not, please get acquainted . Just remember to add the following line to the .pro file:
This is to use the QtSql module, and to work with its classes, you need to include the title of the same name.
The Qt books talk about three levels of the QtSql module:
The driver level includes classes for obtaining data at the physical level, such as:
QSqlDriverCreator is a template class that provides a SQL driver factory for a specific driver type. The template parameter must be a subclass of QSqlDriver .
QSqlCreatorBase is the base class for the SQL driver factories; to return an instance of a specific class of the QSqlDriver class that you want to provide, you need to "rephrase" the createObject () method.
QSqlDatabase is responsible for loading and managing database driver plugins. When a database is added (this is done by the QSqlDatabase :: addDatabase () function), the necessary driver plugin is loaded (using QSqlDriverPlugin ). QSqlDriverPlugin provides an abstract base class for custom QSqlDriver plug-ins.
QSqlResult itself speaks about itself (like all Qt-shny classes), this class provides an abstract interface for accessing data from specific databases. From a practical point of view, we will use QSqlQuery instead of QSqlResult , since QSqlQuery provides a wrapper (“generic”) for the database-specific implementation of QSqlResult .
So, since the level of drivers, as it turned out, is important to use when creating your own driver, then I give an example of code (for the most interested), which can be used as a frame for the driver:
To connect to the database, first of all you need to activate the driver using the static method QSqlDatabase :: addDatabase () . The method receives a string as an argument that identifies the identifier of the database driver . We need "QSQLITE".
The static function addDatabase has an overloaded “brother” that receives not the driver name, but the driver itself ( QSqlDriver *).
Connection is carried out by the open () method. The QSqlDatabase class represents a database connection. The connection provides access to the database through a supported database driver. It is important that you can have multiple connections to the same database.
If an error occurred during the connection (the open () method ) , you can get information about the error through the QSqlDatabase :: lastError () method (returns QSqlError ).
Consider how Qt allows you to execute SQL commands. To do this, you can use the QSqlQuery class. The class can be used not only to execute DML (Data Manipulation Language) expressions, such as SELECT , INSERT , UPDATE and DELETE , but also DDL (Data Definition Language) expressions, such as CREATE TABLE . Note that a DB-specific command that is not an SQL standard can also be executed (for example, for PSQL - “SET DATESTYLE = ISO”).
Successfully executed queries set the state of the request to “active”, so isActive () will return true , otherwise, the state is set to inactive. Requests are executed in the form of a regular string, which is passed to the constructor or to the QSqlQuery :: exec () method. In the first case, when passed to the constructor, the command will be launched automatically (when the object is constructed).
What is very interesting is the navigation feature provided by QSqlQuery . For example, after a SELECT query, you can navigate through the collected data using the next (), previous (), first (), last (), and seek () methods.
The next () method allows moving to the next data line, and calling previous () on the previous line, respectively. first (), last () extract, respectively, the first record from the result. seek () takes an integer index, extracting the record from the result by the received index and “positions the query” on the retrieved record. You can check the size, or rather the number of rows of data (result) using the size () method. It is important to remember that the first record is at position 0, the query must be in the active state, and isSelect () returns true (this happens if the last query was a SELECT ) before calling the seek () method. I advise you to read more about the seek () method in the official documentation .
As mentioned above, if you pass a query string to the constructor of the QSqlQuery class, the query will be executed when the object is created — during construction. Using the exec () method, you can, so to speak, track the execution time of queries. Design
What is interesting, so after execution, the request is positioned on the invalid (th?) Record, that is, to adequately use the results, you must use, say, the next () method.
The exec () method has an overloaded alternative that takes no arguments. Calling this option exec () executes the previously prepared request. Please note - "prepared." To do this, use the prepare () method, which returns true if the request is successfully prepared.
The importance or, one may say, the uniqueness of the method is that the request may contain “placeholders” for binding to values ​​using bindValue ().
You can also use the option of anonymous parameters:
Finally, you can simply use the inline arguments that QString provides:
Copy-paste-to-your-ide example:
To get the result of the query, call the QSqlQuery :: value () method in which the column number should be passed, for which the example used the record () method. This method returns an object of class QSqlRecord , which contains information related to the SELECT query. Using the QSqlRecord :: indexOf () call, we get the column index.
The value () method returns values ​​of type QVariant (a class whose objects can contain values ​​of different types), so you converted the resulting value using the methods QVariant :: toInt () and QVariant :: toString () .
The QtSql module supports the “Interview” concept by providing a number of models for use in views. To thoroughly get acquainted with this concept - take a look here .
As an example, the QSqlTableModel class allows you to display data in tabular and hierarchical form. As stated in the literature, the interview is the easiest way to display the data in the table; here you will not need a cycle to go through the rows of the table. Here is a tiny example:
Once connected, a QTableView table view object and a QSqlTableModel table model object are created. The setTable () method sets the current database in the model, and the select () call fills it with data.
The QSqlTableModel class provides the following editing strategies (settable with setEditStrategy ()):
I think the post came up to the epilogue, and I just left hoping that someone would find what he was looking for, and someone would need the information “for later”.
Below is a discussion of how to use SQLite in Qt . The author tried as much as possible to consider programming databases in Qt.
You can read about these two great products by following the links above, and we will specifically consider programming DB in Qt, in particular, using the example of SQLite. I can only say that SQLite is somewhat different from “ordinary” databases, such as MySQL, in that it does not have a client-server architecture. That is, the database engine is not a separate working process with which the program interacts. SQLite is the library with which your program is built and, thus, the engine becomes an integral part of the program. That is, imagine you decided to save all the data with which your program “collides” into a regular file. One day you decide to save the data in a file, but by organizing it from a "relational" point of view. After that, you realized that the new file structure should be "recognized in a special way." From this, at a minimum, it follows that you need to provide some API that provides a link between this data file and the application. In general, following the logical formulation of the above scenario, you have a database system that does not require a database server and the client itself. It turns out quite fast compared to the "client-server" database system, and the program itself is simplified.
I am on friendly terms with Qt and I recently needed its database functionality. With MySQL, I am also on quite friendly terms and tried to use Qt with MySQL in a program I was developing at that time. Having a lack of time and nerves to “link” MySQL with Qt, I decided to use SQLite, for which Qt has, so to speak, built-in support, that is, there was no need to install / configure anything new (this does not apply to the case if your Qt compiled with the support of "read" modules, without connecting the module QtSql). And yet, if I have to install the program in another computer, I will not “have to” install MySQL server, etc. (controversial topic - I know).
')
Fiy
At the moment I use the SQLiteManager program to create databases, tables, etc., I use it recently, but I immediately liked the program. In my “workhorse”, the (a?) Qt Windows SDK is installed and I use QtCreator, I’ll say straight away - just shine ( not IMHO, really an excellent IDE ).
And so, Qt and databases
As already implicitly mentioned above, Qt has a separate module that provides a convenient "service" of using the database - QtSql . If you have experience with Qt, then you know about the .pro file, if not, please get acquainted . Just remember to add the following line to the .pro file:
QT += sql This is to use the QtSql module, and to work with its classes, you need to include the title of the same name.
#include <QtSql> The Qt books talk about three levels of the QtSql module:
- Driver level
- Program level
- User interface level
Driver level
The driver level includes classes for obtaining data at the physical level, such as:
- QSqlDriver
- QSqlDriverCreator <T *>
- QSqlDriverCreatorBase,
- QSqlDriverPlugin
- QSqlResult
QSqlDriverCreator is a template class that provides a SQL driver factory for a specific driver type. The template parameter must be a subclass of QSqlDriver .
QSqlCreatorBase is the base class for the SQL driver factories; to return an instance of a specific class of the QSqlDriver class that you want to provide, you need to "rephrase" the createObject () method.
QSqlDatabase is responsible for loading and managing database driver plugins. When a database is added (this is done by the QSqlDatabase :: addDatabase () function), the necessary driver plugin is loaded (using QSqlDriverPlugin ). QSqlDriverPlugin provides an abstract base class for custom QSqlDriver plug-ins.
QSqlResult itself speaks about itself (like all Qt-shny classes), this class provides an abstract interface for accessing data from specific databases. From a practical point of view, we will use QSqlQuery instead of QSqlResult , since QSqlQuery provides a wrapper (“generic”) for the database-specific implementation of QSqlResult .
So, since the level of drivers, as it turned out, is important to use when creating your own driver, then I give an example of code (for the most interested), which can be used as a frame for the driver:
class XyzResult : public QSqlResult { public: XyzResult(const QSqlDriver *driver) : QSqlResult(driver) {} ~XyzResult() {} protected: QVariant data(int /* index */) { return QVariant(); } bool isNull(int /* index */) { return false; } bool reset(const QString & /* query */) { return false; } bool fetch(int /* index */) { return false; } bool fetchFirst() { return false; } bool fetchLast() { return false; } int size() { return 0; } int numRowsAffected() { return 0; } QSqlRecord record() const { return QSqlRecord(); } }; class XyzDriver : public QSqlDriver { public: XyzDriver() {} ~XyzDriver() {} bool hasFeature(DriverFeature /* feature */) const { return false; } bool open(const QString & /* db */, const QString & /* user */, const QString & /* password */, const QString & /* host */, int /* port */, const QString & /* options */) { return false; } void close() {} QSqlResult *createResult() const { return new XyzResult(this); } }; Program level
To connect to the database, first of all you need to activate the driver using the static method QSqlDatabase :: addDatabase () . The method receives a string as an argument that identifies the identifier of the database driver . We need "QSQLITE".
QSqlDatabase sdb = QSqlDatabase::addDatabase("QSQLITE"); sdb.setDatabaseName("db_name.sqlite"); if (!sdb.open()) { //.... } The static function addDatabase has an overloaded “brother” that receives not the driver name, but the driver itself ( QSqlDriver *).
Connection is carried out by the open () method. The QSqlDatabase class represents a database connection. The connection provides access to the database through a supported database driver. It is important that you can have multiple connections to the same database.
If an error occurred during the connection (the open () method ) , you can get information about the error through the QSqlDatabase :: lastError () method (returns QSqlError ).
if (!sdb.open()) { qDebug() << sdb.lastError().text(); } Consider how Qt allows you to execute SQL commands. To do this, you can use the QSqlQuery class. The class can be used not only to execute DML (Data Manipulation Language) expressions, such as SELECT , INSERT , UPDATE and DELETE , but also DDL (Data Definition Language) expressions, such as CREATE TABLE . Note that a DB-specific command that is not an SQL standard can also be executed (for example, for PSQL - “SET DATESTYLE = ISO”).
Successfully executed queries set the state of the request to “active”, so isActive () will return true , otherwise, the state is set to inactive. Requests are executed in the form of a regular string, which is passed to the constructor or to the QSqlQuery :: exec () method. In the first case, when passed to the constructor, the command will be launched automatically (when the object is constructed).
What is very interesting is the navigation feature provided by QSqlQuery . For example, after a SELECT query, you can navigate through the collected data using the next (), previous (), first (), last (), and seek () methods.
QSqlQuery query("SELECT country FROM artist"); while (query.next()) { QString country = query.value(0).toString(); do_something(country); } The next () method allows moving to the next data line, and calling previous () on the previous line, respectively. first (), last () extract, respectively, the first record from the result. seek () takes an integer index, extracting the record from the result by the received index and “positions the query” on the retrieved record. You can check the size, or rather the number of rows of data (result) using the size () method. It is important to remember that the first record is at position 0, the query must be in the active state, and isSelect () returns true (this happens if the last query was a SELECT ) before calling the seek () method. I advise you to read more about the seek () method in the official documentation .
As mentioned above, if you pass a query string to the constructor of the QSqlQuery class, the query will be executed when the object is created — during construction. Using the exec () method, you can, so to speak, track the execution time of queries. Design
QSqlQuery query("SELECT country FROM artist"); can also be represented as: QSqlQuery query; query.exec("SELECT country FROM artist"); So, exec () receives a request in the form of a QString . When the query is executed, in case of success, this method returns true and sets the state to the active, in the opposite case, all “opposite” to the specified operations. Of course, it should also be remembered that the query string must obey the syntactic rules of the requested database (in particular, the SQL standard).What is interesting, so after execution, the request is positioned on the invalid (th?) Record, that is, to adequately use the results, you must use, say, the next () method.
The exec () method has an overloaded alternative that takes no arguments. Calling this option exec () executes the previously prepared request. Please note - "prepared." To do this, use the prepare () method, which returns true if the request is successfully prepared.
The importance or, one may say, the uniqueness of the method is that the request may contain “placeholders” for binding to values ​​using bindValue ().
QSqlQuery my_query; my_query.prepare("INSERT INTO my_table (number, address, age)" "VALUES (:number, :address, :age);"); my_query.bindValue(":number", "14"); my_query.bindValue(":address", "hello world str."); my_query.bindValue(":age", "37"); You can also use the option of anonymous parameters:
QSqlQuery my_query; my_query.prepare("INSERT INTO my_table (number, address, age)" "VALUES (?, ?, ?);"); my_query.bindValue("14"); my_query.bindValue("hello world str."); my_query.bindValue("37"); Finally, you can simply use the inline arguments that QString provides:
QSqlQuery my_query; my_query.prepare( QString("INSERT INTO my_table (number, address, age) VALUES (%1, '%2', %3);") .arg("14").arg("hello world str.").arg("37") ); Copy-paste-to-your-ide example:
#include <QtGui/QApplication> #include <QtSql> int main(int argc, char *argv[]) { QCoreApplication app(argc, argv); QSqlDatabase dbase = QSqlDatabase::addDatabase("QSQLITE"); dbase.setDatabaseName("my_db.sqlite"); if (!dbase.open()) { qDebug() << "- !"; return -1; } QSqlQuery a_query; // DDL query QString str = "CREATE TABLE my_table (" "number integer PRIMARY KEY NOT NULL, " "address VARCHAR(255), " "age integer" ");"; bool b = a_query.exec(str); if (!b) { qDebug() << " , !"; } // DML QString str_insert = "INSERT INTO my_table(number, address, age) " "VALUES (%1, '%2', %3);"; str = str_insert.arg("14") .arg("hello world str.") .arg("37"); b = a_query.exec(str); if (!b) { qDebug() << " , , ?"; } //..... if (!a_query.exec("SELECT * FROM my_table")) { qDebug() << " , ."; return -2; } QSqlRecord rec = a_query.record(); int number = 0, age = 0; QString address = ""; while (a_query.next()) { number = a_query.value(rec.indexOf("number")).toInt(); age = a_query.value(rec.indexOf("age")).toInt(); address = a_query.value(rec.indexOf("address")).toString(); qDebug() << "number is " << number << ". age is " << age << ". address" << address; } return app.exec(); } To get the result of the query, call the QSqlQuery :: value () method in which the column number should be passed, for which the example used the record () method. This method returns an object of class QSqlRecord , which contains information related to the SELECT query. Using the QSqlRecord :: indexOf () call, we get the column index.
The value () method returns values ​​of type QVariant (a class whose objects can contain values ​​of different types), so you converted the resulting value using the methods QVariant :: toInt () and QVariant :: toString () .
User interface level
The QtSql module supports the “Interview” concept by providing a number of models for use in views. To thoroughly get acquainted with this concept - take a look here .
As an example, the QSqlTableModel class allows you to display data in tabular and hierarchical form. As stated in the literature, the interview is the easiest way to display the data in the table; here you will not need a cycle to go through the rows of the table. Here is a tiny example:
#include <QtGui/QApplication> #include <QtSql> #include <QTableView> int main(int argc, char *argv[]) { QCoreApplication app(argc, argv); QSqlDatabase dbase = QSqlDatabase::addDatabase("QSQLITE"); dbase.setDatabaseName("my_db.sqlite"); if (!dbase.open()) { qDebug() << "- !"; return -1; } QTableView view; QSqlTableModel model; model.setTable("my_table"); model.select(); model.setEditStrategy(QSqlTableModel::OnFieldChange); view.setModel(&model); view.show(); return app.exec(); } Once connected, a QTableView table view object and a QSqlTableModel table model object are created. The setTable () method sets the current database in the model, and the select () call fills it with data.
The QSqlTableModel class provides the following editing strategies (settable with setEditStrategy ()):
- OnRowChange - writes data as soon as the user moves to another row in the table.
- OnFieldChange - makes a record after the user moves to another cell in the table.
- OnManualSubmit - records data on the call to the submitAll () slot. If the revertAll () slot is called , the data is returned to its original state.
I think the post came up to the epilogue, and I just left hoping that someone would find what he was looking for, and someone would need the information “for later”.
Source: https://habr.com/ru/post/128836/
All Articles