preface
SQLite (sql) is an open source lightweight database software. It does not need a server and can be integrated into other software. It is very suitable for embedded systems.
SQLite (QT built-in driver) can be used directly for versions above Qt5.
usage
1 Preparation
- Introducing SQL module
In Qt project file (. pro file), add SQL module:
QT += sql
- Reference header file
In the class definition that needs to use SQL, reference the relevant header file. For example:
#include <QSqlDatabase> #include <QSqlError> #include <QSqlQuery>
2 use
1. Establish database
Check connection, add database driver, set database name, database login user name and password.
QSqlDatabase database;
if (QSqlDatabase::contains("qt_sql_default_connection"))
{
database = QSqlDatabase::database("qt_sql_default_connection");
}
else
{
database = QSqlDatabase::addDatabase("QSQLITE");
database.setDatabaseName("MyDataBase.db");
database.setUserName("XingYeZhiXia");
database.setPassword("123456");
}
Explanation of the above code:
(1) In the first line, a QSqlDatabase object is created, which will be used for subsequent operations.
(2) if statement is used to check whether the specified connection exists. The connection name specified here is qt_sql_default_connection, which is the Qt default connection name. In actual use, this name can be taken arbitrarily. if it is judged that the connection already exists, the QSqlDatabase::contains() function returns true. At this point, enter the first branch, and QSqlDatabase::database() returns the connection.
(3) If the connection does not exist, enter the else branch. You need to create a connection and add a database. In the first line of the else branch, the parameter QSQLITE of addDatabase() is the driver name corresponding to SQLite and cannot be changed. It should also be noted that the second parameter of adddatabase () is omitted, and the default parameter of the second parameter is the Qt default connection name Qt mentioned above_ sql_ default_ connection. If you need to use a custom connection name (this will happen if the program needs to process multiple database files), you should add a second parameter, such as
database = QSqlDatabase::addDatabase("QSQLITE", "my_sql_connection);
At this time, if you need to judge in another place_ sql_ if (QSqlDatabase::contains("my_sql_connection")) should be used to determine whether the connection exists.
(4) In the second line of the else branch, the parameter of setDatabaseName() is the database file name. If the database does not exist, it will be automatically created during subsequent operations; If it already exists, subsequent operations will be performed on the existing database.
(5) Two lines after the else branch, set the user name and password. The user name and password can be taken freely or omitted.
2. Open the database
Open the database with open() and judge whether it is successful. Note that when checking whether the connection exists in the first step, if the connection exists, the database will be opened by default when returning the connection.
if (!database.open())
{
qDebug() << "Error: Failed to connect database." << database.lastError();
}
else
{
// do something
}
If it is opened successfully, enter the else branch. All operations on the database need to be performed in the else branch.
3. Close the database
After the database operation is completed, it is best to close it.
database.close();
4. Operation database
QSqlQuery class is required for database operation. An object must be defined before operation. The operation method is illustrated below. SQLite statements are required for operations. Several examples in this article will use several common statements. For specific information about SQLite statements, please refer to relevant SQLite materials.
Example 1: create a table
Create a table named student. The table contains three columns. The first column is id, the second column is name, and the third column is age.
QSqlQuery sql_query;
QString create_sql = "create table student (id int primary key, name varchar(30), age int)";
sql_query.prepare(create_sql);
if(!sql_query.exec())
{
qDebug() << "Error: Fail to create table." << sql_query.lastError();
}
else
{
qDebug() << "Table created!";
}
Code interpretation:
(1) The first line defines a QSqlQuery object.
(2) The second line is a QString with SQLite statements. All operations on the database are completed with SQLite statements. These instructions are saved in QSqlQuery objects in QString type through the prepare function. You can also write the instruction directly in the parameter of exec() function in the form of QString, for example:
sql_query.exec("create table student (id int primary key, name varchar(30), age int)");
Create table statement: create table < table_ name> (f1 type1, f2 type2,…);
Create table is a statement to create a table, or create table in uppercase; student is the name of the table, which can be taken arbitrarily; The format of the table is shown in parentheses. The above instructions indicate that there are three columns in the table. The name (header) of the first column is id, the data type stored in this column is int, the name of the second column is name, and the data type is a character array, with a maximum of 30 characters (the difference from char(30) is that the actual length of varchar is variable, and the length of char is always a given value), The name of the third column is age and the data type is int.
If SQL_ If query. Exec() is executed successfully, the table is created successfully.
Example 2: insert data
Insert a row of data into the table you just created.
QString insert_sql = "insert into student values (?, ?, ?)";
sql_query.prepare(insert_sql);
sql_query.addBindValue(max_id+1);
sql_query.addBindValue("Wang");
sql_query.addBindValue(25);
if(!sql_query.exec())
{
qDebug() << sql_query.lastError();
}
else
{
qDebug() << "inserted Wang!";
}
if(!sql_query.exec("INSERT INTO student VALUES(3, \"Li\", 23)"))
{
qDebug() << sql_query.lastError();
}
else
{
qDebug() << "inserted Li!";
}
Insert statement: insert into < table_ name> values (value1, value2,…);
insert into is the insert statement, student is the table name, and values() is the data to be inserted. Here, we insert two sets of data. When inserting the first group of data, addBindValue is used to replace?, in the statement?, The order of substitution is the same as that of addBindValue call. When the second set of data is inserted, the complete statement is written directly.
Example 3: update data (modify data)
QString update_sql = "update student set name = :name where id = :id";
sql_query.prepare(update_sql);
sql_query.bindValue(":name", "Qt");
sql_query.bindValue(":id", 1);
if(!sql_query.exec())
{
qDebug() << sql_query.lastError();
}
else
{
qDebug() << "updated!";
}
Statement: update < table_ name> set <f1=value1>, <f2=value2>… where <expression>;
The update (modify) statement is update...set... Where student is the table name, name is the header name (i.e. the second column),: name is the pending variable, where is used to determine which group of data, and: id is also the pending variable.
The bindValue('', '') function is used to replace the pending variable in the statement with a determined value.
Example 4: query data
(1) Query some data
QString select_sql = "select id, name from student";
if(!sql_query.exec(select_sql))
{
qDebug()<<sql_query.lastError();
}
else
{
while(sql_query.next())
{
int id = sql_query.value(0).toInt();
QString name = sql_query.value(1).toString();
qDebug()<<QString("id:%1 name:%2").arg(id).arg(name);
}
}
Statement select < F1 >, < F2 >,... From < table_ name>;
select is the query instruction< f1> And so on are the variables to be queried (i.e. header), separated by commas; from... Specifies the table.
The above statement is to query the id and name in the student table. After executing the query, use sql_query.value(int) to get the data. Similarly, value(0) represents the first data, that is, id, and value(1) represents name. Note: the return value type of the value() function is QVariant, so it needs to be converted to a specific type with functions such as toInt().
(2) Query all data
QString select_all_sql = "select * from student";
sql_query.prepare(select_all_sql);
if(!sql_query.exec())
{
qDebug()<<sql_query.lastError();
}
else
{
while(sql_query.next())
{
int id = sql_query.value(0).toInt();
QString name = sql_query.value(1).toString();
int age = sql_query.value(2).toInt();
qDebug()<<QString("id:%1 name:%2 age:%3").arg(id).arg(name).arg(age);
}
}
Statement select * from < table_ name>;
Query all data, represented by *. Use while(sql_query.next()) to traverse all rows. Also use value() to get the data.
(3) Query maximum id
QString select_max_sql = "select max(id) from student";
int max_id = 0;
sql_query.prepare(select_max_sql);
if(!sql_query.exec())
{
qDebug() << sql_query.lastError();
}
else
{
while(sql_query.next())
{
max_id = sql_query.value(0).toInt();
qDebug() << QString("max id:%1").arg(max_id);
}
}
This is to use max to get the maximum value in the statement.
Example 5: delete and empty
(1) Delete a piece of data
QString delete_sql = "delete from student where id = ?";
sql_query.prepare(delete_sql);
sql_query.addBindValue(0);
if(!sql_query.exec())
{
qDebug()<<sql_query.lastError();
}
else
{
qDebug()<<"deleted!";
}
Statement delete from < table_ name> where <f1> = <value>
Delete is used to delete entries, and where is used to give qualified conditions. For example, here is to delete the entry with id = 0.
(2) Empty table (delete all)
QString clear_sql = "delete from student";
sql_query.prepare(clear_sql);
if(!sql_query.exec())
{
qDebug() << sql_query.lastError();
}
else
{
qDebug() << "table cleared";
}
If there is no restriction given by where, all contents will be deleted.