如何将QSqlDatabase转换为QDataStream实现SQLite数据库的导出导入？

Got it, let's clear up a key point first: you can't serialize a QSqlDatabase object directly with QDataStream—that's just a database connection handle, not the actual data or database structure you need to persist. What you really need to do is export your database's schema (table definitions) and all table data, then write that to your stream. Later, you can read the stream back to rebuild the database.

Here's a step-by-step implementation tailored to your use case, integrating with Qt's file dialogs:

1. Export Logic (Save/Export Dialog) #

First, we'll extract table schemas and data, then write them to a QDataStream:

bool exportDatabase(QSqlDatabase &db, const QString &filePath) {
    QFile file(filePath);
    if (!file.open(QIODevice::WriteOnly)) {
        qDebug() << "Failed to open file for writing:" << file.errorString();
        return false;
    }

    QDataStream out(&file);
    out.setVersion(QDataStream::Qt_6_5); // Use your project's Qt version

    // Start a transaction to ensure a consistent export
    if (!db.transaction()) {
        qDebug() << "Failed to start export transaction:" << db.lastError().text();
        file.close();
        return false;
    }

    try {
        // Get all user tables (filter out SQLite system tables)
        QSqlQuery tablesQuery("SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%'", db);
        QStringList tableNames;
        while (tablesQuery.next()) {
            tableNames.append(tablesQuery.value(0).toString());
        }

        // Write total table count first
        out << tableNames.size();

        for (const QString &tableName : tableNames) {
            // Step 1: Write table name
            out << tableName;

            // Step 2: Write table creation statement
            QSqlQuery schemaQuery(QString("SELECT sql FROM sqlite_master WHERE type='table' AND name='%1'").arg(tableName), db);
            schemaQuery.next();
            QString createStmt = schemaQuery.value(0).toString();
            out << createStmt;

            // Step 3: Write table data
            QSqlQuery dataQuery(QString("SELECT * FROM %1").arg(tableName), db);
            int columnCount = dataQuery.record().count();
            // Write row count and column count
            out << dataQuery.size() << columnCount;

            while (dataQuery.next()) {
                // Write each column's value
                for (int i = 0; i < columnCount; ++i) {
                    QVariant value = dataQuery.value(i);
                    out << value;
                }
            }
        }

        db.commit();
        file.close();
        return true;
    } catch (...) {
        db.rollback();
        file.close();
        qDebug() << "Export failed during operation";
        return false;
    }
}

// Integrate with Save As/Export Dialog
void showExportDialog(QSqlDatabase &db) {
    QString filePath = QFileDialog::getSaveFileName(nullptr, "Export Database", "", "Database Dump Files (*.db_dump)");
    if (!filePath.isEmpty()) {
        if (exportDatabase(db, filePath)) {
            QMessageBox::information(nullptr, "Success", "Database exported successfully!");
        } else {
            QMessageBox::critical(nullptr, "Error", "Failed to export database.");
        }
    }
}

2. Import Logic (Import Dialog) #

Now, to read the dump file and rebuild the database (including clearing existing content as needed):

bool importDatabase(QSqlDatabase &db, const QString &filePath) {
    QFile file(filePath);
    if (!file.open(QIODevice::ReadOnly)) {
        qDebug() << "Failed to open file for reading:" << file.errorString();
        return false;
    }

    QDataStream in(&file);
    in.setVersion(QDataStream::Qt_6_5); // Match the version used for export

    // Start transaction to avoid partial imports
    if (!db.transaction()) {
        qDebug() << "Failed to start import transaction:" << db.lastError().text();
        file.close();
        return false;
    }

    try {
        // Optional: Clear existing tables (matches your "remove database content" requirement)
        QSqlQuery clearQuery("SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%'", db);
        while (clearQuery.next()) {
            QString tableName = clearQuery.value(0).toString();
            db.exec(QString("DROP TABLE IF EXISTS %1").arg(tableName));
        }

        // Read total table count
        int tableCount;
        in >> tableCount;

        for (int i = 0; i < tableCount; ++i) {
            // Step 1: Read table name
            QString tableName;
            in >> tableName;

            // Step 2: Read and execute table creation statement
            QString createStmt;
            in >> createStmt;
            if (!db.exec(createStmt).isActive()) {
                qDebug() << "Failed to create table" << tableName << ":" << db.lastError().text();
                throw std::runtime_error("Table creation failed");
            }

            // Step 3: Read and insert data
            int rowCount, columnCount;
            in >> rowCount >> columnCount;

            QSqlQuery insertQuery(db);
            QString placeholders = QString(", ?").repeated(columnCount).mid(2); // Generate ?,?,? for values
            QString insertStmt = QString("INSERT INTO %1 VALUES (%2)").arg(tableName).arg(placeholders);
            insertQuery.prepare(insertStmt);

            for (int row = 0; row < rowCount; ++row) {
                insertQuery.clear();
                for (int col = 0; col < columnCount; ++col) {
                    QVariant value;
                    in >> value;
                    insertQuery.addBindValue(value);
                }
                if (!insertQuery.exec()) {
                    qDebug() << "Failed to insert row into" << tableName << ":" << insertQuery.lastError().text();
                    throw std::runtime_error("Data insertion failed");
                }
            }
        }

        db.commit();
        file.close();
        return true;
    } catch (...) {
        db.rollback();
        file.close();
        qDebug() << "Import failed during operation";
        return false;
    }
}

// Integrate with Import Dialog
void showImportDialog(QSqlDatabase &db) {
    QString filePath = QFileDialog::getOpenFileName(nullptr, "Import Database", "", "Database Dump Files (*.db_dump)");
    if (!filePath.isEmpty()) {
        if (importDatabase(db, filePath)) {
            QMessageBox::information(nullptr, "Success", "Database imported successfully!");
        } else {
            QMessageBox::critical(nullptr, "Error", "Failed to import database.");
        }
    }
}

Key Notes #

• Qt Version Consistency: Always use the same QDataStream version for export and import—mismatched versions will break serialization.
• Database Compatibility: This example targets SQLite; if you're using MySQL/PostgreSQL, adjust the table schema query and system table filters accordingly.
• Error Handling: Transactions ensure that if any step fails, the database rolls back to its original state, avoiding partial exports/imports.
• Data Types: QDataStream handles most Qt-compatible SQL types (strings, numbers, dates, blobs) automatically, but test edge cases specific to your data.

内容的提问来源于stack exchange，提问作者MarkoShiva