Qt应用必备模块之软件全局数据库（单例模式）实现

1 概述

采用SQLITE数据库，废话不多说，直接上代码。

2 实现

使用时只需将头文件和源文件加入工程，在要用到配置的地方使用appDatabaseInstance。

2.1 databasesql.h

 

#ifndef DATABASESQL_H
#define DATABASESQL_H

#include <QString>

// loginInfo
#define SQL_CREATE_LOGININFO QString("create table if not exists loginInfo(user text primary key unique,password text,loginTime date)")
#define SQL_INSERT_LOGININFO QString("insert into loginInfo values(\"%1\",\"%2\",%3)")
#define SQL_UPDATE_LOGININFO QString("update loginInfo set password=\"%1\",loginTime=%2 where user=\"%3\"")
#define SQL_SELECT_LOGININFO_BY_USER QString("select * from loginInfo where user=\"%1\"")
#define SQL_SELECT_LOGININFO_ALL QString("select * from loginInfo")
#define SQL_DELETE_LOGININFO_BY_USER QString("delete from loginInfo where user=\"%1\"")

// userInfo
#define SQL_CREATE_USERINFO QString("create table if not exists userInfo(workerno text primary key unique,workername text,password text,serverlevel text)")
#define SQL_INSERT_USERINFO QString("insert into userInfo values(\"%1\",\"%2\",\"%3\",\"%4\")")
#define SQL_UPDATE_USERINFO QString("update userInfo set workername=\"%1\",password=\"%2\",serverlevel=\"%3\" where workerno=\"%4\"")
#define SQL_SELECT_USERINFO_BY_USER QString("select * from userInfo where workerno=\"%1\"")
#define SQL_SELECT_USERINFO_ALL QString("select * from userInfo")
#define SQL_DELETE_USERINFO_BY_USER QString("delete from userInfo where workerno=\"%1\"")
#define SQL_DELETE_USERINFO_ALL QString("delete from userInfo")



#endif // DATABASESQL_H

2.2 appdatabase.h

#ifndef APPDATABASE_H
#define APPDATABASE_H

#include <QSqlDatabase>
#include <QSqlError>
#include <QSqlQuery>
#include <QList>
#include <QMap>
#include "configdef.h"
#include "databasesql.h"

#define appDatabaseInstance AppDatabase::getInstance()

class AppDatabase
{
public:
    static AppDatabase* getInstance()
    {
        if(appDatabase == nullptr)
        {
            appDatabase = new AppDatabase();
        }

        return appDatabase;
    }

    static void quit();

    bool insertData(const QString sqlStr);
    bool updateData(const QString sqlStr);
    bool selectData(const QString sqlStr, QList<QMap<QString,QVariant>> &valuesList);
    bool deleteData(const QString sqlStr);
    bool openDatabase(const QString dbFileName);
    bool initDatabase();
private:
    AppDatabase();//禁止构造函数
    AppDatabase(const AppDatabase&){}//禁止拷贝构造函数
    AppDatabase & operator=(const AppDatabase &){}//禁止赋值拷贝构造函数

private:
    static AppDatabase *appDatabase;

    static QSqlDatabase database;
    static QString dbPath;
    static QString dbFile;
    static QString dbName;
    static QString dbUser;
    static QString dbPasswd;
};

#endif // APPDATABASE_H

2.3 appdatabase.cpp

这里放在cpp文件里进行初始化是为了防止重复包含头文件导致多重定义错误编译不通过。

#include "appdatabase.h"
#include "appsettings.h"
#include <QDir>

AppDatabase *AppDatabase::appDatabase = nullptr;

QSqlDatabase AppDatabase::database;
QString AppDatabase::dbPath = "./database";
QString AppDatabase::dbFile = "database.db";
QString AppDatabase::dbName = "default";
QString AppDatabase::dbUser = "root";
QString AppDatabase::dbPasswd = "1234";

AppDatabase::AppDatabase()
{
    dbPath = appSettingsInstance->getSetting(DATABASE_PATH).toString();
    dbFile = appSettingsInstance->getSetting(DATABASE_FILE).toString();
    dbName = appSettingsInstance->getSetting(DATABASE_NAME).toString();
    dbUser = appSettingsInstance->getSetting(DATABASE_USER).toString();
    dbPasswd = appSettingsInstance->getSetting(DATABASE_PASSWD).toString();

    //数据库文件目录，不存在则创建
    QDir dir;
    if(!dir.exists(dbPath))//判断路径是否存在
    {
        dir.mkpath(dbPath); //创建路径
        qInfo("mkpath:%s",qPrintable(dbPath));
    }

    if(openDatabase(dbPath + "/" + dbFile))
        initDatabase();
}

void AppDatabase::quit()
{
    if(database.isOpen()){
        database.close();
        qInfo("Close database success.");
    }
    qInfo("[OK] AppDatabase quit.");
}

bool AppDatabase::insertData(const QString sqlStr)
{
    QSqlQuery sql_query;
    qInfo("[SQL] %s",qPrintable(sqlStr));
    if(!sql_query.exec(sqlStr))
    {
        qWarning("[SQL] %s",qPrintable(sql_query.lastError().text()));
        return false;
    }
    return true;
}

bool AppDatabase::updateData(const QString sqlStr)
{
    QSqlQuery sql_query;
    qInfo("[SQL] %s",qPrintable(sqlStr));
    if(!sql_query.exec(sqlStr))
    {
        qWarning("[SQL] %s",qPrintable(sql_query.lastError().text()));
        return false;
    }
    return true;
}

bool AppDatabase::selectData(const QString sqlStr,QList<QMap<QString,QVariant>> &valuesList)
{
    QSqlQuery sql_query;
    qInfo("[SQL] %s",qPrintable(sqlStr));
    if(!sql_query.exec(sqlStr))
    {
        qWarning("[SQL] %s",qPrintable(sql_query.lastError().text()));
        return false;
    }

    for( int r=0; sql_query.next(); r++ )
    {
        QSqlRecord rec = sql_query.record();
        QMap<QString,QVariant> valueMap;
        valueMap.clear();

        for( int c=0; c<rec.count(); c++ )
        {
            valueMap[rec.fieldName(c)] = rec.value(c);
        }

        valuesList.append(valueMap);
    }

    return true;
}

bool AppDatabase::deleteData(const QString sqlStr)
{
    QSqlQuery sql_query;
    qInfo("[SQL] %s",qPrintable(sqlStr));
    if(!sql_query.exec(sqlStr))
    {
        qWarning("[SQL] %s",qPrintable(sql_query.lastError().text()));
        return false;
    }
    return true;
}

bool AppDatabase::openDatabase(const QString dbFileName)
{
    //建立并打开数据库
    if(QSqlDatabase::contains("qt_sql_default_connection"))
        database = QSqlDatabase::database("qt_sql_default_connection");
    else
        database = QSqlDatabase::addDatabase("QSQLITE");

    database.setDatabaseName(dbFileName);
    database.setUserName(dbUser);
    database.setPassword(dbPasswd);
    if (!database.open())
    {
        qWarning(qPrintable(QString("Connect database %1 error, %2").arg(dbFileName).arg(database.lastError().text())));
        return false;
    }
    else
    {
        qWarning(qPrintable(QString("Connect database %1 succeed.").arg(dbFileName)));
        return true;
    }
}


bool AppDatabase::initDatabase()
{
    if (database.open())
    {
        QSqlQuery sql_query;

        //创建表loginInfo
        qInfo("[SQL] %s",qPrintable(SQL_CREATE_LOGININFO));
        if(!sql_query.exec(SQL_CREATE_LOGININFO))
        {
            qWarning("[SQL] %s",qPrintable(sql_query.lastError().text()));
            return false;
        }
        //创建表userInfo
        qInfo("[SQL] %s",qPrintable(SQL_CREATE_USERINFO));
        if(!sql_query.exec(SQL_CREATE_USERINFO))
        {
            qWarning("[SQL] %s",qPrintable(sql_query.lastError().text()));
            return false;
        }

        return true;
    }
    else
    {
        qWarning("Init database failed,database is not open.");
        return false;
    }
}