How to operate MySQL database in Qt

1. Install the driver

(1) Installation

To operate the MySQL database in Qt, you must first install the mysql driver file. Just copy the libmusql.dll file under MySQL to the bin folder under the Qt installation path.

Paste the libmysql.dll file directly into this folder.

(2) Verify whether the driver is installed successfully

After the copy is successful, test whether the driver is installed successfully, create a new file, and select the Qt designer interface class, and all subsequent operations can be kept as default.

Introduce the following libraries into the newly generated .cpp file

#include <QSqlDatabase>
#include <QDebug>
#include <QMessageBox>  
#include <QSqlError>    
#include <QString>
#include <QSqlQuery>
#include <QVariantList>

Insert the following code in the constructor, and then click Run, if no warning window pops up It means the installation is successful, otherwise it fails.

//添加一个数据库
    QSqlDatabase db=QSqlDatabase::addDatabase("QMYSQL");    //括号内要写出数据库的类型
    //设置数据库
    db.setHostName("127.0.0.1"); //设置数据库的主机ip
    //设置数据库的用户名
    db.setUserName("root");
    //设置数据库的密码
    db.setPassword("123456");    //这个就是安装MySQL时设置的密码
    //设置数据库的名字
    db.setDatabaseName("aaa2");
    //打开数据库（已经安装过mysql驱动了）
    if(db.open()==false){
        QMessageBox::warning(this,"waring",db.lastError().text());
    }

2. Using MySQL database

(1) Single statement execution

First create a QString object sql, write the statement to be executed in sql, and then create the QSqlQuery class The object query calls its exec() function to execute the code in sql.

    QString sql = "insert into student (id,name,age,math) values (1,'kaw',20,97)";	//书写想要执行的语句
    QSqlQuery query;	//创建一个QSqlQuery对象
    query.exec(sql);    //执行mysql语句

(2) Multiple statement execution

In sql, multiple statements can be executed at the same time by separating each statement with a semicolon. The following operations are performed to add, delete and update the table at the same time.

    QString sql = "insert into student (id,name,age,math) values (13,'kaw',20,97);delete from student where id=2;update student set name='sdd',math=100 where id=10;";
    QSqlQuery query;	//创建一个QSqlQuery对象
    query.exec(sql);    //执行mysql语句

(3) Batch processing operation

Method 1: addBindValue()

Enter the statement you want to execute in query.prepare(), where the to-be-entered The value is replaced by "?", where "?" is the wildcard character. When adding the values ​​you want to set later, you can use idList, nameList, ageList and mathList. To avoid errors, please use addBindValue() in the order of id, name, age, and math to bind values.

    QSqlQuery query;
    query.prepare("insert into student (id,name,age,math) values (?,?,?,?)");	//书写语句模型
    //添加绑定数据
    QVariantList idList;    //创建一个id列表
    idList << 15<<16<<17;
    query.addBindValue(idList); //完成第一个?的绑定
    QVariantList nameList;
    nameList << "ddd"<<"eee"<<"jjj";
    query.addBindValue(nameList);   //完成第二个?的绑定
    QVariantList ageList;
    ageList << 25<<24<<23;
    query.addBindValue(ageList);    //完成第三个?的绑定
    QVariantList mathList;
    mathList << 90<<89<<90;
    query.addBindValue(mathList);   //完成第四个?的绑定
    //执行批处理
    query.execBatch();

Method 2: bindValue()

Directly use a custom name to complete the binding. At this time, the binding order can be decided by yourself.

	QSqlQuery query;
    query.prepare("insert into student (id,name,age,math) values (:id,:name,:age,:math)");  //:id之类的名字时自定义的 自己方便就好
    //添加绑定数据
    QVariantList idList;    //创建一个id列表
    idList << 18<<19<<20;
    query.bindValue(":id",idList); //完成:id的绑定
    QVariantList nameList;
    nameList << "ddd"<<"eee"<<"jjj";
    query.bindValue(":name",nameList);   //完成:name的绑定
    QVariantList ageList;
    ageList << 25<<24<<23;
    query.bindValue(":age",ageList);    //完成:age的绑定
    QVariantList mathList;
    mathList << 90<<89<<90;
    query.bindValue(":math",mathList);   //完成:math的绑定
    //执行批处理
    query.execBatch();

(4) Query

The table to be queried is:

The value after value can be an index or a column name. After taking it out, it needs to be converted into the corresponding data type.

	//查询操作
    QSqlQuery query;
    query.exec("select * from student");
    while(query.next()){
        qDebug()<<query.value(0).toInt()
             <<query.value("name").toString().toUtf8().data()
            <<query.value(2).toInt()
           <<query.value(3).toInt();   
    }

Query results:

The above is the detailed content of How to operate MySQL database in Qt. For more information, please follow other related articles on the PHP Chinese website!