sqlite3 批量插入数据库总结

#ifndef _DATABASEWRITER_H_
#define _DATABASEWRITER_H_
#include "sqlite3.h"
#include <string>
#include <vector>

#define __is_exists(filename) (_access(filename, 0) != -1)

struct userdata_t
{
    int type; /// integer, string
    int length;
    void* value;
};
struct database_init_t
{
    std::string _sql_ctb;
    std::string _sql_ctbi;
    std::string _sql_insrt;
};
// 特殊数据库, 所有表相同, 对象状态不轻易发生变化
class DatabaseWriter
{
public:
    DatabaseWriter(void);
    ~DatabaseWriter(void);
    /**
    * returns: 0:succeed.
    *          1.a new DatabaseWriter file was request to open.
    *         -1.unsolved error!
    */
    int init(const char* filename, const std::vector<database_init_t>& initinfo);
    void uninit(void);

    int start(void);
    int complete(void);

    void prepare(const std::vector<database_init_t>& initinfo);
    void finalize(void);

    void step(int tabloc);
    void fillData(int tabloc, int col, const userdata_t& dat);

private:
    std::string          filename;
    sqlite3*             db;
    sqlite3_stmt**       stmts;
    unsigned int         stmt_count;
};

#endif




#include <io.h>
#include <direct.h>
#include <stdlib.h>
#include "DatabaseWriter.h"


DatabaseWriter::DatabaseWriter(void) : filename(), db(), stmts(), stmt_count()
{
}

DatabaseWriter::~DatabaseWriter(void)
{
}

int DatabaseWriter::init(const char* filename, const std::vector<database_init_t>& initinfo)
{
    if(NULL == this->db)
    {
        if(SQLITE_OK == sqlite3_open(filename, &this->db))
        {
            this->stmt_count = initinfo.size();
            this->stmts = (sqlite3_stmt**)::malloc(sizeof(sqlite3_stmt**) * this->stmt_count);
            ::memset(this->stmts, 0x0, sizeof(sqlite3_stmt**) * this->stmt_count);
            
            if(!__is_exists(filename))
            {
                for(std::vector<database_init_t>::const_iterator i = initinfo.begin(); i != initinfo.end(); ++i)
                {
                    // create table
                    sqlite3_exec(this->db, i->_sql_ctb.c_str(), NULL, NULL, NULL); 

                    // create index
                    sqlite3_exec(this->db, i->_sql_ctbi.c_str(), NULL, NULL, NULL); 
                }
            }

            this->filename = filename;
            return 0;
        }
        else { // unsolved error
            return -1;
        }
    }
    return filename == this->filename ? 0 : 1;
}

void DatabaseWriter::uninit(void)
{
    if(this->db != NULL)
    {
        ::free(this->stmts);
        this->stmts = NULL;
        sqlite3_close(this->db);
        this->db = NULL;
    }
}

int DatabaseWriter::start(void)
{
    return sqlite3_exec(this->db, "begin;", NULL, NULL, NULL);
}

int DatabaseWriter::complete(void)
{
    return sqlite3_exec(this->db, "commit;", NULL, NULL, NULL);
}

void DatabaseWriter::prepare(const std::vector<database_init_t>& initinfo)
{
    for(size_t i = 0; i < this->stmt_count; ++i)
    {
        sqlite3_prepare_v2(this->db, initinfo[i]._sql_insrt.c_str(), -1, &this->stmts[i], NULL);
    }
}

void DatabaseWriter::finalize(void)
{
    for(size_t i = 0; i < this->stmt_count; ++i)
    {
        sqlite3_finalize(this->stmts[i]);
        this->stmts[i] = 0;
    }
}

void DatabaseWriter::step(int loc)
{
    sqlite3_step(this->stmts[loc]);
    sqlite3_reset(this->stmts[loc]);
}

void DatabaseWriter::fillData(int loc, int col, const userdata_t& dat)
{
    if(dat.type == 0)
    {
        sqlite3_bind_int64(this->stmts[loc], col, *( (__int64*)dat.value ));
    }
    else {
        sqlite3_bind_blob(this->stmts[loc], col, dat.value, dat.length, NULL);
    }
}



 
#include <iostream>
#include <exception>
#include <nsconv>
#include "DatabaseWriter.h"
#pragma comment(lib, "c:\\sqlite\\sqlite3.lib")

/** 
* 一条数据插入时:
* 1.打开数据库:
*            (1)数据库文件不存在, 则打开, 并创建表
*            (2)数据库文件不存在, 则打开, 不创建表
*            (3)数据库句柄不为空, 直接返回
* 2.数据插入做准备:
*            
*/

int main(int, char**)
{
    DatabaseWriter writer;
    database_init_t initinfo;
    std::vector<database_init_t> initinfoList;
    for(int i = 0; i < 10; ++i)
    {
        initinfo._sql_ctb = "create table tb_" + std::nsc::to_string<char>(i) + "(account BIGINT)";
        initinfo._sql_insrt = "insert into tb_" + std::nsc::to_string<char>(i) + " values(?)";
        initinfoList.push_back(initinfo);
    }

    int stat = writer.init("test.db", initinfoList);
    userdata_t dat;
    dat.type = 0;
    dat.length = 8;

    /// 一次批量插入
    writer.prepare(initinfoList);
    writer.start();
    for(int i = 0; i < 20; ++i)
    {
        dat.value = new __int64(i);
        writer.fillData(i % 10, 1, dat);
        writer.step( i % 10 );
    }
    stat = writer.complete();
    writer.finalize();

    writer.uninit();

    return 0;
}