Detailed description of database operations in QT

QTMediumDatabaseThe related operations are described in this article.QTMediumDatabaseOperations, the article is very detailed aboutDatabaseFor details, see the content.

Database Operations

1. MySql driver Compilation

In windows, MySql driver compilation in mingw, MySql5, and Qt4 mainly involves the following steps:

(1) download http://www.qtcn.org/download/mingw-utils-0.3.tar.gz
;
(2gz decompress mingw-utils-0.3.tar.gz and copy the reimp tool in the bin directory to the bin in the mingw directory. If Dev-C ++ is used, copy it to the C: \ Dev-Cpp \ bin directory.

(3) copy the include and lib directories in the MySql installation directory to a path without spaces, such as C: \ mysql;

(4) Open Qt Command Prompt and execute the following commands respectively:

Cd c: \ mysql \ lib \ opt reimp-d libmysql. lib dlltool-k-d libmysql. def-l libmysql.

In this way, a libmysql. a file is generated under the c: \ mysql \ lib \ opt directory;

(5) run the following command:

 
 

  
  
cd %QTDIR%＼src＼plugins＼sqldrivers＼mysql   
  
  
 
  
  
qmake -o Makefile "INCLUDEPATH+=C:＼MYSQL＼INCLUDE" "LIBS+=C :＼MYSQL＼LIB＼OPT＼LIBMYSQL.a" mysql.pro  
 
 

Find the Makefile under % QTDIR % \ src \ plugins \ sqldrivers \ mysql. release and Makefile. debug file, remove the-llibmysql item and make the compilation. libqsqlmysql is generated under the % QTDIR % \ plugins \ sqldrivers directory. a, qsqlmysql. dll files, and then in the program's. add QT + = SQL to the pro file and include # include at the beginning of the program to operate the database.

2. Completely solves the problem that the database stores Chinese characters and Qt programs display Chinese and Chinese strings in the database.

(1) varchar, char, text, and other character-related fields in the database and table must be used in gbk_chinese_ci mode. If this method is not used, it saves a lot of trouble.

(2) To re-compile the MySQL driver of Qt, modify the src/SQL/drivers/mysql/qsql_mysql.cpp file. The part to be modified is as follows: the codec function of the 108th line

 
 

  
  
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, re-compile the mysql driver of qt and add the following three sentences at the beginning of the app in the main function of the Qt program. Adding these three statements saves unnecessary trouble.

 
 

  
  
QTextCodec::setCodecForLocale(QTextCodec::codecForName("GBK"));   
  
  
QTextCodec::setCodecForCStrings(QTextCodec::codecForName("GBK"));   
  
  
QTextCodec::setCodecForTr(QTextCodec::codecForName("GBK"));  
 
 

(4) After connecting to the Qt database, 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 directly use a Chinese string in the Qt program, and can directly use a Chinese string between the program and the database.

3. Connect to the MySql database

 
 

  
  
QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");   
  
  
db.setHostName("localhost");   
  
  
db.setDatabaseName("test");   
  
  
db.setUserName("root");   
  
  
db.setPassword("******");   
  
  
bool ok = db.open(); 
 
 

Link to the QSLite database:

 
 

  
  
QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");  
 
 

4. SELECT Operation

 
 

  
  
QSqlQuery query;
  
  
Int numRows;
  
  
Query.exe c ("SELECT * FROM scores order by id ASC ");
  
  
While (query. next ())
  
  
{
  
  
QString name = query. value (0). toString (); // The query. value () parameter can only be index
  
  
Int salary = query. value (1). toInt ();
  
  
Label = setText (QString: number (salary) + name); // test the number of rows queried
  
  
If (db. driver ()-> hasFeature (QSqlDriver: QuerySize ))
  
  
{
  
  
NumRows = query. size ();
  
  
}
  
  
Else
  
  
{
  
  
// This can be very slow
  
  
Query. last ();
  
  
NumRows = query. at () + 1;
  
  
}
  
  
}
 
 

5. INSERT operation

1) insert a record directly)

 
 

  
  
QSqlQuery query;   
  
  
query.exec("INSERT INTO employee (id, name, salary) VALUES (1001, ’Thad Beaumont’, 65000)");  
 
 

2) perform the ": Field" Operation

 
 

  
  
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) pass "?" Operation

 
 

  
  
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 operations

 
 

  
  
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 QSqlDatabase: transaction () can speed up a series of complex operations.

 
 

  
  
// The database is a QSqlDatabase object.
  
  
If (database. driver ()-> hasFeature (QSqlDriver: Transactions) // test whether the database supports Transactions,
  
  
{
  
  
Database. transaction ();
  
  
Query.exe c ("SELECT name FROM scores WHERE id = 19 ");
  
  
If (query. next ())
  
  
{
  
  
// Str = query. value (0). toString ();
  
  
Str = "China ";
  
  
Query.exe c ("insert into scores (name) VALUES ('" + str + "')");
  
  
}
  
  
Database. commit ();
  
  
}
 
 

NOTE: If str is a Chinese string, 'str' must be enclosed in single quotation marks in SQL statements, no single quotation marks are allowed (it refers to the SQL statement included in double quotation marks)

9. Use the SQL Model class

 
 

  
  
QSqlQueryModel-a read-only model for reading database data
  
  
QSqlTableModel-a single table model that can be read and written without the need to write SQL statements
  
  
QSqlRelationalTableModel -- a subclass of QSqlTableModel
 
 

These classes all inherit from qiniacttablemodel, and they all inherit from qiniactitemmodel.

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 can be an index or a field name. The value () parameter of the preceding QSqlQuery can only be an index.
  
  
Str + = "\ n ";
  
  
}
  
  
Label-> setText (str );
 
 

2) Use of QSqlTableModel

① Read data

 
 

  
  
QSqlTableModel tablemodel;  
  
  
 
  
  
tablemodel.setTable("scores");   
  
  
tablemodel.setFilter("id > 10");   
  
  
tablemodel.setSort(0,Qt::DescendingOrder);   
  
  
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);  
 
 

② Modify data

 
 

  
  
QSqlTableModel tablemodel;
  
  
Tablemodel. setTable ("scores ");
  
  
Tablemodel. setFilter ("id> 10 ");
  
  
Tablemodel. setSort (0, Qt: DescendingOrder );
  
  
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 ("modification failed! ");
  
  
Or you can use setData () to modify the Code as follows:
  
  
QSqlTableModel tablemodel;
  
  
Tablemodel. setTable ("scores ");
  
  
Tablemodel. setFilter ("id> 10 ");
  
  
Tablemodel. setSort (0, Qt: DescendingOrder );
  
  
Tablemodel. select ();
  
  
Tablemodel. setData (tablemodel. index (2, 1), "data modification ");
  
  
If (tablemodel. submitAll ())
  
  
Label-> setText ("modified successfully! ");
  
  
Else
  
  
Label-> setText ("modification failed! ");
 
 

③ Delete data

 
 

  
  
Tablemodel. removeRows (row, 5 );
  
  
// RemoveRows () the first parameter is the number of rows in the first row to be deleted, and the second parameter is the total number of rows to be deleted;
  
  
Tablemodel. submitAll ();
  
  
// Note: Use QSqlTableModel to modify or delete data, and use submitAll () to execute the change.
 
 

④ Insert data

 
 

  
  
QSqlRecord record = tablemodel. record ();
  
  
Record. setValue ("name", "inserted ");
  
  
Tablemodel. insertRecord (2, record );
  
  
// Note: insert the insertRecord function here. The first parameter of this function is the row number inserted to tablemodel,
  
  
The second parameter is record. Note that the record here must match the record in tablemodel,
  
  
Therefore, QSqlRecord record = tablemodel. record (); in addition, submitAll is not required for the insert operation. Because, insertRecord), bool value is returned.
 
 

10. Use QTableView

# Include

 
 

  
  
QTableView *view = new QTableView();   
  
  
view->setModel(&model);   
  
  
view->setEditTriggers(QAbstractItemView::NoEditTriggers);   
  
  
view->show(); 
 
 

Summary: DetailsQTMediumDatabaseThe related operations have been introduced. I hope this article will help you!