Qt database (iii) use the qsqlquery class to execute SQL statements (I)

This article was originally reproduced at www.yafeilinux.com. Please indicate the source.

SQL is the structured query language, which is the standard language for relational databases. As mentioned above, the qsqlquery class is used in QT to execute SQL statements. It should be noted that here we are only a QT tutorial, rather than a non-professional database tutorial, so we will not explain in depth some knowledge in the database. Below we will only explain the most common knowledge points.

Let's create a project and then explain the four knowledge points:

1. Operate the result set returned by the SQL statement.

2. Use variables in SQL statements.

3. batch processing.

4. Transaction operations.

Create a qt4 GUI application project. Here, the project name is query. Then, select the qtsql module and select qwidget as the base class. After the project is created, add the C ++ header file named connection. H. Change the file content as follows:

# Ifndef connection_h
# Define connection_h
# Include <qmessagebox>
# Include <qsqldatabase>
# Include <qsqlquery>

Static bool createconnection ()
{
Qsqldatabase DB = qsqldatabase: adddatabase ("qsqlite ");
DB. setdatabasename (": Memory :");
If (! DB. open ()){
Qmessagebox: Critical (0, qapp-> tr ("cannot open database "),
Qapp-> tr ("unable to establish a database connection ."
), Qmessagebox: Cancel );
Return false;
}

Qsqlquery query;
Query.exe C ("create table student (ID int primary key ,"
"Name varchar (20 ))");
Query.exe C ("insert into student values (0, 'first ')");
Query.exe C ("insert into student values (1, 'second ')");
Query.exe C ("insert into student values (2, 'third ')");
Query.exe C ("insert into student values (3, 'fourth ')");
Query.exe C ("insert into student values (4, 'second th ')");

Return true;
}
# Endif // connection_h

Then, modify main. cpp as follows:

# Include <qtgui/qapplication>
# Include "widget. H"
# Include "connection. H"

Int main (INT argc, char * argv [])
{
Qapplication A (argc, argv );

If (! Createconnection ())
Return 1;

Widget W;
W. Show ();
Return a.exe C ();
}

We can see that we open the database in the main function, and the database connection is completed using a function and put it in a file separately. This method makes the Winner Function very concise. We will use this method when using databases in the future. After we opened the database connection, we created a new student table and inserted several records in it.

A row in a table is called a record, and a column is an attribute. This table has five records, ID and name attributes. The "id int primary key" in the program indicates that the ID attribute is the primary key, that is, when a record is added later, an ID item must exist.

Next we open the widget. UI file and add a push button and a spin box to the interface in the designer. Change the text of the button to "query", and click the event slot function, as shown in the following figure.

Void Widget: on_pushbutton_clicked ()
{
Qsqlquery query;
Query.exe C ("select * from student ");
While (query. Next ())
{
Qdebug () <query. Value (0). toint () <query. Value (1). tostring ();
}
}

Add the header file in widget. cpp:

# Include <qsqlquery>
# Include <qtdebug>

Then run the program and click "query". The effect is as follows:

In the output window, all contents in the table are output. This indicates that our database connection has been established successfully.

1. Operate the result set returned by the SQL statement.

In the explain program, we use query.exe C ("select * from student"); to query all the contents in the table. In the SQL statement "select * from student", "*" indicates all the attributes recorded in the query table. And when query.exe C ("select * from student"); after this statement is executed, we get the corresponding execution result, because there may be more than one record, so we call it a result set.

The result set is actually a set of all records queried. In the qsqlquery class, multiple functions are provided to operate on this set. Note that the records in this set are numbered from 0. The most common ones are:

Seek (int n): The query points to the nth record of the result set.

First (): The query points to the first record of the result set.

Last (): Query points to the last record of the result set.

Next (): Query points to the next record. Every time this function is executed, it points to the next adjacent record.

Previous (): Query points to the previous record. Every time this function is executed, it points to the adjacent previous record.

Record (): Obtain the record pointing.

Value (int n): Get the attribute value. N indicates the nth attribute you query. For example, if we use "select * from student" above, it is equivalent to "select ID, name from student", then value (0) returns the value of the ID attribute. Value (1) returns the value of the name attribute. This function returns data of the qvariant type. For the ing between this type and other types, you can view qvariant in help.

At (): Obtain the number of records pointed to by the current query in the result set.

Explain: After query.exe C ("select * from student") is executed, query points out of the result set. We can use query. next (). When this code is executed for the first time, the query points to the first record in the result set. Of course, we can also use the seek (0) function or the first () function to point the query to the first record of the result set. However, to save memory consumption, the recommended feature is to add query. setforwardonly (true) before query.exe C ("select * from student"); this code can only use the next () and seek () functions.

The slot function of the "query" button is changed as follows:

Void Widget: on_pushbutton_clicked ()
{
Qsqlquery query;
Query.exe C ("select * from student ");
Qdebug () <"Exec next ():";
If (query. Next ())
// Execute the next () function once at the beginning, then the query points to the first record of the result set.
{
Int rownum = query. ();
// Obtain the number of records pointed to by the query in the result set
Int columnnum = query. record (). Count ();
// Obtain the number of attributes (column) in each record
Int fieldno = query. record (). indexof ("name ");
// Obtain the number of the column where the "name" attribute is located. The column is numbered from left to right, And the leftmost column is numbered 0.
Int id = query. Value (0). toint ();
// Obtain the value of the ID attribute and convert it to the int type
Qstring name = query. Value (fieldno). tostring ();
// Obtain the value of the name attribute
Qdebug () <"rownum is:" <rownum // output the result
<"Id is:" <ID
<"Name is:" <name
<"Columnnum is:" <columnnum;
}
Qdebug () <"Exec seek (2 ):";
If (query. Seek (2 ))
// Locate the record numbered 2 in the result set, that is, the third record, because the number of the first record is 0.
{
Qdebug () <"rownum is:" <query. ()
<"Id is:" <query. Value (0). toint ()
<"Name is:" <query. Value (1). tostring ();
}
Qdebug () <"Exec last ():";
If (query. Last ())
// Locate the last record in the result set
{
Qdebug () <"rownum is:" <query. ()
<"Id is:" <query. Value (0). toint ()
<"Name is:" <query. Value (1). tostring ();
}
}

Then add the header file to the widget. cpp file.

# Include <qsqlrecord>

Run the program. The result is as follows: