Working with databases in Qt

Qt makes it possible to create platform-independent database applications using standard DBMSs. Qt includes native drivers for Oracle, Microsoft SQL Server, Sybase Adaptive Server, IBM DB2, PostgreSQL, MySQL, and ODBC-compatible databases. Qt includes database-specific widgets, and also supports the extension for working with databases of any built-in or separately written widgets.

Introduction

Working with databases in Qt takes place at various levels:
1. Driver Layer - Includes the QSqlDriver, QSqlDriverCreator, QSqlDriverCreatorBase, QSqlDriverPlugin and QSqlResult classes. This layer provides a low-level bridge between specific databases and the SQL API layer.
2.Layer SQL API - This layer provides access to databases. Connections are established using the QSqlDatabase class. Interaction with the database is carried out using the QSqlQuery class. In addition to the QSqlDatabase and QSqlQuery classes, the SQL API layer relies on the QSqlError, QSqlField, QSqlIndex, and QsqlRecord classes.
3. User Interface Layer - This layer associates data from a database with data-oriented widgets. This includes classes such as QSqlQueryModel, QSqlTableModel, and QSqlRelationalTableModel.

Database connection

To access the database using QSqlQuery and QSqlQueryModel, you must create and open one or more database connections.
Qt can work with the following databases (due to incompatibility with the GPL license, not all plugins come with Qt Open Source Edition):

1. QDB2 - IBM DB2 (version 7.1 and higher
2. QIBASE - Borland InterBase
3. QMYSQL - MySQL
4. QOCI - Oracle Call Interface Driver
5. QODBC - Open Database Connectivity (ODBC) - Microsoft SQL Server and other ODBC-compatible databases
6. QPSQL - PostgreSQL (version 7.3 and higher)
7. QSQLITE2 - SQLite version 2
8. QSQLITE - SQLite version 3
9. QTDS - Sybase Adaptive Server Driver

To build the driver plug-in, which are not included in the Qt package, you need to have the appropriate client library for the DBMS used.

You can connect to the database like this:

1.    QSqlDatabase db = QsqlDatabase::addDatabase("QMYSQL", "mydb");
2.    db.setHostName("bigblue");
3.    db.setDatabaseName("flightdb");
4.    db.setUserName("acarlson");
5.    db.setPassword("1uTbSbAs");
6.    bool ok = db.open();

* This source code was highlighted with Source Code Highlighter.

The first line creates the connection object, and the last opens it. In the interval, some connection information is initialized, including connection name, database name, host name, username, password. This example connects to the flightdb MySQL database on the bigblue node. The argument “QMYSQL” in addDatabase () indicates the type of database driver to use for the connection, and “mydb” is the name of the connection.
Once the connection is established, you can call the static function QSqlDatabase :: database () from anywhere in the program with the name of the connection to get a pointer to this connection. If you do not pass the connection name, it will return the connection by default.
If open () fails, it will return false. In this case, you can get information about the error by calling QSqlDatabase :: lastError ().
To remove a connection to the database, you must first close the database using QSqlDatabase :: close (), and then remove the connection using the static method QSqlDatabase :: removeDatabase ().

Executing SQL Statements

The QSqlQuery class provides an interface for executing SQL queries and navigating through the result set.
To execute SQL queries, simply create a QSqlQuery object and call QSqlQuery :: exec (). For example, like this:

1.    QSqlQuery query;
2.    query.exec("SELECT name, salary FROM employee WHERE salary > 50000");

* This source code was highlighted with Source Code Highlighter.

The QSqlQuery constructor takes an optional QSqlDatabase argument, which specifies which database connection is used. If not specified, then the default connection is used.
If an error occurs, exec () returns false. The error can be accessed using QSqlQuery :: lastError ().
QSqlQuery provides one-time access to the result set of a single query. After exec () is called, the internal QSqlQuery pointer points to the position before the first record. If you call the QSqlQuery :: next () method once, it will move the pointer to the first record. After that, you need to repeat the next () call to access other entries until it returns false. Here is a typical loop that goes through all the records in order:

1.    while (query.next()) {
2.      QString name = query.value(0).toString();
3.      int salary = query.value(1).toInt();
4.      qDebug() << name << salary;
5.    }

* This source code was highlighted with Source Code Highlighter.

QSqlQuery can perform not only SELECT, but also any other queries. The following example inserts a record into a table using INSERT:

1.    QSqlQuery query;
2.    query.exec("INSERT INTO employee (id, name, salary) "
3.         "VALUES (1001, 'Thad Beaumont', 65000)");

* This source code was highlighted with Source Code Highlighter.

If you need to insert multiple records at the same time, it is often more efficient to separate the query from the actual values ​​inserted. This can be done by inserting values ​​through parameters. Qt supports two value insertion syntaxes: named parameters and positional parameters. The following example shows insertion using a named parameter:

1.    QSqlQuery query;
2.    query.prepare("INSERT INTO employee (id, name, salary) "
3.           "VALUES (:id, :name, :salary)");
4.    query.bindValue(":id", 1001);
5.    query.bindValue(":name", "Thad Beaumont");
6.    query.bindValue(":salary", 65000);
7.    query.exec();

* This source code was highlighted with Source Code Highlighter.

This example shows insertion using a positional parameter:

1.    QSqlQuery query;
2.    query.prepare("INSERT INTO employee (id, name, salary) "
3.           "VALUES (?, ?, ?)");
4.    query.addBindValue(1001);
5.    query.addBindValue("Thad Beaumont");
6.    query.addBindValue(65000);
7.    query.exec();

* This source code was highlighted with Source Code Highlighter.

When inserting multiple records, you only need to call QSqlQuery :: prepare () once. You can then call bindValue () or addBindValue (), followed by calling exec () as many times as needed.

Displaying data in a table view

The QSqlQueryModel, QSqlTableModel, and QSqlRelationalTableModel classes can be used as data sources for Qt view classes such as QListView, QTableView, and QTreeView. In practice, QTableView is most often used due to the fact that the resulting SQL selection is essentially a two-dimensional data structure.
The following example creates a view based on an SQL data model:

1.    QSqlTableModel model;
2.    model.setTable("employee");
3.    QTableView *view = new QTableView;
4.    view->setModel(&model);
5.    view->show();

* This source code was highlighted with Source Code Highlighter.

If the model is a read-write model (for example, QSqlTableModel), then the view allows you to edit the fields. This can be disabled using the following code

1.    view->setEditTriggers(QAbstractItemView::NoEditTriggers);

* This source code was highlighted with Source Code Highlighter.

You can use the same model as a data source for multiple views. If the user changes the model data using one of the views, the other views will immediately display the changes.
Presentation classes to indicate columns at the top display headings. To change the title text, use the setHeaderData () function of the model. For instance:

1.    model->setHeaderData(0, Qt::Horizontal, QObject::tr("ID"));
2.    model->setHeaderData(1, Qt::Horizontal, QObject::tr("Name"));
3.    model->setHeaderData(2, Qt::Horizontal, QObject::tr("City"));
4.    model->setHeaderData(3, Qt::Horizontal, QObject::tr("Country"));

* This source code was highlighted with Source Code Highlighter.

Conclusion

This article outlines the basic principles of working with databases in Qt. However, in addition to the possibilities indicated here, there are still many interesting things, for example, transactions, working with foreign keys or creating data-oriented forms. Unfortunately, these topics are extensive enough for one article.

UPD: fixed errors in the code.