QT Database SQLite Summary

The Qsqldatabase class implements the operation of the database connection
The Qsqlquery class is used to execute SQL statements
Qsqlrecord class encapsulates database all records
Qsqlrelationaltablemodel
Qsqlquerymodel
Qsqltablemodel

First: Qsqldatabase class

Qsqldatabase db = Qsqldatabase::adddatabase ("Qsqlite"); Use Qsqlite database Db.sethostname ("localhost"); Set the database host name Db.setdatabasename ("test"); Set the database name Db.setusername ("root"); Set Database login username Db.setpassword ("123456"); Design Database login Password Db.open () Open database connection Db.close ();//Release database

Qstringlist drivers = qsqldatabase::d rivers ();//static member functions, which are member functions of a class, are not objects. Returns a list of all available database drivers Drivers.removeall ("QMYSQL3"  ); Delete items in list foreach (QString driver, drivers)//Traverse database driver, test database driver Type Qdebug () << "\ t" << driver;

Second: Qsqlquery class, querying the database, inserting values into the database and other operational databases

Qsqlquery query; Query.prepare ("INSERT into T_user (name, age) VALUES (: Name,: Age)");   Query.bindvalue (": Name", "Justin"); On this fixed placeholder determine the value of the binding Query.bindvalue (": Age", 33); Query.exec ();

 qsqlquery query; //executes the relevant SQL statement query.exec ("Create table student (id int  Primary key,name varchar)//New student table, ID set as primary key, and a name entry Query.exec ("Insert into student  values (1, ' Xiaogang ') "); Query.exec (" Insert into student values (2, ' xiaoming ') "); Query.exec ( "Insert into student values (3, ' Xiaohong ')");//Insert 3 records into the table  query.exec ("select *  From student ");  to query out all the contents of the table. The "*" number in the SQL statement "Select * from student" indicates that all attributes recorded in the query table are queried. And when Query.exec ("Select * from student"), when this statement is executed, we get the corresponding execution result, because the result may be more than one record, so we call it the result set. 

Seek (Int n)  :query points to the nth record of the result set. Specifies the current position of first ()  :query point to the result set of record one. Last ()  :query points to the final record of the result set. Next ()  :query points to the next record, each time the function is executed, pointing to the next adjacent record. Previous ()  :query points to the previous record, and each time the function is executed, it points to the previous record. Record ()  : Gets the records pointed to now. Value (Int n)  : Gets the values of the property. where n represents the nth attribute of your query, for example, above we use "select * from student" is equivalent to "select id, name from  Student ", then value (0) returns the value of the id attribute, and value (1) Returns the value of the Name property. The function returns data of type Qvariant, and for the corresponding relationship of that type to other types, you can view qvariant in Help. at ()//returns the position of the current query Qstring name = query.value (0). toString ();  //returns the index value of the "Name" field "Justin", Value (i) Returns the value of the I field, 0 means that name,1 represents ageint rownum = query.at ();//Gets the number of the record pointed to by query in the result set int columnnum =  query.record (). Count ();//Gets the number of attributes (that is, columns) in each record Int fieldno = query.record (). IndexOf ("name");// Gets the number of the column that contains the Name property, the column is numbered from left to right, and the leftmost number is 0int id = query.value (0). ToInt ();//Gets the value of the id attribute and converts it to int type qstring  name = query.value (Fieldno). toString ();//Gets the value of the Name Property Qdebug ()  <<  Rownum is :  " << rownum //Results output           <<  " id is : "  << id          <<  " name is : "  << name          <<  " columnNum is : "  <<  columnnum;if (Query.seek (2))  //seek Specifies the current position        if (Query.seek ( Ui->spinbox->value ()))        {             qdebug ()  << query.value (0). ToInt ()  <<  Query.value (1). toString ();            while (Query.next ()   //each time the function is executed, it points to the next adjacent record.            {     &nbsP;      qdebug ()  << query.value (0). ToInt ()  <<  Query.value (1). toString ();  //value (i) returns the value of the I field, 0 means id,1 represents name            }       }

Batch operation function--BULK INSERT INTO database qsqlquery Q;q.prepare ("INSERT into student values (?,?)");          Qvariantlist ints;   Qvariantlist==qlist<qvariant>ints << << << << 13;q.addbindvalue (ints); Bind qvariantlist names;names<< "xiaoming" << "Xiaoliang" << "Xiaogang" << qvariant (qvariant:: string//the last one is an empty string, which should be the same as the previous format q.addbindvalue (names), if (!q.execbatch ())//batch, and output error if an error qdebug () << Q.lasterror ();

Third: The Qsqlquerymodel class read-only data model is a database result set

1.qsqlquerymodel *model = new qsqlquerymodel;model->setquery ("Select * from  student ");  model->setheaderdata (0,&NBSP;QT::HORIZONTAL,&NBSP;TR (" id ")); Model->setheaderdata (1 ,  qt::horizontal, tr ("name")); Qtableview *view = new qtableview;view->setmodel (model);//Redefine models, Model inserts data directly from the DATABASE.DB database View->show (); 2.int column = model->columncount ();  //gets the number of columns   int row = model->rowcount ();    //  Get line Count    Qsqlrecord record = model->record (1);  //get a record   qmodelindex index =  model->index (   //); Gets the value of one property of a record   qdebug ()  <<  "column  num is: " << column << endl            <<  "Row num is:"  << row <<  Endl           << "The second record is:"  << record << endl            << "The data of index ( is):"  << index.data ();  3. Qsqlquery query = model->query ();            //returns the model associated with Qsqlquery   query.exec ("insert into student values  (10, ' Yafei10 ');//Insert a record   model->setquery ("Select * from student") in the model;  //query the entire table again   view->show ();  //to show again, this sentence can also not write 4.  make Qsqlquerymodel class    created database can read and write, Inheriting the Qabstractitemmodel class just at the beginning we said that the model is read-only by default, so we can't modify the contents of the table in the window. But we can create our own models and then display the data and modify the data as we wish. To make it readable and writable, you need your own class to inherit from Qsqlquerymodel, and rewrite SetData ()   and  flags ()   two functions. If we want to change the display of the data, we need to rewrite the information ()   function. bool qabstractitemmodel::setdata  ( const qmodelindex & index, const qvariant & value, int  role = qt::editrole )  //sets the value values to be indexed by index Qt::itemflags qabstractitemmodel::flags   ( const QModelIndex & index )  const  // Returns the flag for a given index index Qvariant qabstractitemmodel::d ata  ( const qmodelindex & index , &NBSP;INT&NBSP;ROLE&NBSP;=&NBSP;QT::D isplayrole )  const  //Returns the value determined by the index and role (display state) qt::i Temflags mysqlquerymodel::flags (Const qmodelindex &index)  const {//returns whether the table can be changed flag     qt::itemflags flags = qsqlquerymodel::flags (Index);     if  (Index.column ()  == 1)  //Second property can be changed          Flags |= qt::itemiseditable;  //flags can be edited, so the second column can be edited     return  Flags;} Qvariant mysqlquerymodel::d ata (const qmodelindex &index, int role)  const{//Change the data display style     qvariant  value = qsqlquerymodel::d ata (index, role);if  (role == qt::textcolorrole  && index.column ()  == 0)   //qt::textcolorrole is OK color etc for 9, so with after is the first column          return qvariantfromvalue (Qcolor (Qt::red));  //The first property has a red font color     return value;}

IV: Qsqltablemodel inherits the Qsqlquerymodel class--This class provides an editable data model that reads and writes to a single SQL table, functions: Modify, INSERT, delete, query, and sort

1. //Displays the database data Model = new qsqltablemodel (this) in the TableView table; model->settable ("Student");   //set the database table for "student" model->seteditstrategy (qsqltablemodel::onmanualsubmit);//Set Save policy to manual commit model- >select ();  //selects all rows of the entire table//model->removecolumn (1),  //does not display the Name property column, and if you add a record at this point, the value of the property is not added. Ui->tableview->setmodel (model);  //redefine the model, which inserts data directly from the DATABASE.DB database//ui->tableview-> Setedittriggers (qabstractitemview::noedittriggers);//Make it non-editable 2.//when TableView is modified, it is saved by the Submitall () function    bool qsqltablemodel::submitall  (), commits all the modified data, and then modifies the data to be saved in the database Model->database (). transaction ();  //starts the transaction operation if  (Model->submitall ())  {   //commits all the modified data, then the modified data is saved in the database      model->database (). commit ();  //Submit}else {         model->database (). rollback ();  //rollback         qmessagebox:: Warning (this, tr ("TableModel"), TR ("Database errorError:  %1″)                               .arg (Model->lastError (). Text ( ));       }model->revertall ();  //undo Modify 3.  Query Operation--void  qsqltablemodel::setfilter  ( const QString & filter )  //screening  qstring  name = ui->lineedit->text ();  model->setfilter (Qobject::tr ("name = "%1′ "). Arg (name));  //Filter by name  model->select ();  //display results 4. Sort Operations   model->setsort (0,QT:: Ascendingorder);  //id attribute, No. 0 column, ascending  model->select ();  model->setsort (0,qt::D escendingorder);  model->select (); 5. Delete Row// int currow = ui->tableview->currentindex (). row ();// Gets the selected row// model->removerow (currow);    //Delete Row//delete Row qitemselectionmodel *selections  = ui->tableviEw->selectionmodel ();  //returns the current selection mode qmodelindexlist selected = selections-> Selectedindexes ();    //returns all selected model item index lists foreach  (qmodelindex index, selected) {     int currow=index.row ();     model->removerow (CurRow);    //Delete all selected rows}int ok = qmessagebox::warning (this,tr ("Delete current line!"), tr ("Are you sure" "Delete current line")                               qmessagebox::yes,qmessagebox::no);        if (Ok == qmessagebox::no)        {           model->revertall ();  //if not deleted, undo         }       else model->submitall ();  //otherwise commits, deletes the row in the database 6. Insert action//insert Row  INT&NBSp;rownum = model->rowcount ();  //gets the number of rows in the table  int id = 10; model-> InsertRow (RowNum);  //add a row  model->setdata (Model->index (rownum,0), id),   //Add id attribute value to New line  //model->submitall ();  //can be submitted directly qsqlrelationaltablemodel->inherits qsqltablemodel-> Inherits qsqlquerymodel->inherits qabstracttablemodel->inherits qabstractitemmodel-> Inherits

V: qsqlrelationaltablemodel--This class provides an editable data model for a single-sheet database table that supports foreign keys, except that they are not different from Qsqltablemodel.

Model->setrelation (2,qsqlrelation ("course", "id", "name")); Set the foreign key//student The third property of the table to the foreign key of the ID property of the course table and display it as the value of the Name property of the course table (the course table appears as the Name property value on the ID) if the user changes the course properties, Then he can only choose among the courses in the curriculum and not fill in the course. The Qsqlrelationaldelegate delegate class in QT can implement this function ui->tableview->setitemdelegate (new Qsqlrelationaldelegate (ui-> TableView)); Qsqlrelationaldelegate Class--provides delegate delegate to display the edit Qsqlrelationaltablemodel class Qtableview *view = new qtableview;view-> Setmodel (model); View->setitemdelegate (new Qsqlrelationaldelegate (view)); The Qdatawidgetmapper class can connect databases and controls (such as Qlineedit) to give a control the value of a database (example: editemployees)

QT Database SQLite Summary