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
The process is very easy: Create a database object. Open it. Then execute some queries.
//in the *.pro file QT += sqland 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:
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:
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.
