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
and include
The process is very easy: Create a database object. Open it. Then execute some queries.
1 2 | //in the *.pro fileQT += sql |
1 2 | //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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | 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)
1 | 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:
1 2 | //search database for a certain namesearch_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:
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:
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:
Note: sometimes i needed to change the query depending on a certain condition. One way to do this is using the prepare() method:
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
QSqlQuery is a class that provides a means of executing and manipulating SQL statements. Its simplest statement looks like this:
1 2 | 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:
1 2 3 4 5 6 7 8 9 10 | 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:
1 2 3 | 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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | 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.
