Qt database (5) qsqlquerymodel

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

At the end of the previous article, we mentioned that QT uses its own mechanism to avoid using SQL statements. It provides us with a simpler database operation and data display model. They are read-only qsqlquerymodel, qsqltablemodel for a single table, and qsqlrelationaltablemodel that supports foreign keys. This time, we will first explain the qsqlquerymodel.

The qsqlquerymodel class provides a read-only data model for the SQL result set. Next we will use this class for the simplest operation.

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

# Ifndef database_h
# Define database_h

# Include <qsqldatabase>
# Include <qsqlquery>

Static bool createconnection ()
{
Qsqldatabase DB = qsqldatabase: adddatabase ("qsqlite ");
DB. setdatabasename ("database. DB ");
If (! DB. open () return false;
Qsqlquery query;
Query.exe C ("create table student (ID int primary key, name vchar )");
Query.exe C ("insert into student values (0, 'yafei0 ′)");
Query.exe C ("insert into student values (1, 'yafei1 ′)");
Query.exe C ("insert into student values (2, 'yafei2 ′)");
Return true;
}

# Endif // database_h

Here we use dB. setdatabasename ("database. DB "); instead of using the previous memory database, we use real files so that the operations performed on the database can be saved.

Go to main. cpp and change the content as follows:

# Include <qtgui/qapplication>
# Include "widget. H"
# Include "database. h"
Int main (INT argc, char * argv [])
{
Qapplication A (argc, argv );
If (! Createconnection ())
Return 1;
Widget W;
W. Show ();
Return a.exe C ();
}

In the widget. UI, add a push button that is displayed as "query" and click the event slot function. The changes are as follows:

Void Widget: on_pushbutton_clicked ()
{
Qsqlquerymodel * model = new qsqlquerymodel;
Model-> setquery ("select * from student ");
Model-> setheaderdata (0, QT: horizontal, TR ("ID "));
Model-> setheaderdata (1, QT: horizontal, TR ("name "));
Qtableview * view = new qtableview;
View-> setmodel (model );
View-> show ();
}

We created the qsqlquerymodel class object model, and used the setquery () function to execute the SQL statement "(" select * from student ");" to query the content of the entire student table, this class does not completely avoid SQL statements. Then we set the name when the table attribute is displayed. Finally, a view is created and the model is associated with the view, so that the data in the database can be displayed in the table in the window.

Add the header file in widget. cpp:

# Include <qsqlquerymodel>
# Include <qtableview>

Run the program and press the "query" button. The effect is as follows:

We can view the database files in the project folder:

Next we will use this model to operate the database.

1. Add the following code to the void Widget: on_pushbutton_clicked () function:

Int column = model-> columncount (); // obtain the number of Columns
Int ROW = model-> rowcount (); // obtain the number of rows
Qsqlrecord record = model-> record (1); // obtain a record
Qmodelindex Index = model-> index (); // obtain the attribute value of a record.
Qdebug () <"column num is:" <column <Endl
<"Row num is:" <row <Endl
<"The second record is:" <record <Endl
<"The data of index (1, 1) is:" <index. Data ();

Add the header file in widget. cpp:

# Include <qsqlrecord>
# Include <qmodelindex>
# Include <qdebug>

Run the program as follows:

2. Of course, we can also use the query statements described earlier to execute SQL statements here.

For example, add the following code to the void Widget: on_pushbutton_clicked () function:

Qsqlquery query = model-> query ();
Query.exe C ("Select name from student where id = 2");
Query. Next ();
Qdebug () <query. Value (0). tostring ();

In this way, the values in the table can be output, and you can run the program to test it.

3. Change the function as follows.

Void Widget: on_pushbutton_clicked ()
{
Qsqlquerymodel * model = new qsqlquerymodel;
Model-> setquery ("select * from student ");
Model-> setheaderdata (0, QT: horizontal, TR ("ID "));
Model-> setheaderdata (1, QT: horizontal, TR ("name "));
Qtableview * view = new qtableview;
View-> setmodel (model );
View-> show ();

Qsqlquery query = model-> query ();
Query.exe C ("insert into student values (10, 'yafei10 ′)");
// Insert a record
}

At this time, we run the program, the effect is as follows:

We found that no records were added to the table. Why?

We close the program and run it again. The effect is as follows:

We found that this new record has been added. We executed the SQL statement for adding records above, but it was displayed before the record was added, so our update was not displayed dynamically. To enable it to dynamically display our updates, we can change the function as follows:

Void Widget: on_pushbutton_clicked ()
{
Qsqlquerymodel * model = new qsqlquerymodel;
Model-> setquery ("select * from student ");
Model-> setheaderdata (0, QT: horizontal, TR ("ID "));
Model-> setheaderdata (1, QT: horizontal, TR ("name "));
Qtableview * view = new qtableview;
View-> setmodel (model );
View-> show ();

Qsqlquery query = model-> query ();
Query.exe C ("insert into student values (20, 'yafei20 ′)");
// Insert a record
Model-> setquery ("select * from student"); // query the entire table again
View-> show (); // display it again
}

In this case, run the program with the following effect:

As you can see, the newly added records are displayed.

As we mentioned at the beginning, this model is read-only by default, so we cannot modify the content in the table in the window. However, we can create our own models, display data and modify data as needed. To make it readable and writable, You need to inherit from the qsqlquerymodel class and rewrite the setdata () and flags () functions. If we want to change the display of data, we need to rewrite the data () function.

In the following example, the ID attribute column of the student table is displayed in red, and the name attribute column can be edited.

1. We add c ++ class in the project, and set the class name to mysqlquerymodel, and the base class to qsqlquerymodel, as shown below:

2. modify the content in mysqlquerymodel. h as follows:

Class mysqlquerymodel: Public qsqlquerymodel
{
Public:
Mysqlquerymodel ();
// The following three functions are all virtual functions. We need to overload them.
Qt: itemflags flags (const qmodelindex & Index) const;
Bool setdata (const qmodelindex & Index, const qvariant & Value, int role );

Qvariant data (const qmodelindex & item, int role = QT: displayrole) const;
//
PRIVATE:
Bool setname (INT studentid, const qstring & name );
Void refresh ();
};

Then, change the mysqlquerymodel. cpp file as follows:

# Include "mysqlquerymodel. h"
# Include <qsqlquery>
# Include <qcolor>

Mysqlquerymodel: mysqlquerymodel ()
{
}

Qt: itemflags mysqlquerymodel: Flags (
Const qmodelindex & Index) const // returns whether the table can be changed
{
Qt: itemflags flags = qsqlquerymodel: Flags (INDEX );
If (index. Column () = 1) // The second attribute can be changed.
Flags | = QT: itemiseditable;
Return flags;
}

Bool mysqlquerymodel: setdata (const qmodelindex & Index, const qvariant & Value, INT/* role */)
// Add data
{
If (index. Column () <1 | index. Column ()> 2)
Return false;

Qmodelindex primarykeyindex = qsqlquerymodel: Index (index. Row (), 0 );
Int id = data (primarykeyindex). toint (); // obtain the ID number.

Clear ();

Bool OK;
If (index. Column () = 1) // The second attribute can be changed.
OK = setname (ID, value. tostring ());

Refresh ();
Return OK;
}

Void mysqlquerymodel: refresh () // update display
{
Setquery ("select * from student ");
Setheaderdata (0, QT: horizontal, qobject: TR ("ID "));
Setheaderdata (1, QT: horizontal, qobject: TR ("name "));
}

Bool mysqlquerymodel: setname (INT studentid, const qstring & name) // Add the value of the name attribute
{
Qsqlquery query;
Query. Prepare ("Update student set name =? Where id = ?");
Query. addbindvalue (name );
Query. addbindvalue (studentid );
Return query.exe C ();
}

Qvariant mysqlquerymodel: Data (const qmodelindex & Index, int role) const
// Change the data display style
{
Qvariant value = qsqlquerymodel: Data (index, role );

If (role = QT: textcolorrole & Index. Column () = 0)
Return qvariantfromvalue (qcolor (QT: Red); // The font color of the first attribute is red.
Return value;
}

Add the header file # include "mysqlquerymodel. h" to the widget. cpp file"

Then, change the function as follows:

Void Widget: on_pushbutton_clicked ()
{
Qsqlquerymodel * model = new qsqlquerymodel;
Model-> setquery ("select * from student ");
Model-> setheaderdata (0, QT: horizontal, TR ("ID "));
Model-> setheaderdata (1, QT: horizontal, TR ("name "));
Qtableview * view = new qtableview;
View-> setmodel (model );
View-> show ();

Mysqlquerymodel * mymodel = new mysqlquerymodel; // create an object of your own Model
Mymodel-> setquery ("select * from student ");
Mymodel-> setheaderdata (0, QT: horizontal, TR ("ID "));
Mymodel-> setheaderdata (1, QT: horizontal, TR ("name "));
Qtableview * view1 = new qtableview;
View1-> setwindowtitle ("mysqlquerymodel"); // modify the window title
View1-> setmodel (mymodel );
View1-> show ();
}

The running effect is as follows:

We can see that our results have come out.