Qt之sql数据库系列操作

1）工程设置：QT += sql

2）创建或打开数据库。

    if(QSqlDatabase::contains("qt_sql_default_connection"))
    {
        database = QSqlDatabase::database("qt_sql_default_connection");
    }
    else
    {
        QString path = QApplication::applicationDirPath();
        database = QSqlDatabase::addDatabase("QSQLITE");
        database.setDatabaseName(path+"/deactiveListDB");
        database.setUserName("xxx");
        database.setPassword("xxxxxx");
        if(!database.open("xxx","xxxxxx"))
        {
            qDebug()<<"open database failed:"<<database.lastError();
            PrintLog("database text:%s",database.lastError().databaseText().toStdString().c_str());
            PrintLog("driver text:%s",database.lastError().driverText().toStdString().c_str());
            return false;
        }
    }

3)创建表

QString create_sql = "create table tableDeactiveList (sealCode varchar(30) primary key,sealName varchar(30),date datetime)";
QSqlQuery query(database);
query.prepare(create_sql);
if(!query.exec())
{
        qDebug()<<"create table failed:"<<query.lastError();
        return false;
}

4）插入一条数据

    QString insert_sql = QString("insert into tableDeactiveList values ('%1','%2','%3')").arg(list[0]).arg(list[1]).arg(list[2]);
    QSqlQuery sqlQuery(database);
    sqlQuery.prepare(insert_sql);
    if(!sqlQuery.exec())
    {
        qDebug()<<"insert failed:"<<sqlQuery.lastError();
    }
    else
    {
        qDebug()<<"insert "<<list[0]<<" "<<list[1]<<" "<<list[2];
        PrintLog("insert %s %s %s",list[0].toStdString().c_str(),list[1].toStdString().c_str(),list[2].toStdString().c_str());
    }

5）查询数据

    QString select_sql = QString("select sealName from tableDeactiveList where sealCode='%1'").arg(sealCode);
    QSqlQuery sqlQuery(database);
    sqlQuery.prepare(select_sql);
    if(!sqlQuery.exec())
    {
        qDebug()<<"select failed:"<<sqlQuery.lastError();
        return false;
    }
    if(sqlQuery.next())
    {
        return true;
    }
    return false;

6）查询表内条目数

    QString select_sql = QString("select COUNT(*) from tableSignLog");
    QSqlQuery sqlQuery(database);
    sqlQuery.prepare(select_sql);
    if(!sqlQuery.exec())
    {
        qDebug()<<"select failed:"<<sqlQuery.lastError();
        return false;
    }
    if(sqlQuery.next())
    {
        int count = sqlQuery.value(0).toInt();
        return count;
    }
    return 0;

7）清空表

    QString delete_sql = QString("truncate tableDeactiveList");
    QSqlQuery sqlQuery(database);
    sqlQuery.prepare(delete_sql);
    if(!sqlQuery.exec())
    {
        qDebug()<<"delete failed:"<<sqlQuery.lastError();
        return false;
    }

    return true;