Qt Learning Road 56 Using Models to Operate Databases

兰博

Qt Learning Road 56 Using Models to Operate Databases [Copy link]

In the previous chapter, we used SQL statements to complete routine operations on the database, including the use of simple CREATE, SELECT and other statements. We also mentioned that Qt not only provides this way of using SQL statements, but also provides a more advanced processing method based on models. This model processing based on QSqlTableModel is more advanced. If you are not familiar with SQL statements and do not need many complex queries, this QSqlTableModel model can basically meet general needs. In this chapter, we will introduce the general use of QSqlTableModel and compare SQL statements to complete operations such as adding, deleting, modifying and querying the database. It is worth noting that QSqlTableModel does not necessarily have to be used in conjunction with QListView or QTableView. We can use it for general processing. , sans-serif]

Operation:

• if (connect("demo.db")) {
• QSqlTableModel model;
• model.setTable("student");
• model.setFilter("age > 20 and age < 25");
• if (model.select()) {
• for (int i = 0; i < model.rowCount(); ++i) {
• QSqlRecord record = model.record(i);
• QString name = record.value("name").toString();
• int age = record.value("age").toInt();
• qDebug() << name << : " << age;
• }
• }
• } else {
• return 1;
• }

[color=rgb(0, 204, 255) !important]复制代码

We still use the connect() function from the previous chapter. Next, we create a QSqlTableModel instance and use the setTable() function to set the table to be operated on; the setFilter() function is to add a filter, which is the part required for the WHERE statement. , 204, 255) !important]复制代码

Use QSqlTableModel::select() function to operate, that is, to perform a query operation. If the query is successful, the function returns true, which determines whether an error has occurred. If there is no error, we use the record() function to retrieve a row of records, which is given in the form of QSqlRecord, and QSqlRecord::value() retrieves the actual data value of a column. Note that since QSqlTableModel does not provide a const_iterator traversal, the foreach macro cannot be used for traversal.

It should also be noted that since QSqlTableModel is only an advanced operation, it is definitely not as convenient as actual SQL statements. Specifically, we can only perform SELECT * queries using QSqlTableModel, and cannot query only certain columns of data. , Helvetica, SimSun, sans-serif]The following code shows how to use QSqlTableModel to perform insert operations:

• QSqlTableModel model;
• model.setTable("student");
• int row Insertion is also simple: model.insertRows(row, 1);
• model.setData(model.index(row, 1), "Cheng");
• model.setData(model.index(row, 2), 24);
• model.submitAll();

[color=rgb(0, 204, 255) !important]Copy code

This means we want to insert a new row of data at index 0. Use the setData() function to prepare the data to be inserted. Note that we write Cheng to the first position of row (via model.index(row, 1). Recall that we treat the model as a two-dimensional table, and this coordinate is equivalent to row, column 1). The rest are similar. Finally, call submitAll() to submit all changes. The operation performed here can be expressed by the following SQL:

• INSERT INTO student (name, age) VALUES ('Cheng', 24)

[color=rgb(0, 204, 255) !important]Copy code

When we take out the existing data, modify it, and then rewrite it into the database, we complete an update operation:

• QSqlTableModel model;
• model.setTable("student");
• model.setFilter("age = 25");
• if (model.select()) {
• if (model.rowCount() == 1) {
• QSqlRecord record = model.record(0);
• record.setValue("age", 26);
• model.setRecord(0, record);
• model.submitAll();
• }
• }

[color=rgb(0, 204, 255) !important]复制代码

In this code, we first find the record with age = 25, then reset age to 26, store it in the same position (here it is index 0), and submit it to complete an update. Of course, we can also set it in the same way as other models: setData() function. The specific code snippet is as follows:

• if (model.select()) {
• if (model.rowCount() == 1) {
• model.setData(model.index(0, 2), 26);
• model.submitAll();
• }
• }

[color=rgb(0, 204, 255) !important]Copy code

Note that our age column is the third column and has an index value of 2 because there are also the id and name columns in front of it. The update operation here can be expressed by the following SQL:

• UPDATE student SET age = 26 WHERE age = 25

[color=rgb(0, 204, 255) !important]Copy code

The deletion operation is similar to the update:

• QSqlTableModel model;
• model.setTable("student");
• model.setFilter("age = 25");
• if (model.select()) {
• if (model.rowCount() == 1) {
• model.removeRows(0, 1);
• model.submitAll();
• }
• }

[color=rgb(0, 204, 255) !important]Copy code

If you use SQL:

• DELETE FROM student WHERE age = 25

[color=rgb(0, 204, 255) !important]Copy code

When we see the removeRows() function, we should think that we can delete multiple rows at once. This is indeed the case, so I will not go into details here.

sans-serif]

• QSqlTableModel model;
  
• model.setTable("student");
  
• model.setFilter("age = 25");
  
• if (model.select()) {
  
•     if (model.rowCount() == 1) {
  
•         model.removeRows(0, 1);
  
•         model.submitAll();
  
•     }
  
• }
  

[color=rgb(0, 204, 255) !important]复制代码

如果使用 SQL 则是：

• DELETE FROM student WHERE age = 25
  

[color=rgb(0, 204, 255) !important]复制代码

当我们看到 removeRows() 函数就应该想到：我们可以一次删除多行。事实也正是如此，这里不再赘述。

sans-serif]

• QSqlTableModel model;
  
• model.setTable("student");
  
• model.setFilter("age = 25");
  
• if (model.select()) {
  
•     if (model.rowCount() == 1) {
  
•         model.removeRows(0, 1);
  
•         model.submitAll();
  
•     }
  
• }
  

[color=rgb(0, 204, 255) !important]复制代码

如果使用 SQL 则是：

• DELETE FROM student WHERE age = 25
  

[color=rgb(0, 204, 255) !important]复制代码

当我们看到 removeRows() 函数就应该想到：我们可以一次删除多行。事实也正是如此，这里不再赘述。

star_66666

Very good explanation.