嵌入式Linux学习之路——Sqlite使用示例

Sqlite使用示例

参考链接：

 https://blog.csdn.net/weixin_41656968/article/details/80473137

 

1.Qt Creator新建项目

           新建一个控制台项目”sqlite_test”，方法可参见“第一个Qt程序”。

2. 文件.pro内容如下

QT -= gui
QT += sql

CONFIG += c++11 console
CONFIG -= app_bundle

# The following define makes your compiler emit warnings if you use
# any Qt feature that has been marked deprecated (the exact warnings
# depend on your compiler). Please consult the documentation of the
# deprecated API in order to know how to port your code away from it.
DEFINES += QT_DEPRECATED_WARNINGS

# You can also make your code fail to compile if it uses deprecated APIs.
# In order to do so, uncomment the following line.
# You can also select to disable deprecated APIs only up to a certain version of Qt.
#DEFINES += QT_DISABLE_DEPRECATED_BEFORE=0x060000    # disables all the APIs deprecated before Qt 6.0.0

SOURCES += \
        main.cpp

# Default rules for deployment.
qnx: target.path = /tmp/$${TARGET}/bin
else: unix:!android: target.path = /opt/$${TARGET}/bin
!isEmpty(target.path): INSTALLS += target

3. 文件main.cpp内容如下

#include <QCoreApplication>
#include <qdebug.h>
#include <QSqlDatabase>
#include <QSqlError>
#include <QSqlQuery>

QSqlDatabase database;

void DbConnOpen(void)
{
    if (QSqlDatabase::contains("qt_sql_default_connection"))
    {
        database = QSqlDatabase::database("qt_sql_default_connection");
    }
    else
    {
        //建立数据库
        //QCoreApplication::setLibraryPaths("/usr/lib/plugins/sqldrivers/");
        //qDebug() << "path: " << QCoreApplication::libraryPaths();
        database = QSqlDatabase::addDatabase("QSQLITE");
        database.setDatabaseName("mydb.db");
        qDebug() << QSqlDatabase::drivers();
    }
    //打开数据库
    if (!database.open())
    {
        qDebug() << "Error: Failed to connect database." << database.lastError();
    }
    else
    {
        qDebug() << "Succeed to connect database." ;
    }
}

void CreateTable(void)
{
     //创建表格
     QSqlQuery sql_query;
     if(!sql_query.exec("create table student(id int primary key, name text, age int)"))
     {
         qDebug() << "Error: Fail to create table."<< sql_query.lastError();
     }
     else
     {
         qDebug() << "Table created!";
     }
}

void InsertDb(void)
{
    //插入数据
    QSqlQuery sql_query;
    if(!sql_query.exec("INSERT INTO student VALUES(1, \"Wang\", 23)"))
    {
        qDebug() << sql_query.lastError();
    }
    else
    {
        qDebug() << "inserted Wang!";
    }
    if(!sql_query.exec("INSERT INTO student VALUES(2, \"Li\", 23)"))
    {
        qDebug() << sql_query.lastError();
    }
    else
    {
        qDebug() << "inserted Li!";
    }
}

void UpdateDb(void)
{
    //修改数据
    QSqlQuery sql_query;
    sql_query.exec("update student set name = \"QT\" where id = 1");
    if(!sql_query.exec())
    {
        qDebug() << sql_query.lastError();
    }
    else
    {
        qDebug() << "updated!";
    }
}

void SelectDb(void)
{
    //查询数据
    QSqlQuery sql_query;
    sql_query.exec("select * from student");
    if(!sql_query.exec())
    {
        qDebug()<<sql_query.lastError();
    }
    else
    {
        while(sql_query.next())
        {
            int id = sql_query.value(0).toInt();
            QString name = sql_query.value(1).toString();
            int age = sql_query.value(2).toInt();
            qDebug()<<QString("id:%1    name:%2    age:%3").arg(id).arg(name).arg(age);
        }
    }
}

void DeleteDb(void)
{
    //删除数据
    QSqlQuery sql_query;
    sql_query.exec("delete from student where id = 1");
    if(!sql_query.exec())
    {
        qDebug()<<sql_query.lastError();
    }
    else
    {
        qDebug()<<"deleted!";
    }
}

int main(int argc, char *argv[])
{
    QCoreApplication a(argc, argv);

    DbConnOpen();
    CreateTable();
    InsertDb();
    UpdateDb();
    SelectDb();
    DeleteDb();
    //关闭数据库
    database.close();

    return a.exec();
}

4.编译、运行和测试

• Ubuntu中中运行编译（桌面构建套件）后生成的“sqlite_test”

当前目录下如果没有“mydb.db”配置文件，会自动生成。

终端输出信息：

("QSQLITE")

Succeed to connect database.

Table created!

inserted Wang!

inserted Li!

updated!

"id:1    name:QT    age:23"

"id:2    name:Li    age:23"

deleted!

 

• 开发板中运行编译（开发板构建套件）后生成的“sqlite_test”

复制可执行文件到开发板，添加权限并运行，终端输出如下信息

QSqlDatabase: QSQLITE driver not loaded

QSqlDatabase: available drivers:

原因是开发板缺少QSqlite相关库，暂时不会自己添加，开发板烧写了一个更全的文件系统后，运行正常。