#include <QtSql>
QT += sql
QSqlDatabase Class implements the operation of database connection
QSqlQuery Classes are used for execution SQL Sentence
QSqlRecord Class encapsulates all records in the database
//First: QSqlDatabase class
QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE"); Use QSQLITE Database, QMYSQL What's wrong with the database?
db.setHostName("localhost"); //Setting the database host name
db.setDatabaseName("test"); //Setting the database name
db.setUserName("root"); //Setting the database login user name
db.setPassword("123456"); //Design database login password
db.open()Open database connection
db.close();//Release database
QStringList drivers = QSqlDatabase::drivers();//Static member functions are member functions of classes, not objects. Returns a list of all available database drivers
drivers.removeAll("QMYSQL3"); //Delete items from the list
foreach(QString driver, drivers) //Traversing database drivers, testing database drivers
qDebug() << "\t" << driver;
//Establishing database files
QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
db.setDatabaseName("database.db");
if(!db.open()) return false;
QSqlQuery query;
query.exec("create table Login(id INTEGER PRIMARY KEY autoincrement, " //id will automatically increase because it cannot be duplicated here, otherwise the database cannot be modified
"goods int, store int, time nvarchar(50), type int, place nvarchar(50),temperature nvarchar(10) )");
Second: QSqlQuery class, query database, insert values into database and other operational databases
QSqlQuery query;
query.prepare("INSERT INTO T_USER (name, age) VALUES (:name, :age)"); //Prepare to execute SQL queries
query.bindValue(":name", "justin"); //Determine the value of the binding on this placeholder
query.bindValue(":age", 33);
query.exec();
QSqlQuery query; //The following executes related QSL statements
query.exec("create table student(id int primary key,name varchar)");
//Create a new student table with id as the primary key and a name entry
query.exec("insert into student values(1,'xiaogang')");
query.exec("insert into student values(2,'xiaoming')");
query.exec("insert into student values(3,'xiaohong')");
//Insert three records into the table
query.exec("select * from student");
To query all the contents of the table. The "*" number in the SQL statement "select * from student" indicates all the attributes recorded in the query table. When query.exec("select * from student");
After this statement is executed, we get the corresponding execution result, because the result may be more than one record, so we call it result set.
while(query.next())
QString name = query.value(0).toString(); // Returns the index value of the "name" field "justin",value(i) Returns the value of the I field, 0 represents name, 1 represents age.
int id = query.value(1).toInt();
See (int n): query points to the n-th record of the result set. Specify the current location
first(): query points to the first record of the result set.
last(): query points to the last record in the result set.
next(): query points to the next record, and each time the function is executed, it points to the next adjacent record.
previous(): query points to the previous record, and every time the function is executed, it points to the next record.
record(): Get the record that you are pointing to now.
value(int n): Gets the value of the attribute. Where n denotes the nth attribute of your 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 ID attribute and value(1) returns the value of name attribute. This function returns data of type QVariant, and you can view QVariant in help about its correspondence with other types.
at()// Returns the location of the current query
int fieldNo = query.record().indexOf("name");//Returns the column number of "name"
int rowNum = query.at();
//Gets the number of the record pointed to by query in the result set
int columnNum = query.record().count();
//Gets the number of attributes (columns) in each record
int fieldNo = query.record().indexOf("name");
//Gets the number of the column in which the "name" attribute resides, the column number from left to right, and the number on the leftmost side is 0
int id = query.value(0).toInt();
//Get the value of the id attribute and convert it to int
QString name = query.value(fieldNo).toString();
//Get the value of the name attribute
qDebug() << "rowNum is : " << rowNum //Output the results
<< " id is : " << id
<< " name is : " << name
<< " columnNum is : " << columnNum;
}
if(query.seek(2)) //seek specifies the current location
if(query.seek(ui->spinBox->value()))
{
qDebug() << query.value(0).toInt() << query.value(1).toString();
while(query.next()) //Each time the function is executed, it points to the next adjacent record.
{
qDebug() << query.value(0).toInt() << query.value(1).toString(); //value(i) returns the value of the I field, 0 for id and 1 for name
}
}
//Batch Operating Function - Batch Insertion into Database
QSqlQuery q;
q.prepare("insert into student values (?, ?)");
QVariantList ints; //QVariantList==QList<QVariant>
ints << 10 << 11 << 12 << 13;
q.addBindValue(ints); //binding
QVariantList names;
names << "xiaoming" << "xiaoliang" << "xiaogang" << QVariant(QVariant::String);
//The last one is an empty string, which should be in the same format as the previous one.
q.addBindValue(names);
if (!q.execBatch()) //Batch processing and output errors if errors occur
qDebug() << q.lastError();
Transactional operations - operation functions transaction(), commit() commit, rollback() rollback
Transaction is an important function of database. The so-called transaction is a sequence of database operations defined by users. These operations are either done entirely or not at all. They are an inseparable unit of work.
In Qt, transaction() is used to start a transaction operation, and commit() function or rollback() function is used to finish it. commit() denotes commit, that is, all operations of committing a transaction.
Specifically, all updates to the database in the transaction are written back to the database, and the transaction ends normally. rollback() means rollback, i.e. a failure occurs during the operation of a transaction and the transaction cannot continue.
The system revokes all completed operations on the database in the transaction and rolls back to the state at the beginning of the transaction.
If (QSqlDatabase:: database (). driver ()-> hasFeature (QSqlDriver:: Transactions)// / first determine whether the database supports transactional operations. HasFeature is a QSQLDriver class function
If (QSqlDatabase:: database (). transaction ()// Start Transaction Operation
QModelIndex class --Used to define data in a data model
//Third: QSqlQueryModel class read-only data model is database result set
//Function: void QSqlQuery Model:: setQuery (const QString & query, const QSqlDatabase & DB = QSqlDatabase ()// Execute access query from a given database db
setHeaderData() //Setting Horizontal Header Title
columnCount(); //Get the number of columns
columnCount(); //Get the number of columns
QSqlRecord QSqlQueryModel::record ( int row ) const //Returns the information contained in row rows
QModelIndex QAbstractItemModel::index ( int row, int column, const QModelIndex & parent = QModelIndex() ) //Returns the determined index of the specified row and column
index.data() //Returns the value of the index index
1.
QSqlQueryModel *model = new QSqlQueryModel;
model->setQuery("select * from student"); //Query the contents of the entire student table to access a given database
model->setHeaderData(0, Qt::Horizontal, tr("id"));
model->setHeaderData(1, Qt::Horizontal, tr("name"));
QTableView *view = new QTableView;
view->setModel(model); //Redefining the model, the model inserts data directly from the database.db
view->show();
2.
int column = model->columnCount(); //Get the number of columns
int row = model->rowCount(); // Get the number of rows
QSqlRecord record = model->record(1); //Get a record
QModelIndex index = model->index(1,1); //Get the value of an attribute 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();
3.
QSqlQuery query = model->query(); //Return the model associated with QSqlQuery
query.exec("insert into student values (10,'yafei10')"); //Insert a record into the model
model->setQuery("select * from student"); //Query the whole table again
view->show(); //If you display it again, you may not write this sentence.
- Enable the database created by QSqlQueryModel class to read and write inherited QAbstractItemModel class
As we said at the beginning, this model is read-only by default, so we can't modify the contents of the table in the window. But we can create our own model, and then display and modify the data according to our own wishes.
To make it readable and writable, you need to inherit your class from QSqlQueryModel and rewrite setData() and flags() functions. If we want to change the display of data, we need to rewrite the data() function.
bool QAbstractItemModel::setData ( const QModelIndex & index, const QVariant & value, int role = Qt::EditRole ) //Set the value indexed by index
Qt::ItemFlags QAbstractItemModel::flags ( const QModelIndex & index ) const //Returns the flag of a given index index
QVariant QAbstractItemModel::data ( const QModelIndex & index, int role = Qt::DisplayRole ) const //Returns the values determined by index and role (display status)
Qt::ItemFlags MySqlQueryModel::flags(
const QModelIndex &index) const //Returns whether the table is changeable
{
Qt::ItemFlags flags = QSqlQueryModel::flags(index);
if (index.column() == 1) //The second property is changeable
flags |= Qt::ItemIsEditable; //flags can be edited, so the second column can be edited
return flags;
}
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) //Qt::TextColorRole is the first column to determine the color equal to 9.
return qVariantFromValue(QColor(Qt::red)); //The font color of the first attribute is red
return value;
}
//Fourth: QSqlQueryModel inherits the QSqlQueryModel class, which provides an editable data model for reading and writing single SQL tables, with the functions of modifying, inserting, deleting, querying, and sorting.
QSqlTableModel *model = new QSqlTableModel;
virtual bool removeColumns ( int column, int count, const QModelIndex & parent = QModelIndex() ) //Model - > removeColumns (0) Delete the first column
bool QSqlTableModel::submitAll ()，//Submit all the modified data, and then the modified data is saved in the database.
void QSqlTableModel::revertAll () //Undo all modifications, but if the database has been submitted for modification, it cannot be changed back by undoing the modification.
void QSqlTableModel::setFilter ( const QString & filter ) //Filter, filter the database according to the string filter
bool QSqlTableModel::select () //Under the condition of screening and sorting, the database is displayed in the mode table which meets the requirements.
void QSqlTableModel::setSort ( int column, Qt::SortOrder order ) //Sort operation. Sort by column and Qt::SortOrder. Qt::SortOrder has ascending and descending order
virtual void revertRow ( int row ) //Delete rows
bool insertRow ( int row, const QModelIndex & parent = QModelIndex() ) //Insert rows and columns
model->setEditStrategy(QSqlTableModel::OnManualSubmit); //Setting the save policy to submit manually, OnManual Submit indicates that we need to submit changes to make them effective.
1. //Display database data in tableview table
model = new QSqlTableModel(this);
model->setTable("student"); //Setting up the "student" database table
model->setEditStrategy(QSqlTableModel::OnManualSubmit); //Set the save policy to submit manually
model->select(); //Select all rows of the entire table
// Model - > removeColumn (1); // does not display the name attribute column, and if records are added at this time, the value of the attribute cannot be added.
ui->tableView->setModel(model); //Redefining the model, the model inserts data directly from the database.db
//UI - > tableView - > setEdit Triggers (QAbstractItemView:: No Edit Triggers); // Make it non-editable
2. //When the tableview is modified, bool QSqlTableModel::submitAll() is saved by submitAll() function, all the modified data is submitted, and then the modified data is saved in the database.
model->database().transaction(); //Start Transaction Operation
if (model->submitAll()) { //Submit all the modified data, and then the modified data is saved in the database.
model->database().commit(); //Submission
} else {
model->database().rollback(); //RollBACK
QMessageBox::warning(this, tr("tableModel"),
tr("Database error: %1″)
.arg(model->lastError().text()));
}
model->revertAll(); //Revocation
3. Query operation--void QSqlTableModel::setFilter ( const QString & filter ) //screen
QString name = ui->lineEdit->text();
model->setFilter(QObject::tr("name = '%1′").arg(name)); //Screening by name
model->select(); //Display result
4.Sort operation //
model->setSort(0,Qt::AscendingOrder); //id attribute, column 0, ascending order
model->select();
model->setSort(0,Qt::DescendingOrder);
model->select();
5.Delete row
// int curRow = ui->tableView->currentIndex().row();
//Get the selected row
// Model - > removeRow (curRow); // Delete a line
//Delete the row
QItemSelectionModel *selections = ui->tableView->selectionModel(); //Returns the current selection mode
QModelIndexList selected = selections->selectedIndexes(); //Returns an index list of all selected model items
foreach (QModelIndex index, selected)
{
int curRow=index.row();
model->removeRow(curRow); //Delete all selected rows
}
int ok = QMessageBox::warning(this,tr("Delete the current line!"),tr("Are you sure?"
"Can you delete the current one?"),
QMessageBox::Yes,QMessageBox::No);
if(ok == QMessageBox::No)
{
model->revertAll(); //If not deleted, revoke
}
else model->submitAll(); //Otherwise commit, delete the row in the database
6.Insert operation//Insert row
int rowNum = model->rowCount(); //Get the number of rows in the table
int id = 10;
model->insertRow(rowNum); //Add a row
model->setData(model->index(rowNum,0),id); //Add id attribute values to new rows
//Model - > submitAll (); // can be submitted directly
QSqlRelationalTableModel->Inherits QSqlTableModel->Inherits QSqlQueryModel->Inherits QAbstractTableModel->Inherits QAbstractItemModel->Inherits QObjec
Fifth: QSqlRelational Table Model - This class provides an editable data model for a single database table that supports foreign keys, except that it is no different from QSqlTableModel.
Model - > setRelation (2, QSqlRelation ("course", "id", "name"); // Set foreign key
// / Set the third attribute of the student table as the foreign key of the ID attribute of the coursetable and display it as the value of the name attribute of the coursetable (the coursetable is shown as the name attribute value on the id)
If the user changes the course attributes, he can only choose among the courses in the course schedule, instead of filling in the course at will. The QSqlRelationalDelegate delegate class in Qt implements this function
ui->tableView->setItemDelegate(new QSqlRelationalDelegate(ui->tableView));
QSqlRelationalDelegate class--Delegate delegate Used to display edits QSqlRelationalTableModel class
QTableView *view = new QTableView;
view->setModel(model);
view->setItemDelegate(new QSqlRelationalDelegate(view));