QT sqlite的简单用法

1、相关头文件

#include <QSqlDatabase>
#include <QSqlError>
#include <QSqlQuery>
#include <QSqlRecord>
#include <QSqlIndex>
#include  <QSqlField>
#include <QFile>
#include <QDebug>

2、数据库对象

QSqlDatabase database;

3、创建数据库并打开数据库

bool initDataBase()
{
    if(QSqlDatabase::contains("qt_sql_default_connect")){
        database = QSqlDatabase::database("qt_sql_default_connect");
    }else{
        database = QSqlDatabase::addDatabase("QSQLITE");
        database.setDatabaseName("myDatabase.db");
        database.setUserName("hs");
        database.setPassword("hs");

        if(database.isValid()){
            qDebug() << "valid." ;
        }else{
            qDebug() << "not valid!"<< database.lastError();
            return false;
        }

        if (!database.open())
        {
            qDebug() << "Error: Failed to connect database." << database.lastError();
            return false;
        }
    }
    return true;
}

4、创建表

bool testCreateTable()
{
    QSqlQuery sql_query;

    QString create_sql = "create table student (id int primary key, name varchar(30), age int)";
    sql_query.prepare(create_sql);
    if(!sql_query.exec())
    {
        qDebug() << "Error: Fail to create table." << sql_query.lastError();
        return false;
    }
    else
    {
        qDebug() << "Table created!";
    }

    return true;
}

5、向表中插入数据

bool testInsert(int id,const QString& name,int age)
{
    QSqlQuery query;
    query.prepare("INSERT INTO student(id,name,age)"
                  "VALUES (:id,:name,:age)");
    query.bindValue(":id",id);
    query.bindValue(":name",name);
    query.bindValue(":age",age);
    return query.exec();
}

6、查询表中的数据

bool testQuery()
{
    QSqlQuery sql_query;
    QString select_sql = "select id, name,age from student";
    if(!sql_query.exec(select_sql))
    {
        qDebug()<<sql_query.lastError();
        return false;
    }
    else
    {
        while(sql_query.next())
        {
            int id = sql_query.value("id").toInt();
            QString name = sql_query.value("name").toString();
            int age = sql_query.value("age").toInt();
            qDebug()<<QString("id:%1 name:%2 age:%3").arg(id).arg(name).arg(age);
        }
    }


    QString lastQuery = sql_query.lastQuery();
    qDebug() << "lastQuery: " << lastQuery;
    return true;
}

7、修改表中的数据

bool testModify(const QString& name,int age,int id)
{
    QString sql = "update student set name=:name,age=:age where id=:id";
    QSqlQuery query;
    query.prepare(sql);
    // 设置参数
    query.bindValue(":name",name);
    query.bindValue(":age",age);
    query.bindValue(":id",id);
    return query.exec();
}

8、删除表中的数据

bool testDelete(int id)
{
    QString sql = "delete from student where id=:id";
    QSqlQuery query;
    query.prepare(sql);
    // 设置参数
    query.bindValue(":id",id);
    return query.exec();
}

9、查询数据库中的表

bool testQueryTable()
{
    // 查询sqlite_master表
    QString querySql = "SELECT name, type FROM sqlite_master WHERE type IN ('table', 'view')";
    QSqlQuery query(querySql);

    // 遍历查询结果
    while (query.next()) {
        QString name = query.value(0).toString();
        QString type = query.value(1).toString();
        qDebug() << "------------------";
        qDebug() << "Object name:" << name;
        qDebug() << "Object type:" << type;
        qDebug() << "------------------";
    }
    return true;
}

10、删除指定的表

bool testDeleteTable()
{
    QSqlQuery sql_query;
    QString drop_sql = "DROP TABLE student";
    sql_query.prepare(drop_sql);
    if(!sql_query.exec())
    {
        qDebug() << sql_query.lastError();
        return false;
    }
    else
    {
        qDebug() << "drop table success";
    }
    return true;
}

11、查询表是否存在

bool isTableExist()
{
    QSqlQuery sql_query;
    // count(*) 表示查询数量，因为student只有一个，因此只查询了一行结果
    sql_query.exec("SELECT COUNT(*) FROM sqlite_master  WHERE type='table' AND name='student'");
    int count = 0;
    if (sql_query.next()) {
        count = sql_query.value(0).toInt();
    }
    return count;
}

12、查询表的字段等

void testRecord()
{
    QSqlQuery query("select * from student");
    //QString sql = "select * from student";
    //query.prepare(sql);
    QSqlRecord rec = query.record();

    qDebug() << "Number of columns: " << rec.count();

    for(int i = 0; i < rec.count();++i){
        qDebug() << "field: " << rec.fieldName(i);
    }

    int nameCol = rec.indexOf("name"); // index of the field "name"
    while (query.next())
        qDebug() << query.value(nameCol).toString(); // output all names
}

13、查询数据库的一些其他输出信息

void testDatabaseOutput()
{
    QString connectionOptions = database.connectOptions();
    qDebug() <<"option:" << connectionOptions;
    QStringList connectionName = database.connectionNames();
    qDebug() << "connectionNames:"<<connectionName;

    qDebug() << "database Name: " << database.databaseName();
    qDebug() << "dirver Name: " << database.driverName();

    //QSqlDriver* driver = database.driver();
    qDebug() << "dirvers: " << database.drivers();

    qDebug() << "host Name: " << database.hostName();

    qDebug() << "password: " << database.password();

    qDebug() << "port: " << database.port();

    qDebug() << "tables: " << database.tables(QSql::TableType::AllTables);

    QSqlIndex rlt = database.primaryIndex("student");
     qDebug() << "primary key: "<<rlt.name();
    QSqlRecord rec = database.record("student");
    qDebug() << "record count: "<<rec.count();

    for(int i = 0; i < rec.count();++i){
        qDebug() << "rec: " << rec.fieldName(i);
        qDebug() << "rec Value:" << rec.value(i).toString();

        QSqlField field = rec.field(i);
        qDebug() <<"field: "<< field.name();
        qDebug() << "field value: " << field.value().toString();
        qDebug() << "field type: " << field.type();
    }


}