sqlite3增删改查C++类实现

sqlite是一款轻量级数据库，由于它的管理接口简单、速度快和稳定性高等特点。在嵌入式系统中也越来越受欢迎。这里贴出简单的使用笔记，以作记录。
sqlitehandle.h:

/*************************************************************************
    > File Name: sqlitehandle.h
    > Author: lius
    > Mail: lius_153@163.com 
    > Created Time: Tue 05 Apr 2016 10:16:51 AM CST
 ************************************************************************/
#include<iostream>
#include<string.h>
#include<stdio.h>
#include"sqlite-3.6.18/sqlite3.h"

class sqliteHandle
{
private:
    sqlite3 *conn;
public:
    sqliteHandle();
    ~sqliteHandle();
    //创建数据库
    bool sqlite3OpenDb();
    //创建数据库表
    bool sqlite3CreateTable();
    //插入数据
    bool sqlite3InsertData(unsigned char (&)[1538]);
    //删除数据
    bool sqlite3DeleteData(unsigned char id);
    //修改数据
    bool sqlite3ChangeData(unsigned char (&)[1538]);
    //查询数据
    bool sqlite3SelectData(unsigned char (&)[1538]);
};

sqlitehandle.cpp:

/*************************************************************************
    > File Name: sqlitehandle.cpp
    > Author: lius
    > Mail: lius_153@163.com 
    > Created Time: Tue 05 Apr 2016 10:17:00 AM CST
 ************************************************************************/
#include"sqlitehandle.h"
using namespace std;
sqliteHandle::sqliteHandle()
{
    conn = NULL;
}
sqliteHandle::~sqliteHandle()
{
    //关闭数据库
    sqlite3_close(conn);
    cout << "sqlite : sqlite close." << endl;
}
//创建数据库
bool sqliteHandle::sqlite3OpenDb()
{
    //打开数据库
    int result = sqlite3_open("/mytest.db",&conn);
    if (result != SQLITE_OK)
    {
       sqlite3_close(conn);
       cout << "sqlite : open sqlite failed. " << endl;
       return false;
    }
    return true;
}
//创建数据库表
bool sqliteHandle::sqlite3CreateTable()
{
    sqlite3_stmt* stmt = NULL;
    const char* createTableETag = "CREATE TABLE ESTABLE(ID BLOB PRIMARY KEY, DATA BLOB)";
    int len = strlen(createTableETag);
    //准备创建数据表，如果创建失败，需要用sqlite3_finalize释放sqlite3_stmt对象，以防止内存泄露。
    if (sqlite3_prepare_v2(conn,createTableETag,len,&stmt,NULL) != SQLITE_OK)
    {
        if (stmt)
            sqlite3_finalize(stmt);
        cout << "sqlite : Create table ETag data failed. " << endl;
        return false;
    }
    //通过sqlite3_step命令执行创建表的语句。对于DDL和DML语句而言，sqlite3_step执行正确的返回值
    //只有SQLITE_DONE，对于SELECT查询而言，如果有数据返回SQLITE_ROW，当到达结果集末尾时则返回
    //SQLITE_DONE。
    if (sqlite3_step(stmt) != SQLITE_DONE) 
    {
        sqlite3_finalize(stmt);
        return false;
    }
    //释放创建表语句对象的资源。
    sqlite3_finalize(stmt);
    cout << "sqlite : Succeed to create test table now." << endl;
    return true;
}
//插入数据
bool sqliteHandle::sqlite3InsertData(unsigned char (&buffer)[1538])
{
    const char* insertSQL1 = "INSERT INTO ESTABLE VALUES(";
    unsigned char* insertSQL2 = new unsigned char[strlen(insertSQL1)/* +strlen(buffer) */+1538];//先分配一块足够的空间    
    strcpy((char*)insertSQL2, insertSQL1);//拷贝
    strncat((char*)insertSQL2, (char*)&buffer[0], 1);//追加
    strcat((char*)insertSQL2, ", '");//追加
    strcat((char*)insertSQL2, (char*)&buffer[1]);//追加
    strcat((char*)insertSQL2, "')");//追加

    char* errmsg;
    if (sqlite3_exec(conn,(const char*)insertSQL2,NULL,NULL,&errmsg) != SQLITE_OK)  
    {
        cout << "sqlite : insert data failed. error : " << errmsg << endl;
        sqlite3_free(errmsg);
        return false;
    }
    return true;
}
//删除数据
bool sqliteHandle::sqlite3DeleteData(unsigned char id)
{   
    const char* deleteSQL1 = "DELETE FROM ESTABLE WHERE ID = ";
    unsigned char* deleteSQL2 = new unsigned char[strlen(deleteSQL1)/* +strlen(buffer) */+1538];//先分配一块足够的空间    
    strcpy((char*)deleteSQL2, deleteSQL1);//拷贝
    strcat((char*)deleteSQL2, (char*)&id);//追加

    char* errmsg;
    if (sqlite3_exec(conn,(const char*)deleteSQL2,NULL,NULL,&errmsg) != SQLITE_OK)  
    {
        cout << "sqlite : delete data failed. error : " << errmsg << endl;
        sqlite3_free(errmsg);
        return false;
    }
    return true;
}
//修改数据
bool sqliteHandle::sqlite3ChangeData(unsigned char (&buffer)[1538])
{
    if(sqlite3SelectData(buffer))
    {
        return false;
    }
    const char* changeSQL1 = "UPDATE ESTABLE SET DATA = '";
    unsigned char* changeSQL2 = new unsigned char[strlen(changeSQL1)/* +strlen(buffer) */+1538];//先分配一块足够的空间    
    strcpy((char*)changeSQL2, changeSQL1);//拷贝
    strcat((char*)changeSQL2, (char*)&buffer[1]);//追加
    strcat((char*)changeSQL2, "' WHERE ID = ");//追加
    strncat((char*)changeSQL2, (char*)&buffer[0], 1);//追加

    char* errmsg;
    if (sqlite3_exec(conn,(const char*)changeSQL2,NULL,NULL,&errmsg) != SQLITE_OK)  
    {
        cout << "sqlite : change data failed. error : " << errmsg << endl;
        sqlite3_free(errmsg);
        return false;
    }
    return true;
}
//查询数据
bool sqliteHandle::sqlite3SelectData(unsigned char (&buffer)[1538])
{   
    const char* selectSQL3 = "SELECT * FROM ESTABLE";
    const char* selectSQL1 = "SELECT DATA FROM ESTABLE WHERE ID = '";
    unsigned char* selectSQL2=new unsigned char[strlen(selectSQL1)/* +strlen(buffer) */+1538];//先分配一块足够的空间    
    strcpy((char*)selectSQL2, selectSQL1);//拷贝
    strncat((char*)selectSQL2, (char*)&buffer[0], 1);//追加
    strcat((char*)selectSQL2, "'");//追加

    char* errmsg;
    char** pResult;
    int nRow, nCol;
    if (sqlite3_get_table(conn, (const char*)selectSQL3, &pResult, &nRow, &nCol, &errmsg) != SQLITE_OK)
    {
        cout << "sqlite : select data failed. error : " << errmsg << endl;
        sqlite3_free(errmsg);
        return false;
    }
    string strOut;
    int nIndex = nCol;
    for(int i=0;i<nRow;i++)
    {
        for(int j=0;j<nCol;j++)
        {
            memset(&buffer[1], 0 , 1538);
            strOut+=pResult[j];
            strOut+=":";
            strOut+=pResult[nIndex];
            strcat((char*)&buffer[1], pResult[nIndex]);//追加
            strOut+="\n";
            ++nIndex;
        }
    }
    sqlite3_free_table(pResult);

    cout<<strOut<<endl;

    cout<<buffer<<endl;

    return true;
}

sqlitemain.cpp:

/*************************************************************************
    > File Name: sqlitemain.cpp
    > Author: lius
    > Mail: lius_153@163.com 
    > Created Time: Tue 05 Apr 2016 04:35:22 PM CST
 ************************************************************************/

#include <iostream>
#include "sqlitehandle.h"
using namespace std;
int main()
{
    sqliteHandle sql;

    if(!sql.sqlite3OpenDb())
        cout << "sqlite open failed!" << endl;
    else
        cout << "sqlite open success!" << endl;
    sql.sqlite3CreateTable();
    unsigned char buffer[1538] = "1hasdfhaidhf34r48hfadf34rhfaaksdhfkasdhfkasdhfkasdhfkjasdhfkjasfuyasd";
    sql.sqlite3InsertData(buffer);
    sql.sqlite3SelectData(buffer);
    //sql.sqlite3DeleteData(buffer[0]);
    //sql.sqlite3SelectData(buffer);
    unsigned char buffer1[1538] = "12222222222222211111111111111112222222233333333333311111111111111111";
    sql.sqlite3ChangeData(buffer1);
    sql.sqlite3SelectData(buffer);
}

代码中本来想用srting来搞，但是使用的数据类型都是unsigned char.不想将类型转来转去，所以就这样操作了。
如想将类中传入定长数组改为变长数组可以采用模版函数实现。