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

Source: Internet
Author: User
Tags rowcount

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

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


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

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

Third, the query operation

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

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


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

  1. Qitemselectionmodel *selections = Tableview->selectionmodel (); //Returns the current selection mode
  2. Qmodelindexlist selecteds = selections->selectedindexes (); //Returns a list of all selected model project indexes
  3. foreach (Qmodelindex index, selecteds)
  4. {
  5. int currow = Index.row (); //Delete all selected rows
  6. Model->removerow (Currow);
  7. }
  8. 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);
  9. if (ok = = Qmessagebox::yes)
  10. {
  11. Model->submitall (); //Submit, delete the row in the database
  12. } Else {
  13. Model->revertall (); //If not deleted, revoke
  14. }



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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.