[QT] Sqlite資料庫基本操作[增删改查]

準備工作

1. 建立資料庫檔案

2. 打開資料庫

3. 建立表格

4. 插入資料

5. 更新資料

6. 查詢某行某列

7. 擷取一列資料

8. 删除一行資料

9  關閉資料庫

文章涉及到使用sqlite資料庫儲存整型,字元串,二進制以及時間類型的操作

在QT中調用Sqlite需在.pro檔案中添加

QT += sql
           

本文使用停車系統中使用的資料庫為例,我們先建立一個結構體把我們需要儲存的資料整理一下,便于操作

typedef struct SQL_DATA{
    QString t_pl;             /* 車牌号 */
    QString t_location;       /* 車位号 */
    QString t_card;           /* 卡号 */
    int t_featuresize;        /* 人臉特征資料大小 */
    QByteArray t_featuredata; /* 人臉特征資料 */
    QString t_datetime;       /* 存車時間 */
    //int t_way;              /* 存車方式 */
} SQL_DATA_TypeDef;

Q_DECLARE_METATYPE(SQL_DATA)
           

在對資料庫進行操作時,建議建立一個資料庫類,本文中建立的類名為HSSql, 在頭檔案中添加資料庫相關頭檔案

#include <QSqlDatabase>
#include <QSqlError>
#include <QSqlQuery>
           

下面簡單介紹資料庫的簡單使用

1. 建立資料庫檔案函數

/* .h檔案 */
QSqlDatabase create_db(QString fileName, QString identifier = "Topp");

/* .c檔案 */
QSqlDatabase HSSql::create_db(QString fileName, QString identifier)
{
    QSqlDatabase db;
    QString name = QCoreApplication::applicationDirPath() + "/" + fileName + ".db";
    db = QSqlDatabase::addDatabase("QSQLITE", identifier);
    db.setDatabaseName(name);

    QTextCodec *codec = QTextCodec::codecForName("UTF-8");
    QTextCodec::setCodecForLocale(codec);

    return db;
}
           

2. 打開資料庫

bool open_db(QSqlDatabase db);

bool HSSql::open_db(QSqlDatabase db)
{
    return db.open();
}
           

3. 建立表格

bool create_table(QSqlDatabase db, QString sql_command);

bool HSSql::create_table(QSqlDatabase db, QString sql_command)
{
    if(!open_db(db)){
        return false;
    }
    QSqlQuery sqlquery(db);
    return sqlquery.exec(sql_command);
}
           

4. 插入資料

bool insert_data_into_table(QSqlDatabase db, QString table, SQL_DATA_TypeDef* data_t);

bool HSSql::insert_data_into_table(QSqlDatabase db, QString table, SQL_DATA_TypeDef *data_t)
{
    m_command = "create table userInfo( pl char(20) not null,       \
                                        location char(9) not null, \
                                        card char(9),              \
                                        featuresize int,           \
                                        featuredata blob,          \
                                        datetime char(20))";
    QSqlQuery sqlquery(db);
    /* 此處可根據實際應用判斷資料的唯一性 */
    QString command = QString("INSERT INTO %1     
                   (pl,location,card,featuresize,featuredata,datetime) VALUES 
                   (:pl,:location,:card,:featuresize,:featuredata,:datetime);").arg(table);
    sqlquery.prepare(command);
    sqlquery.bindValue(":pl", data_t->t_pl);
    sqlquery.bindValue(":location", data_t->t_location);
    sqlquery.bindValue(":card", data_t->t_card);
    sqlquery.bindValue(":featuresize", data_t->t_featuresize);
    sqlquery.bindValue(":featuredata", data_t->t_featuredata);
    sqlquery.bindValue(":datetime", data_t->t_datetime);

    if(sqlquery.exec()) return true;
    else {
        qDebug() << sqlquery.lastError().text();
        return false;
    }
}
           

5. 更新資料

bool update_userInfo(QSqlDatabase db, QString table, int id, QString col_name, QString new_value);

bool HSSql::update_userInfo(QSqlDatabase db, QString table, int id, QString col_name, QString new_value)
{
    QSqlQuery sqlquery(db);
    QString command = QString("update %1 set %2 = %3 where id = :id;").arg(table)
                                                                    .arg(col_name)
                                                                    .arg(new_value);
    sqlquery.bindValue(":id", id);
    if(!sqlquery.exec()) {
        return false;
    }
    else {
        return true;
    }
}
           

6. 查詢某行某列

bool getLocationByPl(const QSqlDatabase &db, const QString &table, const QString &pl, QString &location);

bool HSSql::getLocationByPl(const QSqlDatabase &db, const QString &table, const QString &pl, QString &location)
{
    QSqlQuery sqlquery(db);
    QString command = QString("select location from %1 where pl = '%2';").arg(table).arg(pl);
    if(!sqlquery.exec(command)) {
        qDebug() << sqlquery.lastError().text();
        return false;
    }


    while (sqlquery.next()) {
        location = sqlquery.value(0).toString();
//        qDebug() << location;
        /* 查尋過程中有時會查詢到空字元串，是以我在下面加了條件判斷 */
        if(!location.isEmpty()) {
            return true; 
        }
    }

    return true;
}
           

7. 擷取一列資料

bool getOneColumn(const QSqlDatabase &db, const QString &table, const QString &str, 
                      void(*callBack)(QStringList, void *),void *pUserData);
    bool getOneColumn(const QSqlDatabase &db, const QString &table, const QString &str, 
                      void(*callBack)(QList<int> &, void *),void *pUserData);
    bool getOneColumn(const QSqlDatabase &db, const QString &table, const QString &str, 
                      void(*callBack)(QList<QByteArray> &, void *),void *pUserData);
           

bool HSSql::getOneColumn(const QSqlDatabase &db, const QString &table, const QString &str, void (*callBack)(QStringList, void *), void *pUserData)
{
    QSqlQuery sqlquery(db);
    QStringList str_list;
    QString command = QString("select %1 from %2;").arg(str).arg(table);
    if(!sqlquery.exec(command)) return false;
    while (sqlquery.next()) {
        str_list << sqlquery.value(0).toString();
    }
    callBack(str_list, pUserData);
    return true;
}

bool HSSql::getOneColumn(const QSqlDatabase &db, const QString &table, const QString &str, void (*callBack)(QList<int> &, void *), void *pUserData)
{
    QSqlQuery sqlquery(db);
    QList<int> int_list;
    QString command = QString("select %1 from %2;").arg(str).arg(table);
    if(!sqlquery.exec(command)) return false;
    while (sqlquery.next()) {
        int_list << sqlquery.value(0).toInt();
    }
    callBack(int_list, pUserData);
    return true;
}

bool HSSql::getOneColumn(const QSqlDatabase &db, const QString &table, const QString &str, void (*callBack)(QList<QByteArray> &, void *), void *pUserData)
{
    QSqlQuery sqlquery(db);
    QList<QByteArray> array_list;
    QString command = QString("select %1 from %2;").arg(str).arg(table);
    if(!sqlquery.exec(command)) return false;
    while (sqlquery.next()) {
        array_list << sqlquery.value(0).toByteArray();
    }
    callBack(array_list, pUserData);
    return true;
}
           

8. 删除一行資料

bool delete_data(QSqlDatabase db, QString table, int key);

bool HSSql::delete_data(QSqlDatabase db, QString table, int key)
{
    QSqlQuery sqlquery(db);
    QString delete_sql = QString("delete from %1 where pl = %2").arg(table).arg(key);
    if(sqlquery.exec(delete_sql)) return true;
    else return false;
}
           

9  關閉資料庫

void close_db(QSqlDatabase &db) { if(open_db(db)) db.close(); }
           

另外,在構造函數中需要對使用者建立了結構體進行注冊

qRegisterMetaType<SQL_DATA>("SQL_DATA");
           

在建立表格時,需要一條資料庫指令,這個指令要和我們建立的結構體對應起來,其中

char --- QString

int ---  int

圖檔,資料等其他二進制檔案 --- blob 

datetime --- char (sqlite不支援datetime類型,是以這裡使用char型代替,儲存為字元串格式)

m_command = "create table userInfo( pl char(9) not null,       \
                                        location char(9) not null, \
                                        card char(9),              \
                                        featuresize int,           \
                                        featuredata blob,          \
                                        datetime char(20))";
           

下面寫一個demo僅供參考,未測試

m_command = "create table userInfo( pl char(9) not null,       \
                                        location char(9) not null, \
                                        card char(9),              \
                                        featuresize int,           \
                                        featuredata blob,          \
                                        datetime char(20))";

m_sql = new HSSql;
QSqlDatabase db = m_sql->create_db("ToppDb");
m_sql->create_table(db, m_command);

SQL_DATA_TypeDef data_t;
data_t.t_pl = "ABC";
data_t.t_card = "961202";
data_t.t_location = "201";
data_t.t_featuresize = 123456;
data_t.t_datetime = QDateTime::currentDateTime().toString("yyyy-MM-dd HH:mm:ss");

QByteArray array;
array.resize(2);
array[0] = 0xaa;
array[1] = 0x03;
data_t.t_featuredata = array;

m_sql->insert_data_into_table(db, "userInfo", &data_t);

m_sql->close_db(db);
           

以下是調試過程中遇到的問題:

1. 查詢的到的資料(有漢字)在UI上顯示亂碼

原因: 是由于儲存的字元串中的漢字編碼為GBK

解決:将字元串先轉成UTF-8編碼再儲存

QString UTF8ToGBK(const QString &utfStr);

QString ToppHMI::UTF8ToGBK(const QString &utfStr)
{
    QString ustr = QTextCodec::codecForName("UTF-8")->toUnicode(utfStr.toLocal8Bit().data());
    QByteArray gbk_byte = QTextCodec::codecForName("GBK")->fromUnicode(ustr);

    return QString(gbk_byte);
}


QString gbkToUtf8(const QString &gbkStr);

QString CameraUI::gbkToUtf8(const QString &gbkStr)
{
    QString ustr = QTextCodec::codecForName("GBK")->toUnicode(gbkStr.toLocal8Bit().data());
    QByteArray utf8_byte = QTextCodec::codecForName("UTF-8")->fromUnicode(ustr);

    return QString(utf8_byte);
}
           

2. 擷取目前時間與資料儲存時間之間的內插補點

/* 将查詢到的時間字元串轉成時間戳 */
QDateTime dt = QDateTime::fromString(start.at(i), "yyyy/MM/dd HH:mm:ss");
uint start_T = dt.toTime_t();

/* 擷取目前時間時間戳 */
QDateTime eDT = QDateTime::currentDateTime();
uint et = eDT.toTime_t();
/* 取其內插補點 */
uint dif = et - sDT;

ushort day = dif / 86400;
unsigned char hour = ( dif % 86400 ) / 3600;
unsigned char minute = (( dif % 86400 ) % 3600) / 60;
unsigned char second = (( dif % 86400 ) % 3600) % 60;

return  QString("%1天%2時%3分%4秒").arg(day,3,10,QLatin1Char('0'))
                                   .arg(hour,2,10,QLatin1Char('0'))
                                   .arg(minute,2,10,QLatin1Char('0'))
                                   .arg(second,2,10,QLatin1Char('0'));
           

3. 提供一個儲存圖檔的思路

1. 使用opencv讀取圖檔得到圖檔的Mat型值
2. 使用資料庫儲存Mat的type, cols, rows, elemSize, ptr

e.g 
    sdt.t_imgtype = ret_image.type();
    sdt.t_imgcols = ret_image.cols;
    sdt.t_imgrows = ret_image.rows;
    sdt.t_imgsize = ret_image.elemSize();
    const size_t data_size = ret_image.cols * ret_image.rows * ret_image.elemSize();
    QByteArray imgByte = QByteArray::fromRawData( (const char*)ret_image.ptr(), data_size );
    sdt.t_imgdata = imgByte;

3. 加載資料庫中的圖檔
    cv::Mat img = cv::Mat(sdt.t_imgrows,sdt.t_imgcols, sdt.t_imgtype, (void*)sdt.t_imgdata.data()).clone();
    QImage image((const uchar*)img.data, img.cols, img.rows, QImage::Format_RGB888);
           

4. 使用sqlite3工具可以很友善的進行調試

下載下傳 sqlite3二進制檔案

64位電腦下載下傳以下兩個檔案

解壓之後如圖

在位址欄輸入cmd（也可以将sqlite3加入環境變量）打開調試視窗，輸入

sqlite3 [資料庫檔案的絕對位址]

出現版本号即打開成功

輸入 .table 檢視資料庫中的表格，這裡隻有一張表userInfo

更多相關資料庫操作可以看 21分鐘 MySQL 入門教程