Transferred from: http://blog.chinaunix.net/uid-25201977-id-3014100.html
#include <QtSql>
QT + = Sqlqsqldatabase class enables operation of database connections
The Qsqlquery class is used to execute SQL statements
Qsqlrecord class encapsulates database all records first: Qsqldatabase class
Qsqldatabase db = Qsqldatabase::adddatabase ("Qsqlite"); Using the Qsqlite database, is there a problem with the Qmysql database?
Db.sethostname ("localhost"); Setting the database host name
Db.setdatabasename ("test"); Set database name
Db.setusername ("root"); Set Database login user name
Db.setpassword ("123456"); Design Database Login Password
Db.open () Open database connection Db.close ();//release database Qstringlist drivers = qsqldatabase::d rivers ();//static member function, which is a member function of a class, is not an object. Returns a list of all available database drivers
Drivers.removeall ("QMYSQL3"); Delete items in a list
foreach (QString driver, drivers)//Traverse database driver, test database driver type
Qdebug () << "\ t" << driver; Build database file
Qsqldatabase db = Qsqldatabase::adddatabase ("Qsqlite");
Db.setdatabasename ("database.db");
if (!db.open ()) return false;
Qsqlquery query;
Query.exec ("CREATE TABLE Login (ID INTEGER PRIMARY KEY autoincrement,"//id is automatically incremented because it cannot be duplicated, otherwise the database cannot be modified
"Goods int, store int, time nvarchar (+), type int, place nvarchar (), temperature nvarchar (10))";
Query.exec ("INSERT INTO Login" values (1,1002, 1, ' 2011-9-6 ', 1, ' common ', 24) ");
Query.exec ("INSERT INTO Login" values (2,1005, 1, ' 2011-9-6 ', 1, ' common ', 24) ");
Query.exec ("INSERT INTO Login" values (3,1010, 2, ' 2011-9-7 ', 2, ' common ', 25) ");
Query.exec ("INSERT INTO Login" values (4,1012, 2, ' 2011-9-7 ', 2, ' common ', 25) ");
Query.exec ("INSERT into Login values (1,null, Null,null, Null,null,null)");
Query.exec ("INSERT into Login values (2,null, Null,null, Null,null,null)");
Query.exec ("INSERT into Login values (3,null, Null,null, Null,null,null)");
Query.exec ("INSERT into Login values (4,null, Null,null, Null,null,null)");
Query.exec ("INSERT into Login values (5,null, Null,null, Null,null,null)");
Query.exec ("INSERT into Login values (6,null, Null,null, Null,null,null)");
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)"); Prepare to execute SQL query
Query.bindvalue (": Name", "Justin"); Determine the value of the binding on this fixed placeholder
Query.bindvalue (": Age", 33);
Query.exec ();
Qsqlquery query; The following executes the related QSL statement
Query.exec ("CREATE TABLE student (ID int primary key,name varchar)");
New student table, ID set as primary key, and one 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 ')");
Inserting 3 records into a table
Query.exec ("SELECT * from student");
To query out all the contents of the table. The "*" in the SQL statement "SELECT * from Student" indicates all attributes recorded in the query table. And when Query.exec ("SELECT * from student");
When this statement is executed, we get the result of the execution, because we can get more than one record, so we call it a result set.
while (Query.next ()) QString name = Query.value (0). toString (); Returns the index value of the "Name" field "Justin", Value (i) returns the values of the I field, 0 means that name,1 represents age
int id = query.value (1). ToInt (); seek (int n): query points to the nth record of the result set. Specify the current location first (): query points to a record in the result set. Last (): Query points to the final record of the result set. Next (): query points to the next record, and each time the function is executed, it points to the next record in the adjacent line. 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 value of the property. where n represents the nth attribute of your query, for example, above we use "SELECT * from student" equivalent to "SELECT ID, name from student", then value (0) returns the values of the id attribute (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 location of the current query
int fieldno = Query.record (). IndexOf ("name");//Returns the column number of "name" int rowNum = query.at ();
Gets the number of the record that the query points to in the result set
int columnnum = Query.record (). Count ();
Get 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 0
int id = query.value (0). ToInt ();
Gets the value of the id attribute and converts it to type int
QString name = Query.value (Fieldno). toString ();
Gets the value of the Name property
Qdebug () << "RowNum is:" << rowNum//outputs the result
<< "ID is:" << ID
<< "name is:" << name
<< "Columnnum is:" << columnnum;
}if (Query.seek (2))//seek Specify the current location
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.
{
Qdebug () << query.value (0). ToInt () << query.value (1). ToString (); Value (i) returns the values of the I field, 0 means that id,1 represents the 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); binding 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, output error if error occurs
Qdebug () << q.lasterror ();
Transaction operation--Operation function Transaction (), commit () Commit, rollback () rollback
A transaction is an important function of a database, so-called transactions are user-defined sequences of database operations that either do it all or do nothing and are an inseparable unit of work.
Start a transactional operation in Qt with transaction () and end with the commit () function or the rollback () function. Commit () represents the commit, which commits all the operations of the transaction.
Specifically, all updates to the database in a transaction are written back to the database, and the transaction ends normally. Rollback () indicates a rollback, that is, a failure occurred during the operation of the transaction, and the transaction cannot proceed.
The system revokes all completed operations on the database in the transaction and rolls back to the state at the start of the transaction. if (qsqldatabase::d atabase (). Driver ()->hasfeature (qsqldriver::transactions))//First determine if the database supports transactional operations. Hasfeature is a qsqldriver class function
if (qsqldatabase::d atabase (). Transaction ())//Start transaction operation
Qmodelindex class--used to define data in the data Model third: Qsqlquerymodel class read-only data model as a database result set
Functions: void Qsqlquerymodel::setquery (const QString & query, const qsqldatabase & db = Qsqldatabase ()//Execute Access query from a given The database db
Setheaderdata ()//Set Horizontal header title
ColumnCount (); Get Number of columns
ColumnCount (); Get Number of columns
Qsqlrecord Qsqlquerymodel::record (int row) const//Returns the information contained in row row
Qmodelindex qabstractitemmodel::index (int row, int column, const qmodelindex & parent = Qmodelindex ())//returns the specified row and column The index of the determined One (index)
Index.data ()//Returns the value of the index
1.
Qsqlquerymodel *model = new Qsqlquerymodel;
Model->setquery ("SELECT * from student"); Querying the contents of the entire student table, accessing the given database
Model->setheaderdata (0, Qt::horizontal, 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 (); Get Number of columns
int row = Model->rowcount (); Get the number of rows
Qsqlrecord record = Model->record (1); Get a record
Qmodelindex index = Model->index (a); 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 the Qsqlquery
Query.exec ("INSERT into student values (' yafei10 ')"); Insert a record in the Model Model->setquery ("SELECT * from student"); Querying the entire table again
View->show (); Show again, this sentence can not be written
4. Enable the database created by the Qsqlquerymodel class to read and write to the inherited Qabstractitemmodel class
At first we talked about 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 the SetData () and flags () two functions. If we want to change the display of the data, we need to rewrite it. BOOL Qabstractitemmodel::setdata (const QMODELINDEX & index, const qvariant & value, int role = qt::editrole)// Sets the value that is indexed by index
Qt::itemflags qabstractitemmodel::flags (const QMODELINDEX & index) const//Returns the flag of the given index
Qvariant Qabstractitemmodel::d ata (const QMODELINDEX & index, int role = Qt::D isplayrole) const//Return index and role (display state ) determines the value of Qt::itemflags mysqlquerymodel::flags (
Const QMODELINDEX &index) const//Return table is a flag that can be changed
{
Qt::itemflags flags = qsqlquerymodel::flags (index);
if (index.column () = = 1)//The second property can be changed
Flags |= qt::itemiseditable; Flags can be edited, so the second column can be edited.
return to the 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 determined to be a color equal to 9, so with the first column after
Return Qvariantfromvalue (Qcolor (qt::red)); The first property has a red color for the font
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
Qsqltablemodel *model = new Qsqltablemodel;
virtual bool Removecolumns (int column, int count, const Qmodelindex & parent = Qmodelindex ())//model->removecol Umns (0) Delete the first column bool Qsqltablemodel::submitall (),//Commit all the modified data, and then modify the data to be saved in the database
void Qsqltablemodel::revertall ()//undo all modifications, but if the database has been submitted for modification, it cannot be changed back to void qsqltablemodel::setfilter by undoing the modification (const QString & Filter)//filtering, filtering the database by string filter
BOOL Qsqltablemodel::select ()//In the condition of filtering and sorting, display the required database in the mode table with void Qsqltablemodel::setsort (int column, Qt:: SortOrder order)//Sort operation. Sort by column and Qt::sortorder. Qt::sortorder has ascending and descending virtual void revertrow (int row)//delete line bool InsertRow (int row, const Qmodelindex & parent = Qmod Elindex ())//Insert row, also have Insert Column model->seteditstrategy (QSQLTABLEMODEL::ONMANUALSUBMIT); Setting the Save policy to manual commit, onmanualsubmit indicates that we want to commit the change to make it effective. 1.//Display database data in the TableView table
Model = new Qsqltablemodel (this);
Model->settable ("Student"); Set the database table for "student"
Model->seteditstrategy (Qsqltablemodel::onmanualsubmit); Set save policy to manual commit
Model->select (); Select all rows for the entire table
Model->removecolumn (1); The Name property column is not displayed, and if you add a record at this point, the value of the property is not added.
Ui->tableview->setmodel (model); Redefine models, model inserts data directly from the DATABASE.DB database
Ui->tableview->setedittriggers (qabstractitemview::noedittriggers); Make it non-editable 2. When TableView is modified, the Submitall () function is to save the bool Qsqltablemodel::submitall (), commit all the modified data, and the modified data is saved in the database model-> Database (). transaction (); Start a transaction operation
if (Model->submitall ()) {//commits all modified data and then modifies the data to be saved in the database
Model->database (). commit (); Submit
} else {
Model->database (). rollback (); Rolling back
Qmessagebox::warning (This, tr ("TableModel"),
TR ("Database error:%1″)
. Arg (Model->lasterror (). Text ()));
}
Model->revertall (); Undo Changes
3. Query Operation--void qsqltablemodel::setfilter (const QString & Filter)//filter QString name = Ui->lineedit->text ();
Model->setfilter (qobject::tr ("name = '%1′"). Arg (name)); Filter by name
Model->select (); Displaying results 4. Sort operation//Model->setsort (0,qt::ascendingorder); ID attribute, No. 0 column, ascending order
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 a row
Delete the line Qitemselectionmodel *selections = Ui->tableview->selectionmodel (); Returns the current selection mode
Qmodelindexlist selected = Selections->selectedindexes (); Returns a list of all selected model project indexes
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 ("You are OK"
"Delete the current line?" "),
Qmessagebox::yes,qmessagebox::no);
if (ok = = Qmessagebox::no)
{
Model->revertall (); If you do not delete it, undo
}
else Model->submitall (); Otherwise commits, deletes the row in the database 6. Insert operation//insert line int rowNum = Model->rowcount (); Get the number of rows in a table
int id = 10;
Model->insertrow (RowNum); Add a row
Model->setdata (Model->index (rownum,0), id); Add an ID property value to a new row
Model->submitall (); can be submitted directly
Qsqlrelationaltablemodel->inherits qsqltablemodel->inherits Qsqlquerymodel->inherits Qabstracttablemodel->inherits qabstractitemmodel->inherits Qobjec
V: qsqlrelationaltablemodel--This class provides an editable data model for a single-sheet database table that supports foreign keys, except that there is nothing different than Qsqltablemodel model->setrelation (2, Qsqlrelation ("course", "id", "name")); Set foreign keys
Set the third property of the student 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 value of the Name property on the ID) if the user changes the course properties, then he can only choose among the courses in the curriculum , and you cannot fill out the course freely. 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 editing Qsqlrelationaltablemodel class
Qtableview *view = new Qtableview;
View->setmodel (model);
View->setitemdelegate (New Qsqlrelationaldelegate (view));
The Qdatawidgetmapper class can connect the database and controls (such as Qlineedit) to give the control the value of the database (example: Editemployees) VI: Qtableview, Qlistview, Qtreeview and Qsqlite
"Go" QT Database summary
