Qt SQL Classes

Qt SQL Classes

In this topic, you will learn about the Qt SQL classes that allow you to interact with databases in your applications. Understanding these classes is important because they provide a way to manage and manipulate data efficiently. You will be able to create, read, update, and delete records in a database using these classes.

Introduction to Qt SQL Classes

Qt provides a set of classes that simplify the process of interacting with databases. The main classes in the Qt SQL module include:

• QSqlDatabase: Represents a database connection.
• QSqlQuery: Used to execute SQL queries.
• QSqlTableModel: Provides a model for accessing data in a database table.
• QSqlRecord: Represents a single record in a database table.

These classes work together to provide a complete solution for database management in your applications.

Creating a Database Connection

To work with a database, you first need to create a connection. The following example shows how to connect to a SQLite database:

QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");

db.setDatabaseName("mydatabase.db");

if (!db.open()) {
    qDebug() << "Error: Unable to open the database!";
}

In this example, we create a connection to a SQLite database called mydatabase.db. If the connection fails, we print an error message.

Executing SQL Queries

Once you have established a connection, you can execute SQL queries using the QSqlQuery class. Here is an example of how to execute a SELECT query:

QSqlQuery query;
query.exec("SELECT * FROM mytable");

while (query.next()) {
    QString name = query.value(0).toString();
    qDebug() << "Name:" << name;
}

In this example, we execute a SELECT query to retrieve all records from mytable. We then loop through the results using query.next() and print each name.

Inserting Data into the Database

You can also insert data into the database using the QSqlQuery class. Here is an example:

QSqlQuery query;
query.prepare("INSERT INTO mytable (name) VALUES (:name)");
query.bindValue(":name", "John Doe");

if (!query.exec()) {
    qDebug() << "Error: Unable to insert data!";
}

In this example, we prepare an INSERT statement and bind a value to the placeholder :name. We then execute the query to insert the data.

Updating and Deleting Records

Updating and deleting records can be done using similar methods. Here is an example of updating a record:

QSqlQuery query;
query.prepare("UPDATE mytable SET name = :name WHERE id = :id");
query.bindValue(":name", "Jane Doe");
query.bindValue(":id", 1);

if (!query.exec()) {
    qDebug() << "Error: Unable to update data!";
}

And here is an example of deleting a record:

QSqlQuery query;
query.prepare("DELETE FROM mytable WHERE id = :id");
query.bindValue(":id", 1);

if (!query.exec()) {
    qDebug() << "Error: Unable to delete data!";
}

Using QSqlTableModel

The QSqlTableModel class provides a way to work with data in a database table as a model. This allows you to easily display and manipulate data in views. Here is an example of how to use QSqlTableModel:

QSqlTableModel model;
model.setTable("mytable");
model.select();

for (int row = 0; row < model.rowCount(); ++row) {
    QString name = model.record(row).value("name").toString();
    qDebug() << "Name:" << name;
}

In this example, we create a QSqlTableModel and set it to the mytable table. We then call select() to load the data and loop through the rows to print the names.

Summary

• Qt SQL classes provide a way to manage databases in applications.
• Use QSqlDatabase to create a database connection.
• Execute SQL queries using QSqlQuery.
• Use QSqlTableModel to represent and manipulate data in tables.

Check your understanding

1. What is the purpose of the QSqlDatabase class?
2. How do you execute a SELECT query using QSqlQuery?
3. What method do you use to insert data into a database?
4. Explain how QSqlTableModel simplifies working with database tables.