QT Notes: Database Summary (iii) the SQL model class-qsqltablemodel model

The Qsqltablemodel class inherits to the Qsqlquerymodel class, which provides an editable data model that reads and writes to a single SQL table, features: Modify, INSERT, delete, query, and sort

Common functions

Qvariant headerdata (int section, qt::orientation Orientation, int role = Qt::D isplayrole) CO NST gets the horizontal head or vertical header title

BOOL Setheaderdata (int section, qt::orientation Orientation, const qvariant & value, int role = qt::editrole) Set the horizontal head or vertical header title

int RowCount (const Qmodelindex & Parent= Qmodelindex ()) const//Return row count

int ColumnCount (const Qmodelindex &Index = qmodelindex ()) const//return number of columns

virtual bool Removecolumns (int column, int count, const Qmodelindex & parent = Qmodelindex ())//model->removecol Umns (0) Delete the first column

BOOL Qsqltablemodel::submitall (),//Commit all modified data, and then modify the data to be saved in the database

void Qsqltablemodel::revertall ()//undo all changes, and if the database has been submitted for modification, it cannot be changed back by undoing the changes.

virtual void Revertrow (int row)//restore the specified row changes

void Qsqltablemodel::setfilter (const QString & Filter)//filter, filter the database by string filter, equivalent to the WHERE statement in SQL

BOOL Qsqltablemodel::select ()//In the condition of filtering and sorting, display the database in the mode table in accordance with the requirements

void Qsqltablemodel::setsort (int column, qt::sortorder order)//Sort operation. Sort by column and Qt::sortorder. Qt::sortorder have ascending and descending

BOOL InsertRow (int row, const Qmodelindex & parent = Qmodelindex ())//Insert Row

BOOL InsertColumn (int column, Constqmodelindex & Parent = Qmodelindex ())//Insert Column

Model->seteditstrategy (Qsqltablemodel::onmanualsubmit); Set save policy to manual commit

I. Displaying data from a table in a database in Qtableview

2. Qsqltablemodel *model = new Qsqltablemodel (ParentObject, database); //excerpt from the Help document
4. Model->settable ("employee");
6. Model->seteditstrategy (Qsqltablemodel::onmanualsubmit);
8. Model->select ();
10. Model->removecolumn (0); //don ' t show the ID
12. Model->setheaderdata (0, Qt::horizontal, tr ("Name"));
14. Model->setheaderdata (1, qt::horizontal, tr ("Salary"));
18. Qtableview *view = new Qtableview;
20. View->setmodel (model);
22. View->show ();

Second, modify the data in the Qtableview after the submission, add transaction processing

2. Model->database (). transaction (); //Start transaction Operations
4. if (Model->submitall ()) //Submit all modified data to the database
6. {
8. Model->database (). commit (); //Commit succeeds, transaction will really modify database data
10. } Else {
12. Model->database (). rollback (); //Commit failed, transaction rollback
14. Qmessagebox::warning (This, tr ("TableModel"), TR ("Database error:%1″)." Arg (Model->lasterror (). Text ()));
16. }
18. Model->revertall (); //Undo Changes

Third, the query operation

Equivalent to SQL statement: SELECT * FROM table name WHERE name = "Name variable"

2. Model->setfilter (qobject::tr ("name = '%1′"). Arg (name)); //Filter by name
4. Model->select (); //Display results
6. for (int i = 0; i < Model.rowcount (); ++i)
8. {
10. QString name = Model.record (i). Value ("name"). ToString ();
12. // ... The record of each article is processed here
14. }
18. When working with large datasets, it is recommended that you specify fields by index
20. int primarykeyindex = Model.record (). IndexOf ("id");
22. for (int i = 0; i < Model.rowcount (); ++i)
24. {
26. Qsqlrecord record = Model.record (i);
28. QString name = Record.value ("name"). ToString ();
30. // ... The record of each article is processed here
32. }

Iv. Sorting Operations

1. Model->setsort (0,qt::ascendingorder); //id Property, No. 0 column, ascending order, Qt::D escendingorder in descending order
2. Model->select ();

v. Insert Operation

1. int rowNum = Model->rowcount (); //Gets the number of rows in the table
2. int id = Last id+1;
3. Model->insertrow (RowNum); //Add a row, or use InsertRows (0,1) to add 1 records in 0 rows, depending on the collation of the table, may move to a different row position than the specified row
4. Model->setdata (Model->index (rownum,0), id); //Because the ID is set as the primary key, the ID attribute value must be added to the new row, and the ID field is on column No. 0
5. Model->submitall (); //Can be submitted directly

Vi. Delete a record

First, locate the row you want to delete.

1. Model.setfilter ("id = 10");
2. Model.select ();
3. if (model.rowcount () = = 1)
4. {
5. Model.removerows (0,1) //If you want to delete all records that meet the criteria, change the 1 to Model.rowcount ()
6. Model.submitall ();
7. }

Delete the selected row in Qtableview

1. int currow = Tableview->currentindex (). row ();
2. Model->removerow (Currow); //delete a row

Delete the selected multiline in Qtableview

Qabstractitemview:: Selectionmodeselectionmode()const//prototype

Qmodelindexlistqitemselectionmodel:: Selectedindexes()const//prototype

2. Qitemselectionmodel *selections = Tableview->selectionmodel (); //Returns the current selection mode
4. Qmodelindexlist selecteds = selections->selectedindexes (); //Returns a list of all selected model project indexes
6. foreach (Qmodelindex index, selecteds)
8. {
10. int currow = Index.row (); //Delete all selected rows
12. Model->removerow (Currow);
14. }
18. int ok = qmessagebox::warning (this,tr ("Delete selected Rows!"), TR ("Are you sure you want to delete the rows in the current selection?"), Qmessagebox::yes,qmessagebox::  No);
20. if (ok = = Qmessagebox::yes)
22. {
24. Model->submitall (); //Submit, delete the row in the database
26. } Else {
28. Model->revertall (); //If not deleted, revoke
30. }

vii. Record of updates

Records must be positioned first

1. Model.setfilter ("id = 10");
2. Model.select ();
3. if (model.rowcount () = = 1)
4. {
5. Model.setdata (Model.index (0,1), Qobject::tr ("Xiao Wang"));
6. Model.submitall ();
7. }

You can see that this model is very powerful and completely out of SQL statements, and even if you don't understand the database, you can use it for most common operations. This model provides a buffer that can save all modifications to the model before actually writing to the database until we commit the changes. Of course this is also because we set up its save policy at the very beginning:

Model->seteditstrategy (Qsqltablemodel::onmanualsubmit);

Onmanualsubmit shows that we have to submit changes to make them effective. You can save the changes first, and when we execute the commit function, we actually modify the database. Of course, this model is more advanced than the previous model, and all of the previous operations can be performed here.

QT Notes: Database Summary (iii) the SQL model class-qsqltablemodel model