Qt数据库操作

时间:2021-09-09 02:33:43

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();