Qt and Sqlite


Create new Qt Widgets application. In the .pro file add 'sql' to the QT += line:

QT += core gui sql



In mainwindow.h, #include <QtSql>. Mainwindow.h looks like the following:




#ifndef MAINWINDOW_H
#define MAINWINDOW_H

#include <QMainWindow>
#include <QtSql>

QT_BEGIN_NAMESPACE
namespace Ui { class MainWindow; }
QT_END_NAMESPACE

class MainWindow : public QMainWindow
{
    Q_OBJECT

public:
    MainWindow(QWidget *parent = nullptr);
    ~MainWindow();


private slots:

    void on_pushButton_clicked();

private:
    Ui::MainWindow *ui;
    QSqlDatabase db;
    void createDatabase();
    int dbId;
};
#endif // MAINWINDOW_H


Note the QsqlDatabase db member and the dbId member. Next, design the UI as follows in the Qt designer:



Implement the functionality in MainWindow.cpp:




#include "mainwindow.h"
#include "ui_mainwindow.h"
#include <QDebug>

void MainWindow::createDatabase()
{
    dbId = 0; //this is for the ID column in the DB.
    db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName(QCoreApplication::applicationDirPath()+"/temp.sqlite");

    if(!db.open())
    {
        qDebug() << "Problem opening database";
    }
    QSqlQuery qry;

    //Remaining code in function first checks to see if the table already exists, if not creates it.

    QString checkQuery = "SELECT name FROM sqlite_master WHERE type='table' AND name='employee';";
    int tableCount = 0;
    if(!qry.exec(checkQuery))
    {
        qDebug() << "Could not check for table employee";
    }
    else
    {
       while(qry.next())
       {
           QString name = qry.value(0).toString();
           qDebug() << "Table name is " << name;
           tableCount++;
       }
    }

    if(tableCount == 0)
    {
        QString query("CREATE TABLE employee ("
              "ID integer,"
              "Name VARCHAR(20),"
              "Gender VARCHAR(10),"
              "Age integer,"
              "Occupation VARCHAR(20));");

        if(!qry.exec(query))
        {
          qDebug() << "Could not create table employee";
        }
    }

    db.close();
}

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

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


//Below slot is triggered when the Submit button from the UI is clicked

void MainWindow::on_pushButton_clicked()
{
   dbId++;
   if(!db.open())
   {
       qDebug() << "Could not open database employee";
   }
   QString name = ui->nameLineEdit->text();
   QString gender = ui->genderLineEdit->text();
   int age = ui->ageLineEdit->text().toInt();
   QString occupation = ui->occupationLineEdit->text();

   QSqlQuery qry;
   qry.prepare ("INSERT INTO employee ("
                "ID,"
                "Name,"
                "Gender,"
                "Age,"
                "Occupation)"
                "VALUES (?,?,?,?,?);");

   qry.addBindValue(dbId);
   qry.addBindValue(name);
   qry.addBindValue(gender);
   qry.addBindValue(age);
   qry.addBindValue(occupation);

   if(!qry.exec())
   {
       qDebug() << "Error adding values to db";
   }
   db.close();
}
When deploying the application, the following DLLs are the dependencies:



Next, extend the application by adding a QTableView onto the designer surface and a button to get the results from the database:



Add QStandardItemModel member to mainwindow.h:




#ifndef MAINWINDOW_H
#define MAINWINDOW_H

#include <QMainWindow>
#include <QtSql>
#include <QStandardItemModel>

QT_BEGIN_NAMESPACE
namespace Ui { class MainWindow; }
QT_END_NAMESPACE

class MainWindow : public QMainWindow
{
    Q_OBJECT

public:
    MainWindow(QWidget *parent = nullptr);
    ~MainWindow();


private slots:

    void on_pushButton_clicked();

    void on_pushButton_2_clicked();

private:
    QStandardItemModel model;
    Ui::MainWindow *ui;
    QSqlDatabase db;
    void createDatabase();
    int dbId;
};
#endif // MAINWINDOW_H
Set the model for the table view in the MainWindow constructor:
ui->tableView->setModel(&model);

 Implement the getData button slot:



void MainWindow::on_pushButton_2_clicked()
{
  if(!db.open())
  {
      qDebug() << "Error opening database ";
  }
  QList<QStandardItem*> row;
  QStringList labels;
  labels << "Name" << "Gender" << "Age" << "Occupation";
  int numRows = 0;
  QString selectQuery = "SELECT Name, Gender, Age, Occupation FROM employee";
  model.clear();
  model.insertColumns(0,4);
  model.setHorizontalHeaderLabels(labels);
  QSqlQuery sqlQuery;
  if(!sqlQuery.exec(selectQuery))
  {
          qDebug() << "Could not get employee data";
  }
  else
  {
     while(sqlQuery.next())
     {
          auto name = sqlQuery.value(0).toString();
          auto gender = sqlQuery.value(1).toString();
          auto age = sqlQuery.value(2).toString();
          auto occupation = sqlQuery.value(3).toString();

          row.append(new QStandardItem(name));
          row.append(new QStandardItem(gender));
          row.append(new QStandardItem(age));
          row.append(new QStandardItem(occupation));
          model.insertRow(numRows,row);
          row.clear();
          numRows++;
     }
   }
  db.close();
}
The end result will be similar to the following:


Comments

Popular posts from this blog

QTreeView and QTableView dynamic changes

C++ Tour Part III

C++ strings and string_view