Qtsql Study Notes (4)-Using the SQL model class

In addition to QSQLQUERY,QT provides 3 advanced classes for accessing the database. These classes are Qsqlquerymodel, Qsqltablemodel, and Qsqlrelationaltablemodel.

These classes are driven by Qabstracttablemodel (inherited from Qabstractitemmodel) and make it easier to represent the data in a database by using a single view class (such as Qlistview and Qtableview). This is detailed in the section "representing data with a table view."

Another benefit of using these classes is that it makes it easier for your code to work with other data sources. For example, if you are using Qsqltablemodel, and then you plan to use an XML file to replace the database store data, this is simply a matter of replacing another data model with one data model.

SQL query Model

Qsqlquerymodel provides a read-only model based on SQL queries. For example:

1 Qsqlquerymodel model;2Model.setquery ("SELECT * FROM employee");3 4  for(inti =0; I < Model.rowcount (); ++i) {5     intid = Model.record (i). Value ("ID"). ToInt ();6QString name = Model.record (i). Value ("name"). toString ();7Qdebug () << ID <<name;8}

After you set up the query with qsqlquerymodel::setquery () , you can use qsqlquerymodel::record (int) to access a single record. You can also use Qsqlquerymodel::d ata () and any other functions that inherit from Qabstractitemmodel.

There is also an overloaded setquery () method that takes a Qsqlquery object and operates in its result set. It allows you to set up queries using any of the Qsqlquery features (for example, prepared queries).

SQL table Model

Qsqltablemodel provides a readable and writable model that can operate on only one SQL table at a time. For example:

1 Qsqltablemodel model;2Model.settable ("Employee");3Model.setfilter ("Salary > 50000");4Model.setsort (2, Qt::D escendingorder);5Model.Select();6 7  for(inti =0; I < Model.rowcount (); ++i) {8QString name = Model.record (i). Value ("name"). toString ();9     intSalary = Model.record (i). Value ("Salary"). ToInt ();TenQdebug () << name <<salary; One}

Qsqltablemodel is an advanced, alternative qsqlquery model that you can use to browse and modify a single SQL table . Its typical advantage is that it requires only a small amount of code and does not need to understand SQL syntax.

Use Qsqltablemodel::record () to retrieve a row in the table, and then use Qsqltablemodel::setrecord () to modify the row. For example, the following code will increase the salary of all employees by 10%.

1  for(inti =0; I < Model.rowcount (); ++i) {2Qsqlrecord record =Model.record (i);3     DoubleSalary = Record.value ("Salary"). ToInt ();4Salary *=1.1;5Record.setvalue ("Salary", salary);6 Model.setrecord (i, record);7 }8Model.submitall ();

You can also use the method inherited from Qabstractitemmodel Qsqltablemodel::d ata () and Qsqltablemodel::setdata () to modify the data. For example, the following code shows how to update a record with SetData ():

1 75000 ); 2 model.submitall ();

How to insert a line when the following code:

1 1 ); 2 0 1013 ); 3 1 " Peter Gordon " ); 4 2 68500 ); 5 Model.submitall ();

How to delete 5 contiguous lines when the following code:

1 5 ); 2 model.submitall ();

The first parameter of Qsqltablemodel::removerows () is the index number of the first row with the deletion.

When you have completed the changes to the records, you always need to call Qsqltablemodel::submitall () to ensure that the changes are written to the database.

When and whether you really need to call Submitall () actually depends on the table's edit policy (edit strategy), the default policy is Qsqltablemodel::onrowchange, This means that when the user selects a different row, the changes to the row are applied to the database. Other strategies include qsqltablemodel::onmanualsubmit(all changes will be cached in the model until you call the Submitall () method) and Qsqltablemodel::o Nfieldchange (no changes are cached). These policies are useful when Qsqltablemodel is used in conjunction with a view.

SQL Relational Table model

Qsqlrelationaltablemodel extended the Qsqltablemodel to provide support for foreign keys (foreign key). A foreign key is a one by one mapping between one field in one table and the primary key (primary key) field in another table. For example, if a book table has a Authorid field associated with an ID field in the Author table, then we say Authorid is a foreign key.

The screenshot shows a plain Qsqltablemodel in a qtableview. Foreign keys (city and country) aren ' t resolved to human-readable values. The screenshot shows a Qsqlrelationaltablemodel, with foreign keys resolved into human-readable text Strings.

The following code snippet shows how to set the Qsqlrelationaltablemodel:

1Model->settable ("Employee");2 3Model->setrelation (2, Qsqlrelation (" City","ID","name"));4Model->setrelation (3, Qsqlrelation ("Country","ID","name"));

You can consult the Qsqlrelationaltablemodel documentation for more information.

Qtsql Study Notes (4)-Using the SQL model class