SQLite: 3.Qt and SQLite

I mentioned before that SQLite is a C library. To use this library in C++ applications, you can use many wrappers. But since I'm going in the direction of Qt, I want to use the QtSql module. QtSql module uses some drivers to communicate with databases like SQLite, MySQL, Oracle ...

Or add

//in the *.pro file
QT += sql

and include

//in any class
#include <QtSql>

The process is very easy: Create a database object. Open it. Then execute some queries.

For this entry, I created a project to explain the basic features of QtSql. I will try to explain some parts of it, and you can download it and try to understand the complete code.

Viewing Table Contents:

To view table contents, you need a QSqlTableModel object. QSqlTableModel is a high-level interface for reading and writing database records from a single table.

A simple way to use it is like this:

After creating a database object, I create a QSqlTableModel object and connect it to my database. Then I name the table I want this object to represent.

QSqlDatabase *database = new QSqlDatabase();


//set database driver to QSQLITE
*database = QSqlDatabase::addDatabase("QSQLITE");


database->setDatabaseName("./phonebook.db");

QSqlTableModel *all_model = new QSqlTableModel(this, *database);


all_model->setTable("Contacts");

all_model->select();

Then I can use this model to preview the contents of this table in some 'item views' available in the GUI designers (List View, Tree View, Table View, Column View)

ui->contacts_tableView->setModel(all_model);

QSqlTableModel has some other methods like: sorting, filtering, editing a record...etc. For example, searching a database and viewing results can be implemented by filtering the current model:

//search database for a certain name
search_model->setFilter("Name = \'"+ui->search_lineEdit->text()+"\'");

Queries:

QSqlQuery is a class that provides a means of executing and manipulating SQL statements. Its simplest statement looks like this:

QSqlQuery query("delete from Contacts where Mobile = 1234");
query.exec();

The exec() method returns 'true' if query is successful, and 'false' if it fails.

To make a query containing some variables, you can do this:

QSqlQuery query ("insert into Contacts (Name, Mobile, City) values (:name, :mobile, :city)");
    query.bindValue(0, ui->add_name_lineEdit->text());
    query.bindValue(1, ui->add_mobile_lineEdit->text());
    query.bindValue(2, ui->add_city_lineEdit->text());

    if(!query.exec())
    {
        QMessageBox::warning(0,"Error", "Please check your entered data.");
        return;
    }

where 0, 1, and 2 are the positions of the variables :name, :mobile, and :city. You can also replace the positions by the variable names:

    query.bindValue(":name", ui->add_name_lineEdit->text());
    query.bindValue(":mobile", ui->add_mobile_lineEdit->text());
    query.bindValue(":address", ui->add_city_lineEdit->text();

Note: sometimes i needed to change the query depending on a certain condition. One way to do this is using the prepare() method:

    QSqlQuery query;

    if(ui->remove_name_radioButton->isChecked())
    {
         query.prepare("delete from Contacts where Name = \'" + ui->remove_lineEdit->text()+"\'");
    }
    else
    {
        query.prepare("delete from Contacts where Mobile = \'" + ui->remove_lineEdit->text()+"\'");
    }

    if(!query.exec())
    {
        QMessageBox::warning(0,"Error", "Please check your entered data.");
        return;
    }

The Complete Example:

Now, here is the complete example for Qt with SQLite. It is a phonebook application that views all the contacts (using QSqlTableModel and a Table View), searches for a specific contact (using the filter() method of QSqlTableModel), and adds or removes a contact (using the queries).

Test it and read the code carefully. You can ask me anything you want.

Download

Blog Example by Mahmoud Adly Ezzat is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.