Qtsql Study Notes (3)-Execute SQL statements

The Qsqlquery class provides an interface for executing SQL statements and the result set of a browse query.

The Qsqlquerymodel and Qsqltablemodel classes provide a high-level interface for accessing the database, which is described in the next section. If you are unfamiliar with SQL, if you are unfamiliar with SQL, you can use the Advanced interface classes described in the next section directly.

1 Executing a query

To execute an SQL statement, simply create a Qsqlquery object and then call the qsqlquery::exec () method, as follows:

qsqlquery query;query.exec ("select name, salary from employee WHERE salary > 50000" 
    );

The Qsqlquery constructor accepts an optional Qsqldatabase object parameter to specify which database connection will be used. In the example above, we do not specify any connection, so we use the default connection. If an error occurs while executing the query, EXEC () returns false and the cause of the error can be viewed through qsqlquery::lasterror () .

2 Browsing Query Results

Qsqlquery provides a way to access a record of a query result. After the Exec () method is called, the internal pointer of Qsqlquery is positioned before the first record. We must call the Qsqlquery::next () internal pointer to move to the first record, and then repeatedly call the next () method to move to the other record until the function returns FALSE. Here's a typical loop that loops through all of the records in turn:

 while (Query.next ()) {        = Query.value (0). toString ();         int salary = Query.value (1). ToInt ();         << name << salary;}

　　The qsqlquery::value () function returns a field value for the current record. The index number of the field is counted starting at 0. Qsqlquery::value () returns a qvariant that can hold a variety of C + + and core QT data types, such as int, qstring, and Qbytearray. Different database types are automatically mapped to the nearest QT equivalence type. In the above code, we used Qvariant::tostring () and Qvariant::toint () to convert the variable types to qstring and int.

Add: About the value () method parameter-index number range problem, we only know that the first field corresponding to the index number is 0, but do not know the last field index number, we can traverse all possible values, because when the value () function index number is illegal, will return an illegal qvariant variable, so we can tell whether the last field has been reached by judging the legitimacy of the returned qvariant variable. A sample code is given below:

 while (Query.next ()) {    int0;    QString result;    Qvariant temp;      while (temp = Query.value (i++)). IsValid ()) {        + = temp.tostring ();         " ";    }    UI->textbrowser->append (result);}

Here is a table of conversion relationships between MySQL and QT-supported data types:

You can use Qsqlquery::next (), Qsqlquery::p revious (), Qsqlquery::first (), and Qsqlquery::last () to move the pointer back and forth to the record position. You can use qsqlquery::at () to return the index number of the current row, and if the corresponding database supports it, you can use qsqlquery::size () to return the total number of rows in the query result set (MySQL is supported).

To verify whether a database driver supports an attribute,qsqldriver::hasfeature (). The following example calls Qsqlquery::size () to check if the underlying database supports this feature, otherwise we can only browse to the last record and use the location of the query to get the number of records in the query results.

 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 be very slow   Query.last (); NumRows  = query.at () + 1   
 

You can call Qsqlquery::setforwartonly (true)before calling exec () If you are using only the next () and Seek () of the positive parameter when traversing the query result set. When you are manipulating a large number of result sets, this is a simple optimization order that can significantly speed up your query.

3 inserting, updating, and deleting records

Qsqlquery can execute arbitrary SQL statements. The following example shows inserting a record into a table using the INSERT statement:

qsqlquery query;query.exec (""           " VALUES (1001, ' Thad Beaumont ', 65000) ");

If you want to insert many records at the same time, a valid method is to separate the query statements from the real values, which can be implemented using placeholders. QT supports two types of placeholders: name binding and location binding. The following is an example of a name binding:

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 ();

The following code is an example of location binding:

 qsqlquery query;query.prepare (  " Span style= "color: #800000;" >insert into employee (ID, name, salary)   "  " values (?,?,?)   "  1001    thad Beaumont   ); Query.addbindvalue ( 65000   

Both of these statements are supported for all database drivers provided by QT. When you need to insert multiple records, you only need to call Qsqlquery::p repare (), and then use multiple bindvalue () or Addbindvalue () to bind the required data, and then call the EXEC () function again.

Aside from performance, one of the benefits of using placeholders is that you can specify any value without worrying about missing special characters.

Updating a record is very similar to inserting an operation:

qsqlquery query;query.exec ("UPDATE employee SET salary = 70000 WHERE id = 1003");

You can also use a name or location binding to correlate the actual parameter values.

Finally, a DELETE statement is given below:

qsqlquery query;query.exec ("DELETE from employee WHERE ID = 1007");

4 Business

If the underlying database engine supports transactions, then qsqldriver::hasfeature (qsqldriver::transactions) returns True. You can use Qsqldatabase::transaction () to start a transaction and then write some SQL statements that you want to execute in the transaction, and finally call Qsqldatabase::commit () or Qsqldatabase::rollback (). When you use a transaction, you must start the transaction before you create the query, for example:

qsqldatabase::d atabase (). transaction (); Qsqlquery Query;query.exec ("SELECT ID from employee WHERE name = ' Torild Halvorsen '");if(Query.next ()) {intEmployeeId = Query.value (0). ToInt (); Query.exec ("INSERT into project (ID, name, ownerid)"               "VALUES (201, ' Manhattan Project ',"+ Qstring::number (employeeId) +')');} Qsqldatabase::d atabase (). commit ();

Transactions can guarantee the atomicity of a complex operation, that is, for a sequence of database operations, these operations are either all done or not, and it is an inseparable unit of work.

Qtsql Study Notes (3)-Execute SQL statements