QT QSQL操作

创建并打开数据库

bool Open(const QString &dbFileName, const QString &connectionName /= “”/)
{
QSqlDatabase::removeDatabase(dbFileName);
m_db = QSqlDatabase::addDatabase(“QSQLITE”, connectionName);
m_db.setDatabaseName(dbFileName);
if (!m_db.open())
{
novadebug(“open db fail,{}”, dbFileName.toStdString());
return false;
}
return true;
}

删除表

QString strCreateServer = QString(“drop table %1”).arg(tablename);

创建表

QString strCreateServer = QString(“create table %1(id integer PRIMARY KEY AUTOINCREMENT”).arg(tablename);
for (int i = 0; i < fieldNameList.size(); i++)
{
strCreateServer += QString(",%1 varchar(%2)").arg(fieldNameList.at(i)).arg(sizeList.at(i));
}
strCreateServer += “)”;
queryServer.prepare(strCreateServer)
!queryServer.exec())

插入一行

bool SqlOperation::Insert(const QString &tableName, const QStringList &fieldNameList, const QVector &valVec, const QString &connectionName /= “”/)
{
if(fieldNameList.size() == 0 || valVec.size() == 0)
{
return false;
}
if (fieldNameList.size() != valVec.size())
{
return false;
}
QString fieldstr = fieldNameList[0];
QString valstr = “?”;
for (int i = 1; i < fieldNameList.size(); i++)
{
fieldstr += “,” + fieldNameList[i];
valstr += QString(",") + “?”;
}
QString str = QString(“insert into %1(%2) values(%3)”).arg(tableName).arg(fieldstr).arg(valstr);
QSqlQuery query(m_db);
query.prepare(str);
for (int i = 0; i < valVec.size(); i++)
{
query.bindValue(i, valVec[i]);
}
bool succ = query.exec();
if (!succ)
{
return false;
}
return true;
}

查询

str = QString(“select %1 from %2”).arg(fstr).arg(tableName);
str = QString(“select %1 from %2 where %3”).arg(fstr).arg(tableName).arg(conditionStr);

插入新字段

QString strNewC = QString(“ALTER TABLE %1 ADD %2 varchar(1000)”).arg(strTableName).arg(strFieldName);

更新一个字段

strSql = QString(“update %1 set %2=:%3 where sn=’%4’”).arg(strTableNmae).arg(strFieldNmae).arg(strFieldNmae).arg(strSN);

更新一行

bool SqlOperation::updateDataList(const std::string &tableName, const std::liststd::string &fieldNameList, const std::vectorstd::string &valueVec, const std::string &sn /= “”/)
{
if (fieldNameList.size() == 0 || valueVec.size() == 0)
{
return false;
}
if (fieldNameList.size() != valueVec.size())
{
return false;
}
QString fieldstr;
for (auto &str : fieldNameList)
{
fieldstr.append(QString::fromStdString(str));
fieldstr.append("=?,");
}
fieldstr = fieldstr.left(fieldstr.size() - 1);
QString str = QString(“update %1 set %2 where sn=’%3’”).arg(QString::fromStdString(tableName)).arg(fieldstr).arg(QString::fromStdString(valueVec.at(0)));
QSqlQuery query(m_db);
query.prepare(str);
for (int i = 0; i < valueVec.size(); i++)
{
query.bindValue(i, QString::fromStdString(valueVec[i]));
}
bool succ = query.exec();
if (!succ)
{
qDebug( query.lastError().text().toLocal8Bit().data());//“Update fail,err:{}”,
return false;
}
return true;
}

查询表是否存在

QString sql = QString(“select * from sqlite_master where name=’%1’”).arg(tableName);

查询字段是否存在

QString strSql = QString(“select * from sqlite_master where name=’%1’ and sql like ‘%%2%’”).arg(tableName).arg(field);

获取字段列表

bool SqlOperation::getFieldList(const QString &tableName,QStringList &fieldNameList)
{
QSqlQuery query(m_db);
QString strSQL = QString(“PRAGMA table_info(%1)”).arg(tableName);
query.prepare(strSQL);
bool succ = query.exec(strSQL);
if (succ)
{
while (query.next())
{
QString str = query.value(1).toString();
fieldNameList.append(str);
}
return true;
}
return true;
}