catalogue
Introduction to common classes of database operation in QT
About parameterized SQL statements
Named based parameterized SQL statements
Location based parameterized SQL statements
On the efficiency of implementation
General process steps for accessing database in QT
General steps of adding, deleting and modifying
Background introduction
I have used PHP/C#/VB/VB.NET/JAVA and other programming languages to develop applications based on database. Compared with other programming languages, I feel that QT's database access framework is a little confused in design, so I was a little confused when I first used it. In the process of using QT to develop applications many times later, QT's database access framework was slowly used.
Functional comparison
Generally speaking, the framework involving database access will have the following functional classes: database driven management class, database connection class, database operation class for adding, deleting, modifying and querying, class for receiving the result set returned from query operation, database transaction management class and database exception class.
For example, the approximate correspondence between Java and C # is as follows:
| Java | C# | |
| Database driver layer | java.sql.Driver | System.Data.Common.DbProviderFactory |
| Database connection layer | java.sql.Connection | System.Data.Common.DbConnection |
| Database execution layer | java.sql.Statement java.sql.PreparedStatement | System.Data.Common.DbCommand System.Data.Common.DbDataAdapter |
| Result set presentation layer | java.sql.ResultSet | System.Data.Common.DataReader System.Data.Common.DataTable System.Data.DataRow System.Data.DataColumn |
| Database transaction layer | java.sql.Connection.commit(); java.sql.Connection.rollback(); | System.Data.Common.DbTransaction.commit() System.Data.Common.DbTransaction.rollback() |
| Database exception | java.sql.SQLException | System.Data.Common.DbException |
Note: most interfaces defined in Java SE need specific database implementation. In C#, Microsoft provides interfaces (under the System.Data namespace), abstract classes (under the System.Data.Common namespace) and implementation classes for some specific databases. In the above table, the C# part is mainly abstract classes. In addition, due to different languages, it is difficult to strictly fill the relevant classes or interfaces in the above table, which can only be approximate.
QT's database programming access system mainly provides QSqlDatabase, QSqlDriver, QSqlError, QSqlField, QSqlIndex, QSqlQuery, QSqlQueryModel, QSqlRecord, QSqlResult and QSqlTableModel classes. In addition to the above classes, there are also QSqlDriverCreator, QSqlDriverCreatorBase and QSqlRelationalTableModel classes. If divided according to the above functions, the table can be basically understood:
| QT | explain | |
| Database driver layer | QSqlDriver | |
| Database connection layer | QSqlDatabase | Qsqldatabase.exec (const qstring & Query = qstring()) can execute SQL statements directly |
| Database execution layer | QSqlQuery | |
| Result set presentation layer | QSqlRecord QSqlField QSqlIndex QSqlResult QSqlQueryModel QSqlTableModel | |
| Database transaction layer | QSqlDatabase.commit(); QSqlDatabase.rollback(); | |
| Database exception | QSqlError |
Introduction to common classes of database operation in QT
In QT's help document, QT divides the above database related classes into three levels. The lowest level is the driver layer. The classes at this level include QSqlDriver, QSqlDriverCreator, QSqlDriverCreatorBase, etc. above the driver layer is the SQL interface layer. The classes at this level include QSqlDatabase, QSqlQuery, QSqlField, QSqlRecord, QSqlIndex, QSqlError, etc, Above the SQL interface layer is the user interface layer. The classes of the user interface layer include QSqlQueryModel, QSqlTableModel and QSqlRelationalTableModel.
The functions of the above classes are described as follows:
| Serial number | class | explain |
| 1 | QSqlDriver | This class will not be used directly unless you want to develop your own database driver. It is an abstract class used to access a specific database. |
| 2 | QSqlDatabase | The class used to connect to the database provides functions such as database connection, access and transaction control. It also supports obtaining the currently supported drivers (QSqlDatabase::drivers()), directly supporting SQL statements (qsqldatabase.exec (const qstring & Query = qstring()), and obtaining the primary key of the specified table (QSqlDatabase:: primaryindex (const qstring & tablename)) Obtain the field information (QSqlDatabase:: record (const qstring & tablename)) of the specified table. |
| 3 | QSqlQuery | The class used to execute SQL statements supports the execution of parameterized SQL statements through the overloaded form of QSqlQuery::boundValue(). Return a row of records in the result set through QSqlQuery::record() method, return the result set through QSqlResult *QSqlQuery::result(), and return the value of the specified field through QSqlQuery::value() overload. |
| 4 | QSqlRecord | A class used to encapsulate database records. QSqlRecord encapsulates a row of records in a database table or view. A QSqlRecord may contain 0 to multiple qsqlfields. You can use the overloaded form of QSqlRecord::field() to obtain the qsqlfields contained in QSqlRecord, or use the overloaded form of QSqlRecord::value() to obtain the values of each field of QSqlRecord (the return value is QVariant, which needs to be converted according to the actual field type) |
| 5 | QSqlField | It is used to encapsulate fields in database tables or views. Through QSqlField class, you can obtain information such as field name, table name, whether it is allowed to be empty, default value, field length, whether it is empty, field type, field value, etc. |
| 6 | QSqlIndex | The class used to encapsulate the database index. Through the QSqlIndex class, you can obtain information such as the name of the index and whether the index is in descending or ascending order. |
| 7 | QSqlResult | It is an abstract class used to encapsulate database specific data. Generally, QSqlQuery should be used instead of QSqlResult. QSqlQuery provides a layer of encapsulation based on QSqlResult. If you need to implement your own database driver, you need to implement your own QSqlResult class. Generally, try to use QSqlQuery. |
| 8 | QSqlError | The class used to encapsulate database error information can determine whether a database error has occurred through QSqlError::isValid(). In case of an error, you can obtain the error reported by the database through QSqlError::databaseText(), the error reported by the driver through QSqlError::driverText(), and the error reported by the driver through QSqlError::nativeErrorCode() Get the local error code at the database level. QSqlError::nativeErrorCode() is used to get the database error division at the QT level. There is another convenient method to get the text information of database error description. QSqlError::nativeErrorCode(), which directly connects QSqlError::databaseText() and QSqlError::driverText() into a statement. |
Usage example
Data preparation
Create a MySQL database and create the table tbluserinfo in the database. The SQL statement to create the tbluserinfo table is as follows:
CREATE TABLE `tbluserinfo` (
`Id` INT(11) NOT NULL AUTO_INCREMENT,
`OrgnizationId` VARCHAR(20) NOT NULL COLLATE 'utf8_general_ci',
`AccountName` VARCHAR(20) NOT NULL COLLATE 'utf8_general_ci',
`RealName` VARCHAR(20) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
`Password` VARCHAR(50) NOT NULL COLLATE 'utf8_general_ci',
`LoginCount` INT(11) NOT NULL,
`Email` VARCHAR(20) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
`Telephone` VARCHAR(20) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
`CreateAt` DATETIME NOT NULL,
`LoginAt` DATETIME NULL DEFAULT NULL,
`IsValid` INT(1) NOT NULL,
`Json` TEXT NULL DEFAULT NULL COLLATE 'utf8_general_ci',
PRIMARY KEY (`Id`) USING BTREE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
ROW_FORMAT=DYNAMIC;Create entity class
The entity class is divided into UserInfo.h file and UserInfo.cpp file in a conventional way. The contents of UserInfo.h file are as follows:
#ifndef USERINFO_H
#define USERINFO_H
#include <QString>
#include <QDateTime>
/**
* @brief The UserInfo class Entity class corresponding to user table
*/
class UserInfo
{
private:
int m_id;
QString m_accountName;
QString m_realName;
QString m_password;
int m_loginCount;
QString m_email;
QString m_telephone;
QDateTime m_createAt;
QDateTime m_loginAt;
int m_isValid;
QString m_json;
QString m_orgnization;
public:
UserInfo();
int id() const;
void setId(int id);
QString accountName() const;
void setAccountName(const QString& accountName);
QString realName() const;
void setRealName(const QString& realName);
QString password() const;
void setPassword(const QString& password);
int loginCount() const;
void setLoginCount(int loginCount);
QString email() const;
void setEmail(const QString& email);
QString telephone() const;
void setTelephone(const QString& telephone);
QDateTime createAt() const;
void setCreateAt(const QDateTime& createAt);
QDateTime loginAt() const;
void setLoginAt(const QDateTime& loginAt);
int isValid() const;
void setIsValid(int isValid);
QString json() const;
void setJson(const QString& json);
QString orgnization() const;
void setOrgnization(const QString& orgnization);
bool operator ==(const UserInfo& other) const
{
return this->m_id == other.m_id;
}
};
#endif // USERINFO_H
The contents of UserInfo.cpp file are as follows:
#include "UserInfo.h"
int UserInfo::id() const
{
return m_id;
}
void UserInfo::setId(int id)
{
m_id = id;
}
QString UserInfo::accountName() const
{
return m_accountName;
}
void UserInfo::setAccountName(const QString& accountName)
{
m_accountName = accountName;
}
QString UserInfo::realName() const
{
return m_realName;
}
void UserInfo::setRealName(const QString& realName)
{
m_realName = realName;
}
QString UserInfo::password() const
{
return m_password;
}
void UserInfo::setPassword(const QString& password)
{
m_password = password;
}
int UserInfo::loginCount() const
{
return m_loginCount;
}
void UserInfo::setLoginCount(int loginCount)
{
m_loginCount = loginCount;
}
QString UserInfo::email() const
{
return m_email;
}
void UserInfo::setEmail(const QString& email)
{
m_email = email;
}
QString UserInfo::telephone() const
{
return m_telephone;
}
void UserInfo::setTelephone(const QString& telephone)
{
m_telephone = telephone;
}
QDateTime UserInfo::createAt() const
{
return m_createAt;
}
void UserInfo::setCreateAt(const QDateTime& createAt)
{
m_createAt = createAt;
}
QDateTime UserInfo::loginAt() const
{
return m_loginAt;
}
void UserInfo::setLoginAt(const QDateTime& loginAt)
{
m_loginAt = loginAt;
}
int UserInfo::isValid() const
{
return m_isValid;
}
void UserInfo::setIsValid(int isValid)
{
m_isValid = isValid;
}
QString UserInfo::json() const
{
return m_json;
}
void UserInfo::setJson(const QString& json)
{
m_json = json;
}
QString UserInfo::orgnization() const
{
return m_orgnization;
}
void UserInfo::setOrgnization(const QString& orgnization)
{
m_orgnization = orgnization;
}
UserInfo::UserInfo()
{
}Create database access class
The database access class is mainly responsible for dealing with the database. It usually takes the entity class or basic data type as the carrier. It is usually called DAO (Data Access Object, DAO) in Java and DAL (Data Access Layer, DAL) in C# developers. Generally speaking, in order to facilitate the maintenance of project code, a corresponding entity class and a corresponding database access class will be written for a table in the database. Here, the name of the database access class is QtDatabaseDemo, which is divided into the declaration file QtDatabaseDemo.h and the implementation file QtDatabaseDemo.cpp.
The code of QtDatabaseDemo.h is as follows:
#ifndef QTDATABASEDEMO_H
#define QTDATABASEDEMO_H
#include <QSql>
#include <QSqlDatabase>
#include <QSqlError>
#include <QSqlField>
#include <QSqlIndex>
#include <QSqlQuery>
#include <QSqlRecord>
#include <QDebug>
#include <QDateTime>
#include <QCryptographicHash>
#include <QString>
#include <QByteArray>
#include "UserInfo.h"
class QtDatabaseDemo
{
private:
/**
* @brief readUserInfo Convert a record in the result set into an entity class
* @param sqlRecord A record in the result set
* @param userInfo Entity class corresponding to result set
*/
void readUserInfo(QSqlRecord& sqlRecord, UserInfo& userInfo);
/**
* @brief readUserInfo Convert a record in the result set into an entity class, and traverse all the result sets through next()
* This method is slightly faster than using the QSqlRecord method,
* That is, the performance of readuserinfo (QSqlRecord & sqlrecord, userinfo & userinfo) is slightly lower because it also has a circular process of setting QSqlRecord
* @param sqlQuery QSqlQuery example
* @param userInfo Entity class corresponding to result set
*/
void readUserInfo(QSqlQuery& sqlQuery, UserInfo& userInfo);
public:
QtDatabaseDemo();
~QtDatabaseDemo();
/**
* @brief showCreateConnection Example of creating a database connection
* @param db QSqlDatabase example
*/
void showCreateConnection(QSqlDatabase& db);
/**
* @brief showTablesDetails Display database table information, including tables in the database and field information in each table
*/
void showTablesDetails(void);
/**
* @brief showInsert Insert Examples of
*/
void showInsert(void);
/**
* @brief showUpdate Update Examples of
*/
void showUpdate(void);
/**
* @brief showDelete Delete Examples of
*/
void showDelete(void);
/**
* @brief showQuery Query example
*/
void showQuery(void);
/**
* @brief queryUserInfo Query user information
* @param userId User Id
* @param userInfo Returned query results
*/
void queryUserInfo(int userId, UserInfo& userInfo);
/**
* @brief queryMaxUserId Query the maximum user id
* @return
*/
int queryMaxUserId(void);
/**
* @brief md5Hash Calculate md5 value on string
* @param src Original string
* @param dest Result string
*/
void md5Hash(QString& src, QString& dest);
};
#endif // QTDATABASEDEMO_HThe contents of the QtDatabaseDemo.cpp file are as follows:
#include "QtDatabaseDemo.h"
QtDatabaseDemo::QtDatabaseDemo()
{
}
QtDatabaseDemo::~QtDatabaseDemo()
{
}
void QtDatabaseDemo::showCreateConnection(QSqlDatabase& db)
{
QString connectionName = "QT_MySQL";//Database connection name
//The database type has a fixed format,
//Possible values are: QDB2, QIBASE, qmmysql, QOCI, QODBC, QPSQL, QSQLITE, QSQLITE2, QTDS
QString databaseType = "QMYSQL";
if (!QSqlDatabase::contains(connectionName))
{
//The registration type is qmmysql and the name is QT_ QSqlDatabase of MySQL
//QT available later_ MySQL gets the name of this method
db = QSqlDatabase::addDatabase(databaseType, connectionName);
db.setDatabaseName("dbName");//Database name, please set it according to the actual situation
db.setHostName("192.168.0.*");//Database server IP or host name, please set according to the actual situation
db.setPassword("password");//Please set the account password for connecting to the database server according to the situation
db.setPort(3306);//Please set the port number to connect to the database server according to the situation
db.setUserName("userName");//Please set the account name to connect to the database server according to the situation
}
else
{
//Direct QT_MySQL is obtained by this name, and the database connection is not opened by default
db = QSqlDatabase::database(connectionName, false);
}
}
void QtDatabaseDemo::showTablesDetails()
{
QSqlDatabase db;
//Initialize database connection
showCreateConnection(db);
//If the database driver is invalid, execution will not continue
if (!db.isValid())
{
qWarning() << "Database driver is not valid.";
return;
}
//If the database cannot be opened, the reason for the error is output
if (!db.open())
{
QSqlError sqlError = db.lastError();
if (sqlError.isValid())//If an error occurs, an error message is output
{
qWarning() << "Can not open database,error message is:" << sqlError.text();
}
return ;
}
//Gets the names of all tables in the current library
QStringList tableNames = db.tables(QSql::Tables);
//Traverse each table and output the primary key information and each field information in the table
for (QString tablename : tableNames)
{
qDebug() << QString("----------------------------") << tablename;
//Get primary key information
QSqlIndex sqlIndex = db.primaryIndex(tablename);
//If the primary key is set, the information of the primary key is output
if (!sqlIndex.isEmpty())
{
qDebug() << QString("Name:%1,cursorName:%2")
.arg(sqlIndex.name()).arg(sqlIndex.cursorName());
}
//Gets the information for each field in the table
QSqlRecord sqlRecord = db.record(tablename);
//If the table contains fields, the information of each field is output
if (!sqlRecord.isEmpty())
{
int count = sqlRecord.count();
//Loop out the information of each field
for (int i = 0; i < count; i++)
{
//Get field information by subscript
QSqlField sqlField = sqlRecord.field(i);
QVariant type = sqlField.type();
QVariant value = sqlField.value();
qDebug() << QString("Name:%1,isNull:%2,length:%3,defaultValue:%4,required:%5,type:%6,value:%7")
.arg(sqlField.name()).arg(sqlField.isNull()).arg(sqlField.length()).arg(sqlField.defaultValue().toString())
.arg(sqlField.requiredStatus()).arg(type.toString()).arg(value.toString());
}
}
}
db.close();
}
void QtDatabaseDemo::showInsert()
{
QString insertSql = QString("INSERT INTO `tbluserinfo` "
"(`OrgnizationId`,"
"`AccountName`,"
"`RealName`,"
"`Password`,"
"`LoginCount`,"
"`Email`,"
"`Telephone`,"
" `CreateAt`,"
"`IsValid`,"
"`Json`)"
"VALUES"
"(:OrgnizationId,"
":AccountName,"
":RealName,"
":Password,"
":LoginCount,"
":Email,"
":Telephone,"
":CreateAt,"
":IsValid,"
":Json)");
QSqlDatabase db;
showCreateConnection(db);
//If the database cannot be opened, the reason for the error is output
if (!db.open())
{
QSqlError sqlError = db.lastError();
if (sqlError.isValid())//If an error occurs, an error message is output
{
qWarning() << "Can not open database,error message is:" << sqlError.text();
}
return ;
}
// QSqlQuery sqlQuery(insertSql, db);// This approach is not suitable for parameterized SQL statements
QSqlQuery sqlQuery(db);
sqlQuery.prepare(insertSql);//Parameterized SQL statements must call the prepare method before binding parameter values
QString pwdPlain = "zhoufoxcn@2021.com";
QString pwdSecurit;
md5Hash(pwdPlain, pwdSecurit);//Process passwords
// sqlQuery.bindValue(":OrgnizationId", 1);// The organizationid is listed as varchar. I don't know why there is no error when the assignment type is wrong
sqlQuery.bindValue(":OrgnizationId", "1");
sqlQuery.bindValue(":AccountName", "welcome");
sqlQuery.bindValue(":RealName", "helloworld");
sqlQuery.bindValue(":Password", pwdSecurit);
sqlQuery.bindValue(":LoginCount", 1);
sqlQuery.bindValue(":Email", "hellowolrd@qt.com");
sqlQuery.bindValue(":Telephone", "12345678");
sqlQuery.bindValue(":CreateAt", QDateTime::currentDateTime());
sqlQuery.bindValue(":IsValid", 1);
sqlQuery.bindValue(":Json", "{number:1}");
if (sqlQuery.exec())
{
int numRowsAffected = sqlQuery.numRowsAffected();
long long lastInsertId = sqlQuery.lastInsertId().toLongLong();
qDebug() << QString("numRowsAffected:%1,lastInsertId:%2")
.arg(numRowsAffected).arg(lastInsertId);
}
else
{
QSqlError sqlError = sqlQuery.lastError();
// qWarning() << "Can not insert data,error message is:" << sqlError.text();
if (sqlError.isValid())
{
qWarning() << "Can not insert data,error message is:" << sqlError.text();
}
}
db.close();
}
void QtDatabaseDemo::showUpdate()
{
QString updateSql = QString("UPDATE `tbluserinfo`"
" SET `OrgnizationId` = :OrgnizationId,"
"`AccountName` = :AccountName,"
"`RealName` =:RealName,"
"`Password` =:Password,"
"`LoginCount` =:LoginCount,"
"`Email` = :Email,"
"`Telephone` = :Telephone,"
"`CreateAt` = :CreateAt,"
"`LoginAt` = :LoginAt,"
"`IsValid` = :IsValid, "
"`Json` = :Json "
"WHERE `Id` =:Id");
QSqlDatabase db;
showCreateConnection(db);
//If the database cannot be opened, the reason for the error is output
if (!db.open())
{
QSqlError sqlError = db.lastError();
if (sqlError.isValid())//If an error occurs, an error message is output
{
qWarning() << "Can not open database,error message is:" << sqlError.text();
}
return ;
}
// QSqlQuery sqlQuery(updateSql, db);
QSqlQuery sqlQuery(db);
sqlQuery.prepare(updateSql);
QString pwdPlain = "zhoufoxcn@2021.com";
QString pwdSecurit;
md5Hash(pwdPlain, pwdSecurit);//Process passwords
sqlQuery.bindValue(":OrgnizationId", "1");
sqlQuery.bindValue(":AccountName", "welcome");
sqlQuery.bindValue(":RealName", "helloworld");
sqlQuery.bindValue(":Password", pwdSecurit);
sqlQuery.bindValue(":LoginCount", 1);
sqlQuery.bindValue(":Email", "hellowolrd@qt.com");
sqlQuery.bindValue(":Telephone", "12345678");
sqlQuery.bindValue(":CreateAt", QDateTime::currentDateTime());
sqlQuery.bindValue(":IsValid", 1);
sqlQuery.bindValue(":Json", "{number:1}");
sqlQuery.bindValue(":Id", 25);//Note that a number is written here to save trouble. The actual Id should be written in the actual situation
if (sqlQuery.exec())
{
int numRowsAffected = sqlQuery.numRowsAffected();
qDebug() << QString("Updated UserInfo,numRowsAffected:%1.")
.arg(numRowsAffected);
}
else
{
QSqlError sqlError = sqlQuery.lastError();
if (sqlError.isValid())
{
qWarning() << "Can not update data,error message is:" << sqlError.text();
}
}
db.close();
}
void QtDatabaseDemo::showDelete()
{
//In some Access databases, it is not supported to use ": name" as a name placeholder to use parameters in SQL statements. Only "?" can be used as a placeholder
QString deleteSql = QString("DELETE FROM tbluserinfo where LoginCount = ? AND IsValid=? AND Telephone=?");
QSqlDatabase db;
showCreateConnection(db);
//If the database cannot be opened, the reason for the error is output
if (!db.open())
{
QSqlError sqlError = db.lastError();
if (sqlError.isValid())//If an error occurs, an error message is output
{
qWarning() << "Can not open database,error message is:" << sqlError.text();
}
return ;
}
// QSqlQuery sqlQuery(querySql, db);
QSqlQuery sqlQuery(db);
sqlQuery.prepare(deleteSql);
//Note that the index number of the parameter placeholder starts from 0, just as the array subscript starts from 0
sqlQuery.bindValue(0, 1); //Assign a parameter value of 1 to the first "? Placeholder
sqlQuery.bindValue(1, 1); //Assign a parameter value of 1 to the second "? Placeholder
sqlQuery.bindValue(2, "12345678"); //Specify the parameter value for the third "? Placeholder, and the parameter value is" 12345678 "
if (sqlQuery.exec())
{
int numRowsAffected = sqlQuery.numRowsAffected();
qDebug() << QString("Delete UserInfo,numRowsAffected:%1.")
.arg(numRowsAffected);
}
else
{
QSqlError sqlError = sqlQuery.lastError();
// qDebug() << "nativeErrorCode:" << sqlError.nativeErrorCode() << ",ErrorType:" << (int)(sqlError.type());
if (sqlError.isValid())
{
qWarning() << "Can not delete data,error message is:" << sqlError.text();
}
}
qDebug() << "executedQuery:" << sqlQuery.executedQuery();
db.close();
}
void QtDatabaseDemo::showQuery()
{
QString querySql = QString("SELECT * from tbluserinfo");
UserInfo userInfo;
QSqlDatabase db;
showCreateConnection(db);
//If the database cannot be opened, the reason for the error is output
if (!db.open())
{
QSqlError sqlError = db.lastError();
if (sqlError.isValid())//If an error occurs, an error message is output
{
qWarning() << "Can not open database,error message is:" << sqlError.text();
}
return ;
}
QSqlQuery sqlQuery(querySql, db);//Because parameterized SQL statements are not used, there is no need to call the prepare() method
sqlQuery.setForwardOnly(true);//Setting ForwardOnly to true before executing the query can greatly save memory in most cases
// QSqlQuery sqlQuery(db);
// sqlQuery.prepare(querySql);
if (sqlQuery.exec())
{
while (sqlQuery.next())
{
QSqlRecord sqlRecord = sqlQuery.record();
readUserInfo(sqlRecord, userInfo);
qDebug() << "Account Name:" << userInfo.accountName();
}
}
else
{
QSqlError sqlError = sqlQuery.lastError();
if (sqlError.isValid())
{
qWarning() << "Can not query data,error message is:" << sqlError.text();
}
}
db.close();
}
void QtDatabaseDemo::queryUserInfo(int userId, UserInfo& userInfo)
{
QString querySql = QString("SELECT * from tbluserinfo where `Id` = :Id");
QSqlDatabase db;
showCreateConnection(db);
//If the database cannot be opened, the reason for the error is output
if (!db.open())
{
QSqlError sqlError = db.lastError();
if (sqlError.isValid())//If an error occurs, an error message is output
{
qWarning() << "Can not open database,error message is:" << sqlError.text();
}
return ;
}
// QSqlQuery sqlQuery(querySql, db);
QSqlQuery sqlQuery(db);
sqlQuery.prepare(querySql);
sqlQuery.bindValue(":Id", userId); //Specify SQL parameter values using named placeholders
// sqlQuery.bindValue(0, userId); // The SQL parameter values are specified by location index, which are equivalent
if (sqlQuery.exec())
{
if (sqlQuery.next())
{
// QSqlRecord sqlRecord = sqlQuery.record();// The number of columns can be known by reading in the record mode, but there is a process of setting each field in a loop, so the performance is slightly lower
readUserInfo(sqlQuery, userInfo);
}
}
else
{
QSqlError sqlError = sqlQuery.lastError();
if (sqlError.isValid())
{
qWarning() << "Can not query data,error message is:" << sqlError.text();
}
}
db.close();
}
int QtDatabaseDemo::queryMaxUserId()
{
int maxUserId = -1;
QString querySql = QString("SELECT max(Id) from tbluserinfo");
//QString querySql = QString("SELECT Id from tbluserinfo ORDER BY Id DESC limit 0,1");
QSqlDatabase db;
showCreateConnection(db);
//If the database cannot be opened, the reason for the error is output
if (!db.open())
{
QSqlError sqlError = db.lastError();
if (sqlError.isValid())//If an error occurs, an error message is output
{
qWarning() << "Can not open database,error message is:" << sqlError.text();
}
return maxUserId;
}
QSqlQuery sqlQuery(querySql, db);
if (sqlQuery.exec())
{
if (sqlQuery.next())
{
maxUserId = sqlQuery.value(0).toInt();
}
}
else
{
QSqlError sqlError = sqlQuery.lastError();
if (sqlError.isValid())
{
qWarning() << "Can not query data,error message is:" << sqlError.text();
}
}
db.close();
return maxUserId;
}
void QtDatabaseDemo::md5Hash(QString& src, QString& dest)
{
if (src.isEmpty()) //If the original password is empty, an empty result is returned
{
dest = src;
}
else
{
QByteArray pwdArray = QCryptographicHash::hash(src.toLatin1(), QCryptographicHash::Md5);
if (!dest.isEmpty())//Clear original information
{
dest.clear();
}
dest.append(pwdArray.toHex());
}
//
}
void QtDatabaseDemo::readUserInfo(QSqlRecord& sqlRecord, UserInfo& userInfo)
{
if (!sqlRecord.isEmpty())//If the result set is not empty
{
userInfo.setId(sqlRecord.value(0).toInt());
userInfo.setOrgnization(sqlRecord.value(1).toString());
userInfo.setAccountName(sqlRecord.value(2).toString());
userInfo.setRealName(sqlRecord.value(3).toString());
userInfo.setPassword(sqlRecord.value(4).toString());
userInfo.setLoginCount(sqlRecord.value(5).toInt());
userInfo.setEmail(sqlRecord.value(6).toString());
userInfo.setTelephone(sqlRecord.value(7).toString());
userInfo.setCreateAt(sqlRecord.value(8).toDateTime());
userInfo.setLoginAt(sqlRecord.value(9).toDateTime());
userInfo.setIsValid(sqlRecord.value(10).toInt());
//Judge whether the field is not empty
if (!sqlRecord.isNull(11))
{
userInfo.setJson(sqlRecord.value(11).toString());
}
}
}
void QtDatabaseDemo::readUserInfo(QSqlQuery& sqlQuery, UserInfo& userInfo)
{
if (sqlQuery.isValid())
{
userInfo.setId(sqlQuery.value(0).toInt());
userInfo.setOrgnization(sqlQuery.value(1).toString());
userInfo.setAccountName(sqlQuery.value(2).toString());
userInfo.setRealName(sqlQuery.value(3).toString());
userInfo.setPassword(sqlQuery.value(4).toString());
userInfo.setLoginCount(sqlQuery.value(5).toInt());
userInfo.setEmail(sqlQuery.value(6).toString());
userInfo.setTelephone(sqlQuery.value(7).toString());
userInfo.setCreateAt(sqlQuery.value(8).toDateTime());
userInfo.setLoginAt(sqlQuery.value(9).toDateTime());
userInfo.setIsValid(sqlQuery.value(10).toInt());
//Judge whether the field is not empty
if (!sqlQuery.isNull(11))
{
userInfo.setJson(sqlQuery.value(11).toString());
}
}
}Personal summary
About how to get errors in database execution and judge whether SQL statement execution is successful
If you want to judge whether the SQL statement is executed successfully, you can judge it by the return value of the QSqlQuery::exec() method. If the return value is true, it means successful execution, otherwise it means failed execution.
The return value of the QSqlQuery::exec() method simply determines whether the SQL statement is successfully executed. If you need to determine the number of affected rows after successful SQL execution, you need to use QSqlQuery::numRowsAffected(), which is very useful when deleting data, It is possible that the SQL statement executing the delete operation has no syntax error, but there are no qualified data records. The return value of the QSqlQuery::numRowsAffected() method is 0. In addition, if you need to know the reason for the unsuccessful execution of the SQL statement, you can judge it through the return value QSqlError of the QSqlQuery::lastError() method. If an error does occur, QSqlError::isValid() will return true, and then return the error description through QSqlError::text() to analyze why the SQL statement is in error.
In addition, when connecting to the database, there may be connection errors due to incorrect server information or account information. QSqlDatabase::open() will return false. The error object can be obtained through QSqlDatabase::lastError(), and then the error description can be returned through QSqlError::text() to analyze why the connection to the database is wrong.
Another point to mention is that in the database that supports returning the recently added ID, you can return the primary key of the newly added record through QSqlQuery::lastInsertId(). After the success of new posts or replies in the forum in the early years, one function is to provide users with the ability to view their just published posts. This method is very convenient (but it is all on the Web).
About parameterized SQL statements
For SQL statements without parameters, such as "SELECT * from tbluserinfo", you can use QSqlQuery (querysql, DB); To instantiate the QSqlQuery instance, and then execute the QSqlQuery::exec() method to complete the interaction with the database. However, in most cases, queries have conditions, and these query conditions are entered by the user. If the user's data input is not checked, SQL injection may occur.
Therefore, parametric SQL statements are supported in QT, and QT supports two forms of SQL parametric statements: named placeholder based and index placeholder based.
Named based parameterized SQL statements
Naming based is common in Oracle and MySQL. For example, the following SQL statements are based on named placeholders:
INSERT INTO person (id, forename, surname) VALUES (:id, :forename, :surname)
In the above SQL statement, "id" is the name of the parameter. In programming, the parameter can be assigned directly by name. In this SQL statement based on named placeholders, there are two ways to assign values to parameters. One is based on naming, as shown in the following example:
QSqlQuery query;
query.prepare("INSERT INTO person (id, forename, surname) "
"VALUES (:id, :forename, :surname)");
query.bindValue(":id", 1001);
query.bindValue(":forename", "Bart");
query.bindValue(":surname", "Simpson");
query.exec();
In addition, it can also be based on the subscript position, as shown in the following example:
QSqlQuery query;
query.prepare("INSERT INTO person (id, forename, surname) "
"VALUES (:id, :forename, :surname)");
query.bindValue(0, 1001);
query.bindValue(1, "Bart");
query.bindValue(2, "Simpson");
query.exec();
Personally, I prefer naming based, because it is more flexible. Once the original SQL statement is modified and a field is added or deleted, you only need to delete or add the response field according to the name. If you are based on the position of the parameter in the SQL statement, the field serial number will change as long as the position is changed, and you need to modify the response.
Location based parameterized SQL statements
This method is usually used to Access the database through ODBC. I remember that's how I developed. net applications based on Access in my early years. In location-based parameterized SQL statements, the parameters are expressed as "?" To occupy the position, use "?" during programming Assign values to the parameters according to the position in the SQL statement. Of course, you can assign values directly in order.
The following values are assigned directly according to the parameter position, so the position parameter is not provided, as shown in the following example:
QSqlQuery query;
query.prepare("INSERT INTO person (id, forename, surname) "
"VALUES (?, ?, ?)");
query.addBindValue(1001);
query.addBindValue("Bart");
query.addBindValue("Simpson");
query.exec();
Another form is to provide parameter order information when assigning parameters, which is more flexible. The following is an example:
QSqlQuery query;
query.prepare("CALL AsciiToInt(?, ?)");
query.bindValue(0, "A");
query.bindValue(1, 0, QSql::Out);
query.exec();
int i = query.boundValue(1).toInt(); // i is 65
In the above example, note that the two methods are different. If you do not provide the parameter location information, you need to call the query.addBindValue() method according to the parameter location, while providing the parameter information is to call the query.bindValue() method.
Another point to note is that if parameterized SQL statements are not applied, there is no need for QSqlQuery::prepare(), but if parameterized SQL statements are used, then we must call QSqlQuery::prepare() before assigning parameters to the SQL statements.
On the efficiency of implementation
QSqlQuery class provides several methods about record location, such as previous(), next(), first(), last(), which supports the function of jumping to the previous, next, first and last record respectively, seek() method supports the function of jumping to the specified location, and at() method tells you where you are in the record result set.
I've seen a piece circulated on the Internet before. A novice Java programmer wrote an algorithm to calculate the Date after 24 hours, that is, let thread. Sleep() return after 24 hours. Finally, someone found that he was almost sacrificed to heaven. In the early years, there were real similar things around me. In order to count the number of records, some people used the while loop, and then kept next() to judge whether there was a next record. If so, the calculator added 1 to achieve the purpose of counting the total number of records. Later, I was a little smarter. I used the last() method to locate the last record, and then used the at() method similar to QT to get the current position. Of course, I believe now they will count the total number of records in the way of select count(1) from table. In addition, with database support, QSqlQuery::size() can be used to obtain the total number of records in QT Zhonghai.
When the query returns multiple records, in most cases, we will record from the first one to the end, and rarely return the previous one or the first one. If this is the case, you can set QSqlQuery::setForwardOnly() to true (false by default). In this way, the memory consumption is greatly reduced because you don't need to cache some unused data, but you can't call the previous() method. For example, in the example code of this article, the showQuery() method has a similar usage:
QSqlQuery sqlQuery(querySql, db);// Because parameterized SQL statements are not used, there is no need to call the prepare() method
sqlQuery.setForwardOnly(true);// Setting ForwardOnly to true before executing the query can greatly save memory in most cases
In addition, there are two ways to obtain all field values of a record during database query. One is through the QSqlQuery::value(int index) method, where index is the field index starting from 0. If all field values of the current record are read, you can call the QSqlQuery::next() method to determine whether there is a next record, If there is a next record, it will automatically jump to the next record, otherwise it will return false. Another way is to read in the form of QSqlRecord. An instance of QSqlRecord represents a record in the returned result set, returns the QSqlRecord at the current position through the QSqlQuery::record() method, and then reads the field values according to the field order in QSqlRecord. However, this method is slightly less efficient than the QSqlQuery::value(int index) method.
The above two examples of reading query results are included in the above code, namely qtdatabasedemo:: readuserinfo (qsqlrecord & sqlrecord, userinfo & userinfo) and qtdatabasedemo:: readuserinfo (qsqlquery & sqlQuery, userinfo & userinfo).
General process steps for accessing database in QT
General steps of adding, deleting and modifying
1. Establish database connection, that is, instantiate QSqlDatabase and specify database connection information;
2. Judge whether the database can be opened through the return value of db.open(). If it can be opened, proceed to the next step. If it cannot be opened, analyze the reason why the database cannot be opened through db.lastError();
3. Instantiate QSqlQuery through the instantiated QSqlDatabase. If you execute a parameterized SQL statement, you need to execute the QSqlQuery::prepare() method first, and then bind the parameters.
4. Judge whether the execution is successful through the return value of QSqlQuery.exec(). If the execution is successful, the number of affected rows (i.e. the number of new, updated or deleted records) can be recovered through the QSqlQuery::numRowsAffected() method; If the execution is unsuccessful, you can view the failure reason through the QSqlError object instance returned by QSqlQuery::lastError(). At this time, the QSqlError::isValid() method of the object must also return true, indicating that there is an error in executing the SQL statement.
5. Call the close method of QSqlDatabase to close the database connection.
Query operation
1. Establish database connection, that is, instantiate QSqlDatabase and specify database connection information;
2. Judge whether the database can be opened through the return value of db.open(). If it can be opened, proceed to the next step. If it cannot be opened, analyze the reason why the database cannot be opened through db.lastError();
3. Instantiate QSqlQuery through the instantiated QSqlDatabase. If you execute a parameterized SQL statement, you need to execute the QSqlQuery::prepare() method first, and then bind the parameters.
4. Judge whether the execution is successful through the return value of QSqlQuery.exec(). If the execution is successful, you can judge whether there are available result sets through QSqlQuery::next() (you can also use QSqlQuery::size() to obtain the number of result sets). If there are multiple records in the result set, you can traverse the result set through a while loop; If the execution is unsuccessful, you can view the failure reason through the QSqlError object instance returned by QSqlQuery::lastError(). At this time, the QSqlError::isValid() method of the object must also return true, indicating that there is an error in executing the SQL statement.
5. Call the close method of QSqlDatabase to close the database connection.
September 25, 2021