Qt 数据库操作 #

┌─────────────────────────────────────────────────────────────┐
│                    Qt SQL 模块                              │
├─────────────────────────────────────────────────────────────┤
│                                                             │
│  数据库连接：                                                │
│  └── QSqlDatabase    - 数据库连接管理                       │
│                                                             │
│  数据库操作：                                                │
│  ├── QSqlQuery       - 执行 SQL 语句                        │
│  ├── QSqlTableModel  - 表格模型（可编辑）                   │
│  └── QSqlRelationalTableModel - 关系表格模型                │
│                                                             │
│  其他：                                                     │
│  ├── QSqlError       - 错误信息                             │
│  ├── QSqlRecord      - 记录结构                             │
│  └── QSqlField       - 字段信息                             │
│                                                             │
│  支持的数据库：                                              │
│  SQLite, MySQL, PostgreSQL, ODBC, Oracle 等                 │
│                                                             │
└─────────────────────────────────────────────────────────────┘

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

bool connectSQLite(const QString &dbPath)
{
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName(dbPath);
    
    if (!db.open()) {
        qDebug() << "Failed to open database:" << db.lastError().text();
        return false;
    }
    
    qDebug() << "Database connected successfully";
    return true;
}

bool connectMySQL(const QString &host, const QString &dbName,
                  const QString &user, const QString &password)
{
    QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
    db.setHostName(host);
    db.setDatabaseName(dbName);
    db.setUserName(user);
    db.setPassword(password);
    db.setPort(3306);
    
    if (!db.open()) {
        qDebug() << "Failed to connect:" << db.lastError().text();
        return false;
    }
    
    return true;
}

// 添加命名连接
QSqlDatabase db1 = QSqlDatabase::addDatabase("QSQLITE", "connection1");
db1.setDatabaseName("db1.sqlite");
db1.open();

QSqlDatabase db2 = QSqlDatabase::addDatabase("QSQLITE", "connection2");
db2.setDatabaseName("db2.sqlite");
db2.open();

// 获取连接
QSqlDatabase conn1 = QSqlDatabase::database("connection1");
QSqlDatabase conn2 = QSqlDatabase::database("connection2");

// 移除连接
QSqlDatabase::removeDatabase("connection1");

bool createTable()
{
    QSqlQuery query;
    QString sql = R"(
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            email TEXT UNIQUE,
            age INTEGER,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )
    )";
    
    if (!query.exec(sql)) {
        qDebug() << "Create table failed:" << query.lastError().text();
        return false;
    }
    
    return true;
}

// 方式一：直接执行
bool insertUser(const QString &name, const QString &email, int age)
{
    QSqlQuery query;
    query.prepare("INSERT INTO users (name, email, age) VALUES (?, ?, ?)");
    query.addBindValue(name);
    query.addBindValue(email);
    query.addBindValue(age);
    
    if (!query.exec()) {
        qDebug() << "Insert failed:" << query.lastError().text();
        return false;
    }
    
    return true;
}

// 方式二：命名占位符
bool insertUserNamed(const QString &name, const QString &email, int age)
{
    QSqlQuery query;
    query.prepare("INSERT INTO users (name, email, age) VALUES (:name, :email, :age)");
    query.bindValue(":name", name);
    query.bindValue(":email", email);
    query.bindValue(":age", age);
    
    return query.exec();
}

// 获取插入的 ID
qint64 getLastInsertId()
{
    QSqlQuery query;
    query.exec("SELECT last_insert_rowid()");
    if (query.next()) {
        return query.value(0).toLongLong();
    }
    return -1;
}

// 查询所有
void queryAllUsers()
{
    QSqlQuery query("SELECT id, name, email, age FROM users");
    
    while (query.next()) {
        int id = query.value(0).toInt();
        QString name = query.value(1).toString();
        QString email = query.value(2).toString();
        int age = query.value(3).toInt();
        
        qDebug() << id << name << email << age;
    }
}

// 条件查询
void queryUsersByAge(int minAge)
{
    QSqlQuery query;
    query.prepare("SELECT * FROM users WHERE age >= ?");
    query.addBindValue(minAge);
    
    if (query.exec()) {
        while (query.next()) {
            qDebug() << query.value("name").toString()
                     << query.value("age").toInt();
        }
    }
}

// 按字段名获取值
void queryWithFieldNames()
{
    QSqlQuery query("SELECT * FROM users");
    QSqlRecord record = query.record();
    
    while (query.next()) {
        for (int i = 0; i < record.count(); ++i) {
            QString fieldName = record.fieldName(i);
            QVariant value = query.value(i);
            qDebug() << fieldName << ":" << value;
        }
    }
}

bool updateUserAge(int id, int newAge)
{
    QSqlQuery query;
    query.prepare("UPDATE users SET age = ? WHERE id = ?");
    query.addBindValue(newAge);
    query.addBindValue(id);
    
    if (!query.exec()) {
        qDebug() << "Update failed:" << query.lastError().text();
        return false;
    }
    
    int rowsAffected = query.numRowsAffected();
    return rowsAffected > 0;
}

bool deleteUser(int id)
{
    QSqlQuery query;
    query.prepare("DELETE FROM users WHERE id = ?");
    query.addBindValue(id);
    
    return query.exec();
}

bool transferData()
{
    QSqlDatabase db = QSqlDatabase::database();
    
    if (!db.transaction()) {
        return false;
    }
    
    QSqlQuery query;
    
    try {
        query.exec("INSERT INTO table1 VALUES (...)");
        query.exec("UPDATE table2 SET ...");
        query.exec("DELETE FROM table3 WHERE ...");
        
        db.commit();
        return true;
    } catch (...) {
        db.rollback();
        return false;
    }
}

#include <QSqlTableModel>
#include <QTableView>

QSqlTableModel *model = new QSqlTableModel(this);
model->setTable("users");
model->setEditStrategy(QSqlTableModel::OnManualSubmit);
model->select();

// 设置表头
model->setHeaderData(0, Qt::Horizontal, "ID");
model->setHeaderData(1, Qt::Horizontal, "Name");
model->setHeaderData(2, Qt::Horizontal, "Email");

// 显示在表格视图
QTableView *view = new QTableView;
view->setModel(model);

// 添加行
model->insertRow(0);
model->setData(model->index(0, 1), "New User");
model->setData(model->index(0, 2), "new@email.com");
model->submitAll();  // 提交更改

// 删除行
model->removeRow(0);
model->submitAll();

// 过滤
model->setFilter("age > 20");
model->select();

// 排序
model->setSort(1, Qt::AscendingOrder);
model->select();