A super complete guide to integrating and using SQLite databases in Qt

Preface

SQLite is a lightweight, embedded relational database that does not require independent server processes and stores data in files, making it ideal for local data management for desktop and mobile applications. Qt byQt SQL moduleIt provides native support for SQLite, and developers can easily implement database addition, deletion, modification and query, transaction processing and other operations. This article will explain in detail how to integrate and use SQLite databases in Qt.

1. Environment configuration and preparation work

1.1 Enable the Qt SQL module

In Qt project files (.proAdd SQL module dependencies to ):

QT += sql

1.2 Include header files

Introduce the necessary classes in the code:

#include <QSqlDatabase>
#include <QSqlQuery>
#include <QSqlError>
#include <QSqlTableModel>

2. Connect to SQLite database

2.1 Create and open the database

QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
("my_database.db"); // Database file name (or full path)
if (!()) {
    qDebug() &lt;&lt; "Error: Failed to open database:" &lt;&lt; ().text();
    return;
}

illustrate：
- QSQLITEis the SQLite driver name built in Qt.
- If the file does not exist, SQLite will automatically create a new database.

2.2 Close the database

(); // Explicitly close the connection (usually not required, it will be automatically closed when the program exits)

3. Perform SQL operations

3.1 Create a table

QSqlQuery query;
("CREATE TABLE IF NOT EXISTS users ("
          "id INTEGER PRIMARY KEY AUTOINCREMENT,"
          "name TEXT NOT NULL,"
          "age INTEGER,"
          "email TEXT UNIQUE)");

3.2 Insert data

Execute SQL directly

("INSERT INTO users (name, age, email) VALUES ('Alice', 30, 'alice@')");

Use preprocessing statements (prevent SQL injection)

("INSERT INTO users (name, age, email) VALUES (?, ?, ?)");
("Bob");
(25);
("bob@");
();

3.3 Query data

if (("SELECT id, name, age FROM users WHERE age > 20")) {
    while (()) {
        int id = (0).toInt();
        QString name = ("name").toString();
        int age = (2).toInt();
        qDebug() << "User:" << id << name << age;
    }
} else {
    qDebug() << "Query error:" << ().text();
}

3.4 Update and delete data

// renew("UPDATE users SET age = 31 WHERE name = 'Alice'");

// delete("DELETE FROM users WHERE email IS NULL");

4. Transaction processing

Ensure the atomicity of multiple operations through transactions:

(); // Start a transaction
QSqlQuery query;
("UPDATE account SET balance = balance - 100 WHERE id = 1");
("UPDATE account SET balance = balance + 100 WHERE id = 2");

if (/* Check whether the operation is successful */) {
    ();   // Submit transaction} else {
    (); // Roll back the transaction}

5. Programming with Model-View

Qt providesQSqlTableModelandQSqlQueryModel, conveniently combine databases with UI components (such asQTableView) Binding.

5.1 Display table data

QSqlTableModel *model = new QSqlTableModel(this);
model->setTable("users");
model->setFilter("age > 20");
model->select();

QTableView *view = new QTableView;
view->setModel(model);
view->show();

5.2 Edit and save modification

model-&gt;setEditStrategy(QSqlTableModel::OnManualSubmit);
// The user calls after modifying the data through the view:model-&gt;submitAll(); // Submit all changes to the database

6. Error handling and debugging

6.1 Catching database errors

if (!("INVALID SQL")) {
    qDebug() << "SQL Error:" << ().text();
    qDebug() << "Executed SQL:" << ();
}

6.2 View supported database drivers

qDebug() &lt;&lt; "Available drivers:" &lt;&lt; QSqlDatabase::drivers();
// Output example：("QSQLITE", "QMYSQL", "QPSQL")

7. Advanced tips and precautions

7.1 Batch insertion optimization

Use transactions to speed up large batch inserts:

();
QSqlQuery query;
("INSERT INTO users (name) VALUES (?)");
for (const QString &name : namesList) {
    (name);
    ();
}
();

7.2 Multi-threaded access

SQLite does not support multiple threads simultaneous writing by default, and needs to be passedQSqlDatabase::cloneDatabaseCreate independent connections for each thread.
When using the database in the child thread, make sure to open the connection within the child thread.

7.3 Database Migration

useuser_versionField management database version:

("PRAGMA user_version = 1"); // Set version number("PRAGMA user_version");     // Read version number

8. FAQ

Q1: What should I do if the database file is locked?

Ensure allQSqlQueryandQSqlDatabaseThe object is released in time after use.
Avoid multithreading writing to the same connection at the same time.

Q2: How to prevent SQL injection?

Always useprepare()andaddBindValue()Alternative string stitching.

Q3: How to optimize if query performance is slow?

Add an index to commonly used query fields.
Reduce frequent small transactions and merge them into batch operations.

9. Complete sample code

#include <QCoreApplication>
#include <QSqlDatabase>
#include <QSqlQuery>
#include <QDebug>

int main(int argc, char *argv[]) {
    QCoreApplication a(argc, argv);

    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    ("");
    
    if (!()) {
        qDebug() << "Database error:" << ().text();
        return -1;
    }

    QSqlQuery query;
    ("CREATE TABLE IF NOT EXISTS books ("
               "id INTEGER PRIMARY KEY,"
               "title TEXT,"
               "author TEXT)");

    ("INSERT INTO books (title, author) VALUES (?, ?)");
    ("Qt Programming");
    ("John Doe");
    ();

    ("SELECT * FROM books");
    while (()) {
        qDebug() << "Book:" << ("title").toString()
                 << "by" << ("author").toString();
    }

    ();
    return ();
}

10. Summary

Qt's SQLite support makes local data management simple and efficient. Core points include:

useQSqlDatabaseManage database connections.
passQSqlQueryExecute SQL statements and process the results.
Use transactions to ensure data consistency.
Quickly build UI interfaces in conjunction with the model-view framework.

Official documentation reference：

Qt SQL Module
SQLite official documentation

This is the article about integrating and using SQLite databases in Qt. For more related content related to Qt integration using SQLite databases, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!