This blog describes how you can display data directly from your Mysql table to a TableView in your application.

QSqlQueryModel lets you write your own query in the SQL which can be then used as a model in ListView, GridView, TableView, etc. This gives us a lot of convenience in handling the data in the view.

View Source Code in Github

mysqlmodel.h

#ifndef MYSQLMODEL_H
#define MYSQLMODEL_H

#include <QObject>
#include <QSqlQueryModel>
#include <QSqlRecord>
#include <QSqlField>

class MysqlModel : public QSqlQueryModel
{
    Q_OBJECT
public:
    explicit MysqlModel(QObject *parent = nullptr);

    void setQuery(const QString &query, const QSqlDatabase &db = QSqlDatabase());
    void setQuery(const QSqlQuery &query);
    QVariant data(const QModelIndex &index, int role) const;
    QHash<int, QByteArray> roleNames() const;

    Q_INVOKABLE void callSql(QString queryString);

private :
    void generateRoleNames();
    QHash<int, QByteArray> m_roleNames;


signals:

};

#endif // MYSQLMODEL_H

mysqlmodel.cpp

#include "mysqlmodel.h"

MysqlModel::MysqlModel(QObject *parent) : QSqlQueryModel(parent)
{

}

void MysqlModel::setQuery(const QString &query, const QSqlDatabase &db)
{
    QSqlQueryModel::setQuery(query, db);
    generateRoleNames();
}

void MysqlModel::setQuery(const QSqlQuery &query)
{
    QSqlQueryModel::setQuery(query);
    generateRoleNames();
}

QVariant MysqlModel::data(const QModelIndex &index, int role) const
{
    QVariant value;

    if(role < Qt::UserRole) {
        value = QSqlQueryModel::data(index, role);
    }
    else {
        int columnIdx = role - Qt::UserRole - 1;
        QModelIndex modelIndex = this->index(index.row(), columnIdx);
        value = QSqlQueryModel::data(modelIndex, Qt::DisplayRole);
    }
    return value;
}

QHash<int, QByteArray> MysqlModel::roleNames() const
{
    QVariant value;

    // Important that you set this
    // Else display will not work in QML

    return {{Qt::DisplayRole, "display"}};
}

void MysqlModel::callSql(QString queryString)
{
    this->setQuery(queryString);
}

void MysqlModel::generateRoleNames()
{
    m_roleNames.clear();
    for( int i = 0; i < record().count(); i ++) {
        m_roleNames.insert(Qt::UserRole + i + 1, record().fieldName(i).toUtf8());
    }
}

main.cpp

#include <QGuiApplication>
#include <QQmlApplicationEngine>
#include <QQmlContext>
#include <QDebug>

#include "mysqlmodel.h"

int main(int argc, char *argv[])
{
    QCoreApplication::setAttribute(Qt::AA_EnableHighDpiScaling);

    QGuiApplication app(argc, argv);
    QQmlApplicationEngine engine;


    // Mysql connection
    // Change the credentials accrodingly

    if(QSqlDatabase::isDriverAvailable("QMYSQL")){

        QSqlDatabase dbMysql = QSqlDatabase::addDatabase("QMYSQL");
        dbMysql.setHostName("SERVER");
        dbMysql.setPort(3306);
        dbMysql.setDatabaseName("DATABASE");
        dbMysql.setUserName("DB_USER");
        dbMysql.setPassword("DB_PASSWORD");


        if(!dbMysql.open()){
            qDebug() << "Connection to mysql failed";

        }
    }

    // Initiate mysql instance
    // And run a default query

    MysqlModel mysqlModel;
    mysqlModel.callSql("SELECT * FROM users");

    // Set the model for QML

    engine.rootContext()->setContextProperty("MysqlModel", &mysqlModel);

    const QUrl url(QStringLiteral("qrc:/main.qml"));
    QObject::connect(&engine, &QQmlApplicationEngine::objectCreated,
                     &app, [url](QObject *obj, const QUrl &objUrl) {
        if (!obj && url == objUrl)
            QCoreApplication::exit(-1);
    }, Qt::QueuedConnection);
    engine.load(url);

    return app.exec();
}

main.qml

import QtQuick 2.15
import QtQuick.Window 2.15
import QtQuick.Controls 2.4

Window {
    visible: true
    width: 640
    height: 480
    title: qsTr("QSqlQueryModel - TableView")

    // Uncomment this, if you want to call query from qml
    // If you uncomment this, remember to comment out the followin line in main.cpp
    // mysqlModel.callSql("SELECT * FROM users");

    //    Component.onCompleted: {
    //        MysqlModel.callSql("SELECT * FROM users")
    //    }

    TableView {
        id: tableView

        columnWidthProvider: function (column) { return 100; }
        rowHeightProvider: function (column) { return 60; }
        anchors.fill: parent

        ScrollBar.horizontal: ScrollBar{}
        ScrollBar.vertical: ScrollBar{}
        clip: true

        model: MysqlModel

        // Table Body

        delegate: Rectangle {
            Text {
                text: display // This is set in mysqlmodel.cpp roleNames()
                anchors.fill: parent
                anchors.margins: 10
                color: 'black'
                font.pixelSize: 15
                verticalAlignment: Text.AlignVCenter
            }
        }


        // Table Header

        Row {
            id: columnsHeader
            y: tableView.contentY
            z: 2
            Repeater {
                model: tableView.columns > 0 ? tableView.columns : 1
                Label {
                    width: tableView.columnWidthProvider(modelData)
                    height: 35
                    text: MysqlModel.headerData(modelData, Qt.Horizontal)
                    font.pixelSize: 15
                    padding: 10
                    verticalAlignment: Text.AlignVCenter

                    background: Rectangle { color: "#ccc" }
                }
            }
        }


        ScrollIndicator.horizontal: ScrollIndicator { }
        ScrollIndicator.vertical: ScrollIndicator { }
    }
}