Qt 自带数据库 QSQLite 的使用_综合

下面是用qt的QSQLITE做的数据库,我的环境是redhat
项目要求是提供2个接口：

1.void set(QString key, QString value); //将key-value存入数据库
2.QString get(QString key);// 从数据库获取数据

自己发现的注意点：
1.QSQLite是QT自带的，是跨平台的，主要用QSqlDatabase和QSqlQuery来操作。
2.QSQLIte是支持同步的，同步是支持并发读，但若A线程写，B线程同时也写，我发现B线程的写操作数据库就不处理了。

QSqlQuery和QSqlDatabase的操作，总是感觉不能完全把握，还需进一步观察。

pro 工程文件代码

#-------------------------------------------------
#
# Project created by QtCreator 2019-12-07T17:35:30
#
#-------------------------------------------------QT       += core gui sqlgreaterThan(QT_MAJOR_VERSION, 4): QT += widgetsTARGET = ECOTS_DataStorage
TEMPLATE = app# The following define makes your compiler emit warnings if you use
# any feature of Qt which has been marked as deprecated (the exact warnings
# depend on your compiler). Please consult the documentation of the
# deprecated API in order to know how to port your code away from it.
DEFINES += QT_DEPRECATED_WARNINGS
#DEFINES += QT_NO_DEBUG_OUTPUT
# You can also make your code fail to compile if you use deprecated APIs.
# In order to do so, uncomment the following line.
# You can also select to disable deprecated APIs only up to a certain version of Qt.
#DEFINES += QT_DISABLE_DEPRECATED_BEFORE=0x060000    # disables all the APIs deprecated before Qt 6.0.0SOURCES += \main.cpp \datastorage.cppHEADERS += \datastorage.h

*.h

/** datastorage.h */
#ifndef DATASTORAGE_H
#define DATASTORAGE_H#include <QtSql>
#include <QtSql/QSqlDatabase>
#include <QtSql/QSqlQuery>
#include <QtSql/QSqlRecord>
#include <QtSql/QSqlError>#include <QString>
#include <QStringList>
#include <QDebug>
#include <QMap>#include <stdio.h>
#include <string.h>
#include <sys/stat.h>
#include <unistd.h>
#include <fcntl.h>
#include <algorithm>#define DEFAULT_DATABASE_PATH "/tmp/.ECOTS.db"#define DEFAULT_TABLE_NAME "ECOTS"namespace ECOTS
{class DataStorage
{
public://get singleton objstatic DataStorage &getDataStorage();//insert or updatevoid setVariable(const QString &_key, const QString &_value);//viewQString getVariableString(const QString &_key);//output tables to mapint printTable(QMap<QString,QString> &tableInfo_);//close databasebool close();bool deleteTable(const QString &_tableName = "ECOTS");protected://check database path is accessible, if not exist path -> mkdir 777void checkStorageDbPath(const QString &_dbFile);//check table exits in database or notbool isTableExist(const QString &_tableName);//create table in databsebool createTable(const QString &_tableName);// exec sqlbool exeSqlCmd(QSqlQuery &_query,  const QString &_cmd);private:DataStorage();~DataStorage();QSqlDatabase sqldb;QReadWriteLock rw;
};} //namespace#endif // DATASTORAGE_H

*.cpp

/** datastorage.cpp */
#include "datastorage.h"/** @brief:** @parameter in:* @parameter out:** @return:*/ECOTS::DataStorage &ECOTS::DataStorage::getDataStorage()
{static DataStorage obj;return obj;
}ECOTS::DataStorage::DataStorage()
{if(QSqlDatabase::contains("qt_sql_default_connection")){qDebug() << "connection  is exist ";sqldb = QSqlDatabase::database("qt_sql_default_connection");}else{qDebug() << "connection is new ";/* add SQLITE dirver */sqldb = QSqlDatabase::addDatabase("QSQLITE");}checkStorageDbPath(QString(DEFAULT_DATABASE_PATH));sqldb.setDatabaseName(QString(DEFAULT_DATABASE_PATH));if (!sqldb.isOpen()){if (!sqldb.open()){qDebug() << "open database error: " << sqldb.lastError().text();}}qDebug() << "get database name:  " << sqldb.databaseName();qDebug() << "get database connection name: " << sqldb.connectionNames();if (!isTableExist("ECOTS")){createTable(QString(DEFAULT_TABLE_NAME));}qDebug() << "this database has tables: " << sqldb.tables();}ECOTS::DataStorage::~DataStorage()
{sqldb.close();
}void ECOTS::DataStorage::checkStorageDbPath(const QString &_dbFile)
{std::string stFile = _dbFile.toStdString();std::string dbPath = stFile.substr(0, stFile.find_last_of('/') + 1);qDebug() << "database path is: " << QString::fromStdString(dbPath);if (!access(dbPath.c_str(), F_OK)){//check path exist or notmkdir(dbPath.c_str(), 0777);}chmod(DEFAULT_DATABASE_PATH, S_IRUSR|S_IWUSR|S_IXUSR|S_IRGRP|S_IWGRP|S_IXGRP|S_IROTH|S_IWOTH|S_IXOTH);
}bool ECOTS::DataStorage::isTableExist(const QString &_tableName)
{return sqldb.tables().contains(_tableName);
}bool ECOTS::DataStorage::createTable(const QString &_tableName)
{QSqlQuery query;QString  checkCmd = "SELECT * FROM " + _tableName;if (!query.exec(checkCmd)){//table not existQString sqlCmd = "CREATE TABLE IF NOT EXISTS " + _tableName +" (ID INTEGER PRIMARY KEY AUTOINCREMENT, Name TEXT NOT NULL, Value TEXT NOT NULL)";query.prepare(sqlCmd);if (!query.exec()){qDebug() << "exec sql error: " << query.lastError().text();return false;}qDebug() << "create table success";}return true;}void ECOTS::DataStorage::setVariable(const QString &_key, const QString &_value)
{QWriteLocker wlock(&rw);QString backupValue = _value;if (_value.isNull()){//prevent input NULL to databasebackupValue = "null";}QSqlQuery query;bool isExist = false;// determine whether key exists in tableQString boolCmd = "SELECT * FROM ECOTS WHERE Name = '" + _key + "'";if (exeSqlCmd(query, boolCmd)){if (query.next()){isExist = true;}}else{return;}if (!isExist){ //key not exist -> insertQString insertCmd = "INSERT INTO ECOTS (Name, Value) VALUES ('" + _key + "', '" + backupValue + "')";if (exeSqlCmd(query, insertCmd)) {qDebug() << QString("insert new data key=%1, value=%2 success").arg(_key).arg(backupValue);}}else{//key is exist -> updateQString updateCmd = "UPDATE ECOTS SET Value = '" + backupValue + "' WHERE Name = '" + _key + "'";if (exeSqlCmd(query, updateCmd)) {qDebug() << QString("update %1 = %2 success").arg(_key).arg(backupValue);}}
}QString ECOTS::DataStorage::getVariableString(const QString &_key)
{QReadLocker rlock(&rw);QString result("");QSqlQuery query;QString printCmd("SELECT * FROM ECOTS");if (exeSqlCmd(query, printCmd)){while (query.next()) // if key not exist in table -> query.next=false{int tId = query.value(0).toInt();QString tname = query.value(1).toString();QString tvalue = query.value(2).toString();qDebug() << QString("ID=%1  Name=%2   Value=%3").arg(tId).arg(tname).arg(tvalue);if (_key == tname){result = tvalue;break;}}}return result;
}int ECOTS::DataStorage::printTable(QMap<QString, QString> &tableInfo_)
{tableInfo_.clear();QSqlQuery query;QString printCmd("SELECT * FROM ECOTS");if (exeSqlCmd(query, printCmd)){while (query.next()){QString tname = query.value(1).toString();QString tvalue = query.value(2).toString();tableInfo_.insert(tname, tvalue);}}return tableInfo_.size();
}bool ECOTS::DataStorage::close()
{sqldb.close();return (sqldb.isOpen() ? false : true);
}bool  ECOTS::DataStorage::deleteTable(const QString &_tableName)
{QSqlQuery query;QString rmCmd = "DROP TABLE " + _tableName;exeSqlCmd(query, rmCmd);return ((sqldb.tables().contains(_tableName) )? false : true);
}bool ECOTS::DataStorage::exeSqlCmd(QSqlQuery &_query, const QString &_cmd)
{if (_cmd.isEmpty())return false;bool result = false;_query.prepare(_cmd);if (!_query.exec()) {qDebug() << "exec sql error: " << _query.lastError().text();} else {qDebug() << QString("exec cmd: %1 success").arg(_cmd);result = true;}return result;
}

测试代码

/*测试代码 */#include <QApplication>
#include "datastorage.h"
#include <QThread>
#include <QVector>
#include <QStringList>
#include <QTime>
#include <QDebug>void __sleep(unsigned int msec)
{QTime dieTime = QTime::currentTime().addMSecs(msec);while (QTime::currentTime() < dieTime){QCoreApplication::processEvents(QEventLoop::AllEvents, 100);}
}class dbThread : public QThread
{
public:dbThread() {}
protected:void run(){qDebug() << "thread start ...";ECOTS::DataStorage &db = ECOTS::DataStorage::getDataStorage();qDebug() << QString(" thread-id:   %1").arg(QThread::currentThreadId());QStringList list;list << "aaa" << "bbb" << "ccc" << "ddd" << "eee" << "fff" << "ggg" << "sss" << "xxx";qsrand(QTime(0,0,0).secsTo(QTime::currentTime()));while (1){int t = qrand()  % 9;qDebug() << QString("thread: %1 set var %2").arg(QThread::currentThreadId()).arg(list[t]);db.setVariable("SessionID", list.at(t) );db.setVariable("TestProgramPath", list.at(t));QThread::sleep(3);}}
};class dbThread1 : public QThread
{
public:dbThread1() {}
protected:void run(){qDebug() << "thread start ...";ECOTS::DataStorage &db = ECOTS::DataStorage::getDataStorage();qDebug() << QString(" thread-id:   %1").arg(QThread::currentThreadId());QStringList list;list << "111" << "222" << "333" << "444" << "555" << "666" << "777" << "888" << "999";qsrand(QTime(0,0,0).secsTo(QTime::currentTime()));while (1){int t = qrand()  % 9;qDebug() << QString("thread: %1 set var %2").arg(QThread::currentThreadId()).arg(list[t]);db.setVariable("SessionID", list.at(t) );db.setVariable("TestProgramName", list.at(t));QThread::sleep(3);}}
};
int main(int argc, char *argv[])
{QApplication a(argc, argv);/* create database */ECOTS::DataStorage &db = ECOTS::DataStorage::getDataStorage();/* insert or update data*/QMap<QString, QString> hoge;hoge["TestProgramName"] = "demoTP";hoge["TestProgramPath"] = "/home/jwu/device";hoge["SessionID"] = ":0";hoge["LotID"] = "m123";hoge["waferID"] = "wafer_000001";hoge["SessionID"] = ":9";dbThread t1;dbThread1 t2;t1.start();t2.start();while(1){qDebug() <<"ID = " << QThread::currentThreadId() << "  ***getVar ---> " << db.getVariableString("SessionID");__sleep(5000);}t1.wait();t2.wait();return a.exec();
}

Qt 自带数据库 QSQLite 的使用

下面是用qt的QSQLITE做的数据库,我的环境是redhat项目要求是提供2个接口：

下面是用qt的QSQLITE做的数据库,我的环境是redhat
项目要求是提供2个接口：