QtCreator Qt中执行SQL语句,select,update,delete,insert

1 执行一个查询

发生错误后，QSqlQuery::exec()会返回false,错误信息在query.lastError()中

    //创建数据库连接
    if (!createConnection()) return 1;
    QSqlDatabase db1 =  QSqlDatabase::database("connection1");

    //使用QSqlQuery查询整张表

    QSqlQuery query1(db1);

    qDebug() << "connection1:";

    query1.exec("select * from student");

    //显示错误信息

    qDebug() << query1.lastError().text() ;

2 浏览结果集

    query1.exec("select * from student");

    while(query1.next()) {

        qDebug() << query1.value("id").toInt() << "," << query1.value("name").toString();

    }

QSqLQuery::value()可以返回当前记录的一个字段。返回一个QVariant,后面toInt(),toString()将其转换成相应类型

next()定位到下一条记录，

previous()前一条记录,

first()第一条纪律

last()定位到最后一条记录

seek(n)定位到第n条记录

at()返回当前行的索引

record()返回当前行的记录

size()返回总行数,通过QSqlDriver::hasFeature()判断是否支持

    int numRows;

    //判断是否支持QuerySize特性

    if (db1.driver()->hasFeature(QSqlDriver::QuerySize)) {

        qDebug() << "支持size()" << endl;

        numRows = query1.size();

    } else {

        qDebug() << "不支持size()" << endl;

        query1.last();

        numRows = query1.at()+1;

    }

    query1.seek(2);    //指向索引为2的记录，即第3条记录

    qDebug() << "当前记录:" <<query1.at() << endl;// 返回索引值

    qDebug() << "id:" << query1.value(0).toInt() << ",name" << query1.value(1).toString();

    qDebug() << "id:" << query1.value("id").toInt() << ",name:" << query1.value("name").toString();

    QSqlRecord record = query1.record();//获取当前行的记录

    //获取记录中"id"和"name"两个字段的值

    qDebug() << "id:" << record.value("id").toInt() << ",name:" << record.value("name").toString();

    //获取索引为1的字段，输出字段名与，值

    QSqlField field = record.field(1);

    qDebug() << "fieldName:" << field.name() << ",fieldValue:" << field.value().toString();

3 插入，更新和删除记录

Qt支持两种占位符：名称绑定和位置绑定

    //名称绑定

    query1.prepare("insert into student values(:id,:name)");

    int idValue = 10;

    QString nameValue = "ChenYun";

    query1.bindValue(":id",idValue);

    query1.bindValue(":name",nameValue);

    qDebug() << query1.exec();

    //位置绑定

    query1.prepare("insert into student values(?,?)");

    idValue = 20;

    nameValue = "chenchen";

    query1.addBindValue(idValue);

    query1.addBindValue(nameValue);

    query1.exec();

    // 批处理

    query2.prepare("insert into student (id, name) values (?, ?)");

    QVariantList ids;

    ids << 20 << 21 << 22;

    query2.addBindValue(ids);

    QVariantList names;

    names << "xiaoming" << "xiaoliang" << "xiaogang";

    query2.addBindValue(names);

    if(!query2.execBatch()) qDebug() << query2.lastError();

    // 更新

    query2.exec("update student set name = 'xiaohong' where id = 20");

    // 删除

    query2.exec("delete from student where id = 21");

    query2.exec("select * from student");

    while(query2.next())

    {

        qDebug() << query2.value(0).toInt() << query2.value(1).toString();

    }

4 事务

QSqlDatabase::transaction()//启动一个事务

QSqlDatabase::commit()//结束一个事务

QSqlDatabase::rollback()//数据回滚

    tableModel->database().transaction();

    if (tableModel->submitAll()) {

        tableModel->database().commit();

    } else {

        tableModel->database().rollback();

        QMessageBox::warning(this,"tableModel",QString("数据库错误:%1").arg(tableModel->lastError().text()));

    }