SQLite数据库C++ API封装

　　在一个项目中需要记录目标路径下所有文件的MD5值，由于SQLite简单易用，选择利用它来记录数据。唯一的一张数据表HistoricalMD5，三个属性Dir（完整路径）、Time（时间）、MD5。SQLite常用的C++ API有：sqlite3_open、sqlite3_prepare、sqlite3_bind_parameter_index、sqlite3_bind_text、sqlite3_column_count、sqlite3_step、sqlite3_finalize、sqlite3_close，为了调用方便，对这些函数做了以下封装。

 

Database.h

#include <string>
#include <vector>
#include <sqlite3.h>

using namespace std;

class Database
{
public:
    Database(const char* filename);
    ~Database();

    void insert(string dir, int time, string MD5);
    void update(string dir, int time, string MD5);
    void query(vector<vector<string> > &results);

private:
    sqlite3 *database;

    bool openDB(const char* filename);
    void createTable();
    void createIndex();
    void closeDB();
};

 

Database.cpp

#include "Database.h"
#include <iostream>

Database::Database(const char* filename)
{
    database = NULL;
    openDB(filename);
    createTable();
    createIndex();
}

Database::~Database()
{
    closeDB();
}

bool Database::openDB(const char* filename)
{
    if(sqlite3_open(filename, &database) == SQLITE_OK)
        return true;

    return false;
}

void Database::createTable()
{
    sqlite3_stmt *statement;
    char* query = "CREATE TABLE IF NOT EXISTS HistoricalMD5 (Dir Text PREMARY KEY UNIQUE, Time INTEGER, MD5 TEXT)";

    int rc = sqlite3_prepare(database, query, -1, &statement, 0);
    if ( rc != SQLITE_OK) exit( -1 );

    sqlite3_step(statement);

    sqlite3_finalize(statement);

    string error = sqlite3_errmsg(database);
    if(error != "not an error") cout << query << " " << error << endl;
}

void Database::createIndex()
{
    sqlite3_stmt *statement;
    char* query = "CREATE INDEX IF NOT EXISTS idxOnText ON HistoricalMD5(Dir);";

    int rc = sqlite3_prepare(database, query, -1, &statement, 0);
    if ( rc != SQLITE_OK) exit( -1 );

    sqlite3_step(statement);

    sqlite3_finalize(statement);

    string error = sqlite3_errmsg(database);
    if(error != "not an error") cout << query << " " << error << endl;
}

void Database::insert(string dir, int time, string MD5)
{
    sqlite3_stmt *statement;
    char* query = "INSERT INTO HistoricalMD5 VALUES(@_dir, @_time, @_MD5);";

    int rc = sqlite3_prepare(database, query, -1, &statement, 0);
    if ( rc != SQLITE_OK) exit( -1 );

    int idx = -1;
    idx = sqlite3_bind_parameter_index( statement, "@_dir" );
    sqlite3_bind_text( statement, idx, dir.c_str(), -1, SQLITE_STATIC );
    idx = sqlite3_bind_parameter_index( statement, "@_time" );
    sqlite3_bind_int( statement, idx, time);
    idx = sqlite3_bind_parameter_index( statement, "@_MD5" );
    sqlite3_bind_text( statement, idx, MD5.c_str(), -1, SQLITE_STATIC );

    sqlite3_step(statement);

    sqlite3_finalize(statement);

    string error = sqlite3_errmsg(database);
    if(error != "not an error") cout << query << " " << error << endl;
}

void Database::update(string dir, int time, string MD5)
{
    sqlite3_stmt *statement;
    char* query = "UPDATE HistoricalMD5 SET time=@_time, MD5= @_MD5 WHERE dir=@_dir;";

    int rc = sqlite3_prepare(database, query, -1, &statement, 0);
    if ( rc != SQLITE_OK) exit( -1 );

    int idx = -1;
    idx = sqlite3_bind_parameter_index( statement, "@_dir" );
    sqlite3_bind_text( statement, idx, dir.c_str(), -1, SQLITE_STATIC );
    idx = sqlite3_bind_parameter_index( statement, "@_time" );
    sqlite3_bind_int( statement, idx, time);
    idx = sqlite3_bind_parameter_index( statement, "@_MD5" );
    sqlite3_bind_text( statement, idx, MD5.c_str(), -1, SQLITE_STATIC );

    sqlite3_step(statement);

    sqlite3_finalize(statement);

    string error = sqlite3_errmsg(database);
    if(error != "not an error") cout << query << " " << error << endl;
}

void Database::query(vector<vector<string> > &results)
{
    sqlite3_stmt *statement;
    char* query = "SELECT * FROM HistoricalMD5;";
    if(sqlite3_prepare(database, query, -1, &statement, 0) == SQLITE_OK)
    {
        int cols = sqlite3_column_count(statement);
        int result = 0;
        while(true)
        {
            result = sqlite3_step(statement);

            if(result == SQLITE_ROW)
            {
                vector<string> values;
                for(int col = 0; col < cols; col++)
                {
                    values.push_back((char*)sqlite3_column_text(statement, col));
                }
                results.push_back(values);
            }
            else
            {
                break;
            }
        }

        sqlite3_finalize(statement);
    }

    string error = sqlite3_errmsg(database);
    if(error != "not an error") cout << query << " " << error << endl;

}

void Database::closeDB()
{
    sqlite3_close(database);
}

 

　　推荐几篇相关技术文章：

1、SQlite数据库的C编程接口　　http://blog.csdn.net/northcan/article/details/7226137

2、SQLite学习手册　　http://www.cnblogs.com/stephen-liu74/archive/2012/01/22/2328757.html

3、更多对SQLite的封装　　http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers