下面是用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的操作，总是感觉不能完全把握，还需进一步观察。
#-------------------------------------------------
#
# 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
/** 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
/** 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;
}
/*测试代码 */
#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();
}
网友评论