Qt中使用sqlite3.h小工具类

因为sqlite是c语言写的，它的跨系统特性比较好，一般直接把sqlite.h和sqlite.c文件放到项目中就可以了

#ifndef DBUTIL_H
#define DBUTIL_H

#include <QString>

#include "sqlite3/sqlite3.h"


/**
 * database util
 */
class DBUtil
{

public:
    DBUtil();
    ~DBUtil();


public:
    void close();

    bool insert_data(
            const QString& sql_str);

    void update_data(
            const QString& sql_str);

    QMap<QString, QString> select_line_data(
            const QString& sql_str);

    // select many row data
    QVector<QMap<QString, QString> >
    select_data(
            const QString& sql_str);

    bool select_exist(
            const QString& sql_str);

    void delete_data(
            const QString& sql_str);


private:
    bool connect();


private:
    sqlite3* d_db;




};

#endif // DBUTIL_H

#include "DBUtil.h"
#include "util/CommonFileUtil.h"

#include <QApplication>
#include <QDebug>

#include <sstream>



DBUtil::DBUtil()
{
}



DBUtil::~DBUtil()
{}



void
DBUtil::close()
{

}



bool
DBUtil::connect()
{
    QByteArray db_name = CommonFileUtil::get_database_name().toUtf8();
    const char* file_name = db_name.data();
    if( SQLITE_OK != sqlite3_open(file_name, &d_db) )
    {
        qDebug() << __FILE__ << __LINE__ << "warning: database not connect. " << file_name << sqlite3_errcode(d_db) << sqlite3_errmsg(d_db) << "\n";
        return false;
    }

    return true;
}



bool
DBUtil::insert_data(
        const QString& sql_str)
{
    const bool& c_ok = connect();
    if( !c_ok )
    {
        return false;
    }


    char* error_msg;
    int result = sqlite3_exec(d_db, sql_str.toUtf8().data(), 0, 0, &error_msg);

    sqlite3_free(error_msg);
    sqlite3_close(d_db);

    return (result == SQLITE_OK);
}



void
DBUtil::update_data(
        const QString& sql_str)
{
    const bool& c_ok = connect();
    if( !c_ok )
    {
        return;
    }

    char* error_msg;
    int result = sqlite3_exec(d_db, sql_str.toUtf8().data(), 0, 0, &error_msg);

    sqlite3_free(error_msg);
    sqlite3_close(d_db);

    Q_UNUSED(result);
}



QMap<QString, QString>
DBUtil::select_line_data(
        const QString& sql_str)
{
    QMap<QString, QString> map_result_data;
    const bool& c_ok = connect();
    if( !c_ok )
    {
        return map_result_data;
    }

    sqlite3_stmt* stmt = nullptr;
    int result = sqlite3_prepare(d_db, sql_str.toUtf8().data(), sql_str.length(), &stmt, nullptr);
    if( SQLITE_OK != result )
    {
        qDebug() << __FILE__ << __LINE__ << "warning: database not connect. " << sqlite3_errmsg(d_db) << "\n";
        return map_result_data;
    }

    if( SQLITE_ROW == sqlite3_step(stmt) )
    {
        const int& column_count = sqlite3_column_count(stmt);
        for(int i = 0; i < column_count; i++)
        {
            //qDebug() << __FILE__ << __LINE__ << " i = " << i;
            const char* key = sqlite3_column_name(stmt, i);
            const unsigned char* text = sqlite3_column_text(stmt, i);
            //qDebug() << __FILE__ << __LINE__ << " key = " << key << " text = " << text;
            std::ostringstream oss;
            if( text )
            {
                oss << text;
            }
            else
            {
                oss << "";
            }
            
            map_result_data[QString::fromLocal8Bit(key)] = QString::fromStdString(oss.str());
        }
    }
    //qDebug() << __FILE__ << __LINE__ << " map_result_data = " << map_result_data;

    sqlite3_finalize(stmt);
    sqlite3_close(d_db);

    return map_result_data;
}



QVector<QMap<QString, QString> >
DBUtil::select_data(
        const QString& sql_str)
{
    QVector<QMap<QString, QString> > result_data;
    const bool& c_ok = connect();
    if( !c_ok )
    {
        return result_data;
    }

    QMap<QString, QString> column_data;

    sqlite3_stmt* stmt = nullptr;
    int result = sqlite3_prepare(d_db, sql_str.toUtf8().data(), sql_str.length(), &stmt, nullptr);
    if( SQLITE_OK != result )
    {
        qDebug() << __FILE__ << __LINE__ << "warning: database not connect. " << sqlite3_errmsg(d_db) << "\n";
        return result_data;
    }

    while( SQLITE_ROW == sqlite3_step(stmt) )
    {
        const int& column_count = sqlite3_column_count(stmt);
        for(int i = 0; i < column_count; i++)
        {
            //qDebug() << __FILE__ << __LINE__ << " i = " << i;
            const char* key = sqlite3_column_name(stmt, i);
            const unsigned char* text = sqlite3_column_text(stmt, i);
            //qDebug() << __FILE__ << __LINE__ << " key = " << key << " text = " << text;
            std::ostringstream oss;
            if( text )
            {
                oss << text;
            }
            else
            {
                oss << "";
            }

            column_data[QString::fromLocal8Bit(key)] = QString::fromStdString(oss.str());
            //qDebug() << __FILE__ << __LINE__ << " key = " << key << " text = " << text;
        }

        result_data.push_back(column_data);
    }

    //qDebug() << __FILE__ << __LINE__ << " result_data = " << result_data;

    sqlite3_finalize(stmt);
    sqlite3_close(d_db);

    return result_data;
}



bool
DBUtil::select_exist(
        const QString& sql_str)
{
    const bool& c_ok = connect();
    if( !c_ok )
    {
        return false;
    }

    sqlite3_stmt* stmt = nullptr;
    int result = sqlite3_prepare(d_db, sql_str.toUtf8().data(), sql_str.length(), &stmt, nullptr);
    if( SQLITE_OK != result )
    {
        qDebug() << __FILE__ << __LINE__ << "warning: database not connect. " << sqlite3_errmsg(d_db) << "\n";
        return false;
    }

    if( SQLITE_ROW == sqlite3_step(stmt) )
    {
        sqlite3_finalize(stmt);
        sqlite3_close(d_db);
        return true;
    }

    sqlite3_finalize(stmt);
    sqlite3_close(d_db);

    return false;
}



void
DBUtil::delete_data(
        const QString& sql_str)
{
    const bool& c_ok = connect();
    if( !c_ok )
    {
        return;
    }

    char* error_msg;
    int result = sqlite3_exec(d_db, sql_str.toUtf8().data(), 0, 0, &error_msg);

    sqlite3_free(error_msg);
    sqlite3_close(d_db);

    Q_UNUSED(result);
}

前面有一篇是使用了Qt中的数据库类，本篇直接使用sqlite3的api O(∩_∩)O哈哈~