Working with databases in Qt

Qt makes it possible to create platform-independent applications for working with databases using standard DBMS. 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 an extension for working with databases of any built-in or separately written widgets.

Introduction

Working with databases in Qt occurs 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. SQL API layer - 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 links data from the database to data-oriented widgets. This includes classes such as QSqlQueryModel, QSqlTableModel, and QSqlRelationalTableModel.

Database connection

To access a database using QSqlQuery and QSqlQueryModel, you must create and open one or more connections to the database.
Qt can work with the following databases (due to incompatibility with the GPL license, not all plug-ins 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 compliant 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 a plug-in driver that is not included in the Qt distribution, you need to have the appropriate client library for the used DBMS.

You can connect to the database like this:

QSqlDatabase db = QsqlDatabase::addDatabase( "QMYSQL" , "mydb" ); db.setHostName( "bigblue" ); db.setDatabaseName( "flightdb" ); db.setUserName( "acarlson" ); db.setPassword( "1uTbSbAs" ); bool ok = db.open(); * This source code was highlighted with Source Code Highlighter .

1. QSqlDatabase db = QsqlDatabase::addDatabase( "QMYSQL" , "mydb" ); db.setHostName( "bigblue" ); db.setDatabaseName( "flightdb" ); db.setUserName( "acarlson" ); db.setPassword( "1uTbSbAs" ); bool ok = db.open(); * This source code was highlighted with Source Code Highlighter .
2. QSqlDatabase db = QsqlDatabase::addDatabase( "QMYSQL" , "mydb" ); db.setHostName( "bigblue" ); db.setDatabaseName( "flightdb" ); db.setUserName( "acarlson" ); db.setPassword( "1uTbSbAs" ); bool ok = db.open(); * This source code was highlighted with Source Code Highlighter .
3. QSqlDatabase db = QsqlDatabase::addDatabase( "QMYSQL" , "mydb" ); db.setHostName( "bigblue" ); db.setDatabaseName( "flightdb" ); db.setUserName( "acarlson" ); db.setPassword( "1uTbSbAs" ); bool ok = db.open(); * This source code was highlighted with Source Code Highlighter .
4. QSqlDatabase db = QsqlDatabase::addDatabase( "QMYSQL" , "mydb" ); db.setHostName( "bigblue" ); db.setDatabaseName( "flightdb" ); db.setUserName( "acarlson" ); db.setPassword( "1uTbSbAs" ); bool ok = db.open(); * This source code was highlighted with Source Code Highlighter .
5. QSqlDatabase db = QsqlDatabase::addDatabase( "QMYSQL" , "mydb" ); db.setHostName( "bigblue" ); db.setDatabaseName( "flightdb" ); db.setUserName( "acarlson" ); db.setPassword( "1uTbSbAs" ); bool ok = db.open(); * This source code was highlighted with Source Code Highlighter .
6. QSqlDatabase db = QsqlDatabase::addDatabase( "QMYSQL" , "mydb" ); db.setHostName( "bigblue" ); db.setDatabaseName( "flightdb" ); db.setUserName( "acarlson" ); db.setPassword( "1uTbSbAs" ); bool ok = db.open(); * This source code was highlighted with Source Code Highlighter .

QSqlDatabase db = QsqlDatabase::addDatabase( "QMYSQL" , "mydb" ); db.setHostName( "bigblue" ); db.setDatabaseName( "flightdb" ); db.setUserName( "acarlson" ); db.setPassword( "1uTbSbAs" ); bool ok = db.open(); * This source code was highlighted with Source Code Highlighter .

The first line creates a connection object, and the last one opens it. In the interim, some connection information is initialized, including the connection name, database name, node name, user name, password. In this example, a connection is made to the flightdb MySQL database on the bigblue node. The “QMYSQL” argument in addDatabase () indicates the type of database driver to use for the connection, and “mydb” 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 name of the connection, it returns the default connection.
If open () fails, it will return false. In this case, you can get error information by calling QSqlDatabase :: lastError ().
To remove a database connection, you must first close the database using QSqlDatabase :: close (), and then remove the connection using the static method QSqlDatabase :: removeDatabase ().
')

Execute 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 argument QSqlDatabase, which specifies which database connection is used. If not specified, the default connection is used.
If an error occurs, exec () returns false. Access to the error can be obtained using QSqlQuery :: lastError ().
QSqlQuery provides one-time access to the resulting sample of a single query. After calling exec (), 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 looping 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 execute not only SELECT, but also any other queries. The following example inserts an entry 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 actually inserted values. This can be done by inserting values ​​through parameters. Qt supports two syntaxes for inserting values: name parameters and positional parameters. The following example shows an insert 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 with 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 need to call QSqlQuery :: prepare () only once. You can then call bindValue () or addBindValue () and then call exec () as many times as necessary.

Data display in the 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 sample is essentially a two-dimensional data structure.
The following example creates a view based on the 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 with 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.
Representation classes for designating columns at the top display headings. To change the title text, use the model's setHeaderData () function. For example:

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, besides the possibilities indicated here, there are many other interesting things, for example, transactions, working with foreign keys, or creating data-oriented forms. Unfortunately, these topics are quite extensive for one article.

UPD: corrected errors in the code.