Introduction:QT SQL is one of the important modules of QT, for the sake of convenience, QT has a series of packages for SQL, and divides the SQL API into the following three layers:(1) drive layer(2) SQL API layer(3) User interface layer
Directory:first, the use of the premisesecond, the database classthird, connect to the database · SQL Database DriverIv. executing SQL statements · Data types for QT-supported database Systemsv. Using the SQL Model class
first, the use of the premise:(1) Add the corresponding header file in C + + file#include <QtSql>(2) Add QT SQL module to QT project fileQT + = SQL
second, the database class(1) Drive layer drive layer contains the following classes:Qsqldriver, Qsqldrivercreator, Qsqldrivercreatorbase, Qsqldriverplugin, and Qsqlresult.The driver layer builds an underlying bridge between the specific database system and the SQL API, which makes it irrelevant to the database, that is, QT treats various databases as an object, and the way to use them is to load them into the program via a driver plugin. We can understand that this database is available only if the QT version of the driver supports it. is the type of database that QT supports:
(2) SQL API layer The main function of the collection of this class is to access the database, and the implication is the collection of method classes that perform various operations on the database. For example, you can useQsqldatabaseEstablish a connection byQsqlqueryMake database interactions (queries, etc.). In addition, several common classes are included:Qsqlerror, Qsqlfield, Qsqlindex, and Qsqlrecord.
(3) The class of user interface layer of user interface layer includes:Qsqlquerymodel, Qsqltablemodel, and Qsqlrelationaltablemodel.They can be used to project data onto specific parts, and the part must have QT models and viewsModel/viewFramework.
Note: Before you use the QT SQL class, you must instantiate the Qcoreapplication object.
third, connect to the databasePassQsqlqueryAndQsqlquerymodelAccessing the database creates one or more database connections. Database connection Name[ConnectionName]To differentiate, not database names[DatabaseName]。 You can have multiple connections to the same number of libraries. In additionQsqldatabaseThe concept of default connection is also supported, of course, this default connection is unnamed. When callingQsqlqueryOrQsqlquerymodelmember function with a connection name parameter, the default connection is used if the connection name is not passed. Please note that there is a difference between creating a connection and opening the connection. Creating a connection creates aQsqldatabaseobject, and the connection is not available until this connection is opened. QT official gave an example: the first line creates a default database connection because the program does not pass a specific connection name as the second parameter to Adddatabase (), which can be modified as follows:"This creates a name for the"First"And"SecondTwo connections. It is worth mentioning that before you open the connection, you need to initialize the connection, including the following:SetHostName (), Setdatabasename (), Setusername () and SetPassword ()。 After the connection is initialized, you can call theOpen ()Open, if open fails, will returnfalse, by callingLastError ()You can get an error message. In addition, after the connection is established, the calldatabase ()The connection name is returned, and the connection is called before it is removed.Close ()Close it, and then callremovedatabase ()removed.
iv. Executing SQL statements
Execute QueryQsqlqueryclass provides an interface for executing SQL statements and queries, while theQsqlquerymodelAndQsqltablemodelProvides an advanced interface for database operations. Before executing an SQL statement, you need to create aQsqlqueryobject, and then call theqsqlquery::exec ()For example:QsqlqeuryThe constructor allows you to receive an optionalQsqldatabaseobject, which must be used by the database connection, and the database connection object is not passed in the previous example, so the default database connection will be taken.
operation Result setQsqlqueryAlso provides access to the result set, in the callexec ()AfterQsqlqueryPointer to the first record in the result set by calling theNext ()You can get the first record, and then call next () to get the next record. In the following example, the effect is equivalent to traversing the result set:Qsqlqeury::value ()Returns the specified field value of the current record whose return value type isqvariant，qvariantis actually a type of union, so it needs to be converted into the desired format (Int/qstring/qbytearray, etc.).
We can also putQsqlqueryAs an iterator, because it supportsQsqlquery::next (), Qsqlquery::p revious (), Qsqlquery::first (), Qsqlquery::last (), and Qsqlquery::seek (). Qsqlquery::at ()Returns the row index of the current record.qsqlquery::size ()Returns the number of record bars for the result set, provided thatqsqlquery::size ()Requires database-driven support.
Insert, update, and deleteQsqlqueryArbitrary SQL statements can be executed, not limited toSelects。 such as inserting records: If you want to insert multiple records at the same time, you can use thebindingTo do: Update a record or call a modified record, similar to inserting a record: Finally, delete the record:
Transaction ProcessingIf the underlying database engine supports transaction processing, thenqsqldriver::hasfeature (qsqldriver::transactions)will return true. by callingqsqldatabase::transactions ()You can initialize a transaction, and then you can execute the operation of the SQL statement, or call theQsqldatabase::commit ()OrQsqldatabase::rollback ()The It is important to note that transaction transaction must be started before query creation when using transaction processing. As follows: Transactions can be used for data protection because their operations are atomic (such as querying foreign keys and creating records) and provide a set of rollback mechanisms.
v. Using the SQL Model classApart fromQsqlquery, QT also provides three more advanced classes to access the database, which areQsqlquerymodel, Qsqltablemodel, and Qsqlrelationaltablemodel.
These classes derive from theQabstracttablemodel, and makes the data in the database easy to display in the view class, for exampleQlistviewAndQtableview。
The SQL Query ModelQsqlquerymodel provides a read-only model based on SQL query. Examples are: usingqsqlqeurymodel::setquery ()After you set up query, you can use theqsqlquerymodel::record (int)To access a separate record, or to invoke theQsqlquerymodel::d ata ()or other inherited fromQabstractitemmodelThe method.
The SQL Table ModelQsqltablemodel provides a readable and writable model based on simple SQL tables. The following example: Qsqltablemodel is a high-level alternative to qsqlquery, which can be used to browse and modify a single SQL table, and it does not require users to be familiar with the syntax of SQL. Use Qsqltablemodel::record () to retrieve a row of records in a table, Qsqltablemodel::setrecord () to modify Row Records. As shown in the following example: Of course, you can also use Qsqltablemodel::d ata () and Qsqltablemodel::setdata () to access the database. As shown in the following example: SetData (): Insert a record: Delete five consecutive records:
After the record changes, the Qsqltablemodel::submitall () commit must be called to save, otherwise the data is not written. When and whether to call Qsqltablemodel::submitall () depends on the edit strategy (editing policy) of the table, the default editing strategy is Qsqltablemodel::onrowchange (accompanying changes), The other strategies are as follows:The SQL relational Table ModelQsqlrelationaltablemodelExtends theQsqltablemodelTo provide support for foreign keys. A foreign key is a one-to-two mapping between a field in a table and a primary key field in another table. Table 1 is the use ofQsqltablemodelShow inQtableviewA table in which foreign key city and country have not yet been resolved to a readable value. Table 2 uses theQsqlrelationaltablemodelTo show that the outer key has been converted to a readable string value. The following code snippet showsQsqlrelationaltablemodelThe establishment process:
PS:
This article translates into QT's SQL module, which is truncated in the middle, plus some personal understanding.
The entire QT SQL programming is not fully translated, and then there are two examples of QT, you can go to see for yourself.
due to the limited English skills, there may be a number of improper translation, hope to point out, common progress.
Qt SQL Programming Partial translation
