Qt数据库操作

1、创建数据库，数据表

bool soloBackupDatabaseManager::creatTaskTable()
{
    QString guid = QUuid::createUuid().toString();
    bool ok;

    {
        QSqlDatabase db = QSqlDatabase::addDatabase(dataBaseType, guid);
        db.setDatabaseName("BackupTool.db");
        ok = db.open();
        if (ok)
        {
            QSqlQuery query(db);
            QStringList tables = db.tables();
            if (!tables.contains("Task"))
            {
                QString command;
                command = "create table Task ";
                command += " (TaskName varchar(33), TaskType qint32, DestinationPath varchar(255), LastTime varchar(20), Result qint32,";
                command += "ProjectName varchar(33), ProjectRoot varchar(255))";
                ok = query.exec(command);
            }
            db.close();
        }
    }

    QSqlDatabase::removeDatabase(guid);
    return ok;
}

2、数据库插入

bool soloBackupDatabaseManager::insertTaskInfo(TaskInfo &info)
{
    QString guid = QUuid::createUuid().toString();
    bool ok;

    {
        QSqlDatabase db = QSqlDatabase::addDatabase(dataBaseType, guid);
        db.setDatabaseName("BackupTool.db");
        ok = db.open();
        if (ok)
        {
            QSqlQuery query(db);
            QStringList tables = db.tables();
            if (tables.contains("Task"))
            {
                query.prepare("insert into Task (TaskName, TaskType, DestinationPath, LastTime, Result, ProjectName,ProjectRoot) values(:TaskName, :TaskType, :DestinationPath, :LastTime, :Result, :ProjectName, :ProjectRoot)");
                query.bindValue(":TaskName", info.taskName);
                query.bindValue(":TaskType", info.taskType);
                query.bindValue(":DestinationPath", info.destinationPath);
                query.bindValue(":LastTime", info.lastTime.toString("yyyy-MM-dd hh:mm:ss"));
                query.bindValue(":Result", info.result);
                query.bindValue(":ProjectName", info.projectParameter->getProjectName());
                query.bindValue(":ProjectRoot", info.projectParameter->getProjectRootDir());

                ok = query.exec();
                if (!ok)
                {
                    return false;
                }
            }
            db.close();
        }
    }

    QSqlDatabase::removeDatabase(guid);
    return ok;
}

3、数据库查找

bool soloBackupDatabaseManager::getTaskInfo(QList<TaskInfo> &Infos)
{
    QString guid = QUuid::createUuid().toString();
    bool ok;

    {
        QSqlDatabase db = QSqlDatabase::addDatabase(dataBaseType, guid);
        db.setDatabaseName("BackupTool.db");
        ok = db.open();
        if (ok)
        {
            QSqlQuery query(db);
            QStringList tables = db.tables();
            if (tables.contains("Task"))
            {
                bool success = query.prepare("SELECT * from Task");
                success = success && query.exec();
                if (success)
                {
                    while (query.next())
                    {
                        TaskInfo temp;
                        temp.taskName = query.value("TaskName").toString();
                        temp.taskType = query.value("TaskType").toInt();
                        temp.destinationPath = query.value("DestinationPath").toString();
                        temp.lastTime = QDateTime::fromString(query.value("LastTime").toString());
                        temp.result = query.value("Result").toInt();
                        temp.projectParameter = new SoloBackupProjectParameter();
                        temp.projectParameter->setProjectName(query.value("ProjectName").toString());
                        temp.projectParameter->setProjectRootDir(query.value("ProjectRoot").toString());
                        temp.projectParameter->readParameterFromFile(temp.projectParameter->getProjectPath());
                        Infos << temp;
                    }
                }
            }
            db.close();
        }
    }

    QSqlDatabase::removeDatabase(guid);
    return ok;
}

4、插入的常用的格式

"create table RECEIVER_POINT (ID qint64, LINE qreal, POINT qreal, PINDEX qint16, PCODE varchar(2),"
                             "X qreal, Y qreal, Z qreal, LAT_84 qreal, LONG_84 qreal, ELEV_84 qreal,"
 "STATIC qreal, PDEPTH qreal, WDEPTH qreal, SEISDATUM qreal,"
                                "DATETIME varchar(30), primary key(ID))"

5、事务

mpDatabase->transaction();
mpDatabase->commit();
mpDatabase->rollback();