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:

We recommend hosting TIMEWEB
We recommend hosting TIMEWEB
Stable hosting, on which the social network EVILEG is located. For projects on Django we recommend VDS hosting.

В процессе отладки на реальных базах данных столкнулся с проблемой что перенос данных из объекта QSqlQuery в вектор выполняется достаточно медленно.

    // Цикл получения записей, и добавления их в вектор
    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);
    }

Проблема решилась замена названий в value на номера.

    while (q.next()){
        ar.setID(q.value(0).toInt());
        ar.setShortName(q.value(1).toString().trimmed());
        ar.setAmount(q.value(2).toFloat());
        ar.setPrice(q.value(3).toFloat());
        goods << ar;
        i++;
    }



Comments

Only authorized users can post comments.
Please, Log in or Sign up
Donate

Hello, Dear Users of EVILEG!!!

If the site helped you, then support the development of the site financially, please.

You can do it by following ways:

Thank you, Evgenii Legotckoi

P
Nov. 19, 2019, 2:23 p.m.
Pan

C++ - Test 001. The first program and data types

  • Result:100points,
  • Rating points10
P
Nov. 19, 2019, 2:20 p.m.
Pan

C++ - Test 001. The first program and data types

  • Result:66points,
  • Rating points-1
O
Nov. 18, 2019, 3:54 p.m.
Oksana

C++ - Test 005. Structures and Classes

  • Result:66points,
  • Rating points-1
Last comments
P
Nov. 19, 2019, 2:10 p.m.
Pan

Здравствуйте. Спасибо за статью. Сделал перевод программы. Все работает. Только я делал не динамический, но это пока и не надо. Но с одной проблемой все же столкнулся. В прог…
v
Nov. 19, 2019, 11:26 a.m.
vintorez10

Разобрался!!! Надо было не архив качать а exe файл и его уже распаковывать в нужную папку)))
v
Nov. 19, 2019, 10:19 a.m.
vintorez10

Ребята а что если папки "Source" в архиве opencv нет? Скачал три варианта библиотеки ни в одном нет этой папки.
c
Nov. 18, 2019, 6:27 a.m.
cyberaxe77

Моих знаний пока явно недостаточно, чтобы писать статьи. Так...небольшие заметки "на полях"))).
Nov. 18, 2019, 6:10 a.m.
Evgenij Legotskoj

Пока что на сайте нет активных пользователей PyQt5, кто бы мог писать статьи по PyQt5, к сожалению. Лично я только для статей пользуюсь этой библиотекой. Но можете стать одним из первых ;) Любой…
Now discuss on the forum
Nov. 19, 2019, 8:10 a.m.
Intruder

Михаил, добрый день. Без разницы в дизайнере создается форма или не в дизайнере. Как вы добавляете в QTabWidget? Нашел видеоурок и по нему все сделал. Все получилось. QtabWidg…
Nov. 19, 2019, 7:22 a.m.
Evgenij Legotskoj

Ну тогда создайте, пожалуйста, потом новую тему на форуме с тем кодом, а то мы с вами в оффтоп ушли, здесь немного не о том было обсуждение.
Nov. 19, 2019, 6:18 a.m.
BlinCT

Спасибо за идею. Сейчас попробою.
Nov. 19, 2019, 2:36 a.m.
BlinCT

Всем привет. Если кто дебажил qml часть подскажиет пожалуйста, какие настрйоки требуются в Qt Creator? Я собирал 5.12.5 из исходников, в Настройкс в debugger у меня выставлен путь к со…
EVILEG
About
Services
© EVILEG 2015-2019
Recommend hosting TIMEWEB