Qt - Selecting data from a QSqlQuery database in a QThread stream and creating a QAbstractTableModel model based on it

QThread, Qt, QAbstractTableModel, QSqlQuery

There was a need to obtain a large amount of data from the database for the subsequent work of the user with them (Reference book of goods in a particular store).

For the code do not kick much. Programming for me is a hobby for the mind.

The query itself takes a long time. For this, we do it in the stream, display the endless ProgressBar to the user with the sentence "Lean back and wait." :-)

The data received in the request is stored in a vector of objects of the class Articles.

Articles

Class for storing a single record of the resulting query. For storage of all records we use a vector.

articles.h

#ifndef ARTICLES_H
#define ARTICLES_H

#include <QString>

class Articles
{
public:
    Articles();
    void setColParam(int _colParam);
    void setID(int _articleID);
    void setShortName(QString _shortName);
    void setAmount(float _amount);
    void setPrice(float _price);
    int getColParam();
    int getID();
    QString getShortName();
    float getAmount();
    float getPrice();
private:
    int colParam;           // The number of parameters. necessary to create a model later
    int artileID;           // Product code
    QString shortname;      // Name
    float amount;           // Amount
    float price;            // Price
};

#endif // ARTICLES_H

articles.cpp

#include "articles.h"

Articles::Articles()
{
    setColParam(4);
}

void Articles::setColParam(int _colParam)
{
    colParam = _colParam;
}

int Articles::getColParam()
{
    return colParam;
}

void Articles::setID(int _articleID)
{
    artileID = _articleID;
}

void Articles::setShortName(QString _shortName)
{
    shortname = _shortName;
}

void Articles::setAmount(float _amount)
{
    amount = _amount;
}

void Articles::setPrice(float _price)
{
    price = _price;
}

int Articles::getID()
{
    return artileID;
}

QString Articles::getShortName()
{
    return shortname;
}

float Articles::getAmount()
{
    return amount;
}
float Articles::getPrice()
{
    return price;
}

For logging and debugging I used the methods described in this article Logging Qt application events to a text file .

ListArticles

The class for sampling from the database

listarticles.h

#ifndef LISTARTICLES_H
#define LISTARTICLES_H

#include "articles.h"

#include <QObject>
#include <QSqlQuery>
#include <QSqlError>
#include <QVector>
#include <QSqlRecord>


class ListArticles : public QObject
{
    Q_OBJECT
public:
    explicit ListArticles(QSqlRecord rec, int terminal_D, int shift, QObject *parent = nullptr);

signals:
    // Signal to send data to the main stream for further processing
    void signalSendArticlesList(QVector<Articles>);
    // Signal about the end of data sampling
    void finish();

public slots:
    // Slot for starting data sampling, called from the main stream
    void createListGoods();

private:
    int m_terminalID;               // Store number, parameter for request
    int m_shiftID;                  // Shift number, parameter for request
    QVector<Articles> goods;        // A vector in which we will store the results of the query and transfer them to the main stream.
    QSqlRecord connRec;             // Record selected from another model containing database connection parameters

};

#endif // LISTARTICLES_H

listarticles.cpp

#include "listarticles.h"
#include "loggingcategories.h"

ListArticles::ListArticles(QSqlRecord rec, int terminal_D, int shift, QObject *parent) : QObject(parent)
{
    // Получаем входные данные
    m_terminalID = terminal_D;
    m_shiftID = shift;
    connRec =rec;
}

void ListArticles::createListGoods()
{
    // Create an object to store one record.
    Articles ar;

    // we register type for transfer it through the mechanism a signal slot.
    typedef QVector<Articles> vek;
    qRegisterMetaType<vek>("vektor");

    // From Assistant
    // "A connection can only be used within the thread that created it. Moving connections between threads and creating requests to another thread is not supported."
    // Therefore, we are creating a new connection.

    QSqlDatabase dbth = QSqlDatabase::addDatabase("QIBASE","thcentr");

    dbth.setHostName(connRec.value("conn_host").toString());
    dbth.setDatabaseName(connRec.value("conn_db").toString());
    dbth.setUserName(connRec.value("conn_user").toString());
    dbth.setPassword(connRec.value("conn_pass").toString());


    if(!dbth.open()) {
        qCritical(logCritical()) << Q_FUNC_INFO << "I can not connect to the central database"
                                 << endl << dbth.lastError().text();
        return;
    }
    // Bind request to connection
    QSqlQuery q = QSqlQuery(dbth);

    // We form line with request.
    // I prefer to create queries of this type as strings and test them directly in the database manager.

    QString strSQL = QString("SELECT A.GARTICLE_ID, GA.SHORTNAME, SL.AMOUNT, "
                                 "(SELECT FIRST 1 NEWPRICE FROM HISTORY_PRICES HP "
                                  "WHERE HP.TERMINAL_ID = SL.TERMINAL_ID "
                                  "AND HP.GARTICLE_ID = A.GARTICLE_ID "
                                  "AND HP.DATEOP < SH.DATCLOSE "
                                  "ORDER BY HP.DATEOP DESC) AS PRICE "
                               "FROM GET_ASALDOS (%1, %2, NULL, 0) AS SL "
                               "INNER JOIN ARTICLES A ON A.TERMINAL_ID = SL.TERMINAL_ID AND A.ARTICLE_ID = SL.ARTICLE_ID "
                               "LEFT JOIN SHIFTS SH ON SH.TERMINAL_ID = SL.TERMINAL_ID AND SH.SHIFT_ID = SL.SHIFT_ID "
                               "LEFT JOIN GARTICLES GA ON GA.GARTICLE_ID = A.GARTICLE_ID "
                               "WHERE SL.AMOUNT > 0 "
                               "ORDER BY A.GARTICLE_ID" )
                .arg(m_terminalID)
                .arg(m_shiftID);
    // Execute the request
    if(!q.exec(strSQL)) {
        qInfo(logInfo()) << "Errog goodlist" << q.lastError().text();
        emit finish();
    }
    // The cycle of receiving records, and adding them to the vector
    while (q.next()){
        ar.setID(q.value("GARTICLE_ID").toInt());
        ar.setShortName(q.value("SHORTNAME").toString().trimmed());
        ar.setAmount(q.value("AMOUNT").toFloat());
        ar.setPrice(q.value("PRICE").toFloat());
        goods.append(ar);
    }
    //Passing the result to the main thread.
    emit signalSendArticlesList(goods);
    //Flow finished work
    emit finish();
}

Run the data stream of query

// Data selection occurs when you go to a specific QWizardPage page.
void ArticlePage::initializePage()
{
    // Создаем объект класса и передаем ему параметры
    ListArticles *lsArticles = new ListArticles(recrodConn, field("terminalID").toInt(),field("shiftID").toInt());
    // Create a stream in which our sample will be made.
    QThread *thread = new QThread();
    // Moving a class object to a stream
    lsArticles->moveToThread(thread);

    //// Signals and slots for interacting with the stream

    // When starting a thread, we perform some actions in the current thread.
    // In my case, I simply mark the beginning of the data sample.
    connect(thread,&QThread::started,this,&ArticlePage::slotStartArticlesList);
    // At the start of the stream, we start sampling data
    connect(thread,&QThread::started,lsArticles,&ListArticles::createListGoods);

    // Passing the resulting QVector object from the child to the main thread
    connect(lsArticles,&ListArticles::signalSendArticlesList,this,&ArticlePage::slotGetArticlesList,Qt::DirectConnection);
    // The end of the flow of data sampling
    connect(lsArticles,&ListArticles::finish,thread,&QThread::quit);
    // Remove the object in the stream
    connect(lsArticles,&ListArticles::finish,lsArticles,&ListArticles::deleteLater);
    // Perform actions on the main thread after the completion of the child
    connect(lsArticles,&ListArticles::finish,this,&ArticlePage::slotFinishArticlesList);
    // We say goodbye to the child thread
    connect(thread,&QThread::finished,thread,&QThread::deleteLater);

    // We start a flow
    thread->start();
}

Data exchange slots with stream

void ArticlePage::slotGetArticlesList(QVector<Articles> ls)
{
    //Get the vector with the results from the stream
    goods = ls;
}

void ArticlePage::slotStartArticlesList()
{
    qInfo(logInfo()) << "Began to receive a list of goods" << QTime::currentTime().toString("hh:mm:ss.zzz");

}

void ArticlePage::slotFinishArticlesList()
{
    // based on the received vector with data, create a data model of the VectorModel type and output it in QTableView

    qInfo(logInfo()) << "Finished receiving a list of goods" << QTime::currentTime().toString("hh:mm:ss.zzz");
    ui->frameProgress->hide();
    ui->groupBoxAdd->show();

    modelArticles = new VektorModel(goods);

    ui->tableView->setModel(modelArticles);
    ui->tableView->verticalHeader()->hide();
    ui->tableView->setAlternatingRowColors(true);
    ui->tableView->resizeColumnsToContents();
    // Minimum row height in QTableView
    ui->tableView->verticalHeader()->setDefaultSectionSize(ui->tableView->verticalHeader()->minimumSectionSize());

}

VektorModel

The class derived from QAbstractTableModel creates a model for displaying request data.

vektormodel.h

#ifndef VEKTORMODEL_H
#define VEKTORMODEL_H

#include "articles.h"
#include <QObject>
#include <QAbstractTableModel>
#include <QVariant>



class VektorModel : public QAbstractTableModel
{
    Q_OBJECT
    QVector<Articles>  ar;

public:
    VektorModel(const QVector<Articles> vek);
    QVariant data(const QModelIndex &index, int role) const;
    QVariant headerData( int section, Qt::Orientation orientation, int role ) const;
    int rowCount(const QModelIndex &parent) const;
    int columnCount(const QModelIndex &parent) const;
};

#endif // VEKTORMODEL_H

vectormodel.cpp

#include "vektormodel.h"
#include "articles.h"

VektorModel::VektorModel(const QVector<Articles> vek)
{
    ar = vek;
}
// data mapping model
QVariant VektorModel::data(const QModelIndex &index, int role) const
{
    if ( !index.isValid() ) { return QVariant(); }
        Articles a = ar[index.row()];
    switch (role) {
    case Qt::DisplayRole:
        switch (index.column()) {
        case 0: return a.getID();                           // 1-й column ID
        case 1: return a.getShortName();                    // 2-й column Name
        case 2: return a.getAmount();                       // 3-й column Amount
        case 3: return QString::number(a.getPrice(),'f',2); // 4-й column Price
        default: break;
        }
      break;

    // align the 2nd and 3rd column to the right
    case Qt::TextAlignmentRole:
        if(index.column() == 2 || index.column() == 3 )
            return Qt::AlignRight;
        break;
    default:
        break;
    }

    return QVariant();

}

QVariant VektorModel::headerData(int section, Qt::Orientation orientation, int role) const
{
    //Creating model column headers
    if( role != Qt::DisplayRole ) {
        return QVariant();
    }

    if( orientation == Qt::Vertical ) {
        return section;
    }

    switch( section ) {
    case 0:
        return tr( "Гл.Код" );
    case 1:
        return tr( "Наименование" );
    case 2:
        return tr( "Доступно" );
    case 3:
        return tr( "Цена" );
    }

    return QVariant();
}

int VektorModel::rowCount(const QModelIndex &parent) const
{
    // number of lines
    return ar.size();
}

int VektorModel::columnCount(const QModelIndex &parent) const
{
    // Number of columns
    Articles a;
    return a.getColParam();
}

The result is the following: