QT Qsqlquery Qsqlquerymodel

SQL Execution Action Qsqlquery provides a select, Insert, Update, delete operation for database records. Select operation: Qsqlquery query; Query.exec ("Select name, salary from employee WHERE salary > 50000"); while (Query.next ()) { QString name =query.value (0). toString (); int salary = Query.value (1). ToInt (); Qdebug () << name << salary; } by Qsqlquery::next () next Qsqlquery::p revious () Previous Qsqlquery::first () the first article Qsqlquery::last () Last article Qsqlquery::seek (), locates the position of any one record. Number of columns, number of fields: Query.record (). Count () Number of rows, number of records query.size (); Sql= "SELECT * FROM sys"; Query.exec (SQL); Rec=query.record (); int numrows; while (Query.next ()) { if (Db.driver ()->hasfeature (qsqldriver::querysize)) { Driver support returns the number of records NumRows = Query.size (); } Else { The driver does not support the return record count, only the loop lookup Query.last (); NumRows = query.at () + 1; } } Value: Query.value (0) Insert operation: Single Insert Data Qsqlquery query; Query.prepare ("INSERT into employee (ID, name, salary)" "VALUES (: ID,: Name,: Salary)"); Query.bindvalue (": id", 1001); Query.bindvalue (": Name", "Thad Beaumont"); Query.bindvalue (": Salary", 65000); Query.exec (); BULK INSERT Data Qsqlquery query; Query.prepare ("INSERT into myTable values (?,?)"); Qvariantlist ints; INTs << 1 << 2 << 3 << 4; Query.addbindvalue (INTs); Qvariantlist names; Names << "Harald" << "Boris" << "Trond" << qvariant (qvariant::string); Query.addbindvalue (names); if (!query.execbatch ()) Qdebug () << query.lasterror (); Update operation: Qsqlquery query; Query.prepare ("UPDATE employee SET salary =?") WHERE id = 1003 "); Query.bindvalue (0, 70000); Query.exe (); Delete operation: Qsqlquery query; Query.exec ("DELETE from employee WHERE ID = 1007"); Transaction processing: Qsqldatabase::d atabase (). transaction (); Qsqlquery query; Query.exec ("Select ID from employee WHERE name = ' Torild Halvorsen '"); if (Query.next ()) { int employeeId = query.value (0). ToInt (); Query.exec ("INSERT into Project (ID, name, ownerid)" "VALUES (201, ' Manhattan Project '," + Qstring::number (employeeId) + ")"); } Qsqldatabase::d atabase (). commit (); If the database engine supports transaction processing, the function qsqldriver::hasfeature (qsqldriver::transactions) returns True. You can initialize a transaction by calling Qsqldatabase::transaction (). After performing the work you want to do in the transaction. Then execute Qsqldatabase::commit () to commit the transaction or Qsqldatabase::rollback () to cancel the transaction. Here is an example of Qsqldriver::hasfeature (Qsqldriver::querysize), which can record the number of rows in a faster statistical query. Qsqlquery query; int numrows; Query.exec ("Select name, salary from employee WHERE salary > 50000"); Qsqldatabase defaultdb = qsqldatabase::d atabase (); if (Defaultdb.driver ()->hasfeature (qsqldriver::querysize)) { NumRows = Query.size (); } else { This can is very slow Query.last (); NumRows = query.at () + 1; } Stored procedures: Asciitoint () is a stored procedure in the database. But I've seen it online before. The stored procedure in SQL Server is done through "EXEC", not "call", I'm not sure! Leave a Question ~ Qsqlquery query; Query.prepare (" call Asciitoint (?,?)"); Query.bindvalue (0, "A"); Query.bindvalue (1, 0, qsql::out); 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

Http://www.cnblogs.com/bennylam/archive/2010/03/30/1700761.html

QT Operations Database

2. fully resolves the database store Chinese and QT program display database Chinese and Chinese string problems
(1) The Database and table and the character related fields (varchar, char, text, etc.) are used gbk_chinese_ci this way, not to do so, but doing so will save a lot of trouble.
(2) to recompile the MySQL driver for Qt, the src/sql/drivers/mysql/qsql_mysql.cpp file needs to be modified. The section to be modified is as follows: The codec function on line 108th
Static qtextcodec* codec (mysql* MYSQL)
{
Return Qtextcodec::codecforname ("GBK");//Add part
#if mysql_version_id >= 32321
qtextcodec* Heuristiccodec = Qtextcodec:: Codecforname (Mysql_character_set_name (MySQL));
if (HEURISTICCODEC)
return heuristiccodec;
#endif
return Qtextcodec::codecforlocale ();
}
(3) Then, recompile the QT MySQL driver in the QT program main function to add the following three sentences at the beginning of the app, plus these three is to save unnecessary trouble
Qtextcodec::setcodecforlocale (Qtextcodec::codecforname ("GBK"));
Qtextcodec::setcodecforcstrings (Qtextcodec::codecforname ("GBK"));
QTEXTCODEC::SETCODECFORTR (Qtextcodec::codecforname ("GBK"));
(4) After the QT database is connected, run the "set NAMES ' UTF8 '" statement or "set NAMES ' GBK '".
db = Qsqldatabase::adddatabase ("Qmysql");
Db.sethostname ("localhost");
Db.setdatabasename ("yourdatabase");
Db.setusername ("YourUserName");
Db.setpassword ("YourPassword");
Db.open ();
Db.exec ("SET NAMES ' UTF8 '");
After the above four steps, you can use the Chinese string directly in the QT program, and can directly use Chinese strings in and out of the program and database
3. Connect to MySQL database
Qsqldatabase db = Qsqldatabase::adddatabase ("Qmysql");
Db.sethostname ("localhost");
Db.setdatabasename ("test");
Db.setusername ("root");
Db.setpassword ("******");
bool OK = Db.open ();

Link qslite Database:

Qsqldatabase db = Qsqldatabase::Adddatabase ("Qsqlite");
4. Select operation
Qsqlquery query;
int numrows;
Query.exec ("SELECT * from scores ORDER by ID ASC");
while (Query.next ())
{
QString name = Query.value (0). toString ();//query.value () parameter can only be index
int salary = Query.value (1). ToInt ();
Label = SetText (Qstring::number (Salary) +name);//test query data row count
if (Db.driver ()->hasfeature (qsqldriver::querysize))
{
NumRows = Query.size ();
}
Else
{
This can is very slow
Query.last ();
NumRows = query.at () + 1;
}
}
5. Insert operation
(1) Insert directly (one record)
Qsqlquery query;
Query.exec ("INSERT into employee (ID, name, salary) VALUES (1001, ' Thad Beaumont ', 65000)");
(2) Action by ": Field"
Qsqlquery query;
Query.prepare ("INSERT into employee (ID, name, salary) VALUES (: ID,: Name,: Salary)");
Query.bindvalue (": id", 1001);
Query.bindvalue (": Name", "Thad Beaumont");
Query.bindvalue (": Salary", 65000);
Query.exec ();
(3) through the "? The action
Qsqlquery query;
Query.prepare ("INSERT into employee (ID, name, salary) VALUES (?,?,?)");
Query.addbindvalue (1001);
Query.addbindvalue ("Thad Beaumont");
Query.addbindvalue (65000);
Query.exec ();
6. Update Operation
Qsqlquery query;
query.exec ("UPDATE employee SET salary = 70000 WHERE id = 1003");
7. Delete Operation
Qsqlquery query;
query.exec ("DELETE from employee WHERE ID = 1007");
8.transaction () operation
Sometimes to perform a series of complex operations, using qsqldatabase::transaction () can speed up
Database is a Qsqldatabase object
if (Database.driver ()->hasfeature (qsqldriver::transactions))//testing whether the database supports transactions,
{
database.transaction ();
Query.exec ("Select name from scores WHERE id=19");
if (Query.next ())
{
str = query.value (0). toString ();
str = "China";
Query.exec ("INSERT into scores (name) VALUES ('" +str+ "')");
}
database.commit ();
}
Note that if Str is a Chinese string, it needs to be marked ' str ' in the SQL statement (enclosed in single quotation marks), and if it is an English or numeric string, it can be unquoted (refers to the SQL statement enclosed in double quotes)
9. Using the SQL Model class
qsqlquerymodel--A read-only model of reading database data
qsqltablemodel--A single tabular model that can read and write without having to write SQL statements
A subclass of Qsqlrelationaltablemodel--qsqltablemodel
These classes are inherited from the Qabstracttablemodel, and they all inherit from the Qabstractitemmodel
(1) Use of Qsqlquerymodel
Qsqlquerymodel Querymodel;
Querymodel.setquery ("SELECT * from scores ORDER by ID ASC");
For (Num=0;numquerymodel.rowcount (); num++)
{
str + =Qstring::number (Querymodel.record (num). Value ("id"). toint ());
str + = "";
str + =Querymodel.record (num). Value ("name"). ToString ();
//Note that the value () parameter here can be either index (index) or field name,The value () parameter of the preceding qsqlquery can only be index
str + = "\ n";
}
Label->settext (str);
(2) Use of Qsqltablemodel
① reading Data
Qsqltablemodel TableModel;

Tablemodel.settable ("scores");
tablemodel.setfilter ("ID > Ten");
Tablemodel.setsort (0,qt::D escendingorder);
Tablemodel.select ();
For (Num=0;numtablemodel.rowcount (); num++)
{
str + =qstring::number (Tablemodel.record (num). Value ("id"). toint ());
str + = "";
str + =Tablemodel.record (num). Value (1). toString ();
str + = "\ n";
}
Label->settext (str);
② Modifying Data
Qsqltablemodel TableModel;
Tablemodel.settable ("scores");
Tablemodel.setfilter ("ID > 10");
Tablemodel.setsort (0,qt::D escendingorder);
Tablemodel.select ();
For (Num=0;numtablemodel.rowcount (); num++)
{
Qsqlrecord record = Tablemodel.record (num);
Record.setvalue ("Name", Record.value ("name"). ToString () + "2");
Tablemodel.setrecord (Num,record);
}
if (Tablemodel.submitall ())
Label->settext ("Modified successfully! ");
Else
Label->settext ("Modify failed! ");
or can be modified with SetData (), the code is as follows:
Qsqltablemodel TableModel;
Tablemodel.settable ("scores");
Tablemodel.setfilter ("ID > 10");
Tablemodel.setsort (0,qt::D escendingorder);
Tablemodel.select ();
Tablemodel.setdata (Tablemodel.index (2,1), "Data modification");
if (Tablemodel.submitall ())
Label->settext ("Modified successfully! ");
Else
Label->settext ("Modify failed! ");
③ Deleting data
Tablemodel.removerows (row, 5);
Removerows ()The first parameter is the number of rows for the first row to be deleted, and the second parameter is the total number of rows to delete;
Tablemodel.submitall ();
//Note, use Qsqltablemodel to modify or delete data, and finally use Submitall () to perform changes
④ Inserting Data
Qsqlrecord record = Tablemodel.record ();
record.setvalue ("name", "inserted");
Tablemodel.insertrecord (2,record);
Note that the insertion here takes advantage of the Insertrecord () function,the first parameter of the function is inserted into the TableModel line, the second parameter is a record, note that the record here must match the records in TableModel, so qsqlrecord record = Tablemodel.record (); The insert operation is not Submitall () because, Insertrecord () returns a bool value.
10. Using Qtableview
To use # include at the beginning
Qtableview *view = new Qtableview ();
View->setmodel (&model);
View->setedittriggers (qabstractitemview::noedittriggers);
View->show ();