23/12/2019 26

How to use QSqLite in QT-GUI with C++

Hello everyone, in this article we are going to talk about how can we use qSqLite in QT GUI C++. We will firstly explain and then make an example about QSqLite. Now let's get started.

First we have to add sql library in our projects .pro file.


QT       += sql 

Now we can start to use QSqLite in our QT C++ program. To use QSQlite you can write your codes directly under your slots or you can ceate another C++ class and make callbacks from everywhere of your program. I suggest second way to decrease confisuon. Also write less code.

Below Images you can find how to add new class.
Add New Class - Thecodeprogram And
Choose The File Type - Thecodeprogram And then
Set name of Class Name

Now let's write some code.

First in our database manager header file ( dbmanager.h ) we have to include QSQL header files. Below code block you can see the header.

#include <QSqlDatabase> #include <QSqlQueryModel> #include <QSqlQuery> #include <QSqlError> #include <QSqlRecord> #include <QSqlQueryModel> First define your DbManager variable in header file. I defined it as private.

private:
    QSqlDatabase m_db;

After including header files we will create a constructor method of the class, a destroyer method and an open database method. In constructor method we will set the database type and database file. When we calledback this class we will send the database file name in it. When we close the program to prevent some issues we will close the connection between database and our program.

I am also using the table and column names from structures. Maybe you will use these tabels and columns in many code blocks. Sometimes you may change the name of them. If you write them into everywhere maybe some confissuons take place. To prevent this I will write these names in a structure and I will fetch them from these structures.

Below code block you can see the my table names and column names code structures. I will use the structure names in this article.

    QString tableNamesMusicList = "tblMusicList";
    QString tableNamesSettings = "tblSettings";

    typedef struct {
         QString id = "id";
         QString current_index = "current_index";
         QString selected_index = "selected_index";
         QString repeatSingle = "repeatSingle";
         QString repeatAll = "repeatAll";
         QString shuffle = "shuffle";
         QString volume = "volume";
     } table_settings ;

    typedef struct{
        QString id = "id";
        QString name = "name";
        QString path = "path";
        QString lenghtAsSecond = "lenghtAsSecond";
        QString lenghtToShow = "lenghtToShow";
    } table_music;


    /**
     * @brief List of table and column names which creatted above
     */
    struct tablesAndColumns{

        table_settings _tableSettings;
        table_music _tableMusic;

    };

Below code block youc an see the related functions.


DbManager::DbManager(const QString &path)
{
    m_db = QSqlDatabase::addDatabase("QSQLITE");
    m_db.setDatabaseName(path);
    allTables = new tablesAndColumns();

    if (!m_db.open())
        qDebug() << "Error: connection with database fail";
    else
        qDebug() << "Database: connection ok";
}

DbManager::~DbManager()
{
    if (m_db.isOpen())
        m_db.close();
}

bool DbManager::isOpen() const
{
    return m_db.isOpen();
}

These are required function to start and stop our QSqlite and now we need functions which accomodate main purpose of sql. So we need functions to create table, insert record, select record, delete and update record. Now we will write these methods one by one.

As I mentioned above I will use the stuctures to use table and column names. I Will not write the names directly into query.

Below code block will create a table which named as variable tableName and related column names.


bool DbManager::createMusicListTable(const QString &tableName)
{
    bool success = true;
    QSqlQuery query;
    query.prepare("CREATE TABLE " + tableName + "("
                  + allTables->_tableMusic.id       + " INTEGER PRIMARY KEY AUTOINCREMENT, "
                  + allTables->_tableMusic.name     + " TEXT, "
                  + allTables->_tableMusic.path     + " TEXT,"
                  + allTables->_tableMusic.lenghtAsSecond   + " INTEGER "
                  + allTables->_tableMusic.lenghtToShow + " TEXT );");
    if (!query.exec())
    {
        qDebug() << "Music list table already created ";
        success = false;
    }
    return success;
}

Below code block will insert data into related database table. We will insert datas with parameters to prevent sql injection. I always suggest to add with parameters.


bool DbManager::insertMusic(const QString tableName,const QString &name, const QString &path, const int &lenghtAsSecond, QString lenghtToShow)
{
    bool success = false;
    if (!name.isEmpty())
    {
        QSqlQuery queryAdd;
        queryAdd.prepare("INSERT INTO " + tableName + " (name,path,lenghtAsSecond,lenghtToShow) VALUES (:name,:path,:lenghtAsSecond,:lenghtToShow)");
        queryAdd.bindValue(":name", name);
        queryAdd.bindValue(":path", path);
        queryAdd.bindValue(":lenghtAsSecond", lenghtAsSecond);
        queryAdd.bindValue(":lenghtToShow", lenghtToShow);

        if(queryAdd.exec())
            success = true;
        else
            qDebug() << "record could not add: " << queryAdd.lastError();
    }
    else
        qDebug() << "Data is required to insert.";

    return success;
}

Below code block will DELETE a record from related table and related id value.


bool DbManager::removeMusic(const int &musicId)
{
    bool success = false;

    if (musicId > -1)
    {
        QSqlQuery queryDelete;
        queryDelete.prepare("DELETE FROM " + tableNamesMusicList + " WHERE id=" + QString::number(musicId) );
        success = queryDelete.exec();

        if(!success)
            qDebug() << "Record has been deleted: " << queryDelete.lastError();
    }
    else
        qDebug() << "Record has not been deleted";
    return success;
}

Below code block will update related data on related table.


bool DbManager::updateMusicDuration(const int &musicId , const int &lenghtAsSecond , QString lenghtToShow )
{
    bool success = false;

    if (musicId > -1)
    {
        QString query_str = "UPDATE " + tableNamesMusicList +
                " SET "
                + "lenghtAsSecond= " + QString::number(lenghtAsSecond) + " , "
                + "lenghtToShow='"+ lenghtToShow + "' "
                + "where id=" + QString::number(musicId);

        QSqlQuery queryAdd;
        queryAdd.prepare(query_str);

        if(queryAdd.exec())
            success = true;
        else
            qDebug() << "Music could not be added: " << queryAdd.lastError();
    }
    else
        qDebug() << "Music ID is required to update...";

    return success;
}

Now our codes are ready in our database manger class. Now we will make callbacks from our main window class. Now let's write some codes.

First we need to include our dbmanager header file.

#include <dbmanager.h>

First we will start our class. I started them in MainWindow.cpp file:


static const QString path = "TheMusicDataBase.db";
DbManager db(path);

Below code block will check if database is opened and is database connection is opened correctly, it will create related tables. Also I will check the tables if they created the function will be terminated inside the function.


    if (db.isOpen())
    {
        //if not exist create the database tables.
        db.createMusicListTable(db.tableNamesMusicList);
        db.createSettingsTable(db.tableNamesSettings);
    }
    else
    {
        qDebug() << "Database could not be opened!";
    }

Below code block will make some updates on related database table.


db.updateMusicDuration( music->id, position /1000, sarki_sure_getir( position/1000 )  );

Below code block will remove some record from database table.


void MainWindow::keyReleaseEvent(QKeyEvent* ke)
{
    switch (ke->key()) {
    case(Qt::Key_Delete):
        if(_activeSettings.selected_index > -1){
            db.removeMusic(_activeSettings.selected_id);
        }
        break;
    }
}

Below code block will insert record into database.


db.insertMusic(db.tableNamesMusicList,fileInfo.fileName(),_activeSettings.dosya, 0, "00:00");

That is all in this article.

You can also reach the example program with this link.

Have a nice coding.

Burak Hamdi TUFAN.


Tags

Share this Post



Post a Comment

Success! Your comment sent to post. It will be showed after confirmation.
Error! There was an error sending your comment.

Comments

    There is no comment. Be the owner of first comment...