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线程的写操作数据库就不处理了。

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

1. pro 工程文件代码 

   #-------------------------------------------------
   #
   # Project created by QtCreator 2019-12-07T17:35:30
   #
   #-------------------------------------------------
   
   QT       += core gui sql
   
   greaterThan(QT_MAJOR_VERSION, 4): QT += widgets
   
   TARGET = 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.0
   
   
   SOURCES += \
        main.cpp \
       datastorage.cpp
   
   
   HEADERS += \
       datastorage.h

    

2. *.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 obj
       static DataStorage &getDataStorage();
       //insert or update
       void setVariable(const QString &_key, const QString &_value);
       //view
       QString getVariableString(const QString &_key);
   
       //output tables to map
       int printTable(QMap<QString,QString> &tableInfo_);
       //close database
       bool close();
       bool deleteTable(const QString &_tableName = "ECOTS");
   
   protected:
       //check database path is accessible, if not exist path -> mkdir 777
       void checkStorageDbPath(const QString &_dbFile);
       //check table exits in database or not
       bool isTableExist(const QString &_tableName);
       //create table in databse
       bool createTable(const QString &_tableName);
       // exec sql
       bool exeSqlCmd(QSqlQuery &_query,  const QString &_cmd);
   
   private:
       DataStorage();
       ~DataStorage();
       QSqlDatabase sqldb;
       QReadWriteLock rw;
   };
   
   } //namespace
   
   #endif // DATASTORAGE_H

    

3. *.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 not
           mkdir(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 exist
           QString 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 database
          backupValue = "null";
       }
   
       QSqlQuery query;
       bool isExist = false;
   
       // determine whether key exists in table
       QString boolCmd = "SELECT * FROM ECOTS WHERE Name = '" + _key + "'";
       if (exeSqlCmd(query, boolCmd))
       {
           if (query.next())
           {
               isExist = true;
           }
       }
       else
       {
           return;
       }
   
       if (!isExist)
       { //key not exist -> insert
           QString 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 -> update
           QString 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;
   }

    

4. 测试代码 

   /*测试代码 */
   
   #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();
   }