Important: Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

Class to read and save different objects in SQL database



  • I am trying to create a class that is capable of saving and reading objects of different type in a SQL database. The goal is to avoid redundancy of methods in each object class ( I could, for simplicity write my sql database methods in each object class that I want to save and read and it would work). The strategy I tried is to inherit my object classes with a class containing static methods. Additionnaly different object type have to be saved in different database.

    As the database class contains static methods, I am obliged to use static member variables in order to save the database name and path. This is the point that is bothering me. Static variables can be initialized just once. Hence, the database name and path cannot be different for different objects.

    My question is the following: what strategy should I use, if I want to have a single class that is capable of saving and reading different object types in different databases according to the object type.

    Below I provide a minimal example of what I did and that is not the good strategy:

    example.pro

    QT -= gui
    QT += sql
    
    CONFIG += c++11 console
    CONFIG -= app_bundle
    
    # The following define makes your compiler emit warnings if you use
    # any Qt feature that has been marked 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
    
    # You can also make your code fail to compile if it uses 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 += \
            a.cpp \
            b.cpp \
            c.cpp \
            main.cpp
    
    # Default rules for deployment.
    qnx: target.path = /tmp/$${TARGET}/bin
    else: unix:!android: target.path = /opt/$${TARGET}/bin
    !isEmpty(target.path): INSTALLS += target
    
    HEADERS += \
        a.h \
        b.h \
        c.h
    

    a.h

    #ifndef A_H
    #define A_H
    
    #include "QString"
    #include "c.h"
    
    class A: public C
    {
    public:
        A(const QString &, const double &);
        A();
    
        void setParamA(const double &);
        const double& getParamA() const;
    
        void setMemberId(const QString &);
        const QString& getMemberId() const;
    
    
    private:
        QString m_memberId;
        double m_paramA;
    
        //**************************************************************//
        //***********************SERIALIZATION**************************//
        //**************************************************************//
    
        friend QDataStream &operator<<(QDataStream &out, const A &a)
        {
            out << a.getMemberId() << a.getParamA();
            return out;
        }
    
        friend QDataStream &operator>>(QDataStream &in, A &a)
        {
            QString memberId;
            double paramA;
    
            in >> memberId >> paramA;
    
            a.setMemberId(memberId);
            a.setParamA(paramA);
    
            return in;
        }
    
    
    };
    
    #endif // A_H
    

    a.cpp

    #include "a.h"
    
    QString C::m_connectionName = "aDB";
    QString C::m_pathDatabase = "dbA.db";
    
    
    A::A(const QString &memberId, const double & param):
        m_memberId(memberId),
        m_paramA(param)
    {
    
    }
    
    A::A()
    {
    
    }
    
    void A::setParamA(const double &x)
    {
        m_paramA = x;
    }
    
    const double & A::getParamA() const
    {
        return m_paramA;
    }
    
    void A::setMemberId(const QString &x)
    {
        m_memberId = x;
    }
    
    const QString & A::getMemberId() const
    {
        return m_memberId;
    }
    

    b.h

    #ifndef B_H
    #define B_H
    
    
    #include "QString"
    #include "a.h"
    #include "c.h"
    
    class B: public C
    {
    public:
        B(const QString &, const A &);
        B();
    
        void setParamB(const double &);
        const double& getParamB() const;
    
        void setMemberId(const QString &);
        const QString& getMemberId() const;
    
    
    private:
        QString m_memberId;
        double m_paramB;
    
    
        //**************************************************************//
        //***********************SERIALIZATION**************************//
        //**************************************************************//
    
        friend QDataStream &operator<<(QDataStream &out, const B &b)
        {
            out << b.getMemberId() << b.getParamB();
            return out;
        }
    
        friend QDataStream &operator>>(QDataStream &in, B &b)
        {
            QString memberId;
            double paramB;
    
            in >> memberId >> paramB;
    
            b.setMemberId(memberId);
            b.setParamB(paramB);
    
            return in;
        }
    
    };
    
    
    #endif // B_H
    

    b.cpp

    #include "b.h"
    
    
    /*
    QString C::m_connectionName = "bDB";
    QString C::m_pathDatabase = "dbB.db";
    */
    
    //I want here to be able to connect to a different database but if I remove the comments, I have the famous error
    
    
    B::B(const QString &memberId, const A & a):
        m_memberId(memberId),
        m_paramB(a.getParamA() / 2)
    {
    
    }
    
    B::B()
    {
    
    }
    
    void B::setParamB(const double &x)
    {
        m_paramB = x;
    }
    
    const double & B::getParamB() const
    {
        return m_paramB;
    }
    
    void B::setMemberId(const QString &x)
    {
        m_memberId = x;
    }
    
    const QString & B::getMemberId() const
    {
        return m_memberId;
    }
    

    c.h

    #ifndef C_H
    #define C_H
    
    #include "QString"
    #include "QDebug"
    #include "QtSql"
    
    class C
    {
    public:
        C();
        static void initConnectionToDB();
        static void createNewTableInDB( const QString &);
        static QSqlDatabase connectToDB();
        static void closeConnectionToDB();
    
        template <class T>
        static QByteArray object2blob( const T& u )
        {
          QByteArray blob;
          QDataStream bWrite( &blob, QIODevice::WriteOnly );
          bWrite << u;
          return blob;
        }
    
        template <class T>
        static T blob2object( const QByteArray& blob, T& t)
        {
          QDataStream bRead( blob );
          bRead >> t;
          return t;
        }
    
        template <class T>
        static void saveToDB(const T& t, const QString & tableID, const QString & memberId)
        {
            QSqlQuery query(connectToDB());
    
            query.prepare( "INSERT INTO " + tableID + " (memberId,objectBlob) VALUES (:memberId,:objectBlob)" );
            query.bindValue(":memberId", memberId);
            query.bindValue(":objectBlob",  object2blob<T>(t) );
            if( !query.exec() )
            {
                qDebug() << query.lastError().text();
            }
        }
    
        template <class T>
        static T readFromDB(const QString & tableID, const QString & memberId )
        {
            QSqlQuery query(connectToDB());
            query.prepare( "SELECT * FROM " + tableID + " WHERE memberId ='"+ memberId +"'" );
            T t;
            if ( !query.exec() )
            {
                qDebug() << query.lastError().text();
            }
            else
            {
                if ( !query.next())
                {
                    qDebug()<<"Not in the database";
                }
                else
                {
                    t = blob2object<T>( query.value( 1 ).toByteArray(), t );
                }
            }
            return t;
        }
    
    protected:
        static QString m_connectionName;
        static QString m_pathDatabase;
    
    };
    
    #endif // C_H
    

    c.cpp

    #include "c.h"
    
    C::C()
    {
    
    }
    
    void C::initConnectionToDB()
    {
        QSqlDatabase myDB = QSqlDatabase::addDatabase("QSQLITE", QStringLiteral("%1").arg(m_connectionName));
        myDB.setDatabaseName(m_pathDatabase);
        if (!myDB.open())
        {
            qDebug()<<myDB.lastError().text();
        }
    }
    
    QSqlDatabase C::connectToDB()
    {
        return QSqlDatabase::database(QStringLiteral("%1").arg(m_connectionName));
    }
    
    void C::closeConnectionToDB()
    {
        QSqlDatabase::removeDatabase(QStringLiteral("%1").arg(m_connectionName));
    }
    
    void C::createNewTableInDB( const QString &tableId)
    {
        QSqlQuery query(C::connectToDB());
        query.prepare( "CREATE TABLE IF NOT EXISTS " + tableId + " (memberId TEXT, objectBlob BLOB)" );
        if( !query.exec() )
        {
            qDebug() << query.lastError().text();
        }
    }
    

    main.cpp

    #include <QCoreApplication>
    #include "a.h"
    #include "b.h"
    
    
    int main(int argc, char *argv[])
    {
        QCoreApplication app(argc, argv);
        A::initConnectionToDB();
        B::initConnectionToDB();
    
    
        A::createNewTableInDB("testTableA");
    
    
        A a1("pA", 6);
        a1.saveToDB(a1,"testTableA", "memberA1");
    
        A a2 = A::readFromDB<A>("testTableA", "memberA1");
        qDebug()<<a2.getMemberId()<<a2.getParamA();
    
    
    
        B::createNewTableInDB("testTableB");
    
    
        B b1("memberB1",a1);
        b1.saveToDB(b1, "testTableB", "memberB1");
    
    
        A::closeConnectionToDB();
        B::closeConnectionToDB();
    
        return app.exec();
    }
    

  • Lifetime Qt Champion

    Hi,

    Do you mean something like QxOrm ?


Log in to reply