35 using the model to manipulate the database

The previous SQL statement was used to complete the general operation of 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 model-based approach. This Qsqltablemodel-based model is more advanced, and if you are unfamiliar with SQL statements and do not require a lot of complex queries, this Qsqltablemodel model can basically meet the general requirements. In this section, we will introduce the general use of Qsqltablemodel, and compare SQL statements to complete the operation of database additions and deletions. It should be noted that Qsqltablemodel does not necessarily have to be used in conjunction with Qlistview or Qtableview, and we can use it for general processing.

Query operations

First, let's look at how to use Qsqltablemodel for a SELECT operation:

if (Connect ("Demo.db")) {

Qsqltablemodel model;

Model.settable ("Student");

Model.setfilter ("Age > 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;

}

The Connect () function is still used. The Qsqltablemodel instance is then created,

? The settable () function sets the required action table;

? The SetFilter () function is the addition of a filter, which is the part required by the WHERE statement.

For example, the operation in the above code is actually equivalent to the SQL statement

SELECT * FROM student WHERE is > age < 25

Use the Qsqltablemodel::select () function to perform a query operation. If the query succeeds, the function returns True to determine if an error has occurred. If there is no error, we use the record () function to take out a row of records that are given as Qsqlrecord, while Qsqlrecord::value () takes out the actual data value of a column. Note that because Qsqltablemodel does not provide a const_iterator walker, you cannot traverse using a foreach macro.

Note: Since Qsqltablemodel is only an advanced operation, it is certainly not convenient for actual SQL statements. Specifically, we use Qsqltablemodel only for SELECT * queries, and we cannot query only the data for some of these columns.

Insert operation

The following code shows how to use Qsqltablemodel for insert operations:

Qsqltablemodel model;

Model.settable ("Student");

int row = 0;

Model.insertrows (row, 1);

Model.setdata (Model.index (row, 1), "Cheng");

Model.setdata (Model.index (row, 2), 24);

Model.submitall ();

The insertion is also simple: model.insertrows (row, 1); we want to insert 1 rows of new data in the position of index 0. Use the SetData () function to start preparing the data that you actually want to insert. Note here we write to the first position of row Cheng (via Model.index (row, 1), recall that we used the model as a two-dimensional table, which corresponds to column 1th of Row row), the rest and so on. Finally, call the Submitall () function to commit all the modifications. The operations performed here can be represented by the following SQL:

INSERT into student (name, age) VALUES (' Cheng ', 24)

Update action

Once we have removed the existing data, we modify it and then write it back to the database, which completes the 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 ();

}

}

In this code, we first find the record of age = 25, then set age back to 26, save it in the same location (where it is the index 0 location), and complete the update after submission. Of course, we can set up the same way as other models: the 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 ();

}

}

Note that our age column is the 3rd column, with an index value of 2, because there is an ID and a name two column in front. The update operation here can be represented by the following SQL:

UPDATE Student SET Age = + WHERE age = 25

Delete operation

The delete 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 ();

}

}

If you use SQL, you are:

DELETE from student WHERE age = 25

When we see the removerows () function We should think: we can delete more than one row at a time. That is exactly the case, and it is no longer mentioned here.

35 using the model to manipulate the database