Privacy policyContactsAbout siteOpinionsGitHubDonate
© EVILEG 2015-2018
Recommend hosting
TIMEWEB

Qt/C++ - Lesson 005. QSqlRelationalTableModel - The work with relational tables

QSqlRelationalTableModel, QSqlRelationalTableModel example, qt, qt таблицы, sql, sqlite

In Qt to represent table fields, which involve foreign keys to other tables database, QSqlRelationalTableModel can be used, which is a more advanced version QSqlTableModel class, which was considered in the previous article .

This class allows you to set relationships between tables and substitute values in the representation of the table formed by the values of the connected tables.

This article discusses the option of two tables. One table contains information about the devices (host name and IP address), and the second table ID of the device, which in the second table to be substituted host name and IP address of the device, respectively.

Project Structure for QSqlRelationalTableModel

Структура проекта

The project for this tutorial is a modified version of the preceding Article and remains unchanged.

mainwindow.ui

When creating molds are added to a table that will be specified device. The names of the following tables:

  • tableView
  • tableViewDevice

DataBase.pro

This file remains the same as in the previous article.

main.cpp

The file used in the project, being created by default.

#include "mainwindow.h"
#include <QApplication>

int main(int argc, char *argv[])
{
    QApplication a(argc, argv);
    MainWindow w;
    w.show();

    return a.exec();
}

mainwindow.h

Instead QSqlTableModel library we connect QSqlRelationalTableModel library. Also is added an additional method, which is responsible for initializing the devices table view model.

#ifndef MAINWINDOW_H
#define MAINWINDOW_H

#include <QMainWindow>
#include <QSqlRelationalTableModel>
#include <QSqlRelationalDelegate>
#include <QSqlRelation>

/* Connect the header file for work with the database */
#include "database.h"

namespace Ui {
class MainWindow;
}

class MainWindow : public QMainWindow
{
    Q_OBJECT

public:
    explicit MainWindow(QWidget *parent = 0);
    ~MainWindow();

private:
    Ui::MainWindow  *ui;
    /* The project uses objects to interact with the database and model representation database table
     * */
    DataBase                    *db;
    QSqlRelationalTableModel    *modelMain;
    QSqlRelationalTableModel    *modelDevice;

private:
    /* Also present are two methods that form the model and appearance TableView
     * */
    void setupMainModel(const QString &tableName, const QStringList &headers);
    void setupDeviceModel(const QString &tableName, const QStringList &headers);
    void createUI();
};

#endif // MAINWINDOW_H

mainwindow.cpp

Basically, the original file initialize the data model representation and appearance of the main window. In the method for initializing the main table will be installed communication for which data from the devices of the table will be selected.

When you run the program in a database introduced three devices, then 9 records with random assignment device ID from 1 to 3. The first time you start the program creates the database and devices are introduced into an empty table with the device ID from one to three. The second run will be the ID other than 1, 2 or 3 but as this case study, we restrict ourselves to the first one you run the application.

#include "mainwindow.h"
#include "ui_mainwindow.h"

MainWindow::MainWindow(QWidget *parent) :
    QMainWindow(parent),
    ui(new Ui::MainWindow)
{
    ui->setupUi(this);

    /* The first step is to create an object for the database and initialize the database connection
     * */
    db = new DataBase();
    db->connectToDataBase();

    /* After that produce content database tables 
     * this content will displayed in the tableView and tableViewDevice
     * */
    for(int i = 1; i < 4; i++){
        QVariantList data;
        data.append("Device " + QString::number(i));
        data.append("192.168.0." + QString::number(i));
        db->inserIntoDeviceTable(data);
    }

    for(int i = 0; i < 10; i++){
        QVariantList data;
        QString random = QString::number(qrand() % ((4 + 1) - 1) + 1);
        data.append(QDate::currentDate());
        data.append(QTime::currentTime());
        data.append(random);
        data.append(random);
        db->inserIntoMainTable(data);
    }

    /* Initialize the model to represent the data indicating the names of the columns
     * */
    this->setupMainModel(TABLE,
                     QStringList() << trUtf8("id")
                                   << trUtf8("Дата")
                                   << trUtf8("Время")
                                   << trUtf8("Имя хоста")
                                   << trUtf8("IP адрес")
               );

    this->setupDeviceModel(DEVICE,
                     QStringList() << trUtf8("id")
                                   << trUtf8("Имя хоста")
                                   << trUtf8("IP адрес")
               );
    /* Initialize the appearance of a table with data
     * */
    this->createUI();
}

MainWindow::~MainWindow()
{
    delete ui;
}

void MainWindow::setupMainModel(const QString &tableName, const QStringList &headers)
{
    /* Initializes the data model representation with the installation name 
     * in the database table, on which will be accessed in the table
     * */
    modelMain = new QSqlRelationalTableModel(this);
    modelMain->setTable(tableName);
    /* Set the connection device table, which will be made data substitution. 
     * The method setRelation specified column number in which substitution is made, 
     * as well as through QSqlRelation class name of the table, 
     * the option for which the sample line and column 
     * from which the data will be taken will be made
     * */
    modelMain->setRelation(3, QSqlRelation(DEVICE, "id", DEVICE_HOSTNAME));
    modelMain->setRelation(4, QSqlRelation(DEVICE, "id", DEVICE_IP));

    /* Set the columns names in a table with sorted data
     * */
    for(int i = 0, j = 0; i < modelMain->columnCount(); i++, j++){
        modelMain->setHeaderData(i,Qt::Horizontal,headers[j]);
    }
    // Set Sort Ascending column zero data
    modelMain->setSort(0,Qt::AscendingOrder);
    modelMain->select(); // Делаем выборку данных из таблицы
}

void MainWindow::setupDeviceModel(const QString &tableName, const QStringList &headers)
{
    /* Initializes the data model representation 
     * with the installation name in the database table, 
     * on which will be accessed in the table
     * */
    modelDevice = new QSqlRelationalTableModel(this);
    modelDevice->setTable(tableName);

    for(int i = 0, j = 0; i < modelDevice->columnCount(); i++, j++){
        modelDevice->setHeaderData(i,Qt::Horizontal,headers[j]);
    }

    modelDevice->setSort(0,Qt::AscendingOrder);
    modelDevice->select(); 
}

void MainWindow::createUI()
{
    ui->tableView->setModel(modelMain);     // We set the model on the TableView
    ui->tableView->setColumnHidden(0, true);    // Hide the column id Records
    ui->tableView->setSelectionBehavior(QAbstractItemView::SelectRows);
    ui->tableView->setSelectionMode(QAbstractItemView::SingleSelection);
    ui->tableView->resizeColumnsToContents();
    ui->tableView->setItemDelegate(new QSqlRelationalDelegate(ui->tableView));
    ui->tableView->setEditTriggers(QAbstractItemView::NoEditTriggers);
    ui->tableView->horizontalHeader()->setStretchLastSection(true);

    modelMain->select(); // Fetches the data from the table

    ui->tableViewDevice->setModel(modelDevice);     
    ui->tableViewDevice->setColumnHidden(0, true);    
    ui->tableViewDevice->setSelectionBehavior(QAbstractItemView::SelectRows);
    ui->tableViewDevice->setSelectionMode(QAbstractItemView::SingleSelection);
    ui->tableViewDevice->resizeColumnsToContents();
    ui->tableViewDevice->setItemDelegate(new QSqlRelationalDelegate(ui->tableViewDevice));
    ui->tableViewDevice->setEditTriggers(QAbstractItemView::NoEditTriggers);
    ui->tableViewDevice->horizontalHeader()->setStretchLastSection(true);

    modelDevice->select();
}

database.h

Compared with the example about QSqlTableModel in this example adds two new methods related to the devices table, namely the creation table with createDeviceTable() and insert records into the table of devices with insertIntoDeviceTable() . As well as adding new devices to the directive define tables and processed guidelines for the main table.

#ifndef DATABASE_H
#define DATABASE_H

#include <QObject>
#include <QSql>
#include <QSqlQuery>
#include <QSqlError>
#include <QSqlDatabase>
#include <QFile>
#include <QDate>
#include <QDebug>

#define DATABASE_HOSTNAME   "ExampleDataBase"
#define DATABASE_NAME       "DataBase.db"

#define TABLE                   "MainTable"
#define TABLE_DATE              "Date"
#define TABLE_TIME              "Time"
#define TABLE_IP                "IP"
#define TABLE_HOSTNAME          "Hostname"

#define DEVICE                  "DeviceTable"
#define DEVICE_IP               "IP"
#define DEVICE_HOSTNAME         "Hostname"

class DataBase : public QObject
{
    Q_OBJECT
public:
    explicit DataBase(QObject *parent = 0);
    ~DataBase();
    /* Methods to work directly with the class. 
     * Connect to the database and insert records into the table
     * */
    void connectToDataBase();
    bool inserIntoMainTable(const QVariantList &data);
    bool inserIntoDeviceTable(const QVariantList &data);

private:
    QSqlDatabase    db;

private:
    bool openDataBase();
    bool restoreDataBase();
    void closeDataBase();
    bool createMainTable();
    bool createDeviceTable();
};

#endif // DATABASE_H

database.cpp

Only part of the methods as well as adding two new methods compared with the previous article have undergone changes in this file.

#include "database.h"

DataBase::DataBase(QObject *parent) : QObject(parent)
{

}

DataBase::~DataBase()
{

}

void DataBase::connectToDataBase()
{
    /* см. статью про QSqlTableModel */
}

bool DataBase::restoreDataBase()
{
    if(this->openDataBase()){
        if((!this->createMainTable()) || (!this->createDeviceTable())){
            return false;
        } else {
            return true;
        }
    } else {
        qDebug() << "Не удалось восстановить базу данных";
        return false;
    }
    return false;
}

bool DataBase::openDataBase()
{
    /* cm. article QSqlTableModel */
}

void DataBase::closeDataBase()
{
    db.close();
}

bool DataBase::createMainTable()
{
    QSqlQuery query;
    if(!query.exec( "CREATE TABLE " TABLE " ("
                            "id INTEGER PRIMARY KEY AUTOINCREMENT, "
                            TABLE_DATE      " DATE            NOT NULL,"
                            TABLE_TIME      " TIME            NOT NULL,"
                            TABLE_HOSTNAME  " INTEGER         NOT NULL,"
                            TABLE_IP        " INTEGER         NOT NULL"
                        " )"
                    )){
        qDebug() << "DataBase: error of create " << TABLE;
        qDebug() << query.lastError().text();
        return false;
    } else {
        return true;
    }
    return false;
}

bool DataBase::createDeviceTable()
{
    QSqlQuery query;
    if(!query.exec( "CREATE TABLE " DEVICE " ("
                            "id INTEGER PRIMARY KEY AUTOINCREMENT, "
                            DEVICE_HOSTNAME  " VARCHAR(255)    NOT NULL,"
                            DEVICE_IP        " VARCHAR(16)     NOT NULL"
                        " )"
                    )){
        qDebug() << "DataBase: error of create " << DEVICE;
        qDebug() << query.lastError().text();
        return false;
    } else {
        return true;
    }
    return false;
}

bool DataBase::inserIntoMainTable(const QVariantList &data)
{

    QSqlQuery query;
    query.prepare("INSERT INTO " TABLE " ( " TABLE_DATE ", "
                                             TABLE_TIME ", "
                                             TABLE_HOSTNAME ", "
                                             TABLE_IP " ) "
                  "VALUES (:Date, :Time, :Hostname, :IP )");
    query.bindValue(":Date",        data[0].toDate());
    query.bindValue(":Time",        data[1].toTime());
    query.bindValue(":Hostname",    data[2].toInt());
    query.bindValue(":IP",          data[3].toInt());

    if(!query.exec()){
        qDebug() << "error insert into " << TABLE;
        qDebug() << query.lastError().text();
        return false;
    } else {
        return true;
    }
    return false;
}

bool DataBase::inserIntoDeviceTable(const QVariantList &data)
{

    QSqlQuery query;
    query.prepare("INSERT INTO " DEVICE " ( " DEVICE_HOSTNAME ", "
                                              DEVICE_IP " ) "
                  "VALUES (:Hostname, :IP )");
    query.bindValue(":Hostname",    data[0].toString());
    query.bindValue(":IP",          data[1].toString());
    // После чего выполняется запросом методом exec()
    if(!query.exec()){
        qDebug() << "error insert into " << DEVICE;
        qDebug() << query.lastError().text();
        return false;
    } else {
        return true;
    }
    return false;
}

Result

As a result of the work done and application will contains two tables. The basic table from which that will contain the link to the recording device table and a second table that will contain entries for devices, respectively.

QSqlRelationTableModel Application

Небольшое дополнение, столкнулся вот. При вызове setFilter к модели созданной на основе QSqlRelationalTableModel нужно указывать полный фильтр

myModel->setFilter("tableName.fielsName>100");

Ну да. Приходится уточнять конкретную таблицу, к которой применяется фильтр.

Хотя для QSqlTableModel можно и без такой конкретизации обойтись.

z

Вставки в базу выругались "" Parameter count mismatch""

Показывайте код, что там писали в коде вставки и т.д. Ну и какая структура таблиц базы данных.

z

https://www.dropbox.com/sh/vhxcx0iyq0j4578/AACwgWPnZwNqGBndKESiXfFqa?dl=0

В классе DataBase указывается путь к базе данных. В данном случае C:/example/ и т.д. Так вот, у вас есть каталог example ?

z

Есть, там была база. Я ее удалил, теперь DeviceTable отрабатывает. На остальное матерится error insert into MainTable " Parameter count mismatch"

Изменяли колонки? Названия колонок? Обычно такая ошибка возникает в том случае, если в запросе на добавление не хватает информации, или есть излишняя информация. Не совпадает количество колонок и т.д.

Comments

Only authorized users can post comments.
Please, Log in or Sign up
P
Feb. 18, 2019, 3:39 p.m.
Poyar

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

  • Result:73points,
  • Rating points1
НБ
Feb. 15, 2019, 1:03 p.m.
Николай Булахтин

C++ - Test 002. Constants

  • Result:25points,
  • Rating points-10
Last comments
V
Feb. 14, 2019, 6:41 p.m.
Vlad15007

Спасибо огромное! Заработало!
А
Feb. 12, 2019, 9:26 a.m.
Александр90

Сам разборался, спасибо.
А
Feb. 12, 2019, 8:19 a.m.
Александр90

День добрый! Можешь выложить форму mainwindow.ui от урока? Заранее спасибо
Feb. 11, 2019, 10:51 a.m.
Евгений Легоцкой

Нет, у меня проблема с жёстким диском случилась, занимался восстановлением ПК, ещё пару вечеров придётся этим заниматься, увы.
Now discuss on the forum
Feb. 17, 2019, 5:28 p.m.
Евгений Легоцкой

Добрый день. Очень извиняюсь за долгий ответ Первое, что нашёл, так это необходимость перерисовать чекбокс. void CheckBoxDelegate::paint(QPainter *painter, const QStyleOptionViewItem ...
Feb. 15, 2019, 3:36 p.m.
Евгений Легоцкой

Ну я тут нашёл одно решение, но сам его не проверял. Вам нужно помещать фамилии скорее всего в ячейки заголовка, и потом просто перерисовывать их QHeaderView * header = m_ui->tableWidget...
Feb. 15, 2019, 7:53 a.m.
Евгений Легоцкой

Добрый день! Не работал с remoteobjects, поэтому глянул документацию, чтобы рассмотреть, что это за зверь. После просмотра документации сложилось стойкой впечатление, что это вполне возм...
m
Feb. 14, 2019, 6:28 p.m.
mr_roman

Нашел решение на Java. Удалось интегрировать в проект сервиса на Qt, теперь из Qt запускаю Java-код акселерометра.
Join us in social networks

For registered users on the site there is a minimum amount of advertising